// TODO https://docs.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlconnection.begintransaction?view=sqlclient-dotnet-core-1.1
private async Task <ReturnClass> ExecSqlAsyncMethods(string strQuery, SP_Parameters p, bool isProc)
{
ReturnClass outcome = new ReturnClass(true);
int result = 0;
string errormsg = "";
bool isError = false;
using (var conn = new SqlConnection(connectionstring)) {
await conn.OpenAsync();
using (var tran = conn.BeginTransaction()) {
using (var command = new SqlCommand(strQuery, conn, tran)) {
command.CommandTimeout = commandtimeout;
if (isProc)
{
command.CommandType = CommandType.StoredProcedure;
}
else
{
command.CommandType = CommandType.Text;
}
if (p != null)
{
foreach (SqlParameter objparam1 in p)
{
command.Parameters.Add(objparam1);
}
}
try {
result = await command.ExecuteNonQueryAsync();
} catch (Exception ex) {
errormsg = ex.ToString();
isError = true;
tran.Rollback();
throw;
}
tran.Commit();
}
}
}
if (isError)
{
outcome.Success = false;
outcome.Message = "A query Failed. Please see logs for exact error";
outcome.Techmessage = "GetDataTable error. Query is[" + strQuery + "] Error:[" + errormsg + "]";
}
outcome.Intvar = result;
return(outcome);
}
private ReturnClass ExecSqlProcVoidMethods(string strQuery, bool isProc, SP_Parameters p)
{
ReturnClass outcome = new ReturnClass(true);
int results = 0;
try {
using (var conn = new SqlConnection(connectionstring)) {
conn.Open();
using (var command = new SqlCommand(strQuery, conn)) {
command.CommandTimeout = commandtimeout;
if (isProc)
{
command.CommandType = CommandType.StoredProcedure;
}
else
{
command.CommandType = CommandType.Text;
}
if (p != null)
{
foreach (SqlParameter objparam1 in p)
{
command.Parameters.Add(objparam1);
}
}
results = command.ExecuteNonQuery();
}
}
} catch (Exception ex) {
outcome.SetFailureMessage("An update query Failed. Please see logs for exact error", "ExecSql error. Query is[" + strQuery + "] Error:[" + ex.Message + "]");
results = -1;
}
outcome.Intvar = results;
return(outcome);
}
private ReturnClass SyncScalarMethods(string strQuery, SP_Parameters p, bool isProc, ScalarType sctype)
{
ReturnClass outcome = new ReturnClass(true);
try {
using (var conn = new SqlConnection(connectionstring)) {
conn.Open();
using (var command = new SqlCommand(strQuery, conn)) {
command.CommandTimeout = commandtimeout;
if (isProc)
{
command.CommandType = CommandType.StoredProcedure;
}
else
{
command.CommandType = CommandType.Text;
}
if (p != null)
{
foreach (SqlParameter objparam1 in p)
{
command.Parameters.Add(objparam1);
}
}
try {
using (var reader = command.ExecuteReader()) {
if (reader.Read())
{
switch (sctype)
{
case ScalarType.String:
outcome.Message = reader[0].ToString();
break;
case ScalarType.Int:
outcome.Intvar = (int)reader[0];
break;
case ScalarType.Long:
outcome.Longvar = (long)reader[0];
break;
case ScalarType.Double:
outcome.Doublevar = (double)reader[0];
break;
}
}
}
} catch (Exception ex) {
outcome.SetFailureMessage("A query Failed. Please see logs for exact error", "SyncScalarMethods Error Query is[" + strQuery + "] Error:[" + ex.ToString() + "]");
}
}
}
} catch (SqlException ex) {
outcome.SetFailureMessage("A query Failed. Please see logs for exact error", "SyncScalarMethods Error Query is[" + strQuery + "] Sql Error:[" + ex.ToString() + "]");
} catch (Exception ex) {
outcome.SetFailureMessage("A query Failed. Please see logs for exact error", "SyncScalarMethods Error Query is[" + strQuery + "] Error:[" + ex.ToString() + "]");
}
return(outcome);
}
private async Task <ReturnClass> GetScalarAsyncMethods(string strQuery, SP_Parameters p, bool isProc, ScalarType sctype)
{
ReturnClass outcome = new ReturnClass(true);
string errormsg = "";
bool isError = false;
using (var conn = new SqlConnection(connectionstring)) {
await conn.OpenAsync();
using (var command = new SqlCommand(strQuery, conn)) {
command.CommandTimeout = commandtimeout;
if (isProc)
{
command.CommandType = CommandType.StoredProcedure;
}
else
{
command.CommandType = CommandType.Text;
}
if (p != null)
{
foreach (SqlParameter objparam1 in p)
{
command.Parameters.Add(objparam1);
}
}
try {
using (var reader = await command.ExecuteReaderAsync()) {
if (await reader.ReadAsync())
{
switch (sctype)
{
case ScalarType.String:
outcome.Message = reader[0].ToString();
break;
case ScalarType.Int:
outcome.Intvar = (int)reader[0];
break;
case ScalarType.Long:
outcome.Longvar = (long)reader[0];
break;
case ScalarType.Double:
outcome.Doublevar = (double)reader[0];
break;
}
}
}
} catch (Exception ex) {
errormsg = ex.ToString();
isError = true;
}
}
}
if (isError)
{
outcome.Success = false;
outcome.Message = "A query Failed. Please see logs for exact error";
outcome.Techmessage = "GetDataTable error. Query is[" + strQuery + "] Error:[" + errormsg + "]";
}
return(outcome);
}
public async Task <ReturnClass> GetDoubleScalarProcParamsAsync(string strQuery, SP_Parameters p)
{
ReturnClass outcome = await GetScalarAsyncMethods(strQuery, p, true, ScalarType.Double);
return(outcome);
}
public async Task <ReturnClass> GetDoubleScalarProcAsync(string strQuery)
{
ReturnClass outcome = await GetScalarAsyncMethods(strQuery, null, true, ScalarType.Double);
return(outcome);
}
public ReturnClass UploadImageField(string filepath, string imagefieldparametername, string Sql)
{
ReturnClass outcome = new ReturnClass(true);
SP_Parameters p = new SP_Parameters();
SqlCommand cmd = null;
Stream imgStream = null;
FileInfo file = null;
byte[] imgBinaryData = null;
int RowsAffected = 0;
int filesize = 0;
int n = 0;
if (!imagefieldparametername.StartsWith("@"))
{
imagefieldparametername = "@" + imagefieldparametername;
}
if (!File.Exists(filepath))
{
outcome.SetFailureMessage("The file does not exist or is not accessible.");
}
if (outcome.Success)
{
try {
file = new FileInfo(filepath);
filesize = Convert.ToInt32(file.Length);
} catch (Exception ex) {
outcome.Success = false;
outcome.Message = ex.Message;
}
}
if (outcome.Success)
{
try {
imgStream = File.OpenRead(filepath);
imgBinaryData = new byte[filesize];
n = imgStream.Read(imgBinaryData, 0, filesize);
} catch (Exception ex) {
outcome.Success = false;
outcome.Message = ex.Message;
}
}
if (outcome.Success)
{
try {
using (var conn = new SqlConnection(connectionstring)) {
conn.Open();
cmd = new SqlCommand(Sql, conn);
if (commandtimeout > 0)
{
cmd.CommandTimeout = commandtimeout;
}
p.Add(imagefieldparametername, SqlDbType.Image, filesize, ParameterDirection.Input, imgBinaryData);
foreach (SqlParameter objparam1 in p)
{
cmd.Parameters.Add(objparam1);
}
RowsAffected = cmd.ExecuteNonQuery();
}
} catch (Exception ex) {
outcome.Success = false;
outcome.Message = ex.Message;
}
}
try {
imgStream.Close();
} catch (Exception ex) {
}
return(outcome);
}
public async Task <ReturnClass> GetLongScalarParamsAsync(string strQuery, SP_Parameters p)
{
ReturnClass outcome = await GetScalarAsyncMethods(strQuery, p, false, ScalarType.Long);
return(outcome);
}
public async Task <ReturnClass> GetLongScalarAsync(string strQuery)
{
ReturnClass outcome = await GetScalarAsyncMethods(strQuery, null, false, ScalarType.Long);
return(outcome);
}
public async Task <ReturnClass> ExecProcVoidParamsAsync(string procname, SP_Parameters p)
{
ReturnClass outcome = await ExecSqlAsyncMethods(procname, p, true);
return(outcome);
}
public async Task <ReturnClass> ExecProcVoidAsync(string procname)
{
ReturnClass outcome = await ExecSqlAsyncMethods(procname, null, true);
return(outcome);
}
public async Task <ReturnClass> ExecSqlParamsAsync(string strQuery, SP_Parameters p)
{
ReturnClass outcome = await ExecSqlAsyncMethods(strQuery, p, false);
return(outcome);
}
public async Task <ReturnClass> ExecSqlAsync(string strQuery)
{
ReturnClass outcome = await ExecSqlAsyncMethods(strQuery, null, false);
return(outcome);
}