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