如何参数化VB.NET代码的查询 [英] How do I parameterized the queries of VB.NET code
问题描述
出于安全考虑,我需要参数化我的查询。
我尝试过:
<私有子Save_Click(发件人作为对象,e作为EventArgs)处理Save.Click
MyNonQuery(String.Format(insert into boyscout_pos.tblproducts(&!)
pre Purchase_Invoice,Product_Code,Product_Name,Category,Size,Product_Price,Selling_Price,Qty_Stock)值('{0}','{1}','{2}','{3}','{4}','{ 5}','{6}','{7}'),txtpurchaseinvoice.Text,txtproductcode.Text,txtproductname.Text,txtproductcategory.Text,TextBoxSize.Text,txtproductprice.Text,txtsellingprice.Text,txtproductquantity.Text) )
MessageBox.Show(成功保存)
TxtSearchCode.Clear()
cboproductsize.Items.Clear()
txtproductcode.Clear( )
txtproductname.Clear()
txtproductcategory.Clear()
txtproductprice.Clear()
txtsellingprice.Clear()
TextBoxSize.Clear()
cboproductsize.Text =
txtproductquantity.Clear()
End Sub
Public Sub MyNonQuery(ByVal SQCommand As String)
Dim conn As New
MySqlConnection(server = localhost; userid = root; password =; database = boyscout_pos)
Dim SQLCMD As New MySqlCommand(SQCommand,conn)
conn.Open ()
SQLCMD.ExecuteNonQuery()
conn.Close()
End Sub
你想得到这样的代码:
使用 con 作为 新 MySqlConnection(strConnect)
con.Open()
使用 com 作为 新 MySqlCommand( INSERT INTO myTable(myColumn1,myColumn2)VALUES (@ C1,@ C2),con)
com.Parameters.AddWithValue( @ C1,myValueForColumn1)
com.Parameters.AddWithValue( @ C2,myValueForColumn2)
com.ExecuteNonQuery()
结束 使用
结束 使用这意味着你的方式不会work - 你不能只是将一个字符串传递给Generic函数。
如果您使用的是Visual Studio 2015或更高版本,并且面向.NET 4.6或更高版本,那么像这样的东西将工作:
公共 共享 功能 CreateCommand( ByVal connection As IDbCon nection, ByVal commandText As FormattableString,可选 ByVal commandType As CommandType = CommandType.Text) As IDbCommand
Dim 结果 As IDbCommand = connection.CreateCommand()
Dim parameterNames As 新列表( 字符串)(commandText.ArgumentCount)
对于 每个参数作为 对象 在 commandText.GetArguments()
Dim parameterName 作为 字符串 = @ p& parameterNames.Count
parameterNames.Add(parameterName)
Dim p As IDbDataParameter = result.CreateParameter()
p.ParameterName = parameterName
p.Value = parameter
result.Parameters.Add(p)
下一步
result.CommandText = 字符串 .Format(commandText.Format,parameterNames.ToArray())
result.CommandType = commandType
返回结果
结束 功能
公共 Sub MyNonQuery ( ByVal commandText As FormattableString,可选 < span class =code-keyword> ByVal commandTy pe As CommandType = CommandType.Text)
使用连接作为 新 MySqlConnection( server = localhost; userid = root; password =; database = boyscout_pos)
使用命令 As IDbCommand = CreateCommand(connection,commandText,commandType)
connection.Open()
command.ExecuteNonQuery()
End 使用
结束 使用
结束 Sub
...
MyNonQuery(
插入boyscout _pos.tblproducts(Purchase_Invoice,Product_Code,Product_Name,Category,Size,Product_Price,Selling_Price,Qty_Stock)值({txtpurchaseinvoice.Text},{txtproductcode.Text},{txtproductname.Text},{txtproductcategory.Text},{TextBoxSize.Text },{txtproductprice.Text},{txtsellingprice.Text},{txtproductquantity.Text}))
这使用字符串插值 [ ^ ]将命令文本和参数作为一个对象传递。将其作为 FormattableString [ ^ ],它能够提取参数值并正确传递它们,而不是将它们连接到命令文本中。
(注意:我没有发明这个想法 - 我在文章中看到了它我觉得它在CodeProject上的某个地方。不幸的是,我没有保留链接,我现在找不到它。如果作者发现这个并希望我添加一个链接,请告诉我。)
I need to parameterized my queries for safety purposes.
What I have tried:
Private Sub Save_Click(sender As Object, e As EventArgs) Handles Save.Click MyNonQuery(String.Format("insert into boyscout_pos.tblproducts (Purchase_Invoice,Product_Code,Product_Name,Category,Size,Product_Price,Selling_Price,Qty_Stock) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", txtpurchaseinvoice.Text, txtproductcode.Text, txtproductname.Text, txtproductcategory.Text, TextBoxSize.Text, txtproductprice.Text, txtsellingprice.Text, txtproductquantity.Text)) MessageBox.Show("Successfully Saved") TxtSearchCode.Clear() cboproductsize.Items.Clear() txtproductcode.Clear() txtproductname.Clear() txtproductcategory.Clear() txtproductprice.Clear() txtsellingprice.Clear() TextBoxSize.Clear() cboproductsize.Text = "" txtproductquantity.Clear() End Sub Public Sub MyNonQuery(ByVal SQCommand As String) Dim conn As New MySqlConnection("server=localhost;userid=root;password=;database=boyscout_pos") Dim SQLCMD As New MySqlCommand(SQCommand, conn) conn.Open() SQLCMD.ExecuteNonQuery() conn.Close() End Sub
You want to end up with code like this:
Using con As New MySqlConnection(strConnect) con.Open() Using com As New MySqlCommand("INSERT INTO myTable (myColumn1, myColumn2) VALUES (@C1, @C2)", con) com.Parameters.AddWithValue("@C1", myValueForColumn1) com.Parameters.AddWithValue("@C2", myValueForColumn2) com.ExecuteNonQuery() End Using End UsingWhich means that the way you are doing it will not work - you can't just pass a string to a "Generic" function.
If you're using Visual Studio 2015 or higher, and targeting .NET 4.6 or later, something like this will work:
Public Shared Function CreateCommand(ByVal connection As IDbConnection, ByVal commandText As FormattableString, Optional ByVal commandType As CommandType = CommandType.Text) As IDbCommand Dim result As IDbCommand = connection.CreateCommand() Dim parameterNames As New List(Of String)(commandText.ArgumentCount) For Each parameter As Object In commandText.GetArguments() Dim parameterName As String = "@p" & parameterNames.Count parameterNames.Add(parameterName) Dim p As IDbDataParameter = result.CreateParameter() p.ParameterName = parameterName p.Value = parameter result.Parameters.Add(p) Next result.CommandText = String.Format(commandText.Format, parameterNames.ToArray()) result.CommandType = commandType Return result End Function Public Sub MyNonQuery(ByVal commandText As FormattableString, Optional ByVal commandType As CommandType = CommandType.Text) Using connection As New MySqlConnection("server=localhost;userid=root;password=;database=boyscout_pos") Using command As IDbCommand = CreateCommand(connection, commandText, commandType) connection.Open() command.ExecuteNonQuery() End Using End Using End Sub ... MyNonQuery(
"insert into boyscout_pos.tblproducts (Purchase_Invoice, Product_Code, Product_Name, Category, Size, Product_Price, Selling_Price, Qty_Stock) values ({txtpurchaseinvoice.Text}, {txtproductcode.Text}, {txtproductname.Text}, {txtproductcategory.Text}, {TextBoxSize.Text}, {txtproductprice.Text}, {txtsellingprice.Text}, {txtproductquantity.Text})")
This uses string interpolation[^] to pass the command text and parameters as one object. By passing it as a FormattableString[^], it is able to extract the parameter values and pass them properly, rather than concatenating them into the command text.
(NB: I didn't invent this idea - I saw it in an article, which I think was somewhere on CodeProject. Unfortunately, I didn't keep the link, and I can't find it now. If the author spots this and wants me to add a link, let me know.)
这篇关于如何参数化VB.NET代码的查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!