博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

Asp.net C# DBHelper类

Posted on 2011-04-23 09:24  moss_tan_jun  阅读(733)  评论(1编辑  收藏  举报

public static class DBHelper
    {
        //数据库连接属性
        private static SqlConnection connection;
        public static SqlConnection Connection
        {
            get
            {
                string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }
        /// <summary>
        /// 执行带参存储过程s
        /// </summary>
        public static void ExecProc(string strProName, SqlParameter[] para)
        {
            connection.Open();
            try
            {
                SqlCommand sqlcmd = connection.CreateCommand();
                sqlcmd.CommandText = strProName;
                sqlcmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter paras in para)
                {
                    sqlcmd.Parameters.Add(paras);
                }
                sqlcmd.ExecuteNonQuery();
            }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// 根据sql语句执行非查询操作
        /// </summary>
        public static bool ExecNoQuery(string sql)
        {
           
            try
            {
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = sql;
                return true;
            }
            catch
            {
                return false;
            }
           
        }
        /// <summary>
        /// 根据sql语句获得一个DataTable
        /// </summary>
        public static DataTable ExecQuery(string Sql)
        {
            connection.Open();
            SqlDataAdapter da = new SqlDataAdapter(Sql, connection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
            connection.Close();
         
        }
        /// <summary>
        /// 执行无参SQL语句
        /// </summary>
        public static int ExecuteCommand(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = cmd.ExecuteNonQuery();
            return result;
        }
        /// <summary>
        /// 执行带参SQL语句
        /// </summary>
        public static int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// 执行无参SQL语句,并返回执行记录数
        /// </summary>
        public static int GetScalar(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }
        /// <summary>
        /// 执行有参SQL语句,并返回执行记录数
        /// </summary>
        public static int GetScalar(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }
        /// <summary>
        /// 执行无参SQL语句,并返回SqlDataReader
        /// </summary>
        public static SqlDataReader GetReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        /// <summary>
        /// 执行有参SQL语句,并返回SqlDataReader
        /// </summary>
        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

        public static DataTable GetDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }

        public static DataTable GetDataSet(string sql, params SqlParameter[] values)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }
        /// <summary>
        /// 根据sql语句获得一个单值字符串
        /// </summary>
        public static string ReturnStringScalar(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            try
            {
                string result = cmd.ExecuteScalar().ToString();
                return result;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return "0";
            }
            connection.Close();
        }
        /// <summary>
        /// 执行事务处理
        /// </summary>
        public static bool ExecTSQL(string[] sqls)
        {
            connection.Open();
            SqlTransaction trans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                for (int i = 0; i < sqls.Length; i++)
                {
                    if (sqls[i] == "" || sqls[i] == null)
                    {
                        continue;
                    }
                    SqlCommand cmd = connection.CreateCommand();
                    cmd.Transaction = trans;
                    cmd.CommandText = sqls[i];
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
                return true;
            }
            catch
            {

                trans.Rollback();
                return false;
            }
            finally
            {
                trans = null;
                connection.Close();
            }
        }

    }