Purpose
To access SQL SERVER database and execute Stored Procedures. If you need Dataset, you can use GetDataSet method or if you need DataReader use GetDataReader method and so on.
How to Use
Copy the code below and compile it as DataAccess.cs
Add reference of DataAccess.dll from Bin directory to your project.
Instantiate DataAccess Class and use its functionality.
string SPName= "SP_Search '" + FirstName + " '";
string strConnect = "Data Source=ServerName;InitialCatalog=databaseName; UID=yourUserid;PWD=yourPassword" ;
DataAccess objdb = new DataAccess();
if you want to bind to the DataGrid with DataReader in ASP.net
DataGrid1.DataSource = objdb.GetDataReader(strConn,SPName);
DataGrid1.DataBind();
if you want dataset with two dataTables
System.Data.DataView dvwProduct= new System.Data.DataView() ;
System.Data.DataView dvwSearch= new System.Data.DataView() ;
System.Data.DataSet dst ;
string[] arrProcName = new string[2];
string[] arrTableName = new string[2];
arrProcName[0] = ""SP_Search '" + FirstName + " '";
arrProcName[1] = "sp_getProduct "+ prodID;
arrTableName[0] = "Search";
arrTableName[1] = "Products";
string strConnect = "Data Source=ServerName;InitialCatalog=databaseName;UID=yourUserid; PWD=yourPassword";
dst = objdb.GetDataSet(strConn,arrProcName,arrTableName);
dvwSearch=dst.Tables["Search"].DefaultView;
dvwProduct = dst.Tables["Products"].DefaultView;
Warning: Be careful creating SPName string. You need to form a string With Stored procedure name and give space and parameters name if any. If parameter is a varchar data type ,don't forget to add quotes as shown above.
Source Code
using System;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
public class DataAccess
{
//**************************************
//* Purpose: Accessing SQL database
//*Methods:
//*GetDataSet
//*RunProc
//*GetDataReader
//*GetDataView
//*Created By: Satya Akkinepally
//* *************************************
public DataSet GetDataSet(string strConnect,string[] ProcName , string[] DataTable)
{
//********************************
//* Purpose: Returns Dataset for one or multi datatables
//* Input parameters:
//*strConnect----Connection string
//*ProcName() ---StoredProcedures name in array
//*DataTable()---DataTable name in array
//* Returns :
//*DataSet Object contains data
//**************************************************
DataSet dstEorder ;
SqlConnection conn;
SqlDataAdapter dadEorder;
try
{
int intCnt = ProcName.GetUpperBound(0);
dstEorder = new DataSet();
conn = new SqlConnection(strConnect);
// if one datatable and SP
if(intCnt == 0)
{
dadEorder = new SqlDataAdapter(ProcName[0], conn);
dadEorder.Fill(dstEorder, DataTable[0]);
}
// more than one datatable and one SP
else
{
conn.Open();
//add first data table and first SP
dadEorder = new SqlDataAdapter(ProcName[0], conn);
dadEorder.Fill(dstEorder, DataTable[0]);
// add second datatable and second SP onwards
for(int i=1 ;i< (intCnt +1) ;i++)
{
dadEorder.SelectCommand = new SqlCommand(ProcName[i], conn);
dadEorder.Fill(dstEorder, DataTable[i]);
}
conn.Close();
}
return dstEorder;
}
catch ( Exception objError)
{
//write error to the windows event log
WriteToEventLog(objError);
throw;
}
}
public void RunProc(string strConnect,string ProcName)
{
//****************************************
//* Purpose: Executing Stored Procedures where UPDATE, INSERT
//*and DELETE statements are expected but does not
//*work for select statement is expected.
//* Input parameters:
//*strConnect----Connection string
//*ProcName ---StoredProcedures name
//* Returns :
//*nothing
//* ***************************************
string strCommandText= ProcName;
//create a new Connection object using the connection string
SqlConnection objConnect =new SqlConnection(strConnect);
//create a new Command using the CommandText and Connection object
SqlCommand objCommand =new SqlCommand(strCommandText, objConnect);
try
{
objConnect.Open();
objCommand.ExecuteNonQuery();
}
catch(Exception objError)
{
//write error to the windows event log
WriteToEventLog(objError);
throw;
}
finally
{
objConnect.Close();
}
}
public SqlDataReader GetDataReader(string strConnect, string ProcName)
{
//**************************************
//* Purpose: Getting DataReader for the given Procedure
//* Input parameters:
//*strConnect----Connection string
//*ProcName ---StoredProcedures name
//* Returns :
//*DataReader contains data
//* ************************************
string strCommandText= ProcName;
SqlDataReader objDataReader;
//create a new Connection object using the connection string
SqlConnection objConnect =new SqlConnection(strConnect);
//create a new Command using the CommandText and Connection object
SqlCommand objCommand = new SqlCommand(strCommandText, objConnect);
try
{
//open the connection and execute the command
objConnect.Open();
//objDataAdapter.SelectCommand = objCommand
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
catch( Exception objError)
{
//write error to the windows event log
WriteToEventLog(objError);
throw;
}
return objDataReader;
}
public DataView GetDataView ( string strConnect,string ProcName,string DataSetTable)
{
//*****************************************
//* Purpose: Getting DataReader for the given Procedure
//* Input parameters:
//* strConnect----Connection string
//* ProcName ---StoredProcedures name
//* DataSetTable--DataSetTable name sting
//* Returns :
//* DataView contains data
//* ****************************************
string strCommandText= ProcName;
//create a new Connection object using the connection string
SqlConnection objConnect = new SqlConnection(strConnect);
//create a new Command using the CommandText and Connection object
SqlCommand objCommand= new SqlCommand(strCommandText, objConnect);
//declare a variable to hold a DataAdaptor object
SqlDataAdapter objDataAdapter = new SqlDataAdapter();
try
{
//open the connection and execute the command
objConnect.Open();
objDataAdapter.SelectCommand = objCommand;
//objDataReader = objCommand.ExecuteReader()
}
catch(Exception objError)
{
//write error to the windows event log
WriteToEventLog(objError);
throw;
}
DataSet objDataSet;
objDataSet = new DataSet(DataSetTable);
objDataAdapter.Fill(objDataSet);
DataView objDataView ;
objDataView = new DataView(objDataSet.Tables[0]);
objConnect.Close();
return objDataView;
}
private void WriteToEventLog( Exception objError)
{
//*************************************
//* Purpose:Writing error to the windows event log
//* Input parameters:
//*objError----Exception object
//* Returns :
//*nothing
//* ***************************************************
System.Diagnostics.EventLog objEventLog = new System.Diagnostics.EventLog();
objEventLog.Source = "Your Application Name";
objEventLog.WriteEntry(objError.Message.ToString());
}
}