How to insert a data table into SQL Server database table

How to insert a data table into SQL Server database table

Table of Contents

1. Create a User-Defined TableType in your db.
2. Create Stored Procedure and define the parameters.


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

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..
My Blogs - https://code-sample.com and https://code-sample.xyz
My Book1 - BEST SELLING ANGULAR BOOK (INCLUDING ALL VERSIONS 2, 4, 5, 6, 7)
My Book2 - ANGULAR 2 INTERVIEW QUESTIONS BOOK - Both Books are Available on WorldWide.

www.code-sample.com/. Powered by Blogger.