в 16:40, 26 мая 2010

2010-05-26T16:40:06Z

Admin: 1 версия

2010-05-26T12:44:56Z

1 версия

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

==Call store Procedure and pass parameters==

<source lang="vbnet">
Imports System
Imports System.Collections
Imports System.Data
Imports System.IO
Imports System.Xml.Serialization
Imports System.Xml
Imports System.Windows.Forms
Imports System.Data.SqlClient

Public Class MainClass
Shared Dim WithEvents con As SqlConnection
Shared Sub Main()
con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI")
Dim cmd As New SqlCommand("GetCountryAndOutputParam", con)
cmd.rumandType = CommandType.StoredProcedure
cmd.Parameters.Add( _
New SqlParameter("@country", SqlDbType.VarChar, 50)).Value = "USA"
cmd.Parameters.Add(New SqlParameter("@count", SqlDbType.Int))
cmd.Parameters("@count").Direction = ParameterDirection.Output
Try
con.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
Console.WriteLine("{0} - {1}", reader.GetString(0), reader.GetString(1))
End While
reader.Close()
Finally
con.Close()
End Try
Console.WriteLine("{0} - {1}", _
"Count", cmd.Parameters("@count").Value.ToString())
End Sub
End Class

</source>

==Call Store Procedure in SQL Server==

<source lang="vbnet">
Imports System
Imports System.Collections
Imports System.Data
Imports System.IO
Imports System.Xml.Serialization
Imports System.Xml
Imports System.Windows.Forms
Imports System.Data.SqlClient

Public Class MainClass
Shared Dim WithEvents con As SqlConnection
Shared Sub Main()
con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI")
Dim cmd As New SqlCommand("Ten Most Expensive Products", con)
cmd.rumandType = CommandType.StoredProcedure
Try
con.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
Console.WriteLine("{0} - {1:C}", _
reader.GetString(0), reader.GetDecimal(1))
End While
reader.Close()
Finally
con.Close()
End Try
End Sub
End Class

</source>

==Fill dataset with the results from Store Procedure==

<source lang="vbnet">
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
Module Module1
Sub Main()
" Create a connection object
Dim dbConn As New SqlConnection( _
"server=(local); database=pubs; integrated security=true")
Dim cmd As New SqlCommand()
cmd.rumandText = "spRetrieveAuthor"
cmd.rumandType = System.Data.rumandType.StoredProcedure
cmd.Connection = dbConn
cmd.Parameters.Add(New SqlParameter("@au_id", SqlDbType.Char, 11, "au_id"))
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet("Author")
da.SelectCommand.Parameters(0).Value = "172-32-1176"
da.Fill(ds)
If Not ds Is Nothing Then
MessageBox.Show("Hi, author: " & _
ds.Tables(0).Rows(0)("au_lname").ToString())
End If

End Sub
End Module

</source>

==Stored Procedure Example==

<source lang="vbnet">
Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class MainClass
Public Shared Sub StoredProcedureExample(ByVal con As SqlConnection, ByVal managerID As Integer)
Using com As SqlCommand = con.CreateCommand
com.rumandType = CommandType.StoredProcedure
com.rumandText = "uspGetManagerEmployees"
com.Parameters.Add("@ManagerID", SqlDbType.Int).Value = managerID
Dim result As Integer = com.ExecuteNonQuery
Using reader As SqlDataReader = com.ExecuteReader
Console.WriteLine("Employees managed by manager #{0}.", managerID.ToString)
While reader.Read
Console.WriteLine(" {0}, {1} ({2})", reader("LastName"), reader("FirstName"), reader("employeeID"))
End While
End Using
End Using
End Sub
Public Shared Sub Main()
Using con As New SqlConnection
con.ConnectionString = "Data Source=.\sqlexpress;Database=AdventureWorks;Integrated Security=SSPI;"
con.Open()
StoredProcedureExample(con, 185)
con.Close()
End Using
End Sub
End Class

</source>

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

VB.Net/Database ADO.net/Sql Server Store Procedure - История изменений

в 16:40, 26 мая 2010

Admin: 1 версия