SQL语句参数化（2）DATAGRID数据源绑定

最新推荐文章于 2023-07-08 03:21:52 发布

weixin_30391339

最新推荐文章于 2023-07-08 03:21:52 发布

阅读量58

点赞数

CC 4.0 BY-SA版权

文章标签： ui

原文链接：http://www.cnblogs.com/zss1100/archive/2007/03/04/663423.html

本文介绍了使用VB.NET实现SQL参数化查询的方法，通过具体示例展示了如何在Web应用程序中利用参数化查询来提高安全性及效率。特别是针对DataGrid控件的数据绑定过程进行了详细说明。

在 SQL语句参数化（1）插入数据中，介绍了一般的参数化方法，同理可以应用一些到修改，查询，删除等操作中
下面介绍在DATAGRID等的绑定数据源时的参数化

WebForm1.aspx.vb

Imports System.Text

Public Class WebForm1
Inherits System.Web.UI.Page

#Region " Web 窗体设计器生成的代码 "

'该调用是 Web 窗体设计器所必需的。
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
    Protected WithEvents Button1 As System.Web.UI.WebControls.Button
    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

    '注意: 以下占位符声明是 Web 窗体设计器所必需的。
    '不要删除或移动它。
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: 此方法调用是 Web 窗体设计器所必需的
        '不要使用代码编辑器修改它。
        InitializeComponent()
    End Sub

#End Region
    Dim myconn As New Conn
    Dim mydata As New Getdata
    Dim sql As StringBuilder = New StringBuilder

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        mydata.myconn = New SqlClient.SqlConnection
        mydata.myconn.ConnectionString = myconn.stringconnection
        If Not Page.IsPostBack Then
        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            sql.Append("select chanpin_id,chanpin_name,chanpin_xiaotu,chanpin_putongjia")
            sql.Append(" from chanpin where ")
            sql.Append(" chanpin_name=@chanpin_name")

            ' sql.Append("select chanpin_id,chanpin_name,chanpin_xiaotu,chanpin_putongjia")
            ' sql.Append(" from chanpin where ")
            ' sql.Append(" chanpin_name like '%'+ @chanpin_name +'%'")   ' 用LIKE时，一定要用+号不能用&，+号之前为’（单引号）而不是“
            Dim mypt As SqlClient.SqlParameter() = New SqlClient.SqlParameter() { _
            New SqlClient.SqlParameter("@chanpin_name", SqlDbType.VarChar, 50)}

mypt(0).Value = TextBox1.Text

DataGrid1.DataSource = mydata.getdataview(sql.ToString, "chanpin", mypt)
DataGrid1.DataBind()

End Sub
End Class

Getdata.vb

Public Class Getdata

Public myconn As SqlClient.SqlConnection

   Public Function getdataview(ByVal sql As String, ByVal strTable As String, ByVal myPt As SqlClient.SqlParameter()) As DataView
        Dim myDataAdapter As SqlClient.SqlDataAdapter
        Dim myDataSet As DataSet
        myDataAdapter = New SqlClient.SqlDataAdapter(sql, myconn)
        If Not IsNothing(myPt) Then
            Dim mypt1 As SqlClient.SqlParameter
            For Each mypt1 In myPt
                myDataAdapter.SelectCommand.Parameters.Add(mypt1)
            Next
        End If
        myDataSet = New DataSet
        myDataAdapter.Fill(myDataSet, strTable)
        Dim myTable As DataTable = myDataSet.Tables(strTable)
        getdataview = New DataView(myTable)
    End Function
End Class

转载于:https://www.cnblogs.com/zss1100/archive/2007/03/04/663423.html