locked
how to add datatable record into mysql??? RRS feed

  • Question

  • User2014201474 posted

    I have an datatable, which are contain lots of record, then i want to inserr into mySql database

    I want insert all in once time? got any solutions can do that??

    same like sqlBulkCopy

    Thursday, July 7, 2011 11:54 PM

Answers

  • User1992938117 posted

    I want insert all in once time? got any solutions can do that??

    See:

        public void BulkCopyCTC(List<EmployeeDet> list)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("employee_id",typeof( System.String)));
            dt.Columns.Add(new DataColumn("employee_name", typeof(System.String)));
            dt.Columns.Add(new DataColumn("emp_ctc",typeof( System.Decimal)));
    
            foreach (EmployeeDet item in list)
            {
                DataRow dr = dt.NewRow();
                dr["employee_id"] = item.GetID();
                dr["employee_name"] = item.GetName();
                dr["emp_ctc"] = item.GetCTC();
                dt.Rows.Add(dr);
            }
           
            MySqlConnection con = new MySqlConnection(new ConnectionUtils().GetConnectionString());
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
            con.Open();
            MySqlCommand cmd = new MySqlCommand("SP_InsertCTC", con);
            cmd.CommandType = CommandType.StoredProcedure;
    
            cmd.UpdatedRowSource = UpdateRowSource.None;
          
    
            cmd.Parameters.Add("?e_id", MySqlDbType.String).SourceColumn= "employee_id";
            cmd.Parameters.Add("?e_name", MySqlDbType.String).SourceColumn=  "employee_name";
            cmd.Parameters.Add("?emp_ctc", MySqlDbType.Decimal).SourceColumn=  "emp_ctc";
          
            MySqlDataAdapter da = new MySqlDataAdapter();
            da.InsertCommand = cmd;
            da.UpdateBatchSize = 100;
            int records = da.Update(dt);
            Response.Write("<script>alert('inserted " +  records +" Rows')</script>");
            con.Close();
        }
    }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 8, 2011 1:17 AM