Adapter.cs example

//------------------FOR DEMO USE ONLY----------------------------------
//-------- Copyright by Artem Saveliev artem@savelev.com --------------
//---------------------------------------------------------------------

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace tires.DataAccess
{
	/// <summary>

	/// Static data access utilities
	/// </summary>
	public class Adapter
	{
		public Adapter()
		{
			//

			// TODO: Add constructor logic here
			//
		}
		/// <summary>
		/// Get Database connection based on web.conf
		/// </summary>

		/// <returns>Database connection</returns>
		public static IDbConnection getConn()
		{
			SqlConnection con = new SqlConnection();
			con.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnStr"];
			return con;
		}
		/// <summary>

		/// Get Command object, only use to manipulate database manually
		/// </summary>
		/// <param name="sql">SQL statement to run</param>
		/// <returns></returns>

		public static IDbCommand getCommand(string sql){
			SqlConnection con = (SqlConnection)getConn();
			SqlCommand cmd = new SqlCommand(sql,con);
			con.Open();
			return cmd;
		}
		/// <summary>

		/// Get DataReader for firehose reading of database
		/// </summary>
		/// <param name="sql">SQL statement to run</param>
		/// <returns></returns>

		public static IDataReader getRs(string sql)
		{
			SqlCommand cmd = (SqlCommand)getCommand(sql);
			SqlDataReader reader;
			try
			{
				reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch (Exception e)
			{
				throw new Exception("Error executing following query: " + sql,e);
			}
			return reader;
		}
		/// <summary>

		/// Get DataAdapter to run complex queries
		/// </summary>
		/// <param name="sql">SQL statement to run</param>
		/// <returns>IDataAdapter for the command</returns>

		public static IDataAdapter getAdapter(string sql)
		{
			SqlDataAdapter ad = new SqlDataAdapter((SqlCommand)getCommand(sql));
			return ad;
		}

		/// <summary>

		/// Fill dataset with data
		/// </summary>
		/// <param name="sql">SQL statement to run</param>
		/// <param name="s">dataset to fill</param>

		/// <param name="table">table from result to use (required for typed datasets)</param>
		public static void fillDataSet(string sql, DataSet s, string table) {
			SqlCommand cmd = (SqlCommand)getCommand(sql);
			SqlDataAdapter ad = new SqlDataAdapter(cmd);
			ad.Fill(s,table);
			cmd.Connection.Close();
		}

		/// <summary>

		/// Fill dataset with data
		/// </summary>
		/// <param name="sql">SQL statement to run</param>
		/// <param name="s">dataset to fill</param>

		/// <param name="table">table from result to use (required for typed datasets)</param>
		public static void fillDataSet(IDataAdapter ad, DataSet s, string table) 
		{
			((SqlDataAdapter)ad).MissingSchemaAction = MissingSchemaAction.AddWithKey;
			((SqlDataAdapter)ad).Fill(s,table);
		}

		public static void updateDataSet(IDataAdapter ad, DataSet s, string table)
		{

			((SqlDataAdapter)ad).Update(s,table);
		}

		public static void FillDataSet(string sql, DataSet ds)  
		{
			SqlCommand cmd = (SqlCommand)getCommand(sql);
			SqlDataAdapter ad = new SqlDataAdapter(cmd);
			ad.Fill(ds);
			cmd.Connection.Close();
		}

		/// <summary>

		/// Get DataSet from SQL query
		/// </summary>
		/// <param name="sql">SQL statement to run</param>
		/// <returns>DataSet</returns>

		public static DataSet getDataSet(string sql)
		{
			SqlDataAdapter custDA = new SqlDataAdapter();
			custDA.SelectCommand = (SqlCommand)getCommand(sql);
			DataSet custDS = new DataSet();
			custDA.Fill(custDS);
			custDA.SelectCommand.Connection.Close();
			return custDS;
		}
		/// <summary>

		/// Get one page of SQL result into DataTable
		/// </summary>
		/// <param name="sql">SQL Statement to run</param>
		/// <param name="pageSize">Size of the page to return</param>

		/// <param name="startRecord">Element to start page with</param>
		/// <returns>DataTable comtaining result</returns>
		public static DataTable getDataTablePage(string sql,int pageSize, int startRecord)
		{
			SqlDataAdapter custDA = new SqlDataAdapter();
			custDA.SelectCommand = (SqlCommand)getCommand(sql);
			DataSet custDS = new DataSet();
			custDA.Fill(custDS,startRecord,pageSize,"data");
			custDA.SelectCommand.Connection.Close();
			return custDS.Tables["data"];
		}
		/// <summary>

		/// Get whole SQL result into DataTable
		/// </summary>
		/// <param name="sql">SQL Statement to run</param>
		/// <returns>DataTable comtaining result</returns>

		public static DataTable getDataTable(string sql)
		{
			SqlDataAdapter custDA = new SqlDataAdapter();
			DataTable t;
			try
			{
				custDA.SelectCommand = (SqlCommand)getCommand(sql);
				DataSet custDS = new DataSet();
				custDA.Fill(custDS,"data");
				t = custDS.Tables["data"];
			}
			catch (Exception e)
			{
				throw new Exception("Error executing following query: " + sql,e);
			}
			finally

			{
				if (custDA.SelectCommand != null) custDA.SelectCommand.Connection.Close();
			}
			return t;
		}

		/// <summary>
		/// Get scalar value from the database. Use getText to read large (over 8Kb) text values, because data is split into rows in that case.
		/// </summary>

		/// <param name="sql">SQL statement to run, has to return one column</param>
		/// <returns>Object type, cast to your type</returns>
		public static Object getScalar(string sql)
		{
			object v;

			SqlCommand cmd = (SqlCommand)getCommand(sql);
			try

			{
				v = cmd.ExecuteScalar();
			}
			catch (Exception e)
			{
				throw new Exception("Error executing following query: " + sql,e);
			}
			finally
			{
				cmd.Connection.Close();
			}
			return v;
		}

		/// <summary>

		/// Get scalar value from the database, return null if there is no result. Use getText to read large (over 8Kb) text values, because data is split into rows in that case.
		/// </summary>
		/// <param name="sql">SQL statement to run, has to return one column</param>
		/// <returns>Object type, cast to your type</returns>

		public static Object getScalarEmpty(string sql)
		{
			object v;
			SqlCommand cmd = (SqlCommand)getCommand(sql);
			SqlDataReader r;
			try
			{
				 r = cmd.ExecuteReader();
			}
			catch (Exception e)
			{
				cmd.Connection.Close();
				throw new Exception("Error executing following query: " + sql,e);
			}

			if (r.Read())
			{
				r.Close();
				try

				{
					v = cmd.ExecuteScalar();
				}
				catch (Exception e)
				{
					throw new Exception("Error executing following query: " + sql,e);
				}
				finally
				{
					cmd.Connection.Close();
				}
			}
			else

			{
				r.Close();
				v = null;
			}
			return v;
		}

		/// <summary>
		/// Get scalar value from the database, return zero if there is no result. Use getText to read large (over 8Kb) text values, because data is split into rows in that case.
		/// </summary>

		/// <param name="sql">SQL statement to run, has to return one column</param>
		/// <returns>Object type, cast to your type</returns>
		public static Object getScalarZero(string sql)
		{
			Object o = getScalarEmpty(sql);
			if (o == null) return 0; else return o;
		}

		/// <summary>

		/// Get single text value from the database, able to read more than 8Kb
		/// </summary>
		/// <param name="sql">SQL statement to run, has to return one text column</param>
		/// <returns>string text value</returns>

		public static string getText(string sql) 
		{
			IDataReader r = getRs(sql);
			string result = "";
			while (r.Read())
				result += stringValue(r[0]);
			r.Close();
			return result;
		}

		/// <summary>

		/// Run query that doesn't return result
		/// </summary>
		/// <param name="sql">SQL statement to run</param>
		public static void executeNonQuery(string sql)
		{
			SqlCommand cmd = (SqlCommand)getCommand(sql);
			try

			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception e)
			{
				throw new Exception("Error executing following query: " + sql,e);
			}
			finally
			{
				cmd.Connection.Close();
			}
		}
		/// <summary>

		/// Escape text for SQL database
		/// </summary>
		/// <param name="txt">Text to escape</param>
		/// <returns>Escaped value</returns>

		public static string escape(string txt)
		{
			if (txt != null)
				return txt.Replace("'","''");
			else

				return "";
		}
		/// <summary>
		/// Convert empty (NULL) database value to empty string
		/// </summary>
		/// <param name="val">value</param>

		/// <returns>string represetation</returns>
		public static string stringValue(object val)
		{
			if (DBNull.Value == val)
				return "";
			else

				return val.ToString();
		}
	}
}