c# - 从 gridview 插入数据时处理 'NULL' 和键约束

Question

我正在尝试通过 C# Windows 应用程序中的 gridview 将值插入到数据库中。我尝试了两种不同的方法，但似乎都不适合我。 2类代码如下所示......

假设，即使下面的代码有效......我收到关于主键和外键约束的各种错误......

问题:

1. 我有 confactorID 和 macroID 列作为整数，在目标 businesslogic 表中可以为空......我'我不确定如何通过 C# gridview 工具在这些列中插入“NULL”...

2. 即使我将整数值作为输入，也似乎存在外键和主键(重复)约束问题....

我需要在下面的代码中更改什么才能解决这些问题......我已经被这些问题困扰了 8 个多小时......非常感谢任何帮助。

代码类型 1:

    private void ADD_button_Click(object sender, EventArgs e)
    {
        try
        {
            using (SqlConnection con = new SqlConnection(sqlconn))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    con.Open();

                    for (int i = 1; i < dataGridView.Rows.Count; i++)
                    {
                        string sql = @"INSERT INTO " + schemaName +"ERSBusinessLogic VALUES ("
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_ID"].Value + ", '"
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_Formula"].Value.ToString() + "', "
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_InputsCount"].Value + ",'"
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_Inputs"].Value.ToString() + "', "
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_ConvFactorID"].Value + ", "
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_MacroID"].Value + ", '"
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_DataSeries"].Value.ToString() + "', '"
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_InputTimeDimensionValue"].Value.ToString() + "', "
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_InputTimeDimensionType"].Value + ", "
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_GeographyDimensionID"].Value + ", "
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_InputsUnitsIDs"].Value + ", '"
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_Type"].Value + "', "
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_PrivacyID"].Value + ", '"
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_LongDesc"].Value.ToString() + "', '"
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_InputSources"].Value.ToString() + "', '"
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_OutputName"].Value.ToString() + "', "
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_OutputUnitID"].Value + ", '"
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_OutputDestination"].Value.ToString() + "', '"
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_OutputTimeDimensionValue"].Value.ToString() + "', "
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_OutputTimeDimensionType"].Value + ", "
                                        + dataGridView.Rows[i].Cells["ERSBusinessLogic_GroupID"].Value + ");";

                         if ((dataGridView.Rows[i].Cells["ERSBusinessLogic_ConvFactorID"].Value == " ") && (dataGridView.Rows[i].Cells["ERSBusinessLogic_MacroID"].Value == null))
                         {
                             Convert.ToInt32(dataGridView.Rows[i].Cells["ERSBusinessLogic_ConvFactorID"].Value = "NULL");
                             Convert.ToInt32 (dataGridView.Rows[i].Cells["ERSBusinessLogic_MacroID"].Value = "NULL");

                             cmd.CommandText = sql;
                             cmd.ExecuteNonQuery();
                         }
                         else
                         {
                             cmd.CommandText = sql;
                             cmd.ExecuteNonQuery();
                         }
                     }
                 }
             }
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error : " + ex.Message);
        }
        finally
        {
            con.Close();
        }
    }

代码类型 2:

private void ADD_button_Click(object sender, EventArgs e)
{
    // Getting data from DataGridView
    DataTable myDt = new DataTable();
    myDt = GetDTfromDGV(dataGridView);

    // Writing to sql
    WriteToSQL(myDt);
}

private DataTable GetDTfromDGV(DataGridView dgv)
{
    // Making our DataTable
    DataTable dt = new DataTable();

    foreach (DataGridViewColumn column in dgv.Columns)
    {
        dt.Columns.Add(column.Name, typeof(string));
    }

    // Getting data
    foreach (DataGridViewRow dgvRow in dgv.Rows)
    {
        DataRow dr = dt.NewRow();

        for (int col = 0; col < dgv.Columns.Count; col++)
        {
            dr[col] = dgvRow.Cells[col].Value;
        }

        dt.Rows.Add(dr);
    }

    // removing empty rows
    for (int row = dt.Rows.Count - 1; row >= 0; row--)
    {
        bool flag = true;

        for (int col = 0; col < dt.Columns.Count; col++)
        {
            if (dt.Rows[row][col] != DBNull.Value)
            {
                flag = false;
                break;
            }
        }

        if (flag == true)
        {
            dt.Rows.RemoveAt(row);
        }
    }

    return dt;
}

