using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace Feb02DataBase_DataTable
{
    public partial class Form1 : Form
    {       
        public Form1()
        {
            InitializeComponent();
            PopulatingDGV();
        }

        private void PopulatingDGV()
        {
            dataGridView1.AllowUserToAddRows = true;
            dataGridView1.RowHeadersVisible = false;

            BindingSource bs = new BindingSource();
            bs.DataSource = DAL.GetDataFromDB();
            dataGridView1.DataSource = bs;
            dataGridView1.AutoResizeColumns();
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
        }

        private void buttonSaveDB_Click(object sender, EventArgs e)
        {
            BindingSource bs = (BindingSource)dataGridView1.DataSource;
            DataTable dt1 = (DataTable)bs.DataSource;
            DAL.UpdateDataInDB(dt1);
        }
    }

    public class DAL
    {
        private static string connString = ConfigurationManager.ConnectionStrings["Feb01TestConnectionString"].ConnectionString;

        public static DataTable GetDataFromDB()
        {
            DataTable table = new DataTable("customers");
            using (SqlConnection sqlConn = new SqlConnection(connString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "select_GetCustomers";//SELECT * FROM Customers
                    cmd.CommandType = CommandType.StoredProcedure;  //if you use CommandType.Text;
                    cmd.Connection = sqlConn;
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        da.Fill(table);
                }
            }
            return table;
        }

        public static void UpdateDataInDB(DataTable table)
        {
            using (SqlConnection sqlConn = new SqlConnection(connString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "select_GetCustomers";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = sqlConn;
                    sqlConn.Open();
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        using (SqlCommandBuilder cb = new SqlCommandBuilder(da))
                        {
                            da.UpdateCommand = cb.GetUpdateCommand();
                            da.Update(table);
                        }
                    }
                }
            }
        }
    }
}

Hope it helps,
Mitja
4
Contributors
3
Replies
4
Views
6 Years
Discussion Span
Last Post by tomason
-1

make use of insert query .... insert into tablename values(1,2,3) ..... pass the value of datagridview column in the query and use da.update(dataset,tablename)

0

if using cmd.CommandText = "select_GetCustomers" in method UpdateDataInDB,
at the line below,
da.UpdateCommand = cb.GetUpdateCommand();
you will get In InvalieOperationException and get message below,
"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."

So codes in UpdateDataInDB won't work! Can you give further help?

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.