DBHelper Database Help class, DBHelper Database

DBHelper Database Help class, DBHelper Database

1 /// <summary> 2 // Database Help class 3 /// <author> Devin </author> 4 /// </summary> 5 public sealed class DBHelper 6 {7 private DBHelper () {} 8 9 // <summary> 10 // database connection string 11 /// </summary> 12 private static string connStr = System. configuration. configurationManager. appSettings ["connStr"]. toString (); 13 14 15 /// <summary> 16 // a valid database connection object 17 /// </summary> 18 private static SqlConnection _ MyConnec Tion; 19 public static SqlConnection MyConnection 20 {21 get 22 {23 if (_ MyConnection = null) 24 {25 _ MyConnection = new SqlConnection (connStr ); 26} 27 return _ MyConnection; 28} 29} 30 31 32 // <summary> 33 // The number of affected rows is returned 34 /// </summary> 35 /// <param name = "plain text"> SQL statement </param> 36 /// <param name = "commandParameters"> parameter set </param> 37 /// <returns> </returns> 38 public static int MyExecuteNonQu Ery (string plain text, params SqlParameter [] commandParameters) 39 {40 int result = 0; 41 SqlCommand cmd = new SqlCommand (); 42 SqlConnection conn = MyConnection; 43 try 44 {45 PrepareCommand (cmd, conn, null, plain text, commandParameters); 46 result = cmd. executeNonQuery (); 47} 48 catch (SqlException ex) 49 {50 throw new Exception (ex. message); 51} 52 finally 53 {54 cmd. parameters. clear (); 55} 56 Return result; 57} 58 59 60 61 // <summary> 62 // return DataSet 63 // </summary> 64 // <param name = "plain text"> SQL statement </param> 65 // <param name = "commandParameters"> parameter set </param> 66 // <returns> </returns> 67 public static DataSet MyExecuteDataset (string plain text, params SqlParameter [] commandParameters) 68 {69 DataSet ds = new DataSet (); 70 SqlConnection conn = MyConnection; 71 SqlCommand cmd = new S QlCommand (); 72 PrepareCommand (cmd, conn, null, plain text, commandParameters); 73 using (SqlDataAdapter da = new SqlDataAdapter (cmd) 74 {75 try 76 {77 da. fill (ds); 78} 79 catch (SqlException ex) 80 {81 throw new Exception (ex. message); 82} 83 finally 84 {85 cmd. parameters. clear (); 86} 87} 88 return ds; 89} 90 91 // <summary> 92 // return DataReader (disable DataReader after use) 93 // </summary> 94 // <Param name = "plain text"> SQL statement </param> 95 // <param name = "commandParameters"> parameter set </param> 96 /// <returns> </ returns> 97 public static SqlDataReader MyExecuteReader (string plain text, params SqlParameter [] commandParameters) 98 {99 SqlConnection conn = MyConnection; 100 SqlCommand cmd = new SqlCommand (); 101 try102 {103 PrepareCommand (cmd, conn, null, plain text, commandParameters ); 104 SqlDataReader myReader = cm D. executeReader (CommandBehavior. closeConnection); // when DataReader is disabled, Connection will also close 105 return myReader; 106} 107 catch (SqlException ex) 108 {109 throw new Exception (ex. message); 110} 111 finally112 {113 cmd. parameters. clear (); 114} 115} 116 117 // <summary> 118 // the value of the first column in the first row is 119 /// </summary> 120 // <param name = "plain text "> SQL statement </param> 121 // <param name =" commandParameters "> parameter set </param> 122 /// <returns> </Returns> 123 public static object MyExecuteScalar (string plain text, params SqlParameter [] commandParameters) 124 {125 SqlConnection conn = MyConnection; 126 SqlCommand cmd = new SqlCommand (); 127 try128 {129 PrepareCommand (cmd, conn, null, plain text, commandParameters); 130 return cmd. executeScalar (); 131} 132 catch (SqlException ex) 133 {134 throw new Exception (ex. message); 135} 136 finally137 {138 cmd. parameters. Clear (); 139} 140} 141 142 // <summary> 143 // set SqlCommand144 // </summary> 145 // <param name = "cmd"> execution object </param> 146 // <param name = "conn"> connection object </param> 147 // <param name = "trans"> transaction </param> 148/ // <param name = "plain text"> SQL statement </param> 149 // <param name = "plain parms"> parameter set </param> 150 private static void PrepareCommand (sqlCommand cmd, sqlConnection conn, SqlTransaction trans, string plain text, SqlParame Ter [] partition parms) 151 {152 if (conn. State! = ConnectionState. Open) 153 conn. Open (); 154 cmd. Connection = conn; 155 cmd. CommandText = plain text; 156 if (trans! = Null) 157 cmd. Transaction = trans; 158 cmd. CommandType = CommandType. Text; 159 if (partition parms! = Null) 160 {161 foreach (SqlParameter parameter in parameter parms) 162 {163 if (parameter. direction = ParameterDirection. inputOutput | parameter. direction = ParameterDirection. input) & (parameter. value = null) 164 {165 parameter. value = DBNull. value; 166} 167 cmd. parameters. add (parameter); 168} 169} 170} 171}