Using objCmd As New SqlCommand(sqlTmp, objConn)
With objCmd
.Parameters.Clear()
.Parameters.AddWithValue("@RequestErrorID", ddlError.SelectedValue.ToString)
.Parameters.AddWithValue("@RequestEmpID", sqlTmp = " Select ID FROM TBLOGIN WHERE USERNAME = '" & Request.Cookies("myUsername")("ckUsername") & "' ")
.Parameters.AddWithValue("@RequestDate", SqlDbType.DateTime).Value = DateTime.Now
.ExecuteNonQuery()
End With
End Using
Tag : .NET, Web (ASP.NET), Win (Windows App), VB.NET, VS 2015 (.NET 4.x)
Protected Sub ViewData()
'*** DataTable ***'
Dim dtAdapter As SqlDataAdapter
Dim dt As New DataTable
strSQL = "SELECT * FROM files WHERE FilesID = @sFilesID "
dtAdapter = New SqlDataAdapter(strSQL, objConn)
objCmd = dtAdapter.SelectCommand
objCmd.Parameters.Add("@sFilesID", SqlDbType.Int).Value = Request.QueryString("FilesID")
dtAdapter.Fill(dt)
If dt.Rows.Count > 0 Then
Me.ImgPic.ImageUrl = "ViewImg.aspx?FilesID=" & dt.Rows(0)("FilesID")
Me.txtName.Text = dt.Rows(0)("Name")
End If
dt = Nothing
End Sub
ASP.NET SQL Server BLOB Binary Data and Parameterized Query
Partial Public Class tblOne2UpLinkTableAdapter
Inherits GComponentModel.Component
Private WithEvents _adapter As OleDb.OleDbDataAdapter
Private _connection As OleDb.OleDbConnection
Private _transaction As OleDb.OleDbTransaction
Private _commandCollection() As OleDb.OleDbCommand
Private _clearBeforeFill As Boolean
Public Sub New()
MyBase.New()
Me.ClearBeforeFill = True
End Sub
Protected Friend ReadOnly Property Adapter() As OleDb.OleDbDataAdapter
Get
If (Me._adapter Is Nothing) Then
Me.InitAdapter()
End If
Return Me._adapter
End Get
End Property
Friend Property Connection() As OleDb.OleDbConnection
Get
If (Me._connection Is Nothing) Then
Me.InitConnection()
End If
Return Me._connection
End Get
Set(value As OleDb.OleDbConnection)
Me._connection = value
If (Not (Me.Adapter.InsertCommand) Is Nothing) Then
Me.Adapter.InsertCommand.Connection = value
End If
If (Not (Me.Adapter.DeleteCommand) Is Nothing) Then
Me.Adapter.DeleteCommand.Connection = value
End If
If (Not (Me.Adapter.UpdateCommand) Is Nothing) Then
Me.Adapter.UpdateCommand.Connection = value
End If
Dim i As Integer = 0
Do While (i < Me.CommandCollection.Length)
If (Not (Me.CommandCollection(i)) Is Nothing) Then
CType(Me.CommandCollection(i), OleDb.OleDbCommand).Connection = value
End If
i = (i + 1)
Loop
End Set
End Property
Friend Property Transaction() As OleDb.OleDbTransaction
Get
Return Me._transaction
End Get
Set(value As OleDb.OleDbTransaction)
Me._transaction = value
Dim i As Integer = 0
Do While (i < Me.CommandCollection.Length)
Me.CommandCollection(i).Transaction = Me._transaction
i = (i + 1)
Loop
If ((Not (Me.Adapter) Is Nothing) _
AndAlso (Not (Me.Adapter.DeleteCommand) Is Nothing)) Then
Me.Adapter.DeleteCommand.Transaction = Me._transaction
End If
If ((Not (Me.Adapter) Is Nothing) _
AndAlso (Not (Me.Adapter.InsertCommand) Is Nothing)) Then
Me.Adapter.InsertCommand.Transaction = Me._transaction
End If
If ((Not (Me.Adapter) Is Nothing) _
AndAlso (Not (Me.Adapter.UpdateCommand) Is Nothing)) Then
Me.Adapter.UpdateCommand.Transaction = Me._transaction
End If
End Set
End Property
Protected ReadOnly Property CommandCollection() As OleDb.OleDbCommand()
Get
If (Me._commandCollection Is Nothing) Then
Me.InitCommandCollection()
End If
Return Me._commandCollection
End Get
End Property
Public Property ClearBeforeFill() As Boolean
Get
Return Me._clearBeforeFill
End Get
Set(value As Boolean)
Me._clearBeforeFill = value
End Set
End Property
Private Sub InitAdapter()
Me._adapter = New OleDb.OleDbDataAdapter()
Dim tableMapping As Common.DataTableMapping = New Common.DataTableMapping()
tableMapping.SourceTable = "Table"
tableMapping.DataSetTable = "tblOne2UpLink"
tableMapping.ColumnMappings.Add("PK", "PK")
tableMapping.ColumnMappings.Add("Link", "Link")
tableMapping.ColumnMappings.Add("File", "File")
Me._adapter.TableMappings.Add(tableMapping)
Me._adapter.DeleteCommand = New OleDb.OleDbCommand()
Me._adapter.DeleteCommand.Connection = Me.Connection
Me._adapter.DeleteCommand.CommandText = "DELETE FROM `tblOne2UpLink` WHERE ((`PK` = ?))"
Me._adapter.DeleteCommand.CommandType = CommandType.Text
Me._adapter.DeleteCommand.Parameters.Add(New OleDb.OleDbParameter("Original_PK", OleDb.OleDbType.[Integer], 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "PK", DataRowVersion.Original, False, Nothing))
Me._adapter.InsertCommand = New OleDb.OleDbCommand()
Me._adapter.InsertCommand.Connection = Me.Connection
Me._adapter.InsertCommand.CommandText = "INSERT INTO `tblOne2UpLink` (`Link`, `File`) VALUES (?, ?)"
Me._adapter.InsertCommand.CommandType = CommandType.Text
Me._adapter.InsertCommand.Parameters.Add(New OleDb.OleDbParameter("Link", OleDb.OleDbType.LongVarWChar, 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "Link", DataRowVersion.Current, False, Nothing))
Me._adapter.InsertCommand.Parameters.Add(New OleDb.OleDbParameter("File", OleDb.OleDbType.LongVarWChar, 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "File", DataRowVersion.Current, False, Nothing))
Me._adapter.UpdateCommand = New OleDb.OleDbCommand()
Me._adapter.UpdateCommand.Connection = Me.Connection
Me._adapter.UpdateCommand.CommandText = "UPDATE `tblOne2UpLink` SET `Link` = ?, `File` = ? WHERE ((`PK` = ?))"
Me._adapter.UpdateCommand.CommandType = CommandType.Text
Me._adapter.UpdateCommand.Parameters.Add(New OleDb.OleDbParameter("Link", OleDb.OleDbType.LongVarWChar, 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "Link", DataRowVersion.Current, False, Nothing))
Me._adapter.UpdateCommand.Parameters.Add(New OleDb.OleDbParameter("File", OleDb.OleDbType.LongVarWChar, 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "File", DataRowVersion.Current, False, Nothing))
Me._adapter.UpdateCommand.Parameters.Add(New OleDb.OleDbParameter("Original_PK", OleDb.OleDbType.[Integer], 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "PK", DataRowVersion.Original, False, Nothing))
End Sub
Private Sub InitConnection()
Me._connection = New OleDb.OleDbConnection()
Me._connection.ConnectionString = Global.WindowsApplication1.My.MySettings.Default.One2UpLinkFileConnectionString
End Sub
Private Sub InitCommandCollection()
Me._commandCollection = New OleDb.OleDbCommand(0) {}
Me._commandCollection(0) = New OleDb.OleDbCommand()
Me._commandCollection(0).Connection = Me.Connection
Me._commandCollection(0).CommandText = "SELECT PK, Link, File FROM tblOne2UpLink"
Me._commandCollection(0).CommandType = CommandType.Text
End Sub
Public Overridable Overloads Function Fill(ByVal dataTable As One2UpLinkFileDataSet.tblOne2UpLinkDataTable) As Integer
Me.Adapter.SelectCommand = Me.CommandCollection(0)
If (Me.ClearBeforeFill = True) Then
dataTable.Clear()
End If
Dim returnValue As Integer = Me.Adapter.Fill(dataTable)
Return returnValue
End Function
Public Overridable Overloads Function GetData() As One2UpLinkFileDataSet.tblOne2UpLinkDataTable
Me.Adapter.SelectCommand = Me.CommandCollection(0)
Dim dataTable As One2UpLinkFileDataSet.tblOne2UpLinkDataTable = New One2UpLinkFileDataSet.tblOne2UpLinkDataTable()
Me.Adapter.Fill(dataTable)
Return dataTable
End Function
Public Overridable Overloads Function Update(ByVal dataTable As One2UpLinkFileDataSet.tblOne2UpLinkDataTable) As Integer
Return Me.Adapter.Update(dataTable)
End Function
Public Overridable Overloads Function Update(ByVal dataSet As One2UpLinkFileDataSet) As Integer
Return Me.Adapter.Update(dataSet, "tblOne2UpLink")
End Function
Public Overridable Overloads Function Update(ByVal dataRow As DataRow) As Integer
Return Me.Adapter.Update(New DataRow() {dataRow})
End Function
Public Overridable Overloads Function Update(ByVal dataRows() As DataRow) As Integer
Return Me.Adapter.Update(dataRows)
End Function
Public Overridable Overloads Function Delete(ByVal Original_PK As Integer) As Integer
Me.Adapter.DeleteCommand.Parameters(0).Value = CType(Original_PK, Integer)
Dim previousConnectionState As ConnectionState = Me.Adapter.DeleteCommand.Connection.State
If ((Me.Adapter.DeleteCommand.Connection.State And ConnectionState.Open) _
<> ConnectionState.Open) Then
Me.Adapter.DeleteCommand.Connection.Open()
End If
Try
Dim returnValue As Integer = Me.Adapter.DeleteCommand.ExecuteNonQuery
Return returnValue
Finally
If (previousConnectionState = ConnectionState.Closed) Then
Me.Adapter.DeleteCommand.Connection.Close()
End If
End Try
End Function
Public Overridable Overloads Function Insert(ByVal Link As String, ByVal File As String) As Integer
If (Link Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(0).Value = GDBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(0).Value = CType(Link, String)
End If
If (File Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(1).Value = GDBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(1).Value = CType(File, String)
End If
Dim previousConnectionState As ConnectionState = Me.Adapter.InsertCommand.Connection.State
If ((Me.Adapter.InsertCommand.Connection.State And ConnectionState.Open) _
<> ConnectionState.Open) Then
Me.Adapter.InsertCommand.Connection.Open()
End If
Try
Dim returnValue As Integer = Me.Adapter.InsertCommand.ExecuteNonQuery
Return returnValue
Finally
If (previousConnectionState = ConnectionState.Closed) Then
Me.Adapter.InsertCommand.Connection.Close()
End If
End Try
End Function
Public Overridable Overloads Function Update(ByVal Link As String, ByVal File As String, ByVal Original_PK As Integer) As Integer
If (Link Is Nothing) Then
Me.Adapter.UpdateCommand.Parameters(0).Value = GDBNull.Value
Else
Me.Adapter.UpdateCommand.Parameters(0).Value = CType(Link, String)
End If
If (File Is Nothing) Then
Me.Adapter.UpdateCommand.Parameters(1).Value = GDBNull.Value
Else
Me.Adapter.UpdateCommand.Parameters(1).Value = CType(File, String)
End If
Me.Adapter.UpdateCommand.Parameters(2).Value = CType(Original_PK, Integer)
Dim previousConnectionState As ConnectionState = Me.Adapter.UpdateCommand.Connection.State
If ((Me.Adapter.UpdateCommand.Connection.State And ConnectionState.Open) _
<> ConnectionState.Open) Then
Me.Adapter.UpdateCommand.Connection.Open()
End If
Try
Dim returnValue As Integer = Me.Adapter.UpdateCommand.ExecuteNonQuery
Return returnValue
Finally
If (previousConnectionState = ConnectionState.Closed) Then
Me.Adapter.UpdateCommand.Connection.Close()
End If
End Try
End Function
End Class
Load balance : Server 03
