Bulk Inserting data into MySQL From C#.net

Hey Friends,

I think you have used bulk insert into MS SQL database, Here is an example for Bulk inserting data into MySQL database

First you need to add MySql.Data.dll which can be easily downloaded from http://dev.mysql.com/downloads/connector/net/1.0.html, This library is .net connector for MySQL.

Source code, table structure and the stored procedure are added follows..

Source Code:

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(newConnectionUtils().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);
        con.Close();
    }
}

Table Structure :

CREATE TABLE `employee_ctc` (
        `emp_id` VARCHAR(20) NULL DEFAULT NULL,
        `emp_name` VARCHAR(50) NULL DEFAULT NULL,
        `CTC` DECIMAL(10,2) NULL DEFAULT NULL
)

Stored Procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_InsertCTC`(e_id VARCHAR(20),e_name VARCHAR(50),emp_ctc DECIMAL(10,2))
BEGIN
        INSERT INTO Employee_CTC(emp_id,emp_name,CTC) VALUES(emp_id,emp_name,emp_CTC);
    END$$
Advertisements

10 thoughts on “Bulk Inserting data into MySQL From C#.net

    1. You can write query in MySqlCommand cmd =new MySqlCommand(“you query”, con);
      then put your parameters as with a preceding “?”

      I didn’t tried it any ways check it out

  1. Interesting post. According to your code, if 100 rows of records need to insert into the table, the method will call the Stored Procedure 100 times? Will be any issues on performance?

    From your code, the following line:
    INSERT INTO Employee_CTC(emp_id,emp_name,CTC) VALUES(emp_id,emp_name,emp_CTC);

    Parameters’ name same as table column name?

    Thanks.

  2. Getting error when i am inserting string that contains speacial chaerecters. is there any way to overcome such error??? please help

  3. its very slow method ..
    Mayby 20-times faster method will be building sql multi insert text command like:
    INSERT into tab(a,b,c) values(1,2,3), (4,5,6), (7,8,9), … for examle 10000 rows
    (buffer size for this command can be enlarged)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s