Smelly ODBC Code (C#)

Attention.md

Smelly ODBC Code (C#)

You can use any DB Server for creating the Feedback Table. To help with MySQL database:

• Run the ddl.sql
• Run the dml.sql

NOTE: For other databases, please consult the respective help.

ddl.sql

-- Create Database Session Analytics

CREATE DATABASE sessionAnalytics;

-- Create Table Feedback.

CREATE TABLE `feedback` (
  `sessionId` int(11) DEFAULT NULL,
  `delegateId` int(11) DEFAULT NULL,
  `isSpeaker` char(1) DEFAULT NULL,
  `rating` int(11) DEFAULT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

dml.sql

-- Insert a few records in the feedback table

INSERT INTO `feedback` VALUES (1,1,'Y',5,'2015-10-11 09:01:13'),(1,2,'Y',4,'2015-10-11 09:01:13'),(1,3,'Y',3,'2015-10-11 09:01:13'),(1,4,'N',3,'2015-10-11 09:01:13'),(1,5,'N',2,'2015-10-11 09:01:13'),(1,6,'N',1,'2015-10-11 09:01:13'),(1,7,'N',3,'2015-10-11 09:31:53');

Runner.cs

using System;
using System.Collections;
using System.Collections.Generic;

static class IEnumerableExtensions {
  public static void ForEach<T>(this IList<T> list, Action<T> action) {
    if (list == null)
      throw new ArgumentNullException("Require non-null list!");

    if (action == null)
      throw new ArgumentNullException("Require non-null action!");

    foreach (T item in list) {
      action(item);
    }
  }
}

public class Runner {
  public static void Main(string[] args) {
    string connectionString = "DRIVER={MySQL ODBC 8.0 ANSI Driver};SERVER=localhost;DATABASE=sessionAnalytics;UID=???;PWD=???;OPTION=3;";
    Sql.ExecuteQuery(connectionString, "SELECT * from feedback").ForEach(Console.WriteLine);
  }
}

Session.cs

using System;

public class Feedback {
  private long sessionId;
  private long delegateId;
  private char isSpeaker;
  private int rating;
  private DateTime createdOn;
  
  public Feedback(long sessionId, long delegateId, char isSpeaker, int rating, DateTime createdOn) {
    this.sessionId = sessionId;
    this.delegateId = delegateId;
    this.isSpeaker = isSpeaker;
    this.rating = rating;
    this.createdOn = createdOn;
  }
  
  public override string ToString() {
    return $"Feedback(sessionId={sessionId}, delegateId={delegateId}, isSpeaker={isSpeaker}, rating={rating}, createdOn={createdOn})";
  }
}

Sql.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;

class Sql {
  public static IList<Feedback> ExecuteQuery(string connectionString, string sql) {
    using (OdbcConnection connection = new OdbcConnection(connectionString)) {
      OdbcCommand command = new OdbcCommand(sql, connection);
      connection.Open();
      // Execute the DataReader and access the data.
      using (OdbcDataReader reader = command.ExecuteReader()) {
        IList<Feedback> feedbacks = new List<Feedback>();
        while (reader.Read()) {
          var feedback = new Feedback(reader.GetInt64(0), reader.GetInt64(1), reader.GetChar(2), reader.GetInt16(3), reader.GetDateTime(4));
          feedbacks.Add(feedback);
        }
        return feedbacks;
      }
    }
  }
}