DBHelper

using System;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

/// <summary>
/// 操作数据库 的摘要说明
/// </summary>
public class DataBase
{
    //连接字符串
    public static string Strconnection = "server=.;database=Test;uid=sa;pwd=123456";

    ////创建连接
    //public static SqlConnection ConDataBase = new SqlConnection(Strconnection);
    public static string strPasswordKey = "f?~ 1_?}";
    public DataBase()
    {

        //
        // TODO: 在此处添加构造函数逻辑
        //

    }

    /// <summary>
    /// 执行查询并将查询结果填充到DataSet
    /// </summary>
    /// <param name="CmdType">命令类型 分sql查询语句或存储过程</param>
    /// <param name="StrCmdText">命令字符串</param>
    /// <param name="BolIfPage">是否翻页，true是，false否</param>
    /// <param name="InPageSize">每页显示的记录数，无翻页则为0</param>
    /// <param name="InPageCount">总页数，无翻页则为0</param>
    /// <param name="ParmObj">参数</param>
    /// <returns>返回结果集DataSet</returns>
    public static DataSet ExecuteDataSet(CommandType CmdType, string StrCmdText, bool BolIfPage, int InPageSize, int InPageCount, params SqlParameter[] ParmObj)
    {
        return ExecuteDataSet(Strconnection, CmdType, StrCmdText, BolIfPage, InPageSize, InPageCount, ParmObj);
    }

    /// <summary>
    /// 执行查询并将查询结果填充到DataSet
    /// </summary>
    /// <param name="CmdType">命令类型 分sql查询语句或存储过程</param>
    /// <param name="StrCmdText">命令字符串</param>
    /// <param name="ParmObj">参数</param>
    /// <returns>返回结果集DataSet</returns>
    public static DataSet ExecuteDataSet(CommandType CmdType, string StrCmdText, params SqlParameter[] ParmObj)
    {
        return ExecuteDataSet(Strconnection, CmdType, StrCmdText, false, 0, 0, ParmObj);
    }

    /// <summary>
    /// 执行查询并将查询结果填充到DataSet
    /// </summary>
    /// <param name="StrCon">连接字符串</param>
    /// <param name="CmdType">命令类型</param>
    /// <param name="StrCmdText">命令语句</param>
    /// <param name="ParmObj">参数</param>
    /// <param name="BolIfPage">是否翻页，true是，false否</param>
    /// <param name="InPageSize">每页显示的记录数，无翻页则为0</param>
    /// <param name="InPageCount">总页数，无翻页则为0</param>
    /// <returns>返回结果集DataSet</returns>
    public static DataSet ExecuteDataSet(string StrCon, CommandType CmdType, string StrCmdText, bool BolIfPage, int InPageSize, int InPageCount, params SqlParameter[] ParmObj)
    {
        //StrCon = ConnDb.conn.Decrypt("jxhdjxxjsyxgs", StrCon);
        SqlCommand CmbObj = new SqlCommand();
        using (SqlConnection ConObj = new SqlConnection(StrCon))
        {
            PrepareCommand(CmbObj, ConObj, CmdType, StrCmdText, ParmObj);
            SqlDataAdapter DadObj = new SqlDataAdapter();
            DadObj.SelectCommand = CmbObj;
            DataSet DstObj = new DataSet();
            if (BolIfPage)
            {
                DadObj.Fill(DstObj, InPageCount, InPageSize, "menu");
            }
            else
            {
                DadObj.Fill(DstObj);
            }
            CmbObj.Parameters.Clear();
            DadObj.Dispose();
            return DstObj;
        }
    }


    /// <summary>
    /// 执行SQL操作如：插入，删除或存储过程;操作成功返回受影响数;否则返回0
    /// </summary>
    /// <param name="CmdType">命令类型</param>
    /// <param name="StrCmdText">命令语句</param>
    /// <param name="ParmObj">参数</param>
    /// <returns>操作成功返回受影响数;否则返回0</returns>
    public static int ExecuteNonQuerys(CommandType CmdType, string StrCmdText, params SqlParameter[] ParmObj)
    {
        return ExecuteNonQuerys(Strconnection, CmdType, StrCmdText, ParmObj);
    }


