/// <summary>
/// Executes parameterized stored procedure and returns a dataset object
/// </summary>
/// <param name="connectionstring"></param>
/// <param name="procedurename"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataSet ReturnDataSet(string connectionstring, string procedurename, params SqlParameter[] parameters)
{
DataSet dsResult = new DataSet();
SqlCommand mycomm = CreateSPCommand(connectionstring, procedurename, parameters);
try
{
mycomm.Connection.Open();
SqlDataAdapter da = new SqlDataAdapter(mycomm);
da.Fill(dsResult, "ResultDataSet");
return(dsResult);
}
catch (Exception exTrap)
{
DBAccessMethods.LogError(exTrap);
return(dsResult);
}
finally
{
mycomm.Connection.Close();
}
}
/// <summary>
/// Executes parameterized stored procedure and returns a data table object
/// </summary>
/// <param name="connectionstring"></param>
/// <param name="procedurename"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataTable ReturnTable(string connectionstring, string procedurename, params SqlParameter[] parameters)
{
DataTable returnvalue = new DataTable();
SqlCommand mycomm = CreateSPCommand(connectionstring, procedurename, parameters);
try
{
mycomm.Connection.Open();
SqlDataReader reader = mycomm.ExecuteReader();
returnvalue.Load(reader);
return(returnvalue);
}
catch (Exception exTrap)
{
DBAccessMethods.LogError(exTrap);
return(returnvalue);
}
finally
{
mycomm.Connection.Close();
}
}
/// <summary>
/// Executes parameterized stored procedure and returns single string
/// </summary>
/// <param name="connectionstring"></param>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static string ReturnString(string connectionstring, string procedureName, params SqlParameter[] parameters)
{
string returnvalue = "";
SqlCommand mycomm = CreateSPCommand(connectionstring, procedureName, parameters, parameters);
try
{
mycomm.Connection.Open();
returnvalue = (string)mycomm.ExecuteScalar();
return(returnvalue);
}
catch (Exception exTrap)
{
DBAccessMethods.LogError(exTrap);
returnvalue = "Error|" + exTrap.Message;
return(returnvalue); // = "";
}
finally
{
mycomm.Connection.Close();
}
}
/// <summary>
/// Executes parameterized stored procedure without returning a value
/// </summary>
/// <param name="connectionstring"></param>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
public static void noReturn(string connectionstring, string procedureName, params SqlParameter[] parameters)
{
SqlCommand mycomm = CreateSPCommand(connectionstring, procedureName, parameters);
try
{
mycomm.Connection.Open();
mycomm.ExecuteNonQuery();
HasError = false;
}
catch (Exception exTrap)
{
DBAccessMethods.LogError(exTrap);
HasError = true;
ErrorMessage = exTrap.Message;
//throw exTrap;
}
finally
{
mycomm.Connection.Close();
}
}
//public static byte ReturnByte(string connectionstring, string procedureName, params SqlParameter[] parameters)
//{
// //var returnvalue = new System.Byte();
// //byte[] returnvalue;
// byte[] returnvalue = new byte;
// SqlCommand mycomm = CreateSPCommand(connectionstring, procedureName, parameters);
// try
// {
// mycomm.Connection.Open();
// returnvalue = (byte[])mycomm.ExecuteScalar();
// return returnvalue;
// }
// catch (Exception exTrap)
// {
// DBAccessMethods.LogError(exTrap);
// //returnvalue = "Error|" + exTrap.Message;
// return returnvalue;// = "";
// }
// finally
// {
// mycomm.Connection.Close();
// }
//}
/// <summary>
/// Executes parameterized stored procedure and returns boolean value
/// </summary>
/// <param name="connectionstring"></param>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static Boolean ReturnBool(string connectionstring, string procedureName, params SqlParameter[] parameters)
{
//SqlConnection conn = new SqlConnection(connectionstring);
//SqlCommand comm = new SqlCommand();
//comm.Connection = conn;
//comm.CommandType = CommandType.StoredProcedure;
//comm.CommandText = procedureName;
//foreach (SqlParameter parameter in parameters)
//{
// comm.Parameters.Add(parameter);
//}
////return comm;
SqlCommand mycomm = CreateSPCommand(connectionstring, procedureName, parameters);
try
{
mycomm.Connection.Open();
if (mycomm.ExecuteScalar() != null)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception exTrap)
{
DBAccessMethods.LogError(exTrap);
return(false);
}
finally
{
mycomm.Connection.Close();
}
}
/// <summary>
/// Executes a parameterized stored procedure and returns a single int
/// </summary>
/// <param name="connectionstring"></param>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static Int32 ReturnInt(string connectionstring, string procedureName, params SqlParameter[] parameters)
{
Int32 returnvalue;
SqlCommand mycomm = CreateSPCommand(connectionstring, procedureName, parameters);
try
{
mycomm.Connection.Open();
returnvalue = Convert.ToInt32(mycomm.ExecuteScalar());
return(returnvalue);
}
catch (Exception exTrap)
{
DBAccessMethods.LogError(exTrap);
//returnvalue = exTrap.Message;
//return returnvalue;
return(-1);
}
finally
{
mycomm.Connection.Close();
}
}
