Handling 'NULL' and key constraints when inserting data from a gridview

Question

I'm trying to insert values into the database via gridview from a C# Windows application. I tried 2 different methods but neither seems to be working for me. The 2 type of code is shown below......

Assuming, even if the code below works.... I'm getting various errors regarding the primary key and foreign key constraints.......

Problem:

I have confactorID and macroID columns as integer with nullable in destination businesslogic table....... I'm not sure how to insert 'NULL' in these columns from the C# gridview tool...
Even if I give integer values as input there seems to be foreign key and primary key (duplication) constraint issues....

What do I need to change in my below code to resolve these issues.... I've been stuck with these problem for more than 8 hours... Any help is much appreciated.

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

Code type 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);
    }
}

Thanks

score 3 · Accepted Answer · answered Mar 30 '16 at 11:47

First of all check your database table, columns that keeps IDs from another tables must allow null value like so:

And if your table ID is Identity column with auto increment you don't need to write ID, table automatically add ID.

If everything ok then try to do like so:

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

I tried and that's what I got:

score 0 · Answer 2 · answered Mar 29 '16 at 18:58

You could use parameterized query. For example:

            var sqlcommand = new SqlCommand
            {
                CommandText = "INSERT INTO TABLE(Column1,Column2) VALUES(@Column1Value,@Column2Value)"
            };
            var param1 = new SqlParameter("@Column1Value", SqlDbType.Int)
            {
                Value = (String.IsNullOrWhiteSpace(dataGridView.Rows[i].Cells["ERSBusinessLogic_ConvFactorID"].Value)) ? DBNull.Value: (object)(dataGridView.Rows[i].Cells["ERSBusinessLogic_ConvFactorID"].Value) 
            };
            var param2 = new SqlParameter("@Column2Value", SqlDbType.Int)
            {
                Value = (String.IsNullOrWhiteSpace(dataGridView.Rows[i].Cells["ERSBusinessLogic_MacroID"].Value)) ?  DBNull.Value : (object)dataGridView.Rows[i].Cells["ERSBusinessLogic_MacroID"].Value) 
            };
            sqlcommand.Parameters.Add(param1);
            sqlcommand.Parameters.Add(param2);

score 0 · Answer 3 · edited May 23 '17 at 12:25

If you use method 1 that you tried, you'll probably want to create SqlParameter objects and parameterize your query. Refer to this SO post: Right syntax of SqlParameter. That being said, you just want to get it to work first I'm sure. You could check the value of the DataGridViewCell's Value property for the convFactorID and macroID. If either of these are null, then you can assign a string the text "NULL". For brevity, I've used the C# conditional operator (https://msdn.microsoft.com/en-us/library/ty67wk28.aspx). This is one way you might do what I'm describing:

string convFactorIDText = (dataGridView.Rows[i].Cells["ERSBusinessLogic_ConvFactorID"].Value == null) ? "NULL" : dataGridView.Rows[i].Cells["ERSBusinessLogic_ConvFactorID"].Value.ToString();
string macroIDText = (dataGridView.Rows[i].Cells["ERSBusinessLogic_MacroID"].Value == null) ? "NULL" : dataGridView.Rows[i].Cells["ERSBusinessLogic_MacroID"].Value.ToString();

Then alter you SQL to include the string variables that contain either a actual value or NULL.

string sql = string.Format(@"INSERT INTO {0}.ERSBusinessLogic VALUES ({1}, '{2}', {3}, {4}, {5}, {6}"), 
    schemaName,
    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,
    convFactorIDText,
    macroIDText
    // and so forth
);

Handling 'NULL' and key constraints when inserting data from a gridview

3 Answers3