    /// <summary>
    ///  执行SQL操作如：插入，删除或存储过程;操作成功返回受影响数;否则返回0
    /// </summary>
    /// <param name="CmdType">命令类型</param>
    /// <param name="StrCmdText">命令语句</param>
    /// <param name="ParmObj">参数</param>
    /// <returns>操作成功返回受影响数;否则返回0</returns>
    public static int ExecuteNonQuerys(string StrCon, CommandType CmdType, string StrCmdText, params SqlParameter[] ParmObj)
    {
        //StrCon = ConnDb.conn.Decrypt("jxhdjxxjsyxgs", StrCon);
        SqlCommand CmbObj = new SqlCommand();
        CmbObj.CommandTimeout = 60;
        using (SqlConnection ConObj = new SqlConnection(StrCon))
        {
            PrepareCommand(CmbObj, ConObj, CmdType, StrCmdText, ParmObj);
            try
            {
                int i = CmbObj.ExecuteNonQuery();
                CmbObj.Parameters.Clear();
                return i;
            }
            catch
            {
                return 0;
            }
        }
    }

    /// <summary>
    /// 执行SQL操作如：插入，删除或存储过程;操作成功返回1;否则返回0
    /// </summary>
    /// <param name="CmdType">命令类型</param>
    /// <param name="StrCmdText">命令语句</param>
    /// <param name="ParmObj">参数</param>
    /// <returns>操作成功返回1;否则返回0</returns>
    public static int ExecuteNonQuery(CommandType CmdType, string StrCmdText, params SqlParameter[] ParmObj)
    {
        return ExecuteNonQuery(Strconnection, CmdType, StrCmdText, ParmObj);
    }

    /// <summary>
    ///  执行SQL操作如：插入，删除或存储过程;操作成功返回1;否则返回0
    /// </summary>
    /// <param name="CmdType">命令类型</param>
    /// <param name="StrCmdText">命令语句</param>
    /// <param name="ParmObj">参数</param>
    /// <returns>操作成功返回1;否则返回0</returns>
    public static int ExecuteNonQuery(string StrCon, CommandType CmdType, string StrCmdText, params SqlParameter[] ParmObj)
    {
        //StrCon = ConnDb.conn.Decrypt("jxhdjxxjsyxgs", StrCon);
        SqlCommand CmbObj = new SqlCommand();
        CmbObj.CommandTimeout = 60;
        using (SqlConnection ConObj = new SqlConnection(StrCon))
        {
            PrepareCommand(CmbObj, ConObj, CmdType, StrCmdText, ParmObj);
            try
            {
                CmbObj.ExecuteNonQuery();
                CmbObj.Parameters.Clear();
                return 1;
            }
            catch
            {
                return 0;
            }
        }
    }

    /// <summary>
    /// 操作存储过程
    /// </summary>
    /// <param name="strSql">存储过程名或SQL命令</param>
    /// <param name="ParmObj">参数,无参为null</param>
    /// <returns>操作成功返回true;否则返回false</returns>
    public static bool ExecuteProc(string strSql, params SqlParameter[] ParmObj)
    {
        return (ExecuteNonQuery(CommandType.StoredProcedure, strSql, ParmObj) > 0);
    }

    /// <summary>
    /// 操作存储过程
    /// </summary>
    /// <param name="strSql">存储过程名或SQL命令</param>
    /// <returns>操作成功返回true;否则返回false</returns>
    public static bool ExecuteProc(string strSql)
    {
        return (ExecuteNonQuery(CommandType.StoredProcedure, strSql, null) > 0);
    }

