陶新新同学问起带参数的ADO.NET怎么写,为什么要带参数?
带参数的一个重要作用是安全,如防止SQL注入;再就是代码上更加规范,逻辑上更加清晰……
using
System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
///使用带参数的查询,基本方法如下:
///写SQL语句,参数用@引导;有几个@参数就有几个 SqlParameter 对象;将所有的SqlParameter对象添加到SqlCommand对象 的Parameters集合。
namespace snippetConsole
{
class Program
{
static void Main( string [] args)
{
string connect = " Data Source=.\\sqlexpress; Initial Catalog=Northwind ; Integrated Security = true; " ;
string select = " select CustomerID,City from Customers where CustomerID =@customer_id and City=@city " ;
// ('ANTON','AROUT','BERGS','BLAUS')";London Aachen Nantes London
SqlConnection cn = new SqlConnection(connect);
SqlCommand cmd = new SqlCommand(select, cn);
SqlParameter paramCustID = new SqlParameter( " @customer_id " , " ALFKI " ); // 此处ALFKI可以是外部的文本框等
SqlParameter paramCity = new SqlParameter( " @city " , " Berlin " ); // 此处的Berlin可以是外部文本框控件获得数据
cmd.Parameters.Add(paramCustID);
cmd.Parameters.Add(paramCity);
cn.Open();
SqlDataReader drCustomer = cmd.ExecuteReader();
if (drCustomer.HasRows)
{
while (drCustomer.Read())
{
Console.WriteLine( " CustomerID:{0}\tCompanyName:{1} " ,
drCustomer.GetString( 0 ), drCustomer.GetString( 1 ));
}
}
cn.Close();
Console.ReadKey();
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
///使用带参数的查询,基本方法如下:
///写SQL语句,参数用@引导;有几个@参数就有几个 SqlParameter 对象;将所有的SqlParameter对象添加到SqlCommand对象 的Parameters集合。
namespace snippetConsole
{
class Program
{
static void Main( string [] args)
{
string connect = " Data Source=.\\sqlexpress; Initial Catalog=Northwind ; Integrated Security = true; " ;
string select = " select CustomerID,City from Customers where CustomerID =@customer_id and City=@city " ;
// ('ANTON','AROUT','BERGS','BLAUS')";London Aachen Nantes London
SqlConnection cn = new SqlConnection(connect);
SqlCommand cmd = new SqlCommand(select, cn);
SqlParameter paramCustID = new SqlParameter( " @customer_id " , " ALFKI " ); // 此处ALFKI可以是外部的文本框等
SqlParameter paramCity = new SqlParameter( " @city " , " Berlin " ); // 此处的Berlin可以是外部文本框控件获得数据
cmd.Parameters.Add(paramCustID);
cmd.Parameters.Add(paramCity);
cn.Open();
SqlDataReader drCustomer = cmd.ExecuteReader();
if (drCustomer.HasRows)
{
while (drCustomer.Read())
{
Console.WriteLine( " CustomerID:{0}\tCompanyName:{1} " ,
drCustomer.GetString( 0 ), drCustomer.GetString( 1 ));
}
}
cn.Close();
Console.ReadKey();
}
}
}