SQL语句很强大,很多时候需要拼凑SQL语句来完成某些功能,比如执行查询时候

SELECT * FROM Customers where CustomerID  like  '%inputCustomerId%' or CompanyName like '%inputCompanyName%'

假如有意使用途中字符串,会导致所有表中记录都能查询出来,为了避免这样的情况,需要使用参数化查询

1.首先想到使用store procedure

		Dim objCommand As OleDb.OleDbCommand

                objCommand = New OleDb.OleDbCommand

                With objCommand

                    .CommandText = "Test_StoreProcedure"

                    .Connection = connDbConnection

                    .CommandType = CommandType.StoredProcedure

                    .Parameters.AddWithValue("@CustomerId", customerId)

		    .Parameters.AddWithValue("@CompanyName", companyName)

		    .Parameters.AddWithValue("@ContactName", contactName)

                End With

                Dim ds As DataSet

                Dim objAdaptor As OleDb.OleDbDataAdapter

                objAdaptor = New OleDb.OleDbDataAdapter

                ds = New DataSet

                objCommand.Connection = connDbConnection

                objAdaptor.SelectCommand = objCommand

                objAdaptor.Fill(ds)

可是在store procedure 内部,依然需要拼凑出sql语句来返回结果,等于什么作用都没起

2.利用DataView

	    Dim dv As DataView = New DataView(dt)

            dv.FindRows("customerid like '%" + customerId + "'%")

可以看到这种方式仍然是拼凑条件字符串,不会起作用

3.利用CommandType.Text的OleDbCommand不需要结果集可以用objCommand.ExecuteNonQuery()

		Dim objCommand As OleDb.OleDbCommand                

		objCommand = New OleDb.OleDbCommand                

		With objCommand                   

		    .CommandText = "select * from customer where customerid like ? or companyname like ? or contactname like ?"                   

		    .Connection = connDbConnection                    .CommandType = CommandType.Text

                    .Parameters.AddWithValue("@CustomerId", customerId)

		    .Parameters.AddWithValue("@CompanyName", companyName)

		    .Parameters.AddWithValue("@ContactName", contactName)

                End With

                Dim ds As DataSet

                Dim objAdaptor As OleDb.OleDbDataAdapter

                objAdaptor = New OleDb.OleDbDataAdapter

                ds = New DataSet

                objCommand.Connection = connDbConnection

                objAdaptor.SelectCommand = objCommand

                objAdaptor.Fill(ds)

4.直接利用OleDbDataAdapter的SelectCommand.Parameters,返回dataset

  		Dim sql As String = "select * from customer where customerid like ? or companyname like ? or contactname like ?"

		Dim objAdaptor As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, connDb)
		objAdaptor.SelectCommand.Parameters.AddWithValue("@CustomerId", "%" + customerId + "%")

                objAdaptor.SelectCommand.Parameters.AddWithValue("@CompanyName", "%" + companyName + "%")

                objAdaptor.SelectCommand.Parameters.AddWithValue("@ContactName", "%" + contactName + "%")

		objAdaptor.Fill(ds)

在数据库端看到的执行语句会包含有参数的类型,如下

exec sp_executesql N'select * from customer where customerid LIKE @P1 and companyname LIKE @P2  and inputDate >= @P3 ',N'@P1 nvarchar(2),@P2 nvarchar(2),@P3 datetime',N'%%',N'%%','1900-01-02 00:00:00'

5.利用System.Data.Linq

DataContext db = new DataContext();

IQueryable<Customer> custs = db.Customers;

custs = custs.Where(o=>o.CustomerId.Contains(customerId));
custs = custs.Where(o=>o.CompanyName.Contains(companyName));
custs = custs.Where(o=>o.ContactName.Contains(contactName0);

如果传进去的customerId包含like等语句,则这个linq语句不能成功解析出对应的sql语句,会报错

6.很多时候数据库太复杂,需要用到各种数据库函数的时候,我们既想要利用sql语句来拼装,又不愿意冒着参数注入的危险,可以先用IQueryable<>或者IEnumerable<>来得到结果后再用where进行过滤

IEnumerable<Customer> custs = db.ExecuteQuery<Customer>("select top 5 * from customer");

custs = custs.Where(o=&gt;o.CustomerId.Contains(customerId));