基于SlowX框架的通用数据库操作方法 - DataBaseSlowXDALFunctions

2017-02-21 22:47:14  访问(1599) 赞(0) 踩(0)

  • using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Text;
    using SlowX.DAL.Helpers;

    namespace SlowX.DAL.Functions
    {
    /// <summary>
    /// 快速的数据库操作
    /// </summary>
    [Serializable]
    public class DataBaseSlowXDALFunctions
    {
    /// <summary>
    /// 清空表
    /// </summary>
    /// <param name="_dbConnectionString"></param>
    /// <param name="_emDataBaseHelper"></param>
    /// <param name="_dataBaseVersion"></param>
    /// <param name="_tableName"></param>
    /// <returns></returns>
    public static int Truncate
    (
    string _dbConnectionString,
    SlowX.DAL.Enums.DataBaseHelper.EmDataBaseHelper _emDataBaseHelper,
    int _dataBaseVersion,
    string _tableName
    )
    {
    int theResult = 0;

    SlowX.DAL.Helpers.DBHelper dbHelper
    =
    SlowX.DAL.Helpers.DBHelper.CreateDBHelper(_dbConnectionString, _emDataBaseHelper, _dataBaseVersion);

    try
    {
    dbHelper.OpenDBHelper();

    string SQL = "truncate table " + _tableName;

    theResult = dbHelper.ExecuteNonQuery(SQL);


    dbHelper.EndDBHelper();

    }
    catch (Exception err)
    {
    dbHelper.TranDBHelper();

    throw err;
    }
    finally
    {
    dbHelper.FinallyDBHelper();
    }

    return theResult;
    }


    /// <summary>
    /// 清空表
    /// </summary>
    /// <param name="_tableName"></param>
    /// <param name="dbHelper"></param>
    /// <returns></returns>
    public static int Truncate(string _tableName, DBHelper dbHelper)
    {
    int theResult = 0;

    bool bIsCreate = true;

    if (dbHelper == null)
    {
    dbHelper = SlowX.DAL.Helpers.DBHelper.CreateDBHelper();
    }
    else
    {
    // 没有打开链接 //
    bIsCreate = dbHelper.IsNotOpen();
    }

    try
    {
    if (bIsCreate)
    dbHelper.OpenDBHelper();

    string SQL = "truncate table " + _tableName;

    theResult = dbHelper.ExecuteNonQuery(SQL);

    if (bIsCreate)
    dbHelper.EndDBHelper();

    }
    catch (Exception err)
    {
    if (bIsCreate)
    dbHelper.TranDBHelper();

    throw err;
    }
    finally
    {
    if (bIsCreate)
    dbHelper.FinallyDBHelper();
    }

    return theResult;
    }

    /// <summary>
    /// 清空表
    /// </summary>
    /// <param name="_tableName"></param>
    /// <param name="dbHelper"></param>
    /// <returns></returns>
    public static int DeleteAll(string _tableName, DBHelper dbHelper)
    {
    int theResult = 0;

    bool bIsCreate = true;

    if (dbHelper == null)
    {
    dbHelper = SlowX.DAL.Helpers.DBHelper.CreateDBHelper();
    }
    else
    {
    // 没有打开链接 //
    bIsCreate = dbHelper.IsNotOpen();
    }

    try
    {
    if (bIsCreate)
    dbHelper.OpenDBHelper();

    string SQL = "delete from " + _tableName;

    theResult = dbHelper.ExecuteNonQuery(SQL);

    if (bIsCreate)
    dbHelper.EndDBHelper();

    }
    catch (Exception err)
    {
    if (bIsCreate)
    dbHelper.TranDBHelper();

    throw err;
    }
    finally
    {
    if (bIsCreate)
    dbHelper.FinallyDBHelper();
    }

    return theResult;
    }

    /// <summary>
    /// 记录数
    /// </summary>
    /// <param name="_tableName"></param>
    /// <param name="dbHelper"></param>
    /// <returns></returns>
    public static int Count(string _tableName, DBHelper dbHelper)
    {
    int theResult = 0;

    bool bIsCreate = true;

    if (dbHelper == null)
    {
    dbHelper = SlowX.DAL.Helpers.DBHelper.CreateDBHelper();
    }
    else
    {
    // 没有打开链接 //
    bIsCreate = dbHelper.IsNotOpen();
    }

    try
    {
    if (bIsCreate)
    dbHelper.OpenDBHelper();

    string SQL = "select count(1) from " + _tableName;

    object oValue = dbHelper.ExecuteScalar(SQL);

    if (!(oValue == null || oValue == DBNull.Value || oValue.ToString().Length == 0))
    theResult = Convert.ToInt32(oValue);

    if (bIsCreate)
    dbHelper.EndDBHelper();

    }
    catch (Exception err)
    {
    if (bIsCreate)
    dbHelper.TranDBHelper();

    throw err;
    }
    finally
    {
    if (bIsCreate)
    dbHelper.FinallyDBHelper();
    }

    return theResult;
    }

    /// <summary>
    /// 写入字典数据
    /// </summary>
    /// <param name="_tableName"></param>
    /// <param name="_ID"></param>
    /// <param name="_TheName"></param>
    /// <param name="_dbConnectionString"></param>
    /// <param name="_emDataBaseHelper"></param>
    /// <param name="_dataBaseVersion"></param>
    /// <returns></returns>
    public static int InsertBasicData
    (
    string _tableName,
    long _ID,
    string _TheName,
    string _dbConnectionString,
    SlowX.DAL.Enums.DataBaseHelper.EmDataBaseHelper _emDataBaseHelper,
    int _dataBaseVersion
    )
    {
    int theResult = 0;

    SlowX.DAL.Helpers.DBHelper dbHelper = SlowX.DAL.Helpers.DBHelper.CreateDBHelper(_dbConnectionString, _emDataBaseHelper, _dataBaseVersion);

    try
    {

    dbHelper.OpenDBHelper();

    StringBuilder sb = new StringBuilder();

    sb.Append("insert into ");
    sb.Append(_tableName);
    sb.Append(" (ID,TheName) values(");
    sb.Append(_ID.ToString());
    sb.Append(",'");
    sb.Append(_TheName.Replace("'", "''"));
    sb.Append("')");

    theResult = dbHelper.ExecuteNonQuery(sb.ToString());


    dbHelper.EndDBHelper();

    }
    catch (Exception err)
    {

    dbHelper.TranDBHelper();

    throw err;
    }
    finally
    {

    dbHelper.FinallyDBHelper();
    }

    return theResult;
    }


    /// <summary>
    /// 写入字典数据
    /// </summary>
    /// <param name="_tableName"></param>
    /// <param name="_ID"></param>
    /// <param name="_TheName"></param>
    /// <param name="dbHelper"></param>
    /// <returns></returns>
    public static int InsertBasicData(string _tableName, long _ID, string _TheName, DBHelper dbHelper)
    {
    int theResult = 0;

    bool bIsCreate = true;

    if (dbHelper == null)
    {
    dbHelper = SlowX.DAL.Helpers.DBHelper.CreateDBHelper();
    }
    else
    {
    // 没有打开链接 //
    bIsCreate = dbHelper.IsNotOpen();
    }

    try
    {
    if (bIsCreate)
    dbHelper.OpenDBHelper();

    StringBuilder sb = new StringBuilder();

    sb.Append("insert into ");
    sb.Append(_tableName);
    sb.Append(" (ID,TheName) values(");
    sb.Append(_ID.ToString());
    sb.Append(",'");
    sb.Append(_TheName.Replace("'", "''"));
    sb.Append("')");

    theResult = dbHelper.ExecuteNonQuery(sb.ToString());

    if (bIsCreate)
    dbHelper.EndDBHelper();

    }
    catch (Exception err)
    {
    if (bIsCreate)
    dbHelper.TranDBHelper();

    throw err;
    }
    finally
    {
    if (bIsCreate)
    dbHelper.FinallyDBHelper();
    }

    return theResult;

    }

    /// <summary>
    /// 写入字典数据
    /// </summary>
    /// <param name="_tableName"></param>
    /// <param name="_ID"></param>
    /// <param name="_TheName"></param>
    /// <param name="dbHelper"></param>
    /// <returns></returns>
    public static int InsertStringBasicData(string _tableName, string _ID, string _TheName, DBHelper dbHelper)
    {
    int theResult = 0;

    bool bIsCreate = true;

    if (dbHelper == null)
    {
    dbHelper = SlowX.DAL.Helpers.DBHelper.CreateDBHelper();
    }
    else
    {
    // 没有打开链接 //
    bIsCreate = dbHelper.IsNotOpen();
    }

    try
    {
    if (bIsCreate)
    dbHelper.OpenDBHelper();

    StringBuilder sb = new StringBuilder();

    sb.Append("insert into ");
    sb.Append(_tableName);
    sb.Append(" (ID,TheName) values('");
    sb.Append(_ID.Replace("'","''"));
    sb.Append("','");
    sb.Append(_TheName.Replace("'", "''"));
    sb.Append("')");

    theResult = dbHelper.ExecuteNonQuery(sb.ToString());

    if (bIsCreate)
    dbHelper.EndDBHelper();

    }
    catch (Exception err)
    {
    if (bIsCreate)
    dbHelper.TranDBHelper();

    throw err;
    }
    finally
    {
    if (bIsCreate)
    dbHelper.FinallyDBHelper();
    }

    return theResult;

    }

    /// <summary>
    /// 更新字段名
    /// </summary>
    /// <param name="_tableName"></param>
    /// <param name="_columnName"></param>
    /// <param name="_columnValue"></param>
    /// <param name="_ID"></param>
    /// <param name="dbHelper"></param>
    /// <returns></returns>
    public static int UpdateColumnName(string _tableName, string _columnName, string _columnValue, long _ID, DBHelper dbHelper)
    {
    int theResult = 0;

    bool bIsCreate = true;

    if (dbHelper == null)
    {
    dbHelper = SlowX.DAL.Helpers.DBHelper.CreateDBHelper();
    }
    else
    {
    // 没有打开链接 //
    bIsCreate = dbHelper.IsNotOpen();
    }

    try
    {
    if (bIsCreate)
    dbHelper.OpenDBHelper();

    StringBuilder sb = new StringBuilder();

    sb.Append("update ");
    sb.Append(_tableName);
    sb.Append(" set ");
    sb.Append(_columnName);
    sb.Append(" = '");
    sb.Append(_columnValue.Replace("'", "''"));
    sb.Append("' where ID = ");
    sb.Append(_ID.ToString());

    theResult = dbHelper.ExecuteNonQuery(sb.ToString());

    if (bIsCreate)
    dbHelper.EndDBHelper();

    }
    catch (Exception err)
    {
    if (bIsCreate)
    dbHelper.TranDBHelper();

    throw err;
    }
    finally
    {
    if (bIsCreate)
    dbHelper.FinallyDBHelper();
    }

    return theResult;
    }

    /// <summary>
    /// 分页显示
    /// </summary>
    /// <param name="_dbConnectionString"></param>
    /// <param name="_emDataBaseHelper"></param>
    /// <param name="_dataBaseVersion"></param>
    /// <param name="pageIndex"></param>
    /// <param name="pageSize"></param>
    /// <param name="pkName"></param>
    /// <param name="_tableName"></param>
    /// <returns></returns>
    public static DataSet PageList
    (
    string _dbConnectionString,
    SlowX.DAL.Enums.DataBaseHelper.EmDataBaseHelper _emDataBaseHelper,
    int _dataBaseVersion,
    int pageIndex,
    int pageSize,
    string pkName,
    string _tableName
    )
    {
    if(pageSize == 0)
    pageSize = int.MaxValue;

    DataSet theResult = null;

    SlowX.DAL.Helpers.DBHelper dbHelper
    =
    SlowX.DAL.Helpers.DBHelper.CreateDBHelper(_dbConnectionString, _emDataBaseHelper, _dataBaseVersion);

    try
    {
    dbHelper.OpenDBHelper();

    string SQL = SQLSlowXDALFunctions.PageList
    (
    "*",
    _tableName,
    "",
    pkName + " asc ",
    pkName,
    pageIndex,
    pageSize,
    _emDataBaseHelper,
    _dataBaseVersion
    );

    theResult = dbHelper.ExecuteDataSet(SQL);


    dbHelper.EndDBHelper();

    }
    catch (Exception err)
    {
    dbHelper.TranDBHelper();

    throw err;
    }
    finally
    {
    dbHelper.FinallyDBHelper();
    }

    return theResult;
    }

    /// <summary>
    /// 分页显示
    /// </summary>
    /// <param name="pageIndex"></param>
    /// <param name="pageSize"></param>
    /// <param name="pkName"></param>
    /// <param name="_tableName"></param>
    /// <param name="dbHelper"></param>
    /// <returns></returns>
    public static DataSet PageList
    (
    int pageIndex,
    int pageSize,
    string pkName,
    string _tableName,
    DBHelper dbHelper
    )
    {
    if (pageSize == 0)
    pageSize = int.MaxValue;

    DataSet theResult = null;


    bool bIsCreate = true;

    if (dbHelper == null)
    {
    dbHelper = SlowX.DAL.Helpers.DBHelper.CreateDBHelper();
    }
    else
    {
    // 没有打开链接 //
    bIsCreate = dbHelper.IsNotOpen();
    }

    try
    {
    if (bIsCreate)
    dbHelper.OpenDBHelper();

    string SQL = SQLSlowXDALFunctions.PageList
    (
    "*",
    _tableName,
    "",
    pkName + " asc ",
    pkName,
    pageIndex,
    pageSize,
    dbHelper.GetEmDataBaseHelper(),
    dbHelper.GetDataBaseVersion()
    );

    theResult = dbHelper.ExecuteDataSet(SQL);

    if (bIsCreate)
    dbHelper.EndDBHelper();

    }
    catch (Exception err)
    {
    if (bIsCreate)
    dbHelper.TranDBHelper();

    throw err;
    }
    finally
    {
    if (bIsCreate)
    dbHelper.FinallyDBHelper();
    }

    return theResult;
    }
    }
    }

  • using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text;

