Programming in C#

Database and SQL Routines

In the .NET Framework, you can access your databases via various assemblies including System.Data, System.Data.SqlClient, System.Data.Odbc, and System.Data.OleDb routines. To make use of databases you need to create and open a connection the database, create a query using appropiate database language, e.g. Structured Query Language (SQL), to one or more tables and add any appropriate parameters and then execute it. The Query statement can usually be a Select, Insert, Update or Delete type command to view or modify one or more tables in the database. If using a select command, you need somehow to read the resulting records from the database and process them, you can use DataTable, ResultSets, or just sequential reads using a Reader function.

1. Making a connection to a database.

Connections are made using a connection string which contains the parameters and values needed to connect to a database such as database file, database server, database, user name and password.

a) Connecting to a Microsoft Access file

To connect to a Microsoft Access file, you need to install the Microsoft Database Engine 2010 x86 on your PC to enable OLEDB connections. Also, you need to add System.Data.OleDb reference to your Visual C# project. The OleDBConnection object will contain connection string. In the Connection string, the Provider tells the system what type of database to connect to, the Data Source parameter specifies the path and filename of the Access database. A simple Open() function then opens the connection to the database.

OleDbConnection DBConn;
string AccessDBName;

AccessDBName = "c:\\data\\myfile.accdb"; /* use double slashes when used in strings for file paths */
ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + AccessDBName + ";";

DBConn = new OleDbConnection(ConnStr);
DBConn.Open();

b) Running a Query to view contents of a table in the database.

Once a connection is made, you can run a command to query one or more tables in the database and then display the results. In this case we can store the results in a DataTable and put the results in a DataGridView.
The OleDbCommand object contains the query string, in this case a Select statement. The DBConn is assigned the the OleDbCommand.Connection, a new DataTable object is create, the command is executed and the results stored in the table and then assign to the dataGridView to display table contents.

OleDbDataAdapter DBObj;
OleDbDataReader DBReader;
OleDbCommand DBCmd;
DataTable myTable = null;
string TableName = "mytable";

DBObj = new OleDbDataAdapter();
DBCmd = new OleDbCommand();
DBCmd.CommandText = "select * from " + TableName + ";";
DBCmd.Connection = DBConn;
DBObj.SelectCommand = DBCmd;
DBObj.SelectCommand.Connection = DBConn;
myTable = new DataTable();

DBReader = DBObj.SelectCommand.ExecuteReader();
myTable.Load(DBReader);
DBReader.Close();
DBConn.Close();

dataGridView1.DataSource = myTable;
dataGridView1.Refresh();

c) Connecting to a SQL Server Database.