C# SQL語句自動生成 給出NUnit Test方案

iDotNetSpace發表於2009-07-23
C#SQL

為了降低耦合，為了避免程式中出現太多SQL 難以維護。我寫了一個類來生成SQL

歡迎大家提出意見 ^ ^

如果為具體的類 新增加上 enum { name,sex,age} 就更好用了 !



using System;
using System.Collections.Generic;
using System.Text;
using NUnit.Framework;
using LimitedNumbersTableProject.TableClass;

namespace LimitedNumbersTableProject.NUnitTestClass
{
    [TestFixture]
    class SQLCreatorTest
    {
        SQLCreator creator;

        [TestFixtureSetUp]
        public void Init()
        {
            creator = new SQLCreator();
        }

        [Test]
        public void TestGetSQL1()
        {
            creator.FieldCount = 3;
            creator.TableName = "Person";

            creator.FieldInfoList[0].FieldType = typeof(int);
            creator.FieldInfoList[0].Name = "age";

            creator.FieldInfoList[1].FieldType = typeof(string);
            creator.FieldInfoList[1].Name = "sex";

            creator.FieldInfoList[2].FieldType = typeof(string);
            creator.FieldInfoList[2].Name = "name";

            Console.WriteLine(creator.GetSelectSQL(""));
            //select age,sex,name from Person

            Console.WriteLine(creator.GetDeleteSQL());
            //delete from Person

            Console.WriteLine(creator.GetSelectSQL("where age=13 or age =15"));
            //select age,sex,name from Person where age=13 or age =15

            Console.WriteLine(creator.GetSelectSQL("top 10", "where age=13 or age =15"));
            //select top 10 age,sex,name from Person where age=13 or age =15
        }

        [Test]
        public void TestGetSQL2()
        {
            creator.FieldCount = 3;
            creator.WhereFieldCount = 3;
            creator.TableName = "Person";

            creator.FieldInfoList[0].FieldType = typeof(int);
            creator.FieldInfoList[0].Name = "age";

            creator.FieldInfoList[1].FieldType = typeof(string);
            creator.FieldInfoList[1].Name = "sex";

            creator.FieldInfoList[2].FieldType = typeof(string);
            creator.FieldInfoList[2].Name = "name";

            creator.WhereFieldInfoList[0].FieldType = typeof(string);
            creator.WhereFieldInfoList[0].Condition = "<>";
            creator.WhereFieldInfoList[0].Name = "sex";
            creator.WhereFieldInfoList[0].Value = "男";

            creator.WhereFieldInfoList[1].FieldType = typeof(string);
            creator.WhereFieldInfoList[1].Condition = "=";
            creator.WhereFieldInfoList[1].Name = "name";
            creator.WhereFieldInfoList[1].Value = "Jack";

            creator.WhereFieldInfoList[2].FieldType = typeof(int);
            creator.WhereFieldInfoList[2].Condition = "=";
            creator.WhereFieldInfoList[2].Name = "age";
            creator.WhereFieldInfoList[2].Value = 10;

            Console.WriteLine(creator.GetSelectSQL("group by name"));
            /*select age,sex,name from Person where 
                     sex <> '男' and name = 'Jack' and age = 10  group by name*/

            Console.WriteLine(creator.GetDeleteSQL());
            //delete from  Person  where  sex <> '男' and name = 'Jack' and age = 10  
        }

        [Test]
        public void TestGetSQL3()
        {
            creator.FieldCount = 3;
            creator.WhereFieldCount = 3;
            creator.TableName = "Person";

            creator.FieldInfoList[0].FieldType = typeof(int);
            creator.FieldInfoList[0].Name = "age";
            creator.FieldInfoList[0].Value = 18;

            creator.FieldInfoList[1].FieldType = typeof(string);
            creator.FieldInfoList[1].Name = "sex";
            creator.FieldInfoList[1].Value = "女";

            creator.FieldInfoList[2].FieldType = typeof(string);
            creator.FieldInfoList[2].Name = "name";
            creator.FieldInfoList[2].Value = "Lili";

            creator.WhereFieldInfoList[0].FieldType = typeof(string);
            creator.WhereFieldInfoList[0].Condition = "<>";
            creator.WhereFieldInfoList[0].Name = "sex";
            creator.WhereFieldInfoList[0].Value = "男";

            creator.WhereFieldInfoList[1].FieldType = typeof(string);
            creator.WhereFieldInfoList[1].Condition = "=";
            creator.WhereFieldInfoList[1].Name = "name";
            creator.WhereFieldInfoList[1].Value = "Jack";

            creator.WhereFieldInfoList[2].FieldType = typeof(int);
            creator.WhereFieldInfoList[2].Condition = "=";
            creator.WhereFieldInfoList[2].Name = "age";
            creator.WhereFieldInfoList[2].Value = 100;

            Console.WriteLine(creator.GetUpdateSQL());
            //update Person set  age = 18 , sex = '女' , name = 'Lili'  where 
            //sex <> '男' and name = 'Jack' and age = 100 

            Console.WriteLine(creator.GetDeleteSQL());
            //delete from  Person  where  sex <> '男' and name = 'Jack' and age = 100 

            Console.WriteLine(creator.GetInsertIntoSQL());
            //insert into Person ( age,sex,name ) values ( 18,'女','Lili' )

            Console.WriteLine(creator.GetInsertIntoSelectSQL("Angel"));
            //insert into Person ( age,sex,name )
            //  select age,sex,name from Angel where  sex <> '男' and name = 'Jack' and age = 100   
        }

    }
}

