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> 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);

六、结果如下

七、下载地址：使用SqlSugar操作MySQL/SQLServer数据库资源-CSDN文库

八、完结