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标签中：

2. sqlSugar的使用

2.1 依赖引入

2.2 工具类

using gse.Constant;
using SqlSugar;
using System;
namespace gse.Tools
{
    /// 
    /// SqlSugar配置类
    /// 
    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
{
    /// 
    /// 项目实体
    /// 
    [SugarTable("tbl_project")]
    public class Project
    {
        /// 
        /// 主键
        /// 
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        /// 
        /// 项目ID
        /// 
        public string ProjectId { get; set; }
        /// 
        /// 项目名称
        /// 
        public string ProjectName { get; set; }
        /// 
        /// 项目详细参数
        /// 
        [SugarColumn(IsNullable = true)]
        public string ProjectDesc { get; set; }
        /// 
        /// 是否为当前项目 0否 1是
        /// 
        public int CurrentFlag { get; set; }
        /// 
        /// 项目创建时间
        /// 
        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();
        /// 
        /// 项目添加
        /// 
        /// 
        public string AddProject(Project project)
        {
            string msg = "";
            if (string.IsNullOrEmpty(project.ProjectId))
            {
                return msg = "项目编号不能为空";
            }
            if (string.IsNullOrEmpty(project.ProjectName))
            {
                return msg = "项目名称不能为空";
            }
            List pro1 = SqlSugarBase.Db.Queryable()
                .Where(pro => pro.ProjectId == project.ProjectId).ToList();
            if (pro1.Count > 0)
            {
                return msg = "项目编号重复";
            }
            List pro2 = SqlSugarBase.Db.Queryable()
            .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;
        }
        /// 
        /// 更新项目
        /// 
        /// 项目信息
        public string UpdateProject(Project project)
        {
            string msg = "";
            if (string.IsNullOrEmpty(project.ProjectId))
            {
                return msg = "项目编号不能为空";
            }
            if (string.IsNullOrEmpty(project.ProjectName))
            {
                return msg = "项目名称不能为空";
            }
            List pros = SqlSugarBase.Db.Queryable()
                .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;
        }
        /// 
        /// 设置当前工程
        /// 
        /// 
        /// 
        public void setCurrentProject(Project project)
        {
            try
            {
                Project current = selectCurrentProject();
                if (current != null)
                {
                    //首先将原来的当前状态设置为非当前
                    current.CurrentFlag = 0;
                    SqlSugarBase.Db.Updateable(current).ExecuteCommand();
                }
                //设置选中工程为当前工程
                List pros = SqlSugarBase.Db.Queryable()
               .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;
            }
        }
        /// 
        /// 查看当前工程
        /// 
        /// 
        public Project selectCurrentProject()
        {
            List projects = SqlSugarBase.Db.Queryable()
                         .Where(pro => pro.CurrentFlag == 1).ToList();
            if (projects.Count > 0)
            {
                //首先将原来的当前状态设置为非当前
                return projects[0];
            }
            return null;
        }
        /// 
        /// 删除项目
        /// 
        /// 项目ID
        public void DeleteProject(string projectId)
        {
            SqlSugarBase.Db.Deleteable().Where(it => it.ProjectId == projectId).ExecuteCommand();
        }
        /// 
        /// 根据id查询项目
        /// 
        ///  项目ID
        /// 
        public Project SelectProjectById(string projectId)
        {
            List projects = SqlSugarBase.Db.Queryable()
                .Where(pro => pro.ProjectId == projectId).ToList();
            if (projects.Count > 0)
            {
                return projects[0];
            }
            return null;
        }
        /// 
        /// 根据项目id或者项目名称查询列表
        /// 
        /// 项目ID
        /// 项目名称
        /// 
        public List SelectProjectPage(string projectId, string name)
        {
            if (string.IsNullOrEmpty(projectId) && string.IsNullOrEmpty(name))
            {
                List projects = SqlSugarBase.Db.Queryable().ToList();
                return projects;
            }
            else
            {
                var exp = Expressionable.Create();
                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().Where(exp.ToExpression()).ToList();
                return projects;
            }
        }
    }
}