Matthew Tuffin Blog

Well I placed the library up for download on the codeplex website a week or two ago now and have yet to supply any sort of details on how to use this great tool.

So say you need to access rows in a database table which we will call tblPerson. In this table we have a first name column we want to grab and do some processing, altering the data so we modify some rows of data and then upload the change back to the database.

We want to access an microsoft sql-server located at the following hostname: Test-SQL

How can the library help, well I will go through the stages of how to use it and you can find out why.....

1. Download the library
2. Compile it to the .dll file

3. In a new project or the database one you are working on. Copy the dll to the project and add a reference to its location.
4. Add using DBConnection; to the top of the source file
5. Create a new object of CDataSource
6. Pass the constructor the type of database connection currently MSSQL, ODBC, OLE are the only ones supported, also pass a connection string in pointing to the database and then give the connection a meaningful name. For Example:

private CDataSource DS = new CDataSource(DataBaseType.MSSQL, "Driver={SQL Server};Server=Test-SQL;Database=TestDB;Uid=test;Pwd=test;", "TestDB");

7. Now add a table to the datasource:

DS.AddTable("tblPerson", "PersonQuery", "Select * from tblPerson", true, true);

The first parameter is the ACTUAL table name if you intend to do any Update/Delete/Inserts you need this
The second is a meaningful Name we can assign to that table
The last two parameters only work for MS-SQL. The first one uses the microsoft internal command builder the other one uses a custom command builder to generate update delete and insert statements

8. It might be wise to call a removetable before an addtable to make sure you have removed the table if it exists:

DS.RemoveTable("PersonQuery");

9. There are several ways to access the data we can use the bindingsource which is useful when dealing with other microsoft winform objects like the datagrid. However we just want to look at the rows of data. We can do this by looking in the dataset directly:

foreach (DataRow DR in DS.DataSourceDataSet.Tables["PersonQuery"].Rows)
{
DR["FirstName"] = "Test";
}

The meaningful name you gave the table earlier on is used to find it in the DataSet.
So we made an update well if the command builder was able to work its magic we can update this table back to the database.

10. DS.UpdateTable("PersonQuery");

So the table is now updated in the database. Lets count the lines of code 6 lines of code to pull a table into to memory and update it.

There are few other functions the library is capable that I won't go into to deal just yet. There is the ability in there to do sql like joins on datatables that are in memory. Giving the ability to join tables from two separate databases.

Details of my project can be found on the codeplex website http://www.codeplex.com/DBConnectionLibrary