using SlowX.DAL.Helpers;

namespace SlowX.DAL.Functions
{
    /// <summary>
    /// SQL语句相关的代码
    /// </summary>
    [Serializable]
    public class SQLSlowXDALFunctions
    {
        /// <summary>
        /// 清空表
        /// </summary>
        /// <param name="_tableName"></param>
        /// <returns></returns>
        public static string Truncate(string _tableName)
        {
            return "truncate table " + _tableName;
        }

        /// <summary>
        /// "select count(1) from " + _tableName
        /// </summary>
        /// <param name="_tableName"></param>
        /// <returns></returns>
        public static string Count(string _tableName)
        {
            return "select count(1) from " + _tableName;
        }

        /// <summary>
        /// "select * from " + _tableName
        /// </summary>
        /// <param name="_tableName"></param>
        /// <returns></returns>
        public static string Select(string _tableName)
        {
            return "select * from " + _tableName;
        }

        /// <summary>
        /// select * from " + tableName + " where id = '" + Id.Replace("'", "''") + "'"
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="Id"></param>
        /// <returns></returns>
        public static string GetRow(string tableName, string Id)
        {
            return "select * from " + tableName + " where id = '" + Id.Replace("'", "''") + "'";
        }

        /// <summary>
        /// "select * from " + tableName + " where id = " + Id.ToString();
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="Id"></param>
        /// <returns></returns>
        public static string GetRow(string tableName, long Id)
        {
            return "select * from " + tableName + " where id = " + Id.ToString();
        }

