Oracle,SQL Server,Access万能数据库通用类！
时间：2010-10-10 来源：moss_tan_jun
| 0022 | private string connectionString; |
| 0023 | public string ConntionString |
| 0024 | { |
| 0025 | get |
| 0026 | { |
| 0027 | return connectionString ; |
| 0028 | } |
| 0029 | set |
| 0030 | { |
| 0031 | connectionString = value; |
| 0032 | } |
| 0033 | } |
| 0034 |
| 0035 |
| 0036 | public DataBaseLayer(string strConnect,string dataType) |
| 0037 | { |
| 0038 | this.ConntionString = strConnect; |
| 0039 | this.DbType = dataType; |
| 0040 | } |
| 0041 |
| 0042 |
| 0043 | public DataBaseLayer() |
| 0044 | { |
| 0045 | this.connectionString = ConfigurationSettings.AppSettings["ConnectionString"] ; |
| 0046 | this.dbType = ConfigurationSettings.AppSettings["DataType"] ; |
| 0047 | } |
| 0048 |
| 0049 | /**//// <summary> |
| 0050 | /// 数据库类型 |
| 0051 | /// </summary> |
| 0052 | private string dbType; |
| 0053 | public string DbType |
| 0054 | { |
| 0055 | get |
| 0056 | { |
| 0057 | if ( dbType == string.Empty || dbType == null ) |
| 0058 | { |
| 0059 | return "Access"; |
| 0060 | } |
| 0061 | else |
| 0062 | { |
| 0063 | return dbType; |
| 0064 | } |
| 0065 | } |
| 0066 | set |
| 0067 | { |
| 0068 | if ( value != string.Empty && value != null ) |
| 0069 | { |
| 0070 | dbType = value; |
| 0071 | } |
| 0072 | if (dbType ==string.Empty || dbType == null) |
| 0073 | { |
| 0074 | dbType = ConfigurationSettings.AppSettings["DataType"]; |
| 0075 | } |
| 0076 | if ( dbType == string.Empty || dbType == null ) |
| 0077 | { |
| 0078 | dbType = "Access"; |
| 0079 | } |
| 0080 | } |
| 0081 | } |
| 0082 |
| 0083 |
| 0084 |
| 0085 |
| 0086 | 转换参数#region 转换参数 |
| 0087 | private System.Data.IDbDataParameter iDbPara(string ParaName,string DataType) |
| 0088 | { |
| 0089 | switch(this.DbType) |
| 0090 | { |
| 0091 | case "SqlServer": |
| 0092 | return GetSqlPara(ParaName,DataType); |
| 0093 |
| 0094 | case "Oracle": |
| 0095 | return GetOleDbPara(ParaName,DataType); |
| 0096 |
| 0097 | case "Access": |
| 0098 | return GetOleDbPara(ParaName,DataType); |
| 0099 |
| 0100 | default : |
| 0101 | return GetSqlPara(ParaName,DataType); |
| 0102 |
| 0103 | } |
| 0104 | } |
| 0105 |
| 0106 | private System.Data.SqlClient.SqlParameter GetSqlPara( string ParaName , string DataType) |
| 0107 | { |
| 0108 | switch(DataType) |
| 0109 | { |
| 0110 | case "Decimal": |
| 0111 | return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.Decimal ); |
| 0112 | case "Varchar": |
| 0113 | return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.VarChar ); |
| 0114 | case "DateTime": |
| 0115 | return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.DateTime ); |
| 0116 | case "Iamge": |
| 0117 | return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.Image ); |
| 0118 | case "Int": |
| 0119 | return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.Int ); |
| 0120 | case "Text": |
| 0121 | return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.NText ); |
| 0122 | default : |
| 0123 | return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.VarChar ); |
| 0124 | } |
| 0125 | } |
| 0126 |
| 0127 | private System.Data.OracleClient.OracleParameter GetOraclePara( string ParaName , string DataType) |
| 0128 | { |
| 0129 | switch(DataType) |
| 0130 | { |
| 0131 | case "Decimal": |
| 0132 | return new System.Data.OracleClient.OracleParameter( ParaName, System.Data.OracleClient.OracleType.Double); |
| 0133 |
| 0134 | case "Varchar": |
| 0135 | return new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.VarChar ); |
| 0136 |
| 0137 | case "DateTime": |
| 0138 | return new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.DateTime ); |
| 0139 |
| 0140 | case "Iamge": |
| 0141 | return new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.BFile ); |
| 0142 |
| 0143 | case "Int": |
| 0144 | return new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.Int32 ); |
| 0145 |
| 0146 | case "Text": |
| 0147 | return new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.LongVarChar ); |
| 0148 |
| 0149 | default: |
| 0150 | return new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.VarChar ); |
| 0151 |
| 0152 | } |
| 0153 | } |
| 0154 |
| 0155 | private System.Data.OleDb.OleDbParameter GetOleDbPara( string ParaName , string DataType) |
| 0156 | { |
| 0157 | switch(DataType) |
| 0158 | { |
| 0159 | case "Decimal": |
| 0160 | return new System.Data.OleDb.OleDbParameter( ParaName, System.Data.DbType.Decimal); |
| 0161 |
| 0162 | case "Varchar": |
| 0163 | return new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.String ); |
| 0164 |
| 0165 | case "DateTime": |
| 0166 | return new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.DateTime ); |
| 0167 |
| 0168 | case "Iamge": |
| 0169 | return new System.Data.OleDb.OleDbParameter( ParaName, System.Data.DbType.Binary ); |
| 0170 |
| 0171 | case "Int": |
| 0172 | return new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.Int32 ); |
| 0173 |
| 0174 | case "Text": |
| 0175 | return new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.String ); |
| 0176 |
| 0177 | default: |
| 0178 | return new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.String ); |
| 0179 |
| 0180 | } |
| 0181 | } |
| 0182 |
| 0183 | #endregion |
| 0184 |
| 0185 | 创建 Connection 和 Command#region 创建 Connection 和 Command |
| 0186 |
| 0187 | private IDbConnection GetConnection() |
| 0188 | { |
| 0189 | switch(this.DbType) |
| 0190 | { |
| 0191 | case "SqlServer": |
| 0192 | return new System.Data.SqlClient.SqlConnection(this.ConntionString); |
| 0193 |
| 0194 | case "Oracle": |
| 0195 | return new System.Data.OracleClient.OracleConnection(this.ConntionString); |
| 0196 |
| 0197 | case "Access": |
| 0198 | return new System.Data.OleDb.OleDbConnection(this.ConntionString); |
| 0199 | default: |
| 0200 | return new System.Data.SqlClient.SqlConnection(this.ConntionString); |
| 0201 | } |
| 0202 | } |
| 0203 |
| 0204 |
| 0205 | private IDbCommand GetCommand(string Sql,IDbConnection iConn) |
| 0206 | { |
| 0207 | switch(this.DbType) |
| 0208 | { |
| 0209 | case "SqlServer": |
| 0210 | return new System.Data.SqlClient.SqlCommand(Sql,(SqlConnection)iConn); |
| 0211 |
| 0212 | case "Oracle": |
| 0213 | return new System.Data.OracleClient.OracleCommand(Sql,(OracleConnection)iConn); |
| 0214 |
| 0215 | case "Access": |
| 0216 | return new System.Data.OleDb.OleDbCommand(Sql,(OleDbConnection)iConn); |
| 0217 | default: |
| 0218 | return new System.Data.SqlClient.SqlCommand(Sql,(SqlConnection)iConn); |
| 0219 | } |
| 0220 | } |
| 0221 |
| 0222 | private IDbCommand GetCommand() |
| 0223 | { |
| 0224 | switch(this.DbType) |
| 0225 | { |
| 0226 | case "SqlServer": |
| 0227 | return new System.Data.SqlClient.SqlCommand(); |
| 0228 |
| 0229 | case "Oracle": |
| 0230 | return new System.Data.OracleClient.OracleCommand(); |
| 0231 |
| 0232 | case "Access": |
| 0233 | return new System.Data.OleDb.OleDbCommand(); |
| 0234 | default: |
| 0235 | return new System.Data.SqlClient.SqlCommand(); |
| 0236 | } |
| 0237 | } |
| 0238 |
| 0239 | private IDataAdapter GetAdapater(string Sql,IDbConnection iConn) |
| 0240 | { |
| 0241 | switch(this.DbType) |
| 0242 | { |
| 0243 | case "SqlServer": |
| 0244 | return new System.Data.SqlClient.SqlDataAdapter(Sql,(SqlConnection)iConn); |
| 0245 |
| 0246 | case "Oracle": |
| 0247 | return new System.Data.OracleClient.OracleDataAdapter(Sql,(OracleConnection)iConn); |
| 0248 |
| 0249 | case "Access": |
| 0250 | return new System.Data.OleDb.OleDbDataAdapter(Sql,(OleDbConnection)iConn); |
| 0251 |
| 0252 | default: |
| 0253 | return new System.Data.SqlClient.SqlDataAdapter(Sql,(SqlConnection)iConn);; |
| 0254 | } |
| 0255 |
| 0256 | } |
| 0257 |
| 0258 | private IDataAdapter GetAdapater() |
| 0259 | { |
| 0260 | switch(this.DbType) |
| 0261 | { |
| 0262 | case "SqlServer": |
| 0263 | return new System.Data.SqlClient.SqlDataAdapter(); |
| 0264 |
| 0265 | case "Oracle": |
| 0266 | return new System.Data.OracleClient.OracleDataAdapter(); |
| 0267 |
| 0268 | case "Access": |
| 0269 | return new System.Data.OleDb.OleDbDataAdapter(); |
| 0270 |
| 0271 | default: |
| 0272 | return new System.Data.SqlClient.SqlDataAdapter(); |
| 0273 | } |
| 0274 | } |
| 0275 |
| 0276 | private IDataAdapter GetAdapater(IDbCommand iCmd) |
| 0277 | { |
| 0278 | switch(this.DbType) |
| 0279 | { |
| 0280 | case "SqlServer": |
| 0281 | return new System.Data.SqlClient.SqlDataAdapter((SqlCommand)iCmd); |
| 0282 |
| 0283 | case "Oracle": |
| 0284 | return new System.Data.OracleClient.OracleDataAdapter((OracleCommand)iCmd); |
| 0285 |
| 0286 | case "Access": |
| 0287 | return new System.Data.OleDb.OleDbDataAdapter((OleDbCommand)iCmd); |
| 0288 |
| 0289 | default: |
| 0290 | return new System.Data.SqlClient.SqlDataAdapter((SqlCommand)iCmd); |
| 0291 | } |
| 0292 | } |
| 0293 | #endregion |
| 0294 |
| 0295 | 执行简单SQL语句#region 执行简单SQL语句 |
| 0296 | /**//// <summary> |
| 0297 | /// 执行SQL语句，返回影响的记录数 |
| 0298 | /// </summary> |
| 0299 | /// <param name="SQLString">SQL语句</param> |
| 0300 | /// <returns>影响的记录数</returns> |
| 0301 | public int ExecuteSql(string SqlString) |
| 0302 | { |
| 0303 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 0304 | { |
| 0305 | using (System.Data.IDbCommand iCmd = GetCommand(SqlString,iConn)) |
| 0306 | { |
| 0307 | iConn.Open(); |
| 0308 | try |
| 0309 | { |
| 0310 |
| 0311 | int rows=iCmd.ExecuteNonQuery(); |
| 0312 | return rows; |
| 0313 | } |
| 0314 | catch(System.Exception E) |
| 0315 | { |
| 0316 | throw new Exception(E.Message); |
| 0317 | } |
| 0318 | finally |
| 0319 | { |
| 0320 | if(iConn.State != ConnectionState.Closed) |
| 0321 | { |
| 0322 | iConn.Close(); |
| 0323 | } |
| 0324 | } |
| 0325 | } |
| 0326 | } |
| 0327 | } |
| 0328 |
| 0329 | /**//// <summary> |
| 0330 | /// 执行多条SQL语句，实现数据库事务。 |
| 0331 | /// </summary> |
| 0332 | /// <param name="SQLStringList">多条SQL语句</param> |
| 0333 | public void ExecuteSqlTran(ArrayList SQLStringList) |
| 0334 | { |
| 0335 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 0336 | { |
| 0337 | iConn.Open(); |
| 0338 | using(System.Data.IDbCommand iCmd = GetCommand()) |
| 0339 | { |
| 0340 | iCmd.Connection=iConn; |
| 0341 | using(System.Data.IDbTransaction iDbTran = iConn.BeginTransaction()) |
| 0342 | { |
| 0343 | iCmd.Transaction=iDbTran; |
| 0344 | try |
| 0345 | { |
| 0346 | for(int n=0;n<SQLStringList.Count;n++) |
| 0347 | { |
| 0348 | string strsql = SQLStringList[n].ToString(); |
| 0349 | if ( strsql.Trim().Length>1) |
| 0350 | { |
| 0351 | iCmd.CommandText = strsql; |
| 0352 | iCmd.ExecuteNonQuery(); |
| 0353 | } |
| 0354 | } |
| 0355 | iDbTran.Commit(); |
| 0356 | } |
| 0357 | catch(System.Exception E) |
| 0358 | { |
| 0359 | iDbTran.Rollback(); |
| 0360 | throw new Exception(E.Message); |
| 0361 | } |
| 0362 | finally |
| 0363 | { |
| 0364 | if(iConn.State != ConnectionState.Closed) |
| 0365 | { |
| 0366 | iConn.Close(); |
| 0367 | } |
| 0368 | } |
| 0369 | } |
| 0370 |
| 0371 | } |
| 0372 |
| 0373 | } |
| 0374 | } |
| 0375 | /**//// <summary> |
| 0376 | /// 执行带一个存储过程参数的的SQL语句。 |
| 0377 | /// </summary> |
| 0378 | /// <param name="SQLString">SQL语句</param> |
| 0379 | /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param> |
| 0380 | /// <returns>影响的记录数</returns> |
| 0381 | public int ExecuteSql(string SqlString,string content) |
| 0382 | { |
| 0383 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 0384 | { |
| 0385 | using(System.Data.IDbCommand iCmd = GetCommand(SqlString,iConn)) |
| 0386 | { |
| 0387 | System.Data.IDataParameter myParameter = this.iDbPara( "@content", "Text"); |
| 0388 | myParameter.Value = content ; |
| 0389 | iCmd.Parameters.Add(myParameter); |
| 0390 | iConn.Open(); |
| 0391 | try |
| 0392 | { |
| 0393 |
| 0394 | int rows = iCmd.ExecuteNonQuery(); |
| 0395 | return rows; |
| 0396 | } |
| 0397 | catch( System.Exception e ) |
| 0398 | { |
| 0399 | throw new Exception(e.Message); |
| 0400 | } |
| 0401 | finally |
| 0402 | { |
| 0403 | if(iConn.State != ConnectionState.Closed) |
| 0404 | { |
| 0405 | iConn.Close(); |
| 0406 | } |
| 0407 | } |
| 0408 | } |
| 0409 | } |
| 0410 | } |
| 0411 |
| 0412 |
| 0413 | /**//// <summary> |
| 0414 | /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) |
| 0415 | /// </summary> |
| 0416 | /// <param name="strSQL">SQL语句</param> |
| 0417 | /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> |
| 0418 | /// <returns>影响的记录数</returns> |
| 0419 | public int ExecuteSqlInsertImg(string SqlString,byte[] fs) |
| 0420 | { |
| 0421 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 0422 | { |
| 0423 | using(System.Data.IDbCommand iCmd = GetCommand(SqlString,iConn)) |
| 0424 | { |
| 0425 | System.Data.IDataParameter myParameter = this.iDbPara( "@content", "Image"); |
| 0426 | myParameter.Value = fs ; |
| 0427 | iCmd.Parameters.Add(myParameter); |
| 0428 | iConn.Open(); |
| 0429 | try |
| 0430 | { |
| 0431 | int rows = iCmd.ExecuteNonQuery(); |
| 0432 | return rows; |
| 0433 | } |
| 0434 | catch( System.Exception e ) |
| 0435 | { |
| 0436 | throw new Exception(e.Message); |
| 0437 | } |
| 0438 | finally |
| 0439 | { |
| 0440 | if(iConn.State != ConnectionState.Closed) |
| 0441 | { |
| 0442 | iConn.Close(); |
| 0443 | } |
| 0444 | } |
| 0445 | } |
| 0446 | } |
| 0447 | } |
| 0448 |
| 0449 | /**//// <summary> |
| 0450 | /// 执行一条计算查询结果语句，返回查询结果（object）。 |
| 0451 | /// </summary> |
| 0452 | /// <param name="SQLString">计算查询结果语句</param> |
| 0453 | /// <returns>查询结果（object）</returns> |
| 0454 | public object GetSingle(string SqlString) |
| 0455 | { |
| 0456 | using (System.Data.IDbConnection iConn = GetConnection()) |
| 0457 | { |
| 0458 | using (System.Data.IDbCommand iCmd = GetCommand(SqlString,iConn)) |
| 0459 | { |
| 0460 | iConn.Open(); |
| 0461 | try |
| 0462 | { |
| 0463 | object obj = iCmd.ExecuteScalar(); |
| 0464 | if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value))) |
| 0465 | { |
| 0466 | return null; |
| 0467 | } |
| 0468 | else |
| 0469 | { |
| 0470 | return obj; |
| 0471 | } |
| 0472 | } |
| 0473 | catch(System.Exception e) |
| 0474 | { |
| 0475 | throw new Exception(e.Message); |
| 0476 | } |
| 0477 | finally |
| 0478 | { |
| 0479 | if(iConn.State != ConnectionState.Closed) |
| 0480 | { |
| 0481 | iConn.Close(); |
| 0482 | } |
| 0483 | } |
| 0484 | } |
| 0485 | } |
| 0486 | } |
| 0487 | /**//// <summary> |
| 0488 | /// 执行查询语句，返回IDataAdapter |
| 0489 | /// </summary> |
| 0490 | /// <param name="strSQL">查询语句</param> |
| 0491 | /// <returns>IDataAdapter</returns> |
| 0492 | public IDataAdapter ExecuteReader(string strSQL) |
| 0493 | { |
| 0494 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 0495 | { |
| 0496 | iConn.Open(); |
| 0497 | try |
| 0498 | { |
| 0499 | System.Data.IDataAdapter iAdapter = this.GetAdapater(strSQL,iConn); |
| 0500 | return iAdapter; |
| 0501 | } |
| 0502 | catch(System.Exception e) |
| 0503 | { |
| 0504 | throw new Exception(e.Message); |
| 0505 | } |
| 0506 | finally |
| 0507 | { |
| 0508 | if(iConn.State != ConnectionState.Closed) |
| 0509 | { |
| 0510 | iConn.Close(); |
| 0511 | } |
| 0512 | } |
| 0513 | } |
| 0514 | } |
| 0515 | /**//// <summary> |
| 0516 | /// 执行查询语句，返回DataSet |
| 0517 | /// </summary> |
| 0518 | /// <param name="SQLString">查询语句</param> |
| 0519 | /// <returns>DataSet</returns> |
| 0520 | public DataSet Query(string sqlString) |
| 0521 | { |
| 0522 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 0523 | { |
| 0524 | using(System.Data.IDbCommand iCmd = GetCommand(sqlString,iConn)) |
| 0525 | { |
| 0526 | DataSet ds = new DataSet(); |
| 0527 | iConn.Open(); |
| 0528 | try |
| 0529 | { |
| 0530 | System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString,iConn); |
| 0531 | iAdapter.Fill(ds); |
| 0532 | return ds; |
| 0533 | } |
| 0534 | catch(System.Exception ex) |
| 0535 | { |
| 0536 | throw new Exception(ex.Message); |
| 0537 | } |
| 0538 | finally |
| 0539 | { |
| 0540 | if(iConn.State != ConnectionState.Closed) |
| 0541 | { |
| 0542 | iConn.Close(); |
| 0543 | } |
| 0544 | } |
| 0545 | } |
| 0546 | } |
| 0547 | } |
| 0548 |
| 0549 | /**//// <summary> |
| 0550 | /// 执行查询语句，返回DataSet |
| 0551 | /// </summary> |
| 0552 | /// <param name="sqlString">查询语句</param> |
| 0553 | /// <param name="dataSet">要填充的DataSet</param> |
| 0554 | /// <param name="tableName">要填充的表名</param> |
| 0555 | /// <returns>DataSet</returns> |
| 0556 | public DataSet Query(string sqlString,DataSet dataSet,string tableName) |
| 0557 | { |
| 0558 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 0559 | { |
| 0560 | using(System.Data.IDbCommand iCmd = GetCommand(sqlString,iConn)) |
| 0561 | { |
| 0562 | iConn.Open(); |
| 0563 | try |
| 0564 | { |
| 0565 | System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString,iConn); |
| 0566 | ((OleDbDataAdapter)iAdapter).Fill(dataSet,tableName); |
| 0567 | return dataSet; |
| 0568 | } |
| 0569 | catch(System.Exception ex) |
| 0570 | { |
| 0571 | throw new Exception(ex.Message); |
| 0572 | } |
| 0573 | finally |
| 0574 | { |
| 0575 | if(iConn.State != ConnectionState.Closed) |
| 0576 | { |
| 0577 | iConn.Close(); |
| 0578 | } |
| 0579 | } |
| 0580 | } |
| 0581 | } |
| 0582 | } |
| 0583 |
| 0584 |
| 0585 | /**//// <summary> |
| 0586 | /// 执行SQL语句 返回存储过程 |
| 0587 | /// </summary> |
| 0588 | /// <param name="sqlString">Sql语句</param> |
| 0589 | /// <param name="dataSet">要填充的DataSet</param> |
| 0590 | /// <param name="startIndex">开始记录</param> |
| 0591 | /// <param name="pageSize">页面记录大小</param> |
| 0592 | /// <param name="tableName">表名称</param> |
| 0593 | /// <returns>DataSet</returns> |
| 0594 | public DataSet Query(string sqlString , DataSet dataSet ,int startIndex ,int pageSize, string tableName ) |
| 0595 | { |
| 0596 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 0597 | { |
| 0598 | iConn.Open(); |
| 0599 | try |
| 0600 | { |
| 0601 | System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString,iConn); |
| 0602 |
| 0603 | ((OleDbDataAdapter)iAdapter).Fill(dataSet,startIndex,pageSize,tableName); |
| 0604 |
| 0605 | return dataSet; |
| 0606 | } |
| 0607 | catch(Exception ex) |
| 0608 | { |
| 0609 | throw new Exception(ex.Message); |
| 0610 | } |
| 0611 | finally |
| 0612 | { |
| 0613 | if(iConn.State != ConnectionState.Closed) |
| 0614 | { |
| 0615 | iConn.Close(); |
| 0616 | } |
| 0617 | } |
| 0618 | } |
| 0619 | } |
| 0620 |
| 0621 |
| 0622 | /**//// <summary> |
| 0623 | /// 执行查询语句，向XML文件写入数据 |
| 0624 | /// </summary> |
| 0625 | /// <param name="sqlString">查询语句</param> |
| 0626 | /// <param name="xmlPath">XML文件路径</param> |
| 0627 | public void WriteToXml(string sqlString,string xmlPath) |
| 0628 | { |
| 0629 | Query(sqlString).WriteXml(xmlPath); |
| 0630 | } |
| 0631 |
| 0632 | /**//// <summary> |
| 0633 | /// 执行查询语句 |
| 0634 | /// </summary> |
| 0635 | /// <param name="SqlString">查询语句</param> |
| 0636 | /// <returns>DataTable </returns> |
| 0637 | public DataTable ExecuteQuery(string sqlString) |
| 0638 | { |
| 0639 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 0640 | { |
| 0641 | //System.Data.IDbCommand iCmd = GetCommand(sqlString,iConn); |
| 0642 | DataSet ds = new DataSet(); |
| 0643 | try |
| 0644 | { |
| 0645 | System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString,iConn); |
| 0646 | iAdapter.Fill(ds); |
| 0647 | } |
| 0648 | catch(System.Exception e) |
| 0649 | { |
| 0650 | throw new Exception(e.Message); |
| 0651 | } |
| 0652 | finally |
| 0653 | { |
| 0654 | if(iConn.State != ConnectionState.Closed) |
| 0655 | { |
| 0656 | iConn.Close(); |
| 0657 | } |
| 0658 | } |
| 0659 | return ds.Tables[0]; |
| 0660 | } |
| 0661 | } |
| 0662 |
| 0663 | /**//// <summary> |
| 0664 | /// 执行查询语句 |
| 0665 | /// </summary> |
| 0666 | /// <param name="SqlString">查询语句</param> |
| 0667 | /// <returns>DataTable </returns> |
| 0668 | public DataTable ExecuteQuery(string SqlString,string Proc) |
| 0669 | { |
| 0670 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 0671 | { |
| 0672 | using(System.Data.IDbCommand iCmd = GetCommand(SqlString,iConn)) |
| 0673 | { |
| 0674 | iCmd.CommandType = CommandType.StoredProcedure; |
| 0675 | DataSet ds = new DataSet(); |
| 0676 | try |
| 0677 | { |
| 0678 | System.Data.IDataAdapter iDataAdapter = this.GetAdapater(SqlString,iConn); |
| 0679 | iDataAdapter.Fill(ds); |
| 0680 | } |
| 0681 | catch(System.Exception e) |
| 0682 | { |
| 0683 | throw new Exception(e.Message); |
| 0684 | } |
| 0685 | finally |
| 0686 | { |
| 0687 | if(iConn.State != ConnectionState.Closed) |
| 0688 | { |
| 0689 | iConn.Close(); |
| 0690 | } |
| 0691 | } |
| 0692 | return ds.Tables[0]; |
| 0693 | } |
| 0694 |
| 0695 |
| 0696 | } |
| 0697 | } |
| 0698 |
| 0699 | /**//// <summary> |
| 0700 | /// |
| 0701 | /// </summary> |
| 0702 | /// <param name="Sql"></param> |
| 0703 | /// <returns></returns> |
| 0704 | public DataView ExeceuteDataView(string Sql) |
| 0705 | { |
| 0706 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 0707 | { |
| 0708 | using(System.Data.IDbCommand iCmd = GetCommand(Sql,iConn)) |
| 0709 | { |
| 0710 | DataSet ds = new DataSet(); |
| 0711 | try |
| 0712 | { |
| 0713 | System.Data.IDataAdapter iDataAdapter = this.GetAdapater(Sql,iConn); |
| 0714 | iDataAdapter.Fill(ds); |
| 0715 | return ds.Tables[0].DefaultView; |
| 0716 | } |
| 0717 | catch(System.Exception e) |
| 0718 | { |
| 0719 | throw new Exception(e.Message); |
| 0720 | } |
| 0721 | finally |
| 0722 | { |
| 0723 | if(iConn.State != ConnectionState.Closed) |
| 0724 | { |
| 0725 | iConn.Close(); |
| 0726 | } |
| 0727 | } |
| 0728 | } |
| 0729 | } |
| 0730 | } |
| 0731 |
| 0732 | #endregion |
| 0733 |
| 0734 | 执行带参数的SQL语句#region 执行带参数的SQL语句 |
| 0735 | /**//// <summary> |
| 0736 | /// 执行SQL语句，返回影响的记录数 |
| 0737 | /// </summary> |
| 0738 | /// <param name="SQLString">SQL语句</param> |
| 0739 | /// <returns>影响的记录数</returns> |
| 0740 | public int ExecuteSql(string SQLString,params IDataParameter[] iParms) |
| 0741 | { |
| 0742 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 0743 | { |
| 0744 | System.Data.IDbCommand iCmd = GetCommand(); |
| 0745 | { |
| 0746 | try |
| 0747 | { |
| 0748 | PrepareCommand(out iCmd, iConn, null , SQLString, iParms ); |
| 0749 | int rows=iCmd.ExecuteNonQuery(); |
| 0750 | iCmd.Parameters.Clear(); |
| 0751 | return rows; |
| 0752 | } |
| 0753 | catch(System.Exception E) |
| 0754 | { |
| 0755 | throw new Exception( E.Message ); |
| 0756 | } |
| 0757 | finally |
| 0758 | { |
| 0759 | iCmd.Dispose(); |
| 0760 | if(iConn.State != ConnectionState.Closed) |
| 0761 | { |
| 0762 | iConn.Close(); |
| 0763 | } |
| 0764 | } |
| 0765 | } |
| 0766 | } |
| 0767 | } |
| 0768 |
| 0769 |
| 0770 | /**//// <summary> |
| 0771 | /// 执行多条SQL语句，实现数据库事务。 |
| 0772 | /// </summary> |
| 0773 | /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> |
| 0774 | public void ExecuteSqlTran(Hashtable SQLStringList) |
| 0775 | { |
| 0776 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 0777 | { |
| 0778 | iConn.Open(); |
| 0779 | using (IDbTransaction iTrans = iConn.BeginTransaction()) |
| 0780 | { |
| 0781 | System.Data.IDbCommand iCmd = GetCommand(); |
| 0782 | try |
| 0783 | { |
| 0784 | //循环 |
| 0785 | foreach ( DictionaryEntry myDE in SQLStringList) |
| 0786 | { |
| 0787 | string cmdText = myDE.Key.ToString(); |
| 0788 | IDataParameter[] iParms=( IDataParameter[] ) myDE.Value; |
| 0789 | PrepareCommand( out iCmd , iConn , iTrans , cmdText , iParms ); |
| 0790 | int val = iCmd.ExecuteNonQuery(); |
| 0791 | iCmd.Parameters.Clear(); |
| 0792 | } |
| 0793 | iTrans.Commit(); |
| 0794 | } |
| 0795 | catch |
| 0796 | { |
| 0797 | iTrans.Rollback(); |
| 0798 | throw; |
| 0799 | } |
| 0800 | finally |
| 0801 | { |
| 0802 | iCmd.Dispose(); |
| 0803 | if(iConn.State != ConnectionState.Closed) |
| 0804 | { |
| 0805 | iConn.Close(); |
| 0806 | } |
| 0807 | } |
| 0808 |
| 0809 | } |
| 0810 | } |
| 0811 | } |
| 0812 |
| 0813 |
| 0814 | /**//// <summary> |
| 0815 | /// 执行一条计算查询结果语句，返回查询结果（object）。 |
| 0816 | /// </summary> |
| 0817 | /// <param name="SQLString">计算查询结果语句</param> |
| 0818 | /// <returns>查询结果（object）</returns> |
| 0819 | public object GetSingle(string SQLString,params IDataParameter[] iParms) |
| 0820 | { |
| 0821 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 0822 | { |
| 0823 | System.Data.IDbCommand iCmd = GetCommand(); |
| 0824 | { |
| 0825 | try |
| 0826 | { |
| 0827 | PrepareCommand( out iCmd, iConn, null , SQLString, iParms ); |
| 0828 | object obj = iCmd.ExecuteScalar(); |
| 0829 | iCmd.Parameters.Clear(); |
| 0830 | if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value))) |
| 0831 | { |
| 0832 | return null; |
| 0833 | } |
| 0834 | else |
| 0835 | { |
| 0836 | return obj; |
| 0837 | } |
| 0838 | } |
| 0839 | catch(System.Exception e) |
| 0840 | { |
| 0841 | throw new Exception(e.Message); |
| 0842 | } |
| 0843 | finally |
| 0844 | { |
| 0845 | iCmd.Dispose(); |
| 0846 | if(iConn.State != ConnectionState.Closed) |
| 0847 | { |
| 0848 | iConn.Close(); |
| 0849 | } |
| 0850 | } |
| 0851 | } |
| 0852 | } |
| 0853 | } |
| 0854 |
| 0855 | /**//// <summary> |
| 0856 | /// 执行查询语句，返回IDataReader |
| 0857 | /// </summary> |
| 0858 | /// <param name="strSQL">查询语句</param> |
| 0859 | /// <returns> IDataReader </returns> |
| 0860 | public IDataReader ExecuteReader(string SQLString,params IDataParameter[] iParms) |
| 0861 | { |
| 0862 | System.Data.IDbConnection iConn = this.GetConnection(); |
| 0863 | { |
| 0864 | System.Data.IDbCommand iCmd = GetCommand(); |
| 0865 | { |
| 0866 | try |
| 0867 | { |
| 0868 | PrepareCommand(out iCmd, iConn , null , SQLString , iParms); |
| 0869 | System.Data.IDataReader iReader = iCmd.ExecuteReader(); |
| 0870 | iCmd.Parameters.Clear(); |
| 0871 | return iReader; |
| 0872 | } |
| 0873 | catch(System.Exception e) |
| 0874 | { |
| 0875 | throw new Exception(e.Message); |
| 0876 | } |
| 0877 | finally |
| 0878 | { |
| 0879 | iCmd.Dispose(); |
| 0880 | if(iConn.State != ConnectionState.Closed) |
| 0881 | { |
| 0882 | iConn.Close(); |
| 0883 | } |
| 0884 | } |
| 0885 | } |
| 0886 | } |
| 0887 | } |
| 0888 |
| 0889 | /**//// <summary> |
| 0890 | /// 执行查询语句，返回DataSet |
| 0891 | /// </summary> |
| 0892 | /// <param name="SQLString">查询语句</param> |
| 0893 | /// <returns>DataSet</returns> |
| 0894 | public DataSet Query(string sqlString,params IDataParameter[] iParms) |
| 0895 | { |
| 0896 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 0897 | { |
| 0898 | IDbCommand iCmd = GetCommand(); |
| 0899 | { |
| 0900 | PrepareCommand(out iCmd , iConn , null , sqlString , iParms ); |
| 0901 | try |
| 0902 | { |
| 0903 | IDataAdapter iAdapter = this.GetAdapater(sqlString,iConn); |
| 0904 | DataSet ds = new DataSet(); |
| 0905 | iAdapter.Fill(ds); |
| 0906 | iCmd.Parameters.Clear(); |
| 0907 | return ds; |
| 0908 | } |
| 0909 | catch(System.Exception ex) |
| 0910 | { |
| 0911 | throw new Exception(ex.Message); |
| 0912 | } |
| 0913 | finally |
| 0914 | { |
| 0915 | iCmd.Dispose(); |
| 0916 | if(iConn.State != ConnectionState.Closed) |
| 0917 | { |
| 0918 | iConn.Close(); |
| 0919 | } |
| 0920 | } |
| 0921 | } |
| 0922 | } |
| 0923 | } |
| 0924 |
| 0925 |
| 0926 | /**//// <summary> |
| 0927 | /// 初始化Command |
| 0928 | /// </summary> |
| 0929 | /// <param name="iCmd"></param> |
| 0930 | /// <param name="iConn"></param> |
| 0931 | /// <param name="iTrans"></param> |
| 0932 | /// <param name="cmdText"></param> |
| 0933 | /// <param name="iParms"></param> |
| 0934 | private void PrepareCommand(out IDbCommand iCmd,IDbConnection iConn,System.Data.IDbTransaction iTrans, string cmdText, IDataParameter[] iParms) |
| 0935 | { |
| 0936 | if (iConn.State != ConnectionState.Open) |
| 0937 | iConn.Open(); |
| 0938 | iCmd = this.GetCommand(); |
| 0939 | iCmd.Connection = iConn; |
| 0940 | iCmd.CommandText = cmdText; |
| 0941 | if (iTrans != null) |
| 0942 | iCmd.Transaction = iTrans; |
| 0943 | iCmd.CommandType = CommandType.Text;//cmdType; |
| 0944 | if (iParms != null) |
| 0945 | { |
| 0946 | foreach (IDataParameter parm in iParms) |
| 0947 | iCmd.Parameters.Add(parm); |
| 0948 | } |
| 0949 | } |
| 0950 |
| 0951 | #endregion |
| 0952 |
| 0953 | 存储过程操作#region 存储过程操作 |
| 0954 |
| 0955 | /**//// <summary> |
| 0956 | /// 执行存储过程 |
| 0957 | /// </summary> |
| 0958 | /// <param name="storedProcName">存储过程名</param> |
| 0959 | /// <param name="parameters">存储过程参数</param> |
| 0960 | /// <returns>SqlDataReader</returns> |
| 0961 | public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters ) |
| 0962 | { |
| 0963 | System.Data.IDbConnection iConn = this.GetConnection(); |
| 0964 | { |
| 0965 | iConn.Open(); |
| 0966 |
| 0967 | using(SqlCommand sqlCmd = BuildQueryCommand(iConn,storedProcName, parameters)) |
| 0968 | { |
| 0969 | return sqlCmd.ExecuteReader(CommandBehavior.CloseConnection); |
| 0970 | } |
| 0971 | } |
| 0972 | } |
| 0973 |
| 0974 | /**//// <summary> |
| 0975 | /// 执行存储过程 |
| 0976 | /// </summary> |
| 0977 | /// <param name="storedProcName">存储过程名</param> |
| 0978 | /// <param name="parameters">存储过程参数</param> |
| 0979 | /// <param name="tableName">DataSet结果中的表名</param> |
| 0980 | /// <returns>DataSet</returns> |
| 0981 | public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters ,string tableName) |
| 0982 | { |
| 0983 |
| 0984 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 0985 | { |
| 0986 | DataSet dataSet = new DataSet(); |
| 0987 | iConn.Open(); |
| 0988 | System.Data.IDataAdapter iDA = this.GetAdapater(); |
| 0989 | iDA = this.GetAdapater( BuildQueryCommand(iConn, storedProcName, parameters ) ); |
| 0990 |
| 0991 | ((SqlDataAdapter)iDA).Fill( dataSet,tableName); |
| 0992 | if(iConn.State != ConnectionState.Closed) |
| 0993 | { |
| 0994 | iConn.Close(); |
| 0995 | } |
| 0996 | return dataSet; |
| 0997 | } |
| 0998 | } |
| 0999 |
| 1000 |
| 1001 |
| 1002 | /**//// <summary> |
| 1003 | /// 执行存储过程 |
| 1004 | /// </summary> |
| 1005 | /// <param name="storedProcName">存储过程名</param> |
| 1006 | /// <param name="parameters">存储过程参数</param> |
| 1007 | /// <param name="tableName">DataSet结果中的表名</param> |
| 1008 | /// <param name="startIndex">开始记录索引</param> |
| 1009 | /// <param name="pageSize">页面记录大小</param> |
| 1010 | /// <returns>DataSet</returns> |
| 1011 | public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters ,int startIndex,int pageSize,string tableName) |
| 1012 | { |
| 1013 |
| 1014 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 1015 | { |
| 1016 | DataSet dataSet = new DataSet(); |
| 1017 | iConn.Open(); |
| 1018 | System.Data.IDataAdapter iDA = this.GetAdapater(); |
| 1019 | iDA = this.GetAdapater( BuildQueryCommand(iConn, storedProcName, parameters ) ); |
| 1020 |
| 1021 | ((SqlDataAdapter)iDA).Fill( dataSet,startIndex,pageSize,tableName); |
| 1022 | if(iConn.State != ConnectionState.Closed) |
| 1023 | { |
| 1024 | iConn.Close(); |
| 1025 | } |
| 1026 | return dataSet; |
| 1027 | } |
| 1028 | } |
| 1029 |
| 1030 | /**//// <summary> |
| 1031 | /// 执行存储过程 填充已经存在的DataSet数据集 |
| 1032 | /// </summary> |
| 1033 | /// <param name="storeProcName">存储过程名称</param> |
| 1034 | /// <param name="parameters">存储过程参数</param> |
| 1035 | /// <param name="dataSet">要填充的数据集</param> |
| 1036 | /// <param name="tablename">要填充的表名</param> |
| 1037 | /// <returns></returns> |
| 1038 | public DataSet RunProcedure(string storeProcName,IDataParameter[] parameters,DataSet dataSet,string tableName) |
| 1039 | { |
| 1040 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 1041 | { |
| 1042 | iConn.Open(); |
| 1043 | System.Data.IDataAdapter iDA = this.GetAdapater(); |
| 1044 | iDA = this.GetAdapater(BuildQueryCommand(iConn,storeProcName,parameters)); |
| 1045 |
| 1046 | ((SqlDataAdapter)iDA).Fill(dataSet,tableName); |
| 1047 |
| 1048 | if(iConn.State != ConnectionState.Closed) |
| 1049 | { |
| 1050 | iConn.Close(); |
| 1051 | } |
| 1052 |
| 1053 | return dataSet; |
| 1054 | } |
| 1055 | } |
| 1056 |
| 1057 | /**//// <summary> |
| 1058 | /// 执行存储过程并返回受影响的行数 |
| 1059 | /// </summary> |
| 1060 | /// <param name="storedProcName"></param> |
| 1061 | /// <param name="parameters"></param> |
| 1062 | /// <returns></returns> |
| 1063 | public int RunProcedureNoQuery(string storedProcName, IDataParameter[] parameters ) |
| 1064 | { |
| 1065 |
| 1066 | int result = 0; |
| 1067 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 1068 | { |
| 1069 | iConn.Open(); |
| 1070 | using(SqlCommand scmd = BuildQueryCommand(iConn,storedProcName,parameters)) |
| 1071 | { |
| 1072 | result = scmd.ExecuteNonQuery(); |
| 1073 | } |
| 1074 |
| 1075 | if(iConn.State != ConnectionState.Closed) |
| 1076 | { |
| 1077 | iConn.Close(); |
| 1078 | } |
| 1079 | } |
| 1080 |
| 1081 | return result ; |
| 1082 | } |
| 1083 |
| 1084 | public string RunProcedureExecuteScalar(string storeProcName,IDataParameter[] parameters) |
| 1085 | { |
| 1086 | string result = string.Empty; |
| 1087 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 1088 | { |
| 1089 |
| 1090 | iConn.Open(); |
| 1091 | using(SqlCommand scmd = BuildQueryCommand(iConn,storeProcName,parameters)) |
| 1092 | { |
| 1093 | object obj = scmd.ExecuteScalar(); |
| 1094 | if(obj == null) |
| 1095 | result = null; |
| 1096 | else |
| 1097 | result = obj.ToString(); |
| 1098 | } |
| 1099 |
| 1100 | if(iConn.State != ConnectionState.Closed) |
| 1101 | { |
| 1102 | iConn.Close(); |
| 1103 | } |
| 1104 |
| 1105 | } |
| 1106 |
| 1107 | return result; |
| 1108 | } |
| 1109 |
| 1110 | /**//// <summary> |
| 1111 | /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值) |
| 1112 | /// </summary> |
| 1113 | /// <param name="connection">数据库连接</param> |
| 1114 | /// <param name="storedProcName">存储过程名</param> |
| 1115 | /// <param name="parameters">存储过程参数</param> |
| 1116 | /// <returns>SqlCommand</returns> |
| 1117 | private SqlCommand BuildQueryCommand(IDbConnection iConn,string storedProcName, IDataParameter[] parameters) |
| 1118 | { |
| 1119 |
| 1120 | IDbCommand iCmd = GetCommand(storedProcName,iConn); |
| 1121 | iCmd.CommandType = CommandType.StoredProcedure; |
| 1122 | if (parameters == null) |
| 1123 | { |
| 1124 | return (SqlCommand)iCmd; |
| 1125 | } |
| 1126 | foreach (IDataParameter parameter in parameters) |
| 1127 | { |
| 1128 | iCmd.Parameters.Add( parameter ); |
| 1129 | } |
| 1130 | return (SqlCommand)iCmd; |
| 1131 | } |
| 1132 |
| 1133 | /**//// <summary> |
| 1134 | /// 执行存储过程，返回影响的行数 |
| 1135 | /// </summary> |
| 1136 | /// <param name="storedProcName">存储过程名</param> |
| 1137 | /// <param name="parameters">存储过程参数</param> |
| 1138 | /// <param name="rowsAffected">影响的行数</param> |
| 1139 | /// <returns></returns> |
| 1140 | public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected ) |
| 1141 | { |
| 1142 | using (System.Data.IDbConnection iConn = this.GetConnection()) |
| 1143 | { |
| 1144 | int result; |
| 1145 | iConn.Open(); |
| 1146 | using(SqlCommand sqlCmd = BuildIntCommand(iConn,storedProcName, parameters )) |
| 1147 | { |
| 1148 | rowsAffected = sqlCmd.ExecuteNonQuery(); |
| 1149 | result = (int)sqlCmd.Parameters["ReturnValue"].Value; |
| 1150 |
| 1151 | if(iConn.State != ConnectionState.Closed) |
| 1152 | { |
| 1153 | iConn.Close(); |
| 1154 | } |
| 1155 | return result; |
| 1156 | } |
| 1157 | } |
| 1158 | } |
| 1159 |
| 1160 | /**//// <summary> |
| 1161 | /// 创建 SqlCommand 对象实例(用来返回一个整数值) |
| 1162 | /// </summary> |
| 1163 | /// <param name="storedProcName">存储过程名</param> |
| 1164 | /// <param name="parameters">存储过程参数</param> |
| 1165 | /// <returns>SqlCommand 对象实例</returns> |
| 1166 | private SqlCommand BuildIntCommand(IDbConnection iConn,string storedProcName, IDataParameter[] parameters) |
| 1167 | { |
| 1168 | SqlCommand sqlCmd = BuildQueryCommand(iConn,storedProcName, parameters ); |
| 1169 | sqlCmd.Parameters.Add( new SqlParameter ( "ReturnValue", |
| 1170 | SqlDbType.Int,4,ParameterDirection.ReturnValue, |
| 1171 | false,0,0,string.Empty,DataRowVersion.Default,null )); |
| 1172 | return sqlCmd; |
| 1173 | } |
| 1174 | #endregion |
| 1175 |
| 1176 |
| 1177 | } |
| 1178 | } |
相关阅读 更多 +
排行榜 更多 +
