C# SQL语句自动生成给出NUnit Test方案

最新推荐文章于 2022-07-26 17:28:50 发布

cuibinmo3519

最新推荐文章于 2022-07-26 17:28:50 发布

阅读量109

点赞数

为了降低耦合，为了避免程序中出现太多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";

            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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12639172/viewspace-609969/

cuibinmo3519

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
C# SQL语句自动生成给出NUnit Test方案

为了降低耦合，为了避免程序中出现太多SQL 难以维护。我写了一个类来生成SQL欢迎大家提出意见 ^ ^如果为具体的类添加加上 enum { name,sex,age} 就更好用了 !using System;using Sys...
复制链接

扫一扫