CDataService: A Generic Database Access Class

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