While I am a firm believer in the Enterprise Application Blocks from Patterns and Practices (http://msdn.microsoft.com/practices) I was recently asked to produce a simple way for people to get data without the EAB. I know there are a million examples on the web but here is mine . . .
I start with a class I call DataHelper this guy is nothing more then a wrapper around the command object.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
public class DataHelper
{
private SqlCommand _command = null;
/// <summary>
/// The internal command
/// </summary>
public SqlCommand Command
{
get { return _command; }
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="cmdText">The Text of the query</param>
/// <param name="csKey">This is the string value to the name
/// of the connection string in the connectionstrings section
/// of the app/web config</param>
public DataHelper(string cmdText, string csKey)
{
_command = new SqlCommand(cmdText,
new SqlConnection(ConfigurationManager.ConnectionStrings[csKey] .ConnectionString));
}
/// <summary>
/// Add a parameter to the helper
/// </summary>
/// <param name="name">Name of the parameter</param>
/// <param name="value">Value of the parameter</param>
public void AddParameter(string name, object value)
{
_command.Parameters.Add(new SqlParameter(name, value));
}
/// <summary>
/// Get a dataset from the helper
/// </summary>
/// <returns></returns>
public DataSet GetDataSet()
{
DataSet ds = new DataSet();
using (SqlDataAdapter da = new SqlDataAdapter(_command))
{
da.Fill(ds);
}
return ds;
}
} |
Using the DataHelper is easy. As you can see in the sample code below the constructor takes in the sql statement and the key in the connection strings setting of the config file. This allows me to initialize the command. Next all the needed parameters can be added to the helper using the simplified add parameter method. Finally you call GetDataSet and get yourself some data!
1
2
3
4
|
string sql = "SELECT HumanResources.Department.* " + "FROM HumanResources.Department WHERE GroupName = @GroupName";
DataHelper dh = new DataHelper(sql, "AdventureWorksConnection");
dh.AddParameter("@GroupName", "Manufacturing");
DataSet ds = dh.GetDataSet(); |