数据库的连接和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;

/// <summary>
/// SqlConnections 的摘要说明
/// </summary>

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();
            }
        }

        /// <summary>
        /// 做 ,修改,删除,
        /// </summary>
        /// <param name="sqlTxt">执行的SQL语句</param>
        /// <returns>返回的结果影响多少行</returns>

        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();
            }
        }

        /// <summary>
        /// 带事务的增,删,改数据执行方法(警告:该方法必须 手动开关数据,而且还有手动的提交事务。否则不能更新数据)
        /// </summary>
        /// <param name="_SqlConn">数据库连接对象</param>
        /// <param name="_SqlTran">事务对象</param>
        /// <param name="sqlTxt">执行的Sql语句</param>
        /// <returns></returns>
        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();
            }
        }

 

        /// <summary>
        /// 有存储过的查询
        /// </summary>
        /// <param name="StoredProcedureName">存储过程的名字</param>
        /// <param name="sqlParas">所有参数</param>
        /// <param name="tableName">表名</param>
        /// <returns>DataTable的结果集</returns>
        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;
            }
          
        }

    }

}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值