//public static PagedDataSource GetPagedDatasource(string procedureName, SQLParameterList parameters, int pagesize)
//{
// DataTable data = GetDataTable(procedureName, parameters);
// if (data != null)
// {
// PagedDataSource pagedData = new PagedDataSource();
// pagedData.DataSource = data.DefaultView;
// pagedData.AllowPaging = true;
// pagedData.PageSize = pagesize;
// return pagedData;
// }
// else
// return null;
//}
public static DataTable GetDataTable(string procedureName, SQLParameterList parameters)
{
DataTable table = new DataTable();
SqlDataAdapter dataAdapter = null;
SqlCommand command = null;
SqlConnection connection = new SqlConnection(_connectionString);
try
{
connection.Open();
command = new SqlCommand(procedureName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(table);
return(table);
}
catch
{
return(null);
}
finally
{
connection.Dispose();
}
}
public static SqlDataReader GetDataReader(string procedureName, SQLParameterList parameters)
{
SqlDataReader reader = null;
SqlCommand command = null;
SqlConnection connection = new SqlConnection(_connectionString);
try
{
connection.Open();
command = new SqlCommand(procedureName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
return(reader);
}
catch (Exception ex)
{
connection.Dispose();
if (reader != null)
{
reader.Dispose();
}
throw ex;
return(null);
}
}
public static object ExecuteScalar(string procedureName, SQLParameterList parameters)
{
SqlCommand command = null;
SqlConnection connection = new SqlConnection(_connectionString);
try
{
connection.Open();
command = new SqlCommand(procedureName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
return(command.ExecuteScalar());
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
public static DataSet GetDataSet(string procedureName, SQLParameterList parameters)
{
DataSet dataSet = new DataSet();
SqlDataAdapter dataAdapter = null;
SqlCommand command = null;
SqlConnection connection = new SqlConnection(_connectionString);
try
{
connection.Open();
command = new SqlCommand(procedureName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataSet);
return(dataSet);
}
catch (Exception ex)
{
return(null);
//Need to watch out for catching errors here (lets log it at some point)
//for instance any errors in Pete's CLR's
}
finally
{
connection.Dispose();
}
}