Create Business Layer in asp.net
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;
namespace Acteon
{
/// <summary>
/// This is a static class which provides various Methods to read and update the database.
/// </summary>
public static class Database
{
static String conString;
/// <summary>
/// This is a static constructure in which we initialise some static varible.
/// </summary>
static Database()
{
//System.Configuration.Configuration rootWebConfig =
// System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/localhost:54138");
//System.Configuration.ConnectionStringSettings conString;
//if (rootWebConfig.ConnectionStrings.ConnectionStrings.Count > 0)
//{
// conString =
// rootWebConfig.ConnectionStrings.ConnectionStrings["Silverlight_TestConnectionString"];
// //if (conString != null)
// // Console.WriteLine("Northwind connection string = \"{0}\"",
// // conString.ConnectionString);
// //else
// // Console.WriteLine("No Northwind connection string");
//}
// conString = ConfigurationSettings.GetConfig("ConnectionString");
var _newString = ConfigurationSettings.GetConfig("aspnet2ConfigurationDemo");
if (_newString != null)
{
conString = (((CustomCon)(_newString)).ConnectionString).ToString();
//conString = ConfigurationManager.ConnectionStrings["ERP_MainDB"].ConnectionString;}
}
else
{
// conString = "Data Source=182.18.185.248;Initial Catalog=ERP;uid=salik;pwd=s
alik@123";
conString = "Data Source=SERVER2008r2\\ONLINE24X7;Initial Catalog=Acteon_website;Integrated Security=true;";
//conString = "Data Source=server\\ONLINE24X7;Initial Catalog=ERP;uid=salik;pwd=salik@123;Pooling=true;Max Pool Size=20000; Min Pool Size=10000;Connect Timeout=60";
//conString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
}
}
/// <summary>
/// This method reads data, from the database; into a DataTable.
/// </summary>
/// <param name="query"> T-SQL Or Stored Procedure Call as Query String which need to execute againest the database.</param>
/// <returns>The resultant DataTable OR null (if Error takes place in process)</returns>
public static DataTable getTable(string query)
{
SqlConnection myConnection = new SqlConnection(conString);
SqlCommand myCommand = new SqlCommand();
try
{
myCommand.CommandText = query;
myCommand.Connection = myConnection;
DataTable tab = new DataTable();
myConnection.Open();
tab.Load(myCommand.ExecuteReader());
return tab;
}
catch (Exception ex)
{
//Log.Write("Error :" + ex.Message);
return null;
}
finally
{
myConnection.Close();
myConnection.Dispose();
myCommand.Dispose();
}
}
public static Object getScalerRecord(SqlCommand _cmd)
{
object value = null;
SqlConnection myConnection = new SqlConnection(conString);
SqlCommand myCommand = _cmd;
try
{
myCommand.Connection = myConnection;
myConnection.Open();
value = myCommand.ExecuteScalar();
}
catch (Exception ex)
{
string str = ex.Message;
//Log.Write("Error :" + ex.Message);
}
finally
{
myConnection.Close();
myConnection.Dispose();
myCommand.Dispose();
}
return value;
}
/// <summary>
/// This method reads a single value, from the database; into a object.
/// </summary>
/// <param name="query"> T-SQL Or Stored Procedure Call as Query String which need to execute againest the database.</param>
/// <returns>The resultant object OR null (if Error takes place in process)</returns>
public static object getScalar(string query)
{
object value = null;
SqlConnection myConnection = new SqlConnection(conString);
SqlCommand myCommand = new SqlCommand();
try
{
myCommand.CommandText = query;
myCommand.Connection = myConnection;
myConnection.Open();
value = myCommand.ExecuteScalar();
}
catch (Exception ex)
{
//Log.Write("Error :" + ex.Message);
}
finally
{
myConnection.Close();
myConnection.Dispose();
myCommand.Dispose();
}
return value;
}
public static object getScalarWithType(string query, CommandType type, System.Int64 Rolno)
{
object value = null;
SqlConnection myConnection = new SqlConnection(conString);
SqlCommand myCommand = new SqlCommand();
try
{
myCommand.CommandType = type;
myCommand.CommandText = query;
myCommand.Parameters.AddWithValue("@rolno", Rolno);
myCommand.Connection = myConnection;
myConnection.Open();
value = myCommand.ExecuteScalar();
}
catch (Exception ex)
{
//Log.Write("Error :" + ex.Message);
}
finally
{
myConnection.Close();
myConnection.Dispose();
myCommand.Dispose();
}
return value;
}
/// <summary>
/// This method reads data, from the database; into a DataSet.
/// </summary>
/// <param name="query"> T-SQL Or Stored Procedure Call as Query String which need to execute againest the database.</param>
/// <returns>The resultant DataSet OR null (if Error takes place in process)</returns>
public static DataSet getDataSet(string query)
{
SqlConnection myConnection = new SqlConnection(conString);
SqlCommand myCommand = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(query, myConnection);
try
{
DataSet ds = new DataSet();
myConnection.Open();
da.Fill(ds);
return ds;
}
catch (Exception ex)
{
//Log.Write("Error :" + ex.Message);
return null;
}
finally
{
myConnection.Close();
myConnection.Dispose();
da.Dispose();
}
}
/// <summary>
/// This method reads data, from the database; into a DataSet.
/// </summary>
/// <param name="MyCommand"> T-SQL Or Stored Procedure Call as Query String which need to execute againest the database.</param>
/// <returns>The resultant DataSet OR null (if Error takes place in process)</returns>
public static DataTable getDataTable(SqlCommand myCommand)
{
SqlConnection myConnection = new SqlConnection(conString);
myCommand.Connection = myConnection;
SqlDataAdapter da = new SqlDataAdapter(myCommand);
try
{
DataTable dt = new DataTable();
myConnection.Open();
da.Fill(dt);
return dt;
}
catch (Exception ex)
{
//Log.Write("Error :" + ex.Message);
return null;
}
finally
{
myConnection.Close();
myConnection.Dispose();
da.Dispose();
}
}
/// <summary>
/// This method reads data, from the database; into a DataSet.
/// </summary>
/// <param name="MyCommand"> T-SQL Or Stored Procedure Call as Query String which need to execute againest the database.</param>
/// <returns>The resultant DataSet OR null (if Error takes place in process)</returns>
public static DataSet getDataSet(SqlCommand myCommand)
{
SqlConnection myConnection = new SqlConnection(conString);
myCommand.Connection = myConnection;
SqlDataAdapter da = new SqlDataAdapter(myCommand);
try
{
DataSet ds = new DataSet();
myConnection.Open();
da.Fill(ds);
return ds;
}
catch (Exception ex)
{
//Log.Write("Error :" + ex.Message);
return null;
}
finally
{
myConnection.Close();
myConnection.Dispose();
da.Dispose();
}
}
/// <summary>
/// Executes a Transact-SQL statement against the connection and returns the number of rows effected.
/// </summary>
/// <param name="query">Transact-SQL statement</param>
/// <exception cref="System.InvalidOperationException"/>
/// <exception cref="System.Data.SqlClient.SqlException"/>
/// <exception cref="System.ArgumentException"/>
/// <returns> returns the number of rows effected. </returns>
public static int updateTable(string query)
{
int AfectedRows = -1;
SqlConnection myConnection = new SqlConnection(conString);
SqlCommand myCommand = new SqlCommand();
try
{
myCommand.CommandText = query;
myCommand.Connection = myConnection;
myConnection.Open();
AfectedRows = myCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
//Log.Write("Error :" + ex.Message);
throw ex;
}
finally
{
myConnection.Close();
myConnection.Dispose();
myCommand.Dispose();
}
return AfectedRows;
}
/// <summary>
/// Executes a Transact-SQL statement against the connection and returns the number of rows effected.
/// </summary>
/// <param name="myCommand">SQLCommand</param>
/// <exception cref="System.InvalidOperationException"/>
/// <exception cref="System.Data.SqlClient.SqlException"/>
/// <exception cref="System.ArgumentException"/>
/// <returns> returns the number of rows effected. </returns>
public static int updateTable(SqlCommand myCommand)
{
int AfectedRows;
SqlConnection myConnection = new SqlConnection(conString);
try
{
myCommand.Connection = myConnection;
myConnection.Open();
AfectedRows = myCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
//Log.Write("Error :" + ex.Message);
throw ex;
}
finally
{
myConnection.Close();
myConnection.Dispose();
myCommand.Dispose();
}
return AfectedRows;
}
/// <summary>
/// Executes a Transact-SQL statement against the connection and returns the result of SP as Object.
/// </summary>
/// <param name="query">Transact-SQL statement</param>
/// <exception cref="System.InvalidOperationException"/>
/// <exception cref="System.Data.SqlClient.SqlException"/>
/// <exception cref="System.ArgumentException"/>
/// <returns> returns the output of SP execution. </returns>
public static object insertTable(string query)
{
object returnedResult = null;
SqlConnection myConnection = new SqlConnection(conString);
SqlCommand myCommand = new SqlCommand();
try
{
myCommand.CommandText = query;
myCommand.Connection = myConnection;
myConnection.Open();
returnedResult = myCommand.ExecuteScalar();
}
catch (Exception ex)
{
//Log.Write("Error :" + ex.Message);
throw ex;
}
finally
{
myConnection.Close();
myConnection.Dispose();
myCommand.Dispose();
}
return returnedResult;
}
public static Int32 ExecuteNonQry(SqlCommand _cmd)
{
object returnedResult = null;
using (SqlConnection myConnection = new SqlConnection(conString))
{
if (myConnection.State == ConnectionState.Closed)
{
myConnection.Open();
}
//SqlTransaction _tra = myConnection.BeginTransaction();
try
{
_cmd.Connection = myConnection;
return _cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//Log.Write("Error :" + ex.Message);
throw ex;
}
finally
{
myConnection.Close();
myConnection.Dispose();
}
return 0;
}
}
/// <summary>
/// Executes a Transact-SQL statement against the connection and returns the number of rows effected.
/// </summary>
/// <param name="myCommand">SQLCommand</param>
/// <exception cref="System.InvalidOperationException"/>
/// <exception cref="System.Data.SqlClient.SqlException"/>
/// <exception cref="System.ArgumentException"/>
/// <returns> returns the number of rows effected. </returns>
public static object insertTable(SqlCommand myCommand)
{
object returnedResult = null;
using (SqlConnection myConnection = new SqlConnection(conString))
{
if (myConnection.State == ConnectionState.Closed)
{
myConnection.Open();
}
SqlTransaction _tra = myConnection.BeginTransaction();
try
{
myCommand.Connection = myConnection;
myCommand.Transaction = _tra;
returnedResult = myCommand.ExecuteScalar();
_tra.Commit();
}
catch (Exception ex)
{
//Log.Write("Error :" + ex.Message);
_tra.Rollback();
throw ex;
}
finally
{
myConnection.Close();
myConnection.Dispose();
myCommand.Dispose();
}
return returnedResult;
}
}
//public static Object getScalerRecord(SqlCommand _cmd)
//{
// object value = null;
// SqlConnection myConnection = new SqlConnection(conString);
// SqlCommand myCommand = _cmd;
// try
// {
// myCommand.Connection = myConnection;
// myConnection.Open();
// value = myCommand.ExecuteScalar();
// }
// catch (Exception ex)
// {
// string str = ex.Message;
// //Log.Write("Error :" + ex.Message);
// }
// finally
// {
// myConnection.Close();
// myConnection.Dispose();
// myCommand.Dispose();
// }
// return value;
//}
public static object GetOutPutValue(SqlCommand _cmd, string _outputPerametgerName)
{
if (object.ReferenceEquals(_cmd, null))
throw new Exception("Command object not initialize properly");
using (_cmd)
{
using (SqlConnection _cmon = new SqlConnection(conString))
{
if (_cmon.State == ConnectionState.Closed)
{
_cmon.Open();
}
_cmd.Connection = _cmon;
_cmd.ExecuteNonQuery();
return _cmd.Parameters[_outputPerametgerName].Value;
}
}
}
public static string[] GetOutPutValue(SqlCommand _cmd, string[] _outputPerametgerName)
{
if (object.ReferenceEquals(_cmd, null))
throw new Exception("Command object not initialize properly");
using (_cmd)
{
using (SqlConnection _cmon = new SqlConnection(conString))
{
if (_cmon.State == ConnectionState.Closed)
{
_cmon.Open();
}
_cmd.Connection = _cmon;
_cmd.ExecuteNonQuery();
List<String> _list = new List<string>();
foreach (string _str in _outputPerametgerName)
{
_list.Add(((string)_cmd.Parameters[_str].Value) ?? "");
}
return _list.ToArray();
}
}
}
}
}
