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:
I got benfit from this post. thanks
Post a Comment