ADO.NET中不同数据提供者所用参数格式如下：
Provider Named/Positional Parameter Marker
SqlClient Named @parmname
OracleClient Named :parmname (or parmname)
OleDb Positional ?
Odbc Positional ?
在DbCommand.CommandType=CommandType.Text时，DbParameter就要符合上述要求。如SqlParameter则要使用参数名来决定参数值，而oledb则是根据参数顺序来决定参数值。但是当DbCommand.CommandType=CommandType.StoreProcedure时，则可以采用采用占位符或名字来确定参数值。
这种不统一使得要写出跨越各种数据提供者的程序变得复杂了。同时要实现统一的数据层当然还要考虑不同数据库的sql语句区别（所以要尽量采用标准的sql语句）。本来ADO.net提供了很好的类结构(DbConnection, DbCommand等以Db开头的类)来实现这种很重要的功能，可是这些原因使得我们还是要自己写不少代码。
下面是一段示例代码：
//create proc TestParam
//@courseid varchar(50)
//as
//select * from course where courseid=@courseid
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text;
namespace ADOParameterTest
...{
class Program
...{
enum ConnectionType : byte ...{ Sql, Ole };
static string sqlConnectionString = "Data Source=CTEC-LLY;Initial Catalog=examdb;Integrated Security=True";
static string oleConnectionString = "
rovider=sqloledb;Data Source=CTEC-LLY;Initial Catalog=examdb;User Id=saassword=sa"; static DbConnection GetConnection(ConnectionType ct)...{
if (ct == ConnectionType.Sql)
最新回复
sitedir (2008-4-17 09:02:23)
return new SqlConnection(sqlConnectionString);
}
else
...{
return new OleDbConnection(oleConnectionString);
}
}
static void TestOleCommandText()
...{
Console.WriteLine("TestOleCommandText");
using(DbConnection con = GetConnection(ConnectionType.Ole))
...{
con.Open();
DbCommand cmd = con.CreateCommand();
//cmd.CommandText = "declare @courseid as varchar(50); set @courseid=’0001’;select * from course where courseid=@courseid";
cmd.CommandText = "select * from course where courseid=?";
//odbc&oledb只能是?做占位符，这时跟参数名无关跟参数顺序是相关的
DbParameter p = cmd.CreateParameter();
p.ParameterName = "courseid";
p.Value = "0001";
cmd.Parameters.Add(p);
//下面被注释的参数设置方式也是对的，可以注释上面四行而采用下面的语句
//记住参数名是没有意义的，顺序决定参数
//cmd.Parameters.Add(new OleDbParameter("@courseid", "0001"));
ShowResult(cmd.ExecuteReader());
}
}
static void TestOleStoredProcedure()
sitedir (2008-4-17 09:02:51)
Console.WriteLine("TestOleStoredProcedure");
using (DbConnection con = GetConnection(ConnectionType.Ole))
...{
con.Open();
DbCommand cmd = con.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "TestParam";
Console.WriteLine("用@param做占位符");
//odbc&oledb只能是?做占位符，这时跟参数名无关跟参数顺序是相关的
DbParameter p = cmd.CreateParameter();
p.ParameterName = "@courseid";
p.Value = "0001";
cmd.Parameters.Add(p);
//下面被注释的参数设置方式也是对的，可以注释上面四行而采用下面的语句
//记住参数名是没有意义的，顺序决定参数
//cmd.Parameters.Add(new OleDbParameter("@courseid", "0001"));
ShowResult(cmd.ExecuteReader());
Console.WriteLine("用?做占位符");
p.ParameterName = "";
ShowResult(cmd.ExecuteReader());
}
}
static void TestSqlCommandText()
...{
Console.WriteLine("TestSqlCommandText");
using (DbConnection con = GetConnection(ConnectionType.Sql))
...{
sitedir (2008-4-17 09:03:19)
DbCommand cmd = con.CreateCommand();
//sql只能是@param做占位符，跟oledb相反，跟参数名有关跟参数顺序无关
//oracle只能用:param做占位符，跟参数名有关跟参数顺序无关
cmd.CommandText = "select * from course where courseid=@courseid";
DbParameter p = cmd.CreateParameter();
p.ParameterName = "@courseid";
p.Value = "0001";
cmd.Parameters.Add(p);
//下面被注释的参数设置方式也是对的，可以注释上面四行而采用下面的语句
//记住只有参数名有意义，顺序无关
//cmd.Parameters.Add(new OleDbParameter("@courseid", "0001"));
ShowResult(cmd.ExecuteReader());
}
}
static void TestSqlStoredProcedure()
...{
Console.WriteLine("TestSqlStoredProcedure");
using (DbConnection con = GetConnection(ConnectionType.Sql))
...{
con.Open();
DbCommand cmd = con.CreateCommand();
//sql只能是@param做占位符，跟oledb相反，跟参数名有关跟参数顺序无关
//oracle只能用:param做占位符，跟参数名有关跟参数顺序无关
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "TestParam";
Console.WriteLine("用@param做占位符");
sitedir (2008-4-17 09:03:50)
p.ParameterName = "@courseid";
p.Value = "0001";
cmd.Parameters.Add(p);
//下面被注释的参数设置方式也是对的，可以注释上面四行而采用下面的语句
//记住只有参数名有意义，顺序无关
//cmd.Parameters.Add(new OleDbParameter("@courseid", "0001"));
ShowResult(cmd.ExecuteReader());
Console.WriteLine("用?做占位符");
p.ParameterName = "";
ShowResult(cmd.ExecuteReader());
}
}
static void ShowResult(DbDataReader reader)
...{
int count = reader.FieldCount;
while(reader.Read())
...{
Console.WriteLine("-------------------------------");
for(int i=0; i<count; i++)
...{
Console.WriteLine(string.Format("Filed[{0}]={1}", i, reader.GetValue(i).ToString()));
}
}
reader.Close();
Console.WriteLine();
}
static void Main(string[] args)
...{
try
...{
TestOleCommandText();
sitedir (2008-4-17 09:04:25)
TestOleStoredProcedure();
TestSqlStoredProcedure();
}
catch (Exception ex)
...{
Console.WriteLine(ex.Message);
}
Console.ReadLine();
}
}
}