User Defined Table Types : SQL Server 2008

Every programmer will come across situations where application need to interact with a database to store a batch of records together.

As an example, let’s consider student education history.
Assume, we have a school management application where user can provide details of previous schools studied by a student.

A student might have studied in more than 1 School previously. When User provides details of last n schools, We usually store them in a ado.net datatable.
Assume, I have a database table structure like this to store the details of student’s previous education.

PREVIOUS_SCHOOL

SCHOOL_NAME Varchar (250)
SCHOOL_ADDRESS Varchar (250)
STANDARDS_COVERED Varchar (50)
LAST_STANDARD_MEDIUM Varchar (20)
LAST_STANDARD_ACADEMIC_YEAR Varchar (20)
LAST_STANDARD_PERCENTAGE Varchar (20)
SCHOOL_LEAVING_DATE Varchar (20)
SCHOOL_LEAVING_REASON Varchar (250)

Now, typically what a beginner programmer would do is to call a stored procedure to insert each previous school record. There will be a total of 5 calls to the procedure inserting one at a time. Also, there is a strict need of handling a transaction too at a level to make sure data is stored as intended.

Not new but an other way to achieve the same functionality would be call the stored procedure only once and send data of all 5 students at one go. The procedure can then insert the records to table.

This is possible through using SQL Server 2008 user defined table types.

What is a user defined table type?
By definition from Microsoft,
User-defined table type is a user-defined type that represents the definition of a table structure. You can use a user-defined table type to declare table-valued parameters for stored procedures or functions, or to declare table variables that you want to use in a batch or in the body of a stored procedure or function.

How to create a user defined table type for our above requirement?
CREATE TYPE [dbo].[UDT_PREVIOUS_SCHOOL] AS TABLE(

[SCHOOL_NAME] [varchar](250) NULL,
[SCHOOL_ADDRESS] [varchar](250) NULL,
[STANDARDS_COVERED] [varchar](50) NULL,
[LAST_STANDARD_MEDIUM] [varchar](20) NULL,
[LAST_STANDARD_ACADEMIC_YEAR] [varchar](20) NULL,
[LAST_STANDARD_PERCENTAGE] [varchar](20) NULL,
[SCHOOL_LEAVING_DATE] [varchar](20) NULL,
[SCHOOL_LEAVING_REASON] [varchar](250) NULL
)

Now, assume that we have the details of 5 Students in a datatable written from your ASP.Net code.

The datatable would have columns similar to the table structure we have.
Now, let us write a stored procedure which can accept Datatable as a parameter.
CREATE PROCEDURE [dbo].[USP_SAVE_STUDENT_PREV_DETAILS]

@PREV_SCHOOLS dbo.UDT_PREVIOUS_SCHOOL READONLY

AS 

BEGIN TRANSACTION

INSERT  INTO PREVIOUS_SCHOOL
( 
[SCHOOL_NAME] ,
[SCHOOL_ADDRESS] ,
[STANDARDS_COVERED] ,
[LAST_STANDARD_MEDIUM] ,
[LAST_STANDARD_ACADEMIC_YEAR] ,
[LAST_STANDARD_PERCENTAGE] ,
[SCHOOL_LEAVING_DATE] ,
[SCHOOL_LEAVING_REASON]
)
SELECT  PS.SCHOOL_NAME ,
PS.SCHOOL_ADDRESS ,
PS.STANDARDS_COVERED ,
PS.LAST_STANDARD_MEDIUM ,
PS.LAST_STANDARD_ACADEMIC_YEAR ,
PS.LAST_STANDARD_PERCENTAGE ,
PS.SCHOOL_LEAVING_DATE ,
PS.SCHOOL_LEAVING_REASON
FROM    @PREV_SCHOOLS AS PS ;


IF @@ERROR <> 0 
BEGIN
-- Rollback the transaction
ROLLBACK

-- Raise an error and return
RAISERROR ('ERROR_INSERTING_PREVIOUS_SCHOOL_DETAILS', 16, 1)
RETURN '0'
END         
Did you observe the line at the beginning of procedure?

@PREV_SCHOOLS dbo.UDT_PREVIOUS_SCHOOL READONLY
Yes. @PREV_SCHOOLS is a parameter of the type UDT_PREVIOUS_SCHOOL we just created at the beginning. As implies it should be readonly and cannot be written. May be in future, MS will think on it.

We can now pass a datatable which has the structure of this user defined type to this stored procedure!

I know you don’t want to wait to see the code to call the stored procedure we just created.

