2006年11月12日 星期三 天气晴 Last Modify: Jan 25, 2008
==================================================================================
今日主攻使用oledbCommand中使用Parameter增加参数。由于有sqlCommand.add(sqlParameters……)的模糊的例子,在黑暗中探索MSDN吧……
这样可以一定程度上避免使用字符串连接带来的SQL注入的问题,虽然安全方面没有银弹,但总比原来更安全可靠。
OLE DB.NET Framework 数据提供程序使用标有问号 (?) 的定位参数,而不使用命名参数。
string queryString = "SELECT * FROM Table1 WHERE Field1 LIKE ?";
OleDbCommand command = new OleDbCommand(queryString, connection);
command.Parameters.Add("@p1", OleDbType.Char, 3).Value = "a";
OleDbDataReader reader = command.ExecuteReader();
另一个"大型"的例子:
下面的示例通过 OleDbDataAdapter 中的 OleDbParameterCollection 集合创建 OleDbParameter 的多个实例。这些参数用于从数据源中选择数据并将数据放在 DataSet 中。此示例假定已经使用适当的架构、命令和连接创建了 DataSet 和 OleDbDataAdapter。
public DataSet GetDataSetFromAdapter(
DataSet dataSet, string connectionString, string queryString)
{
using (OleDbConnection connection =
new OleDbConnection(connectionString))
{
OleDbDataAdapter adapter =
new OleDbDataAdapter(queryString, connection);
// Set the parameters.
adapter.SelectCommand.Parameters.Add(
"@CategoryName", OleDbType.VarChar, 80).Value = "toasters";
adapter.SelectCommand.Parameters.Add(
"@SerialNum", OleDbType.Integer).Value = 239;
// Open the connection and fill the DataSet.
try
{
connection.Open();
adapter.Fill(dataSet);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// The connection is automatically closed when the
// code exits the using block.
}
return dataSet;
}
主函数:
using System;
using System.Data;
using System.Data.OleDb;
class Class1
{
static void Main()
{
// string x = "Provider=SQLOLEDB;Data Source=(local);Integrated Security=SSPI;Initial Catalog=Northwind";
}
public DataSet GetDataSetFromAdapter(
DataSet dataSet, string connectionString, string queryString)
{
using (OleDbConnection connection =
new OleDbConnection(connectionString))
{
OleDbDataAdapter adapter =
new OleDbDataAdapter(queryString, connection);
// Set the parameters.
adapter.SelectCommand.Parameters.Add(
"@CategoryName", OleDbType.VarChar, 80).Value = "toasters";
adapter.SelectCommand.Parameters.Add(
"@SerialNum", OleDbType.Integer).Value = 239;
// Open the connection and fill the DataSet.
try
{
connection.Open();
adapter.Fill(dataSet);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// The connection is automatically closed when the
// code exits the using block.
}
return dataSet;
}
没想到从String转换成int总是不成功,换成int?也不行……但是在添加参数的时候声明参数类型为integer程序居然正常运行了……
string conStr = ConfigurationManager.AppSettings["NewsCon"];
conStr = conStr + Server.MapPath(ConfigurationManager.AppSettings["NewsDB"]);
string cmdStr = "SELECT ID, title, content, creater, creatTime, lastEditTime, form FROM News WHERE (isPassed = 1) AND (ID = ?)";
OleDbConnection Conn = new OleDbConnection(conStr);
OleDbCommand Cmd = new OleDbCommand(cmdStr, Conn);
Cmd.Parameters.Add("@ID", OleDbType.Integer).Value = newsID;
Rev 1.0 Create Document Nov 12, 2006
Rev 1.1 Added some comments Jan 25, 2008