none
How to insert selected datagridview records into SQL server database table. RRS feed

  • Question

  • I have multiple records on datagriedview in windows form which is coming from a table. I want to select some record from datagriedview and insert selected rows into Table having identical schema as datagridview. It means table has same number of column and same data type as column present in datagridview.

    How to solve problem:

    Sunday, December 31, 2017 5:29 PM

Answers

  • Hi AJret,

    >>According to this approach if we have hundred tables then we have to write 100 case in switch block. I think this will not be good approach.

    Maybe the following will achieve it, which works on my side.

     private void button2_Click(object sender, EventArgs e)
            {
                string tableName = comboBox1.Text;//retrieve it from control.
                List<String> fieldsName = new List<string>();
                List<String> parmsName = new List<string>();
                for (int i = 1; i < dataGridView1.Columns.Count; i++)
                {
                    fieldsName.Add(dataGridView1.Columns[i].Name);
                    parmsName.Add(string.Format("@{0}", dataGridView1.Columns[i].Name));
                }
                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    bool isSelected = Convert.ToBoolean(row.Cells["checkBoxColumn"].Value);
                    if (isSelected)
                    {
                        string constring = ConnUtils.Conn1String;
                        using (SqlConnection con = new SqlConnection(constring))
                        {
                            string sqlString = string.Format("Insert into {0} ({1}) values ({2})", tableName, string.Join(",", fieldsName), string.Join(",", parmsName));
                            List<SqlParameter> pars = new List<SqlParameter>();
                            foreach (var item in parmsName)
                            {
                                SqlParameter sp = new SqlParameter(item, row.Cells[item.Substring(1)].Value);
                                pars.Add(sp);
                            }
                            using (SqlCommand cmd = new SqlCommand(sqlString, con))
                            {
                                cmd.Parameters.AddRange(pars.ToArray());
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }
                        }
                    }
                 }
            }

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by AJret Wednesday, January 3, 2018 9:17 AM
    Wednesday, January 3, 2018 7:23 AM
    Moderator

