CDataService.cs for generic database access with a testing client program - class1.cs. A short explanation with attached codes are included.
A Generic Database Access Class
Using a generic database access component in data-aware applications can simply development and separate data tier from business tier. The following C# component - CDataService.cs has the four basic database manipulating functions to select, insert, update and delete data in SQL database. Client code can pass either a SQL statement or a stored procedure name with parameters to complete the database access. For database other than SQL 7.0/2000, the namespace - "System.Data.SqlClient" should be replaced with "System.Data.OleDb". All "Sql" prefix in the code should be replaced with "OleDb", for instance, "SqlDataAdapter" should be changed to "OleDbDataAdapter".
A WebService for generic database access can be developed easily based on the CDataService.cs. However, all public data access web methods may need a parameter for connection string since they are stateless, unless an application or a session object is used to store the connection string.
Database Access Component
The public method - OpenDataSorce( ) returns an object of System.Data.SqlClient.SqlConnection in case a client code needs a connection for some special database access, such as debugging. However, in general, a business object should avoid calling this method directly to access a database connection. Thus, this method may be changed to private for the real world.
CDataService.cs code
using
System;
using System.Data;
using System.IO;
using System.Data.SqlClient;
namespace DataService_
{
/// <summary>
/// PURPOSE: A generic database access class for SQL server 7.0 / 2000, for other database, use System.Data.Oledb
/// FILEREV: 8/1/2001 - initial version
/// </summary>
public class CDataService
{
/// <summary>
/// A string variable To store connection string
/// </summary>
private string m_ConnectionString;
/// <summary>
/// A SQlConnection object for database access
/// </summary>
private SqlConnection m_Connection;
/// <summary>
/// Property to set/get connection string
/// Connection string can also be set via the overloaded constructor
/// </summary>
public string ConnectionString
{
get {return m_ConnectionString; }
set {m_ConnectionString = value;}
}
/// <summary>
/// Defaut constructor
/// </summary>
public CDataService()
{
}
/// <summary>
/// A overloaded constructor for setting connection string
/// </summary>
/// <param name="ConnectionString">Example: "server=(local);database=northwind;user id=sa;password=mypassword"</param>
public CDataService(string ConnectionString)
{
this.m_ConnectionString = ConnectionString;
}
/// <summary>
/// A destructor to close connection if any
/// </summary>
~CDataService()
{
this.CloseDataSource();
}
/// <summary>
/// Opens a connection, throws an exception if errors occur.
/// </summary>
/// <returns>System.Data.SqlClient.SqlConnection</returns>
public SqlConnection OpenDataSource()
{
//Test if SqlConnection exists
if (m_Connection==null)
{
//If Connection String is not supplied, try reading from DataService_.cfg
if (m_ConnectionString == null || m_ConnectionString.Length == 0 )
{
try
{
m_ConnectionString = ReadConnectionString(@"DataService_.cfg");
}
catch (System.Exception e)
{
throw e;
}
}
try
{
m_Connection = new SqlConnection(m_ConnectionString);
m_Connection.Open();
}
catch (System.Exception e)
{
throw e;
}
}
else if (m_Connection.State != ConnectionState.Open)
{
m_Connection.ConnectionString = m_ConnectionString;
try
{
m_Connection.Open();
}
catch (Exception e)
{
throw e;
}
}
return m_Connection;
}
/// <summary>
/// Reads Connection String from a text file, should use an encrypted file in the real world
/// </summary>
/// <param name="FilePath">text filename</param>
/// <returns>Connection String</returns>
private string ReadConnectionString(string FilePath)
{
string s = null;
try
{
StreamReader sr = File.OpenText(FilePath);
s = sr.ReadToEnd();
}
catch (System.Exception e)
{
throw e;
}
return (s);
}
/// <summary>
/// Close connection if any, then set m_Connection to null
/// </summary>
public void CloseDataSource()
{
if (m_Connection!=null)
{
if (m_Connection.State == ConnectionState.Open )
m_Connection.Close();
m_Connection = null;
}
}
/// <summary>
/// Get dataset by sql selection statement or stored procedure
/// </summary>
/// <param name="QueryString">Selection statement or stored procedure name with parameters</param>
/// <param name="TableName">A table name such as "Customers"</param>
/// <returns>A DataSet contains data or null</returns>
public DataSet SelectSqlData(string QueryString, string TableName, bool ustClose)
{
DataSet ds = new DataSet();
SqlDataAdapter ad = new SqlDataAdapter();
try
{
ad.SelectCommand = new SqlCommand(QueryString, this.OpenDataSource());
if (TableName.Trim().Length > 0) ad.Fill(ds,TableName);
else ad.Fill(ds);
}
catch (System.Exception e)
{
throw e;
}
finally
{
if(MustClose == true) this.CloseDataSource();
}
return ds;
}
/// <summary>
/// An overloaded methods without MustClose, the connection will remain open
/// </summary>
/// <param name="QueryString">See method being overloaded</param>
/// <returns>See method being overloaded</returns>
public DataSet SelectSqlData(string QueryString, string TableName)
{
return SelectSqlData(QueryString,TableName,false);
}
/// <summary>
/// An overloaded methods without table name
/// </summary>
/// <param name="QueryString">See method being overloaded</param>
/// <returns>See method being overloaded</returns>
public DataSet SelectSqlData(string QueryString)
{
return SelectSqlData(QueryString,"",false);
}
/// <summary>
/// Insert data using sql statement or call a store procedure
/// </summary>
/// <param name="InsertString">a sql statement or stored procedure name with parameters</param>
public void InsertSqlData(string InsertString, bool MustClose)
{
SqlDataAdapter da = new SqlDataAdapter();
try
{
da.InsertCommand = new SqlCommand(InsertString, this.OpenDataSource());
da.InsertCommand.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
if(MustClose == true) this.CloseDataSource();
}
}
/// <summary>
/// An overloaded function
/// </summary>
/// <param name="InsertString"></param>
/// <param name="MustClose"></param>
public void InsertSqlData(string InsertString)
{
InsertSqlData(InsertString, false);
}
/// <summary>
/// A function to delete date
/// </summary>
/// <param name="DeleteString">sql detele statement or stored procedure</param>
public void DeleteSQLData(string DeleteString, bool MustClose)
{
SqlDataAdapter da = new SqlDataAdapter();
try
{
da.DeleteCommand = new SqlCommand(DeleteString, this.OpenDataSource());
da.DeleteCommand.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
if(MustClose == true ) this.CloseDataSource();
}
}
public void DeleteSQLData(string DeleteString)
{
DeleteSQLData(DeleteString, false);
}
/// <summary>
/// To update sql database using sql statement or stored procedure
/// </summary>
/// <param name="UpdateString">SQL statement or stored procedure</param>
/// <param name="MustClose">Close connection if true</param>
public void UpdateSQLData(string UpdateString, bool MustClose )
{
SqlDataAdapter da = new SqlDataAdapter();
try
{
da.UpdateCommand = new SqlCommand(UpdateString, this.OpenDataSource());
da.UpdateCommand.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
if(MustClose == true) this.CloseDataSource();
}
}
/// <summary>
/// An overloaded method, leaving connection open
/// </summary>
/// <param name="UpdateString">See overloaded function</param>
public void UpdateSQLData(string UpdateString )
{
UpdateSQLData(UpdateString,false);
}
} //Class
} //Namespace
class1.cs code
using System;
using System.Data;
using DataService_;
namespace TryDataService
{
class Class1
{
private const string ConnString = "server=(local);database=northwind;user id=sa;password=mypassword;";
static void Main(string[] args)
{
string sSQL;
DataSet ds = new DataSet();
CDataService objData = new CDataService(ConnString);
Console.WriteLine("Fetch data by SQL statement...");
sSQL = "select * from orders where customerid = " + SQLString("WELLI");
try
{
ds = objData.SelectSqlData(sSQL,"orders",true); //read and close connection
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
}
Console.WriteLine("Fetch data via stored procedure...");
sSQL = "sp_GetOrders " + SQLString("WELLI");
try
{
ds = objData.SelectSqlData(sSQL,"orders",true); //sp_GetOrders is a stored procedure
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
}
Console.WriteLine("Insert, update and delete data...");
sSQL = "insert into Customers (CustomerId, CompanyName ) Values ( ";
sSQL += SQLString("AAAAA") + ", " + SQLString("A Company") + ")";
try
{
objData.InsertSqlData(sSQL); //insert a customer, leave connection open
sSQL = "update customers set ContactName = " + SQLString("A Name");
sSQL += "where CustomerId = " + SQLString("AAAAA");
objData.UpdateSQLData(sSQL); //update and leave connection open
sSQL = "sp_DeleteCustomer " + SQLString("AAAAA");
objData.DeleteSQLData(sSQL); //delete the customer via a stored procefure
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
objData.CloseDataSource();
}
}
private static string SQLString(string s)
{
return ("'" + s.Replace("'","''") + "'");
}
}
}