SQL with CS
From SQLZoo
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