使用datatable和mysqldataadapter的mysql批量插入中的问题 [英] Problem in mysql bulk insert using datatable and mysqldataadapter

问题描述

嗨我在使用dataadapter和datatable的mysql插件中遇到一个奇怪的问题....我无法弄明白实际问题是什么..



只有一行插入然后错误givien



你的SQL语法有错误;检查与您的MySQL服务器版本对应的手册，以便在附近使用正确的语法; SP_InsertCTC; SP_InsertCTC; SP_InsertCTC; SP_InsertCTC;在第1行

hi i am having a strange problem in mysql insert using dataadapter and datatable.... i am not able to figure it out what is the actual problem ..

Only one row insert and then error givien

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near;SP_InsertCTC; SP_InsertCTC; SP_InsertCTC; SP_InsertCTC; at line 1

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();
    }
}




Table

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
)

Store Procedure

DELIMITER $$

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(e_id ,e_name,emp_ctc);
END $$

DELIMITER ;

推荐答案

CREATE DEFINER =`root` @`localhost `PROCEDURE`SP_InsertCTC`（e_id VARCHAR（ 20 ），e_name VARCHAR（ 50 ），emp_ctc DECIMAL（< span class =code-digit> 10 ， 2 ））
BEGIN

INSERT INTO Employee_CTC（emp_id ，emp_name，CTC）VALUES（e_id，e_name，emp_ctc）;
END

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(e_id ,e_name,emp_ctc); END

DELIMITER;

DELIMITER ;

这篇关于使用datatable和mysqldataadapter的mysql批量插入中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！