CAD二次开发（11）-在用C#进行CAD开发SQLite和SqlSugar的使用

1. System.Data.SQLite的使用

1.1 包的引入

利用 Nuget引入其相关包

1.2 App.config配置引入到CAD

因为我们当前的项目是类库项目，需要依托于宿主程序才可以运行，所以我们就得需要将SQLite自动生成的App.config里面的部分配置移植到CAD的配置文件中。保证System.Data.SQLite依赖的正常运行。

1.3 CAD配置文件中引入配置信息

打开后将如下内容复制到CAD安装目录下的acad.exe.config文件中的configuration标签中：

	<system.data>
	  <DbProviderFactories>
		<remove invariant="System.Data.SQLite.EF6" />
		<add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
	  <remove invariant="System.Data.SQLite" /><add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" /></DbProviderFactories>
	</system.data>
	<runtime>
	  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
		<dependentAssembly>
		  <assemblyIdentity name="System.Data.SQLite" publicKeyToken="db937bc2d44ff139" culture="neutral" />
		  <bindingRedirect oldVersion="0.0.0.0-1.0.118.0" newVersion="1.0.118.0" />
		</dependentAssembly>
	  </assemblyBinding>
	</runtime>

2. sqlSugar的使用

2.1 依赖引入

2.2 工具类

using gse.Constant;
using SqlSugar;
using System;

namespace gse.Tools
{
    /// <summary>
    /// SqlSugar配置类
    /// </summary>
    public class SqlSugarBase
    {
        //创建数据库对象 (用法和EF Dappper一样通过new保证线程安全)
        public  SqlSugarClient Db = new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = "datasource="+ CommonConstant.DB_DIRECTORY,
            DbType = DbType.Sqlite,
            IsAutoCloseConnection = true
        },
        db => {

            db.Aop.OnLogExecuting = (sql, pars) =>
            {

                //获取原生SQL推荐 5.1.4.63  性能OK
                Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));

            };

        });
    }
}

2.3 具体使用

实体类

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace gse.Entities
{
    /// <summary>
    /// 项目实体
    /// </summary>
    [SugarTable("tbl_project")]
    public class Project
    {
        /// <summary>
        /// 主键
        /// </summary>
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        /// <summary>
        /// 项目ID
        /// </summary>
        public string ProjectId { get; set; }

        /// <summary>
        /// 项目名称
        /// </summary>
        public string ProjectName { get; set; }

        /// <summary>
        /// 项目详细参数
        /// </summary>
        [SugarColumn(IsNullable = true)]
        public string ProjectDesc { get; set; }

        /// <summary>
        /// 是否为当前项目 0否 1是
        /// </summary>
        public int CurrentFlag { get; set; }

        /// <summary>
        /// 项目创建时间
        /// </summary>
        public string CreateTime { get; set; }

        public Project()
        {

        }

        public Project(int id, string projectId, string projectName, string projectDesc, int currentFlag, string createTime)
        {

            Id = id;
            ProjectId = projectId;
            ProjectName = projectName;
            ProjectDesc = projectDesc;
            CurrentFlag = currentFlag;
            CreateTime = createTime;
        }
        public override string ToString()
        {
            return "Project{" +
                   "Id='" + Id + '\'' +
                   ", ProjectId='" + ProjectId + '\'' +
                   ", ProjectName='" + ProjectName + '\'' +
                   ", ProjectDesc='" + ProjectDesc + '\'' +
                   ", CurrentFlag=" + CurrentFlag +
                   ", CreateTime='" + CreateTime + '\'' +
                   '}';
        }
    }
}

Service

using gse.Entities;
using gse.Tools;
using SqlSugar;
using System;
using System.Collections.Generic;

namespace gse.Service
{
    public class TblProjectService
    {

        SqlSugarBase SqlSugarBase = new SqlSugarBase();

        /// <summary>
        /// 项目添加
        /// </summary>
        /// <param name="project"></param>
        public string AddProject(Project project)
        {
            string msg = "";
            if (string.IsNullOrEmpty(project.ProjectId))
            {
                return msg = "项目编号不能为空";

            }
            if (string.IsNullOrEmpty(project.ProjectName))
            {
                return msg = "项目名称不能为空";
            }


            List<Project> pro1 = SqlSugarBase.Db.Queryable<Project>()
                .Where(pro => pro.ProjectId == project.ProjectId).ToList();
            if (pro1.Count > 0)
            {
                return msg = "项目编号重复";
            }

            List<Project> pro2 = SqlSugarBase.Db.Queryable<Project>()
            .Where(pro => pro.ProjectName == project.ProjectName).ToList();
            if (pro2.Count > 0)
            {
                return msg = "项目名称重复";
            }
            project.CreateTime = DateTimeUtils.DATE.Now();
            SqlSugarBase.Db.Insertable(project).ExecuteReturnIdentity();
            return msg;
        }

