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

為了降低耦合，為了避免程式中出現太多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/，如需轉載，請註明出處，否則將追究法律責任。

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

相關文章

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