using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
namespace DataBaseService
{
public partial class DataBaseService : IDataBaseService
{
public static string ConnectionString = Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["con"]);
static SqlConnection GlobalConn()
{
SqlConnection sql = new SqlConnection(ConnectionString);
return sql;
}
public DataSet GetDataSet(string uspName, string[] paramArray, object[] valueArray)
{
SqlConnection con = null;
DataSet dataSet;
SqlDataAdapter sqlDataAdapter;
SqlCommand sqlCommand;
int i;
dataSet = new DataSet();
try
{
sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = new SqlCommand();
con = GlobalConn();
con.Open();
sqlDataAdapter.SelectCommand.Connection = con;
sqlCommand = sqlDataAdapter.SelectCommand;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = uspName;
i = 0;
while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
{
if (paramArray[i] != null)
{
sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
}
i++;
}
sqlDataAdapter.Fill(dataSet);
}
catch (Exception Ex)
{
throw new Exception(Ex.Message, Ex);
}
finally
{
con.Close();
}
return dataSet;
}
public DataSet GetDataSetWithOutParam(string spName)
{
SqlConnection con = null;
DataSet dataSet;
SqlDataAdapter sqlDataAdapter;
SqlCommand sqlCommand;
dataSet = new DataSet();
try
{
sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = new SqlCommand();
con = GlobalConn();
con.Open();
sqlDataAdapter.SelectCommand.Connection = con;
sqlCommand = sqlDataAdapter.SelectCommand;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = spName;
sqlDataAdapter.Fill(dataSet);
}
catch (Exception Ex)
{
throw new Exception(Ex.Message, Ex);
}
finally
{
con.Close();
}
return dataSet;
}
public DataSet GetDataSetReturnWithParam(string uspName, string[] paramArray, object[] valueArray, string OutParamName, out Int32 OutParam)
{
SqlConnection con = null;
DataSet dataSet;
SqlDataAdapter sqlDataAdapter;
SqlCommand sqlCommand;
int i;
dataSet = new DataSet();
OutParam = 0;
try
{
sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = new SqlCommand();
con = GlobalConn();
con.Open();
sqlDataAdapter.SelectCommand.Connection = con;
sqlCommand = sqlDataAdapter.SelectCommand;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = uspName;
i = 0;
while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
{
if (paramArray[i] != null)
{
sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
}
i++;
}
//Set out parameter
sqlCommand.Parameters.AddWithValue(OutParamName, OutParam);
sqlCommand.Parameters[OutParamName].Direction = ParameterDirection.Output;
sqlCommand.Parameters[OutParamName].DbType = System.Data.DbType.Int32;
sqlDataAdapter.Fill(dataSet);
if (sqlCommand.Parameters[OutParamName] != null)
{
OutParam = Convert.ToInt32(sqlCommand.Parameters[OutParamName].Value); //sqlCommand.Parameters.Add(OutParamName, SqlDbType.Int)
}
}
catch (Exception Ex)
{
throw new Exception(Ex.Message, Ex);
}
finally
{
con.Close();
}
return dataSet;
}
public DataSet GetDataSetWithReturnValue(string uspName, string[] paramArray, object[] valueArray, out Int32 OutParam)
{
SqlConnection con = null;
DataSet dataSet;
SqlDataAdapter sqlDataAdapter;
SqlCommand sqlCommand;
int i;
dataSet = new DataSet();
OutParam = 0;
string OutParamName = "@ReturnValue";
try
{
sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = new SqlCommand();
con = GlobalConn();
con.Open();
sqlDataAdapter.SelectCommand.Connection = con;
sqlCommand = sqlDataAdapter.SelectCommand;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = uspName;
i = 0;
while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
{
if (paramArray[i] != null)
{
sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
}
i++;
}
//Set out parameter
sqlCommand.Parameters.AddWithValue(OutParamName, OutParam);
sqlCommand.Parameters[OutParamName].Direction = ParameterDirection.ReturnValue;
sqlCommand.Parameters[OutParamName].SqlDbType = SqlDbType.Int;
sqlDataAdapter.Fill(dataSet);
if (sqlCommand.Parameters[OutParamName] != null)
{
OutParam = Convert.ToInt32(sqlCommand.Parameters[OutParamName].Value); //sqlCommand.Parameters.Add(OutParamName, SqlDbType.Int)
}
}
catch (Exception Ex)
{
throw new Exception(Ex.Message, Ex);
}
finally
{
con.Close();
}
return dataSet;
}
public DataTable GetDataTable(string uspName, string strParam, string strMain)
{
SqlConnection con = null;
DataTable dataTable;
SqlDataAdapter sqlDataAdapter;
dataTable = new DataTable();
try
{
sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = new SqlCommand();
con = GlobalConn();
con.Open();
sqlDataAdapter.SelectCommand.Connection = con;
SqlCommand sqlCommand = sqlDataAdapter.SelectCommand;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = uspName;
sqlCommand.Parameters.Add(strParam, SqlDbType.VarChar).Value = strMain;
sqlDataAdapter.Fill(dataTable);
}
catch (Exception Ex)
{
//GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem in Getting Data.", Ex.Message);
throw new Exception(Ex.Message, Ex);
}
finally
{
con.Close();
}
return dataTable;
}
public void ExecuteSPReturn(string uspName)
{
SqlConnection con = null;
try
{
SqlCommand sqlCommand = new SqlCommand();
con = GlobalConn();
con.Open();
sqlCommand.Connection = con;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = uspName;
sqlCommand.ExecuteNonQuery();
}
catch (Exception Ex)
{
//GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem occured during execution.", Ex.Message);
throw new Exception(Ex.Message, Ex);
}
finally
{
con.Close();
}
}
public object ExecuteSPReturnWithParam(string uspName, string[] ParamArr, object[] ValueArr, object OutParam)
{
SqlConnection con = null;
string str;
SqlCommand sqlCommand;
int i;
try
{
str = "";
sqlCommand = new SqlCommand();
con = GlobalConn();
SqlConnection sqlCon = con;
sqlCon.Open();
sqlCommand.Connection = sqlCon;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = uspName;
if ((ParamArr.Length.Equals(0)) || (ValueArr.Length.Equals(0)))
{
return "";
}
if (!ParamArr.Length.Equals(ValueArr.Length))
{
return "";
}
i = 0;
while (i < ParamArr.Length)
{
if (ParamArr[i] != null)
{
sqlCommand.Parameters.AddWithValue(ParamArr[i], ValueArr[i]);
}
str = string.Concat(str, ",", ValueArr[i].ToString());
i++;
}
sqlCommand.Parameters["@OutParam"].Direction = ParameterDirection.Output;
sqlCommand.Parameters["@OutParam"].DbType = System.Data.DbType.Int64;
sqlCommand.ExecuteNonQuery();
OutParam = sqlCommand.Parameters.Add("@OutParam", SqlDbType.BigInt).Value;
sqlCon.Close();
}
catch (Exception Ex)
{
//GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem occured during execution.", Ex.Message);
throw new Exception(Ex.Message, Ex);
}
finally
{
con.Close();
}
return OutParam;
}
public long SetAddDataXML(string uspName, DataSet ds, long ID )
{
SqlConnection con = null;
object obj;
string str;
SqlDataAdapter sqlDataAdapter;
obj = new object();
str = "";
obj = 0;
try
{
sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = new SqlCommand();
con = GlobalConn();
con.Open();
sqlDataAdapter.SelectCommand.Connection = con;
SqlCommand sqlCommand = sqlDataAdapter.SelectCommand;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = uspName;
MemoryStream memoryStream = new MemoryStream();
ds.WriteXml(memoryStream);
memoryStream.Position = 0;
StreamReader streamReader = new StreamReader(memoryStream);
str = streamReader.ReadToEnd();
sqlCommand.Parameters.AddWithValue("@AddData", str);
//sqlCommand.Parameters.AddWithValue("@MasterType", mstType);
sqlCommand.Parameters["@Outparam"].Direction = ParameterDirection.Output;
sqlCommand.ExecuteNonQuery();
obj = sqlCommand.Parameters.Add("@Outparam", SqlDbType.BigInt).Value;
}
catch (Exception Ex)
{
//GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem occured during execution. Unable to save data.", Ex.Message);
throw new Exception(Ex.Message, Ex);
}
finally
{
con.Close();
}
return Convert.ToInt64(obj);
}
public void ExecuteSQLQuery(string sStr)
{
SqlConnection con = null;
try
{
SqlCommand sqlCommand = new SqlCommand();
con = GlobalConn();
con.Open();
sqlCommand.Connection = con;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = sStr;
sqlCommand.ExecuteNonQuery();
}
catch (Exception Ex)
{
//GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem occured during execution. Unable to do the operation.", Ex.Message);
throw new Exception(Ex.Message, Ex);
}
finally
{
con.Close();
}
}
public int ExecuteNonQueryWithReturn(string uspName, string[] paramArray, object[] valueArray)
{
SqlConnection sqlCon = null;
try
{
int result = 0;
SqlCommand sqlCommand = new SqlCommand();
sqlCon = GlobalConn();
sqlCon.Open();
sqlCommand.Connection = sqlCon;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = uspName;
int i = 0;
while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
{
if (paramArray[i] != null)
{
sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
}
i++;
}
SqlParameter myParm = sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
sqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;
sqlCommand.ExecuteNonQuery();
result = (Int32)sqlCommand.Parameters["@ReturnValue"].Value;
return result;
}
catch (Exception Ex)
{
throw new Exception(Ex.Message, Ex);
}
finally
{
sqlCon.Close();
}
}
public int ExecuteNonQueryWithReturn(string uspName, string param, System.Data.SqlTypes.SqlXml value)
{
SqlConnection sqlCon = null;
try
{
int result = 0;
SqlCommand sqlCommand = new SqlCommand();
sqlCon = GlobalConn();
sqlCon.Open();
sqlCommand.Connection = sqlCon;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = uspName;
sqlCommand.Parameters.Add(param, SqlDbType.Xml);
sqlCommand.Parameters[param].Value = value.Value.Replace(" xmlns=\"http://tempuri.org/\"", "");
SqlParameter myParm = sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
sqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;
sqlCommand.ExecuteNonQuery();
result = (Int32)sqlCommand.Parameters["@ReturnValue"].Value;
return result;
}
catch (Exception Ex)
{
throw new Exception(Ex.Message, Ex);
}
finally
{
sqlCon.Close();
}
}
public int ExecuteNonQueryWithSaveXML(string uspName, string[] paramArray, object[] valueArray, string xmlparam, System.Data.SqlTypes.SqlXml value)
{
SqlConnection sqlCon = null;
try
{
int result = 0;
SqlCommand sqlCommand = new SqlCommand();
sqlCon = GlobalConn();
sqlCon.Open();
sqlCommand.Connection = sqlCon;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = uspName;
int i = 0;
while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
{
if (paramArray[i] != null)
{
sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
}
i++;
}
sqlCommand.Parameters.Add(xmlparam, SqlDbType.Xml);
sqlCommand.Parameters[xmlparam].Value = value.Value.Replace(" xmlns=\"http://tempuri.org/\"", "");
SqlParameter myParm = sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
sqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;
sqlCommand.ExecuteNonQuery();
result = (Int32)sqlCommand.Parameters["@ReturnValue"].Value;
return result;
}
catch (Exception Ex)
{
throw new Exception(Ex.Message, Ex);
}
finally
{
sqlCon.Close();
}
}
public int ExecuteSPWithReturn(string uspName)
{
SqlConnection con = null;
int result = 0;
try
{
SqlCommand sqlCommand = new SqlCommand();
con = GlobalConn();
con.Open();
sqlCommand.Connection = con;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = uspName;
sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
sqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;
sqlCommand.ExecuteNonQuery();
result = (Int32)sqlCommand.Parameters["@ReturnValue"].Value;
}
catch (Exception Ex)
{
//GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem occured during execution.", Ex.Message);
throw new Exception(Ex.Message, Ex);
}
finally
{
con.Close();
}
return result;
}
public int ExecuteNonQuery(string uspName, string[] paramArray, object[] valueArray)
{
SqlConnection sqlCon = null;
try
{
int result=0;
SqlCommand sqlCommand = new SqlCommand();
sqlCon= GlobalConn();
sqlCon.Open();
sqlCommand.Connection = sqlCon;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = uspName;
int i = 0;
while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
{
if (paramArray[i] != null)
{
sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
}
i++;
}
result = sqlCommand.ExecuteNonQuery();
sqlCon.Close();
return result;
}
catch (Exception Ex)
{
throw new Exception(Ex.Message, Ex);
}
finally
{
sqlCon.Close();
}
}
}
}