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

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("'", "''") + "'");
            }
      }
}