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 > )

                 sql += " where " + data.WhereSql;

             if (data.GroupBy.Length > )

                 sql += " group by " + data.GroupBy;

             if (data.Having.Length > )

                 sql += " having " + data.Having;

             if (data.OrderBy.Length > )

                 sql += " order by " + data.OrderBy;

             if (data.PagingItemsPerPage >

                 && data.PagingCurrentPage > )

             {

                 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(, temp.Length - ));

             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();

         }

     }

巴特西

Firebird Fluentdata

最新文章

热门文章