Here is the code,
''' This method returns the connection object of the database
Private Shared Function Connection() As SqlConnection
Dim SQLCon As New SqlClient.SqlConnection
SQLCon.ConnectionString = ";"
Return SQLCon
End Function


''' This method saves all student previous school records at one shot!
''' Datatable having 5 rows of data each belonging to 1 school. 
''' Data Columns in the datatable should have same structure we have in user defined

table type at backendPublic Shared Sub SaveStudentPreviousSchools(ByVal i_dtPreviousSchoolTable As DataTable)
Dim objSqlCon As New SqlConnection
Dim SQLCmd As New SqlCommand()
Const sParamName As String = "PREV_SCHOOLS" ' This is the name of parameter your stored 

procedure accepts
Try
objSqlCon = Connection()
objSqlCon.Open()

'STORED PROCEDURE YOU CREATED IN SQL SERVER
SQLCmd.CommandText = "USP_SAVE_STUDENT_PREV_DETAILS"

SQLCmd.CommandType = CommandType.StoredProcedure
SQLCmd.Connection = objSqlCon

SQLCmd.Parameters.AddWithValue(sParamName, i_dtPreviousSchoolTable)
SQLCmd.Parameters(sParamName).SqlDbType = SqlDbType.Structured

' USER DEFINED TYPE YOU CREATED IN SQL SERVER
SQLCmd.Parameters(sParamName).TypeName = "UDT_PREVIOUS_SCHOOL"

SQLCmd.ExecuteScalar()
Catch ex As Exception
'Do whatever you want
Finally
objSqlCon.Close()
End Try
End Sub
Now don’t worry... Here is the c# version for you... (By a converter)
/// This method returns the connection object of the database

private static SqlConnection Connection()
{
System.Data.SqlClient.SqlConnection SQLCon = new System.Data.SqlClient.SqlConnection();
SQLCon.ConnectionString = "";
return SQLCon;
}

/// This method saves all student previous school records at one shot!
/// param name "i_dtPreviousSchoolTable": Datatable having 5 rows of data each belonging to 1 school. 
/// Data Columns in the datatable should have same structure we have in user defined table type at backendpublic static void SaveStudentPreviousSchools(System.Data.DataTable i_dtPreviousSchoolTable)
{
SqlConnection objSqlCon = new SqlConnection();
SqlCommand SQLCmd = new SqlCommand();
const string sParamName = "PREV_SCHOOLS";
// This is the name of parameter your stored procedure accepts
try
{
objSqlCon = Connection();
objSqlCon.Open();

//STORED PROCEDURE YOU CREATED IN SQL SERVER
SQLCmd.CommandText = "USP_SAVE_STUDENT_PREV_DETAILS";

SQLCmd.CommandType = System.Data.CommandType.StoredProcedure;
SQLCmd.Connection = objSqlCon;

SQLCmd.Parameters.AddWithValue(sParamName, i_dtPreviousSchoolTable);
SQLCmd.Parameters[sParamName].SqlDbType = System.Data.SqlDbType.Structured;

// USER DEFINED TYPE YOU CREATED IN SQL SERVER
SQLCmd.Parameters[sParamName].TypeName = "UDT_PREVIOUS_SCHOOL";

SQLCmd.ExecuteScalar();
}
catch (Exception ex)
{
//Do whatever you want
}
finally
{
objSqlCon.Close();
}
}  


i_dtPreviousSchoolTable : Datatable which has the 5 records

What did we achieve?

1 call to db and 5 records saved. Better than any common approach.
We did not use any cursors to loop through the records too.

Few restrictions about user defined table types from Microsoft.

• A user-defined table type cannot be used as a column in a table or a field in a structured user-defined type.

• Alias types based on a user-defined table type

• The [NOT FOR REPLICATION] option is not allowed.

• CHECK constraints require a computed column to be persisted.

• The primary key on computed columns must be PERSISTED and NOT NULL.

• A nonclustered index cannot be created on a user-defined table type unless the index is the result of creating a PRIMARY KEY or UNIQUE constraint on the user-defined table type. (SQL Server enforces any UNIQUE or PRIMARY KEY constraint by using an index.)

• The user-defined table type definition cannot be modified after it is created.

• User-defined functions cannot be called within the definition of computed columns of a user-defined table type.

Conclusion
The User defined table type is very useful when doing bulk operations like above. Also, it provides a way wherein you can have your own type without depending on a different structure.

Popular posts from this blog

Facebook Javascript API : Feed and Share Dialog for Beginners

What's new and expected in .NET Framework 4.5

Real time Push Notifications with SignalR & PNotify (Pines Notify)