CDataService: A Generic Database Access Class

CDataService.cs for generic database access with a testing client program - class1.cs. A short explanation of the attached codes is included.
 
A Generic Database Access Class
 
Using a generic database access component in data-aware applications can simply development and separate the data tier from the business tier. The following C# component - CDataService.cs has the four basic database manipulating functions to select, insert, update, and delete data in the 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
  1. using System;  
  2. using System.Data;  
  3. using System.IO;  
  4. using System.Data.SqlClient;  
  5. namespace DataService_ {  
  6.       /// <summary>  
  7.       /// PURPOSE: A generic database access class for SQL server 7.0 / 2000, for other database, use System.Data.Oledb  
  8.       /// FILEREV: 8/1/2001 - initial version  
  9.       /// </summary>  
  10.       public class CDataService {  
  11.             /// <summary>  
  12.             /// A string variable To store connection string  
  13.             /// </summary>  
  14.             private string m_ConnectionString;  
  15.             /// <summary>  
  16.             /// A SQlConnection object for database access  
  17.             /// </summary>  
  18.             private SqlConnection m_Connection;  
  19.             /// <summary>  
  20.             /// Property to set/get connection string  
  21.             /// Connection string can also be set via the overloaded constructor  
  22.             /// </summary>  
  23.             public string ConnectionString {  
  24.                   get { return m_ConnectionString; }  
  25.                   set { m_ConnectionString = value; }  
  26.             }  
  27.             /// <summary>  
  28.             /// Defaut constructor  
  29.             /// </summary>  
  30.             public CDataService() {}  
  31.             /// <summary>  
  32.             /// A overloaded constructor for setting connection string  
  33.             /// </summary>  
  34.             /// <param name="ConnectionString">Example: "server=(local);database=northwind;user id=sa;password=mypassword"</param>  
  35.             public CDataService(string ConnectionString) {  
  36.                         this.m_ConnectionString = ConnectionString;  
  37.                   }  
  38.                   /// <summary>  
  39.                   /// A destructor to close connection if any  
  40.                   /// </summary>  
  41.                   ~CDataService() {  
  42.                         this.CloseDataSource();  
  43.                   }  
  44.             /// <summary>  
  45.             /// Opens a connection, throws an exception if errors occur.  
  46.             /// </summary>  
  47.             /// <returns>System.Data.SqlClient.SqlConnection</returns>  
  48.             public SqlConnection OpenDataSource() {  
  49.                   //Test if SqlConnection exists  
  50.                   if (m_Connection == null) {  
  51.                         //If Connection String is not supplied, try reading from DataService_.cfg  
  52.                         if (m_ConnectionString == null || m_ConnectionString.Length == 0) {  
  53.                               try {  
  54.                                     m_ConnectionString = ReadConnectionString(@ "DataService_.cfg");  
  55.                               } catch (System.Exception e) {  
  56.                                     throw e;  
  57.                               }  
  58.                         }  
  59.                         try {  
  60.                               m_Connection = new SqlConnection(m_ConnectionString);  
  61.                               m_Connection.Open();  
  62.                         } catch (System.Exception e) {  
  63.                               throw e;  
  64.                         }  
  65.                   } else if (m_Connection.State != ConnectionState.Open) {  
  66.                         m_Connection.ConnectionString = m_ConnectionString;  
  67.                         try {  
  68.                               m_Connection.Open();  
  69.                         } catch (Exception e) {  
  70.                               throw e;  
  71.                         }  
  72.                   }  
  73.                   return m_Connection;  
  74.             }  
  75.             /// <summary>  
  76.             /// Reads Connection String from a text file, should use an encrypted file in the real world  
  77.             /// </summary>  
  78.             /// <param name="FilePath">text filename</param>  
  79.             /// <returns>Connection String</returns>  
  80.             private string ReadConnectionString(string FilePath) {  
  81.                   string s = null;  
  82.                   try {  
  83.                         StreamReader sr = File.OpenText(FilePath);  
  84.                         s = sr.ReadToEnd();  
  85.                   } catch (System.Exception e) {  
  86.                         throw e;  
  87.                   }  
  88.                   return (s);  
  89.             }  
  90.             /// <summary>  
  91.             /// Close connection if any, then set m_Connection to null  
  92.             /// </summary>  
  93.             public void CloseDataSource() {  
  94.                   if (m_Connection != null) {  
  95.                         if (m_Connection.State == ConnectionState.Open)  
  96.                               m_Connection.Close();  
  97.                         m_Connection = null;  
  98.                   }  
  99.             }  
  100.             /// <summary>  
  101.             /// Get dataset by sql selection statement or stored procedure  
  102.             /// </summary>  
  103.             /// <param name="QueryString">Selection statement or stored procedure name with parameters</param>  
  104.             /// <param name="TableName">A table name such as "Customers"</param>  
  105.             /// <returns>A DataSet contains data or null</returns>  
  106.             public DataSet SelectSqlData(string QueryString, string TableName, bool ustClose) {  
  107.                   DataSet ds = new DataSet();  
  108.                   SqlDataAdapter ad = new SqlDataAdapter();  
  109.                   try {  
  110.                         ad.SelectCommand = new SqlCommand(QueryString, this.OpenDataSource());  
  111.                         if (TableName.Trim().Length > 0) ad.Fill(ds, TableName);  
  112.                         else ad.Fill(ds);  
  113.                   } catch (System.Exception e) {  
  114.                         throw e;  
  115.                   } finally {  
  116.                         if (MustClose == truethis.CloseDataSource();  
  117.                   }  
  118.                   return ds;  
  119.             }  
  120.             /// <summary>  
  121.             /// An overloaded methods without MustClose, the connection will remain open  
  122.             /// </summary>  
  123.             /// <param name="QueryString">See method being overloaded</param>  
  124.             /// <returns>See method being overloaded</returns>  
  125.             public DataSet SelectSqlData(string QueryString, string TableName) {  
  126.                   return SelectSqlData(QueryString, TableName, false);  
  127.             }  
  128.             /// <summary>  
  129.             /// An overloaded methods without table name  
  130.             /// </summary>  
  131.             /// <param name="QueryString">See method being overloaded</param>  
  132.             /// <returns>See method being overloaded</returns>  
  133.             public DataSet SelectSqlData(string QueryString) {  
  134.                   return SelectSqlData(QueryString, ""false);  
  135.             }  
  136.             /// <summary>  
  137.             /// Insert data using sql statement or call a store procedure  
  138.             /// </summary>  
  139.             /// <param name="InsertString">a sql statement or stored procedure name with parameters</param>  
  140.             public void InsertSqlData(string InsertString, bool MustClose) {  
  141.                   SqlDataAdapter da = new SqlDataAdapter();  
  142.                   try {  
  143.                         da.InsertCommand = new SqlCommand(InsertString, this.OpenDataSource());  
  144.                         da.InsertCommand.ExecuteNonQuery();  
  145.                   } catch (Exception e) {  
  146.                         throw e;  
  147.                   } finally {  
  148.                         if (MustClose == truethis.CloseDataSource();  
  149.                   }  
  150.             }  
  151.             /// <summary>  
  152.             /// An overloaded function  
  153.             /// </summary>  
  154.             /// <param name="InsertString"></param>  
  155.             /// <param name="MustClose"></param>  
  156.             public void InsertSqlData(string InsertString) {  
  157.                   InsertSqlData(InsertString, false);  
  158.             }  
  159.             /// <summary>  
  160.             /// A function to delete date  
  161.             /// </summary>  
  162.             /// <param name="DeleteString">sql detele statement or stored procedure</param>  
  163.             public void DeleteSQLData(string DeleteString, bool MustClose) {  
  164.                   SqlDataAdapter da = new SqlDataAdapter();  
  165.                   try {  
  166.                         da.DeleteCommand = new SqlCommand(DeleteString, this.OpenDataSource());  
  167.                         da.DeleteCommand.ExecuteNonQuery();  
  168.                   } catch (Exception e) {  
  169.                         throw e;  
  170.                   } finally {  
  171.                         if (MustClose == truethis.CloseDataSource();  
  172.                   }  
  173.             }  
  174.             public void DeleteSQLData(string DeleteString) {  
  175.                   DeleteSQLData(DeleteString, false);  
  176.             }  
  177.             /// <summary>  
  178.             /// To update sql database using sql statement or stored procedure  
  179.             /// </summary>  
  180.             /// <param name="UpdateString">SQL statement or stored procedure</param>  
  181.             /// <param name="MustClose">Close connection if true</param>  
  182.             public void UpdateSQLData(string UpdateString, bool MustClose) {  
  183.                   SqlDataAdapter da = new SqlDataAdapter();  
  184.                   try {  
  185.                         da.UpdateCommand = new SqlCommand(UpdateString, this.OpenDataSource());  
  186.                         da.UpdateCommand.ExecuteNonQuery();  
  187.                   } catch (Exception e) {  
  188.                         throw e;  
  189.                   } finally {  
  190.                         if (MustClose == truethis.CloseDataSource();  
  191.                   }  
  192.             }  
  193.             /// <summary>  
  194.             /// An overloaded method, leaving connection open  
  195.             /// </summary>  
  196.             /// <param name="UpdateString">See overloaded function</param>  
  197.             public void UpdateSQLData(string UpdateString) {  
  198.                   UpdateSQLData(UpdateString, false);  
  199.             }  
  200.       } //Class  
  201. //Namespace 
class1.cs code
  1. using System;  
  2. using System.Data;  
  3. using DataService_;  
  4. namespace TryDataService {  
  5.       class Class1 {  
  6.             private  
  7.             const string ConnString = "server=(local);database=northwind;user id=sa;password=mypassword;";  
  8.             static void Main(string[] args) {  
  9.                   string sSQL;  
  10.                   DataSet ds = new DataSet();  
  11.                   CDataService objData = new CDataService(ConnString);  
  12.                   Console.WriteLine("Fetch data by SQL statement...");  
  13.                   sSQL = "select * from orders where customerid = " + SQLString("WELLI");  
  14.                   try {  
  15.                         ds = objData.SelectSqlData(sSQL, "orders"true); //read and close connection  
  16.                   } catch (Exception e) {  
  17.                         Console.WriteLine(e.ToString());  
  18.                   }  
  19.                   Console.WriteLine("Fetch data via stored procedure...");  
  20.                   sSQL = "sp_GetOrders " + SQLString("WELLI");  
  21.                   try {  
  22.                         ds = objData.SelectSqlData(sSQL, "orders"true); //sp_GetOrders is a stored procedure  
  23.                   } catch (Exception e) {  
  24.                         Console.WriteLine(e.ToString());  
  25.                   }  
  26.                   Console.WriteLine("Insert, update and delete data...");  
  27.                   sSQL = "insert into Customers (CustomerId, CompanyName ) Values ( ";  
  28.                   sSQL += SQLString("AAAAA") + ", " + SQLString("A Company") + ")";  
  29.                   try {  
  30.                         objData.InsertSqlData(sSQL); //insert a customer, leave connection open  
  31.                         sSQL = "update customers set ContactName = " + SQLString("A Name");  
  32.                         sSQL += "where CustomerId = " + SQLString("AAAAA");  
  33.                         objData.UpdateSQLData(sSQL); //update and leave connection open  
  34.                         sSQL = "sp_DeleteCustomer " + SQLString("AAAAA");  
  35.                         objData.DeleteSQLData(sSQL); //delete the customer via a stored procefure  
  36.                   } catch (Exception e) {  
  37.                         Console.WriteLine(e.ToString());  
  38.                   } finally {  
  39.                         objData.CloseDataSource();  
  40.                   }  
  41.             }  
  42.             private static string SQLString(string s) {  
  43.                   return ("'" + s.Replace("'""''") + "'");  
  44.             }  
  45.       }  


Similar Articles