Performing a bulk insert into db from my web forms asp.net c#RSS

• • Reply
  Alamdreal1

  Member

  78 Points

  225 Posts

  Performing a bulk insert into db from my web forms asp.net c#

  Sep 29, 2017 11:59 AM|Alamdreal1|LINK

  Hello,
  I have been trying to insert new bulk queries into a table from a button click but have not been able to get through.
  
  Here is my Question
  I have three tables
  - user details
  -pay structure
  - paySalary
  
  What I intend to do is dat when I click a button from my aspx page it should select all the users and insert into the salary table the salary table their salary.
  
  Thanks for the help
• • Reply
  Rajneesh Ver...

  All-Star

  28988 Points

  7250 Posts

  Re: Performing a bulk insert into db from my web forms asp.net c#

  Sep 29, 2017 03:09 PM|Rajneesh Verma|LINK

  If you really need to get data from one table and place to other then why can't you create a stored procedure with Insert and Select, sample query as below:

  INSERT INTO
  dbo.PersonLog(PersonName, Address, Mobile, FK_PersonId)
  SELECT
  Person.PersonId, Person.PersonName, Person.Address, Person.Mobile
  FROM
  dbo.Person
  WHERE dbo.Person.PersonID = @PersionID

  Rajneesh Verma's Blog
  www.rajneeshverma.com
  
  
• • Reply
  Cathy Zou

  Star

  8650 Points

  2882 Posts

  Re: Performing a bulk insert into db from my web forms asp.net c#

  Oct 02, 2017 07:06 AM|Cathy Zou|LINK

  Hi  Alamdreal1,

  From your description, you want  insert multiple record to database at same time.

  If that the case, you could use the following code:

  protected void Bulk_Insert(object sender, EventArgs e)
  {
      DataTable dt = new DataTable();
      dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                          new DataColumn("Name", typeof(string)),
                          new DataColumn("Country",typeof(string)) });
      foreach (GridViewRow row in GridView1.Rows)
      {
          if ((row.FindControl("CheckBox1") as CheckBox).Checked)
          {
              int id = int.Parse(row.Cells[1].Text);
              string name = row.Cells[2].Text;
              string country = row.Cells[3].Text;
              dt.Rows.Add(id, name, country);
          }
      }
      if (dt.Rows.Count > 0)
      {
          string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
          using (SqlConnection con = new SqlConnection(consString))
          {
              using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
              {
                  //Set the database table name
                  sqlBulkCopy.DestinationTableName = "dbo.Customers";
   
                  //[OPTIONAL]: Map the DataTable columns with that of the database table
                  sqlBulkCopy.ColumnMappings.Add("Id", "CustomerId");
                  sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                  sqlBulkCopy.ColumnMappings.Add("Country", "Country");
                  con.Open();
                  sqlBulkCopy.WriteToServer(dt);
                  con.Close();
              }
          }
      }
  }

  Related sample in the following links:

  https://www.aspsnippets.com/Articles/SqlBulkCopy-Bulk-Copy-data-from-DataTable-DataSet-to-SQL-Server-Table-using-C-and-VBNet.aspx

  https://www.aspsnippets.com/Articles/SqlBulkCopy--Bulk-Insert-records-and-Update-existing-rows-if-record-exists-using-C-and-VBNet.aspx

  Best regards

  Cathy

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.