ASP.NET三层架构中数据层数据访问类部分代码
代码如下：
1 using System.Collections.Generic; 2 using System.Linq; 3 using System.Web; 4 using System.Data; 5 using System.Data.SqlClient; 6 7 namespace TestWeb.DLL 8 { 9 public class DataAccess 10 { 11 private string connectionString = ""; 12 13 public string ConnectionString 14 { 15 get 16 { 17 return connectionString; 18 } 19 set 20 { 21 connectionString = value; 22 } 23 } 24 25 protected string BuildSqlStr() 26 { 27 return ConfiguretionSettings.AppSettings["slja"].ToString(); 28 } 29 30 protected SqlConnection BuildSqlConnection() 31 { 32 if (connectionString == "") 33 { 34 connectionString = BuildSqlStr(); 35 } 36 SqlConnection conn = new SqlConnection(connectionString); 37 conn.Open(); 38 return conn; 39 } 40 41 protected Sqlconnection BuildSqlConnection(connectionString) 42 { 43 SqlConnection conn = new SqlConnection(connectionString); 44 conn.Open(); 45 return conn; 46 } 47 48 protected SqlCommand BuildSqlCommand() 49 { 50 SqlCommand Comobj = new SqlCommand(); 51 Comobj.Connection = BuildSqlConnection(); 52 return Comobj; 53 } 54 protected SqlCommand BuildSqlCommand(string cmdText) 55 { 56 return new SqlCommand(cmdText, BuildSqlConnection()); 57 } 58 59 protected DataSet BuildSqlDataSet(string cmdText) 60 { 61 SqlCommand ObjectCommand = BuildSqlCommand(cmdText); 62 SqlDataAdapter ObjectAdapter = new SqlDataAdapter(ObjectCommand); 63 DataSet Ds = new DataSet(); 64 ObjectAdapter.Fill(Ds); 65 ObjectCommand.Connection.Close(); 66 ObjectCommand.Connection.Dispose(); 67 ObjectCommand.Dispose(); 68 return Ds; 69 } 70 71 protected DataReader BuildSqlDataReader(string cmdText) 72 { 73 SqlCommand ObjectCommand = BuildSqlCommand(cmdText); 74 SqlDataReader ObjectDataReader = ObjectCommand.ExecuteReader(); 75 ObjectCommand.Connection.Close(); 76 ObjectCommand.Connection.Dispose(); 77 ObjectCommand.Dispose(); 78 return ObjectDataReader; 79 } 80 81 protected int BuildSqlExecuteNonQuery(string cmdText) 82 { 83 SqlCommand ObjectCommand = BuildSqlCommand(cmdText); 84 int cellout = ObjectCommand.ExecuteNonQuery(); 85 ObjectCommand.Connection.Close(); 86 ObjectCommand.Connection.Dispose(); 87 ObjectCommand.Dispose(); 88 return cellout; 89 } 90 91 protected string BuildSqlExecuteScalar(string cmdText) 92 { 93 SqlCommand ObjectCommand = BuildSqlCommand(cmdText); 94 string returnStr = string.Empty; 95 if (ObjectCommand.ExecuteScalar() != null) 96 { 97 returnStr = ObjectCommand.ExecuteScalar().Tostring(); 98 } 99 ObjectCommand.Connection.Close(); 100 ObjectCommand.Connection.Dispose(); 101 ObjectCommand.Dispose(); 102 return returnStr; 103 } 104 105 //sisi 106 //four and four---------------------------------------------------------------------------------- 107 108 protected int RunSqlParametersExecuteNonQuery(string Sqlstr, SqlParameter[] parameters) 109 { 110 SqlCommand ObjectCommand = BuildSqlCommand(); 111 ObjectCommand.CommandType = CommandType.Text; 112 ObjectCommand.CommandText = Sqlstr; 113 int count = 0; 114 foreach (SqlParameter parameter in parameters) 115 { 116 ObjectCommand.Parameters.Add(parameter); 117 } 118 count = ObjectCommand.ExecuteNonQuery(); 119 ObjectCommand.Connection.Close(); 120 ObjectCommand.Connection.Dispose(); 121 ObjectCommand.Dispose(); 122 return count; 123 } 124 125 protected int RunSqlParametersExecuteNonQuery(string Sqlstr, SqlParameter parameters) 126 { 127 SqlCommand ObjectCommand = BuildSqlCommand(); 128 ObjectCommand.CommandType = CommandType.Text; 129 ObjectCommand.CommandText = Sqlstr; 130 int count = 0; 131 ObjectCommand.Parameters.Add(parameters); 132 count = ObjectCommand.ExecuteNonQuery(); 133 ObjectCommand.Connection.Close(); 134 ObjectCommand.Connection.Dispose(); 135 ObjectCommand.Dispose(); 136 return count; 137 } 138 139 protected int RunSqlParametersExecuteNonQuery(DbCommand sqlCommand, CommandType commtype, string Sqlstr) 140 { 141 sqlCommand.Connection = this.BuildSqlConnection(); 142 sqlCommand.CommandType = commtype; 143 sqlCommand.ComandText = Sqlstr; 144 int count = sqlCommand.ExecuteNonQuery(); 145 sqlCommand.Connection.Close(); 146 sqlCommand.Connection.Dispose(); 147 sqlCommand.Dispose(); 148 return count; 149 } 150 151 protected int RunSqlParametersExecuteNonQuery(DbCommand sqlCommand) 152 { 153 sqlCommand.Connection = this.BuildSqlConnection(); 154 int count = sqlCommand.ExecuteNonQuery(); 155 sqlCommand.Connection.Close(); 156 sqlCommand.Connection.Dispose(); 157 sqlCommand.Dispose(); 158 return count; 159 } 160 161 //---------------------------------------------------------------------------------------------------- 162 163 protected string RunSqlParametersExecuteScalar(string Sqlstr, SqlParameter[] parameters) 164 { 165 SqlCommand ObjectCommand = BuildSqlCommand(); 166 ObjectCommand.CommandType = CommandType.Text; 167 ObjectCommand.CommandText = Sqlstr; 168 string returnStr = string.Empty; 169 foreach (SqlParameter parameter in parameters) 170 { 171 ObjectCommand.Parameters.Add(parameter); 172 } 173 returnStr = ObjectCommand.ExecuteScalar(); 174 ObjectCommand.Connection.Close(); 175 ObjectCommand.Connection.Dispose(); 176 ObjectCommand.Dispose(); 177 return returnStr; 178 } 179 180 protected string RunSqlParametersExecuteScalar(string Sqlstr, SqlParameter parameters) 181 { 182 SqlCommand ObjectCommand = BuildSqlCommand(); 183 ObjectCommand.CommandType = CommandType.Text; 184 ObjectCommand.CommandText = Sqlstr; 185 string returnStr = string.Empty; 186 ObjectCommand.Parameters.Add(parameters); 187 returnStr = ObjectCommand.ExecuteScalar(); 188 ObjectCommand.Connection.Close(); 189 ObjectCommand.Connection.Dispose(); 190 ObjectCommand.Dispose(); 191 return returnStr; 192 } 193 194 protected string RunSqlParametersExecuteScalar(DbCommand sqlCommand, CommandType commtype, string Sqlstr) 195 { 196 sqlCommand.Connection = this.BuildSqlConnection(); 197 sqlCommand.CommandType = commtype; 198 sqlCommand.ComandText = Sqlstr; 199 string returnStr = sqlCommand.ExecuteScalar(); 200 sqlCommand.Connection.Close(); 201 sqlCommand.Connection.Dispose(); 202 sqlCommand.Dispose(); 203 return returnStr; 204 } 205 206 protected string RunSqlParametersExecuteScalar(DbCommand sqlCommand) 207 { 208 sqlCommand.Connection = this.BuildSqlConnection(); 209 string returnStr = sqlCommand.ExecuteScalar(); 210 sqlCommand.Connection.Close(); 211 sqlCommand.Connection.Dispose(); 212 sqlCommand.Dispose(); 213 return returnStr; 214 } 215 216 //--------------------------------------------------------------------------------------------------- 217 218 protected DataSet RunSqlParametersReturnDataSet(string Sqlstr, SqlParameter[] parameters) 219 { 220 SqlCommand ObjectCommand = BuildSqlCommand(); 221 ObjectCommand.CommandType = CommandType.Text; 222 ObjectCommand.CommandText = Sqlstr; 223 foreach (SqlParameter parameter in parameters) 224 { 225 ObjectCommand.Parameters.Add(parameter); 226 } 227 SqlDataAdapter ObjectAdapter = new SqlDataAdapter(ObjectCommand); 228 DataSet Ds = new DataSet(); 229 ObjectAdapter.Fill(Ds,"DataList"); 230 ObjectCommand.Connection.Close(); 231 ObjectCommand.Connection.Dispose(); 232 ObjectCommand.Dispose(); 233 return Ds; 234 } 235 236 protected DataSet RunSqlParametersReturnDataSet(string Sqlstr, SqlParameter parameters) 237 { 238 SqlCommand ObjectCommand = BuildSqlCommand(); 239 ObjectCommand.CommandType = CommandType.Text; 240 ObjectCommand.CommandText = Sqlstr; 241 foreach (SqlParameter parameter in parameters) 242 ObjectCommand.Parameters.Add(parameter); 243 SqlDataAdapter ObjectAdapter = new SqlDataAdapter(ObjectCommand); 244 DataSet Ds = new DataSet(); 245 ObjectAdapter.Fill(Ds,"DataList"); 246 ObjectCommand.Connection.Close(); 247 ObjectCommand.Connection.Dispose(); 248 ObjectCommand.Dispose(); 249 return Ds; 250 } 251 252 protected DataSet RunSqlParametersReturnDataSet(DbCommand sqlCommand, CommandType commtype, string Sqlstr) 253 { 254 sqlCommand.Connection = this.BuildSqlConnection(); 255 sqlCommand.CommandType = commtype; 256 sqlCommand.ComandText = Sqlstr; 257 SqlDataAdapter ObjectAdapter = new SqlDataAdapter(sqlCommand); 258 DataSet Ds = new DataSet(); 259 ObjectAdapter.Fill(Ds,"DataList"); 260 sqlCommand.Connection.Close(); 261 sqlCommand.Connection.Dispose(); 262 sqlCommand.Dispose(); 263 return Ds; 264 } 265 266 protected DataSet RunSqlParametersReturnDataSet(DbCommand sqlCommand) 267 { 268 sqlCommand.Connection = this.BuildSqlConnection(); 269 SqlDataAdapter ObjectAdapter = new SqlDataAdapter(sqlCommand); 270 DataSet Ds = new DataSet(); 271 ObjectAdapter.Fill(Ds,"DataList"); 272 sqlCommand.Connection.Close(); 273 sqlCommand.Connection.Dispose(); 274 sqlCommand.Dispose(); 275 return Ds; 276 } 277 278 //------------------------------------------------------------------------------------------- 279 280 protected SqlDataReader RunSqlParametersRuturnDataReader(string Sqlstr, SqlParameter[] parameters) 281 { 282 SqlCommand ObjectCommand = BuildSqlCommand(); 283 ObjectCommand.CommandType = CommandType.Text; 284 ObjectCommand.CommandText = Sqlstr; 285 foreach (SqlParameter parameter in parameters) 286 { 287 ObjectCommand.Parameters.Add(parameter); 288 } 289 SqlDataReader sdr = ObjectCommand.ExecuteReader(); 290 ObjectCommand.Connection.Close(); 291 ObjectCommand.Connection.Dispose(); 292 ObjectCommand.Dispose(); 293 return sdr; 294 } 295 296 protected SqlDataReader RunSqlParametersRuturnDataReader(string Sqlstr, SqlParameter parameters) 297 { 298 SqlCommand ObjectCommand = BuildSqlCommand(); 299 ObjectCommand.CommandType = CommandType.Text; 300 ObjectCommand.CommandText = Sqlstr; 301 foreach (SqlParameter parameter in parameters) 302 ObjectCommand.Parameters.Add(parameter); 303 SqlDataReader sdr = ObjectCommand.ExecuteReader(); 304 ObjectCommand.Connection.Close(); 305 ObjectCommand.Connection.Dispose(); 306 ObjectCommand.Dispose(); 307 return sdr; 308 } 309 310 protected SqlDataReader RunSqlParametersRuturnDataReader(DbCommand sqlCommand, CommandType commtype, string Sqlstr) 311 { 312 sqlCommand.Connection = this.BuildSqlConnection(); 313 sqlCommand.CommandType = commtype; 314 sqlCommand.ComandText = Sqlstr; 315 SqlDataReader sdr = ObjectCommand.ExecuteReader(); 316 sqlCommand.Connection.Close(); 317 sqlCommand.Connection.Dispose(); 318 sqlCommand.Dispose(); 319 return sdr; 320 } 321 322 protected SqlDataReader RunSqlParametersRuturnDataReader(DbCommand sqlCommand) 323 { 324 sqlCommand.Connection = this.BuildSqlConnection(); 325 SqlDataReader sdr = ObjectCommand.ExecuteReader(); 326 sqlCommand.Connection.Close(); 327 sqlCommand.Connection.Dispose(); 328 sqlCommand.Dispose(); 329 return sdr; 330 } 331 } 332 }