using System;
using System.Collections.Generic;
using System.Text;
using LimitedNumbersTableProject.StringTools;

namespace LimitedNumbersTableProject.TableClass
{
    public class SQLCreator
    {
        #region 建構函式
        //private static SQLCreator lnt = null;
        public SQLCreator()
        {
            FieldInfoList = new List();
            WhereFieldInfoList = new List();
        }
        //public static SQLCreator GetInstance()
        //{
        //    if (lnt == null)
        //        return lnt = new SQLCreator();
        //    return lnt;
        //}
        #endregion

        #region 輔助函式

        ///


        /// 清除表欄位的選中狀態
        ///
        public void Reset()
        {
            foreach (FieldInfo info in FieldInfoList)
            {
                info.Reset();
            }
            foreach (FieldInfo info in WhereFieldInfoList)
            {
                info.Reset();
            }
        }

        private string GetWhereSubSentence()
        {
            MyStringTool mst = MyStringTool.GetInstance();
            string whereSubSentence = string.Empty;
            foreach (FieldInfo info in WhereFieldInfoList)
            {
                if (info.IsSelected)
                {
                    whereSubSentence += string.Format(" {0} {1} {2} and", info.Name, info.Condition, info.Value);
                }
            }
            whereSubSentence = mst.RemoveStringFromEnd(whereSubSentence, "and".Length);//將最後的"and"刪除
            return whereSubSentence;
        }

        private string GetSelectSubSentence()
        {
            MyStringTool mst = MyStringTool.GetInstance();
            string selectItemsStr = string.Empty;
            foreach (FieldInfo info in FieldInfoList)
            {
                if (info.IsSelected)
                {
                    selectItemsStr += info.Name + ",";
                }
            }
            selectItemsStr = mst.RemoveStringFromEnd(selectItemsStr, ",".Length);//將最後的","刪除
            return selectItemsStr;
        }

        private string GetSelectValuesSubSentence()
        {
            MyStringTool mst = MyStringTool.GetInstance();
            string selectItemsStr = string.Empty;
            foreach (FieldInfo info in FieldInfoList)
            {
                if (info.IsSelected)
                {
                    selectItemsStr += info.Value + ",";
                }
            }
            selectItemsStr = mst.RemoveStringFromEnd(selectItemsStr, ",".Length);//將最後的","刪除
            return selectItemsStr;
        }

        #endregion

        #region LimitNubmerTable的欄位
        private string _tableName;
        public string TableName
        {
            get { return _tableName; }
            set { _tableName = value; }
        }

        private List _fieldInfoList;
        public List FieldInfoList
        {
            get { return _fieldInfoList; }
            private set { _fieldInfoList = value; }
        }

        private List _whereFieldInfoList;
        public List WhereFieldInfoList
        {
            get { return _whereFieldInfoList; }
            set { _whereFieldInfoList = value; }
        }

        private int _fieldCount;
        ///


        /// 設定這個屬性時，設定表的欄位個數
        ///
        public int FieldCount
        {
            get { return _fieldCount; }
            set
            {
                _fieldCount = value;
                FieldInfoList.Clear();
                for (int i = 0; i < value; i++)
                {
                    FieldInfoList.Add(new FieldInfo());
                }
            }
        }

        private int _whereFieldCount;
        ///


        /// 寫where子句時要用的欄位的個數
        ///
        public int WhereFieldCount
        {
            get { return _whereFieldCount; }
            set
            {
                _whereFieldCount = value;
                WhereFieldInfoList.Clear();
                for (int i = 0; i < value; i++)
                {
                    WhereFieldInfoList.Add(new FieldInfo());
                }
            }
        }
        #endregion

        #region 自動生成SQL語句

        #region 生成InsertInto語句
        ///


        /// 生成 Insert Into SQL
        ///
        ///
        public string GetInsertIntoSQL()
        {

            //select
            string insertSubSentence = GetSelectSubSentence();

            //select values
            string selectValuesSubSentence = GetSelectValuesSubSentence();

            return string.Format("insert into {0} ( {1} ) values ( {2} ) ", TableName, insertSubSentence, selectValuesSubSentence);
        }
        #endregion

        #region 生成insert into select

