Using NuoDB Integration with Visual Studo

NuoDB integration with Visual Studio provides two ways to use the ADO.NET driver for NuoDB:

The examples in the following sections are based on the WindowsFormSample project that is provided with the ADO.NET driver.

Connecting to a NuoDB Database

To connect to a NuoDB database:

  1. In the Visual Studio Server Explorer panel, right-click Data Connections and select Add Connection.
  2. In the Choose Data Source dialog, in the Data Source field, select NuoDB Database.
  3. In the Data Provider field, select .NET Data Provider for NuoDB and click Continue.
  4. In the Add Connection dialog, configure the connection settings as follows:
    Connection StringDescription
    Broker AddressThe DNS or IP address of the machine that the NuoDB broker is running on.
    DatabaseThe name of the NuoDB database you want to access.
    UsernameThe user name to use for the connection to the database.
    PasswordThe password to use for the connection to the database.
    Default SchemaThe name of the schema to connect to.
    Enable Connection PoolingIndicate whether to enable connection pooling.
    Idle TimeoutOptional. A number of seconds after which a connection that is not being used times out and disconnects. The default is 10 seconds.
    Maximum Age

    Optional. Maximum number of seconds that an underlying connection can exist before the driver closes the underlying connection instead of returning it to the connection pool upon connection object close. Idle pooled connections are closed and removed from the pool if they reach the defined value for Maximum Age.

    The default is 10,000 seconds (2.77 hours). A value of zero means that the underlying connections can remain open for an indefinite length of time; that is, the driver will never close the connection.

    Max ConnectionsOptional. Specifies the maximum number of connections that can be open at the same time. The default is 100.
  5. Click Test Connection.
  6. If the test is successful, click OK to finish. Otherwise, correct as needed and try the test again.

Using LINQ to Query NuoDB Databases

Another way to use the NuoDB ADO.NET driver is to build an ADO.NET Entity Framework data source that can be queried by using LINQ. The steps for doing this are as follows:

  1. Attach a NuoDB database to a schema:
    • From the Visual Studio Project menu, select Add New Item.
    • In the Data section, select ADO.NET Entity Model.
    • In the first dialog of the Entity Data Model Wizard, select Generate From Database and click Next.
    • In the next dialog of the wizard, for the data connection that your application should use to connect to the Database, select the connection that you created in the previous step. Alternatively, you can create a new connection here.
    • Indicate whether to include the password in the metadata of the application and click Next.
    • In the next dialog of the wizard, select the tables and views that you want to expose to the LINQ framework.
    • In the Model Namespace field, enter a name for the model and click Finish. The ORM Model Editor opens and you can fine-tune the mapping between the LINQ objects and the database tables.
  2. Query the database:
    • In your code, create an instance of the generated model:
    • testEntities ctx = new testEntities();
    • Using the custom instruction from LINQ, extract data from the model objects. For example, to retrieve from the sample HOCKEY database the player who scored most in his career, you can build a list of all players with the sum of their goals, order it in descending order and pick just the first entry:
    • var bestPlayer = (from player in ctx.SCORING
         group player by player.PLAYERID into goals
         select new
            Player = goals.Key,
            CareerGoals = goals.Sum(player => player.GOALS)
         }).OrderByDescending(p => p.CareerGoals).First();
    • When the bestPlayer variable is accessed, the LINQ query is transformed into a SQL query, executed on the NuoDB database and the result made available:
    • string bestScorer = bestPlayer.Player + " (" + bestPlayer.CareerGoals + " goals)";
  3. Binding queries to user-interface controls. You can directly associate queries with user-interface items, such as a grid, which lets the control handle the presentation of the data. To do this, assign the LINQ query to the DataSource property of the control, for example:
  4. this.HOCKEYDataGridView1.DataSource = from player in context.PLAYERS
       where player.POSITION == "Goalie"
       select player;

Adding a Form Component to the Entity Framework

To add form components to the Entity Frameworks model:

  1. In a Visual Studio Windows Form project, display the Data Sources view by selecting Data | Show Data Sources.
  2. In the Data Sources view, click the Add New Data Source toolbar button. In the Data Source Configuration wizard, select Object from the list of choices and click Next.
  3. Expand the structure of the application and select the HOCKEY type. This is the type that the Entity Framework wizard created to model the HOCKEY table in the database.
  4. Click Finish to display a new, top-level HOCKEY entry in the Data Sources view.
  5. Select this new HOCKEY node and drag it on to the Windows Form to create a new Data Grid View.
  6. Populate this new grid control by writing some custom code.
  7. Double-click the form to open the code window and change it to include a reference to the newly defined testEntities, binding its HOCKEY property to the DataSource property of the new data grid object.

Run the modified code to see two Data Grid controls bound to the same database, using the two different binding technologies.

Binding a Data Source to a Windows Form DataSet

To bind a data source to a Windows form DataSet:

  1. In a Visual Studio Windows Form project, display the Data Sources view by selecting Data | Show Data Sources.
  2. Click Add New Data Source.
  3. In the Data Source Configuration wizard:
    • Choose Database | Next.
    • Choose Dataset | Next.
    • Accept the default data connection option [( localhost(test)] and click Next.
    • Accept Save the Connection String to the Application Configuration File and click Next.
    • Select the tables that you want to import into the DataSet and click Finish. (As a test, choose HOCKEY.)

The solution updates to include the new DataSet and Data Sources view.

Creating a Form or Other Database Object

To create a form or other database object:

  1. In the Data Sources view, under testDataSet, select the HOCKEY node and drag it on to the form preview. This creates a new Data Grid View object along with the objects that implement the visual form and the NuoDB database. You can create other objects by clicking the arrow button to the right of HOCKEY and selecting an object from the drop-down list.
  2. Compile and run the application to display the table form.