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 == true) this.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 == true) this.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 == true) this.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 == true) this.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. }
  46. }