none
Visual Studio 2017 RRS feed

  • Question

  • i want to add all the data grid view records to a sql server database table from a button click.

    there are 4 columns in the data grid view.

    i have

    class Database
        {
            private SqlConnection con;
            private SqlCommand cmd;
            private SqlDataAdapter da;
            private SqlDataReader dr;
    
            public Database()
            {
                con = new SqlConnection("Data Source=DESKTOP-GSJITB6\\MICROSOFT;Initial Catalog=NIBM;Integrated Security=True");
            }
    
            public void openConnection()
            {
                con.Open();
            }
            public void closeConnection()
            {
                con.Close();
            }
            public int save_update_delete(string a)
            {
                openConnection();
                cmd = new SqlCommand(a, con);
                int i = cmd.ExecuteNonQuery();
                closeConnection();
                return i;
            }
    }

    used a separate class for the database connection. 

    i look forward to do the process by a sql injection.


    Thursday, May 23, 2019 5:46 PM

Answers

  • Hi Thushan,

    Thank you for posting here.

    According to your description, you want to add all the data grid view records to a sql server database table from a button click.

    You could try the following code.

    private void Button1_Click(object sender, EventArgs e)
            {
                Database database = new Database();
                //database.openConnection();
                DataTable table = (DataTable)this.dataGridView1.DataSource;
                foreach (DataRow item in table.Rows)
                {
                    string sql = string.Format("insert into Student(Name,StuID) values('{0}','{1}')",item["Name"].ToString(), item["StuID"].ToString());
                    database.save_update_delete(sql);
                }
                MessageBox.Show("success");
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                DataTable table = new DataTable();
                table.Columns.Add("Name", typeof(string));
                table.Columns.Add("StuID", typeof(string));
                table.Rows.Add("test1", "1001");
                table.Rows.Add("test2", "1002");
                this.dataGridView1.DataSource = table;
    
            }

    Result:

    Best Regards,

    Jack


    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.

    Friday, May 24, 2019 5:58 AM
    Moderator

All replies

  • Hello,

    My recommendation is to read the following documentation.

    Key information for the inserts is in paragraph two. The UpDate method is how data is saved back to the database table.

    The SqlDataAdapter for this to work is declared at form level using private rather than public.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, May 23, 2019 5:53 PM
    Moderator
  • Hi Thushan,

    Thank you for posting here.

    According to your description, you want to add all the data grid view records to a sql server database table from a button click.

    You could try the following code.

    private void Button1_Click(object sender, EventArgs e)
            {
                Database database = new Database();
                //database.openConnection();
                DataTable table = (DataTable)this.dataGridView1.DataSource;
                foreach (DataRow item in table.Rows)
                {
                    string sql = string.Format("insert into Student(Name,StuID) values('{0}','{1}')",item["Name"].ToString(), item["StuID"].ToString());
                    database.save_update_delete(sql);
                }
                MessageBox.Show("success");
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                DataTable table = new DataTable();
                table.Columns.Add("Name", typeof(string));
                table.Columns.Add("StuID", typeof(string));
                table.Rows.Add("test1", "1001");
                table.Rows.Add("test2", "1002");
                this.dataGridView1.DataSource = table;
    
            }

    Result:

    Best Regards,

    Jack


    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.

    Friday, May 24, 2019 5:58 AM
    Moderator