思路简介
利用现有的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
4341
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
