public TResult ExecScalarValued <TResult>(string sqlCommandString, params SqlParameter[] inputParametrs)
{
try
{
var result = default(TResult);
using (var sqlConnection = new SqlConnection(GetConnectionString))
{
sqlConnection.Open();
using (var sqlCommand = new SqlCommand(sqlCommandString, sqlConnection))
{
sqlCommand.CommandType = CommandType.Text;
if (inputParametrs != null)
{
sqlCommand.Parameters.AddRange(inputParametrs);
}
result = (TResult)sqlCommand.ExecuteScalar();
}
}
return(result);
}
catch (Exception ex)
{
var serviceDataError = new DataErrorDto();
serviceDataError.ErrorMessage = ex.Message;
serviceDataError.ErrorDetails = ex.ToString();
log.Error($"Type Error: {serviceDataError.ErrorMessage}\n ErrorDetails {ex.ToString()}");
throw new FaultException <DataErrorDto>(serviceDataError, ex.ToString());
}
}
public TModel ExecProcGetModel <TModel>(string sqlCommandString, params SqlParameter[] inputParametrs)
where TModel : class, new()
{
var result = default(TModel);
try
{
using (var sqlConnection = new SqlConnection(GetConnectionString))
{
sqlConnection.Open();
using (var sqlCommand = new SqlCommand(sqlCommandString, sqlConnection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.AddRange(inputParametrs);
using (var dataTable = new DataTable())
{
dataTable.Load(sqlCommand.ExecuteReader());
result = dataTable.ConvertToModel <TModel>();
}
}
}
return(result);
}
catch (Exception ex)
{
var serviceDataError = new DataErrorDto();
serviceDataError.ErrorMessage = ex.Message;
serviceDataError.ErrorDetails = ex.ToString();
log.Error($"Type Error: {serviceDataError.ErrorMessage}\n ErrorDetails {ex.ToString()}");
throw new FaultException <DataErrorDto>(serviceDataError, ex.ToString());
}
}
public TArray[] GetArrayByColumn <TArray>(string sqlCommandString, string columnName, params SqlParameter[] inputParametrs)
{
var result = default(TArray[]);
try
{
using (var sqlConnection = new SqlConnection(GetConnectionString))
{
sqlConnection.Open();
using (var sqlCommand = new SqlCommand(sqlCommandString, sqlConnection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
if (inputParametrs != null)
{
sqlCommand.Parameters.AddRange(inputParametrs);
}
using (var dataTable = new DataTable())
{
dataTable.Load(sqlCommand.ExecuteReader());
result = dataTable.ConvertToArrayByColumn <TArray>(columnName);
}
}
}
return(result);
}
catch (Exception ex)
{
var serviceDataError = new DataErrorDto();
serviceDataError.ErrorMessage = ex.Message;
serviceDataError.ErrorDetails = ex.ToString();
log.Error($"Type Error: {serviceDataError.ErrorMessage}\n ErrorDetails {ex.ToString()}");
throw new FaultException <DataErrorDto>(serviceDataError, ex.ToString());
}
}
public void ExecNonQuery <TModel>(string sqlCommandString, TModel objectmodel, params SqlParameter[] parametrs)
{
try
{
var dataTable = ConvertToDataTable(objectmodel);
using (var sqlConnection = new SqlConnection(GetConnectionString))
{
sqlConnection.Open();
using (var sqlCommand = new SqlCommand(sqlCommandString, sqlConnection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
var param = sqlCommand.Parameters.AddWithValue("@dt", dataTable);
if (parametrs != null)
{
sqlCommand.Parameters.AddRange(parametrs);
}
param.SqlDbType = SqlDbType.Structured;
sqlCommand.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
var serviceDataError = new DataErrorDto();
serviceDataError.ErrorMessage = ex.Message;
serviceDataError.ErrorDetails = ex.ToString();
log.Error($"Type Error: {serviceDataError.ErrorMessage}\n ErrorDetails {ex.ToString()}");
throw new FaultException <DataErrorDto>(serviceDataError, ex.ToString());
}
}
public void ExecNonQuery <TOut>(string sqlCommandString, string outPutName, out TOut outPutValue, params SqlParameter[] parametrs)
{
try
{
using (var sqlConnection = new SqlConnection(GetConnectionString))
{
sqlConnection.Open();
using (var sqlCommand = new SqlCommand(sqlCommandString, sqlConnection))
{
var outPutParametr = sqlCommand.Parameters.Add($"@{outPutName}", SqlDbTypeHelper.GetDbType(typeof(TOut)));
outPutParametr.Direction = ParameterDirection.Output;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.AddRange(parametrs);
sqlCommand.ExecuteNonQuery();
outPutValue = (TOut)outPutParametr.Value;
}
}
}
catch (Exception ex)
{
var serviceDataError = new DataErrorDto();
serviceDataError.ErrorMessage = ex.Message;
serviceDataError.ErrorDetails = ex.ToString();
log.Error($"Type Error: {serviceDataError.ErrorMessage}\n ErrorDetails {ex.ToString()}");
throw new FaultException <DataErrorDto>(serviceDataError, ex.ToString());
}
}
public void ExecNonQuery <TModel, TOut>(string sqlCommandString, TModel objectmodel, string outPutName,
out TOut outPutValue) where TModel : class
{
try
{
var dataTable = ConvertToDataTable(objectmodel);
using (var sqlConnection = new SqlConnection(GetConnectionString))
{
sqlConnection.Open();
using (var sqlCommand = new SqlCommand(sqlCommandString, sqlConnection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
var returnVal = sqlCommand.Parameters.Add($"@{outPutName}", SqlDbTypeHelper.GetDbType(typeof(TOut)));
returnVal.Direction = ParameterDirection.Output;
var param = sqlCommand.Parameters.AddWithValue("@dt", dataTable);
param.SqlDbType = SqlDbType.Structured;
sqlCommand.ExecuteNonQuery();
outPutValue = (TOut)returnVal.Value;
}
}
}
catch (Exception ex)
{
var serviceDataError = new DataErrorDto();
serviceDataError.ErrorMessage = ex.Message;
serviceDataError.ErrorDetails = ex.ToString();
log.Error($"Type Error: {serviceDataError.ErrorMessage}\n ErrorDetails {ex.ToString()}");
throw new FaultException <DataErrorDto>(serviceDataError, ex.ToString());
}
}
public TModel[] ExecProcGetModels <TModel, TOut>(string sqlCommandString, string outPutName, out TOut outPutValue,
params SqlParameter[] inputParametrs) where TModel : class, new()
{
var result = default(TModel[]);
try
{
using (var sqlConnection = new SqlConnection(GetConnectionString))
{
sqlConnection.Open();
using (var sqlCommand = new SqlCommand(sqlCommandString, sqlConnection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
var outPutParametr = sqlCommand.Parameters.Add($"@{outPutName}", SqlDbTypeHelper.GetDbType(typeof(TOut)));
outPutParametr.Direction = ParameterDirection.Output;
sqlCommand.Parameters.AddRange(inputParametrs);
using (var dataTable = new DataTable())
{
dataTable.Load(sqlCommand.ExecuteReader());
result = dataTable.ConvertToArrayOfModels <TModel>();
outPutValue = (TOut)outPutParametr.Value;
}
return(result);
}
}
}
catch (Exception ex)
{
var serviceDataError = new DataErrorDto();
serviceDataError.ErrorMessage = ex.Message;
serviceDataError.ErrorDetails = ex.ToString();
log.Error($"Type Error: {serviceDataError.ErrorMessage}\n ErrorDetails {ex.ToString()}");
throw new FaultException <DataErrorDto>(serviceDataError, ex.ToString());
}
}
public void ExecNonQuery(string sqlCommandString, params SqlParameter[] parametrs)
{
try
{
using (var sqlConnection = new SqlConnection(GetConnectionString))
{
sqlConnection.Open();
using (var sqlCommand = new SqlCommand(sqlCommandString, sqlConnection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.AddRange(parametrs);
sqlCommand.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
var serviceDataError = new DataErrorDto();
serviceDataError.ErrorMessage = ex.Message;
serviceDataError.ErrorDetails = ex.ToString();
log.Error($"Type Error: {serviceDataError.ErrorMessage}\n ErrorDetails {ex.ToString()}");
throw new FaultException <DataErrorDto>(serviceDataError, ex.ToString());
}
}