大家看看我这个数据库架子怎么样？

Question

using System;
using System.Data;
using System.Data.SqlClient;public class Database
{
    private static SqlConnection NewConnection(string sConnection)
    { 
    
    }    public class Procedure
    {
        private static SqlCommand NewCommand()
        { 
        
        }        public static void Execute(string sConnection, string sProcedureName)
        {        }        public static DataSet GetDataSetByExecute(string sConnecion, string sProcedureName)
        {        }        public static DataSet GetDataSetByExecute(string sConnecion, string sProcedureName,SqlParameter[]spParams)
        {        }        public static DataTable GetDataTableByExecute(string sConnecion, string sProcedureName)
        {        }        public static DataSet GetDataTableByExecute(string sConnecion, string sProcedureName, SqlParameter[] spParams)
        {        }
    }    public class SQL
    {
        private static SqlCommand NewCommand()
        {        }        public static void Execute(string sConnection, string sSQL)
        {        }        public static DataSet GetDataSetByExecute(string sConnecion, string sSQL)
        {        }        public static DataTable GetDataTableByExecute(string sConnecion, string sSQL)
        {        }
    }
}

Answer 1

贴一个昨天我刚写的using System;
using System.Data;
using System.Text;
using System.Configuration;
using System.Data.Common;
using System.Data.OracleClient;/// <summary>
/// DataAccessService 的摘要说明
/// </summary> 
namespace HUBOMS.DataAccess
{
    public class DataAccessService
    {
        private OracleConnection connection;        public DataAccessService()
        {
            ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["default"];
            if (connectionString == null)
            {
                throw new Exception("创建 Oracle 数据库连接失败。未在当前 Web 配置中找到名为 default 的 ConnectionString。请在 Web.config 文件的 configuration/connectionString 节，添加“<add name=\"default\" connectionString=\"数据库连接字符\"/>”");
            }
            connection = new OracleConnection(connectionString.ConnectionString);
        }        public int executeNonQuery(string p_sql)
        {
            try
            {
                connection.Open();
                DbCommand cmd = connection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = p_sql;
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                connection.Close();
            }
        }        // 执行指定的 SQL 语句，返回数据集。
        public DataSet getDataSet(string p_sql)
        {
            connection.Open();
            try
            {
                OracleDataAdapter adapter = new OracleDataAdapter();
                adapter.SelectCommand = connection.CreateCommand();
                adapter.SelectCommand.CommandType = CommandType.Text;
                adapter.SelectCommand.CommandText = p_sql;
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            finally
            {
                connection.Close();
            }
        }        // 执行指定的 SQL 语句，返回数据表。
        public DataTable getDataTable(string p_sql)
        {
            connection.Open();
            try
            {
                OracleDataAdapter adapter = new OracleDataAdapter();
                adapter.SelectCommand = connection.CreateCommand();
                adapter.SelectCommand.CommandType = CommandType.Text;
                adapter.SelectCommand.CommandText = p_sql;
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
            finally
            {
                connection.Close();
            }
        }
        // 执行指定的 SQL 语句，返回第一条记录。
        public DataRow getDataRow(string p_sql)
        {
            DataTable dt = this.getDataTable(p_sql);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0];
            }
            return null;
        }
        // 执行指定的 SQL 语句，返回记录集合。
        public DataRowCollection getDataRows(string p_sql)
        {            DataTable dt = this.getDataTable(p_sql);            return dt.Rows;
        }
    }
}

Answer 2