    /// <summary>
    /// 执行查询并将查询,返回SqlDataReader
    /// </summary>
    /// <param name="CmdType">执行类型</param>
    /// <param name="StrCmdText">存储过程名或SQL命令</param>
    /// <param name="ParmObj">参数，无参数为null</param>
    /// <returns>返回SqlDataReader</returns>
    public static SqlDataReader ExecuteReader(CommandType CmdType, string StrCmdText, params SqlParameter[] ParmObj)
    {
        return ExecuteReader(Strconnection, CmdType, StrCmdText, ParmObj);
    }

    /// <summary>
    /// 执行查询并将查询,返回SqlDataReader
    /// </summary>
    /// <param name="StrCon">连接字符串</param>
    /// <param name="CmdType">执行类型</param>
    /// <param name="StrCmdText">存储过程名或SQL命令</param>
    /// <param name="ParmObj">参数，无参数为null</param>
    /// <returns>返回SqlDataReader</returns>
    public static SqlDataReader ExecuteReader(string StrCon, CommandType CmdType, string StrCmdText, params SqlParameter[] ParmObj)
    {
        //StrCon = ConnDb.conn.Decrypt("jxhdjxxjsyxgs", StrCon);
        SqlDataReader DtrObj;
        SqlCommand CmdObj = new SqlCommand();
        SqlConnection ConObj = new SqlConnection(StrCon);
        try
        {
            PrepareCommand(CmdObj, ConObj, CmdType, StrCmdText, ParmObj);
            SqlDataReader reader = CmdObj.ExecuteReader(CommandBehavior.CloseConnection);
            CmdObj.Parameters.Clear();
            DtrObj = reader;
        }
        catch
        {
            throw;
        }
        return DtrObj;
    }

    /// <summary>
    /// PrepareCommand
    /// </summary>
    /// <param name="CmdObj">SqlCommand</param>
    /// <param name="ConObj">SqlConnection</param>
    /// <param name="CmdType">执行类型</param>
    /// <param name="StrCmdText">存储过程名或SQL命令</param>
    /// <param name="ParmObj">参数，无参数为null</param>
    private static void PrepareCommand(SqlCommand CmdObj, SqlConnection ConObj, CommandType CmdType, string StrCmdText, SqlParameter[] ParmObj)
    {
        if (ConObj.State != ConnectionState.Open)
        {
            ConObj.Open();
        }
        CmdObj.Connection = ConObj;
        CmdObj.CommandText = StrCmdText;
        CmdObj.CommandTimeout = 1000;
        CmdObj.CommandType = CmdType;
        if (ParmObj != null)
        {
            foreach (SqlParameter parameter in ParmObj)
            {
                CmdObj.Parameters.Add(parameter);
            }
        }
    }

    /// <summary>
    /// 操作存储过程或SQL返回结果集的第一行第一列
    /// </summary>
    /// <param name="CmdType">执行类型</param>
    /// <param name="StrCmdText">存储过程名或SQL命令</param>
    /// <param name="ParmObj">参数，无参数为null</param>
    /// <returns>返回结果集</returns>
    public static object ExecuteScalar(CommandType CmdType, string StrCmdText, params SqlParameter[] ParmObj)
    {
        return ExecuteScalar(Strconnection, CmdType, StrCmdText, ParmObj);
    }

    /// <summary>
    /// 操作存储过程或SQL返回结果集的第一行第一列
    /// </summary>
    /// <param name="CmdType">执行类型</param>
    /// <param name="StrCmdText">存储过程名或SQL命令</param>
    /// <param name="ParmObj">参数，无参数为null</param>
    /// <returns>返回结果集object</returns>
    public static object ExecuteScalar(SqlConnection ConObj, CommandType CmdType, string StrCmdText, params SqlParameter[] ParmObj)
    {
        SqlCommand CmdObj = new SqlCommand();
        PrepareCommand(CmdObj, ConObj, CmdType, StrCmdText, ParmObj);
        object obj2 = CmdObj.ExecuteScalar();
        CmdObj.Parameters.Clear();
        return obj2;
    }

