VB.Net/Database ADO.net/SqlDataReader

Материал из VB Эксперт
Версия от 15:44, 26 мая 2010; Admin (обсуждение | вклад) (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>