public DTReturnClass ExecProcRSParams(string procname, SP_Parameters p)
{
DTReturnClass outcome = new DTReturnClass(true);
DataTable dt = new DataTable();
SqlCommand cmd = null;
SqlDataAdapter da = null;
try{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd = new SqlCommand();
if (commandtimeout > 0)
{
cmd.CommandTimeout = commandtimeout;
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procname;
cmd.Connection = conn;
if (p != null)
{
foreach (SqlParameter objparam1 in p)
{
cmd.Parameters.Add(objparam1);
}
}
da = new SqlDataAdapter(cmd);
da.Fill(dt);
da.Dispose();
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
outcome.Datatable = dt;
} catch (Exception ex) {
outcome.Success = false;
outcome.Message = "An query Failed. Please see logs for exact error";
outcome.Techmessage = "ExecProcRSParams error. Procedure is[" + procname + "] Error:[" + ex.ToString() + "]";
} finally {
if (cmd != null)
{
cmd.Dispose();
}
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
return(outcome);
}
public ReturnClass GetStringScalarParams(string procname, SP_Parameters p)
{
ReturnClass outcome = new ReturnClass(true);
SqlCommand cmd = null;
// DO NOT SPECIFY OUTPUT PARAMS WITHIN p.
// All procs must have output param named @out
string outy = "";
try {
cmd = new SqlCommand(procname, conn);
if (commandtimeout > 0)
{
cmd.CommandTimeout = commandtimeout;
}
cmd.CommandType = CommandType.StoredProcedure;
if (p != null)
{
foreach (SqlParameter objparam1 in p)
{
cmd.Parameters.Add(objparam1);
}
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.ExecuteNonQuery();
outy = cmd.Parameters[cmd.Parameters.Count - 1].Value.ToString();
} catch (Exception ex) {
outcome.Success = false;
outcome.Message = "An GetStringScalarParams query Failed. Please see logs for exact error";
outcome.Techmessage = "GetStringScalarParams error. Procedure is[" + procname + "] Error:[" + ex.ToString() + "]";
} finally {
if (cmd != null)
{
cmd.Dispose();
}
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
if (outcome.Success)
{
outcome.Message = outy;
}
return(outcome);
}
public ReturnClass ExecProcIntResultParams(string procname, SP_Parameters p)
{
ReturnClass outcome = new ReturnClass(true);
SqlCommand cmd = null;
int outy = 0;
int i = 0;
try{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd = new SqlCommand();
if (commandtimeout > 0)
{
cmd.CommandTimeout = commandtimeout;
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procname;
cmd.Connection = conn;
if (p != null)
{
foreach (SqlParameter objparam1 in p)
{
cmd.Parameters.Add(objparam1);
}
}
i = cmd.ExecuteNonQuery();
outy = (int)cmd.Parameters[cmd.Parameters.Count - 1].Value;
} catch (Exception ex) {
outcome.Success = false;
outcome.Message = "An ExecProcIntResultParams query Failed. Please see webmaster for exact error";
outcome.Techmessage = "ExecProcIntResultParams error. Procedure is[" + procname + "] Error:[" + ex.ToString() + "]";
} finally {
if (cmd != null)
{
cmd.Dispose();
}
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
outcome.Intvar = outy;
return(outcome);
}
public ReturnClass ExecSqlParams(string q, SP_Parameters p)
{
ReturnClass outcome = new ReturnClass(true);
SqlCommand cmd = null;
int results = 0;
try {
cmd = new SqlCommand();
if (commandtimeout > 0)
{
cmd.CommandTimeout = commandtimeout;
}
cmd.Connection = conn;
cmd.CommandText = q;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
if (p != null)
{
foreach (SqlParameter objparam1 in p)
{
cmd.Parameters.Add(objparam1);
}
}
results = cmd.ExecuteNonQuery();
} catch (Exception ex) {
outcome.Success = false;
outcome.Message = "An update query Failed. Please see logs for exact error";
outcome.Techmessage = "ExecSqlParams error. Query is[" + q + "] Error:[" + ex.ToString() + "]";
results = -1;
} finally {
if (cmd != null)
{
cmd.Dispose();
}
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
outcome.Intvar = results;
return(outcome);
}
public ReturnClass GetDoubleScalarParams(string Sql, SP_Parameters p)
{
ReturnClass outcome = new ReturnClass(true);
double returnvalue = -1;
DataTable dt = new DataTable();
SqlCommand cmd = null;
SqlDataAdapter da = null;
try {
cmd = new SqlCommand();
if (commandtimeout > 0)
{
cmd.CommandTimeout = commandtimeout;
}
cmd.Connection = conn;
cmd.CommandText = Sql;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
if (p != null)
{
foreach (SqlParameter objparam1 in p)
{
cmd.Parameters.Add(objparam1);
}
}
da = new SqlDataAdapter(cmd);
da.Fill(dt);
da.Dispose();
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
returnvalue = Convert.ToDouble(dt.Rows[i][0]);
}
}
} catch (Exception ex) {
outcome.Success = false;
outcome.Message = "A query Failed. Please see logs for exact error";
outcome.Techmessage = "GetIntScalarParams error. Query is[" + Sql + "] Error:[" + ex.ToString() + "]";
} finally {
if (cmd != null)
{
cmd.Dispose();
}
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
if (outcome.Success)
{
outcome.Doublevar = returnvalue;
}
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 {
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);
}
} catch (Exception ex) {
outcome.Success = false;
outcome.Message = ex.Message;
}
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
if (outcome.Success)
{
try {
RowsAffected = cmd.ExecuteNonQuery();
} catch (Exception ex) {
outcome.Success = false;
outcome.Message = ex.Message;
}
}
try {
imgStream.Close();
} catch (Exception ex) {
}
return(outcome);
}
