.NET Core Dapper操作Sybase数据库

1、Dapper中Sybase的连接字符串

  <add key="SybaseConnStr" value="Data Source=192.168.31.163; Port=5000; Database=pubs2; Uid=sa; Pwd=liangliang;Charset=iso_1;"/>

2、需要引用的dll

1）通过Nuget引用

Nuget搜索Sybase，找到合适版本的AseClient。

2）在Sybase安装目录

在D:\SAP\DataAccess或D:\SAP\DataAccess64目录找到对应版本的。

3、创建数据库连接

public static IDbConnection SybaseConn
{
    get
    {
        try
        {
            return new AseConnection(System.Configuration.ConfigurationManager.AppSettings["SybaseConnStr"]);
        }
        catch (Exception ex)
        {
            logger.Error(ex.Message);
        }
        return null;
    }
}

4、增删改查

//增
using (IDbConnection conn = SybaseConn)
{
    Users user = new Users();
    user.Name = "CNKI";
    user.Age = 38;
    string sqlCommandText = @"INSERT INTO USERS(Name,Age)VALUES(@Name,@Age)";
    int result = conn.Execute(sqlCommandText, user);
}
//批量增
using (IDbConnection conn = SybaseConn)
{
    List<Users> list = new List<Users>();
    for (int i = 0; i < 5; i++)
    {
        Users user = new Users();
        user.Name = "CNKI";
        user.Age = 38;
        list.Add(user);
    }
    string sqlCommandText = @"INSERT INTO USERS(Name,Age)VALUES(@Name,@Age)";
    int result = conn.Execute(sqlCommandText, list);
}
//删
using (IDbConnection conn = SybaseConn)
{
    Users user = new Users();
    user.ID = 1;
    string sqlCommandText = @"DELETE FROM USERS WHERE ID=@ID";
    int result = conn.Execute(sqlCommandText, user);
}
//改
using (IDbConnection conn = SybaseConn)
{
    Users user = new Users();
    user.ID = 2;
    user.Name = "CNKI";
    user.Age = 18;
    string sqlCommandText = @"UPDATE USERS SET Age=@Age WHERE ID=@ID";
    int result = conn.Execute(sqlCommandText, user);
}
//查
using (IDbConnection conn = SybaseConn)
{
    string sqlCommandText = @"SELECT * FROM USERS WHERE ID=@ID";
    Users user = conn.Query<Users>(sqlCommandText, new  { ID=2 }).FirstOrDefault();
}

5、Dapper中的DynamicParameters动态参数集合类

using (IDbConnection conn = SybaseConn)
 {
     string sqlCommandText = @"SELECT * FROM USER WHERE ID=@ID";
     var p = new DynamicParameters();
     p.Add("@ID", 1);
     User user2 = conn.Query<User>(sqlCommandText,p).FirstOrDefault();
 }

6、Dapper事物操作

[TestMethod]
public void TestDapperTransaction()
{
     using (IDbConnection conn = SybaseConn)
     {
         conn.Open();
         IDbTransaction trans = conn.BeginTransaction();
         int row = conn.Execute(@"update t set name='cjavapy.com' where id=@id", new { id = 3 }, trans);
         row += conn.Execute("delete from t where id=@id", new { id = 5 }, trans);
         for (int i = 0; i < 100; i++)
         {
             conn.Execute(@"insert t(id, name) values (@id, @name)", new { id = i, name = "cjavapy.com" + i });
         }
         trans.Commit();
      }
}