C# - Bits to connect to a database

The following are the basic methods to retrieve data from and store data in a database using C#.

The connection string

In a web application, we pick this up in the web.config as follows:

private static string ConnectionString {
  get {
    var cs = ConfigurationManager.ConnectionStrings["ConnStrConsult"];
    if (cs != null)
      return cs.ConnectionString;
    else
      throw new Exception("Connection string not found.");
  }
}

Retrieve data

This method takes an SQL SELECT statement and an optional number of parameters that are used in this statement. Also see LINQ on datatable for further processing of the retrieved data and Retrieve objects to retrieve an IEnumerable of model objects instead.

private static DataTable Retrieve(string query, params object[] parameters) {
  try {
    var data = new DataTable();
    using (var adapter = new OdbcDataAdapter(query, ConnectionString)) {
      SetParameters(adapter.SelectCommand, parameters);
      adapter.Fill(data);
    }
    return data;
  }
  catch (OdbcException ex) {
    if (ex.Message.Contains("database not found"))
      throw new Exception("Could not find database.");
    else
      throw;
  }
}

private static void SetParameters(OdbcCommand command, params object[] parameters) {
  if (command != null && parameters != null && parameters.Length > 0) {
    int i = 0;
    while (i < parameters.Length) {
      command.Parameters.AddWithValue("p" + i, parameters[i] ?? DBNull.Value);
      i++;
    }
  }
}

Execute statements

This one takes an INSERT, UPDATE or DELETE statement with optional parameters and executes it on the database.

private static object Execute(string query, params object[] parameters) {
  using (var connection = new OdbcConnection(ConnectionString))
  using (var command = new OdbcCommand(query, connection)) {
    SetParameters(command, parameters);
    connection.Open();
    return command.ExecuteScalar();
  }
}