根据实体类生成SQL语句（增删改）

本文主要是介绍根据实体类生成SQL语句（增删改），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
根据实体类生成SQL语句（增删改）
代码：
Skip to content
Product 
Team
Enterprise
Explore 
Marketplace
Pricing 
Search
Sign in
Sign up
MaChuhao
/
MCHDAL
Public
Code
Issues
Pull requests
Actions
Projects
Wiki
Security
Insights
MCHDAL/CreateSQLStr.cs /
@MaChuhao
MaChuhao 第一次版本提交，该模块是在.NET环境下自动生成SQL语句，并封装了增删查改的方法。
Latest commit 33c3783 on 6 Mar 2017
 History
 1 contributor
259 lines (224 sloc)  9.07 KB
   
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Reflection;
using System.Data.SqlClient;

namespace MCHDAL
{
    public class CreateSQLStr
    {
        /// <summary>
        /// 通用实体类存储新数据到数据库的方法 
        /// 调用此方法可获得SQL Insert语句
        /// </summary>
        /// <typeparam name="T">模板T</typeparam>
        /// <param name="model">实体对象</param>
        /// <param name="tablename">表名</param>
        /// <returns></returns>
        public static string SaveSQLStr<T>(T model, string tablename)
        {

            //获得此模型的类型
            Type type = typeof(T);
            
            string fieldsName = "INSERT INTO " + tablename+ "(";
            string fieldsValue = "VALUES(";

            PropertyInfo[] propertys = model.GetType().GetProperties();
            //遍历该对象的所有属性
            foreach (PropertyInfo pi in propertys)
            {
                string name = pi.Name;
                object value = pi.GetValue(model, null);

                if(value != null)
                {
                    if(pi.PropertyType.Name.Equals("Int32"))
                    {
                        if(Int32.Parse(value.ToString()) != 0)
                        {
                            fieldsName = fieldsName + pi.Name + ',';

                            fieldsValue = fieldsValue + Int32.Parse(value.ToString()) + ',';
                        }
                    }
                    else if (pi.PropertyType.Name.Equals("DateTime"))
                    {
                        if (value.Equals("0001/1/1 0:00:000") || value.Equals("0001/1/1 0:00:00") || (DateTime)value == DateTime.MinValue)
                        {
                            continue;
                        }
                        else
                        {
                            fieldsName = fieldsName + pi.Name + ',';
                            fieldsValue = fieldsValue + "'" + value.ToString() + '\'' + ',';
                        }
                    }
                    else if (pi.PropertyType.Name.Equals("String")|| pi.PropertyType.Name.Equals("Nullable`1"))
                    {
                        fieldsName = fieldsName + pi.Name + ',';

                        fieldsValue = fieldsValue + "'" + value.ToString() + '\'' + ',';
                    }
                   
                   
                }
                
            }
            fieldsName = fieldsName.Substring(0, fieldsName.Length - 1) + ')' + ' ';

            //确保该语句返回值为主键ID
            fieldsValue = fieldsValue.Substring(0, fieldsValue.Length - 1) + ')' + "  SELECT @@IDENTITY";

            return fieldsName + fieldsValue;
        }


        /// <summary>
        /// 通用实体类更新数据库表数据的方法 
        /// 调用此方法可获得SQL Update语句
        /// </summary>
        /// <typeparam name="T">模板T</typeparam>
        /// <param name="model">实体对象</param>
        /// <param name="tablename">表名</param>
        /// <returns></returns>
        public static string UpdateSQLStr<T>(T model, string tablename)
        {
            bool flag = false;
            //获得此模型的类型
            Type type = typeof(T);

            string fields = "Update " + tablename + " set ";
            string where = " where ";

            PropertyInfo[] propertys = model.GetType().GetProperties();
            //遍历该对象的所有属性
            foreach (PropertyInfo pi in propertys)
            {
                string name = pi.Name;
                object value = pi.GetValue(model, null);

                if (value != null)
                {
                    if (pi.PropertyType.Name.Equals("Int32"))
                    {
                        if (Int32.Parse(value.ToString()) != 0)
                        {
                            if (name.Equals("ID"))
                            {
                                where = where + pi.Name + "=" + Int32.Parse(value.ToString());
                                flag = true;
                            }
                            else
                            {
                                fields = fields + pi.Name + '=' + Int32.Parse(value.ToString()) + ',';                               
                            }
                        }
                    }
                    else if (pi.PropertyType.Name.Equals("DateTime"))
                    {
                        if (value.Equals("0001/1/1 0:00:000") || value.Equals("0001/1/1 0:00:00") || (DateTime)value == DateTime.MinValue)
                        {
                            continue;
                        }
                        else
                        {
                            fields = fields + pi.Name + '=' + "'" + value.ToString() + '\'' + ',';
                        }
                       
                    }
                    else if (pi.PropertyType.Name.Equals("String"))
                    {
                        fields = fields + pi.Name + '=' + "'" + value.ToString() + '\'' + ',';
                    }
                }

            }
            fields = fields.Substring(0, fields.Length - 1)  + ' ';

            if (flag)
            {
                return fields + where;
            }
            else
            {
                return "false";
            }
            
        }


