程式師世界 >> 數據庫知識 >> SyBase數據庫 >> SyBase綜合文章 >> Sybase ASE數據庫使用ADO.net訪問存儲過程方法詳解

Sybase ASE數據庫使用ADO.net訪問存儲過程方法詳解

編輯：SyBase綜合文章

本文為本人(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
{ //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
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