Connecting programmatically with .NET

Define a connection between a .NET application and the Db2 database, BLUDB.

Before you begin

Before you can connect to your database, you must carry out the following steps:

Verify prerequisites, including installing driver packages, configuring your local environment, and downloading SSL certificates (if needed)
Collect connect information, including database details such as host name and port numbers, and connect credentials such as user ID and password

About this task

The following steps show you how to connect your application to the database with .NET.

Procedure

From a command prompt, enter the following commands.
These commands create new entries in the driver configuration file (db2dsdriver.cfg) on your computer and set the connection attributes. You need to do this step only one time.
- For a connection without SSL:
```
db2cli writecfg add -database BLUDB -host hostname -port 50000
db2cli writecfg add -dsn alias -database BLUDB -host hostname -port 50000
```
- For a connection with SSL:
```
db2cli writecfg add -database BLUDB -host hostname -port 50001
db2cli writecfg add -dsn alias -database BLUDB -host hostname -port 50001
db2cli writecfg add -database BLUDB -host hostname -port 50001 -parameter "SecurityTransportMode=SSL"
```
where:

hostname

The host name of the server.

alias

The name for an alias that you want to use to establish the .NET connection. Choose a name that is meaningful to you; for example, analytics.
Optional: To verify the .NET connection to the database, enter the following command at a command prompt:
```
 testconn40 "DATABASE=alias;UID=userid;PWD=password;"
```
where:

alias

The name of the alias that you created with the db2cli writecfg command in step 1

userid

Your Db2 user ID.

password

The password that you use to connect to your Db2 database.

Example

The following syntax shows sample C# code that uses the .NET driver to make a connection to the database.

using System;
using IBM.Data.DB2;

namespace dotNetSSLTest
{
    class Program
    {
        static void Main(string[] args)
        {
            DB2Command MyDB2Command = null;
            // Use the dsn alias that you defined in db2dsdriver.cfg with the db2cli writecfg command in step 1.
            String MyDb2ConnectionString = "database=alias;uid=userid;pwd=password;"; 
            DB2Connection MyDb2Connection = new DB2Connection(MyDb2ConnectionString);
            MyDb2Connection.Open();
            MyDB2Command = MyDb2Connection.CreateCommand();
            MyDB2Command.CommandText = "SELECT branch_code, city from GOSALES.BRANCH";
            Console.WriteLine(MyDB2Command.CommandText);

            DB2DataReader MyDb2DataReader = null;
            MyDb2DataReader = MyDB2Command.ExecuteReader();
            Console.WriteLine("BRANCH\tCITY");
            Console.WriteLine("============================");
            while (MyDb2DataReader.Read())
            {
                for (int i = 0; i <= 1; i++)
                {
                    try
                    {
                        if (MyDb2DataReader.IsDBNull(i))
                        {
                            Console.Write("NULL");
                        }
                        else
                        {
                            Console.Write(MyDb2DataReader.GetString(i));
                        }
                    }
                    catch (Exception e)
                    {
                        Console.Write(e.ToString());
                    }
                    Console.Write("\t"); 

                }
                Console.WriteLine("");
            }
            MyDb2DataReader.Close();
            MyDB2Command.Dispose();
            MyDb2Connection.Close();
        }
    }
}