关于ASP.NET DBHelper的实用写法！

Question

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
namespace Dal
{
    public static class DBC
    {
        private static SqlConnection con;
        /// <summary>
        /// 连接数据库？
        /// </summary>
        public static SqlConnection Con
        {
            get
            {
                string conString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString.ToString();
                if (con == null)
                {
                    con = new SqlConnection(conString);
                    con.Open();
                }
                else if (con.State == System.Data.ConnectionState.Closed)
                {
                    con.Open();
                }
                else if (con.State == System.Data.ConnectionState.Broken)
                {
                    con.Close();
                    con.Open();
                }
                return DBC.con;
            }
        }
        /// <summary>
        /// 检测是否含有危险字符（防止Sql注入）？
        /// </summary>
        /// <param name="contents">预检测的内容</param>
        /// <returns>返回True或false</returns>
        public static bool HasDangerousContents(string contents)
        {
            bool bReturnValue = false;
            if (contents.Length > 0)
            {
                //convert to lower
                string sLowerStr = contents.ToLower();
                //RegularExpressions
                string sRxStr = @"(\sand\s)|(\sand\s)|(\slike\s)|(select\s)|(insert\s)|(delete\s)|(update\s[\s\S].*\sset)|(create\s)|(\stable)|(<[iframe|/iframe|script|/script])|(')|(\sexec)|(\sdeclare)|(\struncate)|(\smaster)|(\sbackup)|(\smid)|(\scount)";
                //Match
                bool bIsMatch = false;
                System.Text.RegularExpressions.Regex sRx = new System.Text.RegularExpressions.Regex(sRxStr);
                bIsMatch = sRx.IsMatch(sLowerStr, 0);
                if (bIsMatch)
                {
                    bReturnValue = true;
                }
            }
            return bReturnValue;
        }
        /// <summary>
        /// 密码加密？
        /// </summary>
        /// <param name="password"></param>
        /// <returns></returns>
        public static string Encrypt(string password)
        {
            System.Security.Cryptography.MD5 md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
            string password_md5 = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(password, "MD5");
            return password_md5;
        }
        /// <summary>
        /// 执行sql语句/存储过程，返回影响的行数？
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string sqlStr)
        {
            int result = 0;
            SqlCommand cmd = new SqlCommand(sqlStr, Con);
            try
            {
                result = cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (SqlException se)
            {
                throw se;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return result;
        }
        /// <summary>
        /// 执行sql语句，返回影响的行数？
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static int ExecuteCommandByParam(string sqlStr, params SqlParameter[] values)
        {
            int result = 0;
            SqlCommand cmd = new SqlCommand(sqlStr, Con);
            try
            {
                if (values != null)
                { //判断是否有参数                     foreach (SqlParameter para in values)//循环添加参数 
                    {                        cmd.Parameters.Add(para);
                    }
                }
                //cmd.Parameters.AddRange(values);
                result = cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (SqlException se)
            {
                throw se;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return result;
        }
        /// <summary>
        /// 执行cmd,返回一个结果？
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public static int ExecuteNonQueryByCmd(SqlCommand cmd)
        {
            int Result = 0;
            try
            {
                cmd.Connection = Con;
                Result = cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            return Result;
        }
        /// <summary>
        ///  执行sql语句，返回影响的行数？
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="paramName"></param>
        /// <param name="paramValue"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sqlStr, string[] paramName, string[] paramValue)
        {
            int result = 0;            SqlCommand cmd = new SqlCommand(sqlStr, Con);
            try
            {
                for (int i = 0; i < paramName.Length; i++)
                {
                    cmd.Parameters.AddWithValue(paramName[i], paramValue[i]);
                }
                result = cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (SqlException se)
            {
                throw se;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return result;
        }
        ======================================
请“专业人员”点评/指点！

Answer 1

/// <summary>
        /// 执行sql语句/存储过程，返回第一行的第一列，比如插入新记录，返回自增的id？
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        public static int GetScalar(string sqlStr)
        {
            int result = 0;
            SqlCommand cmd = new SqlCommand(sqlStr, Con);
            try
            {
                result = Convert.ToInt32(cmd.ExecuteScalar());
                con.Close();
            }
            catch (SqlException se)
            {
                throw se;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return result;
        }
        /// <summary>
        ///  执行sql语句，返回影响的行数？
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static int GetScalarByParamArray(string sqlStr, params SqlParameter[] values)
        {
            int result = 0;
            SqlCommand cmd = new SqlCommand(sqlStr, Con);
            try
            {
                cmd.Parameters.AddRange(values);
                result = Convert.ToInt32(cmd.ExecuteScalar());
                con.Close();
            }
            catch (SqlException se)
            {
                throw se;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return result;
        }
        /// <summary>
        ///  执行sql语句，返回一个Object？
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="paramName"></param>
        /// <param name="paramValue"></param>
        /// <returns></returns>
        public static object ExecuteScalarByParam(string sqlStr, string[] paramName, string[] paramValue)
        {
            object obj = null;
            SqlCommand cmd = new SqlCommand(sqlStr, Con);
            try
            {
                for (int i = 0; i < paramName.Length; i++)
                {
                    cmd.Parameters.AddWithValue(paramName[i], paramValue[i]);
                }
                obj = cmd.ExecuteScalar();
                con.Close();
            }
            catch (SqlException se)
            {
                throw se;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return obj;
        }
        /// <summary>
        /// 返回一个Object？
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>        public static object GetObject(string sql)
        {
            object obj = null;
            try
            {
                SqlCommand cmd = new SqlCommand(sql, Con);
                obj = cmd.ExecuteScalar();
            }
            catch (SqlException se)
            {
                throw se;
            }
            return obj;
        }
        /// <summary>
        /// 返回一个Object？
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="values"></param>
        /// <returns></returns>        public static object GetObjectByParam(string sqlStr, params SqlParameter[] values)
        {
            object obj = null;
            try
            {
                SqlCommand cmd = new SqlCommand(sqlStr, Con);
                cmd.Parameters.AddRange(values);
                obj = cmd.ExecuteScalar();
            }
            catch (SqlException se)
            {
                throw se;
            }
            return obj;
        }
        

Answer 2

/// <summary>
        /// 执行sql语句/存储过程，返回一个DataReader？
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sqlStr)
        {
            SqlDataReader reader = null;
            SqlCommand cmd = new SqlCommand(sqlStr, Con);
            try
            {
                reader = cmd.ExecuteReader();
                //reader.Close();
            }
            catch (SqlException se)
            {
                throw se;
            }
            finally
            {
                cmd.Dispose();
                //con.Close();
            }
            return reader;
        }
        /// <summary>
        /// 返回一个DataReader？
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static SqlDataReader GetReaderByParam(string sqlStr, params SqlParameter[] values)
        {
            SqlDataReader reader = null;
            SqlCommand cmd = new SqlCommand(sqlStr, Con);
            try
            {
                cmd.Parameters.AddRange(values);
                reader = cmd.ExecuteReader();
                con.Close();
            }
            catch (SqlException se)
            {
                throw se;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return reader;
        }        /// <summary>
        /// 执行sql语句/存储过程，返回一个DataTable？
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static DataTable GetDataSet(string sqlStr)
        {
            SqlCommand cmd = new SqlCommand(sqlStr, Con);
            DataSet ds = new DataSet();
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter(cmd);
                con.Close();
            }
            catch (SqlException se)
            {
                throw se;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            da.Fill(ds);
            return ds.Tables[0];
        }
        /// <summary>
        /// 返回一个DataTable？
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static DataTable GetDataSetByParam(string sqlStr, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sqlStr, Con);
            DataSet ds = new DataSet();
            SqlDataAdapter da =null;
            try
            {
                cmd.Parameters.AddRange(values);
                da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                con.Close();
            }
            catch (SqlException se)
            {
                throw se;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            //da.Fill(ds);
            return ds.Tables[0];
        }
        /// <summary>
        /// 执行带参数SQL语句，返回一张表？
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="paramName"></param>
        /// <param name="paramValue"></param>
        /// <returns></returns>
        public static DataTable GetDataTableByParam(string sqlStr, string[] paramName, string[] paramValue)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = null; ;
            SqlDataAdapter da = null;
            try
            {
                cmd = new SqlCommand(sqlStr, Con);
                for (int i = 0; i < paramName.Length; i++)
                {
                    cmd.Parameters.AddWithValue(paramName[i], paramValue[i]);
                }
                da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (SqlException se)
            {
                throw se;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return dt;
        }
        /// <summary>
        /// 执行带参数的存储过程，返回一张表？
        /// </summary>
        /// <param name="produre"></param>
        /// <param name="paramName"></param>
        /// <param name="paramValue"></param>
        /// <returns></returns>
        public static DataTable GetDataTableByProdure(string produre, string[] paramName, string[] paramValue)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = null;
            SqlDataAdapter da = null;
            try
            {
                cmd = new SqlCommand(produre, Con);
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < paramName.Length; i++)
                {
                    cmd.Parameters.AddWithValue(paramName[i], paramValue[i]);
                }
                da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (SqlException se)
            {
                throw se;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return dt;
        }
        /// <summary>
        /// 执行SqlCommand命令，并返回一张表?
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public static DataTable GetDataTableByCmd(SqlCommand cmd)
        {
            DataTable dt = new DataTable();
            cmd.Connection = con;
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (SqlException se)
            {
                throw se;
            }
            finally
            {
                da.Dispose();
            }
            return dt;
        }
        /// <summary>
        /// 执行带参数的存储过程，并返回受影响的行数？
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="paramName"></param>
        /// <param name="paramValue"></param>
        /// <returns></returns>
        public static int ExecuteProcedure(string procedureName, string[] paramName, string[] paramValue)
        {
            SqlCommand cmd = new SqlCommand(procedureName, Con);
            for (int i = 0; i < paramName.Length; i++)
            {
                cmd.Parameters.AddWithValue(paramName[i], paramValue[i]);
            }
            cmd.CommandType = CommandType.StoredProcedure;
            int result = cmd.ExecuteNonQuery();
            return result;
        }
        /// <summary>
        /// 返回影响行数？
        /// </summary>
        /// <param name="procedureName"></param>
        /// <returns></returns>
        public static int ExecuteProcedureByName(string procedureName)
        {
            SqlCommand cmd = new SqlCommand(procedureName, Con);
            cmd.CommandType = CommandType.StoredProcedure;
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result;
        }
    }
}

Answer 3

本帖最后由 net_lover 于 2010-09-04 17:06:05 编辑

  

Answer 4

ado.net好像还能用会儿吧。这位大侠莫非用linq？

Answer 5

很好很复杂  我们的DBhelper  都很短很实用的  不过没有你功能多