ORM架構-SqlSugar C#二次開發通用介面（SQLite，SQLServer，MySQL）
思路簡介
利用現有的SqlSugar框架進行二次開發適用於低資料量的通用SQL介面。
利用泛型將未知的資料型別序列化為json字元，在使用通用資料型別對資料庫進行指點互動
1.定義通用互動資料型別
2.定義通用介面
3.配置類
4.介面實現
5.注意事項及各個Sql安裝包
通用互動資料型別
public class SqlSDT
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int Kay { get; set; }
public string ID { get; set; }
[SugarColumn(Length = 11000)]
public string data { get; set; }
}
SqlSDT組成為：主鍵-Key，標誌-ID，資料段-data
Key：用於資料庫自增
ID：資料儲存時的標誌符號，用於資料查詢修改
data：資料儲存段，用於將資料序統一列化後儲存資料庫。
通用介面定義
public interface IDataBase
{
short SetType(string dbType);
short Connect(string ipAddr,string dbName,string userName,string passwd);
short Connect(string ConnectionString=null);
short Close();
short Write<T>(string tableName, string id,T data);
short Write<T>(string tableName, string id, List<T> data);
short Read<T>(string tablename, string id,ref T data);
short ReadList<T>(string tablename,ref List<T> data);
}
SQL介面：IDataBase
指令原型 Short Connect(string ipAddr,string dbName,string userName,string passwd)
| 指令Connect | |
|---|---|
| 指令說明 | 連線資料庫 |
| 指令引數 | 該指令共有 4個引數，引數的詳細資訊如下。 |
| 指令說明 | 連線資料庫 |
| ipAddr | 連線資料庫所需IP其他資料庫：可預設NULL |
| dbName | 需連線的資料庫名 |
| userName | 連線資料庫所需使用者名稱其他資料庫：可預設NULL |
| passwd | 連線資料庫所需密碼其他資料庫：可預設NULL |
| 指令返回值 | 操作是否成功。0-T ,-1-F |
指令原型 Short Close();
| 指令Close | |
|---|---|
| 指令說明 | 關閉資料庫連線 |
| 指令引數 | 該指令共有 0個引數，引數的詳細資訊如下。 |
| 指令返回值 | 操作是否成功。0-T ,-1-F |
指令原型 Void SetType(string dbType);
| 指令SetType | |
|---|---|
| 指令說明 | 設定連線資料庫型別 |
| 指令引數 | 該指令共有 1個引數，引數的詳細資訊如下。 |
| dbType | “SqlLite” “SqlServer”“MySql” |
| 指令返回值 | 無。 |
指令原型 Short Write(string tableName, string id,T data)
Short Write(string tableName, string id, List data)
| 指令Write | |
|---|---|
| 指令說明 | 向資料庫插入/更新 資料 |
| 指令引數 | 該指令共有 3個引數，引數的詳細資訊如下。 |
| tableName | 表名 |
| id | 資料儲存索引ID（儲存List時為起始索引，自動延伸） |
| data | 需插入或更新的資料或資料集 |
| 指令返回值 | 操作是否成功。0-T ,-1-F |
指令原型 Short Read(string tablename, string id, ref T data)
| 指令Read | |
|---|---|
| 指令說明 | 從指定表格中讀取資料 |
| 指令引數 | 該指令共有 3個引數，引數的詳細資訊如下。 |
| tablename | 表名 |
| id | 查詢索引ID |
| data | 從指定表格中讀取資料 |
| 指令返回值 | 操作是否成功。0-T ,-1-F |
指令原型 Short ReadList(string tablename,ref List data);
| 指令ReadList | |
|---|---|
| 指令說明 | 從指定表格中讀取資料 |
| 指令引數 | 該指令共有 2個引數，引數的詳細資訊如下。 |
| tablename | 表名 |
| data | 從指定表格中讀取資料List |
| 指令返回值 | 操作是否成功。0-T ,-1-F |
通用介面定義
public class Config
{
public static string GetCurrentProjectPath
{
get
{
return Environment.CurrentDirectory.Replace(@"\bin\Debug", "");
}
}
public static string defaultString = @"DataSource=" + GetCurrentProjectPath + @"\DataBase\";
public static string ConnectionString = @"DataSource=" + GetCurrentProjectPath + @"\DataBase\SqlSugar4xTest.sqlite";
public static string Server = "";
public static string Source = "";
public static string Database = "";
public static string Uid = "";
public static string Pwd = "";
public static SqlSugar.DbType dbType = SqlSugar.DbType.Sqlite;
public static string SqlConString
{
get
{
string ret = "";
switch (Config.dbType)
{
case SqlSugar.DbType.MySql:
case SqlSugar.DbType.SqlServer:
#region server=localhost;Database=SqlSugar4xTest;Uid=root;Pwd=haosq
ret =
"server=" + Server +
";Database=" + Database +
";Uid=" + Uid +
";Pwd=" + Pwd + ";";
#endregion
break;
case SqlSugar.DbType.Oracle:
#region Data Source=localhost/orcl;User ID=system;Password=jhl52771
ret =
"server=" + Server +
";Database=" + Database +
";Uid=" + Uid +
";Pwd=" + Pwd + ";";
#endregion
break;
case SqlSugar.DbType.Sqlite:
if (Database == "")
{
ret = defaultString;
return ret;
}
if (Database.Contains(".sqlite"))
{
ret = Database;
}
else
{
ret = Config.defaultString + Database + ".sqlite";
}
break;
case SqlSugar.DbType.PostgreSQL:
case SqlSugar.DbType.Dm:
case SqlSugar.DbType.Kdbndp:
default:
ret = "";
break;
}
return ret;
}
}
}
用於配置檔案儲存，主要物件成員：
dbType： 資料庫型別
SqlConString：SqlSugar連線資料庫的字元
介面實現
class SqliteCon : IDataBase
{
static SqlSugarClient db;
#region ****************介面**************
public short SetType(string dbType)
{
short ret =0;
SqlSugar.DbType Type = SqlSugar.DbType.Sqlite;
try
{
Config.dbType = (SqlSugar.DbType)Enum.Parse(typeof(SqlSugar.DbType), dbType);
}
catch (Exception ex)
{
ret = -1;
}
return ret;
}
/// <summary>
/// 連線sqlite資料庫
/// </summary>
/// <param name="ipAddr">無</param>
/// <param name="dbName">資料庫名</param>
/// <param name="userName">無</param>
/// <param name="passwd">無</param>
/// <returns></returns>
public short Connect(string ipAddr, string dbName, string userName, string passwd)
{
if (dbName == "") return -1;
try
{
switch (Config.dbType)
{
case SqlSugar.DbType.MySql:
case SqlSugar.DbType.SqlServer:
#region server=localhost;Database=SqlSugar4xTest;Uid=root;Pwd=haosq
Config.Server = ipAddr;
Config.Database = dbName;
Config.Uid = userName;
Config.Pwd = passwd;
#endregion
break;
case SqlSugar.DbType.Oracle:
#region Data Source=localhost/orcl;User ID=system;Password=jhl52771
Config.Source = ipAddr;
Config.Database = dbName;
Config.Uid = userName;
Config.Pwd = passwd;
#endregion
break;
case SqlSugar.DbType.Sqlite:
Config.Database = dbName;
break;
case SqlSugar.DbType.PostgreSQL:
case SqlSugar.DbType.Dm:
case SqlSugar.DbType.Kdbndp:
default:
return -1;
}
db = new SqlSugarClient(new ConnectionConfig()
{
DbType = Config.dbType,
ConnectionString = Config.SqlConString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true,
AopEvents = new AopEvents
{
OnLogExecuting = (sql, p) =>
{
Console.WriteLine(sql);
Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
}
}
});
//If no exist create datebase
if (!db.DbMaintenance.CreateDatabase()) return -1;
}
catch (Exception ex)
{
return -1;
}
return 0;
}
public short Connect(string ConnectionString = null)
{
short ret = 0;
try
{
if (ConnectionString != null)
{
Config.ConnectionString = ConnectionString;
}
db = new SqlSugarClient(new ConnectionConfig()
{
DbType = Config.dbType,
ConnectionString = Config.ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true,
AopEvents = new AopEvents
{
OnLogExecuting = (sql, p) =>
{
Console.WriteLine(sql);
Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
}
}
});
//If no exist create datebase
db.DbMaintenance.CreateDatabase();
}//"Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=master;Integrated Security=True;Connect Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=Test;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
catch (Exception ex)
{
ret = -1;
}
return ret;
}
public short Read<T>(string tablename, string id,ref T data)
{
short ret = 0;
try
{
var query = db.Queryable<SqlSDT>().AS(tablename).Where(it => it.ID == id).ToList();
if (query != null && query.Count > 0)
{
JsonToT(query[0].data, ref data);
//data = SqlSDT2T<T>(query[0]);
}
else
{
data = default(T);
}
}
catch (Exception ex)
{
ret = -1;
}
return ret;
}
public short ReadList<T>(string tablename,ref List<T> data)
{
short ret = 0;
try
{
var query = db.Queryable<SqlSDT>().AS(tablename).ToList();
if (query != null && query.Count > 0)
{
JsonToTList<T>(query, ref data);
//data = SqlSDT2T<T>(query);
}
}
catch (Exception ex)
{
ret = -1;
}
return ret;
}
/// <summary>
/// 鍵不存在是插入，存在是修改
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tableName">表名</param>
/// <param name="id">鍵</param>
/// <param name="data"></param>
/// <returns></returns>
public short Write<T>(string tableName, string id, T data)
{
short ret = 0;
try
{
var BaseTables = db.DbMaintenance.GetTableInfoList();
if (!HadTable(tableName))
{
//CreateTable<SqlSDT>(tableName);
db.MappingTables.Add(typeof(SqlSDT).Name, tableName);
db.CodeFirst.InitTables(typeof(SqlSDT));
}
var query = db.Queryable<SqlSDT>().AS(tableName).Where(it => it.ID == id).ToList();
SqlSDT SDTdata = new SqlSDT();
SDTdata.ID = id;
SDTdata.data = TToJson2(data);
//SDTdata.data = TToBytes(data);
if (query != null&& query.Count!=0)//已經存在的進行更新
{
ret = UpdataTable(tableName, SDTdata)?(short)0 : (short)-1;
}
else//不存在的 插入
{
ret = Insertable(tableName,SDTdata) ? (short)0 : (short)-1;
}
var query2 = db.Queryable<SqlSDT>().AS(tableName).ToList();
}
catch (Exception ex)
{
ret = -1;
}
return ret;
}
public short Write<T>(string tableName, string id, List<T> data)
{
short ret = -1;
try
{
var BaseTables = db.DbMaintenance.GetTableInfoList();
if (!HadTable(tableName))
{
//CreateTable<SqlSDT>(tableName);
db.MappingTables.Add(typeof(SqlSDT).Name, tableName);
db.CodeFirst.InitTables(typeof(SqlSDT));
}
int CurID = int.Parse(id);
SqlSDT SDTdata = new SqlSDT();
foreach (var item in data)
{
var query = db.Queryable<SqlSDT>().AS(tableName).Where(it => it.ID == id).ToList();
SDTdata.ID = CurID.ToString();
SDTdata.data = TToJson2(item);
if (query != null && query.Count != 0)//已經存在的進行更新
{
ret = UpdataTable(tableName, SDTdata) ? (short)0 : (short)-1;
}
else//不存在的 插入
{
ret = Insertable(tableName, SDTdata) ? (short)0 : (short)-1;
}
CurID++;
}
var query2 = db.Queryable<SqlSDT>().AS(tableName).ToList();
}
catch (Exception ex)
{
ret = -1;
}
return ret;
}
public short Close()
{
if (db != null)
{
try
{
db.Close();
}
catch (Exception ex)
{
return -1;
}
}
return 0;
}
#endregion
#region ******序列化及資料轉換相關*******
public string TToJson<T>(T data)
{
//Json.NET序列化
return JsonConvert.SerializeObject(data);
}
public string TToJson2<T>(T data)
{
var jsonSetting = new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore };
//Json.NET序列化
return JsonConvert.SerializeObject(data, Formatting.Indented, jsonSetting);
}
public short JsonToT<T>(string Data, ref T data)
{
short ret = 0;
try
{
data = JsonConvert.DeserializeObject<T>(Data);//反序列化
}
catch (Exception ex)
{
ret = -1;
}
return ret;
}
public short JsonToTList<T>(List<SqlSDT> sqlData, ref List<T> data)
{
short ret = 0;
try
{
foreach (SqlSDT item in sqlData)
{
T temp = JsonConvert.DeserializeObject<T>(item.data);//反序列化;
data.Add(temp);
}
}
catch (Exception ex)
{
ret = -1;
}
return ret;
}
/// <summary>
/// 二進位制轉泛型
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="bytes"></param>
/// <returns></returns>
public T BytesToT<T>(byte[] bytes)
{
using (var ms = new System.IO.MemoryStream())
{
ms.Write(bytes, 0, bytes.Length);
var bf = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
ms.Position = 0;
var x = bf.Deserialize(ms);
return (T)x;
}
}
/// <summary>
/// 泛型轉二進位制
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="obj"></param>
/// <returns></returns>
public byte[] TToBytes<T>(T obj)
{
var bf = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
using (var ms = new System.IO.MemoryStream())
{
bf.Serialize(ms, obj);
return ms.ToArray();
}
}
/ <summary>
/ 將Sql通用資料型別轉換為指定型別
/ </summary>
/ <typeparam name="T">需要轉換的資料型別</typeparam>
/ <param name="sqlData">通用資料型別</param>
/ <returns></returns>
//public T SqlSDT2T<T>(SqlSDT sqlData)
//{
// T data = BytesToT<T>(sqlData.data);
// return data;
//}
/ <summary>
/ 將Sql通用資料型別List轉換為指定型別List
/ </summary>
/ <typeparam name="T">需要轉換的資料型別</typeparam>
/ <param name="sqlData">通用資料型別List</param>
/ <returns></returns>
//public List<T> SqlSDT2T<T>(List<SqlSDT> sqlData)
//{
// List<T> data = new List<T>();
// foreach (SqlSDT item in sqlData)
// {
// T temp = BytesToT<T>(item.data);
// data.Add(temp);
// }
// return data;
//}
#endregion
#region *********Sqlite操作相關*********
public void ClaerDB(string tablename)
{
db.Deleteable<SqlSDT>().AS(tablename).Where(it => it.ID != "").ExecuteCommand();
}
/// <summary>
/// 當前資料庫是否存在對應表
/// </summary>
/// <param name="TableName">需要查詢的表名</param>
/// <returns></returns>
public bool HadTable(string TableName)
{
bool ret = false;
var BaseTables = db.DbMaintenance.GetTableInfoList();
foreach (var item in BaseTables)
{
if (item.Name == TableName|| item.Name == TableName.ToLower())
{
ret = true;
}
}
return ret;
}
/// <summary>
/// 利用已有實列型別建立表
/// </summary>
/// <typeparam name="T">實列型別</typeparam>
/// <param name="TableName">表名字</param>
public void CreateTable<T>(string TableName)
{
db.MappingTables.Add(typeof(T).Name, TableName);
db.CodeFirst.InitTables(typeof(T));
}
/// <summary>
/// 刪除指點表
/// </summary>
/// <param name="db">SQLite連結</param>
/// <param name="TableName">需要刪除的表名</param>
/// <returns></returns>
public string DelectTable(string TableName)
{
string ret = "";
if (!HadTable(TableName))
{
ret= "Table:" + TableName + " not find!!";
}
else
{
db.DbMaintenance.DropTable(TableName);//刪除表
}
return ret;
}
/// <summary>
/// 更新表資料
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="updateObj">通用資料型別</param>
/// <returns></returns>
public bool UpdataTable(string TableName, SqlSDT updateObj)
{
bool ret = false;
try
{
var result = db.Updateable<SqlSDT>(updateObj).AS(TableName).ExecuteCommand();//update single
ret = true;
}
catch (Exception ex)
{
ret = false;
}
return ret;
}
/// <summary>
/// 更新表資料
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="updateObjs">通用資料型別List</param>
/// <returns></returns>
public bool UpdataTable(string TableName, List<SqlSDT> updateObjs)
{
bool ret = false;
try
{
var result = db.Updateable<SqlSDT>(updateObjs).AS(TableName).ExecuteCommand();//update List<Class>
ret = true;
}
catch (Exception ex)
{
ret = false;
}
return ret;
}
/// <summary>
/// 插入資料
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="updateObj">通用資料型別</param>
/// <returns></returns>
public bool Insertable(string TableName, SqlSDT updateObj)
{
bool ret = false;
try
{
var result = db.Insertable<SqlSDT>(updateObj).AS(TableName).ExecuteCommand();//Insert Class
ret = true;
}
catch (Exception ex)
{
ret = false;
}
return ret;
}
/// <summary>
/// 插入資料
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="updateObj">通用資料型別</param>
/// <returns></returns>
public bool Insertable(string TableName, List<SqlSDT> updateObj)
{
bool ret = false;
try
{
var result = db.Insertable<SqlSDT>(updateObj).AS(TableName).ExecuteCommand();//Insert List<Class>
ret = true;
}
catch (Exception ex)
{
ret = false;
}
return ret;
}
/// <summary>
/// 在指定表中通過ID獲取資料
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="id">通用資料ID</param>
/// <returns></returns>
public SqlSDT FindDataByID(String TableName, string id)
{
SqlSDT data = new SqlSDT();
try
{
var query = db.Queryable<SqlSDT>().AS(TableName).Where(it => it.ID == id).ToList();
if (query.Count > 0)
{
data = query[0];
return data;
}
else
{
Console.WriteLine("TableName:" + TableName + " not find ID=" + id + " Data");
return null;
}
}
catch (Exception ex)
{
Console.WriteLine("Error:"+ ex.Message + " TableName:" + TableName + " ID=" + id);
return null;
}
}
/// <summary>
/// 在指定表中通過ID獲取資料List
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="id">通用資料ID</param>
/// <returns></returns>
public List<SqlSDT> FindDataListByID(String TableName, string id)
{
List<SqlSDT> data = new List<SqlSDT>();
try
{
var query = db.Queryable<SqlSDT>().AS(TableName).Where(it => it.ID == id).ToList();
if (query.Count > 0)
{
foreach (var item in query)
{
data.Add(item);
}
return data;
}
else
{
Console.WriteLine("TableName:" + TableName + " not find ID=" + id + " Data");
return null;
}
}
catch (Exception ex)
{
Console.WriteLine("Error:" + ex.Message + " TableName:" + TableName + " ID=" + id);
return null;
}
}
#endregion
}
注意事項及各個Sql安裝包
在使用MySql時需注意更新NuGet中的MySql.Data 避免連線字元不相容引發報錯
以上都已經測試過
使用資料庫：
MySql：
連結: https://pan.baidu.com/s/1MLwgynXrscRIzV_EtrGIBQ 提取碼: wrp2
SqlServer:
連結: https://pan.baidu.com/s/10STRYD0gKFN_yZI33dtC5A 提取碼: iq29
相關文章
- .NET SAAS 架構與設計 -SqlSugar ORM架構SqlSugarORM
- c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試C#SqlSugarORM框架Server
- .NET 通用多條件動態引數查詢方法 - SqlSugar ORMSqlSugarORM
- c# 國內外ORM 框架 dapper efcore sqlsugar freesql hisql sqlserver資料常規插入測試效能對比C#ORM框架APPSqlSugarServer
- C#使用sqlite-net搭建簡易的ORMC#SQLiteORM
- SQLite體系架構SQLite架構
- NET8 ORM 使用AOT SqlSugarORMSqlSugar
- 如何將 Dapper 換成 SqlSugar ORMAPPSqlSugarORM
- NX二次開發-NX連線SqlServer資料庫(增刪改查)C#版SQLServer資料庫C#
- 企業應用架構研究系列十三：整合EFCore&Dapper 通用ORM框架EFDapper應用架構APPORM框架
- .NET 5 ORM 八大實用技巧 乾貨 - SqlSugar ORMORMSqlSugar
- 基於C#開發 B/S架構的實驗室管理系統 雲LIS系統（MVC + SQLserver + Redis）C#架構MVCSQLServerRedis
- C#二次開發BIMFACE系列61 File Management檔案管理服務介面二次開發及實戰詳解C#
- .Net Core 3.0 MVC 中使用 SqlSugar ORM框架MVCSqlSugarORM框架
- BIMFACE二次開發SDK 開源C#版C#
- 重新架構：從 Redis 到 SQLite 效能提升架構RedisSQLite
- C# SqlSugar增刪改查C#SqlSugar
- 記SqlSugar ORM框架之找不到主鍵問題SqlSugarORM框架
- 關於orm的個人測試——SqlSugar與FreeSqlORMSqlSugar
- 比較爽的導航查詢 功能 - SqlSugar ORMSqlSugarORM
- MySql架構MySql架構
- 致敬社群開源介面平臺並二次開發
- sqlsugar freesql hisql 三個ORM框架效能測試對比SqlSugarORM框架
- 用 .SqlSugar ORM 來實現報表功能 .NET CORE /.NETSqlSugarORM
- ORM 創新解放勞動力 -SqlSugar 新功能介紹ORMSqlSugar
- NET5 ORM 六大新功能 - SqlSugar 5.0.2.7ORMSqlSugar
- SQLite之C介面SQLite
- [Java分散式架構實戰]Java+MySQL開發規範Java分散式架構MySql
- MySQL的架構MySql架構
- MySQL 高可用架構之 MMM 架構MySql架構
- ORM操作mysqlORMMySql
- [開發教程]第1講：Bootstrap使用者介面與互動架構boot架構
- 招聘golang開發&架構師Golang架構
- iOS開發-MVP架構模式iOSMVP架構模式
- Golang IM架構聊天開發Golang架構
- Monkey二次開發 -- Monkey jar包構建JAR
- C# 資料操作系列 - 14 深入探索SqlSugarC#SqlSugar
- C#連線SQLite的字串C#SQLite字串