ITPub博客

首页 > Linux操作系统 > Linux操作系统 > C# SQL语句自动生成 给出NUnit Test方案

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

原创 Linux操作系统 作者:iDotNetSpace 时间:2009-07-23 10:24:15 0 删除 编辑

为了降低耦合,为了避免程序中出现太多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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-04

  • 博文量
    2376
  • 访问量
    5303883