Sybase ASE数据库使用ADO.net访问存储过程方法详解

本文为本人(iihero)原创，如若转载，请尊重个人劳动，务必注明原始出处。

在ASE里使用ado.net，基本上常见的有两种方式，一种是使用oledb方式(dotnet系统框架自带)，一种是直接使用ASE自带的adonet库来访问。想获取高性能，后者为佳。

而每种方式对store procedure的调用，也可以分两种模式，一种是显示指定这是调用存储过程，另一种是采取传统的存储过程调用文本方式："{call test_proc(?, ?)}"，这表示调用存储过程，带两个参数。

简单的SQL CUD操作或者SELECT查询，就不用介绍了，本文同样适合这些情形的处理。

下边是一个详细的示例，既有输出参数，同时又有输出的结果集。准备工作：
创建下述的表和示例存储过程：test_proc

create table spring.student
(
    sno int not null primary key, 
    sname varchar(32) not null, 
    sgender char(1) not null, 
    sbirth datetime not null,
    sage numeric(2) null, 
    sdept varchar(128) null
)
go
insert into student values(2007001, '李勇', 'M', '1987-9-1', null, 'CS')
insert into student values(2007002, '刘晨', 'F', '1988-10-22', null, 'IS')
insert into student values(2007003, '王敏', 'F', '1990-2-3', null, 'MA')
insert into student values(2007004, '张铁林', 'M', '1989-4-1', null, 'IS')
go
create proc spring.test_proc(@s_name char(30), @s_count int output) with recompile
as
select @s_count = count(a.sno) from spring.student a where a.sname = @s_name
select 'demo123'
go
declare @result int
exec spring.test_proc '李勇', @result output
select @result
go

示例程序：（总共4种访问方式）
重要提上边提到的两种调用模式。
还有一个是关于数据库的连接串：两个库的连接串是不一样的：
1. OLEDB
string oleString = @"Provider=Sybase.ASEOLEDBProvider;Initial Catalog=iihero;User Id=spring;Password=spring1;Server Name=sean-laptop;Network Protocol=Winsock;Server Port Address=5000" ;
这里我没有采用http://www.connectionstrings.com/上提到的oledb连接方式，如下：
Provider =Sybase.ASEOLEDBProvider; Srvr =myASEserver,5000; Catalog =myDataBase; User Id =myUsername; Password =myPassword;
而是直接采用机器名(Server Name)，端口(Server Port Address)，数据库(Initial Catalog), 用户(User Id), 密码(Password)的形式，一目了然，同时你也不用依赖于创建的数据源或者interfaces文件(sql.ini)

2. AseDotNet
string adoNetString = @"Data Source=sean-laptop;Port=5000;UID=spring;PWD=spring1;Database=iihero;";
这种方式比较简单，机器名(Data Source), 端口(Port)，用户名(UID), 密码(PWD)，数据库(Database)
也是含义非常明确。

不过，值得说明的是，本文采用的是AseClient的第二个版本：即$SYBASE\DataAccess\ADONET\dll \Sybase.AdoNet2.AseClient.dll而不是Sybase.Data.AseClient.dll。因为第二个版本的实现更容易使 用。
Sybase.Data.AseClient.dll中都是直接实现接口类IDbConnection, IDbCommand之类，而第二个版本则是继承抽象类DbCoonnection, DbCommand。

