SQL with CS

A connection based on parameters and cursors works well with C#.

Connecting to SQL Server Databases

The namespace System.Data.SqlClient includes the SqlCommand class that has all you need to run SQL from C#

• "Data Source=localhost\\SQLEXPRESS;Initial Catalog=sqlzoo;Integrated Security=True" is the connection string - this only works for Microsoft SQL Server databases.
• SqlDataReader acts as a cursor into the result table.
• @name is a named parameter that appears in the SQL statement

using System;
using System.Data.SqlClient;
class Program
{
  static void Main(string[] args)
  {
    SqlCommand command = new SqlCommand(
      "select capital from world where name=@name",
      new SqlConnection("Data Source=localhost\\SQLEXPRESS;Initial Catalog=sqlzoo;Integrated Security=True"
    ));
    command.Parameters.Add(new SqlParameter("name", "France"));
    command.Connection.Open();
    SqlDataReader sdr = command.ExecuteReader();
    while (sdr.Read())
    {
      Console.WriteLine(sdr["capital"]);
    }
    command.Connection.Close();
  }
}

Connecting to a non-Microsoft database

You will need to install a driver. This example uses the MySQL ODBC driver from Oracle. You'll be able to find an ODBC driver for any database (including MS SQL Server).

using System;
using System.Data.Odbc;
class Program
{
  static void Main(string[] args)
  {
    string constr = "Driver={MySQL ODBC 8.0 ANSI Driver};Server=localhost;Database=gisq;User=scott;Password=tiger;Option=3;";
    OdbcCommand command = new OdbcCommand(
      "select capital from world where name=?",
      new OdbcConnection(constr)
    );
    command.Parameters.AddWithValue("@name", "France");
    command.Connection.Open();
    OdbcDataReader sdr = command.ExecuteReader();
    while (sdr.Read())
    {
      Console.WriteLine(sdr[0]);
    }
    command.Connection.Close();
  }
}

• Notice that the parameter appears as ? in the query
• You need to index the result (in the sdr cursor) using an integer not a name