对CodeSmith + netTiers 生成DAL的一点补充

作者：肖波

个人博客：http://blog.csdn.net/eaglet ; http://www.cnblogs.com/eaglet

2007/7 南京

版本
CodeSmith 4.0
netTiers 2.0.1

背景
        最近在项目中使用CodeSmith + netTiers 生成数据访问层DAL，感觉效果很好，减少了大量的简单重复劳动。
不过在使用过程中发现CodeSmith提供的方法不能完全满足项目需要，主要体现在两个方面：
1、 Data.DataRepository.TableProvider.GetPaged方法无法输入带参数的条件，调用前必须进行SQL 拼接，
这样可能导致SQL 注入攻击。
2、 DataRepository.Provider.ExecuteDataSet 无法分页查询

为解决以上问题，我做了如下代码对生成的DAL进行了补充。这些代码可以在DAL外部使用，也可以修改netTiers
模板，内置到DAL中。

    /**//// <summary>
    /// 带参数的条件查询子句异常
    /// </summary>
    public class ParaWhereStringException : Exception
    {
        public ParaWhereStringException(String message) 
            : base(message)
        {

        }
    }

    /**//// <summary>
    /// 带参数的条件查询子句
    /// </summary>
    public class ParaWhereString
    {
        enum T_STATE
        {
            Idle = 0,
            At   = 1,
            Str  = 2,
        }

        T_STATE m_State;
        int m_LastPos;
        int m_CurPos;
        String m_WhereString;
        List<String> m_Words = new List<string>();

        private void Clear()
        {
            m_State = T_STATE.Idle;
            m_LastPos = 0;
            m_CurPos = 0;
            m_WhereString = "";
            m_Words = new List<string>();
        }

        private void ChangeState(T_STATE curState)
        {
            m_State = curState;
            NewWord();
        }

        private void EndWord()
        {
            m_Words.Add(m_WhereString.Substring(m_LastPos, m_WhereString.Length - m_LastPos));
        }

        private void NewWord()
        {
            m_Words.Add(m_WhereString.Substring(m_LastPos, m_CurPos - m_LastPos));
            m_LastPos = m_CurPos;
        }

        private void StateMachine(char ch)
        {
            switch (m_State)
            {
                case T_STATE.Idle:
                    if (ch == '@')
                    {
                        ChangeState(T_STATE.At);
                    }
                    else if (ch == '\'')
                    {
                        ChangeState(T_STATE.Str);
                    }

                    break;
                case T_STATE.At:
                    if ((ch >= 'a' && ch <= 'z') || (ch >= 'A' && ch <= 'Z') || ch == '_')
                    {
                        break;
                    }

                    if (ch >= '0' && ch <= '9' && m_CurPos - m_LastPos > 1)
                    {
                        break;
                    }

                    if (ch == '\'')
                    {
                        ChangeState(T_STATE.Str);
                    }
                    else
                    {
                        ChangeState(T_STATE.Idle);
                    }

                    break;

                case T_STATE.Str:
                    if (ch == '\'')
                    {
                        m_CurPos++;

                        if (m_WhereString[m_CurPos] == '\'')
                        {
                            break;
                        }
                        else
                        {
                            ChangeState(T_STATE.Idle);
                        }
                    }
                    break;
            }

            if (m_CurPos == m_WhereString.Length - 1)
            {
                //无论任何状态，只要到了最后一个字符，结束状态机
                EndWord();
                return;
            }
        }

        private void SplitWhereString(String whereString)
        {
            System.Diagnostics.Debug.Assert(whereString != null);

            m_State = T_STATE.Idle;

            m_LastPos = 0;
            m_CurPos = 0;

            while (m_CurPos < whereString.Length)
            {
                StateMachine(whereString[m_CurPos]);
                m_CurPos++;
            }
        }

        private String GetParaValue(String paraName, object value)
        {
            if ((value is int) || (value is uint) ||
                (value is short) || (value is ushort) ||
                (value is sbyte) || (value is byte) ||
                (value is long) || (value is ulong) ||
                (value is float) || (value is double)
                )
            {
                return value.ToString();
            }

            if ((value is string) || (value is char))
            {
                return "'" + value.ToString().Replace("'", "''") + "'";
            }

            if (value is DateTime)
            {
                DateTime d = (DateTime)value;

                return "'" + d.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }

            if (value == DBNull.Value)
            {
                return "NULL";
            }

            throw new ParaWhereStringException(String.Format("invalid type of para={0}!",
                paraName));
        }

        /**//// <summary>
        /// 根据参数获取条件子句
        /// </summary>
        /// <param name="whereString">
        /// 带参数的条件子句，如
        /// "Price>@MinPrice and Price < @MaxPrice"
        /// </param>
        /// <param name="parameters">参数列表</param>
        /// <returns>获取实际的条件子句，如 "Price > 10 and Price < 100"</returns>
        public String GetWhereString(String whereString, List<SqlParameter> parameters)
        {
            if (parameters == null)
            {
                return whereString;
            }

            Clear();

            m_WhereString = whereString;
            SplitWhereString(whereString);

            Hashtable table = new Hashtable();

            foreach (SqlParameter para in parameters)
            {
                if (para.Value == null)
                {
                    table['@' + para.ParameterName.ToLower()] = DBNull.Value;
                }
                else
                {
                    table['@' + para.ParameterName.ToLower()] = para.Value;
                }
            }

            StringBuilder whereStr = new StringBuilder();

            foreach (String str in m_Words)
            {
                if (str.Length > 0)
                {
                    if (str[0] == '@')
                    {
                        object value = table[str.ToLower().Trim()];
                        if (value == null)
                        {
                            throw new ParaWhereStringException(String.Format("para={0} does not in parameters!",
                                str));
                        }

                        whereStr.Append(GetParaValue(str, value));
                        continue;
                    }
                }

                whereStr.Append(str);
            }

            return whereStr.ToString();
        }

    }

    /**//// <summary>
    /// 数据存储扩展
    /// </summary>
    public class DataRepositoryEx
    {
        /**//// <summary>
        /// 获取分页的查询结果,查询语句必须是
        /// Select 形式的，不能处理存储过程
        /// </summary>
        /// <param name="fields">where 子句前面的部分，不能有top关键字 如 “Price，ReleaseTime, RecName as Address”</param>
        /// <param name="tableName">要查询的表名</param>
        /// <param name="condition">带参数的 where子句，不包括where关键字 如 “Price > @MinPrice and Price < @MaxPrice”</param>
        /// <param name="parameters">where子句的参数</param>
        /// <param name="orderBy">order by 子句部分, 如果有Group by 也可以写在这里 如“order by ReleaseTime ASC”</param>
        /// <param name="pageNo">页面号，从0开始编号</param>
        /// <param name="pageLength">页面长度，即每页面记录数</param>
        /// <param name="count">输出查询结果的总数</param>
        /// <returns>以数据表形式返回查询结果集</returns>
        static public DataTable SelectPaged(String fields, String tableName,
            String condition, List<SqlParameter> parameters, String orderBy, int pageNo, int pageLength, out int count)
        {
            System.Diagnostics.Debug.Assert(pageNo >= 0);
            System.Diagnostics.Debug.Assert(pageLength > 0);

            ParaWhereString paraWhereStr = new ParaWhereString();
            String sqlCond = paraWhereStr.GetWhereString(condition, parameters);

            String sql;

            if (condition == null)
            {
                condition = "";
            }

            if (condition == "")
            {
                sql = String.Format("select count(*) cnt from {0}", tableName);
            }
            else
            {
                sql = String.Format("select count(*) cnt from {0} where {1}", tableName, sqlCond);
            }

            DataSet ds = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql);

            count = (int)ds.Tables[0].Rows[0]["cnt"];

            int upperBound = (pageNo + 1) * pageLength;

            int lowerBound = pageNo * pageLength;

            if (condition == "")
            {
                sql = String.Format("select top {0} {1} from {2} ", upperBound, fields, tableName);
            }
            else
            {
                sql = String.Format("select top {0} {1} from {2} where {3} ", upperBound, fields, tableName, sqlCond);
            }

            if (orderBy != "" && orderBy != null)
            {
                sql += orderBy;
            }

            ds = DataRepository.Provider.ExecuteDataSet(CommandType.Text, sql);

            if (ds.Tables[0].Rows.Count <= lowerBound)
            {
                ds.Tables[0].Clear();
            }
            else
            {
                for (int i = 0; i < lowerBound; i++)
                {
                    ds.Tables[0].Rows.RemoveAt(0);
                }
            }

            return ds.Tables[0];
        }
    }

