none
How to create a DataTable class for C# WinForms program RRS feed

  • Question

  • I'm a database programmer and I'm trying to improve my C# knowledge by creating a WinForms program that uses data from a PostgreSQL database.

    So far I've got two DataGridViews populated from two different PostgreSQL database tables. I am able to add, edit and delete records in the DGVs.

    The problem is, everything is in my Form1.cs code. I know that's not a best-practice setup - that I need to create a DataTable class that the Form code can use to read from and modify the database tables.

    I've been doing some searching and found some tutorials for creating classes, etc, but I'm just not well-versed in C# enough yet to apply those examples to my particular needs for this program.

    Here's an example of how I'm currently working with the DataSet and DataTable to connect a DGV to a PostgreSQL database:

    The code that connects to the database:

    public static string connString = String.Format("Server = <serverIPaddress>; Port = <port#>; Database = <dbName>; User Id = <userID>; Password = <password>;");
    NpgsqlConnection conn = new NpgsqlConnection(connString);

    The code that gets the data and build that DataTable and DataSet:

    public DataTable GetData(string selectSql)
    {
    	DataSet ds = new DataSet();
    
    	try
    	{
    		conn.Open();
    		NpgsqlDataAdapter da = new NpgsqlDataAdapter(selectSql, conn);
    		conn.Close();
    
    		da.Fill(ds);
    		return ds.Tables[0];
    	}
    }

    Code to populate a DGV:

    public void PopulateDgvCategories()
    {
    	string selectedComp = cboSelectCompany.Text;
    	dgvCategories.DataSource = GetData("SELECT id, category, old_value, old_desc, new_value, new_desc, reference1, reference2 " +
    										"FROM masterfiles.xref WHERE company_name = '" + selectedComp + "' ORDER BY category, old_value");
    
    	dgvCategories.Columns[0].Visible = false;
    	dgvCategories.Rows[0].Cells[0].Selected = false;
    
    }

    I know that this should all be in its own class so the rest of the program can use it.

    I created a CategoriesDataTable class and I have this:

    using System;
    using System.Data;
    
    namespace Cross_Reference_Tool
    {
    	public class CategoriesDataTable
    	{
    
    	}
    }

    Not much there! How do I move the existing code into that class and what needs to be modified to use the class properly?

    I would appreciate it someone could provide some skeleton code and instruction to get me going in the right direction, or perhaps provide a resource/some resources to refer to.

    Thanks for any help you can provide!


    • Edited by Mark Yorkovich Monday, March 30, 2020 1:18 PM Provided example code
    Monday, March 30, 2020 12:46 PM

Answers

  • using System; using System.Data; namespace Cross_Reference_Tool { string connString = String.Format("Server = <serverIPaddress>; Port = <port#>; Database = <dbName>; User Id = <userID>; Password = <password>;"); public class CategoriesDataTable { public DataTable GetData(string selectSql) {

    NpgsqlConnection conn = new NpgsqlConnection(connString); DataSet ds = new DataSet(); try { conn.Open(); NpgsqlDataAdapter da = new NpgsqlDataAdapter(selectSql, conn); conn.Close(); da.Fill(ds); return ds.Tables[0]; } } } }

    All you have to do is make a project folder in the Windows form project. You can name the project folder Cross_Reference_Tool.

    You can then add a class in the folder. By making the folder and putting the class in the folder, it's a form of SoC, and you are also implementing namespace seperation, because any class created in the folder gets the folder name as the namespace automatically by Visual Stuido.

    Then at the top of the form class,  you will have a using statement pointing to the namespace where the class was created in.

    using Cross_Reference_Tool;

    Capiche?


    Monday, March 30, 2020 3:32 PM

