.NET的DAO类

写了一个.NET的简易的DAO类

1.可以支持带或不带参数的SQL

2.可以返回XML的结果集或dataset的结果集

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

public class GeneralDAO
{
    static string conn = ConfigurationManager.ConnectionStrings
            ["ConnectionString"].ConnectionString;

    private SqlConnection Conn = null;



    public GeneralDAO()
    {

    }
    private void openConnection()
    {
        if (Conn == null) {
            Conn = new SqlConnection(conn);
            Conn.Open();
        }
        else if (Conn.State == System.Data.ConnectionState.Closed)
        {
            Conn.Open();
        }
        else if (Conn.State == System.Data.ConnectionState.Broken)
        {
            Conn.Close();
            Conn.Open();
        }
    }
    private void closeConnection()
    {
        if (Conn != null)
        {
            Conn.Close();
        }
      
    }

    public string doSearch(string sqls)
    {
        try
        {
            DataSet dataSet = new DataSet();
            openConnection();
            String result = "";
            SqlDataAdapter sqlDA = new SqlDataAdapter(sqls, Conn);
            sqlDA.Fill(dataSet, "Result");
            result = dataSet.GetXml();
            return result;

        }
        finally
        {
            closeConnection();
        }

    }

    public string doSearch(string sqls, IList<DbParameter> sqlparameters)
    {
        try
        {
            openConnection();
            String result="";
            using (SqlDataAdapter sqlDA = new SqlDataAdapter(sqls, Conn))
            {
                DataSet dataSet = new DataSet();
                if (sqlparameters != null)
                {

                    foreach (SqlParameter parameter in sqlparameters)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        sqlDA.SelectCommand.Parameters.Add(parameter);
                    }
                }

                sqlDA.Fill(dataSet, "Result");
                sqlDA.SelectCommand.Parameters.Clear();
                result = dataSet.GetXml();
            }
            return result;
              
        }
        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            closeConnection();
        }

    }


    public DataSet doSearchByDataset(string sqls)
    {
        try
        {
            DataSet dataSet = new DataSet();
            openConnection();
            SqlDataAdapter sqlDA = new SqlDataAdapter(sqls, Conn);
            sqlDA.Fill(dataSet, "Result");
            return dataSet;

        }
        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            closeConnection();
        }

    }  


    public DataSet doSearchByDataset(string sqls, IList<DbParameter> sqlparameters)
    {
       
        try
        {
            openConnection();
            DataSet dataSet = new DataSet();
            using (SqlDataAdapter sqlDA = new SqlDataAdapter(sqls, Conn))
            {
                if (sqlparameters != null)
                {

                    foreach (SqlParameter parameter in sqlparameters)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        sqlDA.SelectCommand.Parameters.Add(parameter);
                    }
                }

                sqlDA.Fill(dataSet, "Result");
                sqlDA.SelectCommand.Parameters.Clear();
                return dataSet;
                
            }
           
        }
       
        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            closeConnection();
        }

    }

    public bool doExec(string sqls)
    {
        bool result = false;
        openConnection();
        SqlTransaction trans = Conn.BeginTransaction();
        try
        {
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.CommandText = sqls;
            sqlcom.Connection = Conn;
            sqlcom.Transaction = trans;
            sqlcom.ExecuteNonQuery();
            trans.Commit();
            result = true;
        }
        catch (SqlException ex)
        {
            result = false;
            throw new Exception(ex.Message);
        }
        finally
        {
            closeConnection();
        }
        return result;
    }

    public bool doExec(string sqls, IList<DbParameter> sqlparameters)
    {
        bool result = false;
        openConnection();
        SqlTransaction trans = Conn.BeginTransaction();
        try
        {
            using (SqlCommand sqlcom = new SqlCommand())
            {

                if (sqlparameters != null)
                {
                    foreach (SqlParameter parameter in sqlparameters)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        sqlcom.Parameters.Add(parameter);
                    }
                }

                sqlcom.CommandText = sqls;
                sqlcom.Connection = Conn;
                sqlcom.Transaction = trans;
                sqlcom.ExecuteNonQuery();
                trans.Commit();
                result = true;

            }
        }
        catch (SqlException ex)
        {
            trans.Rollback();
            result = false;
            throw new Exception(ex.Message);
        }

        finally
        {
            closeConnection();
        }
        return result;
    }
}

使用：

string sqls = "insert into users(user_name) values(@username)";
        List<DbParameter> paramlist = new List<DbParameter>();
        SqlParameter p1 = new SqlParameter("@username", TextBox2.Text.Trim());
        paramlist.Add(p1);
        GeneralDAO dao = new GeneralDAO();
        dao.doExec(sqls, paramlist);

或：

        GeneralDAO dao = new GeneralDAO();
        dao.doSearch("select * from users");