patterncsharpMinor
General select statement to stored procedures
Viewed 0 times
storedproceduresstatementgeneralselect
Problem
I'm trying to write a general function to several specific stored procedures, but I'm afraid that it will cause errors that I don't see now. The function is to get the name of the stored procedure as a string, and get a list of objects that contain the parameters of the stored procedure. Does it look good to you?
public static DataTable SelectQuery(string storedProcedureName, List parameters)
{
DataTable dt = new DataTable();
OleDbConnection con = WorkerDB.GetConnection();
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = storedProcedureName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
for (int i = 0; i < parameters.Count; i++)
{
cmd.Parameters.AddWithValue("?", parameters[i]);
}
try
{
con.Open();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);
da.Dispose();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return dt;
}Solution
This is first refactored version of your code. I have changed following thing
Few more things needs to be thought like out param if you need.
- If any object provide dispose method , prefer using statement.
- Use dictionary rather than list of parameter as you have a possibility of specifying param names also
- Current implementation sticks only to datatable , you might need to think about the datareader.
- naming of variable should be explicit.
Few more things needs to be thought like out param if you need.
public static DataTable ExecuteDataTable(string queryName, CommandType commandType, Dictionary parameters=null)
{
using (OleDbConnection connection = WorkerDB.GetConnection())
{
using (var command=new OleDbCommand())
{
connection.Open();
command.CommandText = queryName;
command.CommandType = commandType;
command.Connection = connection;
if (parameters != null)
{
foreach (var parameter in parameters)
{
command.Parameters.AddWithValue(parameter.Key, parameter.Value);
}
}
var dataAdapter = new OleDbDataAdapter(command);
var dataTable = new DataTable();
dataAdapter.Fill(dataTable);
dataAdapter.Dispose();
return dataTable;
}
}
}Code Snippets
public static DataTable ExecuteDataTable(string queryName, CommandType commandType, Dictionary<string,object> parameters=null)
{
using (OleDbConnection connection = WorkerDB.GetConnection())
{
using (var command=new OleDbCommand())
{
connection.Open();
command.CommandText = queryName;
command.CommandType = commandType;
command.Connection = connection;
if (parameters != null)
{
foreach (var parameter in parameters)
{
command.Parameters.AddWithValue(parameter.Key, parameter.Value);
}
}
var dataAdapter = new OleDbDataAdapter(command);
var dataTable = new DataTable();
dataAdapter.Fill(dataTable);
dataAdapter.Dispose();
return dataTable;
}
}
}Context
StackExchange Code Review Q#59044, answer score: 5
Revisions (0)
No revisions yet.