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