        /// <summary>
        /// "select " + columnName + " from " + tableName
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public static string SelectColumn(string tableName, string columnName)
        {
            return "select " + columnName + " from " + tableName;
        }

        /// <summary>
        /// Top Select 相关的语句
        /// </summary>
        /// <param name="select"></param>
        /// <param name="from"></param>
        /// <param name="where"></param>
        /// <param name="orderby"></param>
        /// <param name="topNum"></param>
        /// <param name="_emDataBaseHelper"></param>
        /// <returns></returns>
        public static string TopSelect
            (
                string select, 
                string from,
                string where, 
                string orderby,
                int topNum,
                SlowX.DAL.Enums.DataBaseHelper.EmDataBaseHelper _emDataBaseHelper)
        {
            StringBuilder theResult = new StringBuilder();

            switch (_emDataBaseHelper)
            {
                case Enums.DataBaseHelper.EmDataBaseHelper.Oracle:

                    // SQL = "select slx.* from (" + SQL + ")slx where rownum <= " + top.ToString();
                    theResult.Append("select slx.* from (");
                    theResult.Append("select ");
                    theResult.Append(select);
                    theResult.Append(" from ");
                    theResult.Append(from);

                    if (where != null && where.Length > 0)
                    {
                        theResult.Append(" where ");
                        theResult.Append(where);
                    }

                    if (orderby != null && orderby.Length > 0)
                    {
                        theResult.Append(" order by ");
                        theResult.Append(orderby);
                    }

                    theResult.Append(")slx where rownum <= ");
                    theResult.Append(topNum.ToString());
                    break;
                default:
                    theResult.Append("select top ");
                    theResult.Append(topNum.ToString());
                    theResult.Append(" ");
                    theResult.Append(select);
                    theResult.Append(" from ");
                    theResult.Append(from);

                    if (where != null && where.Length > 0)
                    {
                        theResult.Append(" where ");
                        theResult.Append(where);
                    }

                    if (orderby != null && orderby.Length > 0)
                    {
                        theResult.Append(" order by ");
                        theResult.Append(orderby);
                    }
                    break;
            }

            return theResult.ToString();
        }