        /// <summary>
        /// 更新项目
        /// </summary>
        /// <param name="project">项目信息</param>
        public string UpdateProject(Project project)
        {
            string msg = "";
            if (string.IsNullOrEmpty(project.ProjectId))
            {
                return msg = "项目编号不能为空";

            }
            if (string.IsNullOrEmpty(project.ProjectName))
            {
                return msg = "项目名称不能为空";
            }
            List<Project> pros = SqlSugarBase.Db.Queryable<Project>()
                .Where(pro => pro.ProjectName == project.ProjectName && pro.ProjectId == project.ProjectId)
                .ToList();
            if (pros.Count > 0)
            {
                Project pro = pros[0];
                pro.ProjectId = project.ProjectId;
                pro.ProjectName = project.ProjectName;
                pro.ProjectDesc = project.ProjectDesc;
                pro.CurrentFlag = project.CurrentFlag;
                SqlSugarBase.Db.Updateable(pro).ExecuteCommand();
            }
            else
            {
                return msg = "项目不存在";
            }
            return msg;
        }

        /// <summary>
        /// 设置当前工程
        /// </summary>
        /// <param name="project"></param>
        /// <returns></returns>
        public void setCurrentProject(Project project)
        {
            try
            {
                Project current = selectCurrentProject();
                if (current != null)
                {
                    //首先将原来的当前状态设置为非当前
                    current.CurrentFlag = 0;
                    SqlSugarBase.Db.Updateable(current).ExecuteCommand();
                }
                //设置选中工程为当前工程
                List<Project> pros = SqlSugarBase.Db.Queryable<Project>()
               .Where(pro => pro.ProjectName == project.ProjectName && pro.ProjectId == project.ProjectId)
               .ToList();
                if (pros.Count > 0)
                {
                    Project pro = pros[0];
                    pro.CurrentFlag = 1;
                    SqlSugarBase.Db.Updateable(pro).ExecuteCommand();
                }
            }
            catch (Exception ex)
            {
                //事务回滚
                SqlSugarBase.Db.Ado.RollbackTran();
                return;
            }

        }

        /// <summary>
        /// 查看当前工程
        /// </summary>
        /// <param name="project"></param>
        public Project selectCurrentProject()
        {
            List<Project> projects = SqlSugarBase.Db.Queryable<Project>()
                         .Where(pro => pro.CurrentFlag == 1).ToList();
            if (projects.Count > 0)
            {
                //首先将原来的当前状态设置为非当前
                return projects[0];
            }
            return null;
        }

        /// <summary>
        /// 删除项目
        /// </summary>
        /// <param name="projectId">项目ID</param>
        public void DeleteProject(string projectId)
        {
            SqlSugarBase.Db.Deleteable<Project>().Where(it => it.ProjectId == projectId).ExecuteCommand();
        }

        /// <summary>
        /// 根据id查询项目
        /// </summary>
        /// <param name="projectId"> 项目ID</param>
        /// <returns></returns>
        public Project SelectProjectById(string projectId)
        {
            List<Project> projects = SqlSugarBase.Db.Queryable<Project>()
                .Where(pro => pro.ProjectId == projectId).ToList();
            if (projects.Count > 0)
            {
                return projects[0];
            }
            return null;
        }

        /// <summary>
        /// 根据项目id或者项目名称查询列表
        /// </summary>
        /// <param name="projectId">项目ID</param>
        /// <param name="name">项目名称</param>
        /// <returns></returns>
        public List<Project> SelectProjectPage(string projectId, string name)
        {
            if (string.IsNullOrEmpty(projectId) && string.IsNullOrEmpty(name))
            {
                List<Project> projects = SqlSugarBase.Db.Queryable<Project>().ToList();
                return projects;
            }
            else
            {
                var exp = Expressionable.Create<Project>();
                exp.OrIF(!string.IsNullOrEmpty(projectId), it => it.ProjectId.Equals(projectId));
                exp.OrIF(!string.IsNullOrEmpty(name), it => it.ProjectName.Contains(name));
                var projects = SqlSugarBase.Db.Queryable<Project>().Where(exp.ToExpression()).ToList();
                return projects;
            }

        }

    }
}