в 16:40, 26 мая 2010

2010-05-26T16:40:06Z

Admin: 1 версия

2010-05-26T12:44:54Z

1 версия

Новая страница

==Execute Update command with parameters==

<source lang="vbnet">
Imports System
Imports System.Data
Imports System.Data.SqlClient

public class MainClass
Shared Sub Main()
Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
"integrated security=sspi;database=MyDatabase")
" Sql Select Query
Dim sql As String = "SELECT * FROM Employee"
" Sql Update Statement
Dim updateSql As String = _
"UPDATE Employee " & _
"SET FirstName = @FirstName " & _
"WHERE ID = @ID"
Try
" Create Data Adapter
Dim da As New SqlDataAdapter
da.SelectCommand = New SqlCommand(sql, thisConnection)
" Create and fill Dataset
Dim ds As New DataSet
da.Fill(ds, "Employee")
" Get the Data Table
Dim dt As DataTable = ds.Tables("Employee")
" Display Rows Before Changed
Console.WriteLine("Before altering the dataset")
For Each row As DataRow In dt.Rows
Console.WriteLine("{0} | {1} | {2}", _
row("ID").ToString().PadRight(10), _
row("FirstName").ToString().PadRight(10), _
row("LastName"))
Next
" Modify city in first row
dt.Rows(0)("FirstName") = "Ham"
" Update Employees
" 1. Create Command
Dim UpdateCmd As New SqlCommand(updateSql, thisConnection)
" 2. Map Parameters
" 2.1 City
UpdateCmd.Parameters.Add("@FirstName", _
SqlDbType.NVarChar, 15, "FirstName")
" 2.2 EmployeeId
Dim idParam As SqlParameter = _
UpdateCmd.Parameters.Add("@ID", _
SqlDbType.Int, 4, "ID")
idParam.SourceVersion = DataRowVersion.Original
" Update employees
da.UpdateCommand = UpdateCmd
da.Update(ds, "Employee")
" Display Rows After Alteration
Console.WriteLine("=========")
Console.WriteLine("After altering the dataset")
For Each row As DataRow In dt.Rows
Console.WriteLine("{0} | {1} | {2}", _
row("ID").ToString().PadRight(10), _
row("FirstName").ToString().PadRight(10), _
row("LastName"))
Next
Catch ex As SqlException
" Display error
Console.WriteLine("Error: " & ex.ToString())
Finally
" Close Connection
thisConnection.Close()
Console.WriteLine("Connection Closed")
End Try
End Sub
End Class

</source>

==Insert command with parameters==

<source lang="vbnet">
Imports System
Imports System.Data
Imports System.Data.SqlClient

public class MainClass
Shared Sub Main()
Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
"integrated security=sspi;database=MyDatabase")
" Sql Query
Dim sql As String = "SELECT * FROM Employee "
Dim insertSql As String = "INSERT INTO Employee " & _
"(ID, FirstName, LastName)VALUES" & _
"(@ID, @FirstName, @LastName)"
Try
" Create Data Adapter
Dim da As New SqlDataAdapter
da.SelectCommand = New SqlCommand(sql, thisConnection)
" Create and fill Dataset
Dim ds As New DataSet
da.Fill(ds, "Employee")
" Get the Data Table
Dim dt As DataTable = ds.Tables("Employee")
" Display Rows Before Changed
Console.WriteLine("Before altering the dataset")
For Each row As DataRow In dt.Rows
Console.WriteLine("{0} | {1} | {2}", _
row("ID").ToString().PadRight(10), _
row("FirstName").ToString().PadRight(10), _
row("LastName"))
Next
" Add A Row
Dim newRow As DataRow = dt.NewRow()
newRow("FirstName") = "Edna"
newRow("LastName") = "Everage"
newRow("ID") = "2"
dt.Rows.Add(newRow)
" Display Rows After Alteration
Console.WriteLine("=========")
Console.WriteLine("After altering the dataset")
For Each row As DataRow In dt.Rows
Console.WriteLine("{0} | {1} | {2}", _
row("ID").ToString().PadRight(10), _
row("FirstName").ToString().PadRight(10), _
row("LastName"))
Next
" Insert employees
" 1. Create command
Dim insertCmd As New SqlCommand(insertSql, thisConnection)
" 2. Map parameters
insertCmd.Parameters.Add("@FirstName", _
SqlDbType.NVarChar, 10, "FirstName")
insertCmd.Parameters.Add("@LastName", _
SqlDbType.NVarChar, 20, "LastName")
insertCmd.Parameters.Add("@ID", _
SqlDbType.Int, 15, "ID")
" 3. Insert employees
da.InsertCommand = insertCmd
da.Update(ds, "Employee")
Catch ex As SqlException
" Display error
Console.WriteLine("Error: " & ex.ToString())
Finally
" Close Connection
thisConnection.Close()
Console.WriteLine("Connection Closed")
End Try
End Sub
End Class

</source>

← Предыдущая	Версия 16:40, 26 мая 2010
(нет различий)

VB.Net/Database ADO.net/SqlCommand Update - История изменений

в 16:40, 26 мая 2010

Admin: 1 версия