Bootstrap
最近换了公司,现在这个公司需要使用Sybase数据库,本文分享一下.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();
}
}