Populate a SQL Database with resulting changes of Windows Forms DataGridView

Hello I have read several articles but am not getting the answer I seek. I wish to do a two fold process. 1. Populate a datagridview from a query NOT a table directly with a button that executes a query to populate. 2. Change the values on the datagrid to speak BACK to the database to make the changes. The problem I am encountering is example I find assume you are having a STATIC universe NOT one that may change. I already knew the population portion but I am curious how you use the datagridview to make a connection to a database and say: "Do what I show at this point in time as changes and save them to the database they origi开发者_开发技巧nally came from."

Code I was following slightly: http://www.switchonthecode.com/tutorials/csharp-tutorial-binding-a-datagridview-to-a-database They used an Access method and I am using SQL but it should not matter much.

EG: I can set a dataAdapter element in one process instance but the other instance is NOT aware of it. I assume something can be done as you can do these quite easily with drag and drop with datasets. Maybe I am going about it wrong as well and should be setting some elements static and some dynamic. All I really want is to prove I could change the values under VALUE to something different and commit that back to the database with a binding source. However the binding source is unknown in another instance and setting some things up statically have met with failure they way I defined them. Any help is much appreciated, thanks!

PRE SET UP:

1. Create a 'Test' Database with a single table defined as:

   Create table Test ( ID int Identity, VALUE varchar(2) ) insert into Test values ('A'),('B'),('C')

2. Create a Windows Form Application in Visual Studio 2010(should work in 2008 or earlier as I don't believe I am doing anything explicitly .NET 4.0). Windows Form should have two buttons: a. 'btnPopulate' b. 'btnUpdate'; a Text Box: 'txtquery' filled with text: 'select * from Test'; and a datagridview: 'gridview'.

Actual code used behind form:

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;

namespace DataGridTestDBUpdater
{
    public partial class TestDataGrid : Form
    {
        public string con = "Integrated Security=SSPI;Persist Security Info=False;Data Source =(local);Initial Catalog=Test";

        public TestDataGrid()
        {
            InitializeComponent();
        }

        private void btnPopulate_Click(object sender, EventArgs e)
        {
            string query = txtquery.Text;

            using (SqlConnection cn = new SqlConnection(con))
            {
                using (SqlCommand cmd = new SqlCommand(query, cn))
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter())
                    {
                        using (DataTable table = new DataTable())
                        {
                            cn.Open();

                            using (BindingSource bs = new BindingSource())
                            {
                                adapter.SelectCommand = cmd;

                                adapter.Fill(table);

                                bs.DataSource = table;

                                gridview.DataSource = bs.DataSource;
                            }

                            cn.Close();
                        }
                    }
                }
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            using (BindingSource bs = new BindingSource())
            {
                bs.DataSource = gridview.DataSource;

                using (DataTable table = new DataTable())
                {
                    using(SqlDataAdapter adpt = new SqlDataAdapter())
                    {
                        bs.DataSource = gridview.DataSource;

                        /// ALL OF MY WORK IS FAILING FROM HERE ON ///
                        /// WANT TO GET UPDATE TO WORK HERE: ///
                        /// 

                        ///adpt.Update(bs.DataSource);

                    }
                }
            }

        }
    }
}

I may have misunderstood your question. Feel free to correct, if that is the case. From what I gather, you want a very persistent connection for your application to your database. That is, if something is changed in one instance of your application the other instance should automatically be aware of the change and refresh its presentation of the underlying data.

If my understanding of your question is correct, unfortunately, there is no straight forward solution to it. What you show in your grid in one instance of your application is an IN MEMORY representation of (in your case a DataTable object) the underlying data that was populated with the database values AT THE TIME of invoking "btnPopulate_Click" method. Any changes in database after that is NOT transparent to your instance until the underlying datatable is refreshed again.

I figured it out, you just have to set private instances of the SqldataAdapter and BindingSource. Then one instance will know about the other. Plus, I should NOT have been using the (using) reference. In most cases it is a wise choice as it implements a dispose method but in this instance I need another method to see the instance I declared in population. Answer I did(This WILL NOT WORK with multiple tables or tables where you do not define a primary key).

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;

namespace TestEntryForm
{
    public partial class Form1 : Form
    {
        private BindingSource bs = new BindingSource();
        private SqlDataAdapter da = new SqlDataAdapter();
        private string cnc = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=localhost";

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            datadridview.DataSource = bs;
        }

        private void SelectCommand(string selectcmd)
        {
            da = new SqlDataAdapter(selectcmd, cnc);

            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(da);

            DataTable table = new DataTable();

            table.Locale = System.Globalization.CultureInfo.InvariantCulture;
            da.Fill(table);
            bs.DataSource = table;

            datadridview.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);

        }

        private void btnLoad_Click(object sender, EventArgs e)
        {
            SelectCommand(txtQuery.Text);
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            da.Update((DataTable)bs.DataSource);
        }
    }
}