using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Text.RegularExpressions;
/// <summary>
/// Summary description for DALayer
/// </summary>
public class DALayer
{
public SqlConnection
cn = new SqlConnection();
SqlDataAdapter sdapt;
DataTable dt;
DataSet ds;
string err = string.Empty;
SqlCommand cmd;
static SqlTransaction
trans;
public DataSet
FetchRecords(SqlCommand command, ref string strError)
{
int i = 0;
try
{
string strQuery = command.CommandText;
strError = "";
OpenConnection();
Regex reg = new Regex("~@~");
string[] queries = reg.Split(strQuery);
ds = new DataSet();
foreach (string qry in queries)
{
SqlCommand cmd = new SqlCommand(qry,
cn);
SetSqlCommandParameters(cmd, qry, command);
sdapt = new SqlDataAdapter(cmd);
dt = new DataTable();
sdapt.Fill(dt);
ds.Tables.Add(dt);
i++;
}
}
catch (Exception
ae)
{
strError
+= "Query Number: " + i.ToString()
+ " " + ae.Message.ToString();
}
finally
{
cn.Close();
}
if (strError == ""
|| strError == null)
{
strError = "T";
}
return ds;
}
public void
ExecuteSingleQuery(SqlCommand command, ref string strError)
{
FetchRecords(command, ref strError);
}
public DALayer()
{
}
public int
GetSingleIntegerValue(SqlCommand command, ref string strError)
{
try
{
ds = FetchRecords(command, ref
strError);
strError = "T";
if (ds.Tables[0].Rows.Count > 0)
{
return int.Parse(ds.Tables[0].Rows[0][0].ToString());
}
}
catch (Exception
ae)
{
strError = ae.ToString();
}
finally
{
cn.Close();
}
return 0;
}
public DateTime
FetchDate(SqlCommand command, ref string strError)
{
try
{
DataSet ds = new DataSet();
ds = FetchRecords(command, ref
strError);
if (strError != "T")
{
throw new
Exception(strError);
}
return DateTime.Parse(ds.Tables[0].Rows[0][0].ToString());
}
catch (Exception
ae)
{
strError = ae.Message.ToString();
return DateTime.Parse("1900-01-01");
}
}
public SqlConnection
ReturnConnection()
{
try
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
OpenConnection();
return cn;
}
catch (Exception
ae)
{
throw ae;
}
}
public void
OpenConnection()
{
try
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
cn.ConnectionString = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
cn.Open();
}
catch (Exception
ae)
{
err = ae.ToString();
}
}
public void
BeginTransaction()
{
trans
= cn.BeginTransaction();
}
public void
CommitTransaction()
{
trans.Commit();
}
public void
CloseConnection()
{
cn.Close();
}
public void
ExecuteQuery(SqlCommand command, ref string strError)
{
string strQuery = command.CommandText;
SqlTransaction trans = null;
try
{
OpenConnection();
Regex reg = new Regex("~@~");
string[] queries = reg.Split(strQuery);
trans = cn.BeginTransaction();
foreach (string qry in queries)
{
cmd = new SqlCommand(qry,
cn);
SetSqlCommandParameters(cmd, qry, command);
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
}
trans.Commit();
strError = "T";
}
catch (Exception
ae)
{
if (trans != null)
trans.Rollback();
strError = ae.ToString();
}
finally
{
cn.Close();
}
}
private void
SetSqlCommandParameters(SqlCommand cmd, string qry, SqlCommand
command)
{
foreach (System.Data.Common.DbParameter
item in command.Parameters)
{
if (qry.Contains(item.ParameterName))
{
cmd.Parameters.AddWithValue(item.ParameterName, item.Value);
}
}
}
public void
ExecuteSingleQuery(string strQuery, ref string strError)
{
try
{
cmd = new SqlCommand(strQuery,
cn);
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
strError = "T";
}
catch (Exception
ex)
{
trans.Rollback();
strError = ex.Message.ToString();
}
}
public DataSet
FetchRecords(string strQuery, ref string strError)
{
strError = "";
OpenConnection();
Regex reg = new Regex("~@~");
string[] queries = reg.Split(strQuery);
ds = new DataSet();
int i = 0;
try
{
foreach (string qry in queries)
{
sdapt = new SqlDataAdapter(qry,
cn);
dt = new DataTable();
sdapt.Fill(dt);
ds.Tables.Add(dt);
i++;
}
}
catch (Exception
ae)
{
strError += "Query Number: "
+ i.ToString() + " " +
ae.Message.ToString();
}
finally
{
cn.Close();
}
if (strError == ""
|| strError == null)
{
strError = "T";
}
return ds;
}
public void
ExecuteQuery(string strQuery, ref string strError)
{
OpenConnection();
Regex
reg = new Regex("~@~");
string[] queries = reg.Split(strQuery);
BeginTransaction();
try
{
foreach (string qry in queries)
{
cmd = new SqlCommand(qry,
cn);
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
}
trans.Commit();
strError = "T";
}
catch (Exception
ae)
{
trans.Rollback();
strError = ae.Message.ToString();
}
finally
{
cn.Close();
}
}
public void
InsertMoneyTransfer(string PKFrom, string PKTo, decimal
BalanceAmount,
decimal TransferAmount, ref
string strError, ref
int ReturnID)
{
string strQuery = "spInsertMoneyTransfer";
OpenConnection();
SqlTransaction trans = cn.BeginTransaction();
try
{
cmd = new SqlCommand(strQuery,
cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PKFrom",
PKFrom);
cmd.Parameters.AddWithValue("@PKTo",
PKTo);
cmd.Parameters.AddWithValue("@BalanceAmount",
BalanceAmount);
cmd.Parameters.AddWithValue("@TransferAmount",
TransferAmount);
cmd.Parameters.Add("@ID", SqlDbType.Int, 0, "TransactionNo");
cmd.Parameters["@ID"].Direction
= ParameterDirection.Output;
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
ReturnID = (int)cmd.Parameters["@ID"].Value;
trans.Commit();
strError = "T";
}
catch (Exception
ae)
{
trans.Rollback();
strError = ae.Message.ToString();
}
finally
{
cn.Close();
}
}
public void
InsertFeedback(string Subject, string Message, string
EmailAddress,
ref string strError, ref int ReturnID)
{
string strQuery = "spInsertFeedBack";
OpenConnection();
SqlTransaction trans = cn.BeginTransaction();
try
{
cmd = new SqlCommand(strQuery,
cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Subject",
Subject);
cmd.Parameters.AddWithValue("@Message",
Message);
cmd.Parameters.AddWithValue("@EmailAddress",
EmailAddress);
cmd.Parameters["@ID"].Direction
= ParameterDirection.Output;
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
ReturnID = (int)cmd.Parameters["@ID"].Value;
trans.Commit();
strError = "T";
}
catch (Exception
ae)
{
trans.Rollback();
strError = ae.Message.ToString();
}
finally
{
cn.Close();
}
}
}
No comments:
Post a Comment