Using ADO.Net for your Data Access Classes

• Download Gda.zip - 2.3 KB

Introduction

Most of the enterprise applications today cannot exist without a Relational Database management system. The choice of the RDBMS depends upon various factors like the analytics that need to be performed with the data, platform that is used for application development , Size of the tables and types of data that need to be stored.

I find Microsoft SQL Server most compatible with Microsoft .Net and it has a lot of support in the .Net framework classes. Although .Net supports all major platforms but undoubtedly performs best with SQL Server. In this article I would discuss some key practices that are used to perform basic Database operations to get you started.

Connecting to Database:

Its easy , there are many classes in the .Net framework to be used with different backends. The System.Data namespace has many specialized classes like (System.Data.SQLClient.SQLConnection) and general ones like (System.Data.OLEDB.OledbConnection) . For just giving you an overview I would use OledbConnection Class as it can be used with different backends.

All you need to do is to instantiate the class and set the connection string property before you can open and close the connection. To find the exact Connection string you can use a udl "universal data link " file and try connect to your database. Simply rightclick on your desktop and make a new text file.Click on File > Save as > "abc.udl". Close the file and double click on the udl file. Now try connecting to the database by entering the login information and selecting your drivers and hit test connection . When the test succeeds close the udl by hitting "OK" and open the file with a text editor. On the third line you see the connection string. Typically you would connect to the database as:

/// <summary>

/// This method is used internally by other methods of GlobalDataAccess. 

/// </summary>

/// <returns></returns>

public bool ConnectToDatabase()
{
bool toReturn = false;
try
{
Conn = new SqlConnection(sConnecionString);
Conn.Open();
toReturn = true;
}
catch (Exception ex)
{
toReturn = false;
throw new Exception(ex.Message + " Cannot Establish coinnection with SQL Server"); 
}
return toReturn;
} 

Specify What StoredProc or query to run:

You can specify the query or stored procedure that you want to execute in a Command Object . The choice of the Command Object also depends upon what connection you are using . In case of OLEDB.OledbConnection you can use OLEDB.OledbCommand and pass in the query and the open connection instance. (For the open connection instance call the Open method on the connection instance before passing it in) and you are all set up with your command.Mostly Command object is used when you want to Update , Insert , delete the data in any of your tables or want to select single rows and not show the results in a datagrid , instead you want to populate a drop down list since it exposes methods like ExecuteReader which returns a DataReader ( with which you can iterate and process each result).

It is often desirable to retrieve the dataset to show on a datagrid or bind with crystal reports in such cases often DataAdapter is used since it provides simple operations with which a data adapter can be populated.Instead of using a Command Object we can instantiate a DataAdapter Object and pass in the query and the open connection. DataAdapter class is available in the OLEDB and SQLClient namespaces. After the query and the connection information is passed in the DataAdapter instance call the fill method of the dataadapter and pass in an empty data set which will get filled with query results and you are ready to set it as a datasource for datagrids or crystal reports and various other controls

Getting a DataSet or A Reader for UI Controls:

/// <summary>

/// The Name of the Stored Procedure and connection string should be supplied with the constructor

/// </summary>

/// <returns>Dataset that can be shown on datagrid or formatted with xsl</returns> 

public System.Data.DataSet ExecuteDataSet()
{
System.Data.DataSet ds = new System.Data.DataSet();
try
{
// Connect to the database

if (!ConnectToDatabase())
throw new Exception("Cannot Connect to Database");
// if the connection is opened initialize the command object

Cmd.Connection = Conn; 
DataAdapter = new SqlDataAdapter(Cmd); 
DataAdapter.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
DataAdapter.SelectCommand.CommandText = sCommandName;
// execute the command and get the dataset and return it to caller

DataAdapter.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw new Exception(ex.Message + "Cannot Execute Dataset");
ds = null;
}
finally
{
DataAdapter = null; 
Conn = null; 
}

return ds;
} // End Execute dataset


 

Using the Code:

It is a 3 step process.

1. Instantiate the class and pass in stored procedure name
2. Set the connection string in the web.config or modify it to your needs
3. Add Parameters and Call execution methods

 

// specify the SP Name

GlobalDataAccess getAllAlerts = new GlobalDataAccess("TestSP");
// specify the key name in web.config with connection string

bool success = getAllAlerts.InitDBConnection("TestDB");
getAllAlerts.AddParameter("@StartDate", "01-01-2007");
getAllAlerts.AddParameter("@EndDate", "01-01-2015");
DataSet dsAllAlerts = getAllAlerts.ExecuteDataSet();
dgAlerts.DataSource = null;
dgAlerts.DataSource = dsAllAlerts;
ReportData.ReportDataSet = dsAllAlerts;