        /// <summary>
        /// Oracle分页用的select语句
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="iPageIndex"></param>
        /// <param name="iPageSize"></param>
        /// <returns></returns>
        public static string OraclePageListSelect
            (
                string SQL,
                int iPageIndex,
                int iPageSize
            )
        {
            if(iPageIndex == 0)
                return "select slx.* from (" + SQL + ")slx where rownum <= " + iPageSize.ToString();

            int startIndex = iPageIndex * iPageSize;

            return "select * from (select a.*, rownum as p__SqlRowNumberValue from ("
                + SQL + ")a where rownum <= " + (startIndex + iPageSize).ToString() + ") where p__SqlRowNumberValue > " + startIndex;
        }

        /// <summary>
        /// Oracle分页用的select语句
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="startIndex"></param>
        /// <param name="iPageSize"></param>
        /// <returns></returns>
        public static string OracleStartIndexListSelect
            (
                string SQL,
                int startIndex,
                int iPageSize
            )
        {
            if (startIndex == 0)
                return "select slx.* from (" + SQL + ")slx where rownum <= " + iPageSize.ToString();

            return "select * from (select a.*, rownum as p__SqlRowNumberValue from ("
                + SQL + ")a where rownum <= " + (startIndex + iPageSize).ToString() + ") where p__SqlRowNumberValue > " + startIndex;
        }


