//Create a .cs file to access common method
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;// this Microsoft.ApplicationBlocks.Data.dll file shou ld be downloaded
using Common;
using Common.Settings;
using Common.Exceptions;
namespace Common.Data
{
public class BaseDataAccess
{
private const string CONNECTION_STRING_PARAM = "ConnectionString";
private static string _connectionString = ConfigSettings.GetProperty(CONNECTION_STRING_PARAM);
private string _commandText;
private string _spName;
private SqlParameter[] _sqlParameter;
private CommandType _commandType;
private SqlDataReader _reader;
private int _result;
public BaseDataAccess()
{
//
}
public BaseDataAccess(string commandText)
{
_commandText = commandText;
}
public BaseDataAccess(string spName, SqlParameter[] inputParam)
{
_spName = spName;
_sqlParameter = inputParam;
}
public string getConnectionString()
{
return _connectionString;
}
public void setConnectionString(string connectionString)
{
_connectionString = connectionString;
}
public string getCommandText()
{
return _commandText;
}
public void setCommandText(string commandText)
{
_commandText = commandText;
}
public string getStoredProcedureName()
{
return _spName;
}
public void setStoredProcedureName(string spName)
{
_spName = spName;
}
public SqlParameter[] getInputParam()
{
return _sqlParameter;
}
public void setParam(SqlParameter[] sqlParameter)
{
_sqlParameter = sqlParameter;
}
public CommandType getCommandType()
{
return _commandType;
}
public void setCommandType(CommandType commandType)
{
_commandType = commandType;
}
// Read
public SqlDataReader read(string sqlCommandText)
{
try
{
_reader = SqlHelper.ExecuteReader(_connectionString, CommandType.Text, sqlCommandText);
}
catch (Exception ex)
{
AppException.HandleException(ex);
}
return _reader;
}
public StoredProcedureDataReader read(string storedProcedureName, SqlParameter[] sqlParameter)
{
StoredProcedureDataReader _spreader = new StoredProcedureDataReader();
try
{
_spreader = getStoredProcedureDataReader(storedProcedureName, sqlParameter);
}
catch (Exception ex)
{
AppException.HandleException(ex);
}
return _spreader;
}
public StoredProcedureDataReader Execute(string storedProcedureName, SqlParameter[] sqlParameter)
{
StoredProcedureDataReader _spreader = new StoredProcedureDataReader();
try
{
_spreader = getStoredProcedureDataReader(storedProcedureName, sqlParameter);
}
catch (Exception ex)
{
AppException.HandleException(ex);
}
return _spreader;
}
// Insert
public int insert(string sqlCommandText)
{
try
{
_result = SqlHelper.ExecuteNonQuery(_connectionString, CommandType.Text, sqlCommandText);
}
catch (Exception ex)
{
AppException.HandleException(ex);
}
return _result;
}
public int insert(string spName, SqlParameter[] sqlParameter)
{
try
{
_result = SqlHelper.ExecuteNonQuery(_connectionString, CommandType.StoredProcedure, spName, sqlParameter);
}
catch (Exception ex)
{
AppException.HandleException(ex);
}
return _result;
}
// Update
public int update(string sqlCommandText)
{
try
{
_result = SqlHelper.ExecuteNonQuery(_connectionString, CommandType.Text, sqlCommandText);
}
catch (Exception ex)
{
AppException.HandleException(ex);
}
return _result;
}
public int update(string spName, SqlParameter[] sqlParameter)
{
try
{
_result = SqlHelper.ExecuteNonQuery(_connectionString, CommandType.StoredProcedure, spName, sqlParameter);
}
catch (Exception ex)
{
AppException.HandleException(ex);
}
return _result;
}
// Delete
public int delete(string sqlCommandText)
{
try
{
_result = SqlHelper.ExecuteNonQuery(_connectionString, CommandType.Text, sqlCommandText);
}
catch (Exception ex)
{
AppException.HandleException(ex);
}
return _result;
}
public int delete(string spName, SqlParameter[] sqlParameter)
{
try
{
_result = SqlHelper.ExecuteNonQuery(_connectionString, CommandType.StoredProcedure, spName, sqlParameter);
}
catch (Exception ex)
{
AppException.HandleException(ex);
}
return _result;
}
// This method will return all the parameters defind for the stored procedure
public SqlParameter[] getSpParameterSet(string spName)
{
try
{
_sqlParameter = SqlHelperParameterCache.GetSpParameterSet(_connectionString, spName);
}
catch (Exception ex)
{
AppException.HandleException(ex);
}
return _sqlParameter;
}
// This method is written to read the output parameters. SQL Helper class is not returning the
// output parameters.
private StoredProcedureDataReader getStoredProcedureDataReader(string spName, SqlParameter[] sqlParameter)
{
StoredProcedureDataReader _spDatareader = new StoredProcedureDataReader();
SqlConnection cnn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
try
{
cmd.Connection = cnn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName;
if (sqlParameter != null)
{
foreach (SqlParameter param in sqlParameter)
{
cmd.Parameters.Add(param);
}
}
cnn.Open();
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
_spDatareader.setSqlDataReader(dr);
if (sqlParameter != null)
_spDatareader.setSqlParameter(sqlParameter);
}
catch (Exception ex)
{
AppException.HandleException(ex);
}
finally
{
cmd.Dispose();
}
return _spDatareader;
}
public class StoredProcedureDataReader
{
SqlDataReader _reader;
SqlParameter[] _sqlParameter;
public SqlDataReader getSqlDataReader()
{
return _reader;
}
public void setSqlDataReader(SqlDataReader reader)
{
_reader = reader;
}
public SqlParameter[] getSqlParameter()
{
return _sqlParameter;
}
public void setSqlParameter(SqlParameter[] sqlParameter)
{
_sqlParameter = sqlParameter;
}
}
public SqlDataReader executeWithTransaction(string spName, SqlParameter[] sqlParameter, SqlTransaction transaction)
{
try
{
_reader = SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName, sqlParameter);
}
catch (Exception ex)
{
AppException.HandleException(ex);
}
return _reader;
}
public DataTable GetRecords(string storedProcedureName, SqlParameter[] parameters)
{
SqlConnection sqlCon = new SqlConnection(_connectionString);
DataSet dataSetRecord = new DataSet();
SqlCommand sqlCmd = new SqlCommand();
SqlDataAdapter dataAdapter;
sqlCmd.Connection = sqlCon;
sqlCmd.CommandText = storedProcedureName;
sqlCmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter param in parameters)
{
sqlCmd.Parameters.Add(param);
}
try
{
dataAdapter = new SqlDataAdapter(sqlCmd);
dataAdapter.Fill(dataSetRecord, "Result");
}
catch (Exception ex)
{
AppException.HandleException(1, ex.Message, ex);
}
finally
{
sqlCon.Close();
}
return dataSetRecord.Tables["Result"];
}
}
}
//After creating above .cs file add the below code to perform CRUD operation.
public void insertUpdate()
{
StoredProcedureDataReader reader = new StoredProcedureDataReader();
try
{
_sqlParameter = getSpParameterSet("ProcName");
_sqlParameter[0].Value ="Value";
reader = Execute("ProcName", _sqlParameter);
_reader = reader.getSqlDataReader();
if (_reader == null)
return string.empty;
if (_reader.HasRows)
{
if (_reader.Read())
{
string value = Convert.ToInt64(_reader.GetValue(0));
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (_reader != null)
{
_reader.Close();
_reader.Dispose();
}
reader = null;
_sqlParameter = null;
}
}
public void getDetailList()
{
List<DoctorShare> lstDoctorShare = new List<DoctorShare>();
StoredProcedureDataReader reader = new StoredProcedureDataReader();
DoctorShare objDoctorShare = null;
try
{
_sqlParameter = getSpParameterSet("Proc Name");
_sqlParameter[0].Value = "value";
reader = Execute("Proc Name", _sqlParameter);
_reader = reader.getSqlDataReader();
if (_reader == null)
return lstDoctorShare;
if (_reader.HasRows)
{
while (_reader.Read())
{
string value = Convert.ToInt64(_reader.GetValue(0));
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
_reader.Close();
_reader.Dispose();
reader = null;
_sqlParameter = null;
}
}
public int delete()
{
int _result = 0;
try
{
_sqlParameter = getSpParameterSet("ProcName");
_sqlParameter[0].Value = "value";
_result = insert("ProcName", _sqlParameter);
}
}
catch (Exception ex)
{
AppException.HandleException(ex);
}
finally
{
_sqlParameter = null;
}
return _result;
}
No comments:
Post a Comment