DBQuery封装类快速操作数据库交互程序-Powered by 25yicms

在.net(C#)当中程序结构比较多，有简单单类结构，简单三层，工厂模式三层应该等等。在项目当中我们应该学会灵活运用，像工厂模式三层操作起来会比较费时间。

现在我介绍一个DBQuery类，对数据库交互的操作比较简单。

如我们读取一个表的数据保存到DataTable内，我们只要直接写：
DataTable dt = DBQuery.OpenTable("select top 1 AwardTime,Id from GuessNum2 order by AwardTime desc");
更新一个表的数据，用DBQuery封装类操作就是：
DBQuery.ExecuteNonQuery("update EnlistGuessNum3 set Usered=1 where GuessNum3Id=" + dt.Rows[i][0].ToString().Trim());
如果我们要用DBQuery封装类来操作分页:
DataTable dt;
dt = DBQuery.OpenTable("select top " + pagesize + " Id,AwardTime,Num1,Num2,Num3,NumSum,(select count(Id) from GuessNum2 where " + ReMark + ") as countpage from GuessNum2 where " + ReMark + " and Id not in(select top " + pagesize * pageindex + " Id from GuessNum2 where " + ReMark + " order by Id desc) order by Id desc");
这是不是很方便呢！

DBQuery封装类全部代码如下，把以下程序拷贝，然后在C#(asp.net)中建一个DBQuery.cs文件既可调用。
/*开始*/
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;
/// <summary>
/// DBQuery 的摘要说明
/// </summary>
public sealed class DBQuery
{
    private static string strConnection = System.Configuration.ConfigurationSettings.AppSettings["ATigConn"];
    private static SqlConnection conn2 = null;
    static SqlConnection conn = sqlcon.getConn();
    static DataTable dt;
    public DBQuery()
    {
        //dataclass dc = new dataclass();
        // conn = dc.getConn();
    }
    public static DataTable OpenTable(string sql)
    {
        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
        dt = new DataTable();
        try
        {
            da.Fill(dt);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            da.Dispose();
        }

        return dt;
    }
    public static DataTable OpenTable(string TableName, string[] column, string[] condition, string IndexColumn, bool IsAsc, int PageSize, int Page)
    {

        string tempsql = "";
        string tempsql2 = "";

        for (int i = 0; i < column.Length; i++)
        {
            tempsql += column[i];
            if (i < column.Length - 1) tempsql += ",";
        }

        for (int i = 0; i < condition.Length; i++)
        {
            tempsql2 += condition[i];
            if (i < condition.Length - 1) tempsql2 += " and ";
        }

        string sql = "";
        if (Page == 0)
            sql = "select top " + PageSize.ToString() + " " + tempsql + " from " + TableName + " where " + tempsql2 + " order by " + IndexColumn + " " + (IsAsc ? "Asc" : "Desc");
        else
            sql = "select top " + PageSize.ToString() + " " + tempsql + " from " + TableName + " where " + tempsql2 + " and " + IndexColumn + (IsAsc ? ">" : "<") + "(select " + (IsAsc ? "max" : "min") + "(" + IndexColumn + ") from " + TableName + " where " + IndexColumn + " in (select top " + (Page * PageSize) + " " + IndexColumn + " from " + TableName + " where " + tempsql2 + " order by " + IndexColumn + " " + (IsAsc ? "Asc" : "Desc") + ")) order by " + IndexColumn + " " + (IsAsc ? "Asc" : "Desc");

SqlDataAdapter da = new SqlDataAdapter(sql, conn);

        dt = new DataTable();
        try
        {
            da.Fill(dt);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            da.Dispose();
        }

        return dt;
    }
    public static object ExecuteScalar(string sql)
    {
        object o;
        SqlCommand scd = new SqlCommand(sql, conn);
        conn.Open();
        try
        {
            o = scd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            scd.Dispose();
            conn.Close();
        }

        return o;
    }
    public static void ExecuteNonQuery(string sql)
    {
        open();

        if (conn2 == null)
        {
        }
        int iResult = 0;

        using (SqlCommand sqlcmd = new SqlCommand(sql, conn2))
        {
            try
            {
                sqlcmd.CommandTimeout = 300;
                sqlcmd.ExecuteNonQuery();
                iResult = 1;
            }
            catch (Exception ex)
            {
                //WriteLog("[ExecuteSql]" + ex.Message.ToString() + "\n" + sql + "\n", true); //这是调用写日志文件的一个过程
                iResult = 0;
            }
            finally
            {
                if (conn2.State == ConnectionState.Open)
                {
                    conn2.Close();
                }
            }
        }
    }
    public static void open()
    {
        if (conn2 == null)
        {
            conn2 = new SqlConnection(strConnection);
        }
        if (conn2.State == ConnectionState.Closed)
        {
            conn2.Open();
        }
    }

    public static void close()
    {
        if (conn2 != null || conn2.State == ConnectionState.Open)
        {
            conn2.Close();
        }
    }