using System;
using System.Data;
using System.Data.SqlClient;/// <summary>
/// 数据库类
/// </summary>
public class Database
{
    /// <summary>
    /// 返回一个实例且打开的数据库连接对象
    /// </summary>
    /// <param name="sConnection">连接字符串</param>
    /// <returns></returns>
    private static SqlConnection NewConnection(string sConnection)
    {
        SqlConnection con = new SqlConnection(sConnection);
        con.Open();
        return con;
    }    /// <summary>
    /// 存储过程类
    /// </summary>
    public class Procedure
    {
        /// <summary>
        /// 返回实例的SQL执行对象
        /// </summary>
        /// <param name="sConnection">连接字符串</param>
        /// <param name="sProcedureName">存储过程名</param>
        /// <param name="spParams">参数</param>
        /// <returns></returns>
        private static SqlCommand NewCommand(string sConnection, string sProcedureName, SqlParameter[] spParams)
        {
            SqlCommand cmd = new SqlCommand(sProcedureName, NewConnection(sConnection));
            cmd.CommandType = CommandType.StoredProcedure;
            if (spParams != null)
            {
                foreach (SqlParameter item in spParams)
                {
                    cmd.Parameters.Add(item);
                }
            }
            return cmd;
        }        /// <summary>
        /// 执行无参数无返回结果的存储过程
        /// </summary>
        /// <param name="sConnection">连接字符串</param>
        /// <param name="sProcedureName">存储过程名</param>
        public static void Execute(string sConnection, string sProcedureName)
        {
            SqlCommand cmd = NewCommand(sConnection, sProcedureName, null);
            cmd.Connection.Close();
        }        /// <summary>
        /// 执行一个无参数的存储过程,返回一个数据缓存
        /// </summary>
        /// <param name="sConnecion">连接字符串</param>
        /// <param name="sProcedureName">存储过程名</param>
        /// <returns></returns>
        public static DataSet GetDataSetByExecute(string sConnecion, string sProcedureName)
        {
            return GetDataSetByExecute(sConnecion, sProcedureName, null);
        }        /// <summary>
        ///  执行一个有参数的存储过程,返回一个数据缓存
        /// </summary>
        /// <param name="sConnecion">连接字符串</param>
        /// <param name="sProcedureName">存储过程名</param>
        /// <param name="spParams">存储过程参数</param>
        /// <returns></returns> 
        public static DataSet GetDataSetByExecute(string sConnecion, string sProcedureName, SqlParameter[] spParams)
        {
            SqlDataAdapter dataadapter = new SqlDataAdapter();
            DataSet dataset = new DataSet();            dataadapter.SelectCommand = NewCommand(sConnecion, sProcedureName, spParams);
            dataadapter.Fill(dataset);
            dataadapter.SelectCommand.Connection.Close();            return dataset;
        }        /// <summary>
        /// 执行一个无参数的存储过程,返回一个数据缓存
        /// </summary>
        /// <param name="sConnecion">连接字符串</param>
        /// <param name="sProcedureName">存储过程名</param>
        /// <returns></returns>
        public static DataTable GetDataTableByExecute(string sConnecion, string sProcedureName)
        {
            return GetDataSetByExecute(sConnecion, sProcedureName).Tables[0];
        }        /// <summary>
        /// 执行一个有参数的存储过程,返回一个数据缓存
        /// </summary>
        /// <param name="sConnecion">连接字符串</param>
        /// <param name="sProcedureName">存储过程名</param>
        /// <param name="spParams">存储过程参数</param>
        /// <returns></returns>
        public static DataTable GetDataTableByExecute(string sConnecion, string sProcedureName, SqlParameter[] spParams)
        {
            return GetDataSetByExecute(sConnecion, sProcedureName, spParams).Tables[0];
        }
    }    /// <summary>
    /// SQL类
    /// </summary>
    public class SQL
    {
        /// <summary>
        /// 返回实例的SQL执行对象
        /// </summary>
        /// <param name="sConnection">连接字符串</param>
        /// <param name="sProcedureName">存储过程名</param>
        /// <returns></returns>
        private static SqlCommand NewCommand(string sConnection, string sSQL)
        {
            SqlCommand cmd = new SqlCommand(sSQL, NewConnection(sConnection));
            cmd.CommandType = CommandType.Text;
            return cmd;
        }        /// <summary>
        /// 执行无参数无返回结果的存储过程
        /// </summary>
        /// <param name="sConnection">连接字符串</param>
        /// <param name="sProcedureName">存储过程名</param>
        public static void Execute(string sConnection, string sSQL)
        {
            SqlCommand cmd = NewCommand(sConnection, sSQL);
            cmd.Connection.Close();
        }        /// <summary>
        /// 执行一个无参数的存储过程,返回一个数据缓存
        /// </summary>
        /// <param name="sConnecion">连接字符串</param>
        /// <param name="sProcedureName">存储过程名</param>
        /// <returns></returns>
        public static DataSet GetDataSetByExecute(string sConnecion, string sSQL)
        {
            SqlDataAdapter dataadapter = new SqlDataAdapter();
            DataSet dataset = new DataSet();            dataadapter.SelectCommand = NewCommand(sConnecion, sSQL);
            dataadapter.Fill(dataset);
            dataadapter.SelectCommand.Connection.Close();            return dataset;
        }        /// <summary>
        /// 执行一个无参数的存储过程,返回一个数据缓存
        /// </summary>
        /// <param name="sConnecion">连接字符串</param>
        /// <param name="sProcedureName">存储过程名</param>
        /// <returns></returns>
        public static DataTable GetDataTableByExecute(string sConnecion, string sSQL)
        {
            return GetDataSetByExecute(sConnecion, sSQL).Tables[0];
        }
    }
}

Answer 3

.... 就是一个简单的DaoHelper类 
建议楼主用连接池来提高效率

Answer 4

每个函数都要求一个 string sConnecion 参数是不是太烦了？ 完全可以抽出来的

Answer 5

楼主这一套,我过去vs2003,vs2003就是搞这一套的,
现在是vs2005,vs2008,应该多关注新的东东,
vs2005,vs2008中的强类型的DataSet,完全不需要如此所谓"架子"!!

Answer 6

微软有写好的SQLHELPERLZ可以参考下，不过个人觉得写的很烂，完全就是报装了下ADO.NET而已

Answer 7

应该通过工厂模式实现。可看看petshop.
http://www.cnblogs.com/xw885/articles/110582.html