SqlDao 1.0.1

SqlDaoDemo

C# 操作数据库的DAO类库,CURD 操作不需要拼写SQl语句,支持事务操作，节约时间，提高开发效率，亲测 Mysql 、SQLite 好用。

获取

在nuget上添加对 SqlDao 的引用或搜索 SqlDao;

配制

以Wpf 桌面项目为例，通常我们是把数据库的连接字符串放到 app.config 文件中

```
  <connectionStrings>
    <add name="mysqlConn" connectionString="Database=test;Data Source=127.0.0.1;User Id=admin;Password=code@8888;pooling=false;CharSet=utf8;port=3306"/>
  </connectionStrings>
```

在代码内获取配制

```
  String connstr = ConfigurationManager.ConnectionStrings["mysqlConn"].ConnectionString.ToString();
```

使用

最基本的使用（不推荐）

  //增加一个用户
   User user = new User
    {
        Name = "WolfCode",
        Age = 27,
        Salary = (decimal)3900.90,
        IsDelete = 1,
        DeleteTime = DateTime.Now
    };

    String connstr = ConfigurationManager.ConnectionStrings["mysqlConn"].ConnectionString.ToString();
    MySqlHelper mySqlHelper = new MySqlHelper(connstr);
    int res = mySqlHelper.Insert(user);

每一次操作都会 重新连接数据库，所以不推荐。

推荐使用方式

在App.xaml.cs 中生成一个静态的变量，作为主要操作数据库的助手类。如果有多数据源，其它数据源的操作使用上面基本操作方式。
如果不是WPF项目那就找一个全局能访问的类中初始化即可。

```
 private static MySqlHelper mySqlHelper;
 public static MySqlHelper MainSqlHelper {
      get
      {

          if (mySqlHelper == null)
          {
              String connstr = ConfigurationManager.ConnectionStrings["mysqlConn"].ConnectionString.ToString();
              mySqlHelper = new MySqlHelper(connstr);
          }

          return mySqlHelper;
        }
    }
```

新增 用例

• 用例 1

 User user = new User
 {
     Name = "WolfCode",
     Age = 27,
     Salary = (decimal)3900.90,
 };
 int result = App.MainSqlHelper.Insert(user);

 if (result > 0)
 {
     Console.WriteLine("Insert successed");
 }
 else
 {
     Console.WriteLine("Insert failured");
 }

更新 用例

• 用例 1

User user = new User
 {
     Id = 5, //数据表中一定要有这条数据。否则修改失败
     Name = "Wolf123",
     Age = 27,
     Salary = (decimal)3900.90,
 };
 int result = App.MainSqlHelper.Update(user);
 if (result > 0)
 {
     Console.WriteLine("Update successed");
 }
 else
 {
     Console.WriteLine("Update failured");
 }

删除 用例

• 用例 1

 string sql = " delete from user where id >5 ;";          
 int res = App.MainSqlHelper.Delete(sql);
 if(res > 0)
 {
     Console.WriteLine($"成功删除 {res} 条数据");
 }
 else
 {
     Console.WriteLine($"删除失败");
 }

• 用例 2

 User user = new User { Id = 7 };
 int rows = App.MainSqlHelper.Delete(user);

 // int rows = App.MainSqlHelper.Delete(user,isTrueDelete:false); //isTrueDelete:false不删除数据，把字段is_delete 改为 1,默认为true
 if (rows > 0)
 {
     Console.WriteLine($"成功删除");
 }
 else
 {
     Console.WriteLine($"删除失败");
 }

查询 用例

• 用例 1

//查询 User表中的所有记录
string sql = SqlBuilder.GetSelectSql(TableName.user.ToString());
List<User> users = App.MainSqlHelper.Select<User>(sql);
Console.WriteLine("--datas : " + users.Count);

• 用例 2

//查询 User表中 di > 5 并且 is_delete =0 的所有记录的 id 和 name 字段
 string sql1 = SqlBuilder.GetSelectSql(TableName.user.ToString(), fields: "id ,name", conditon: "id >5 and is_delete =0");
 List<User> users1 = App.MainSqlHelper.Select<User>(sql1);
 Console.WriteLine("--datas : " + users1.Count);

• 用例 3

//查询User表中的 10 条数据，按id 倒序排序
string sql2 = SqlBuilder.GetSelectSql(TableName.user.ToString(), fields: null, conditon: null, groupBy: null, having: null, orderBy: "id desc", limit: 10, offset: 0);
List<User> users2 = App.MainSqlHelper.Select<User>(sql2);
Console.WriteLine("--datas : " + users2.Count);

• 用例 4 多表查询需要手动拼写Sql语句

//多表查询需要手动拼写Sql语句
String joinSql = "SELECT u.* ,r.money,r.remark FROM record as r JOIN `user` as u where u.is_delete = 0 and u.id = r.user_id";
List<Object> os = App.MainSqlHelper.Select<Object>(joinSql);

*其它的查询类似，依照操作

查询或者更新 用例

  User user = new User
  {
      Id = 9, //数据表中有这条数据则修改否则增加
      Name = "Wolf123",
      Age = 27,
      Salary = (decimal)3900.90,
  };
  int res = App.MainSqlHelper.InsertOrUpdate(user);
  if (res > 0)
  {
      Console.WriteLine("InsertOrUpdate successed");
  }
  else
  {
      Console.WriteLine("InsertOrUpdate failured");
  }

事务操作 用例

//模拟发工资的操作，既要改变账户的金额，又要记录流水，需要用到事务。

    int userId = 1;
    User user = App.MainSqlHelper.FindById<User>(userId);
    if (user != null)
    {
        String asql = SqlBuilder.GetSelectSql(TableName.account.ToString(), null, "user_id = " + user.Id);
        Account account = App.MainSqlHelper.Find<Account>(asql);
        string accSql;
        if (account != null)
        {
            account.Money += user.Salary;
            accSql = SqlBuilder.GetUpdateSql(account);
        }
        else
        {
            account = new Account
            {
                UserId = user.Id,
                Money = user.Salary
            };
            accSql = SqlBuilder.GetInsertSql(account);
        }
        Record record = new Record
        {
            Id = 1,
            UserId = user.Id,
            Money = user.Salary,
            UpdateTime = DateTime.Now,
            Remark = "发11 月份工资"
        };
        string insertsql = SqlBuilder.GetInsertSql(record);
        string[] sqls = new string[] { accSql, insertsql };

        // statr transation
        int res = App.MainSqlHelper.TransactionExecute(sqls);

        if (res > 0)
        {
            Console.WriteLine("操作成功！");
        }
        else
        {
            Console.WriteLine("操作失败！");
        }
     }

详细文档 (https://github.com/crazywolfcode/SqlDaoDemo/blob/master/README.md)