extendtypedresultset.snippet

清华大学出版社出版的 移动应用开发宝典 张大威（2008）的附书源代码

<?xml version="1.0"?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <Title>Extends a Sql Server 2005 Compact Edition SqlCeResultSet</Title>
      <Author>Jim Wilson/Andy Wigley</Author>
      <Description>Creates a partial class to extend the tool generated strongly typed SqlCeResultSet with methods that allow you to use queries, sort and select on indices. The tool-generated code only allows Table Direct lookups, which are very fast, but these extensions allow you to use all the functionality of the SqlCeResultSet.</Description>
      <Shortcut>extendSqlCeRS</Shortcut>
    </Header>
    <Snippet>
      <References>
        <Reference>
          <Assembly>System.Data.SqlServerCe</Assembly>
        </Reference>
      </References>
      <Imports>
        <Import>
          <Namespace>System.Data</Namespace>
        </Import>
        <Import>
          <Namespace>System.Data.SqlServerCe</Namespace>
        </Import>
      </Imports>
      <Declarations>
        <Literal>
          <ID>TableName</ID>
          <Type>String</Type>
          <ToolTip>The name of the table in the SQL Server 2005 Compact Edition database</ToolTip>
          <Default>TableName</Default>
        </Literal>
        <Literal>
          <ID>Database</ID>
          <Type>String</Type>
          <ToolTip>The name of the database, for example MyDatabase.sdf. If your database has a password, include that in the string, for example: MyDatabase.sdf; Password=""mydatabasepassword"";</ToolTip>
          <Default>MyDatabase.sdf</Default>
        </Literal>
      </Declarations>
      <Code Language="VB" Kind="type decl"><![CDATA[Partial Class $TableName$ResultSet

    Const databaseFilename As String = "$Database$"
    Const tableName As String = "$TableName$"

    ''' <summary>
    ''' Construct the ResultSet instance initializing the ResultSetOptions and connection string
    ''' optionally opening the table. Calling this method with an openTable value of true provides
    ''' the same behavior as using the default constructor.
    ''' </summary>
    ''' <param name="openTable"></param>
    ''' <remarks></remarks>
    Public Sub New(ByVal openTable As Boolean)
        MyBase.New()
        InitializeResultSetOptions()
        InitializeResultSetConnectionString()

        If (openTable) Then
            Me.Open()
        End If
    End Sub

    ''' <summary>
    ''' ResultSet will contain those records
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub OpenEx()
        Using sqlCeSelectCommand As SqlCeCommand = CreateConnectionAndCommand()
            sqlCeSelectCommand.CommandText = tableName
            sqlCeSelectCommand.CommandType = System.Data.CommandType.TableDirect

            sqlCeSelectCommand.ExecuteResultSet(ResultSetOptions, Me)
        End Using
    End Sub

    ''' <summary>
    ''' ResultSet will contain those records
    ''' </summary>
    ''' <param name="sqlCommandText"></param>
    ''' <remarks></remarks>
    Public Sub Open(ByVal sqlCommandText As String)
        Using sqlCeSelectCommand As SqlCeCommand = CreateConnectionAndCommand()
            sqlCeSelectCommand.CommandText = sqlCommandText
            sqlCeSelectCommand.CommandType = System.Data.CommandType.Text

            sqlCeSelectCommand.ExecuteResultSet(ResultSetOptions, Me)
        End Using
    End Sub

    ''' <summary>
    ''' ResultSet will contain only those records where the value of the specified index falls
    ''' within rangeStart and rangeEnd inclusive. Records will be returned in the order of the index.
    ''' If rangeStart is null, the ResultSet will contain the whole table with the records returned
    ''' in the order of the index
    ''' </summary>
    ''' <param name="indexName"></param>
    ''' <param name="rangeStart"></param>
    ''' <param name="rangeEnd"></param>
    ''' <remarks></remarks>
    Public Sub Open(ByVal indexName As String, ByVal rangeStart() As Object, ByVal rangeEnd() As Object)
        Using sqlCeSelectCommand As SqlCeCommand = CreateConnectionAndCommand()

            sqlCeSelectCommand.CommandText = tableName
            sqlCeSelectCommand.CommandType = System.Data.CommandType.TableDirect
            sqlCeSelectCommand.IndexName = indexName

            ' If a range value specified, call SetRange - start w/o end is legal, but not vice versa
            If Not rangeStart Is Nothing Then
                sqlCeSelectCommand.SetRange(DbRangeOptions.Default, rangeStart, rangeEnd)
            End If

            sqlCeSelectCommand.ExecuteResultSet(Me.ResultSetOptions, Me)
        End Using
    End Sub

    ''' <summary>
    ''' See the summary of Open(string indexName, object[] rangeStart, object[] rangeEnd)
    ''' for details.
    ''' This method is a wrapper provided for convenience in those cases where the specified index 
    ''' contains only a single column
    ''' </summary>
    ''' <param name="indexName"></param>
    ''' <param name="rangeStart"></param>
    ''' <param name="rangeEnd"></param>
    ''' <remarks></remarks>
    Public Sub Open(ByVal indexName As String, ByVal rangeStart As Object, ByVal rangeEnd As Object)
        Open(indexName, New Object() {rangeStart}, New Object() {rangeEnd})
    End Sub

    ''' <summary>
    ''' ResultSet will contain only those records where the value of the specified index falls
    ''' within the criteria set by the rangeOptions, rangeStart, rangeEnd criteria. 
    ''' Records will be returned in the order of the index.
    ''' </summary>
    ''' <param name="indexName"></param>
    ''' <param name="rangeOptions"></param>
    ''' <param name="rangeStart"></param>
    ''' <param name="rangeEnd"></param>
    ''' <remarks></remarks>
    Public Sub Open(ByVal indexName As String, ByVal rangeOptions As DbRangeOptions, ByVal rangeStart() As Object, ByVal rangeEnd() As Object)
        Using sqlCeSelectCommand As SqlCeCommand = CreateConnectionAndCommand()
            sqlCeSelectCommand.CommandText = tableName
            sqlCeSelectCommand.CommandType = System.Data.CommandType.TableDirect
            sqlCeSelectCommand.IndexName = indexName

            sqlCeSelectCommand.SetRange(rangeOptions, rangeStart, rangeEnd)

            sqlCeSelectCommand.ExecuteResultSet(Me.ResultSetOptions, Me)
        End Using
    End Sub

    ''' <summary>
    ''' See the summary of Open(string indexName, DbRangeOptions rangeOptions, object[] rangeStart, object[] rangeEnd)
    ''' for details.
    ''' This method is a wrapper provided for convenience in those cases where the specified index 
    ''' contains only a single column
    ''' </summary>
    ''' <param name="indexName"></param>
    ''' <param name="rangeOptions"></param>
    ''' <param name="rangeStart"></param>
    ''' <param name="rangeEnd"></param>
    ''' <remarks></remarks>
    Public Sub Open(ByVal indexName As String, ByVal rangeOptions As DbRangeOptions, ByVal rangeStart As Object, ByVal rangeEnd As Object)
        Open(indexName, rangeOptions, New Object() {rangeStart}, New Object() {rangeEnd})
    End Sub

    ''' <summary>
    ''' Make connection string publicly visible
    ''' </summary>
    Public ReadOnly Property DBConnectionString() As String
        Get
            Return Me.ResultSetConnectionString
        End Get
    End Property

    ''' <summary>
    ''' Make options publicly visible
    ''' </summary>
    Public ReadOnly Property DBResultSetOptions() As ResultSetOptions
        Get
            Return ResultSetOptions
        End Get
    End Property

#Region "InitializeResultSetConnectionString, InitializeResultSetOptions, CreateConnectionAndCommand"

    ''' <summary>
    ''' Set the resultSetConnectionString member variable to the default connection string.
    ''' </summary>
    ''' <remarks></remarks>
    Protected Sub InitializeResultSetConnectionString()
        If resultSetConnectionString Is Nothing Then
            resultSetConnectionString = ("Data Source =" _
                + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) _
                + "\" + databaseFilename + ";"))
        End If
    End Sub

    ''' <summary>
    ''' Set the resultSetOptions member variable to the default values. By default it is set
    ''' to support scrolling, updates and be sensitive to changes in the datasource.
    ''' </summary>
    ''' <remarks></remarks>
    Protected Sub InitializeResultSetOptions()
        ResultSetOptions = System.Data.SqlServerCe.ResultSetOptions.Scrollable
        resultSetOptions = (resultSetOptions Or System.Data.SqlServerCe.ResultSetOptions.Sensitive)
        resultSetOptions = (resultSetOptions Or System.Data.SqlServerCe.ResultSetOptions.Updatable)
    End Sub

    ''' <summary>
    ''' Creates a connection, opens it and factories a command instance from the connection.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function CreateConnectionAndCommand() As SqlCeCommand
        SqlCeConnection = New System.Data.SqlServerCe.SqlCeConnection(ResultSetConnectionString)
        SqlCeConnection.Open()

        Return SqlCeConnection.CreateCommand()
    End Function

#End Region

#Region "IDisposable Members"

    Public Shadows  Sub Dispose()
        If Not Me.Connection Is Nothing Then
            Me.Connection.Dispose()
        End If
        MyBase.Dispose()
    End Sub

#End Region

End Class  '$TableName$]]></Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>