首页 新闻 搜索 专区 学院

写一个数据库通用类库,需要注意哪些方面以及需要做到怎样的效果

0
悬赏园豆:10 [已解决问题] 解决于 2012-08-17 15:55

想写一个通用数据库通用类库,而且必须要和配置文件打交道,不知道从哪些方面下手!

希望能得到兄弟们的亲身体验的建议!

TimYang的主页 TimYang | 初学一级 | 园豆:12
提问于:2012-08-11 12:12
< >
分享
最佳答案
0

你可以百度一下petshop,petshop里面有个SqlHelper.cs类库,有对数据的增删改查,还有数据的提取,毕竟是微软的东西还是比较权威的,如果不想装,那就把邮箱留下,我把类库发给你

收获园豆:10
唯吴独尊 | 小虾三级 |园豆:707 | 2012-08-12 09:12
其他回答(2)
0

是写连接数据库的,增删改成数据库的吗????

如果是只要的,就好好把ADO.Net学好吧

悟行 | 园豆:12422 (专家六级) | 2012-08-11 15:41
0

贴个代码给你参考吧,参考而已哦,自己去写自己去调试,经验主要是靠自己积累的,加油!!

View Code
/*
 * Name:Kevin Liang
 * URL :http://www.cnblogs.com/FreeDong
 * E-mail:dongdongl@yahoo.cn
 */

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;
using System.Web.Configuration;

/// <summary>
/// DBHelper 的摘要说明
/// </summary>
public class DBHelper
{
    SqlCommand cmd = null;
    public DBHelper()
    {
        cmd = new SqlCommand();
        cmd.Connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LeaveWordString"].ToString());
    }

    /// <summary>
    /// 判断数据是否存在
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="paras">参数</param>
    /// <returns>存在的行,除第一行外全部忽略</returns>
    public int ExecuteScalar(string sql, SqlParameter[] paras)
    {
        int count = 0;
        if (cmd.Connection.State == ConnectionState.Closed)
            cmd.Connection.Open();
        cmd.CommandText = sql;
        foreach (SqlParameter para in paras)
        {
            cmd.Parameters.Add(para);
        }
        try
        {
            count = Convert.ToInt32(cmd.ExecuteScalar());
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            if (cmd.Connection.State == ConnectionState.Open)
                cmd.Connection.Close();
            cmd.Dispose();
            cmd.Parameters.Clear();
        }
        return count;
    }

    /// <summary>
    /// 执行非查询
    /// </summary>
    /// <param name="sql">数据操作SQL</param>
    /// <param name="paras">SQL参数</param>
    /// <returns>受影响行</returns>
    public int ExecuteNonQuery(string sql, SqlParameter[] paras)
    {
        int affectRow = 0;
        if (cmd.Connection.State == ConnectionState.Closed)
            cmd.Connection.Open();
        cmd.CommandText = sql;
        foreach (SqlParameter para in paras)
        {
            cmd.Parameters.Add(para);
        }
        try
        {
            affectRow = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {            
            throw;
        }
        finally
        {
            if (cmd.Connection.State == ConnectionState.Open)
                cmd.Connection.Close();
            cmd.Dispose();
            cmd.Parameters.Clear();
        }
        return affectRow;
    }

    /// <summary>
    /// 获取DataReader对象
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="paras">参数</param>
    /// <returns>DataReader对象</returns>
    public SqlDataReader GetDataReader(string sql, SqlParameter[] paras)
    {
        SqlDataReader sda = null;
        if (cmd.Connection.State == ConnectionState.Closed)
            cmd.Connection.Open();
        cmd.CommandText = sql;
        foreach (SqlParameter para in paras)
        {
            cmd.Parameters.Add(para);
        }
        try
        {
            sda = cmd.ExecuteReader();
        }
        catch (Exception ex)
        {            
            throw;
        }
        finally
        {
            //if (cmd.Connection.State == ConnectionState.Open)
            //    cmd.Connection.Close();
            cmd.Dispose();
            cmd.Parameters.Clear();
        }
        return sda;
    }

    /// <summary>
    /// 获取数据集(无参数)
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <returns>返回数据集Table</returns>
    public DataTable GetTableByQuery(string sql)
    {
        DataTable dt = null;
        DataSet ds = new DataSet();
        if (cmd.Connection.State == ConnectionState.Closed)
            cmd.Connection.Open();
        cmd.CommandText = sql;
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            sda.SelectCommand = cmd;
            sda.Fill(ds);
        }
        dt = ds.Tables[0];
        if (cmd.Connection.State == ConnectionState.Open)
            cmd.Connection.Close();
        cmd.Dispose();
        cmd.Parameters.Clear();
        return dt;
    }

    /// <summary>
    /// 获取数据集
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="paras">查询参数</param>
    /// <returns>返回数据集Table</returns>
    public DataTable GetTableByQuery(string sql,SqlParameter[] paras)
    {
        DataTable dt = null;
        DataSet ds = new DataSet();
        if (cmd.Connection.State == ConnectionState.Closed)
            cmd.Connection.Open();
        cmd.CommandText = sql;
        foreach (SqlParameter para in paras)
        {
            cmd.Parameters.Add(para);
        }

        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            sda.SelectCommand = cmd;
            sda.Fill(ds);
        }
        dt = ds.Tables[0];
        if (cmd.Connection.State == ConnectionState.Open)
            cmd.Connection.Close();
        cmd.Dispose();
        cmd.Parameters.Clear();
        return dt;
    }
}

/// <summary>
/// 此类仅是测试时使用
/// </summary>
public class DataAccessTest
{
    string conStr = "server=.;database=PersonalWord;uid=sa;pwd=";
    SqlCommand cmd = null;
    public DataAccessTest()
    {
        cmd.Connection = new SqlConnection(conStr);
    }
    public int ExecuteNonQuery(string sql, SqlParameter[] parms)
    {
        int affectRow = 0;
        if (cmd.Connection.State == ConnectionState.Closed)
            cmd.Connection.Open();

        cmd.CommandText = sql;
        cmd.CommandType = CommandType.Text;
        foreach (SqlParameter parm in parms)
        {
            cmd.Parameters.Add(parm);
        }
        try
        {
            affectRow = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            if (cmd.Connection.State == ConnectionState.Open)
                cmd.Connection.Close();
            cmd.Parameters.Clear();
            cmd.Dispose();
        }
        return affectRow;
    }

    public DataSet GetDataQuery(string sql, SqlParameter[] parms)
    {
        DataSet ds = new DataSet();
        if (cmd.Connection.State == ConnectionState.Closed)
            cmd.Connection.Open();

        cmd.CommandText = sql;
        foreach (SqlParameter parm in parms)
        {
            cmd.Parameters.Add(parm);
        }
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            sda.SelectCommand = cmd;
            sda.Fill(ds);
        }

        if (cmd.Connection.State == ConnectionState.Open)
            cmd.Connection.Close();
        cmd.Parameters.Clear();
        cmd.Dispose();

        return ds;
    }

    public DataTable GetTestTable(int id)
    {
        string sql = "select * from tableName where id = @id";
        SqlParameter[] parms = new SqlParameter[1];
        parms[0] = new SqlParameter("@id", SqlDbType.Int);
        parms[0].Value = id;
        DataTable dt = GetDataQuery(sql, parms).Tables[0];
        return dt;
    }
}
dong.net | 园豆:205 (菜鸟二级) | 2012-08-11 23:05
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册