C#如何使用SqlSugar操作MySQL/SQL Server数据库

一. 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>
{
     private static Singleton<T> _instance = null;

     public Singleton() { }

     public static Singleton<T> Instance()
     {
        if (_instance == null)
             _instance = new Singleton<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; }
        }


    }

五. 使用

MySqlCNHelper cn = new MySqlCNHelper();

bool blRet = await cn.InsertRecordProModel(model);

var List1 = await cn.QueryDataList();

var List2 = await cn.QueryAllotModelList(model);

bool blRet = await cn.QueryUserModelIsExists(model);

六、结果如下