VB.Net/Database ADO.net/SqlDataReader
Содержание
- 1 Employee Table Metadata
- 2 Format data when reading the data from SqlDataReader
- 3 From SqlDataReader get Column Name
- 4 Get data from SqlDataReader by Data type
- 5 Get info about each column: Name, Ordinal and FieldType
- 6 Get number of columns in SqlDataReader
- 7 Use column name to index data
- 8 Use SqlDataReader.Read to read result set
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>