    /// <summary>
    /// 操作存储过程或SQL返回结果集的第一行第一列
    /// </summary>
    /// <param name="StrCon">连接字符串</param>
    /// <param name="CmdType">执行类型</param>
    /// <param name="StrCmdText">存储过程名或SQL命令</param>
    /// <param name="ParmObj">参数，无参数为null</param>
    /// <returns>返回结果集object</returns>
    public static object ExecuteScalar(string StrCon, CommandType CmdType, string StrCmdText, params SqlParameter[] ParmObj)
    {
        //StrCon = ConnDb.conn.Decrypt("jxhdjxxjsyxgs", StrCon);
        SqlCommand CmdObj = new SqlCommand();
        using (SqlConnection ConObj = new SqlConnection(StrCon))
        {
            PrepareCommand(CmdObj, ConObj, CmdType, StrCmdText, ParmObj);
            object obj2 = CmdObj.ExecuteScalar();
            CmdObj.Parameters.Clear();
            return obj2;
        }
    }

    /// <summary>
    /// 操作存储过程或SQL返回结果集DataTable
    /// </summary>
    /// <param name="CmdType">执行类型</param>
    /// <param name="StrCmdText">存储过程名或SQL命令</param>
    /// <param name="ParmObj">参数，无参数为null</param>
    /// <returns>返回结果集DataTable</returns>
    public static DataTable ExecuteTable(CommandType CmdType, string StrCmdText, params SqlParameter[] ParmObj)
    {
        return ExecuteTable(Strconnection, CmdType, StrCmdText, ParmObj);
    }

    /// <summary>
    /// 操作存储过程或SQL返回结果集DataTable
    /// </summary>
    /// <param name="ConObj">SqlConnection</param>
    /// <param name="CmdType">执行类型</param>
    /// <param name="StrCmdText">存储过程名或SQL命令</param>
    /// <param name="ParmObj">参数，无参数为null</param>
    /// <returns>返回结果集DataTable</returns>
    public static DataTable ExecuteTable(SqlConnection ConObj, CommandType CmdType, string StrCmdText, params SqlParameter[] ParmObj)
    {
        SqlCommand CmdObj = new SqlCommand();
        PrepareCommand(CmdObj, ConObj, CmdType, StrCmdText, ParmObj);
        SqlDataAdapter DadObj = new SqlDataAdapter();
        DadObj.SelectCommand = CmdObj;
        DataSet DstObj = new DataSet();
        DadObj.Fill(DstObj, "Result");
        CmdObj.Parameters.Clear();
        return DstObj.Tables["Result"];
    }

    /// <summary>
    /// 操作存储过程或SQL返回结果集DataTable
    /// </summary>
    /// <param name="StrCon">连接字符串</param>
    /// <param name="CmdType">执行类型</param>
    /// <param name="StrCmdText">存储过程名或SQL命令</param>
    /// <param name="parm">参数，无参数为null</param>
    /// <returns>返回结果集DataTable</returns>
    public static DataTable ExecuteTable(string StrCon, CommandType CmdType, string StrCmdText, params SqlParameter[] ParmObj)
    {
        //StrCon = ConnDb.conn.Decrypt("jxhdjxxjsyxgs", StrCon);
        SqlCommand CmdObj = new SqlCommand();
        using (SqlConnection ConObj = new SqlConnection(StrCon))
        {
            PrepareCommand(CmdObj, ConObj, CmdType, StrCmdText, ParmObj);
            SqlDataAdapter DadObj = new SqlDataAdapter();
            DadObj.SelectCommand = CmdObj;
            DataSet DstObj = new DataSet();
            DadObj.Fill(DstObj, "Result");
            CmdObj.Parameters.Clear();
            if (DstObj.Tables.Count > 0)
            {
                return DstObj.Tables["Result"];
            }
            return null;
        }
    }

