如何将DataGridView中新增的行数据插入到数据库中？

Question

我有一个datagridview，在表格中有一些数据。

datagridview允许用户输入新的数据行。

问题是：

我想知道如何将新插入的数据行（无论添加了多少行）保存到数据库中，而不会添加现有数据，从而导致重复。

有人可以帮忙吗？

编辑：我正在使用sql数据库，这是我的datagridview。

图片中显示的数据已经存在于数据库中，那么，如果用户向datagridview中插入新的多行数据，应该放置什么代码？

我的代码如下：

private void button1_Click(object sender, EventArgs e)
        {
            con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = "Data Source=tcp:SHEN-PC,49172\\SQLEXPRESS;Initial Catalog=LSEStock;Integrated Security=True";
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter();

            for (int i = 0; i<dataGridView1.Rows.Count; i++ )
            {

                String insertData = "INSERT INTO CostList(SupplierName, CostPrice, PartsID) VALUES (@SupplierName, @CostPrice, @PartsID)" ;
                SqlCommand cmd = new SqlCommand(insertData, con);
                cmd.Parameters.AddWithValue("@SupplierName", dataGridView1.Rows[i].Cells[0].Value);
                cmd.Parameters.AddWithValue("@CostPrice", dataGridView1.Rows[i].Cells[1].Value);
                cmd.Parameters.AddWithValue("@PartsID", textBox1.Text);
                da.InsertCommand = cmd;
                cmd.ExecuteNonQuery();
            }

            con.Close();

- J.S.

---

你能提供一些更详细的信息吗？你正在添加什么类型的数据，背后的数据库是什么？ - Moop

Answer 1

你是否已经声明了SQLCommandBuilder，DataAdaper和DataTable？例如：

SQLConnection con = (your connection);
SQLDataAdapter = sda;
SQLCommandBuilder = scb;
DataTable = dt;
private void btnEnter_Click(object sender, EventArgs e)


da = new SqlDataAdapter("SELECT * FROM [table] WHERE [columnA]='" + txtData.Text + "' OR [columnB]='" + txtData.Text + "' OR [ColumnC]='" + txtData.Text + "' OR [ColumnD]='" + txtData.Text + "'", con);
            ds = new DataSet();
            dt = new DataTable();
            ds.Clear();
            da.Fill(dt);
            dg.DataSource = dt;

            con.Open();
            con.Close();

private void btnUpdate_Click(object sender, EventArgs e)
    {
        //when button is clicked, the SQL Database gets updated with the data that is plugged into the datagridview.
        scb = new SqlCommandBuilder(da);
        da.Update(dt);

    }

- DHB

Answer 2

这是我的插入、取消和删除语句：

    protected void gv_RowCommand(object sender, GridViewCommandEventArgs e)
        {

            if (e.CommandName == "Insert") //- this is needed to explain that the INSERT command will only work when INSERT is clicked
            {
                gv.DataBind();

                DataTable d = dbcon.GetDataTable("SELECT * FROM CIS.CIS_TRANS ORDER BY ID DESC", "ProjectCISConnectionString");

                string transCode = "", fundCode = "", BSA_CD = "", DP_TYPE = "";

                if (d.Rows.Count > 0)
                {
                    transCode = d.Rows[0]["TRANS_CD"].ToString();
                    fundCode = d.Rows[0]["FUND_CD"].ToString();
                    BSA_CD = d.Rows[0]["BSA_CD"].ToString();
                    DP_TYPE = d.Rows[0]["DP_TYPE"].ToString();

                    if (transCode.Trim().Length > 0)
                    {
                        dbcon.Execute("INSERT INTO CIS.CIS_TRANS (ID,TRANS_CD) VALUES(CIS.S_CIS_TRANS.nextval,'')", "ProjectCISConnectionString");

                        gv.DataBind();
                    }
                }
gv.EditIndex = gv.Rows.Count - 1;

        }
        else if (e.CommandName == "Cancel")
        {
            DataTable d = dbcon.GetDataTable("SELECT * FROM CIS.CIS_TRANS ORDER BY ID DESC", "ProjectCISConnectionString");

            string transCode = "";

            if (d.Rows.Count > 0)
            {
                transCode = d.Rows[0]["TRANS_CD"].ToString();

                if (transCode.Trim().Length == 0)
                {
                    dbcon.Execute(string.Format("DELETE CIS.CIS_TRANS WHERE ID = '{0}'", d.Rows[0]["ID"]), "ProjectCISConnectionString");

                    gv.DataBind();
                }
            }

- LOZ

Answer 3

你用的是什么类型的数据库？这是我针对Oracle数据库的源代码。如果是SQL数据库，请用@代替冒号。

源代码：

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ProjectCISConnectionString %>" ProviderName="<%$ConnectionStrings:ProjectCISConnectionString.ProviderName %>"
            SelectCommand="SELECT * FROM CIS.CIS_TRANS ORDER BY ID ASC" 
            DeleteCommand="DELETE FROM CIS.CIS_TRANS WHERE ID = :ID"
            InsertCommand="INSERT INTO CIS.CIS_TRANS (TRANS_CD,FUND_CD,BSA_CD,DP_TYPE,TRANS_CD_DESC) VALUES (:TRANS_CD,:FUND_CD,:BSA_CD,:DP_TYPE,:TRANS_CD_DESC)"
            UpdateCommand="UPDATE CIS.CIS_TRANS SET TRANS_CD = :TRANS_CD, FUND_CD = :FUND_CD, BSA_CD = :BSA_CD, DP_TYPE = :DP_TYPE, TRANS_CD_DESC =:TRANS_CD_DESC WHERE ID = :ID">

        </asp:SqlDataSource>

你还需要一个查询页面和其他一些东西，请告诉我更多信息。

- LOZ

Answer 4

我做了类似这样的事情。快速简单，看起来运行良好：

cmd1.Connection = this.sqlConnection1;
this.sqlConnection1.Open();

 foreach (GridViewRow row in your_grid.Rows)
        {
            Label id = (Label)row.FindControl("your_control"); //what ever control you are using

            SqlCommand cmd1 = new SqlCommand();

            cmd1.CommandText = "insert into your_table values (@p,@p1,@p2)";

            cmd1.Parameters.Add("@p", SqlDbType.VarChar).Value = your_control.Text;
            cmd1.Parameters.Add("@p1", SqlDbType.VarChar).Value = your_control.Text;
            cmd1.Parameters.Add("@p2", SqlDbType.VarChar).Value = your_control.Text;


            cmd1.CommandType = CommandType.Text;


            cmd1.ExecuteNonQuery();

        }
            this.sqlConnection1.Close();

- briskovich