Thursday, 11 April 2013

ADO .NET c# Connection Pooling with vFabric SQLFire

Whether it's accessing a database using JAVA or in this case c# I always want to use a connection pool and in this example I show and simple way to do this with a c# ADO .NET client accessing SQLFire.

1. Add a reference to your Visual Studio project in the VMware.Data.SQLFire.dll. This DLL is installed in the  

vFabric_SQLFire_11_bNNNNN\adonet\lib directory.


2. Reference the driver namespace in each source file where you want to use SQLFire components. For example, include this directive with all other references required in your application:

using VMware.Data.SQLFire;

3.  Create a c# console application as follows
  
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using VMware.Data.SQLFire;
using System.Data;

namespace SQLFireDemo
{
    class QueryDemo
    {
        private string sqlfHost = "192.168.1.4";
        private int sqlfPort = 1527;

        public QueryDemo()
        {
        }

        private string GetConnectionString()
        {
            return string.Format(@"server={0}:{1}", sqlfHost, sqlfPort);
        }

        public void run()
        {
            using (SQLFClientConnection conn = new SQLFClientConnection(GetConnectionString()))
            {
                conn.Open();
                SQLFCommand command = new SQLFCommand
                      (string.Format("SELECT * FROM dept"), conn);
                SQLFDataReader reader = command.ExecuteReader();

                try
                {
                    StringBuilder row = new StringBuilder();
                    while (reader.Read())
                    {
                        Console.WriteLine(string.Format("Dept[deptno={0}, dname={1}]",
                                          reader.GetString(0),
                                          reader.GetString(1)));
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                }
                finally
                {
                    reader.Close();
                }
            }

        }

        static void Main(string[] args)
        {
            QueryDemo test = new QueryDemo();
            test.run();
        }
    }
}

Output as follows

Dept[deptno=10, dname=ACCOUNTING]
Dept[deptno=20, dname=RESEARCH]
Dept[deptno=30, dname=SALES]
Dept[deptno=40, dname=OPERATIONS]
Dept[deptno=50, dname=MARKETING]
Dept[deptno=60, dname=DEV]
Dept[deptno=70, dname=SUPPORT]
Press any key to continue . . .

4 comments:

Anonymous said...

So where do you exactly use connection pooling? Seems like you are not

Pas Apicella said...

Yes you are correct. For SQLFire you must use the approaches outlined below.

http://pubs.vmware.com/vfabric53/index.jsp?topic=/com.vmware.vfabric.sqlfire.1.1/developers_guide/topics/server-side/ado.net-pooling.html

Dennis Burns said...

Step by step and correctly explained article about how any .net developer about how to add a reference to your Visual Studio project in the VMware.Data.SQLFire.dll.

Valentin said...

This is cool!