private void WriteToSQL(DataTable dt)
{
    using (SqlConnection con = new SqlConnection(sqlconn))
    {
        SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con);
        // Setting the database table name
        sqlBulkCopy.DestinationTableName = "[AnimalProductsCoSD].[CoSD].[ERSBusinessLogic]";
        // Mapping the DataTable columns with that of the database table
        Convert.ToInt32 (sqlBulkCopy.ColumnMappings.Add(dt.Columns[0].ColumnName, "ERSBusinessLogic_ID"));
       Convert.ToString(sqlBulkCopy.ColumnMappings.Add(dt.Columns[1].ColumnName, "ERSBusinessLogic_Formula"));
       Convert.ToInt32 (sqlBulkCopy.ColumnMappings.Add(dt.Columns[2].ColumnName, "ERSBusinessLogic_InputsCount"));
        Convert.ToString (sqlBulkCopy.ColumnMappings.Add(dt.Columns[3].ColumnName, "ERSBusinessLogic_Inputs"));
        Convert.ToInt32 (sqlBulkCopy.ColumnMappings.Add(dt.Columns[4].ColumnName, "ERSBusinessLogic_ConvFactorID"));
        Convert.ToInt32 (sqlBulkCopy.ColumnMappings.Add(dt.Columns[5].ColumnName, "ERSBusinessLogic_MacroID"));

        Convert.ToString (sqlBulkCopy.ColumnMappings.Add(dt.Columns[6].ColumnName, "ERSBusinessLogic_DataSeries"));
        Convert.ToString (sqlBulkCopy.ColumnMappings.Add(dt.Columns[7].ColumnName, "ERSBusinessLogic_InputTimeDimensionValue"));
        Convert.ToInt32 (sqlBulkCopy.ColumnMappings.Add(dt.Columns[8].ColumnName, "ERSBusinessLogic_InputTimeDimensionType"));
        Convert.ToInt32 (sqlBulkCopy.ColumnMappings.Add(dt.Columns[9].ColumnName, "ERSBusinessLogic_GeographyDimensionID"));
        Convert.ToInt32 (sqlBulkCopy.ColumnMappings.Add(dt.Columns[10].ColumnName, "ERSBusinessLogic_InputsUnitsIDs"));
        Convert.ToString (sqlBulkCopy.ColumnMappings.Add(dt.Columns[11].ColumnName, "ERSBusinessLogic_Type"));

        Convert.ToInt32 (sqlBulkCopy.ColumnMappings.Add(dt.Columns[12].ColumnName, "ERSBusinessLogic_PrivacyID"));
        Convert.ToString (sqlBulkCopy.ColumnMappings.Add(dt.Columns[13].ColumnName, "ERSBusinessLogic_LongDesc"));
        Convert.ToString (sqlBulkCopy.ColumnMappings.Add(dt.Columns[14].ColumnName, "ERSBusinessLogic_InputSources"));
        Convert.ToString (sqlBulkCopy.ColumnMappings.Add(dt.Columns[15].ColumnName, "ERSBusinessLogic_OutputName"));
        Convert.ToInt32 (sqlBulkCopy.ColumnMappings.Add(dt.Columns[16].ColumnName, "ERSBusinessLogic_OutputUnitID"));
        Convert.ToString (sqlBulkCopy.ColumnMappings.Add(dt.Columns[17].ColumnName, "ERSBusinessLogic_OutputDestination"));

        Convert.ToString (sqlBulkCopy.ColumnMappings.Add(dt.Columns[18].ColumnName, "ERSBusinessLogic_OutputTimeDimensionValue"));
        Convert.ToInt32 (sqlBulkCopy.ColumnMappings.Add(dt.Columns[19].ColumnName, "ERSBusinessLogic_OutputTimeDimensionType"));
        Convert.ToInt32 (sqlBulkCopy.ColumnMappings.Add(dt.Columns[20].ColumnName, "ERSBusinessLogic_GroupID"));

        con.Open();
        sqlBulkCopy.WriteToServer(dt);
    }
}

谢谢

Answer 1

首先检查您的数据库表，保留来自其他表的 ID 的列必须允许空值，如下所示:

而且如果你的表ID是自增的Identity列你不需要写ID，表会自动添加ID。

如果一切正常，那么尝试这样做:

private DataTable GetDTfromDGV(DataGridView dgv)
    {
        // Macking our DataTable
        DataTable dt = new DataTable();
        //Another way to add columns
        dt.Columns.AddRange(new DataColumn[5]
            {
                //new DataColumn("table_ID", typeof(string)), if table_ID is not Identity column with auto increment then uncomment
                new DataColumn("sql_col2", typeof(string)),
                new DataColumn("sql_col3", typeof(string)),
                new DataColumn("sql_col4", typeof(string)),
                new DataColumn("Table_2_ID", typeof(int)),
                new DataColumn("Table_3_IDt", typeof(int))
            });
        // Getting data
        foreach (DataGridViewRow dgvRow in dgv.Rows)
        {
            DataRow dr = dt.NewRow();
            for (int col = 1; col < dgv.Columns.Count; col++) //if table_ID is not Identity column with auto increment then start with 0
            {
                dr[col - 1] = dgvRow.Cells[col].Value == null ? DBNull.Value : dgvRow.Cells[col].Value;
            }
            dt.Rows.Add(dr);
        }
        // removing empty rows
        ....
        return dt;
    }
    private void WriteToSQL(DataTable dt)
    {
        string connectionStringSQL = "Your connection string";
        using (SqlConnection sqlConn = new SqlConnection(connectionStringSQL))
        {
            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConn);
            // Setting the database table name
            sqlBulkCopy.DestinationTableName = "Table_1";
            // Mapping the DataTable columns with that of the database table
            //sqlBulkCopy.ColumnMappings.Add(dt.Columns[0].ColumnName, "table_ID"); table_ID is Identity column with auto increment
            sqlBulkCopy.ColumnMappings.Add(dt.Columns[0].ColumnName, "sql_col2");
            sqlBulkCopy.ColumnMappings.Add(dt.Columns[1].ColumnName, "sql_col3");
            sqlBulkCopy.ColumnMappings.Add(dt.Columns[2].ColumnName, "sql_col4");
            sqlBulkCopy.ColumnMappings.Add(dt.Columns[3].ColumnName, "Table_2_ID");
            sqlBulkCopy.ColumnMappings.Add(dt.Columns[4].ColumnName, "Table_3_ID");
            sqlConn.Open();
            sqlBulkCopy.WriteToServer(dt);
        }
    }

我试过了，这就是我得到的: