Insert multipal rows into database

 Sometimes we need to insert or update multipal records in a single call using c# code . Here I am going to tell how you can achive this throw the new features of SQL Server 2008. In Sql Server 2008, we can pass table type variable in the parameters of the stored procedrue. So, we can create a table type object in the .net code and pass that object in parameter. Let me give you an example step by step.

Steps:

1.      1. Create a table named Employee.

2.      

   CREATE TABLE [dbo].[Employee](
      [EmpId] [nvarchar](20) NULL,
      [Name] [nvarchar](50) NULL,
      [Salary] [decimal](18, 0) NULL
     ) ON [PRIMARY]
     GO

     

3.      2. Create a table type variable named EmployeeTable

    CREATE TYPE EmployeeTable AS TABLE
      (     
        [EmpId][nvarchar](20) NULL,
        [Name][nvarchar](50) NULL,
        [Salary][decimal](18, 0) NULL
      )
      GO

    3.  Create a stored procedure to insert/update the data.         

ALTER PROCEDURE SaveEmployee
      @EmployeeTable as EmployeeTable READONLY
AS
BEGIN
      SET NOCOUNT ON;

    DECLARE @InsertEmployeeTable as EmployeeTable,
            @UpdateEmployeeTable as EmployeeTable 
        
    /* Insert those records which are not exist in the Employee table  */
    INSERT INTO @InsertEmployeeTable 
    SELECT * FROM @EmployeeTable 
      WHERE [@EmployeeTable].[EmpId] NOT IN (SELECT EmpId FROM Employee)
    
    /* Insert those records which are exist in the Employee table  */
    INSERT INTO @UpdateEmployeeTable 
    SELECT * FROM @EmployeeTable
          WHERE [@EmployeeTable].[EmpId] IN (SELECT EmpId FROM Employee)
    
    /* Insert the new records in the Employee table */
    INSERT INTO Employee 
    SELECT * FROM @InsertEmployeeTable
    
    /* update the existing records in the Employee table */
    UPDATE Employee
    SET  Name = [@UpdateEmployeeTable].[Name]
        Salary = [@UpdateEmployeeTable].[Salary]    
   FROM Employee INNER JOIN @UpdateEmployeeTable ON Employee.EmpId = [@UpdateEmployeeTable].[EmpId]
       
END
GO

       Here I am passing EmployeeTable type variable @EmployeeTable in the parameter.  Created two EmployeTable variable @InsertEmployeeTable and  @UpdateEmployeeTable. @InsertEmployeeTable is used to stored those records which are     going to be insert and  @UpdateEmployeeTable to update records.

 4. Create a console application and add an app.cofig file and add the connection string.

 5. Add a class Employee.cs.Here I have created the Save() method which takes the collectios of Employee object. This function basically convert the IList collection to the datatable and pass that datable into the parameter of the stored procedure.  Here you have to very careful when converting the Employee collection to the DataTable. The datatable should have the same same as EmployeeTable type in the Sql Server.

     public void Save(IList<Employee>obEmployees)
        {
            SqlConnection connection =new SqlConnection(ConfigurationManager.AppSettings["Emp.Connection"].ToString());
            
            SqlCommand obCommand = new SqlCommand();
            obCommand.Connection = connection;
            obCommand.Connection.Open();
            obCommand.CommandText = "SaveEmployee";
            obCommand.CommandType =System.Data.CommandType.StoredProcedure;
            //Convert collection to data table
            DataTable dtEmployee = GetEmployeeTable(obEmployees);
            obCommand.Parameters.Add("@EmployeeTable", dtEmployee);
            obCommand.ExecuteNonQuery();
            obCommand.Connection.Close();
        }

        private DataTable GetEmployeeTable(IList<Employee> obEmployeeList)
        {
            var dtEmployee = new DataTable("dtEmployee");
            dtEmployee.Columns.Add(new DataColumn("EmpId"));
            dtEmployee.Columns.Add(new DataColumn("Name"));
            dtEmployee.Columns.Add(new DataColumn("Salary"));
            DataRow dataRow;
            //Assing each object to Data Row and add to Datatable
            foreach (var obEmployee in obEmployeeList)
            {
                dataRow = dtEmployee.NewRow();
               
                dtEmployee.Rows.Add(GetEmployeeDataRow(dataRow, obEmployee));
            }
            return dtEmployee;
        }

        private static DataRow GetEmployeeDataRow(DataRow dataRow, Employee obEmployee)
        {
            dataRow["EmpId"] = obEmployee.EmpId;
            dataRow["Name"] = obEmployee.Name;
            dataRow["Salary"] = obEmployee.Salary;
            return dataRow;
        }

  6. Add the following code in the Program.cs file.

     static void Main(string[] args)
        {
            Employee obEmployee = new Employee();
            Console.WriteLine("*********** SAVE EMP *********");
            List<Employee> obEmployees = new List<Employee>();
            obEmployees.Add(new Employee {EmpId = "EMP01", Name = "A", Salary = 6000});
            obEmployees.Add(new Employee { EmpId = "EMP02", Name = "B", Salary = 7000 });
            obEmployees.Add(new Employee { EmpId = "EMP03", Name = "C", Salary = 8000 });
            obEmployee.Save(obEmployees);
            Console.WriteLine("************************");
            Console.ReadLine();
        }

If you run this code first time then application will add those value into the table. And if you run the same code with differenct Name and Salary but same EmpId then it will update all the records.

Download :