none
How to access a databound DataGridView's DataSource? RRS feed

  • Question

  • I am building a data-driven WinForms application in C# using a PostgreSQL database. Currently I am populating my two DataGridViews as follows.

    I have a class in which I plan on doing all of my database-related work - get data from the database, add update and delete data in the database tables, etc. That class currently looks like this:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Data;
    using Npgsql;
    
    namespace Test_XRef_Tool.Xref_Test
    {
        public class Db
        {
            private string connString = String.Format("Server = localhost; Port = 5432; Database = dvdrental; User Id = userid; Password = password;");
    
            // Used to retrieve data for population of controls (DGVs, CBOs, etc)
            public DataTable GetData(string selectQuery)
            {
                NpgsqlConnection conn = new NpgsqlConnection(connString);
                DataSet ds = new DataSet();
    
                try
                {
                    conn.Open();
                    NpgsqlDataAdapter da = new NpgsqlDataAdapter(selectQuery, conn);
                    conn.Close();
    
                    da.Fill(ds);
                    return ds.Tables[0];
                }
            }
    
        }
    }

    And on my Form1 class I'm populating two DGVs. The code to populate them looks like this:

    Db categoriesData = new Db();
    dgvCategories.DataSource = categoriesData.GetData("SELECT * FROM actor");
    
    Db defaultsData = new Db();
    dgvDefaults.DataSource = defaultsData.GetData("SELECT * FROM film");
    This all works fine to populate the DGVs, but I realized that during user-editing of the DGVs and various other operations I plan on doing in the program, I need access to the DGV's datasource - prior to any changes made to the DGV cells.

    How do I do that? My thought is that I need to create some sort of object or variable on the Form1 class that will hold the current state of the DGV's data, but I can't figure out how to do that - if that's even the right way to do this.

    Tuesday, March 31, 2020 1:29 AM

Answers

  • Hi Mark,

    You could handle this two different ways. 

    One is to simply cast the grid's DataSource to a DataTable. You can do that one of two ways:

    DataTable dtCategories = (DataTable)dgvCategories.DataSource;
    
    // or
    // --- preferred syntax ---
    DataTable dtCategories = dgvCategories.DataSource as DataTable;
    // be sure to check that dtCategories is not null
    

    Now, why is that second option preferable? Because if the grid's DataSource is not a DataTable, the first option will cause an exception.

    The second option won't blow up if the grid's DataSource is not a DataTable, it will just return a null (hence the reason to be check for null)


    Or, you could handle it with global DataTable variables on your Form, that you'll be able to access anywhere on your Form. So, something like this:

    // declare the global variables
    private DataTable dtCategories;
    private DataTable dtDefaults;
    
    // Then, when databinding the grids:
    Db categoriesData = new Db();
    this.dtCategories = categoriesData.GetData("SELECT * FROM actor");
    dgvCategories.DataSource = this.dtCategories;
    
    Db defaultsData = new Db();
    this.dtDefaults = defaultsData.GetData("SELECT * FROM film");
    dgvDefaults.DataSource = this.dtDefaults;

    Hope that helps!  =0)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, March 31, 2020 4:59 AM
    Moderator

All replies

  • High level

    • The DataAdapter needs to live in an instance of your data class as a public variable so you can make use of the Update method among other things.
    • Never explicitly call AcceptChanges on any DataTable containers, this is the responsibility of the Update method.
    • Same for AcceptChangesDuringFill
    • If you need to access the current row of the DataGridView, using a BindingSource where the Current property can be cast to a DataRow and access column in the row via DataRow.Field.

    Comments on current code

    • Never use SELECT *, use SELECT (list of columns) FROM some table - see also.
    • Use "using" statements for connection objects.
    • Never use string concatenation with SQL.

    That's it high level. No code samples as you did not acknowledge my code sample link or for that matter my reply in your last post so not going to write code and have the code ignored.


    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

    Tuesday, March 31, 2020 2:06 AM
    Moderator
  • Hi Mark,

    You could handle this two different ways. 

    One is to simply cast the grid's DataSource to a DataTable. You can do that one of two ways:

    DataTable dtCategories = (DataTable)dgvCategories.DataSource;
    
    // or
    // --- preferred syntax ---
    DataTable dtCategories = dgvCategories.DataSource as DataTable;
    // be sure to check that dtCategories is not null
    

    Now, why is that second option preferable? Because if the grid's DataSource is not a DataTable, the first option will cause an exception.

    The second option won't blow up if the grid's DataSource is not a DataTable, it will just return a null (hence the reason to be check for null)


    Or, you could handle it with global DataTable variables on your Form, that you'll be able to access anywhere on your Form. So, something like this:

    // declare the global variables
    private DataTable dtCategories;
    private DataTable dtDefaults;
    
    // Then, when databinding the grids:
    Db categoriesData = new Db();
    this.dtCategories = categoriesData.GetData("SELECT * FROM actor");
    dgvCategories.DataSource = this.dtCategories;
    
    Db defaultsData = new Db();
    this.dtDefaults = defaultsData.GetData("SELECT * FROM film");
    dgvDefaults.DataSource = this.dtDefaults;

    Hope that helps!  =0)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, March 31, 2020 4:59 AM
    Moderator
  • Hi Mark Yorkovich,
    As Kareninstructor said, you can use DataAdapter.Update() method to update the data source.
    And SqlCommandBuilder is used to automatically generate add, delete, and modify statements.
    Here is a simple code example you can refer to.

    SqlDataAdapter adapter = null;
    DataSet dSet = null;
    string strConn ="connectString";
    private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
    {
        SqlCommandBuilder scb = new SqlCommandBuilder(adapter);
        try
        {
            adapter.Update(dSet);
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
    private void Form1_Load(object sender, EventArgs e)
    {
        adapter = new SqlDataAdapter("select * from t", strConn);
        dSet = new DataSet();
        adapter.Fill(dSet);
        dataGridView1.DataSource = dSet.Tables[0];
    }

    Best Regards,
    Daniel Zhang


    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, March 31, 2020 8:12 AM
  • Thanks for the tips, Karen.

    Regarding your comments: "SELECT * FROM" is currently just a "placeholder" of sorts while I get things rolling. "SQL Injection!" Thanks for the reminder! :) I'm just refreshing my memory on this stuff!

    Tuesday, March 31, 2020 12:03 PM