【下载文档:  asp.net 数据库的连接和datatable类.txt 】

---

asp.net 数据库的连接和datatable类
using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; ///

/// SqlConnections 的摘要说明 ///

namespace System.mySQLConnection { public class SqlConnections { //当前连接的是第几个。 static int count = 0; //最大连接量 static int maxCount = 30; static SqlConnection[] sqlConns = new SqlConnection[maxCount]; //自己创建自己 static SqlConnections myConn = new SqlConnections(); //初始化 private SqlConnections() { for (int i = 0; i < maxCount; i++) { sqlConns[i] = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ADOStr"].ToString()); } } //根据条件来获取不同的表，与字段。 public static DataTable GetDataTable(String sqlTest, String tableName) { try { DataSet myDS = new DataSet(); SqlDataAdapter myAdapater = new SqlDataAdapter(sqlTest, SqlConnections.GetSQLConnection()); myAdapater.Fill(myDS, tableName); return myDS.Tables[tableName]; } catch (SqlException sqlE) { return null; } } // 获取数据库连接 public static SqlConnection GetSQLConnection() { try { try { sqlConns[count].Close(); } catch (Exception eeX) { } return sqlConns[count]; } finally { //当前个数自加。。 if (count == (maxCount - 1)) { count = 0; } else { count++; } } } //返回一个参数 public static String GetNumOneField(String sqlTxt) { SqlConnection sqlConnTemp = SqlConnections.GetSQLConnection(); try { SqlCommand sqlCommT = new SqlCommand(sqlTxt, sqlConnTemp); sqlConnTemp.Open(); return sqlCommT.ExecuteScalar().ToString(); } catch (Exception sqlE) { return "没有"; } finally { sqlConnTemp.Close(); } } //返回一个参数 public static String GetNumOneField(SqlConnection sqlConnTemp,SqlTransaction tempSqlTran ,String sqlTxt) { try { SqlCommand sqlCommT = new SqlCommand(sqlTxt, sqlConnTemp); sqlCommT.Transaction = tempSqlTran; return sqlCommT.ExecuteScalar().ToString(); } catch (Exception sqlE) { return "没有"; } finally { //sqlConnTemp.Close(); } } ///

/// 做 ，修改，删除， ///

/// 执行的SQL语句 /// 返回的结果影响多少行 public static int UpdateOrDelete(String sqlTxt) { SqlConnection sqlConnTemp = SqlConnections.GetSQLConnection(); try { SqlCommand sqlCommT = new SqlCommand(sqlTxt, sqlConnTemp); sqlConnTemp.Open(); return sqlCommT.ExecuteNonQuery(); } catch (SqlException sqlE) { return 0; } finally { //sqlConnTemp.Close(); } } public static int UpdateOrDelete(CommandType _CommandType, SqlParameter[] _sqlParas, String sqlTxt) { SqlConnection sqlConnTemp = SqlConnections.GetSQLConnection(); try { SqlCommand sqlCommT = new SqlCommand(sqlTxt, sqlConnTemp); sqlCommT.CommandType = _CommandType; sqlCommT.Parameters.Clear(); for (int a = 0; a < _sqlParas.Length; a++) { sqlCommT.Parameters.Add(_sqlParas[a]); } sqlConnTemp.Open(); return sqlCommT.ExecuteNonQuery(); } catch (SqlException sqlE) { return 0; } finally { //sqlConnTemp.Close(); } } ///

/// 带事务的增，删，改数据执行方法(警告：该方法必须 手动开关数据,而且还有手动的提交事务。否则不能更新数据) ///

/// 数据库连接对象 /// 事务对象 /// 执行的Sql语句 /// public static int UpdateOrDelete(SqlConnection _SqlConn,SqlTransaction _SqlTran, String sqlTxt) { SqlConnection sqlConnTemp = _SqlConn; try { SqlCommand sqlCommT = new SqlCommand(sqlTxt, sqlConnTemp); sqlCommT.Transaction = _SqlTran; sqlConnTemp.Open(); return sqlCommT.ExecuteNonQuery(); } catch (SqlException sqlE) { return 0; } finally { //sqlConnTemp.Close(); } } public static int UpdateOrDelete(SqlConnection _SqlConn, SqlTransaction _SqlTran, CommandType _CommandType, SqlParameter [] _sqlParas, String sqlTxt) { SqlConnection sqlConnTemp = _SqlConn; try { SqlCommand sqlCommT = new SqlCommand(sqlTxt, sqlConnTemp); sqlCommT.Transaction = _SqlTran; sqlCommT.CommandType = _CommandType; sqlCommT.Parameters.Clear(); for (int a = 0; a < _sqlParas.Length; a++) { sqlCommT.Parameters.Add(_sqlParas[a]); } return sqlCommT.ExecuteNonQuery(); } catch (SqlException sqlE) { return 0; } finally { // sqlConnTemp.Close(); } } ///

/// 有存储过的查询 ///

/// 存储过程的名字 /// 所有参数 /// 表名 /// DataTable的结果集 public static DataTable GetDataTable(String StoredProcedureName, SqlParameter [] sqlParas,String tableName) { try { DataSet myDS = new DataSet(); SqlDataAdapter myAdapater = new SqlDataAdapter(StoredProcedureName, SqlConnections.GetSQLConnection()); myAdapater.SelectCommand.CommandType = CommandType.StoredProcedure; for (int i = 0; i < sqlParas.Length; i++) { //if (myAdapater.SelectCommand.Parameters.Contains(sqlParas[i])) //myAdapater.SelectCommand.Parameters.RemoveAt(sqlParas[i].ParameterName); myAdapater.SelectCommand.Parameters.Add(sqlParas[i]); } myAdapater.Fill(myDS, tableName); myAdapater.SelectCommand.Parameters.Clear(); myAdapater.Dispose(); return myDS.Tables[tableName]; } catch (SqlException sqlE) { return null; } } } }