Configuring SqlCli

In order for an SqlCli client object to be able to connect and interact with a SQL database, the object needs to be configured first. Essentially, it needs to know two things:

• A “driver” (to know which database to connect to)
• A “connection string” (to connect and access the database)

The driver is specified via the Driver() configuration function, while the connection string is configured via the ConnString() configuration function.

A list of drivers is available in this manual. For each one of such drivers, here below we’re going to document how to build a proper/suitable connection string.

Driver: “n1ql” (Couchbase)

When using the n1ql driver, the connection string can either be the host name (or IP address) and port of a standalone Couchbase instance, or the URL of a Couchbase cluster.

Example of standalone Couchbase connection:

{
  var cli = new SqlCli();
  cli.Driver("n1ql");
  cli.ConnString("localhost:8093");
  if (cli.Connect()) {
    // Perform SQL tasks....
    cli.Close();
  }
}

Example of connecting to a Couchbase cluster:

{
  var cli = new SqlCli();
  cli.Driver("n1ql");
  cli.ConnString("http://localhost:9000/");
  if (cli.Connect()) {
    // Perform SQL tasks....
    cli.Close();
  }
}

Driver: “firebirdsql” (FirebirdSQL)

When using the firebirdsql driver, the connection string must be in the following format:

user:password@servername/foo/bar.fdb

Example of standalone FirebirdSQL connection:

{
  var cli = new SqlCli();
  cli.Driver("firebirdsql");
  cli.ConnString("user:password@servername/foo/bar.fdb");
  if (cli.Connect()) {
    // Perform SQL tasks....
    cli.Close();
  }
}

Driver: “bigquery” (Google BigQuery)

When using the bigquery driver, the connection string must be in the following format:

bigquery://projectid/location/dataset

Example of standalone BigQuery connection:

{
  var cli = new SqlCli();
  cli.Driver("bigquery");
  cli.ConnString("bigquery://projectid/location/dataset");
  if (cli.Connect()) {
    // Perform SQL tasks....
    cli.Close();
  }
}

Driver: “mssql” (MS SQL Server)

When using the mssql driver, the general rule to build your connection string is to build it as a URL following this general specification:

sqlserver://username:password@host/instance?param1=valueparam2=value

So, for example, if you’re connecting to a local instance of SQLExpress, you’ll use a connection string that looks like this:

sqlserver://sa@localhost/SQLExpress?database=masterconnection+timeout=30

Whereas if you’re connecting to a SQL server on localhost (but want to specify a non-standard port) you’ll use a connection string like this:

sqlserver://sa:mypass@localhost:1234?database=masterconnection+timeout=30

Remember to URL-encode any and every part of the connection string, because it’s ultimately a URL and must follow the rules of all URLs.

Example of standalone SQL Server connection:

{
  var cli = new SqlCli();
  cli.Driver("mssql");
  cli.ConnString("sqlserver://sa:mypass@localhost?database=master&connection+timeout=30");
  if (cli.Connect()) {
    // Perform SQL tasks....
    cli.Close();
  }
}

Driver: “mysql” (MySQL/MariaDB)

When using the mysql driver, the connection string must be in the following format:

user:password@(hostname_or_ipaddr:port)/dbname

Example of standalone MySQL connection:

{
  var cli = new SqlCli();
  cli.Driver("mysql");
  cli.ConnString("user:pass@(localhost:3306)/testdb");
  if (cli.Connect()) {
    // Perform SQL tasks....
    cli.Close();
  }
}

Driver: “oracle” (Oracle)

When using the oracle driver, the connection string must be in the following format:

user/pass@hostname_or_ipaddr:port/database

Example of standalone Oracle connection:

{
  var cli = new SqlCli();
  cli.Driver("oracle");
  cli.ConnString("user/pass@localhost:1521/mydb");
  if (cli.Connect()) {
    // Perform SQL tasks....
    cli.Close();
  }
}

Driver: “pq” (Postgres)

When using the pg driver, the connection string may contain the following information fields:

• host: the host name or IP address of the machine/VM running Postgres
• port: the port your Postgres listens on (default 5432)
• user: a username to accedd the database
• password: a password to accedd the database
• dbname: the name of the database you want to access
• sslmode: enable || disable

Depending on your database server configuration you may or may not include some of the fields here above in your connection string.

Example of standalone Postgres connection:

{
  var cli = new SqlCli();
  cli.Driver("pq");
  cli.ConnString("host=localhost port=5432 user=your_db_user password=your_db_passwrod dbname=db_name sslmode=disable");
  if (cli.Connect()) {
    // Perform SQL tasks....
    cli.Close();
  }
}

Driver: “sqlite” (SQLite)

When using the sqlite driver, the connection string can either be a fully qualified path to the database data file, or the special keyword :memory: if you wish to use a volatile in-memory DB.

Example of in-memory SQLite DB:

{
  var cli = new SqlCli();
  cli.Driver("sqlite");
  cli.ConnString(":memory:");
  if (cli.Connect()) {
    // Perform SQL tasks....
    cli.Close();
  }
}

Example of file-based SQLite DB:

{
  var cli = new SqlCli();
  cli.Driver("sqlite");
  cli.ConnString("/home/someuser/mydata.sqlite");
  if (cli.Connect()) {
    // Perform SQL tasks....
    cli.Close();
  }
}