如何实现一个通用的数据库操作类？

[bl_song]

1.想实现一个通用的数据库操作类或者类库，包括sql语句的执行，存储过程的执行等功能，然后通过修改配置文件，实现数据库的切换。
2.下面是我写的操作Access的一个类，大家帮我看看，提出建议！

public abstract class OleDbHelper
    {
        public static readonly string OleDbConnString1 = ConfigurationManager.ConnectionStrings["OleDbConnString1"].ConnectionString;
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        protected OleDbHelper()
        {
        }

        public static void CacheParameters(string cacheKey, params OleDbParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        public static OleDbParameter[] GetCachedParameters(string cacheKey)
        {
            OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];
            if (cachedParms == null)
            {
                return null;
            }
            OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];
            int i = 0;
            int j = cachedParms.Length;
            while (i < j)
            {
                clonedParms[i] = (OleDbParameter)((ICloneable)cachedParms[i]).Clone();
                i++;
            }
            return clonedParms;
        }

        public static int ExecuteNonQuery(OleDbConnection connection, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)
        {
            OleDbCommand cmd = new OleDbCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)
        {
            OleDbCommand cmd = new OleDbCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)
        {
            OleDbCommand cmd = new OleDbCommand();
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        public static OleDbDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)
        {
            OleDbDataReader reader;
            OleDbCommand cmd = new OleDbCommand();
            OleDbConnection conn = new OleDbConnection(connectionString);
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                reader = rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
            return reader;
        }

        public static object ExecuteScalar(OleDbConnection connection, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)
        {
            OleDbCommand cmd = new OleDbCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)
        {
            OleDbCommand cmd = new OleDbCommand();
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (OleDbParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }
    }

[wuyq11]

http://topic.csdn.net/u/20091019/10/4eb4f2d4-0cb6-49ce-abc5-a135ac6b733d.html

[zhubo_1117]

通用的要用反射，要用标准的sql等等

[bl_song]

对于问题1大家给个实例，我好结贴！

[silentwins]

参考SQLHelper

[yasire]

 using (OleDbConnection connection = new OleDbConnection(connectionString))
            {}

看见楼主有的地方用了using，有点地方没用，建议都加上啊～

[vip__888]

SqlHelper。。。微软都写好了
不过楼主 access好像没存储过程吧

[guyehanxinlei]

SQLHelper还可以

[加油馒头]

楼主这个不就是sqlhelp吗？ 有多少区别？

[flyerwing]

估计没有通用的，相对主流通用吧，未知数据库访问类。
程序怎么写都有一处要写死的

[YangLau]

这个也算啊 如果还想简化，就用泛型和反射吧！

[njlywy]

可以借鉴下sqlHelper

[sjhoon]

为何不用Linq来操作一个类库文件：
public class Movies；
{

private string movies;
public List<Movie> Movies( Movie movie )
{
return new movies{movie{title="",data="",...},movie{...}};

}
}

最后在用linq语言：
var query= from m in movies
select m;


就是这个意思，使用也很简单，看看相关书籍就知道了。

[AngleBabyHong]

SQLHelper
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace HotelManager.DAL
{
///<summary>
/// 数据库访问通用方法类
/// </summary>
public static class DBHelper
{
private static SqlConnection connection;//连接对象
public static SqlConnection Connection
{
get
{
string connectionString =ConfigurationManager.ConnectionStrings["HotelManager"].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;
}
}
//执行SQl语句
public static int ExecuteCommand(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
return result;
}
//带参数值的sql
public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
//执行SQl语句的
public static int GetScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}

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;
}
//查询返回记录集对象
public static SqlDataReader GetReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
//带参数值的查询返回记录集对象
public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
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];
}

}
}

[yangniao]

。。。。。。。