ADO .Net Data Access in C#
By Jaywant Dharwadkar

Download Source: DotNetData.zip

The example defines the prototype for accessing the data through ADO .Net objects in C#.

For any given database table there are 4 important operations while working with relational tables and databases. The 4 different operations, which can be done on the database, are ADD, UPDATE, DELETE and SELECT.

This prototype gives the users (especially those who come from writing COM object) the idea of a different architecture in .Net world about executing the database operations through store procedures just like people used to do it in VB COM objects. To tell you the truth I found .Net much more simpler and more appealing and the whole prototype is written in matter of 5-6 hrs. Also please excuse me for using VB like names in C# for the variables. Also excuse with my bad language. But hopefully users can understand my prototype.

I have used SQL 2000 desktop edition and Visual .Net Studio Beta 2 AND C# to write these prototype. In this example I am using database "pubs" which comes standard example with SQL server installation. Inside pubs database I have used the already existing table called "Stores". One can use any database or table but would have to make necessary changes in order to make the code work.

My connection string look like this

string strCn = @"uid=sa;pwd=;server=" + strServer + ";database=" + strDB;

Where

strServer = "Name of the users Machine where DB resides."

strDB = "Name of the database "

For example for my PC –

strServer = "COMP2\NETSDK"

str DB = "pubs"

Please not that \NETSDK is created when .Net Studio is installed on the machine. One might use machine name as machine name in my case "COMP2" as Server name. But it doesn’t work since the databases are found under "COMP2\NETSDK" server. One can find the SQL server under Server Explorer in .Net VS.

Also use \ for creating a back slash. Single "\" is considered as an escape sequence in C#. For more information please refer to C# reference books.

To make things simpler I am only concentrating the business objects and data services. I have used windows form to execute this procedure through the buttons on the form.

This prototype has following objects.

Data Services – (Store Procedures.)

I have written 4 store procedures, which encapsulates the functionality of the common database operation that is SELECT, DELETE, INSERT and UPDATE.

Business Objects

Namespace – "DotNetData" – I just used this name since I like it…

User can change the namespace to any thing they like.

DotNetData.DBFunctions – C# class library "DBFunctions" is a utility object that encapsulates the DB Operations.

In COM world this particular object is essentially a transaction object, which is interfacing the database and is implemented inside the other COM objects.

DotNetData.Stores – C# class library "Stores" contains a functions which implements functions from class DBFunctions. Also defines the different operation on the database table "Store".

Please note that business objects are divided into 2 objects. DBFunctions would be common denominator object for other class library such as class library Stores. Developer need to reference class DBFunctions while writing methods of class library such as Stores.

Windows Form "frmStore" – I have used Data Grid to show the retrieved data and Button objects to call and implement different functions from class library "Stores"