ParaWhereString  类用于将带参数的条件子句转换为不带参数的条件子句，供GetPaged,GetAll两个方法使用。这个类是一个通用的类，也可以用于
其他应用中获取带参数的条件子句的最终转换后的条件子句。

DataRepositoryEx 类提供分查询的方法。

ParaWhereString 调用示例

            ParaWhereString paraWhereString = new ParaWhereString();

            string whereString = "price>@minPrice and price <= @maxPrice and str like '%adb''@aaa dsafj'";

            List<SqlParameter> paras = new List<SqlParameter>();
               paras.Add(new SqlParameter("minPrice", 100));
               paras.Add(new SqlParameter("MaxPrice", 1000));


            String sql = paraWhereString.GetWhereString(whereString, paras);

            Console.WriteLine(sql);

输出结果：
price>100 and price <= 1000 and str like '%adb''@aaa dsafj'

DataRepositoryEx 调用示例

用于测试的表结构

use Test

   GO

Create Table Test
(
id int identity (1,1) not null,
a int

)

向表Test中插入若干条连续的记录

查询分页数据示例

        int count;

        List<SqlParameter> paras = new List<SqlParameter>();
        paras.Add(new SqlParameter("min", 3));
        paras.Add(new SqlParameter("max", 30));

        DataTable table = SecUser.Cert.BLL.DataRepositoryEx.SelectPaged("id, a", "test..test", "id >= @min and id < @max", 
            paras, "order by id DESC", 0, 10, out count);

        Response.Write(String.Format("Count={0}", count));

        foreach(DataRow row in table.Rows)
        {
            Response.Write(String.Format("</p>{0}", row["id"]));
        }

查询结果：

Count=27

29

28

27

26

25

24

23

22

21

20