All replies

  • Hi AJret,

    1. Add a CheckBox Column to the DataGridView at the first position. like this:

    private void button1_Click(object sender, EventArgs e)
    {
                string connstring1 = ConnUtils.Conn1String;
                string connstring2 = ConnUtils.Conn2String;
                string tableName = comboBox1.Text; //"course";
                string tableNam2 = comboBox2.Text;
                DataTable dt1 = ConnUtils.GetTableFromTableName(tableName, connstring1);
                DataTable dt2 = ConnUtils.GetTableFromTableName(tableNam2, connstring2);
                DataTable dt3 = Utils.compareTable(dt1, dt2);
                dataGridView1.DataSource = dt3.DefaultView;
                //Add a CheckBox Column to the DataGridView at the first position.
                DataGridViewCheckBoxColumn checkBoxColumn = new DataGridViewCheckBoxColumn();
                checkBoxColumn.HeaderText = "";
                checkBoxColumn.Width = 30;
                checkBoxColumn.Name = "checkBoxColumn";
                dataGridView1.Columns.Insert(0, checkBoxColumn);
            }

    2. do a loop to insert into database. like this:

     private void button2_Click(object sender, EventArgs e)
            {
                int inserted = 0;
                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    bool isSelected = Convert.ToBoolean(row.Cells["checkBoxColumn"].Value);
                    if (isSelected)
                    {
                        string constring = ConnUtils.Conn2String;
                        using (SqlConnection con = new SqlConnection(constring))
                        {
                            using (SqlCommand cmd = new SqlCommand("INSERT INTO course (Id, CourseName) VALUES(@Id, @CourseName)", con))
                            {
                                cmd.Parameters.AddWithValue("@Id", row.Cells["Id"].Value);
                                cmd.Parameters.AddWithValue("@CourseName", row.Cells["CourseName"].Value);
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }
                        }
                        inserted++;
                    }
                }
    
                if (inserted > 0)
                {
                    MessageBox.Show(string.Format("{0} records inserted.", inserted), "Message");
                }
            }

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 1, 2018 2:04 AM
    Moderator
  • Hi Zhanglong Wu,

    I am able to create check box at first position in datagridview. I think your code will work only when you already know about table name and schema.

    In my problem first i am printing records from selected table from a list of table. Then i want to insert selected record into respective table. 

     cmd.Parameters.AddWithValue("@Id", row.Cells["Id"].Value);
                                cmd.Parameters.AddWithValue("@CourseName", row.Cells["CourseName"].Value);
                               

    Here in your code you know that only "Id" and "CourseName" attribute. In my problem every time attribute in datagridview will be changed according to selected table.

    I think first we should create a dataTable for selected record and then insert datatable into respective table.

    Monday, January 1, 2018 6:53 AM
  • Hi Zhanglong Wu,

    Need some help. Please give me some idea.
    Tuesday, January 2, 2018 5:21 AM
  • Hi AJret,

    According to your description, it seems that you could retrieve table name from control, Then we could write switch condition to use different SQL statement and related parameters. like this:

    private void button2_Click(object sender, EventArgs e)
            {
                string tableName = "";//retrieve it from control.
                List<SqlParameter> pars = new List<SqlParameter>();
                string sqlString = string.Empty;
                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    bool isSelected = Convert.ToBoolean(row.Cells["checkBoxColumn"].Value);
                    if (isSelected)
                    {
                        string constring = ConnUtils.Conn2String;
                        using (SqlConnection con = new SqlConnection(constring))
                        {
                            switch (tableName)
                            {
                                case "course":
                                    sqlString = "INSERT INTO course (Id, CourseName) VALUES(@Id, @CourseName)";
                                    pars.Clear();
                                    SqlParameter p0 = new SqlParameter("@Id", SqlDbType.Int);
                                    p0.Value = row.Cells["Id"].Value;
                                    pars.Add(p0);
                                    // other parameter.
                                    break;
                                case "student":
                                    break;
                                    //others
                            }
                            using (SqlCommand cmd = new SqlCommand(sqlString, con))
                            {
                                //cmd.Parameters.AddWithValue("@Id", row.Cells["Id"].Value);
                                //cmd.Parameters.AddWithValue("@CourseName", row.Cells["CourseName"].Value);
                                cmd.Parameters.AddRange(pars.ToArray());
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }
                        }
                    }
                }

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 2, 2018 5:44 AM
    Moderator
  • Hi Zhanglong Wu,

    I think you are not getting my question. 

    What i have already done : I am able to print different records into datagridview for selected table of two database. After it i want to copy (Transfer) those selected different records into lrespective ive  database table.

    Tuesday, January 2, 2018 5:55 AM
  • Hi AJret,

    >>What i have already done : I am able to print different records into datagridview for selected table of two database. After it i want to copy (Transfer) those selected different records into lrespective ive  database table.

    Do you mean that you want to insert the different records to the third database which have the same schema?

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 2, 2018 6:40 AM
    Moderator
  • Hi Zhanglong Wu,

    >>Do you mean that you want to insert the different records to the third database which have the same schema?

    No third database. You know that we getting those records in datagridview which is present in test database table but not in live database table. So, I want to insert those datagridview record into live database table. Only those records will be inserted into live database table which is checked by user not all records of datagridview.

    Tuesday, January 2, 2018 6:54 AM
  • Hi AJret,

    Please try SqlBulkCopy, which insert datatable records into database, please refer to the following code.

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp
    {
        public partial class Form2 : Form
        {
    
            public DataTable table { get; set; }
            public Form2()
            {
                InitializeComponent();
            }
    
            private void Form2_Load(object sender, EventArgs e)
            {
                
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                string connstring1 = ConnUtils.Conn2String;
                string connstring2 = ConnUtils.Conn1String;
                string tableName = comboBox1.Text; //"course";
                string tableNam2 = comboBox2.Text;
                DataTable dt1 = ConnUtils.GetTableFromTableName(tableName, connstring1);
                DataTable dt2 = ConnUtils.GetTableFromTableName(tableNam2, connstring2);
                table = Utils.compareTable(dt1, dt2);
    
                table.TableName = tableName;
    
                dataGridView1.DataSource = table.DefaultView;
                //Add a CheckBox Column to the DataGridView at the first position.
                DataGridViewCheckBoxColumn checkBoxColumn = new DataGridViewCheckBoxColumn();
                checkBoxColumn.HeaderText = "";
                checkBoxColumn.Width = 30;
                checkBoxColumn.Name = "checkBoxColumn";
                dataGridView1.Columns.Insert(0, checkBoxColumn);
            }
    
            private void button2_Click(object sender, EventArgs e)
            {
                using (var bulkCopy = new SqlBulkCopy(ConnUtils.Conn1String, SqlBulkCopyOptions.KeepIdentity))
                {
                    // my DataTable column names match my SQL Column names, so I simply made this loop. However if your column names don't match, just pass in which datatable name matches the SQL column name in Column Mappings
                    foreach (DataColumn col in table.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    }
                    bulkCopy.BulkCopyTimeout = 600;
                    bulkCopy.DestinationTableName = table.TableName;
                    bulkCopy.WriteToServer(table);
                }
            }
        }
    }
    

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 2, 2018 7:21 AM
    Moderator
  • Hi Zhanglong Wu,

    Is there any other way to insert record by record into live database table rather than inserting all records at a time using SqlBulKCopy.


    • Edited by AJret Tuesday, January 2, 2018 7:44 AM
    Tuesday, January 2, 2018 7:43 AM
  • Hi AJret,

    As i said, use different SQL statement and SqlParameter via switch statement.

    private void button2_Click(object sender, EventArgs e)
            {
                string tableName = "";//retrieve it from control.
                List<SqlParameter> pars = new List<SqlParameter>();
                string sqlString = string.Empty;
                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    bool isSelected = Convert.ToBoolean(row.Cells["checkBoxColumn"].Value);
                    if (isSelected)
                    {
                        string constring = ConnUtils.Conn2String;
                        using (SqlConnection con = new SqlConnection(constring))
                        {
                            switch (tableName)
                            {
                                case "course":
                                    sqlString = "INSERT INTO course (Id, CourseName) VALUES(@Id, @CourseName)";
                                    pars.Clear();
                                    SqlParameter p0 = new SqlParameter("@Id", SqlDbType.Int);
                                    p0.Value = row.Cells["Id"].Value;
                                    pars.Add(p0);
                                    // other parameter.
                                    break;
                                case "student":
    sqlString = "INSERT INTO student(StudentId, StudentName) VALUES(@StudentId, @CourseName)";
                                    pars.Clear();
                                    SqlParameter p0 = new SqlParameter("@StudentId", SqlDbType.Int);
                                    p0.Value = row.Cells["StudentId"].Value;
                                    pars.Add(p0);
                                    // other parameter.
                                    break;
                                    //others
                            }
                            using (SqlCommand cmd = new SqlCommand(sqlString, con))
                            {
                                //cmd.Parameters.AddWithValue("@Id", row.Cells["Id"].Value);
                                //cmd.Parameters.AddWithValue("@CourseName", row.Cells["CourseName"].Value);
                                cmd.Parameters.AddRange(pars.ToArray());
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }
                        }
                    }
                }

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 2, 2018 9:06 AM
    Moderator
  • Hi Zhanglong Wu,

    According to this approach if we have hundred tables then we have to write 100 case in switch block. I think this will not be good approach.


    Tuesday, January 2, 2018 10:03 AM
  • Hi AJret,

    >>According to this approach if we have hundred tables then we have to write 100 case in switch block. I think this will not be good approach.

    Maybe the following will achieve it, which works on my side.

     private void button2_Click(object sender, EventArgs e)
            {
                string tableName = comboBox1.Text;//retrieve it from control.
                List<String> fieldsName = new List<string>();
                List<String> parmsName = new List<string>();
                for (int i = 1; i < dataGridView1.Columns.Count; i++)
                {
                    fieldsName.Add(dataGridView1.Columns[i].Name);
                    parmsName.Add(string.Format("@{0}", dataGridView1.Columns[i].Name));
                }
                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    bool isSelected = Convert.ToBoolean(row.Cells["checkBoxColumn"].Value);
                    if (isSelected)
                    {
                        string constring = ConnUtils.Conn1String;
                        using (SqlConnection con = new SqlConnection(constring))
                        {
                            string sqlString = string.Format("Insert into {0} ({1}) values ({2})", tableName, string.Join(",", fieldsName), string.Join(",", parmsName));
                            List<SqlParameter> pars = new List<SqlParameter>();
                            foreach (var item in parmsName)
                            {
                                SqlParameter sp = new SqlParameter(item, row.Cells[item.Substring(1)].Value);
                                pars.Add(sp);
                            }
                            using (SqlCommand cmd = new SqlCommand(sqlString, con))
                            {
                                cmd.Parameters.AddRange(pars.ToArray());
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }
                        }
                    }
                 }
            }

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by AJret Wednesday, January 3, 2018 9:17 AM
    Wednesday, January 3, 2018 7:23 AM
    Moderator