本文档后端采用.Net5 Web Api。
[GitHubhisql最新源码下载] https://github.com/tansar/HiSql
[HiSql.net文档] https://hisql.net/
[HiSql 查询语句教程] https://www.cnblogs.com/tansar/p/15779029.html
[hisql orm 框架insert数据写入教程] https://www.cnblogs.com/tansar/p/15786799.html
[hisql与目前比较流行的ORM框架性能测试对比] https://www.cnblogs.com/tansar/p/15774492.html
[NuGet官网] NuGet.io
注意:HiSql支持framework4.6 .netcore .net5或以上环境
安装包
1,通过NuGet的方式
2,通过管理包程序控制台(不翻墙可能有点慢,官网在前言里面有)
3,通过源码编译
git clone https://github.com/tansar/HiSql.git
为了便于查看操作信息,建议也安装Nlog的包,方便日志写入
基础使用介绍
连接数据库
1,在项目中新建类文件 HiSqlSetupExtension.cs, 用于注入数据库配置,hisql数据库访问对象。
数据库连接字符串直接写在appsettings.json文件中,不建议直接写在方法里。
appsettings.json
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
//数据库一定要是真是存在的,目前HiSql只支持DbFirst
"ConnectionStrings": {
"Default": "Data Source=.;Initial Catalog=HiSqlTest;Integrated Security=True"
},
"AllowedHosts": "*"
}
HiSqlSetupExtension.cs
using HiSql;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using System;
namespace Hisql
{
public static class HiSqlSetupExtension
{
public static IServiceCollection AddHiSqlSetup(this IServiceCollection services)
{
//注入HiSqlConfig
services.AddTransient<HiSqlConfig>();
//注入HiSqlClient
services.AddTransient<HiSqlClient>((d) =>
{
var config = d.GetService<HiSqlConfig>();
var hisql = new HiSqlClient(config);
return hisql;
});
return services;
}
}
internal class HiSqlConfig : ConnectionConfig
{
private static readonly NLog.Logger logger = NLog.LogManager.GetLogger("HiSqlSetup");
public HiSqlConfig(IConfiguration configuration)
{
DbType = DBType.SqlServer;
DbServer = "HISQL";
ConnectionString = configuration.GetSection("ConnectionStrings:Default").Value;
Schema = "dbo";
SqlExecTimeOut = 1000 * 5;
AppEvents = new AopEvent()
{
OnDbDecryptEvent = (connstr) =>
{
//解密连接字段
return connstr;
},
OnLogSqlExecuting = (sql, param) =>
{
//sql执行前 日志记录 (异步)
#if DEBUG
logger.Info($"执行前sql:{sql} time:{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss ffff")}");
#endif
},
OnLogSqlExecuted = (sql, param) =>
{
#if DEBUG
//sql执行后 日志记录 (异步)
logger.Info($"执行后sql:{sql} time:{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss ffff")}");
#endif
},
OnSqlError = (sqlEx) =>
{
//sql执行错误后 日志记录 (异步)
logger.Error($"执行错误:{sqlEx.Message} sql:{sqlEx.Sql} time:{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss ffff")}");
},
OnTimeOut = (int timer) =>
{
//logger.Trace($"执行超时:{timer} time:{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss ffff")}");
}
};
}
}
}
2,在 Startup 中的ConfigureServices方法中,添加 hisql的使用。
3,新建控制器 HiSqlController,添加初始化方法。
private readonly HiSqlClient sqlClient;
public HiSqlController(HiSqlClient sqlClient)
{
this.sqlClient = sqlClient;
}
/// <summary>
/// 初始化HiSql
/// </summary>
/// <returns></returns>
[HttpGet]
public JsonResult Install()
{
sqlClient.CodeFirst.InstallHisql();
var tables = sqlClient.DbFirst.GetTables().ToList().Where(t => t.TabName.StartsWith("H"));
return new JsonResult(tables);
}
5,启动项目后,在swagger中找到方法执行后,系统会创建下图的4个基础表
表名 | 说明 |
Hi_TabModel | 表结构信息主表 |
Hi_FieldModel | 表结构信息明细表 |
Hi_Domain | 数据域 |
Hi_DataElement | 数据元素 |
添加表数据
首先先建立一张测试表
USE [HiSqlTest]
GO
/****** Object: Table [dbo].[H_Test] Script Date: 2021/10/26 9:37:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[H_Test](
[Hid] [int] NOT NULL,
[UserName] [nchar](50) NULL,
[UserAge] [int] NULL,
[ReName] [nvarchar](50) NULL,
CONSTRAINT [PK_H_Test] PRIMARY KEY CLUSTERED
(
[Hid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[H_Test] ADD CONSTRAINT [DF_H_Test_UserAge] DEFAULT ((0)) FOR [UserAge]
GO
插入并查询数据
/// <summary>
///添加数据
/// </summary>
/// <returns></returns>
[HttpPost]
public ActionResult InsertDate()
{
//查询该数据是否存在
//Query(查询的表名).Field(字段名,*号代表查询表中所有字段).Where(new Filter { { "字段名", OperType.EQ, "值" } })
var Isvail = sqlClient.Query("H_Test").Field("*").Where(new Filter { { "UserName", OperType.EQ, "TestNo1" } }).ToTable();
if (!(Isvail.Rows.Count > 0))
{
//不存在则添加数据
//Insert("表名", new { 字段名 = 值})
sqlClient.Insert("H_Test", new { Hid = 1, UserName = "TestNo1", UserAge = 100, ReName = "Tom" }).ExecCommand();
}
//查询添加数据后的结果
var result = sqlClient.Query("H_Test").Field("*").ToTable();
//定义一个集合存数据
List<string> obj = new List<string>();
//遍历查询得到的数据
foreach (DataRow item in result.Rows)
{
obj.Add(item["UserName"].ToString());
}
//输出结果
return new JsonResult(obj);
}
修改表数据
/// <summary>
/// 修改表中数据
/// </summary>
/// <param name="test"></param>
/// <returns></returns>
[HttpPost]
public ActionResult UpdateData(Test test)
{
//修改方法
var rtn= sqlClient.Update("H_Test", new {
Hid = test.Hid,
UserName = test.UserName,
UserAge = test.UserAge,
ReName = test.ReName,
}).ExecCommand();
//查询修改数据后的结果
var result = sqlClient.Query("H_Test").Field("*").ToTable();
//定义对象集合
List<Test> list = new List<Test>();
//获取模型的类型
Type type = typeof(Test);
string tempName = "";
foreach (DataRow item in result.Rows)
{
Test objt = new Test();
PropertyInfo[] propertys = objt.GetType().GetProperties();// 获得此模型的公共属性
foreach (PropertyInfo pro in propertys)
{
//获取字段名
tempName = pro.Name;
if (result.Columns.Contains(tempName))
{
//判断字段名是否存在
if (!pro.CanWrite) continue;
object value = item[tempName];
if (value == DBNull.Value) continue;
if(pro.PropertyType==typeof(string))
{
pro.SetValue(objt, value.ToString(), null);
}
else
{
pro.SetValue(objt, value, null);
}
}
}
list.Add(objt);
}
return new JsonResult(list);
}
删除表数据
/// <summary>
/// 删除数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
[HttpPost]
public ActionResult DeleteData(int id)
{
int outcome = sqlClient.Delete("H_Test", new { Hid = id }).ExecCommand();
// outcome大于0表示删除成功 否则为失败
//如果不传任何条件则表示删除该表的所有数据,该操作有风险请慎用
//int v = sqlClient.Delete("H_Test").ExecCommand();
if (outcome>0)
{
return Content("删除成功");
}
else
{
return Content("未知错误");
}
}
添加视图
/// <summary>
/// 创建视图
/// </summary>
/// <param name="ViewName"></param>
/// <returns></returns>
[HttpPost]
public ActionResult CreatView(string ViewName)
{
//获取数据库中所有视图
List<TableInfo> ViewsInfo = sqlClient.DbFirst.GetViews();
//判断新建的视图名称是否已经存在
if (ViewsInfo.Any(x => x.TabName == ViewName))
{
return Content(ViewName + "视图已存在");
}
else
{
//创建视图
//CreateView(视图名称,sqlClient.HiSql("查询语句").ToSql(),OpLevel.Execute);
var rtn = sqlClient.DbFirst.CreateView(ViewName,
sqlClient.HiSql("select * from H_Test").ToSql(),
OpLevel.Execute);
return new JsonResult(rtn.Item2 + "\n\r执行语句:" + rtn.Item3);
}
}
修改视图
/// <summary>
/// 修改视图
/// </summary>
/// <param name="ViewName"></param>
/// <param name="sql"></param>
/// <returns></returns>
[HttpPost]
public ActionResult EditView(string ViewName, string sql)
{
//判断视图是否存在
List<TableInfo> ViewsInfo = sqlClient.DbFirst.GetViews();
if (ViewsInfo.Any(x => x.TabName == ViewName))
{
//修改视图
//ModiView(视图名称,sqlClient.HiSql("修改后的查询语句").ToSql(),OpLevel.Execute);
var rtn = sqlClient.DbFirst.ModiView(ViewName,
sqlClient.HiSql(sql).ToSql(),
OpLevel.Execute);
//判断是否执行成功
if (rtn.Item1)
{
return new JsonResult(rtn.Item2 + "\n\r执行语句:" + rtn.Item3);
}
else
return new JsonResult(rtn.Item2);
}
else
{
return new JsonResult($"{ViewName}视图不存在");
}
}
删除视图
/// <summary>
/// 删除视图
/// </summary>
/// <param name="ViewName"></param>
/// <returns></returns>
[HttpPost]
public ActionResult DeleteView(string ViewName)
{
//判断视图是否存在
List<TableInfo> ViewsInfo = sqlClient.DbFirst.GetViews();
if (ViewsInfo.Any(x => x.TabName == ViewName))
{
//执行删除操作
//DropView(视图名称,OpLevel.Execute);
var rtn = sqlClient.DbFirst.DropView(ViewName,
OpLevel.Execute);
//判断是否删除成功
if (rtn.Item1)
{
return new JsonResult(rtn.Item2 + "\n\r执行语句:" + rtn.Item3);
}
else
return new JsonResult(rtn.Item2);
}
else
{
return new JsonResult($"{ViewName}视图不存在");
}
}
添加索引
/// <summary>
/// 创建索引
/// </summary>
/// <param name="IndexName"></param>
/// <returns></returns>
[HttpPost]
public List<string> CreateTableView(string TableNames)
{
List<string> obj = new List<string>();
//获取表结构
TabInfo tabInfo = sqlClient.Context.DMInitalize.GetTabStruct("Hi_FieldModel");
//获取指定列名集合
List<HiColumn> hiColumns = tabInfo.Columns.Where(c => c.FieldName == "DefaultValue").ToList();
//创建索引
//CreateIndex(表名, 索引名称, 建立索引的列名集合, OpLevel.Execute);
var rtn = sqlClient.DbFirst.CreateIndex(TableNames, "H04_OrderInfo_POSOrderID", hiColumns, OpLevel.Execute);
if (rtn.Item1)
obj.Add(rtn.Item2 + "\n\r执行语句:" + rtn.Item3);
else
obj.Add(rtn.Item2);
return obj;
}
删除索引
/// <summary>
/// 删除索引
/// </summary>
/// <param name="IndexName"></param>
/// <returns></returns>
[HttpGet]
public List<string> DeleteIndexs(string IndexName)
{
List<string> obj = new List<string>();
//DelIndex(表名, 索引名, OpLevel.Execute);
var rtn = sqlClient.DbFirst.DelIndex("Hi_FieldModel", "H04_OrderInfo_POSOrderID", OpLevel.Execute);
if (rtn.Item1)
obj.Add(rtn.Item2 + "\n\r执行语句:" + rtn.Item3);
else
obj.Add(rtn.Item2);
return obj;
}
HiSql.dll 5.0
「5.0」等文件 https://www.aliyundrive.com/s/pmz6ZUa2Rnr 点击链接保存,或者复制本段内容,打开「阿里云盘」APP ,无需下载极速在线查看,视频原画倍速播放。
HiSql.dll 6.0
「6.0」等文件 https://www.aliyundrive.com/s/TkydUzfwy7Z 点击链接保存,或者复制本段内容,打开「阿里云盘」APP ,无需下载极速在线查看,视频原画倍速播放。