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()); } }