1 class DBHelper
2 {
3 public class DBHelper
4 {
5 //TODO: Set this to read from the web.config when it is posted live.
6 /// <summary>
7 /// Database connection string.
8 /// </summary>
9 const string DBConnStr = @"Data Source=localhost\devsql;Initial Catalog=Helpdesk;Integrated Security=True";
10
11 /// <summary>
12 /// Returns filled dataset from stored procedure name and its parameters
13 /// </summary>
14 public static DataSet FillDataset(string Statement, SqlParameter[] Params, CommandType SQLCommandType)
15 {
16 SqlConnection myConnection = new SqlConnection(DBConnStr);
17 SqlDataAdapter myAdapter = new SqlDataAdapter();
18
19 myAdapter.SelectCommand = new SqlCommand(Statement, myConnection);
20 myAdapter.SelectCommand.CommandType = SQLCommandType;
21
22 // assign all parameters with its values
23 for (int i = 0; i < Params.Length; i++)
24 {
25 myAdapter.SelectCommand.Parameters.Add(Params[i]);
26 }
27
28 DataSet myDataSet = new DataSet();
29
30 myAdapter.Fill(myDataSet);
31
32 return myDataSet;
33 }
34
35 /// <summary>
36 /// Executes stored procedure with its parameters
37 /// </summary>
38 public static void ExecSQL(string Statement, SqlParameter[] Params, CommandType SQLCommandType)
39 {
40 SqlConnection myConnection = new SqlConnection(DBConnStr);
41 SqlCommand myCmd = new SqlCommand(Statement, myConnection);
42 myCmd.CommandType = SQLCommandType;
43
44 // assign all parameters with its values
45 for (int i = 0; i < Params.Length; i++)
46 {
47 myCmd.Parameters.Add(Params[i]);
48 }
49
50 try
51 {
52 myConnection.Open();
53 myCmd.ExecuteNonQuery();
54 }
55 finally
56 {
57 myConnection.Close();
58 }
59 }
60
61 /// <summary>
62 /// Executes stored procedure with its parameters
63 /// </summary>
64 public static int ExecScalarSQL(string Statement, SqlParameter[] Params, CommandType SQLCommandType)
65 {
66 int ReturnValue;
67
68 SqlConnection myConnection = new SqlConnection(DBConnStr);
69 SqlCommand myCmd = new SqlCommand(Statement, myConnection);
70 myCmd.CommandType = SQLCommandType;
71
72 // assign all parameters with its values
73 for (int i = 0; i < Params.Length; i++)
74 {
75 myCmd.Parameters.Add(Params[i]); ;
76 }
77
78 try
79 {
80 myConnection.Open();
81 ReturnValue = System.Convert.ToInt32(myCmd.ExecuteScalar());
82 }
83 finally
84 {
85 myConnection.Close();
86 }
87
88 return ReturnValue;
89 }
90
91 /// <summary>
92 /// Checks a dataset to make sure that it has at least 1 table with 1 row in it.
93 /// </summary>
94 public static bool DataSetHasRows(DataSet ds)
95 {
96 return ds.Tables.Count > 0 & ds.Tables[0].Rows.Count > 0;
97 }
98 }
99 }
100