Firebird Fluentdata

Fluentdata 支持很多种数据库驱动，但对Firebird不友好，不过可以使用DB2Provider来操作大部分功能，

例如： new DbContext().ConnectionString(connectionString, new DB2Provider(), FirebirdClientFactory.Instance) 

但是对于Insert后获取自动生成的最大Id，就会报错，因为DB2的语法不通。

起初怀疑获取最大Id方法是通过自动添加select max(id)来获取，这样就有可能访问量大时会存在错误。

好在官网有源码可以查看，结果不是的。

每种数据库都有insert后获取inserted记录的Id的方法，Firebird也不例外，语法是： insert A (x,x) values (x,x) returning id, x, x 和Oracle语法差不多。

那就果断添加一个 FirebirdProvider ，后续我们再查看下其他各数据库ExecuteReturnLastId的具体做法。

FirebirdProvider 代码：

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using FluentData.Providers.Common;
using FluentData.Providers.Common.Builders;

namespace FluentData
{
    public class FirebirdProvider: IDbProvider
    {
        public string ProviderName
        {
            get
            {
                return "FirebirdSql.Data.FirebirdClient";
            }
        }

        public bool SupportsOutputParameters
        {
            get { return true; }
        }

        public bool SupportsMultipleResultsets
        {
            get { return true; }
        }

        public bool SupportsMultipleQueries
        {
            get { return true; }
        }

        public bool SupportsStoredProcedures
        {
            get { return true; }
        }

        public bool RequiresIdentityColumn
        {
            get { return false; }
        }

        public IDbConnection CreateConnection(string connectionString)
        {
            return ConnectionFactory.CreateConnection(ProviderName, connectionString);
        }

        public string GetParameterName(string parameterName)
        {
            return "@" + parameterName;
        }

        public string GetSelectBuilderAlias(string name, string alias)
        {
            return name + " as " + alias;
        }

        public string GetSqlForSelectBuilder(SelectBuilderData data)
        {
            var sql = "";
            sql = "select " + data.Select;
            sql += " from " + data.From;
            if (data.WhereSql.Length > 0)
                sql += " where " + data.WhereSql;
            if (data.GroupBy.Length > 0)
                sql += " group by " + data.GroupBy;
            if (data.Having.Length > 0)
                sql += " having " + data.Having;
            if (data.OrderBy.Length > 0)
                sql += " order by " + data.OrderBy;
            if (data.PagingItemsPerPage > 0
                && data.PagingCurrentPage > 0)
            {
                sql += string.Format(" rows {0} to {1}", data.GetFromItems(), data.GetToItems());
            }

            return sql;
        }

        public string GetSqlForInsertBuilder(BuilderData data)
        {
            return new InsertBuilderSqlGenerator().GenerateSql(this, "@", data);
        }

        public string GetSqlForUpdateBuilder(BuilderData data)
        {
            return new UpdateBuilderSqlGenerator().GenerateSql(this, "@", data);
        }

        public string GetSqlForDeleteBuilder(BuilderData data)
        {
            return new DeleteBuilderSqlGenerator().GenerateSql(this, "@", data);
        }

        public string GetSqlForStoredProcedureBuilder(BuilderData data)
        {
            return data.ObjectName;
        }

        public DataTypes GetDbTypeForClrType(Type clrType)
        {
            return new DbTypeMapper().GetDbTypeForClrType(clrType);
        }

        public object ExecuteReturnLastId<T>(IDbCommand command, string identityColumnName = null)
        {
            if (identityColumnName == null) identityColumnName = "id";

            string temp = command.Data.Sql.ToString().Trim();
            if (temp.EndsWith(";"))
                command.Data.Sql.Clear().Append(temp.Substring(0, temp.Length - 1));

            command.ParameterOut("FluentDataLastId", command.Data.Context.Data.FluentDataProvider.GetDbTypeForClrType(typeof(T)));
            command.Sql(" returning " + identityColumnName);


            object lastId = null;

            command.Data.ExecuteQueryHandler.ExecuteQuery(false, () =>
            {
                command.Data.InnerCommand.ExecuteNonQuery();

                lastId = command.ParameterValue<object>("FluentDataLastId");
            });

            return lastId;
        }

        public void OnCommandExecuting(IDbCommand command)
        {
        }

        public string EscapeColumnName(string name)
        {
            return name;
        }
    }
}

具体调用方法： new DbContext().ConnectionString(connectionString, new FirebirdProvider(), FirebirdClientFactory.Instance) 

DB2做法是： select IDENTITY_VAL_LOCAL() as LastId from sysibm.sysdummy1; 

Mysql做法是： select LAST_INSERT_ID() as `LastInsertedId` 

Oracle做法是：sql结尾添加输出参数   returning id into :FluentDataLastId 

PgSql做法是： select lastval(); 

Sqlite做法是： select last_insert_rowid(); 

SqlCe做法是： select cast(@@identity as int) 

SqlServer做法是： select SCOPE_IDENTITY() 

 ===================================================

实体方法扩展：

    public class Base
    {
        public long id { get; set; }
    }

    public class T_session : Base
    {
        public string token { get; set; }
        public long? user_id { get; set; }
        public DateTime login_time { get; set; }
        public DateTime? logout_time { get; set; }
        public DateTime update_time { get; set; }
    }

    public static class DbExt
    {
        public static long Insert(this Base enty, IDbContext db)
        {
            return db.Insert(enty.GetType().Name, enty).AutoMap(x => x.id).ExecuteReturnLastId<long>();
        }

        public static int Update(this Base enty, IDbContext db)
        {
            return db.Update(enty.GetType().Name, enty).AutoMap(x => x.id).Where(x => x.id).Execute();
        }

        public static int Delete(this Base enty, IDbContext db)
        {
            return db.Delete(enty.GetType().Name, enty).Where(x => x.id).Execute();
        }

    }