    /// <summary>
    /// 查询记录是否存在//相同记录
    /// </summary>
    /// <param name="CmdType">执行类型</param>
    /// <param name="StrCmdText">SQL语句或存储过程</param>
    /// <param name="ParmObj">查询参数</param>
    /// <returns>存在返回true 否则false</returns>
    public static bool Exists(CommandType CmdType, string StrCmdText, params SqlParameter[] ParmObj)
    {
        bool Bolfolg = true;

        if ((ObjectToInt32(ExecuteScalar(CmdType, StrCmdText, ParmObj)) > 0))
        {
            Bolfolg = true;
        }
        else
        {
            Bolfolg = false;
        }
        return Bolfolg;
    }

    /// <summary>
    /// 查询记录是否存在//相同记录
    /// </summary>
    /// <param name="strSql">查询sql语句</param>
    /// <returns>存在返回true 否则false</returns>
    public static bool ExistsSql(string strSql)
    {
        return Exists(CommandType.Text, strSql, null);
    }

    /// <summary>
    /// 获取表里某字段的最大值
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="fieldName">字段名</param>
    /// <returns>该字段在表里的最大值</returns>
    public static int GetMaxId(string tableName, string fieldName)
    {
        string query = "select max(" + fieldName + ") from " + tableName;
        return ObjectToInt32(ExecuteScalar(CommandType.Text, query, null));
    }

    /// <summary>
    /// 将数据对象转换成整形
    /// </summary>
    /// <param name="obj"></param>
    /// <returns>返回-1表示转换失败</returns>
    public static int ObjectToInt32(object obj)
    {
        int result = 0;
        try
        {
            if (!(object.Equals(obj, null) || object.Equals(obj, DBNull.Value)))
            {
                int.TryParse(obj.ToString(), out result);
            }
            return result;
        }
        catch
        {
            return -1;
        }
    }

    /// <summary>
    /// 读取sql server数据库结构 输出表名
    /// </summary>
    /// <param name="sType">结构类型 Table表 Views视图</param>
    /// <returns></returns>
    public static DataTable GetSchemaTable()
    {
        DataTable DtblObj;
        using (SqlConnection ConObj = new SqlConnection(Strconnection))
        {
            if (ConObj.State != ConnectionState.Open)
            {
                ConObj.Open();
            }
            try
            {
                DtblObj = ConObj.GetSchema("TABLES");
            }
            catch
            {
                DtblObj = null;
            }
            finally
            {
                ConObj.Close();
            }
        }
        return DtblObj;
    }

    /// <summary>
    /// MD5,SHA1加密
    /// </summary>
    /// <param name="StrPassword">要加密的密码</param>
    /// <returns>返回加密后的字符串</returns>
    public static string Encrypt(string StrPassword)
    {
        string str = "";
        FormsAuthenticationTicket ticket = new System.Web.Security.FormsAuthenticationTicket(StrPassword, true, 2);
        str = FormsAuthentication.Encrypt(ticket).ToString();
        return str;
    }

    /// <summary>
    /// MD5,SHA1加密
    /// </summary>
    /// <param name="StrPassword">要加密的密码</param>
    /// <param name="InFormat">0 为 SHA1,1 为 MD5</param>
    /// <returns>返回加密后的字符串</returns>
    public static string Encrypt(string StrPassword, int InFormat)
    {
        string str = "";
        switch (InFormat)
        {
            case 0:
                str = FormsAuthentication.HashPasswordForStoringInConfigFile(StrPassword, "SHA1");
                break;
            case 1:
                str = FormsAuthentication.HashPasswordForStoringInConfigFile(StrPassword, "MD5");
                break;
        }
        return str;
    }

