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$$