Table of Contents
1. Create a User-Defined TableType in your db.
2. Create Stored Procedure and define the parameters.
3. using c# .net, send the DataTable data to sql server.
Create a User-Defined TableType in your db.
/******
Object: UserDefinedTableType [dbo].[MapExcelTableType] Script Date: 07/22/2014 14:57:05 ******/
CREATE TYPE [dbo].[MapExcelTableType]
AS TABLE(
[ID] [int] NULL,
[CostCenter] [varchar](150) NULL,
[MobileNo] [varchar](150) NULL,
[EmailID] [varchar](150) NULL,
[FirstName] [varchar](150) NULL,
[LastName] [varchar](150) NULL,
[Services] [varchar](150) NULL,
[UsageType] [varchar](150) NULL,
[Network] [varchar](150) NULL,
[UsageIncluded] [int] NULL,
[Unit] [varchar](150) NULL
)
GO
Create Stored Procedure and define the parameters.
ALTER PROCEDURE [dbo].[SubscriberServiceMappingByExcel]
@TableData MapExcelTableType Readonly,
@CompanyID int,
@TenantID int
AS
BEGIN
SET NOCOUNT
ON
DECLARE @ID
int,
@CostCenter varchar(150), @MobileNo varchar(150), @EmailID varchar(150), @FirstName varchar(150), @LastName varchar(150),@Services varchar(150),@UsageType varchar(150), @Network varchar(150), @UsageIncluded int, @Unit varchar(150)
DECLARE @TempTable TABLE (ID int,CostCenter varchar(150), MobileNo varchar(150), EmailID varchar(150), FirstName varchar(150), LastName varchar(150),Services varchar(150),UsageType varchar(150), Network varchar(150), UsageIncluded int, Unit varchar(150))
DECLARE
_cursor CURSOR FOR
SELECT ID, CostCenter,
MobileNo, EmailID,
FirstName, LastName,
Services,
UsageType, Network,
UsageIncluded, Unit FROM
@TableData
OPEN _cursor
FETCH NEXT
FROM _cursor
INTO @ID,
@CostCenter,
@MobileNo,
@EmailID,
@FirstName,
@LastName,
@Services,
@UsageType,
@Network,
@UsageIncluded,
@Unit
WHILE @@FETCH_STATUS
= 0
BEGIN
Insert Into
[Subscriber](
EmailID
,MobileNo
,FirstName
,LastName
,CompanyID
,TenantID
,StatusID
,CostCenterID
)
values
(
@EmailID
,@MobileNo
,@FirstName
,@LastName
,@CompanyID
,@TenantID
,3
,@CostCenter
)
END
FETCH NEXT
FROM _cursor
INTO
@ID, @CostCenter, @MobileNo, EmailID, @FirstName,@LastName,@Services,@UsageType,@Network,
@UsageIncluded,@Unit
END
CLOSE _cursor
DEALLOCATE _cursor
IF(@@ERROR = 0)
SELECT 1
ELSE
SELECT 1
END
using c# .net, send the DataTable data to sql server.
public string SaveExcelData(DataTable excelTable, int CompanyID, int TenantID)
{
string strReturnValue = "";
try
{
SqlHelper helper = new SqlHelper();
helper.OpenConnection();
ArrayList sqlParameter = new ArrayList();
object returnValue = "";
var param = new SqlParameter("@TableData", SqlDbType.Structured);
param.TypeName = "dbo.MapExcelTableType";
param.Value = excelTable;
sqlParameter.Add(param);
sqlParameter.Add(new SqlParameter("@CompanyID",
CompanyID));
sqlParameter.Add(new SqlParameter("@TenantID",
TenantID));
returnValue =
helper.ExecuteScalar("SubscriberServiceMappingByExcel", CommandType.StoredProcedure, sqlParameter);
try
{
strReturnValue =
returnValue.ToString();
}
catch (Exception ex)
{ }
}
catch (Exception ex)
{
strReturnValue = "error";
}
return strReturnValue;
}