First of all we need to
create a user defined data type in SQL:
CREATE TYPE [dbo].[CustomUserType]
AS TABLE(
[Name] [varchar](250) NULL,
[Age] [varchar](250) NULL,
[Email] [varchar](250) NULL,
[Department] [nvarchar](200) NULL
)
GO
Now create a procedure
in SQL to consume variable of above defined data table:
CREATE PROC [dbo].[SaveCustomUserInfo]
(
@UserTable [dbo].CustomUserType
READONLY
)
AS
BEGIN
DECLARE @ErrCode INT
BEGIN TRAN
INSERT INTO
UserMaster([Name],[Age],[Email],
[Department])
SELECT [Name],[Age],[Email], [Department] FROM
@UserTable
SELECT @ErrCode = @@ERROR
IF (@ErrCode
<> 0) GOTO PROBLEM
COMMIT TRAN
PROBLEM:
IF (@ErrCode
<> 0) BEGIN
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
END
SELECT *
FROM UserMaster
END
Now pass table with
pre-defined columns to above create procedure:
public void SaveCustomUserInfo (DataTable UserTable)
{
ds = new
DataSet();
try
{
OpenConn();
strSql = "SaveCustomUserInfo";
SqlParameter[] parm = new SqlParameter[1];
parm[0] = new SqlParameter("@UserTable",
UserTable);
ds =
SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, strSql, parm);
}
catch
(Exception Ex)
{
throw
Ex;
}
finally
{ CloseConn(); }
}
Use following code to
create data table and pass to above mentioned function:
DataTable dt = new
DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Age");
dt.Columns.Add("Email");
dt.Columns.Add("Department");
DataRow dr = dt.NewRow();
dr["Name"]
= "Nitin";
dr["Age"]
= "30";
dr["Email"]
= "nitindhiman@gmail.com";
dr["Department"]
= ".net";
dt.Rows.Add(dr);
SaveServiceNowAccountDetail(dt);