    /// <summary>
    /// 绑定DropDownList控件，取得选中值
    /// </summary>
    /// <param name="str_Value">数据库表示Value值字段</param>
    /// <param name="str_Text">数据库表示Text值字段</param>
    /// <param name="str_Value_Field"></param>
    /// <param name="str_Sql"></param>
    /// <param name="myDropDownList"></param>
    public static void SelectBindDropDownListValue(string str_Value, string str_Text, string str_Value_Field, string str_Sql, DropDownList myDropDownList)
    {
        BindDropDownList(str_Value, str_Text, str_Sql, myDropDownList);// 绑定myDropDownList控件
        if (myDropDownList.Items.Count > 0)
        {
            myDropDownList.Items[0].Selected = false;
            for (int i = 0; i < myDropDownList.Items.Count; i++)
            {
                if (str_Value_Field == myDropDownList.Items[i].Value)
                {
                    myDropDownList.Items[i].Selected = true;
                    break;
                }
            }
        }
    }

    /// <summary>
    /// 绑定CheckBoxList控件并显示数据,CheckBoxList控件Value,Text值将分别等于等于str_Value,str_Text值
    /// </summary>
    /// <param name="str_Value">绑定DropDownList控件Value值相对应数据库表字段名</param>
    /// <param name="str_Text">绑定DropDownList控件Text值相对应数据库表字段名</param>
    /// <param name="sql">Select-SQL语句</param>
    /// <param name="myDropDownList">DropDownList控件id值</param>
    public static void BindCheckBoxList(string str_Value, string str_Text, string sql, CheckBoxList myCheckBoxList)
    {
        myCheckBoxList.DataSource = ExecuteDataSet(CommandType.Text, sql, null);
        myCheckBoxList.DataValueField = str_Value;
        myCheckBoxList.DataTextField = str_Text;
        myCheckBoxList.DataBind();
    }

    /// <summary>
    /// 绑定DropDownList控件并显示数据,DropDownList控件Value,Text值将分别等于等于str_Value,str_Text值
    /// </summary>
    /// <param name="str_Value">绑定DropDownList控件Value值相对应数据库表字段名</param>
    /// <param name="str_Text">绑定DropDownList控件Text值相对应数据库表字段名</param>
    /// <param name="sql">Select-SQL语句</param>
    /// <param name="myDropDownList">DropDownList控件id值</param>
    public static void BindDropDownList(string str_Value, string str_Text, string sql, DropDownList myDropDownList)
    {
        myDropDownList.DataSource = ExecuteDataSet(CommandType.Text, sql, null);
        myDropDownList.DataValueField = str_Value;
        myDropDownList.DataTextField = str_Text;
        myDropDownList.DataBind();
    }


    /// <summary>
    /// 查询记录指定值
    /// </summary>
    /// <param name="Strsql">SQL语句</param>
    /// <returns>返回查询的值</returns>
    public static string SelectObject(string Strsql)
    {
        using (SqlConnection ConObj = new SqlConnection( Strconnection))
        {
            ConObj.Open();
            SqlCommand ComObj = new SqlCommand(Strsql, ConObj);
            object obj = ComObj.ExecuteScalar();
            return (obj == null) ? "" : obj.ToString();
        }
    }

