在 C# 中将数据从数据表插入数据库时出现问题

Question

我有一个 3 列的网格视图，我在"添加"按钮的帮助下添加空行。现在我正在尝试将网格中输入的数据插入到我的表中。这是我的代码。

SqlConnection con = new SqlConnection(ConfigurationManager
    .ConnectionStrings["gridconnection"]
    .ConnectionString);
public void SetInitialRow()
{
    DataTable dt = new DataTable();
    DataRow dr = null;
    //dt.Columns.Add(new DataColumn("RowNumber", typeof(string)));
    dt.Columns.Add(new DataColumn("Column1", typeof(string)));
    dt.Columns.Add(new DataColumn("Column2", typeof(string)));
    dt.Columns.Add(new DataColumn("Column3", typeof(string)));
    dr = dt.NewRow();
   // dr["RowNumber"] = 1;
    dr["Column1"] = string.Empty;
    dr["Column2"] = string.Empty;
    dr["Column3"] = string.Empty;
    dt.Rows.Add(dr);
    //Store the DataTable in ViewState
    ViewState["CurrentTable"] = dt;
    Gridview1.DataSource = dt;
    Gridview1.DataBind();
}
public void AddNewRowToGrid()
{
    int rowIndex = 0;
    if (ViewState["CurrentTable"] != null)
    {
        DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
        DataRow drCurrentRow = null;
        if (dtCurrentTable.Rows.Count > 0)
        {
            for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
            {
                //extract the TextBox values
                TextBox box1 = (TextBox)Gridview1.Rows[rowIndex]
                    .Cells[1].FindControl("TextBox1");
                TextBox box2 = (TextBox)Gridview1.Rows[rowIndex]
                    .Cells[2].FindControl("TextBox2");
                TextBox box3 = (TextBox)Gridview1.Rows[rowIndex]
                    .Cells[3].FindControl("TextBox3");
                drCurrentRow = dtCurrentTable.NewRow();
                drCurrentRow["RowNumber"] = i + 1;
                dtCurrentTable.Rows[i - 1]["Column1"] = box1.Text;
                dtCurrentTable.Rows[i - 1]["Column2"] = box2.Text;
                dtCurrentTable.Rows[i - 1]["Column3"] = box3.Text;
                rowIndex++;
            }
            dtCurrentTable.Rows.Add(drCurrentRow);
            ViewState["CurrentTable"] = dtCurrentTable;
            Gridview1.DataSource = dtCurrentTable;
            Gridview1.DataBind();
        }
    }
    else
    {
        Response.Write("ViewState is null");
    }
    //Set Previous Data on Postbacks
    SetPreviousData();
}
protected void SetPreviousData()
{
    int rowIndex = 0;
    if (ViewState["CurrentTable"] != null)
    {
        DataTable dt = (DataTable)ViewState["CurrentTable"];
        if (dt.Rows.Count > 0)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                TextBox box1 = (TextBox)Gridview1.Rows[rowIndex]
                     .Cells[1].FindControl("TextBox1");
                TextBox box2 = (TextBox)Gridview1.Rows[rowIndex]
                     .Cells[2].FindControl("TextBox2");
                TextBox box3 = (TextBox)Gridview1.Rows[rowIndex]
                     .Cells[3].FindControl("TextBox3");
                box1.Text = dt.Rows[i]["Column1"].ToString();
                box2.Text = dt.Rows[i]["Column2"].ToString();
                box3.Text = dt.Rows[i]["Column3"].ToString();
                rowIndex++;
            }
        }
    }
}
protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        SetInitialRow();
    }
}
protected void ButtonAdd_Click(object sender, EventArgs e)
{
    AddNewRowToGrid();
}
protected void ButtonAdd_Click1(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    DataRow dr = null;
    dt.Columns.Add(new DataColumn("Column1", typeof(string)));
    dt.Columns.Add(new DataColumn("Column2", typeof(string)));
    dt.Columns.Add(new DataColumn("Column3", typeof(string)));
    dr = dt.NewRow();
    string consString = ConfigurationManager.ConnectionStrings["gridconnection"]
        .ConnectionString;
    using (SqlConnection connection = new SqlConnection(consString))
    {
        connection.Open();
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
        {
            //foreach (DataColumn c in dt.Columns)
            //   // bulkCopy.ColumnMappings.Add(c.Column1, c.Column2, c.Column3);
            bulkCopy.DestinationTableName = "GridExcel";
            //bulkCopy.ColumnMappings.Add(Column1, c.Column2, c.Column3);
            SqlBulkCopyColumnMapping Column = 
                new SqlBulkCopyColumnMapping("Column1", "Column1");
            bulkCopy.ColumnMappings.Add(Column);
            SqlBulkCopyColumnMapping Column2nd = 
               new SqlBulkCopyColumnMapping("Column2", "Column2");
            bulkCopy.ColumnMappings.Add(Column2nd);
            SqlBulkCopyColumnMapping Column3rd = 
               new SqlBulkCopyColumnMapping("Column3", "Column3");
            bulkCopy.ColumnMappings.Add(Column3rd);
            try
            {
                bulkCopy.WriteToServer(dt);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

GridExcel 是我的数据库表名称，列名称为 Column1、Column2、Column3。问题是数据没有插入到表中。请帮忙。

Answer 1

protected void ButtonAdd_Click1(object sender, EventArgs e)
 {
    if (ViewState["CurrentTable"] != null)
        {
        DataTable dt = (DataTable)ViewState["CurrentTable"];       
        if (dt.Rows.Count > 0)
        {
            string consString = ConfigurationManager.ConnectionStrings["gridconnection"]
              .ConnectionString;
            using (SqlConnection connection = new SqlConnection(consString))
            {
                connection.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                {
                    //foreach (DataColumn c in dt.Columns)
                    //   // bulkCopy.ColumnMappings.Add(c.Column1, c.Column2, c.Column3);
                    bulkCopy.DestinationTableName = "GridExcel";
                    //bulkCopy.ColumnMappings.Add(Column1, c.Column2, c.Column3);
                    SqlBulkCopyColumnMapping Column =
                        new SqlBulkCopyColumnMapping("Column1", "Column1");
                    bulkCopy.ColumnMappings.Add(Column);
                    SqlBulkCopyColumnMapping Column2nd =
                       new SqlBulkCopyColumnMapping("Column2", "Column2");
                    bulkCopy.ColumnMappings.Add(Column2nd);
                    SqlBulkCopyColumnMapping Column3rd =
                       new SqlBulkCopyColumnMapping("Column3", "Column3");
                    bulkCopy.ColumnMappings.Add(Column3rd);
                    try
                    {
                        bulkCopy.WriteToServer(dt);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                }
            }
        }
    }
 }

我不熟悉 ASP.Net 但更新了您的ButtonAdd_Click1，这可能会解决您的问题，这工作正常并解决您的问题请检查此