All replies

  • What you need to understand is SoC.

    https://en.wikipedia.org/wiki/Separation_of_concerns

    How you accomplish SoC is by using layered or n-tier pattern.

    https://docs.microsoft.com/en-us/previous-versions/msp-n-p/ee658117(v=pandp.10)

    https://www.codeproject.com/Articles/36847/Three-Layer-Architecture-in-C-NET-2

    You may not need the business layer, and you could just have presentation and data access layers.

    Monday, March 30, 2020 1:17 PM
  • Okay - thanks for those resources, DA924x. Those are new terms and concepts to me.

    Also, I provided example code of what I currently have.

    Monday, March 30, 2020 1:20 PM
  • If you want to start out simple see the following class to read data, ignore : BaseSqlServerConnections, in the constructor create your connection string. It's all SQL-Server but the pattern remains the same for other databases. Loading is done here into a form. Otherwise go the route DA924x pointed too or Entity Framework Core with the following provider.


    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

    Monday, March 30, 2020 3:30 PM
    Moderator
  • using System; using System.Data; namespace Cross_Reference_Tool { string connString = String.Format("Server = <serverIPaddress>; Port = <port#>; Database = <dbName>; User Id = <userID>; Password = <password>;"); public class CategoriesDataTable { public DataTable GetData(string selectSql) {

    NpgsqlConnection conn = new NpgsqlConnection(connString); DataSet ds = new DataSet(); try { conn.Open(); NpgsqlDataAdapter da = new NpgsqlDataAdapter(selectSql, conn); conn.Close(); da.Fill(ds); return ds.Tables[0]; } } } }

    All you have to do is make a project folder in the Windows form project. You can name the project folder Cross_Reference_Tool.

    You can then add a class in the folder. By making the folder and putting the class in the folder, it's a form of SoC, and you are also implementing namespace seperation, because any class created in the folder gets the folder name as the namespace automatically by Visual Stuido.

    Then at the top of the form class,  you will have a using statement pointing to the namespace where the class was created in.

    using Cross_Reference_Tool;

    Capiche?


    Monday, March 30, 2020 3:32 PM
  • DA924x, that's getting me started. I did pretty much exactly as you suggested and created a folder in my solution, added the CategoriesDataTable, and moved the GetData method over to that class.

    Then in my Form1.cs class I added

    using Cross_Reference_Tool;

    with the rest of the using statements.

    But I'm having trouble figuring out the correct code to call the GetData method in the other class.

    In my original code I populated a DGV with 

    dgvCategories.DataSource = GetData("SELECT QUERY");

    My expectation with the class was to just use the same code, since I thought that the using statement at the top of the Form1 class would make the GetData method in the Cross_Reference_Tool class available.

    However, the code that I'm using in the Form class to call the GetData method is this:

    dgvCategories.DataSource = GetData("SELECT QUERY");

    And there is a red error underlining GetData with the message, "The name GetData does not exist in the current context". Again, my assumption was that "using Cross_Reference_Tool" would make the GetData method available for the Form class to use.



    Monday, March 30, 2020 10:11 PM
  • Never mind the above. I found after some more research that I had to instantiate (I think that's the correct term here) the CategoriesDataTable:

    CategoriesDataTable categoriesData = new CategoriesDataTable();
    dgvCategories.DataSource = categoriesData.GetData("SELECT * FROM actor");

    It worked like a charm after that.

    Thanks!

    Monday, March 30, 2020 10:39 PM
  • Never mind the above. I found after some more research that I had to instantiate (I think that's the correct term here) the CategoriesDataTable:

    CategoriesDataTable categoriesData = new CategoriesDataTable();
    dgvCategories.DataSource = categoriesData.GetData("SELECT * FROM actor");

    It worked like a charm after that.

    Thanks!

    Yes that's correct you have to instance a class into an object, unless you are using a static class. You can do the below way too. to instance the class into an object and use a method.

    dgvCategories.DataSource = new CategoriesDataTable().GetData("SELECT * FROM actor");

    Monday, March 30, 2020 11:03 PM
  • Thanks for the tip!
    Tuesday, March 31, 2020 1:25 AM