VB.Net/Database ADO.net/SqlDataReader

Материал из VB Эксперт
Версия от 12:44, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Employee Table Metadata

  
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


Format data when reading the data from SqlDataReader

 
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


From SqlDataReader get Column Name

 
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


Get data from SqlDataReader by Data type

 
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


Get info about each column: Name, Ordinal and FieldType

 
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


Get number of columns in SqlDataReader

 
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


Use column name to index data

 
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


Use SqlDataReader.Read to read result set

 
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
Источник — «http://vbex.ru/index.php?title=VB.Net/Database_ADO.net/SqlDataReader&oldid=625»