SqlSugar库操作mysql数据库方法
添加引用:
MySql.Data.dll,SqlSugar.dll
增删改查
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace SqlSugar_MysqlTest
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button_mysql_test_Click(object sender, EventArgs e)
{
DBContext<Sugar_Get_Info_Class> aa= DBContext<Sugar_Get_Info_Class>.OpDB();
Expression < Func<Sugar_Get_Info_Class, bool>> cd = null;
string insert_str = "sn_202207043";
// List<Sugar_Get_Info_Class> list=aa.GetList(cd=f=>f.id=="10009"&&f.lot=="123456"); //多条件查询
//查询
List<Sugar_Get_Info_Class> list = aa.GetList(cd = f => f.sn_box== insert_str);
for (int i=0;i<list.Count;i++)
{
// MessageBox.Show(list[i].id + "," + list[i].sn_box);
}
if(list.Count>0)
{
// MessageBox.Show("已经存在物流码"+ insert_str);
// return;
}
//by txwtech
else
{
//增加
// aa.Insert(new Sugar_Get_Info_Class { id = "10011", lot = "", sn_box = insert_str });
}
//删除
aa.Delete(cd = f => f.id == "10008");
//public virtual bool Update(Expression<Func<T, T>> columns, Expression<Func<T, bool>> whereExpression);
// Update(Expression < Func < T, T >> 执行的动作,返回的结果类型
//Update(Expression<Func<T, bool>> 执行的动作,返回的结果类型
//修改
aa.Update(p=>new Sugar_Get_Info_Class {base_data="99.0",lot="LA123456" },cd=f=>f.id=="10009");
}
}
}
DBContext.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
//using AgvDisPatch.Config;
//by txwtech
using SqlSugar;
namespace SqlSugar_MysqlTest
{
public class DBContext<T> where T : class, new()
{
public SqlSugarClient Db;
/// <summary>
/// 单列模式
/// </summary>
private static DBContext<T> mSingle = null;
public static DBContext<T> GetInstance()
{
if (mSingle == null)
mSingle = new DBContext<T>();
return mSingle;
}
/// <summary>
/// 修改后的代码
/// </summary>
/// <returns></returns>
public static DBContext<T> OpDB()
{
DBContext<T> dbcontext_t = new DBContext<T>();
dbcontext_t.Db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = "database='" + "txw_oqa_db" + "';Data Source = '" + "127.0.0.1" + "'; User Id = '" + "root" + "'; pwd='" + "87958868" + "';charset='utf8';pooling=true",
//DbType = SqlSugar.DbType.MySql,
//IsAutoCloseConnection = true,
//InitKeyType = InitKeyType.Attribute
// ConnectionString = ConfigFile.DataConnString,
DbType = SqlSugar.DbType.MySql,//我这里使用的是Mysql数据库
IsAutoCloseConnection = true,//自动关闭连接
InitKeyType = InitKeyType.Attribute
});
return dbcontext_t;
}
protected DBContext()
{ //通过这个可以直接连接数据库
Db = new SqlSugarClient(new ConnectionConfig()
{
// "database='" + "return" + "';Data Source = '" + "127.0.0.1" + "'; User Id = '" + "root" + "'; pwd='" + "root" + "';charset='utf8';pooling=true",
//可以在连接字符串中设置连接池pooling=true;表示开启连接池
//eg:min pool size=2;max poll size=4;表示最小连接池为2,最大连接池是4;默认是100
ConnectionString = "database='" + "txw_oqa_db" + "';Data Source = '" + "127.0.0.1" + "'; User Id = '" + "root" + "'; pwd='" + "87958868" + "';charset='utf8';pooling=true",
// ConnectionString = ConfigFile.DataConnString,
DbType = SqlSugar.DbType.MySql,//我这里使用的是Mysql数据库
IsAutoCloseConnection = true,//自动关闭连接
InitKeyType = InitKeyType.Attribute
});
}
public void Dispose()
{
if (Db != null)
{
Db.Dispose();
}
}
public SimpleClient<T> CurrentDb { get { return new SimpleClient<T>(Db); } }
/// <summary>
/// 获取所有
/// </summary>
/// <returns></returns>
public virtual List<T> GetList()
{
return CurrentDb.GetList();
}
/// <summary>
/// 根据表达式查询
/// </summary>
/// <returns></returns>
public virtual List<T> GetList(Expression<Func<T, bool>> whereExpression)
{
//db.Queryable<Student>().Where(it => it.Name != null).ToList()
return CurrentDb.GetList(whereExpression);
}
/// <summary>
/// 根据表达式查询分页
/// </summary>
/// <returns></returns>
public virtual List<T> GetPageList(Expression<Func<T, bool>> whereExpression, PageModel pageModel)
{
return CurrentDb.GetPageList(whereExpression, pageModel);
}
/// <summary>
/// 根据表达式查询分页并排序
/// </summary>
/// <param name="whereExpression">it</param>
/// <param name="pageModel"></param>
/// <param name="orderByExpression">it=>it.id或者it=>new{it.id,it.name}</param>
/// <param name="orderByType">OrderByType.Desc</param>
/// <returns></returns>
public virtual List<T> GetPageList(Expression<Func<T, bool>> whereExpression, PageModel pageModel, Expression<Func<T, object>> orderByExpression = null, OrderByType orderByType = OrderByType.Asc)
{
return CurrentDb.GetPageList(whereExpression, pageModel, orderByExpression, orderByType);
}
/// <summary>
/// 根据主键查询
/// </summary>
/// <returns></returns>
public virtual List<T> GetById(dynamic id)
{
return CurrentDb.GetById(id);
}
/// <summary>
/// 根据主键删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public virtual bool Delete(dynamic id)
{
if (string.IsNullOrEmpty(id.ObjToString))
{
Console.WriteLine(string.Format("要删除的主键id不能为空值!"));
}
return CurrentDb.Delete(id);
}
/// <summary>
/// 根据实体删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public virtual bool Delete(T data)
{
if (data == null)
{
Console.WriteLine(string.Format("要删除的实体对象不能为空值!"));
}
return CurrentDb.Delete(data);
}
/// <summary>
/// 根据主键删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public virtual bool Delete(dynamic[] ids)
{
if (ids.Count() <= 0)
{
Console.WriteLine(string.Format("要删除的主键ids不能为空值!"));
}
return CurrentDb.AsDeleteable().In(ids).ExecuteCommand() > 0;
}
/// <summary>
/// 根据表达式删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public virtual bool Delete(Expression<Func<T, bool>> whereExpression)
{
return CurrentDb.Delete(whereExpression);
}
/// <summary>
/// 根据实体更新,实体需要有主键
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public virtual bool Update(T obj)
{
if (obj == null)
{
Console.WriteLine(string.Format("要更新的实体不能为空,必须带上主键!"));
}
return CurrentDb.Update(obj);
}
/// <summary>
///批量更新
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public virtual bool Update(List<T> objs)
{
if (objs.Count <= 0)
{
Console.WriteLine(string.Format("要批量更新的实体不能为空,必须带上主键!"));
}
return CurrentDb.UpdateRange(objs);
}
public virtual bool Update(Expression<Func<T, T>> columns, Expression<Func<T, bool>> whereExpression)
{
return CurrentDb.Update(columns,whereExpression);
//public virtual bool Update(Expression<Func<T, T>> columns, Expression<Func<T, bool>> whereExpression);
}
/// <summary>
/// 插入
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public virtual bool Insert(T obj)
{
return CurrentDb.Insert(obj);
}
/// <summary>
/// 批量
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public virtual bool Insert(List<T> objs)
{
return CurrentDb.InsertRange(objs);
}
}
}
创建一个类:Sugar_Get_Info_Class.cs
每个类文件对一个数据库的表
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SqlSugar_MysqlTest
{
[SugarTable("lot_weight_measurement_record")]//数据库表名
class Sugar_Get_Info_Class
{
// class DeviceDataMode : BasicRx
// {
public string id { get; set; }
public string lot { get; set; }
public string base_data { set; get; }
public string sn_box { set; get; }
public string weight { set; get; }
public string exec_date { set; get; }
public string back1 { set; get; }
public string back2 { set; get; }
public string back3 { set; get; }
// }
}
}
mysql数据库的创建方法参考:https://www.cnblogs.com/txwtech/category/1973054.html
工程代码:https://download.csdn.net/download/txwtech/85898643?spm=1001.2014.3001.5503
解压密码:
txwtech blog is wonderful