How to create a DataTable class for C# WinForms program

[Mark Yorkovich]

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!

Continue reading...