        /// <summary>
        /// SQLServer2005以上的分页算法
        /// </summary>
        /// <param name="sqlSelectResult"></param>
        /// <param name="sqlFromResult"></param>
        /// <param name="sqlWhereResult"></param>
        /// <param name="sqlOrderResult"></param>
        /// <param name="pageIndex"></param>
        /// <param name="iPageSize"></param>
        /// <returns></returns>
        public static string SqlServerRowNumberPageListSelect
            (
                string sqlSelectResult,
                string sqlFromResult,
                string sqlWhereResult,
                string sqlOrderResult,
                int pageIndex,
                int iPageSize
            )
        {
            int startIndex = pageIndex * iPageSize;

            return SqlServerRowNumberStartIndexListSelect
                (
                    sqlSelectResult,
                    sqlFromResult,
                    sqlWhereResult,
                    sqlOrderResult,
                    startIndex,
                    iPageSize
                );
        }

        /// <summary>
        /// SQLServer2005以上的分页算法
        /// </summary>
        /// <param name="sqlSelectResult"></param>
        /// <param name="sqlFromResult"></param>
        /// <param name="sqlWhereResult"></param>
        /// <param name="sqlOrderResult"></param>
        /// <param name="startIndex"></param>
        /// <param name="iPageSize"></param>
        /// <returns></returns>
        public static string SqlServerRowNumberStartIndexListSelect
            (
                string sqlSelectResult,
                string sqlFromResult,
                string sqlWhereResult,
                string sqlOrderResult,
                int startIndex,
                int iPageSize
            )
        {

            // select * from (select ID,TheCode,CurID,CreateTime,UpdateTime,ROW_NUMBER() over (order by ID desc,TheCode asc) as p__SqlRowNumberValue from UTB_CODEX_MEMORY where ID >= @p1)slx where slx.p__SqlRowNumberValue > 10 and slx.p__SqlRowNumberValue <= 20

            int toPageNum = iPageSize;

            if (toPageNum != int.MaxValue)
                toPageNum += startIndex;

            StringBuilder theResult = new StringBuilder();

            theResult.Append("select * from (select ");
            theResult.Append(sqlSelectResult + ",");
            theResult.Append("ROW_NUMBER() over (");
            theResult.Append("order by ");
            theResult.Append(sqlOrderResult);
            theResult.Append(") as p__SqlRowNumberValue from ");
            theResult.Append(sqlFromResult);

            if (sqlWhereResult.Length != 0)
            {
                theResult.Append(" where ");
                theResult.Append(sqlWhereResult);
            }

            theResult.Append(")slx where ");

            theResult.Append("slx.p__SqlRowNumberValue > ");
            theResult.Append(startIndex.ToString());
            theResult.Append(" and slx.p__SqlRowNumberValue <= ");
            theResult.Append(toPageNum.ToString());

            return theResult.ToString();
        }


