using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;using System.Data;namespace DateBase{    public class Helper    {        /// <summary>        /// 定义SqlConnection        /// </summary>        public static SqlConnection conn = null;        /// <summary>        /// 数据库连接 public static string str = ConfigurationManager.AppSettings["MySqlConncetion"];        /// </summary>        public static string str = "Data Source=.;Initial Catalog=MyOffice;User ID=www_jy;password=*****************";        /// <summary>        /// 事物回滚        /// </summary>        public static SqlTransaction trans = null;        /// <summary>        /// 打开数据库连接        /// </summary>        public static SqlConnection Connection        {            get            {                try                {                    if (conn == null)                    {                        conn = new SqlConnection(str);                        conn.Open();                    }                    else if (conn.State == ConnectionState.Closed)                    {                        conn.Open();                    }                    else if (conn.State == ConnectionState.Broken)                    {                        conn.Close();                        conn.Open();                    }                }                catch (Exception e)                {                    //抛出错误                }                return conn;            }        }        /// <summary>        /// 打开事物        /// </summary>        public static void OpenTrans()        {            try            {                trans = Connection.BeginTransaction();            }            catch (Exception e)            {                //抛出异常            }        }        /// <summary>        /// 事物提交        /// </summary>        public static void CommitTrans()        {            trans.Commit();        }        /// <summary>        /// 事物回滚        /// </summary>        public static void RollBackTrans()        {            trans.Rollback();        }        /// <summary>        /// 执行增、删、改        /// </summary>        /// <param name="sql"></param>        /// <returns></returns>        public static int GetExecute(string sql)        {            int num = 0;            try            {                SqlCommand cmd = new SqlCommand(sql, Connection);                cmd.Transaction = trans;                num = cmd.ExecuteNonQuery();            }            catch (Exception e)            {                //抛出异常                RollBackTrans();            }            finally            {                //关闭数据库连接                conn.Close();            }            return num;        }        /// <summary>        /// 有参数执行增、删、改操作        /// </summary>        /// <param name="sql"></param>        /// <param name="pare"></param>        /// <returns></returns>        public static int GetExecute(string sql, SqlParameter[] pare)        {            int num = 0;            try            {                SqlCommand cmd = new SqlCommand(sql, Connection);                cmd.Parameters.AddRange(pare);                cmd.Transaction = trans;                num = cmd.ExecuteNonQuery();            }            catch (Exception e)            {                //抛出异常                RollBackTrans();            }            finally            {                //关闭数据库连接                conn.Close();            }            return num;        }        /// <summary>        /// 查询条数        /// </summary>        /// <param name="sql"></param>        /// <returns></returns>        public static int GetExecuteScalar(string sql)        {            int num = 0;            try            {                SqlCommand cmd = new SqlCommand(sql, Connection);                num = Convert.ToInt32(cmd.ExecuteScalar());            }            catch (Exception e)            {                //抛出异常            }            finally            {                //关闭数据库连接                conn.Close();            }            return num;        }        /// <summary>        /// 条件查询        /// </summary>        /// <param name="sql"></param>        /// <param name="pare"></param>        /// <returns></returns>        public static int GetExecuteScalar(string sql, SqlParameter[] pare)        {            int num = 0;            try            {                SqlCommand cmd = new SqlCommand(sql, Connection);                cmd.Parameters.AddRange(pare);                num = Convert.ToInt32(cmd.ExecuteScalar());            }            catch (Exception e)            {                //抛出异常            }            finally            {                //关闭数据库连接                conn.Close();            }            return num;        }        /// <summary>        /// 查找数据库        /// </summary>        /// <param name="sql"></param>        /// <returns></returns>        public static DataSet GetDataSet(string sql)        {            DataSet ds = new DataSet();            try            {                SqlDataAdapter adapter = new SqlDataAdapter(sql, Connection);                adapter.Fill(ds);            }            catch (Exception e)            {                //抛出异常            }            finally            {                //关闭数据库连接                conn.Close();            }            return ds;        }        /// <summary>        /// 有参查找数据库        /// </summary>        /// <param name="sql"></param>        /// <param name="pare"></param>        /// <returns></returns>        public static DataSet GetDataSet(string sql, SqlParameter[] pare)        {            DataSet ds = new DataSet();            try            {                SqlCommand cmd = new SqlCommand(sql, Connection);                cmd.Parameters.AddRange(pare);                SqlDataAdapter adapter = new SqlDataAdapter(cmd);                adapter.Fill(ds);            }            catch (Exception e)            {                //抛出异常            }            finally            {                //关闭数据库连接                conn.Close();            }            return ds;        }    }}