Search This Blog & Web

Thursday, January 13, 2011

Table Type Parameter in SQL SERVER 2008

We have problem to insert multi line data from websites or Grid into database.
Till SQL SERVER 2000 we will send calls for each row insertion to the procedure
In SQL SERVER 2005 there is a new data type XML which can be used to insert multiple data in one row. But we need to include XQuery to read that data first.
In SQL SERVER 2008 a new parameter type Table Parameter introduce which can be used to pass parameter from code to database.
to do this we need to perform following
1. Create a User defined table type

/****** Object:  UserDefinedTableType [dbo].[CommentsValues]    Script Date: 01/13/2011 10:59:07 ******/
CREATE TYPE [dbo].[CommentsValues] AS TABLE(
[CommentID] [int] NULL,
[Value] [bit] NULL,
[Comments] [varchar](max) NULL,
[CommentTypeId] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[CommentTypeId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

2. Create Procedure using user define table type.

CREATE PROCEDURE [dbo].[up_insertUpdateComments]
-- Add the parameters for the stored procedure here
@PropID int,
@UserID int,
@pCommentsValues CommentsValues READONLY
AS
BEGIN
SET NOCOUNT ON;

Declare @tCommentsValues table ( [CommentID] int , [Value] bit , [Comments] text, [CommentTypeId] int)

Insert into @tCommentsValues ( [CommentID] , [Value] , [Comments] , [CommentTypeId] )
select (case when CommentID is null  then (row_number() over (order by [CommentTypeId]) + @CommentId) else commentid end) ,value,Comments,[CommentTypeId]
from @pCommentsValues


   select * from @tCommentsValues


END
3. Call from .Net Application
public datatable GetTable()
{
DataTable CustomTable = new DataTable();
CustomTable.Columns.Add(new DataColumn("Id", Type.GetType("System.Int32")));
CustomTable.Columns.Add(new DataColumn("CValue", Type.GetType("System.String")));
DataRow row = CustomTable.NewRow();
row[0] ="1";
 row[1] = "wajid";
 CustomTable.Rows.Add(row);
 row[0] ="2";
 row[1] = "shamas";
 CustomTable.Rows.Add(row);
row[0] ="3";
 row[1] = "sheraz";
 CustomTable.Rows.Add(row);
CustomTable.AcceptChanges();

return CustomTable;
}



1 comment:

Bill CEO said...

I got benfit from this post. thanks