实际上ASE的连接串还是非常简明的。
下边是实际验证时使用的代码。读者可以根据自己的喜好选择任意一种方式。

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data.OleDb;
using System.Data;
using Sybase.Data.AseClient;
namespace adonetdemo
{
    class ASEDotNetDemo
    {
        DbConnection _conn;
        DbConnection _adoNetConn;
        Sybase.Data.AseClient.AseConnection conn;
        string oleString = @"Provider=Sybase.ASEOLEDBProvider;Initial Catalog=iihero;User Id=spring;Password=spring1;Server Name=sean-laptop;Network Protocol=Winsock;Server Port Address=5000";
        string adoNetString = @"Data Source=sean-laptop;Port=5000;UID=spring;PWD=spring1;Database=iihero;";
        public void testOleDb()
        {
            try
            {
                _conn = new OleDbConnection(oleString);
                _conn.Open();
                DbCommand cmd = _conn.CreateCommand();
                cmd.CommandText = "test_proc";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@s_name";
                param.DbType = System.Data.DbType.String;
                param.Direction = System.Data.ParameterDirection.Input;
                param.Value = "李勇";
                cmd.Parameters.Add(param);
                DbParameter param2 = cmd.CreateParameter();
                param2.ParameterName = "@s_count";
                param2.DbType = System.Data.DbType.Int32;
                param2.Direction = System.Data.ParameterDirection.Output;
                cmd.Parameters.Add(param2);
                DbDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {
                    Console.WriteLine(rs.GetString(0));
                }
                rs.Close();
                // int count = cmd.ExecuteNonQuery(); // if there is no resultset
                // output param2 
                Console.WriteLine("@s_count = " + param2.Value);
                
                cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message + "\n" + ex.InnerException);
            }
        }
        public void testOleDb2()
        {
            try
            {
                _conn = new OleDbConnection(oleString);
                _conn.Open();
                DbCommand cmd = _conn.CreateCommand();
                cmd.CommandText = "{ call test_proc(?, ?) }";
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@s_name";
                param.DbType = System.Data.DbType.String;
                param.Direction = System.Data.ParameterDirection.Input;
                param.Value = "李勇";
                cmd.Parameters.Add(param);
                DbParameter param2 = cmd.CreateParameter();
                param2.ParameterName = "@s_count";
                param2.DbType = System.Data.DbType.Int32;
                param2.Direction = System.Data.ParameterDirection.Output;
                cmd.Parameters.Add(param2);
                DbDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {
                    Console.WriteLine(rs.GetString(0));
                }
                rs.Close();
                // int count = cmd.ExecuteNonQuery(); // if there is no resultset
                // output param2 
                Console.WriteLine("@s_count = " + param2.Value);
                cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message + "\n" + ex.InnerException);
            }
        }
        // use traditional call mode, like: "{ call test_proc(?, ?) }"
        public void testAseAdoDotNet2()
        {
            try
            {
                _conn = new AseConnection(adoNetString);
                _conn.Open();
                // method 1: DbCommand cmd = new AseCommand("{ call test_proc(?, ?) }", (AseConnection)_conn);
                // or:
                DbCommand cmd = _conn.CreateCommand();
                cmd.CommandText = "{ call test_proc(?, ?) }";
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@s_name";
                param.DbType = System.Data.DbType.String;
                param.Direction = System.Data.ParameterDirection.Input;
                param.Value = "李勇";
                cmd.Parameters.Add(param);
                DbParameter param2 = cmd.CreateParameter();
                param2.ParameterName = "@s_count";
                param2.DbType = System.Data.DbType.Int32;
                param2.Direction = System.Data.ParameterDirection.Output;
                cmd.Parameters.Add(param2);
                DbDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {
                    Console.WriteLine(rs.GetString(0));
                }
                rs.Close();
                // output param2 
                Console.WriteLine("@s_count = " + param2.Value);
                cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message + "\n" + ex.InnerException);
            }
        }
        public void testAseAdoDotNet()
        {
            try
            {
                _conn = new AseConnection(adoNetString);
                _conn.Open();
                DbCommand cmd = _conn.CreateCommand();
                cmd.CommandText = "test_proc";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@s_name";
                param.DbType = System.Data.DbType.String;
                param.Direction = System.Data.ParameterDirection.Input;
                param.Value = "李勇";
                cmd.Parameters.Add(param);
                DbParameter param2 = cmd.CreateParameter();
                param2.ParameterName = "@s_count";
                param2.DbType = System.Data.DbType.Int32;
                param2.Direction = System.Data.ParameterDirection.Output;
                cmd.Parameters.Add(param2);
                DbDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {
                    Console.WriteLine(rs.GetString(0));
                }
                rs.Close();
                // output param2 
                Console.WriteLine("@s_count = " + param2.Value);
                cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message + "\n" + ex.InnerException);
            }
        }
        static void Main(string[] args)
        {
            ASEDotNetDemo t = new ASEDotNetDemo();
            t.testOleDb();
            t.testOleDb2();
            t.testAseAdoDotNet();
            t.testAseAdoDotNet2();
        }
    }
}

输出结果：
demo123
@s_count = 1
demo123
@s_count = 1
demo123
@s_count = 1
demo123
@s_count = 1