        /// <summary>
        /// SQLServer2000版本（或Access版本的分页算法）
        /// </summary>
        /// <param name="sqlSelectResult"></param>
        /// <param name="sqlFromResult"></param>
        /// <param name="sqlWhereResult"></param>
        /// <param name="sqlOrderResult"></param>
        /// <param name="pkName"></param>
        /// <param name="pageIndex"></param>
        /// <param name="iPageSize"></param>
        /// <returns></returns>
        public static string SqlNotInModelPageListSelect
            (
                string sqlSelectResult,
                string sqlFromResult,
                string sqlWhereResult,
                string sqlOrderResult,
                string pkName,
                int pageIndex,
                int iPageSize
            )
        {
            int startIndex = pageIndex * iPageSize;

            return SqlNotInModelStartIndexListSelect(sqlSelectResult, sqlFromResult, sqlWhereResult, sqlOrderResult, pkName, startIndex, iPageSize);
        }

        /// <summary>
        /// SQLServer2000版本（或Access版本的分页算法）
        /// </summary>
        /// <param name="sqlSelectResult"></param>
        /// <param name="sqlFromResult"></param>
        /// <param name="sqlWhereResult"></param>
        /// <param name="sqlOrderResult"></param>
        /// <param name="pkName"></param>
        /// <param name="startIndex"></param>
        /// <param name="iPageSize"></param>
        /// <returns></returns>
        public static string SqlNotInModelStartIndexListSelect
            (
                string sqlSelectResult,
                string sqlFromResult,
                string sqlWhereResult,
                string sqlOrderResult,
                string pkName,
                int startIndex,
                int iPageSize
            )
        {
            StringBuilder theResult = new StringBuilder();

            if (startIndex == 0)
            {
                theResult.Append("select ");

                if (iPageSize != int.MaxValue)
                {
                    theResult.Append("top " + iPageSize.ToString() + " ");                     
                }

                theResult.Append(sqlSelectResult);
                theResult.Append(" from ");
                theResult.Append(sqlFromResult);

                if (sqlWhereResult != null && sqlWhereResult.Length > 0)
                {
                    theResult.Append(" where ");
                    theResult.Append(sqlWhereResult);
                }

                if (sqlOrderResult != null && sqlOrderResult.Length > 0)
                {
                    theResult.Append(" order by ");
                    theResult.Append(sqlOrderResult);
                }

                return theResult.ToString();
            }

            // select top 10 ID,TheCode,CurID,CreateTime,UpdateTime from UTB_CODEX_MEMORY where ID not in (select top 10 ID from UTB_CODEX_MEMORY order by ID desc,TheCode asc) order by ID desc,TheCode asc

           

            theResult.Append("select ");

            if (iPageSize != int.MaxValue)
            {
                theResult.Append("top " + iPageSize.ToString() + " ");
            }

            theResult.Append(sqlSelectResult);
            theResult.Append(" from ");
            theResult.Append(sqlFromResult);

            if (sqlWhereResult.Length != 0)
            {
                theResult.Append(" where ");
                theResult.Append("(");
                theResult.Append(sqlWhereResult);
                theResult.Append(") and ");
            }
            else
            {
                theResult.Append(" where ");
            }

            theResult.Append(pkName);
            theResult.Append(" not in (");
            theResult.Append("select top ");
            theResult.Append(startIndex.ToString());
            theResult.Append(" ");
            theResult.Append(pkName);
            theResult.Append(" from ");
            theResult.Append(sqlFromResult);

            if (sqlWhereResult.Length != 0)
            {
                theResult.Append(" where ");
                theResult.Append(sqlWhereResult);
            }

            if (sqlOrderResult.Length != 0)
            {
                theResult.Append(" order by ");
                theResult.Append(sqlOrderResult);
            }

            theResult.Append(")");

            if (sqlOrderResult.Length != 0)
            {
                theResult.Append(" order by ");
                theResult.Append(sqlOrderResult);
            }

            return theResult.ToString();
        }


