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;
}
}
}
}
文章版权声明：除非注明，否则均为主机测评原创文章，转载或复制请以超链接形式并注明出处。
