в 16:40, 26 мая 2010

2010-05-26T16:40:06Z

Admin: 1 версия

2010-05-26T12:44:42Z

1 версия

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

==Employee Table Metadata==

<source lang="vbnet">
Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class MainClass
Public Shared Sub Main()
Using con As New SqlConnection
con.ConnectionString = "Data Source=.\sqlexpress;Database=AdventureWorks;Integrated Security=SSPI"
Using com As SqlCommand = con.CreateCommand
com.rumandType = CommandType.Text
com.rumandText = "SELECT e.BirthDate,c.FirstName,c.LastName FROM HumanResources.Employee e INNER JOIN Person.Contact c ON e.EmployeeID=c.ContactID ORDER BY e.BirthDate;SELECT * FROM HumanResources.Employee"
con.Open()
Using reader As SqlDataReader = com.ExecuteReader
While reader.Read
Console.WriteLine(" {0,18:D} - {1} {2}", reader.GetDateTime(0), reader("FirstName"), reader(2))
End While
If (reader.NextResult()) Then
For field As Integer = 0 To reader.FieldCount - 1
Console.WriteLine(" Column Name:{0} Type:{1}", reader.GetName(field), reader.GetDataTypeName(field))
Next
End If
End Using
con.Close()
End Using
End Using
End Sub
End Class

</source>

==Format data when reading the data from SqlDataReader==

<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 1
Dim sql As String ="SELECT FirstName, LastName FROM Employee; "
"Create Command object
Dim thisCommand As New SqlCommand _
(sql, thisConnection)
Try
" Open Connection
thisConnection.Open()
Console.WriteLine("Connection Opened")
" Execute Query
Dim thisReader As SqlDataReader = thisCommand.ExecuteReader()
While (thisReader.Read())
" Get column values for all rows
Console.WriteLine("{0} | {1}", _
thisReader.GetString(0).PadLeft(25), _
thisReader.GetString(1))
End While

"Close DataReader
thisReader.Close()
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>

==From SqlDataReader get Column Name==

<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 1
Dim sql As String ="SELECT FirstName, LastName FROM Employee; "
"Create Command object
Dim thisCommand As New SqlCommand _
(sql, thisConnection)
Try
" Open Connection
thisConnection.Open()
Console.WriteLine("Connection Opened")
" Execute Query
Dim thisReader As SqlDataReader = thisCommand.ExecuteReader()
" Get column names
Console.WriteLine("Column Names: {0} | {1}", _
thisReader.GetName(0).PadLeft(11), _
thisReader.GetName(1))
"Close DataReader
thisReader.Close()
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>

==Get data from SqlDataReader by Data type==

<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 ID, FirstName FROM Employee"
"Create Command object
Dim thisCommand As New SqlCommand(sql, thisConnection)

Try
" Open Connection
thisConnection.Open()
Console.WriteLine("Connection Opened")

" Execute Query
Dim thisReader As SqlDataReader = thisCommand.ExecuteReader()
" Fetch Data
While (thisReader.Read())
Console.WriteLine("{0} | {1} ", _
thisReader.GetInt32(0), _
thisReader.GetString(1).PadLeft(32))
End While
"Close DataReader
thisReader.Close()
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>

==Get info about each column: Name, Ordinal and FieldType==

<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 1
Dim sql As String ="SELECT FirstName, LastName FROM Employee; "
"Create Command object
Dim thisCommand As New SqlCommand _
(sql, thisConnection)
Try
" Open Connection
thisConnection.Open()
Console.WriteLine("Connection Opened")
" Execute Query
Dim thisReader As SqlDataReader = thisCommand.ExecuteReader()
" Get info about each column
Console.WriteLine(""{0}" has index {1} and type {2}", _
thisReader.GetName(0), _
thisReader.GetOrdinal("FirstName"), _
thisReader.GetFieldType(0))
"Close DataReader
thisReader.Close()
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>

==Get number of columns in SqlDataReader==

<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 1
Dim sql As String ="SELECT FirstName, LastName FROM Employee; "
"Create Command object
Dim thisCommand As New SqlCommand _
(sql, thisConnection)
Try
" Open Connection
thisConnection.Open()
Console.WriteLine("Connection Opened")
" Execute Query
Dim thisReader As SqlDataReader = thisCommand.ExecuteReader()

" Get number of columns
Console.WriteLine()
Console.WriteLine("Number of columns in a row: {0}", _
thisReader.FieldCount)

"Close DataReader
thisReader.Close()
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>

==Use column name to index data==

<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 1
Dim sql As String ="SELECT FirstName, LastName FROM Employee; "
"Create Command object
Dim thisCommand As New SqlCommand _
(sql, thisConnection)
Try
" Open Connection
thisConnection.Open()
Console.WriteLine("Connection Opened")
" Execute Query
Dim thisReader As SqlDataReader = thisCommand.ExecuteReader()
Console.WriteLine("{0} {1}", _
"First Name".PadLeft(25), _
"Last Name".PadLeft(25))
While (thisReader.Read())
Console.WriteLine("{0} | {1}", _
thisReader("FirstName").ToString().PadLeft(25), _
thisReader("LastName").ToString().PadLeft(25))
End While
"Close DataReader
thisReader.Close()
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>

==Use SqlDataReader.Read to read result set==

<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")
Dim thisCommand As New SqlCommand _
("SELECT FirstName FROM Employee", _
thisConnection)
Try
" Open Connection
thisConnection.Open()
Console.WriteLine("Connection Opened")
" Execute Query
Dim thisReader As SqlDataReader = thisCommand.ExecuteReader()
While (thisReader.Read())
Console.WriteLine("Name: {0}", _
thisReader(0))
End While
"Close DataReader
thisReader.Close()

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/SqlDataReader - История изменений

в 16:40, 26 мая 2010

Admin: 1 версия