        /// <summary>
        /// 合并成Select的SQL语句
        /// </summary>
        /// <param name="sqlSelectResult"></param>
        /// <param name="sqlFromResult"></param>
        /// <param name="sqlWhereResult"></param>
        /// <param name="sqlOrderResult"></param>
        /// <returns></returns>
        public static string ToSelectSQL
            (
                string sqlSelectResult,
                string sqlFromResult,
                string sqlWhereResult,
                string sqlOrderResult
            )
        {
            StringBuilder theResult = new StringBuilder();

            theResult.Append("select ");
            theResult.Append(sqlSelectResult);
            theResult.Append(" from ");
            theResult.Append(sqlFromResult);

            if (sqlWhereResult != null && sqlWhereResult.Length > 0)
            {
                theResult.Append(" where ");
                theResult.Append(sqlWhereResult);
            }

            if (sqlOrderResult != null && sqlOrderResult.Length > 0)
            {
                theResult.Append(" order by ");
                theResult.Append(sqlOrderResult);
            }

            return theResult.ToString();
        }


        /// <summary>
        /// 合并成Count的SQL语句
        /// </summary>
        /// <param name="sqlFromResult"></param>
        /// <param name="sqlWhereResult"></param>
        /// <returns></returns>
        public static string ToCountSQL
            (
                string sqlFromResult,
                string sqlWhereResult 
            )
        {
            StringBuilder theResult = new StringBuilder();

            theResult.Append("select count(1) from ");
            theResult.Append(sqlFromResult);

            if (sqlWhereResult != null && sqlWhereResult.Length > 0)
            {
                theResult.Append(" where ");
                theResult.Append(sqlWhereResult);
            }

            return theResult.ToString();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlSelectResult"></param>
        /// <param name="sqlFromResult"></param>
        /// <param name="sqlWhereResult"></param>
        /// <param name="sqlOrderResult"></param>
        /// <param name="pkName"></param>
        /// <param name="pageIndex"></param>
        /// <param name="iPageSize"></param>
        /// <param name="iRowCount"></param>
        /// <param name="_emDataBaseHelper"></param>
        /// <param name="_dataBaseVersion"></param>
        /// <returns></returns>
        public static string PageList
            (
                string sqlSelectResult,
                string sqlFromResult,
                string sqlWhereResult,
                string sqlOrderResult,
                string pkName,
                int pageIndex,
                int iPageSize, 
                SlowX.DAL.Enums.DataBaseHelper.EmDataBaseHelper _emDataBaseHelper,
                int _dataBaseVersion
            )
        {
            if (pageIndex == 0 && iPageSize == int.MaxValue)
                return ToSelectSQL(sqlSelectResult, sqlFromResult, sqlWhereResult, sqlOrderResult);

            switch (_emDataBaseHelper)
            {
                case Enums.DataBaseHelper.EmDataBaseHelper.Oracle:
                    return OraclePageListSelect(ToSelectSQL(sqlSelectResult, sqlFromResult, sqlWhereResult, sqlOrderResult), pageIndex, iPageSize);
                case Enums.DataBaseHelper.EmDataBaseHelper.SQLServer:
                    if(_dataBaseVersion ==  (int)SlowX.DAL.Enums.SqlServerVersion.EmSqlServerVersion.SqlServer2000) 
                    {
                        return SqlNotInModelPageListSelect(sqlSelectResult, sqlFromResult, sqlWhereResult, sqlOrderResult, pkName, pageIndex, iPageSize);
                    }
                    else
                    {
                        return SqlServerRowNumberPageListSelect(sqlSelectResult,sqlFromResult,sqlWhereResult,sqlOrderResult,pageIndex,iPageSize);
                    } 
                default:
                    throw new Exception
                        (
                            "方法："
                            + MethodBase.GetCurrentMethod().ReflectedType.FullName
                            + " "
                            + MethodBase.GetCurrentMethod().ToString()
                            + " 发生异常："
                            + "枚举（"
                            + _emDataBaseHelper.GetType().FullName
                            + "."
                            + _emDataBaseHelper.ToString()
                            + "）未知，对应的代码尚未实现。"
                        );
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlSelectResult"></param>
        /// <param name="sqlFromResult"></param>
        /// <param name="sqlWhereResult"></param>
        /// <param name="sqlOrderResult"></param>
        /// <param name="pkName"></param>
        /// <param name="pageIndex"></param>
        /// <param name="iPageSize"></param>
        /// <param name="iRowCount"></param>
        /// <param name="_emDataBaseHelper"></param>
        /// <param name="_dataBaseVersion"></param>
        /// <returns></returns>
        public static string StartIndexList
            (
                string sqlSelectResult,
                string sqlFromResult,
                string sqlWhereResult,
                string sqlOrderResult,
                string pkName,
                int startIndex,
                int iPageSize, 
                SlowX.DAL.Enums.DataBaseHelper.EmDataBaseHelper _emDataBaseHelper,
                int _dataBaseVersion
            )
        {
            if (startIndex == 0 && iPageSize == int.MaxValue)
                return ToSelectSQL
                                (
                                    sqlSelectResult,
                                    sqlFromResult,
                                    sqlWhereResult,
                                    sqlOrderResult
                                );

            switch (_emDataBaseHelper)
            {
                case Enums.DataBaseHelper.EmDataBaseHelper.Oracle:                    
                    return OracleStartIndexListSelect
                        (
                            ToSelectSQL
                                (
                                    sqlSelectResult, 
                                    sqlFromResult, 
                                    sqlWhereResult, 
                                    sqlOrderResult
                                ), 
                                startIndex, 
                                iPageSize
                        );
                case Enums.DataBaseHelper.EmDataBaseHelper.SQLServer:
                    if (_dataBaseVersion == (int)SlowX.DAL.Enums.SqlServerVersion.EmSqlServerVersion.SqlServer2000)
                    {
                        return SqlNotInModelStartIndexListSelect
                            (
                                sqlSelectResult, 
                                sqlFromResult, 
                                sqlWhereResult,
                                sqlOrderResult, 
                                pkName, 
                                startIndex, 
                                iPageSize
                            );
                    }
                    else
                    {
                        return SqlServerRowNumberStartIndexListSelect
                            (
                                sqlSelectResult, 
                                sqlFromResult, 
                                sqlWhereResult, 
                                sqlOrderResult, 
                                startIndex, 
                                iPageSize
                            );
                    }
                default:
                    throw new Exception
                        (
                            "方法："
                            + MethodBase.GetCurrentMethod().ReflectedType.FullName
                            + " "
                            + MethodBase.GetCurrentMethod().ToString()
                            + " 发生异常："
                            + "枚举（"
                            + _emDataBaseHelper.GetType().FullName
                            + "."
                            + _emDataBaseHelper.ToString()
                            + "）未知，对应的代码尚未实现。"
                        );
            }
        }
    }
}

标签：基于SlowX框架的通用数据库操作方法 - DataBaseSlowXDALFunctions 

上一条：

下一条：


 

相关评论

评论加载中……
 

发表评论

类型：
内容：
  (Alt+Enter)
 
  ┈全部┈  
 
（显示默认分类）