        /// <summary>
        /// 通用实体类删除数据库表数据的方法
        /// 调用此方法可获得SQL Delete语句
        /// </summary>
        /// <typeparam name="T">模板T</typeparam>
        /// <param name="model">实体对象</param>
        /// <param name="tablename">表名</param>
        /// <returns></returns>
        public static string DeleteSQLStr<T>(T model, string tablename)
        {
            bool flag = false;
            //获得此模型的类型
            Type type = typeof(T);

            string fields = "Delete from  " + tablename;
            string where = " where ";

            PropertyInfo[] propertys = model.GetType().GetProperties();
            //遍历该对象的所有属性
            foreach (PropertyInfo pi in propertys)
            {
                string name = pi.Name;
                object value = pi.GetValue(model, null);
                if (name.Equals("ID") && Int32.Parse(value.ToString()) != 0)
                {
                    where = where + pi.Name + "=" + Int32.Parse(value.ToString());
                    flag = true;
                }
            }

            if (flag)
            {
                return fields + where;
            }
            else
            {
                return "false";
            }

        }


        /// <summary>
        /// 通用实体类查询数据库表数据的方法
        /// 调用此方法可获得SQL Select语句
        /// </summary>
        /// <typeparam name="T">模板T</typeparam>
        /// <param name="model">实体对象</param>
        /// <param name="tablename">表名</param>
        /// <returns></returns>
        public static string SelectSQLStr<T>(T model, string tableName)
        {
            string where = " where ";
            bool flag = false;
            PropertyInfo[] propertys = model.GetType().GetProperties();
            foreach(PropertyInfo pi in propertys)
            {
                string name = pi.Name;
                object value = pi.GetValue(model, null);

                if (value != null)
                {
                    if (pi.PropertyType.Name.Equals("Int32"))
                    {
                        if (Int32.Parse(value.ToString()) != 0)
                        {
                            where = where + pi.Name + "=" + Int32.Parse(value.ToString()) + " and ";
                            flag = true;                               
                        }
                    }else if (pi.PropertyType.Name.Equals("DateTime"))
                    {
                        //datetime转换时报错，因此以0001/1/1 0:00:000标识时间为空
                        if (value.Equals("0001/1/1 0:00:000")||value.Equals("0001/1/1 0:00:00")||(DateTime)value == DateTime.MinValue)
                        {
                            flag = true;
                        }
                        else
                        {
                            where = where + pi.Name + '=' + "'" + value.ToString() + '\'' + " and ";
                            flag = true;
                        }
                    }
                    else if (pi.PropertyType.Name.Equals("String"))
                    {
                        where = where + pi.Name + '=' + "'" + value.ToString() + '\'' +  " and ";
                        flag = true;
                    }


                }
            }

            where = where.Substring(0, where.Length - 4);

            if (flag)
            {
                return "SELECT * FROM " + tableName + where;
            }
            else
            {
                return "SELECT * FROM " + tableName;
            }
        }

    }
}
© 2022 GitHub, Inc.
Terms
Privacy
Security
Status
Docs
Contact GitHub
Pricing
API
Training
Blog
About
Loading complete
View Code
详细见 Git
这篇关于根据实体类生成SQL语句（增删改）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
Java教程

根据实体类生成SQL语句（增删改）

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯
