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;
}
|
Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..
|