Fix Issue #1080

[mdockal]

Fix for issue #1080

Sqlite doesn't have a table called INFORMATION_SCHEMA.TABLES, instead you must use SQLITE_MASTER table. Updated the code to determine if it IsSqliteConection and append the correct table name. Also SQLITE_MASTER does not have a column name called "TABLE_NAME", fixed that issue as well.

[NickCraver]

I understand the issue here (agree we need a fix), but historically the detection mechanism hasn't worked out well.

An aside/FYI on the technical side: instead of creating a string with every comparison with .ToLower(), instead you can do:

string.Equals(_connection.GetType().Name, "sqliteconnection", StringComparison.OrdinalIgnoreCase) 

...but the main issue is how the detection works in general. For example, you can wrap a SqliteConnection in...whatever you want really. MiniProfiler and other libs wrap the connection to track which connections are issues. In the MiniProfiler example, the type of DbConnection would be ProfiledDbConnection...and we'd still fail.

We have a similar problem in Dapper.Contrib with the SQL adapters so we need to find a better solution. Some ideas I've considered:

• Go into a "mode" for a provider based on exceptions thrown, e.g. "SqliteException" - we listen for these and change the type.
  • But that has to be stored somewhere, and what if you change connections (in Contrib) or have multiple types? Not safe.
• try/catch here, and upon failure (that exception type...we could switch on it), we run the correct one.
  • But that's pretty slow and wasteful

@mgravell any thoughts here?

[mdockal]

@NickCraver I was not aware of the String.Equals() Method Overload. I didn't realize that there was a problem with _connection.GetType().Name. It makes sense if a connection inherits from the framework (see example below) connections this method will fail.

I believe something like this would work because I am using it in another project I am working on:

  var connection = new DerivedSqliteConnection(@"Data Source=test.db;Cache=Shared"); 
  var sqlFlavor = SqlFlavor.For(connection);  //Returns SqliteFlavor
  var builder = new StringBuilder("select 1 from ");              
  builder.Append(sqlFlavor.SchemaTableNameString);                
  if (!string.IsNullOrEmpty(schemaName)) builder.Append("TABLE_SCHEMA = @schemaName AND ");
  builder.Append(sqlFlavor.SchemaTableColumnNameString);

The problem is you need to traverse the Type.BaseType all the way down to object and see if it inherits from one of the framework connections.

    public interface ISqlFlavor
    {
        string Name { get; }
        string SchemaTableNameString { get; }
        string SchemaTableColumnNameString { get; }
    }

    public abstract class SqlFlavor : ISqlFlavor
    {

        protected static readonly Dictionary<string, ISqlFlavor> LanguageDictionary = new Dictionary<string, ISqlFlavor>(StringComparer.OrdinalIgnoreCase)
        {
            ["sqlconnection"] = new SqlServerFlavor(),
            ["sqlceconnection"] = new SqlServerCeFlavor(),
            ["npgsqlconnection"] = new PostgreSqlFlavor(),
            ["sqliteconnection"] = new SqliteFlavor(),
            ["mysqlconnection"] = new MySqlFlavor()
        };

        public abstract string Name { get; }

        public virtual string SchemaTableNameString => "INFORMATION_SCHEMA.TABLES where ";

        public virtual string SchemaTableColumnNameString => "TABLE_NAME = @name";

        public static ISqlFlavor For(string name, bool throwExceptionIfNotFound = false)
        {
            if (LanguageDictionary.ContainsKey(name)) return LanguageDictionary[name];
            if (throwExceptionIfNotFound)
            {
                throw new InvalidOperationException(string.Format("The SqlFlavor {0} does not exist.", name));
            }
            return null;           
        }

        public static ISqlFlavor For(IDbConnection connection)
        {           
            var flavor = default(ISqlFlavor);
            var connectionType = connection.GetType();
            // Traverse connection type and base types to find a connection.
            while (connectionType != null)
            {
                flavor = For(connectionType.Name);
                if (flavor != null) break;
                connectionType = connectionType.BaseType;
            }
            return flavor;           
        }
        
    }

    public class SqliteFlavor : SqlFlavor
    {
        public override string Name => "Sqlite";

        public override string SchemaTableNameString => "SQLITE_MASTER where ";

        public override string SchemaTableColumnNameString => "NAME = @name";
    }

    public class SqlServerFlavor : SqlFlavor
    { 
        public override string Name => "SqlServer";
    }

    public class PostgreSqlFlavor : SqlFlavor
    {
        public override string Name => "PostgreSql";
    }

    public class MySqlFlavor : SqlFlavor
    {
        public override string Name => "MySql";
    }

    public class SqlServerCeFlavor : SqlFlavor
    {
       public override string Name => "SqlServerCe";
    }

Each flavor or dialect of SQL can have variations in syntax. SqlFlavor can be used to encapsulate these variations and it is fully testable. I noticed that you have something similar in Dapper.Contrib.