.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();
        }
    }