ADO.NET参数详解

这个问题以前总是遇到但是每次都是找到解决方法就没有具体找原因昨天再次遇到参数问题才想着一定要搞清楚了

ADONET中不同数据提供者所用参数格式如下

Provider Named/Positional Parameter Marker

SqlClient Named @parmname

OracleClient Named parmname (or parmname)

OleDb Positional ?

Odbc Positional ?

在DbCommandCommandType=CommandTypeText时DbParameter就要符合上述要求如SqlParameter则要使用参数名来决定参数值而oledb则是根据参数顺序来决定参数值但是当DbCommandCommandType=CommandTypeStoreProcedure时则可以采用采用占位符或名字来确定参数值

这种不统一使得要写出跨越各种数据提供者的程序变得复杂了同时要实现统一的数据层当然还要考虑不同数据库的sql语句区别（所以要尽量采用标准的sql语句）本来提供了很好的类结构(DbConnection DbCommand等以Db开头的类)来实现这种很重要的功能可是这些原因使得我们还是要自己写不少代码

下面是一段示例代码

//create proc TestParam

//@courseid varchar()

//as

//select * from course where courseid=@courseid

using System;

using SystemCollectionsGeneric;

using SystemDataCommon;

using SystemDataOleDb;

using SystemDataSqlClient;

using SystemText;

namespace ADOParameterTest

{

class Program

{

enum ConnectionType : byte { Sql Ole };

static string sqlConnectionString = Data Source=CTECLLY;Initial Catalog=examdb;Integrated Security=True;

static string oleConnectionString = Provider=sqloledb;Data Source=CTECLLY;Initial Catalog=examdb;User Id=sa;Password=sa;

static DbConnection GetConnection(ConnectionType ct){

if (ct == ConnectionTypeSql)

{

return new SqlConnection(sqlConnectionString);

}

else

{

return new OleDbConnection(oleConnectionString);

}

}

static void TestOleCommandText()

{

ConsoleWriteLine(TestOleCommandText);

using(DbConnection con = GetConnection(ConnectionTypeOle))

{

conOpen();

DbCommand cmd = conCreateCommand();

//cmdCommandText = declare @courseid as varchar(); set @courseid=;select * from course where courseid=@courseid;

cmdCommandText = select * from course where courseid=?;

//odbc&oledb只能是?做占位符这时跟参数名无关跟参数顺序是相关的

DbParameter p = cmdCreateParameter();

pParameterName = courseid;

pValue = ;

cmdParametersAdd(p);

//下面被注释的参数设置方式也是对的可以注释上面四行而采用下面的语句

//记住参数名是没有意义的顺序决定参数

//cmdParametersAdd(new OleDbParameter(@courseid ));

ShowResult(cmdExecuteReader());

}

}

static void TestOleStoredProcedure()

{

ConsoleWriteLine(TestOleStoredProcedure);

using (DbConnection con = GetConnection(ConnectionTypeOle))

{

conOpen();

DbCommand cmd = conCreateCommand();

cmdCommandType = SystemDataCommandTypeStoredProcedure;

cmdCommandText = TestParam;

ConsoleWriteLine(用@param做占位符);

//odbc&oledb只能是?做占位符这时跟参数名无关跟参数顺序是相关的

DbParameter p = cmdCreateParameter();

pParameterName = @courseid;

pValue = ;

cmdParametersAdd(p);

//下面被注释的参数设置方式也是对的可以注释上面四行而采用下面的语句

//记住参数名是没有意义的顺序决定参数

//cmdParametersAdd(new OleDbParameter(@courseid ));

ShowResult(cmdExecuteReader());

ConsoleWriteLine(用?做占位符);

pParameterName = ;

ShowResult(cmdExecuteReader());

}

}

static void TestSqlCommandText()

{

ConsoleWriteLine(TestSqlCommandText);

using (DbConnection con = GetConnection(ConnectionTypeSql))

{

conOpen();

DbCommand cmd = conCreateCommand();

//sql只能是@param做占位符跟oledb相反跟参数名有关跟参数顺序无关

//oracle只能用:param做占位符跟参数名有关跟参数顺序无关

cmdCommandText = select * from course where courseid=@courseid;

DbParameter p = cmdCreateParameter();

pParameterName = @courseid;

pValue = ;

cmdParametersAdd(p);

//下面被注释的参数设置方式也是对的可以注释上面四行而采用下面的语句

//记住只有参数名有意义顺序无关

//cmdParametersAdd(new OleDbParameter(@courseid ));

ShowResult(cmdExecuteReader());

}

}

static void TestSqlStoredProcedure()

{

ConsoleWriteLine(TestSqlStoredProcedure);

using (DbConnection con = GetConnection(ConnectionTypeSql))

{

conOpen();

DbCommand cmd = conCreateCommand();

//sql只能是@param做占位符跟oledb相反跟参数名有关跟参数顺序无关

//oracle只能用:param做占位符跟参数名有关跟参数顺序无关

cmdCommandType = SystemDataCommandTypeStoredProcedure;

cmdCommandText = TestParam;

ConsoleWriteLine(用@param做占位符);

DbParameter p = cmdCreateParameter();

pParameterName = @courseid;

pValue = ;

cmdParametersAdd(p);

//下面被注释的参数设置方式也是对的可以注释上面四行而采用下面的语句

//记住只有参数名有意义顺序无关

//cmdParametersAdd(new OleDbParameter(@courseid ));

ShowResult(cmdExecuteReader());

ConsoleWriteLine(用?做占位符);

pParameterName = ;

ShowResult(cmdExecuteReader());

}

}

static void ShowResult(DbDataReader reader)

{

int count = readerFieldCount;

while(readerRead())

{

ConsoleWriteLine();

for(int i=; i<count; i++)

{

ConsoleWriteLine(stringFormat(Filed[{}]={} i readerGetValue(i)ToString()));

}

}

readerClose();

ConsoleWriteLine();

}

static void Main(string[] args)

{

try

{

TestOleCommandText();

TestSqlCommandText();

TestOleStoredProcedure();

TestSqlStoredProcedure();

}

catch (Exception ex)

{

ConsoleWriteLine(exMessage);

}

ConsoleReadLine();

}

}

}