Comma separated / delimited string to a table in SQL Server

Recently, I came across a piece of TSQL code that would take a comma separated string as an input and parse it to return a single column table from it.
CREATE FUNCTION [dbo].[UDF_string_to_table]

(
@string VARCHAR(8000),
@delimiter CHAR(1)
)
RETURNS @output TABLE(CommaSeparatedString VARCHAR(256))

BEGIN

DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1

INSERT INTO @output (CommaSeparatedString) VALUES(SUBSTRING(@string, @start, @end – @start))

SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END

RETURN
END

Output:

select * from UDF_string_to_table(‘1,2,3′,’,’)

ADO.NET Entity Framework

What is Entity Framework?

The Microsoft ADO.NET Entity Framework is an Object/Relational Mapping (ORM) framework that enables developers to work with relational data as domain-specific objects, eliminating the need for most of the data access plumbing code that developers usually need to write. Using the Entity Framework, developers issue queries using LINQ, then retrieve and manipulate data as strongly typed objects. The Entity Framework’s ORM implementation provides services like change tracking, identity resolution, lazy loading, and query translation so that developers can focus on their application-specific business logic rather than the data access fundamentals.

To simply say it: Entity framework is an Object/Relational Mapping (O/RM) framework. It is an enhancement to ADO.NET that gives developers an automated mechanism for accessing & storing the data in the database and working with the results in addition to DataReader and DataSet.

Image

Now the question is what is O/RM framework and why do we need it?

ORM is a tool for storing data from domain objects to relational database like MS SQL Server in an automated way without much programming.

O/RM includes three main parts:

Domain class objects, Relational database objects and Mapping information on how domain objects maps to relational database objects (tables, views  &  storedprocedures).

ORM helps us to keep our database design separate from our domain class design. This makes application maintainable and extendable. It also automates standard CRUD operation (Create, Read, Update & Delete) so developer doesn’t need to write it manually.

What it exactly Mean:Suppose a developer needs to write a code to create/ edit and delete and customer information, he creates stored procedures in the database, and he writes the code to calls this procedures with relevant parameters.

If he needs to support more than one database, they need to write different code (database specific) to execute these stored procedures.

What if suppose we have option to access different database with single set of code and also no need to worry much about database connectivity and access??……. That’s what entity framework does.

Since the entity framework integrated with LINQ, we (developer) can issue all queries in the code to do the database operation. With these features we can now concentrate more on writing business logic instead of spending time on relational data.

Since the entity framework built on ado.net provider model, with existing provider being updated additively to support this entity framework, the developer can easily migrate/ upgrade the application built on asp.net to entity framework.

Some of the Capabilities of Entity Framework:

a. It supports major database servers.

b. It can handle real-world database schemas and Stored procedures

c. Its visual studio integrated features provides option to generate models automatically.

d. It is integrated with asp.net, WCF, WPF and WCF data services

Some of the high level benefits:

a. Reduces the development time spending on database access

b. No need to write different database access code for different databases

c. Database object schema can be changed without doing changes in the code

Reference:http://msdn.microsoft.com/en-us/library/bb399567.aspx

How to Commit and Rollback Transaction in .NET & Sql Server

If you are sending several commands to SQL Server database, you should make it a habit to build your application to be rollback capable in order to recover all commands that were already executed. In ADO.NET, we can achieve this recovery process by using the SqlTransaction object. A SqlTransaction’s ROLLBACK method can undo any work performed since the BeginTransaction method was executed while the COMMIT method ends a transaction and makes all changes visible to other users.

Example C#:

SqlTransaction trans = null;

try
{
con.Open();
// BeginTransaction() Requires Open Connection
trans = con.BeginTransaction();

// Assign Transaction to Command
cmd.Transaction = trans;

//1st command to execute. This will be rollback if 2nd command fails
cmd.CommandText = “INSERT INTO HeaderTab (Description) VALUES (‘Inserted New Category’)”;
cmd.ExecuteNonQuery();

//2nd command to execute
cmd.CommandText = “INSERT INTO DetailedTab (Description) VALUES (‘TEST CATEGORY’)”;
cmd.ExecuteNonQuery();

trans.Commit();
}
catch
{
trans.Rollback();
Console.WriteLine(“Error while processing command. Previous execution was rollbacked”);
}
finally
{
con.Close();
}

Example SqlServer:

CREATE PROCEDURE DeleteEmployee
( @EmployeeID int )
AS
BEGIN
BEGIN TRY
 BEGIN TRANSACTION TraDel— Start the transaction

— Delete the Employee record
DELETE FROM Employees
WHERE EmployeeID = @EmployeeID

— If we reach here, success!
COMMIT TRANSACTION TraDel
END TRY
BEGIN CATCH
— Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION TraDel

— Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
END