    /// <summary>
    /// 执行SQL,返回查询结果
    /// </summary>
    /// <param name="sql">Select-SQL语句</param>
    public static string Exec(string sql)
    {
        string show = "";
        using (SqlConnection con = new SqlConnection( Strconnection))
        {
            try
            {
                con.Open();
                SqlCommand Com = new SqlCommand(sql, con);
                show = Com.ExecuteScalar().ToString();

            }
            catch
            {
                show = "";
            }
            return show.ToString();
        }
    }
    /// <summary>
    /// 执行SQL,返回查询结果
    /// </summary>
    /// <param name="sql">Select-SQL语句</param>
    public static string Exec(string sql, params SqlParameter[] ParmObj)
    {
        string show = "";
        using (SqlConnection con = new SqlConnection( Strconnection))
        {
            try
            {
                con.Open();
                SqlCommand Com = new SqlCommand(sql, con);
                if (ParmObj != null)
                {
                    foreach (SqlParameter parameter in ParmObj)
                    {
                        Com.Parameters.Add(parameter);
                    }
                }
                show = Com.ExecuteScalar().ToString();

            }
            catch
            {
                show = "";
            }
            return show.ToString();
        }
    }
    /// <summary>
    /// 执行SQL,返回查询结果
    /// </summary>
    /// <param name="sql">Select-SQL语句</param>
    public static string Exec(string strConnection, string sql)
    {
        string show = "";
        using (SqlConnection con = new SqlConnection( strConnection))
        {
            try
            {
                con.Open();
                SqlCommand Com = new SqlCommand(sql, con);
                show = Com.ExecuteScalar().ToString();

            }
            catch
            {
                show = "";
            }
            return show.ToString();
        }
    }
    /// <summary>
    /// 事务提交多条SQL语句
    /// </summary>
    /// <param name="sqls">SQL语句</param>
    /// <returns></returns>   
    public static bool Transaction(params string[] sqls)
    {
        using (SqlConnection ConObj = new SqlConnection( Strconnection))
        {
            SqlCommand CmbObj = new SqlCommand();
            CmbObj.CommandTimeout = 60;
            CmbObj.Connection = ConObj;
            ConObj.Open();
            bool isok = true;
            CmbObj.Transaction = ConObj.BeginTransaction();
            try
            {
                foreach (string sql in sqls)
                {
                    if (sql.Length == 0) { continue; }
                    //PrepareCommand(CmbObj, ConObj, CommandType.Text, sql, null);
                    if (ConObj.State != ConnectionState.Open)
                    {
                        ConObj.Open();
                    }
                    CmbObj.CommandTimeout = 1000;
                    CmbObj.CommandText = sql;
                    CmbObj.CommandType = CommandType.Text;
                    CmbObj.ExecuteNonQuery();
                    CmbObj.Parameters.Clear();
                }
                CmbObj.Transaction.Commit();
                isok = true;
            }
            catch
            {
                CmbObj.Transaction.Rollback();
                isok = false;
            }
            finally
            {
                CmbObj.Dispose();
                ConObj.Close();
            }
            return isok;
        }
    }
    /// <summary>
    /// 事务提交多条SQL语句
    /// </summary>
    /// <param name="sqls">SQL语句</param>
    /// <returns></returns>   
    public static bool TransactionOther(string strConnection, params string[] sqls)
    {
        using (SqlConnection ConObj = new SqlConnection( strConnection))
        {
            SqlCommand CmbObj = new SqlCommand();
            CmbObj.CommandTimeout = 60;
            CmbObj.Connection = ConObj;
            ConObj.Open();
            bool isok = true;
            CmbObj.Transaction = ConObj.BeginTransaction();
            try
            {
                foreach (string sql in sqls)
                {
                    if (sql.Length == 0) { continue; }
                    //PrepareCommand(CmbObj, ConObj, CommandType.Text, sql, null);
                    if (ConObj.State != ConnectionState.Open)
                    {
                        ConObj.Open();
                    }
                    CmbObj.CommandTimeout = 1000;
                    CmbObj.CommandText = sql;
                    CmbObj.CommandType = CommandType.Text;
                    CmbObj.ExecuteNonQuery();
                    CmbObj.Parameters.Clear();
                }
                CmbObj.Transaction.Commit();
                isok = true;
            }
            catch
            {
                CmbObj.Transaction.Rollback();
                isok = false;
            }
            finally
            {
                CmbObj.Dispose();
                ConObj.Close();
            }
            return isok;
        }
    }
    /// <summary>
    /// 提交一条SQL语句的事务
    /// </summary>
    /// <param name="sqls"></param>
    /// <returns></returns>

