使用SqlSugar操作MySQL/SQL Server数据库

一、框架简介

SqlSugar 是一款 老牌 .NET开源ORM框架，由果糖大数据科技团队维护和更新 ，开箱即用最易上手的ORM 

优点 ：【生态丰富】【高性能】【超简单】 【功能全面】 【多库兼容】【适合产品】

 

二. SqlSugar 连接MySQL数据库

 public class MySqlCNHelper : Singleton<MySqlCNHelper>
 {
        public static SqlSugarClient CnDB;
        public void InitDB() 
        {
            //--------------------MySQL--------------------
            CnDB = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = "server=127.0.0.1;uid=root;pwd=zhumengxy;database=fish",
                DbType = DbType.MySql,
                IsAutoCloseConnection = true,
                InitKeyType = InitKeyType.Attribute 
            });
 
            //--------------------SQL Server--------------------
            //mDB = new SqlSugarClient(new ConnectionConfig()
            //{
            //    ConnectionString = "Data Source=your_server_name;Initial                            
            //    Catalog=your_database_name;User                 
            //          ID=your_username;Password=your_password",
             //DbType = DbType.SqlServer,
            //    IsAutoCloseConnection = true,  // 查询完毕后自动关闭连接
            //})
 
            CnDB.Aop.OnLogExecuting = (sql, pars) =>
            {
                  Console.WriteLine(sql + "\r\n" +
                    mDB.Utilities.SerializeObject(pars.ToString()));
                  Console.WriteLine();
            };
 
     }
}
 
 

三.  数据库操作

//插入
public async Task<bool> InsertRecordProModel(RecordProModel recordPro) => await CnDB.Insertable(recordPro).ExecuteCommandAsync() > 0;
 
 
//查询1
public async Task<List<T>> QueryDataList<T>() => await CnDB.Queryable<T>().ToListAsync();
 
 
//查询2
public async Task<List<AllotModel>> QueryAllotModelList(AllotModel allot) => await CnDB.Queryable<AllotModel>().Where(t => t.EsamNo == allot.EsamNo || t.ProdOrderNo == allot.ProdOrderNo).ToListAsync();
 
 
//查询3
public async Task<bool> QueryUserModelIsExists(UserModel user) => await CnDB.Queryable<UserModel>().Where(t => t.UserName == user.UserName && t.PassWord == user.PassWord).AnyAsync();
 
 
//删除
public async Task<bool> DeleteRecordProModelBW(int id) => await CnDB.Deleteable<RecordProModel>().Where(t => t.ID == id).ExecuteCommandAsync() > 0;
 
 
//更新
public async void UpdateData(int id ,string esamNo)
{
    
    AllotModel userData = await CnDB.Queryable<AllotModel>().Where(t => t.ID == id).FirstAsync();
    if (userData != null)
    {
        userData.EsamNo = esamNo;
        await CnDB.Updateable(userData).ExecuteCommandAsync();
    }
}
 
 

四.  Singleton类

public class Singleton<T> where T : class, new()
{
    private static T _instance;
 
    private static readonly object syslock = new object();
 
    public static T Instance()
    {
        if (_instance == null)
        {
            lock (syslock)
            {
                if (_instance == null)
                {
                    _instance = new T();
                }
            }
        }
        return _instance;
    }
}
 
 
 

四、AllotModel 类

 public class AllotModel
    {
        private int Id;
        public int ID
        {
            get { return Id; }
            set { Id = value;}
        }
 
        private string esamNo = "";
        public string EsamNo
        {
            get { return esamNo; }
            set { esamNo = value;}
        }
 
        private AppModel appInfo = new AppModel(); 
        public AppModel AppInfo
        {
            get { return appInfo; }
            set { appInfo = value; }
        }
 
        private string cipherTxt = ""; 
        public string CipherTxt
        {
            get { return cipherTxt; }
            set { cipherTxt = value;}
        }
 
        private string repetitions; 
        public string Repetitions
        {
            get { return repetitions; }
            set { repetitions = value;}
        }
 
        private string prodOrderNo = "";
        public string ProdOrderNo
        {
            get { return prodOrderNo; }
            set { prodOrderNo = value; }
        }
 
 
    }

六、使用

bool blRet = await MySqlCNHelper.Instance().InsertRecordProModel(model);

var List1 = await  MySqlCNHelper.Instance().QueryDataList();

var List2 = await  MySqlCNHelper.Instance().QueryAllotModelList(model);

bool blRet = await  MySqlCNHelper.Instance().QueryUserModelIsExists(model);