返回介绍

Retrieving data with SqliteDataReader

发布于 2025-02-22 22:20:17 字数 5213 浏览 0 评论 0 收藏 0

The SqliteDataReader is a class used to retrieve data from the database. It is used with the SqliteCommand class to execute an SQL SELECT statement and then access the returned rows. It provides fast, forward-only, read-only access to query results. It is the most efficient way to retrieve data from tables.

Rather than using a constructor, we create an instance of the SqliteDataReader by calling the ExecuteReader() method of the SqliteCommand object. While the SqlDataReader is being used, the associated SqlConnection serves the SqlDataReader . No other operations can be performed on the SqlConnection other than closing it.

Option Strict On

Imports Mono.Data.Sqlite

Module Example

  Sub Main()

    Dim cs As String = "URI=file:test.db"

    Using con As New SqliteConnection(cs)    
      con.Open()
    
      Using cmd As New SqliteCommand(con)

        cmd.CommandText = "SELECT * FROM Cars LIMIT 5"
        
        Dim rdr As SqliteDataReader = cmd.ExecuteReader()

        Using rdr        
          While (rdr.Read())
            Console.WriteLine(rdr.GetInt32(0) & " " _ 
              & rdr.GetString(1) & " " & rdr.GetInt32(2))
          End While     
        End Using    
      End Using

      con.Close()
    End Using
    
  End Sub

End Module

We get 5 cars from the Cars table and print them to the console.

Dim rdr As SqliteDataReader = cmd.ExecuteReader()

To create an SQLiteDataReader object, we must call the ExecuteReader() method of the SqliteCommand object.

While (rdr.Read())
  Console.WriteLine(rdr.GetInt32(0) & " " _ 
    & rdr.GetString(1) & " " & rdr.GetInt32(2))
End While

The Read() method advances the data reader to the next record. It returns true if there are more rows; otherwise false. We can retrieve the value using the array index notation, or use a specific method to access column values in their native data types. The latter is more efficient.

$ mono retrieve.exe 
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000

The first five rows of the Cars table.

We can retrieve the fields by their column names.

Option Strict On

Imports Mono.Data.Sqlite

Module Example

  Sub Main()

    Dim cs As String = "URI=file:test.db"

    Using con As New SqliteConnection(cs)    
      con.Open()
    
      Using cmd As New SqliteCommand(con)
        cmd.CommandText = "SELECT * FROM Cars LIMIT 5"
        
        Dim rdr As SqliteDataReader = cmd.ExecuteReader()

        Using rdr
          While (rdr.Read())
            Console.Write("{0} ", rdr("Id"))
            Console.Write("{0} ", rdr("Name"))
            Console.WriteLine("{0} ", rdr("Price"))
          End While     
        End Using

      End Using
      con.Close()
    End Using
    
  End Sub

End Module

The example prints 5 rows from the Cars table. This time we use the column names to get the table fields.

While (rdr.Read())
  Console.Write("{0} ", rdr("Id"))
  Console.Write("{0} ", rdr("Name"))
  Console.WriteLine("{0} ", rdr("Price"))
End While

The database table fields are referenced by their column names.

Multiple statements

The ADO.NET specification allows to execute multiple statements in a single string. In case of queries, the SqliteDataReader returns multiple result sets. It has the NextResult() method to navigate through the result sets.

Option Strict On

Imports Mono.Data.Sqlite

Module Example

  Sub Main()

    Dim cs As String = "URI=file:test.db"

    Using con As New SqliteConnection(cs)     
      con.Open()
    
      Using cmd As New SqliteCommand(con)
        cmd.CommandText = "SELECT 25; SELECT 44; SELECT 33"
        
        Dim rdr As SqliteDataReader = cmd.ExecuteReader()

        Using rdr        
          Do          
            rdr.Read()
            Console.WriteLine("{0}", rdr.GetInt32(0))
          Loop While rdr.NextResult()     
        End Using    
      End Using

      con.Close()
    End Using
    
  End Sub

End Module

We have three queries in one SQL string. There will be three result sets.

cmd.CommandText = "SELECT 25; SELECT 44; SELECT 33"

There are three SELECT statements. They are separated by the semicolon character. Each of them will return a single value.

Do          
  rdr.Read()
  Console.WriteLine("{0}", rdr.GetInt32(0))

Loop While rdr.NextResult()

The Read() method advances the SqliteDataReader to the next record. The GetInt32() method retrieves the value as a 32-bit signed integer. The NextResult() advances the data reader to the next result.

$ mono multiple.exe 
25
44
33

Running the example.

We have finished reading data with the SqliteDataReader .

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。
列表为空,暂无数据
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
    原文