这个问题以前总是遇到,但是每次都是找到解决方法就没有具体找原因。昨天再次遇到参数问题,才想着一定要搞清楚了。
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开头的类)来实现这种很重要的功能,可是这些原因使得我们还是要自己写不少代码。
下面是一段示例代码:
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 = " Provider=sqloledb;Data Source=CTEC-LLY;Initial Catalog=examdb;User Id=sa;Password=sa " ;
static DbConnection GetConnection(ConnectionType ct){
if (ct == ConnectionType.Sql)
{
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()
{
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))
{
con.Open();
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做占位符 " );
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 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();
TestSqlCommandText();
TestOleStoredProcedure();
TestSqlStoredProcedure();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadLine();
}
}
}
// @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 = " Provider=sqloledb;Data Source=CTEC-LLY;Initial Catalog=examdb;User Id=sa;Password=sa " ;
static DbConnection GetConnection(ConnectionType ct){
if (ct == ConnectionType.Sql)
{
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()
{
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))
{
con.Open();
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做占位符 " );
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 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();
TestSqlCommandText();
TestOleStoredProcedure();
TestSqlStoredProcedure();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadLine();
}
}
}