        ///


        /// 生成 Insert Into select SQL
        ///
        ///
        public string GetInsertIntoSelectSQL(string secondTableName)
        {

            //select SubSentence
            string insertSubSentence = GetSelectSubSentence();

            string tmp = TableName;
            TableName = secondTableName;

            //select SQL
            string selectValuesSubSentence = GetSelectSQL();

            TableName = tmp;

            return string.Format("insert into {0} ( {1} )  {2} ", TableName, insertSubSentence, selectValuesSubSentence);
        }

        #endregion

        #region 生成Select語句
        ///


        /// 生成 Select SQL
        ///
        /// 新增到最後的SQL語句（可選）
        ///
        public string GetSelectSQL(string additionalSentence)
        {
            return GetSelectSQL("", additionalSentence);
        }
        public string GetSelectSQL()
        {
            return GetSelectSQL("", "");
        }
        ///
        /// 生成 Select SQL
        ///
        /// top 子句
        /// 比如新增：where子句,orderby,desc 等
        ///
        public string GetSelectSQL(string topSubSentence, string additionalSentence)
        {
            if (additionalSentence == null)
                return string.Empty;

            //select
            string selectItemsStr = GetSelectSubSentence();

            //where
            string whereSubSentence = GetWhereSubSentence();

            //return
            if (WhereFieldInfoList.Count != 0)
            {
                return string.Format("select {0} {1} from {2} where {3} {4}", topSubSentence,
                    selectItemsStr, TableName, whereSubSentence, additionalSentence);
            }
            else
                return string.Format("select {0} {1} from {2} {3}", topSubSentence,
                    selectItemsStr, TableName, additionalSentence);
        }
        #endregion

        #region 生成Update語句
        ///


        /// 生成Update語句
        ///
        ///
        public string GetUpdateSQL()
        {
            MyStringTool smt = MyStringTool.GetInstance();

            //update
            string updateItemsStr = string.Empty;
            foreach (FieldInfo info in FieldInfoList)
            {
                if (info.IsSelected)
                {
                    updateItemsStr += string.Format(" {0} = {1} ,", info.Name, info.Value);
                }
            }
            updateItemsStr = smt.RemoveStringFromEnd(updateItemsStr, ",".Length);//將最後的","刪除

            //where
            string whereSubSentence = GetWhereSubSentence();

            //return
            if (WhereFieldInfoList.Count != 0)
            {
                return string.Format("update {0} set {1} where {2} ", TableName, updateItemsStr, whereSubSentence);
            }
            else
                return string.Format("update {0} set {1} ", TableName, updateItemsStr);
        }


        #endregion

        #region 生成Delete語句

        public string GetDeleteSQL()
        {
            //where
            string whereSubSentence = GetWhereSubSentence();

            //return
            if (WhereFieldInfoList.Count != 0)
            {
                return string.Format("delete from  {0}  where {1} ", TableName, whereSubSentence);
            }
            else
                return string.Format("delete from {0} ", TableName);
        }

        #endregion

        #endregion
    }
}

using System;
using System.Collections.Generic;
using System.Text;

namespace LimitedNumbersTableProject.TableClass
{
    public class FieldInfo
    {
        #region 函式
        ///


        /// IsSelected設定為false
        ///
        public void Reset()
        {
            IsSelected = false;
        }

        ///


        /// IsSelected設定為true
        ///
        public void Set()
        {
            IsSelected = true;
        }

        public FieldInfo()
        {
            Value = string.Empty;
            Name = string.Empty;
            Condition = string.Empty;
            FieldType = typeof(string);
            IsSelected = false;
        }
        #endregion

        #region 欄位
        private string _name;
        ///


        /// 欄位名稱
        ///
        public string Name
        {
            get { return _name; }
            set 
            { 
                _name = value;
                IsSelected = true;
            }
        }

        private object _value;
        ///


        /// 設定值時，同時設定IsSelected為true
        /// 如果是string型別的欄位，則get時加單引號
        ///
        public object Value
        {
            get
            {
                //string和String的型別都是String
                if (FieldType == typeof(string))
                    return string.Format("'{0}'", _value);
                else
                    return _value;
            }
            set
            {
                _value = value;
                IsSelected = true;
            }
        }

        private bool _isSelected;
        ///


        /// 是否選中這個欄位
        ///
        public bool IsSelected
        {
            get { return _isSelected; }
            private set { _isSelected = value; }
        }

        private Type _type;
        ///


        /// 欄位的型別
        ///
        public Type FieldType
        {
            get { return _type; }
            set { _type = value; }
        }

        private string _condition;
        ///


        /// where子句需要的條件
        ///
        public string Condition
        {
            get { return _condition; }
            set { _condition = value; }
        }
        #endregion
    }
}

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12639172/viewspace-609969/，如需轉載，請註明出處，否則將追究法律責任。

相關文章