    public static bool Transaction(ref string strSQL)
    {
        SqlConnection ConObj = new SqlConnection( Strconnection);
        SqlCommand CmbObj = new SqlCommand();
        CmbObj.CommandTimeout = 60;
        CmbObj.Connection = ConObj;
        ConObj.Open();
        bool isok = true;
        CmbObj.Transaction = ConObj.BeginTransaction();
        try
        {
            CmbObj.CommandText = strSQL;
            CmbObj.CommandType = CommandType.Text;
            CmbObj.ExecuteNonQuery();
            CmbObj.Parameters.Clear();
            CmbObj.Transaction.Commit();
            isok = true;
        }
        catch
        {
            CmbObj.Transaction.Rollback();
            isok = false;
        }
        finally
        {
            CmbObj.Dispose();
            ConObj.Close();
        }
        return isok;
    }
    /// <summary>
    /// 返回一个DataTable
    /// </summary>
    /// <param name="StrCmdText"></param>
    /// <returns></returns>
    /// 

    public static DataTable ExecuteTable(ref string StrCmdText)
    {
        SqlConnection ConObj = new SqlConnection( Strconnection);
        ConObj.Open();
        SqlCommand cmd = new SqlCommand(StrCmdText, ConObj);
        cmd.CommandTimeout = 1800;
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        sda.Fill(dt);
        sda.Dispose();
        ConObj.Dispose();
        return dt;
    }
    /// <summary>
    /// 返回一个单元格数据
    /// </summary>
    /// <param name="StrCmdText"></param>
    /// <returns></returns>
    public static object ExecuteScalar(ref string StrCmdText)
    {
        SqlConnection ConObj = new SqlConnection( Strconnection);
        ConObj.Open();
        SqlCommand cmd = new SqlCommand(StrCmdText, ConObj);
        object o = cmd.ExecuteScalar();
        ConObj.Dispose();
        return o;
    }
    /// <summary>
    /// 返回受影响行数
    /// </summary>
    /// <param name="StrCmdText"></param>
    /// <returns></returns>
    public static int ExecuteNonQuery(ref string StrCmdText)
    {
        SqlConnection ConObj = new SqlConnection(Strconnection);
        ConObj.Open();
        SqlCommand cmd = new SqlCommand(StrCmdText, ConObj);
        int i = cmd.ExecuteNonQuery();
        ConObj.Dispose();
        return i;
    }
    /// <summary>
    /// 大批量插入数据到数据库
    /// </summary>
    /// <param name="dt"></param>
    /// <returns></returns>
    public static int SqlBulkCopy(DataTable table)
    {
        string connStr = Strconnection;
        SqlBulkCopy bulkCopy = new SqlBulkCopy(connStr, SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers);
        bulkCopy.BulkCopyTimeout = 3600;
        bulkCopy.DestinationTableName = table.TableName;//设置数据库中对象的表名 

        string lei = "";
        //设置数据表table和数据库中表的列对应关系 
        foreach (DataColumn col in table.Columns)
        {
            bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
            lei += col.ColumnName+",";
        }
        //将数据集合和目标服务器库表中的字段对应 

        bulkCopy.WriteToServer(table);//将数据表table复制到数据库中
        return 1;

    }
    public static int SqlBulkCopy(DataTable table,string tablename)
    {
        string connStr = Strconnection;
        SqlBulkCopy bulkCopy = new SqlBulkCopy(connStr, SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers);
        bulkCopy.BulkCopyTimeout = 3600;
        bulkCopy.DestinationTableName = tablename;//设置数据库中对象的表名 

        string lei = "";
        //设置数据表table和数据库中表的列对应关系 
        foreach (DataColumn col in table.Columns)
        {
            bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
            lei += col.ColumnName + ",";
        }
        //将数据集合和目标服务器库表中的字段对应 

        bulkCopy.WriteToServer(table);//将数据表table复制到数据库中
        return 1;

    }
}