How can I use an existing key table to generate the entity Ids

I am working with an existing database which has its own 'key' table with an entry for each table in the database and is used to generate each table's unique Ids (via call to stored procedure). Is there a (generalized) way I can utilize this existing key generation table when I create any new entity and save it?  If so, please provide details as I am quite new to C#, .Net and LightSpeed.

You can use an existing key table by setting LightSpeedContext.NamingStrategy to a custom INamingStrategy.  You will need to implement the GetTableName and GetColumnName members, and map the "KeyTable" and "NextId" names:

public class MyNamingStrategy : INamingStrategy {
    public string GetTableName(string defaultName, string className) {
      if (className == null && defaultName == "KeyTable")
        return "MyKeyTable";
      return defaultName;
    }
    public string GetColumnName(string defaultName, string fieldName) {
      if (fieldName == null && defaultName == "NextId")
        return "MyNextIdColumn";
      return defaultName;
    }
    // all other members should just return defaultName
}

However, this will work only for a single key table with a single sequence.  We don't support "key table per table" though this is on the wish list.  Also, we don't support stored procedure access to the key table.  You can implement this by overriding GeneratedId() in the entity class to call the stored procedure:

protected override object GeneratedId() {
  var dbFactory = this.UnitOfWork.Context.DataProviderObjectFactory;
  using (IDbCommand cmd = dbFactory.CreateCommand()) {
    cmd.CommandText = "GetKey";  // your sproc name here
    cmd.CommandType = CommandType.StoredProcedure;
    // set up parameters if required
    this.UnitOfWork.PrepareCommand(cmd);  // very important!
    object id = cmd.ExecuteNonScalar();
    return id;
  }
}

Please note I have not tested this code, and that I've omitted all error checking.

Could you please clarify what you mean when you say you don't support "key table per table"?

What we have is a ONE MasterKey table with a row for each of the other tables, eg

  columns:   Id  TableName   Current_Val    Increment_val   etc

                   1      TableA                3                 1
                   2      TableB                6                 1
                   3      TableC            204                 1

1)      Is this the scenario you are saying is NOT supported? If it is not, can you suggest an approach that would work with the existing database or what changes could be made?

2)      If it IS supported, additionally we do in fact use a stored procedure to access the MasterKey table to get NextId for each table. It takes 'TableName' as in parameter and 'NextId' as out parameter. How would I obtain the 'TableName' to pass to the stored procedure in the GeneratedId( )  method?

thanks in advance

1. The scenario you describe is *not* supported -- at least, not built in.  (By "key table per table" I was referring to multiple key tables, rather than multiple key sequences in a single table like you have.  But it doesn't really matter because neither of those is built in.)

2. You can get the table name from the entity type:

public static string GetTableName(Entity entity) {
  object[] attrs = entity.GetType().GetCustomAttributes(typeof(TableAttribute), false);
  if (attrs.Length > 0)
    return ((TableAttribute)attrs[0]).Name;
  else
    return entity.GetType().Name;
}

(This assumes you are not using the automatic pluralisation option.)

Then, in GeneratedId():

var tableParameter = dbFactory.CreateParameter("TableName", GetTableName(this));
cmd.Parameters.Add(tableParameter);

Having some problems overriding the GeneratedId( ) method as I was expecting to be able to do ...

We have a large number of tables from an existing database added to the LightSpeed designer.
The .cs file generated by the designer has the entities corresponding to the tables inheriting from Entity<int>, eg

public partial class ModelClassForDBTable1 : Entity<int>
{
...
}

public partial class ModelClassForDBTable2 : Entity<int>
{
...
}

etc, for each of the N tables added to the model.

I can override GeneratedId() for a specific model class, eg

    public partial class ModelClassForDBTable1 : Entity<int>
    {
        protected override Object GeneratedId()
        {
  .
  .
  .

...but that would mean I would have to do this for EVERY table in the model? Is that correct?

Is there a way to override GeneratedId( ) in a common place for all the classes generated by the model?

I tried the following ...

    public partial class Entity<T> : Entity
    {
        protected override Object GeneratedId()
        {
            System.Reflection.MemberInfo info = typeof(Customertrade);
  .
  .
  .

... but that gave me the following compiler errors:

1)   'Mindscape.LightSpeed.Entity<T>' does not implement inherited abstract member 'Mindscape.LightSpeed.Entity.SetIdInternal(object)'
2)   'Mindscape.LightSpeed.Entity<T>' does not implement inherited abstract member 'Mindscape.LightSpeed.Entity.get_IdInternal()'

 

 

First, create a base class in code, and override GeneratedId() in that, e.g.:

namespace MyProject {
  public class EntityBase : Entity<int> {
    protected override object GeneratedId() { ... }
  }
}

Now we want to make the real entities inherit from EntityBase instead of from Entity<int>.  To do this, we need to do two things: first, tell the designer that EntityBase exists; and second, tell each entity to derive from EntityBase.

To tell the designer that EntityBase exists, go into the LightSpeed Model explorer (View > Other Windows > LightSpeed Model if it's not already open).  Right-click the model (the root of the tree view) and choose Add New External Class Reference.  In the Properties window, enter the class name (e.g. MyProject.EntityBase) and select the identity type (Int32).

To tell an entity to derive from EntityBase, select the entity, go to the Properties window, and drop down the Base Class setting.  Select MyProject.EntityBase as the base class.

Your entities now derive from EntityBase and will therefore get the overridden version of GeneratedId().  Note that as you add new entities you will need to set their Base Class to EntityBase too (it isn't currently possible to automate this, though it is on the wish list).

If you need different entity types to have different identity types, that's also possible: declare EntityBase as a generic type (public class EntityBase<TId> : Entity<TId>).  In this case when you create the External Class Reference you must set the Identity Type to Generic.

Hello,
Sorry for the lapse ... I have had some diversions but I am still trying to work my way through this scenario. Most recently I have been trying to get my GenerateId( ) method to work as you outlined in your first response in this thread where GenerateId( ) invokes a stored procedure. Our stored procedure takes a table name as input parameter and has an output parameter for the next database ID. I have tried various things and I can't seem to get the call to the stored procedure to work.

Here is the error I'm getting:

------ Begin Error -------
ORA-06550: line 1, column 33:
PLS-00103: Encountered the symbol ":" when expecting one of the following:

   ( - + case mod new not null <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> avg
   count current exists max min prior sql stddev sum variance
   execute forall merge time timestamp interval date
   <a string literal with character set specification>
   <a number> <a single-quoted SQL string> pipe
The symbol "( was inserted before ":" to continue.
ORA-06550: line 1, column 56:
PLS-00103: Encountered the symbol ":" when expecting one of the following:

   ( - + case mod new not null <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> avg
   count current exists max min prior sql stddev su

------ End Error -----

My guess is I'm not setting up the parameters correctly especially in the case of the OUTPUT parameter but I wasn't able to find examples or a way to get more specific information about the error.

I get the dbFactory as in your first response:
     var dbFactory = this.UnitOfWork.Context.DataProviderObjectFactory;

Then I make call similar to
     IDbDataParameter aParam = dbFactory.CreateParameter( ......
to create the parameters. Then I add them to the command I created from dbFactory similar to
     cmd.Parameters.Add( .......

Would it be possible for you to provide more details on how to set up the parameters for the stored procedure? 

Also, please note in your first reply you used "cmd.ExecuteNonScalar()" but I couldn't find that method .... I did find/try cmd.ExecuteNonQuery() and cmd.ExecuteScalar(). Is one of those the correct one?

Thanks in advance for your assistance.

Doh!  Yes, I meant ExecuteScalar(), though since your sproc returns the result via an out-parameter I think you will need to use ExecuteNonQuery() and an out-parameter instead.

Here's an example of how to call a stored procedure from GeneratedId() -- I used SQL Server because I'm more familiar with that but hopefully it will be easy to translate to Oracle.

First, here's my (obviously dummy!) stored procedure:

CREATE PROCEDURE GetCustomId
  @tableName NVARCHAR(50)
AS
BEGIN
  SELECT 12345 AS NextId
END

Note that I'm using a SELECT to return my desired ID as the single column of a single-row recordset; again, your sproc definition is slightly different but hopefully you can see how it translates.

Now here's my GeneratedId() implementation for calling it:

protected override object GeneratedId()
{
  using (IDbConnection conn = UnitOfWork.Context.DataProviderObjectFactory.CreateConnection())
  {
    conn.ConnectionString = UnitOfWork.Context.ConnectionString;
    conn.Open();

    using (IDbCommand cmd = conn.CreateCommand())
    {
      cmd.CommandText = "GetCustomId";
      cmd.CommandType = CommandType.StoredProcedure;

      IDbDataParameter tableName = cmd.CreateParameter();
      tableName.ParameterName = "tableName";
      tableName.Value = GetType().Name;
      cmd.Parameters.Add(tableName);

      UnitOfWork.PrepareCommand(cmd);

      return cmd.ExecuteScalar();
    }
  }
}

This should all be pretty self-explanatory, again with the caveat that ExecuteScalar is returning the sole column of the sole row of the SELECT statement.  Because your sproc returns the value via an out-parameter, you'll instead need to create another parameter, with its direction set to Out, and get the returned ID from that.  In that case I believe you would need to call ExecuteNonQuery instead of ExecuteScalar.  Something like this (not tested):

      IDbDataParameter tableName = cmd.CreateParameter();
      tableName.ParameterName = "tableName";
      tableName.Value = GetType().Name;
      cmd.Parameters.Add(tableName);

      IDbDataParameter result = cmd.CreateParameter();
      result.ParameterName = "result";
      result.DbType = DbType.Int32;
      result.Direction = ParameterDirection.Output;
      cmd.Parameters.Add(result);

      UnitOfWork.PrepareCommand(cmd);

      cmd.ExecuteNonQuery();

      return result.Value;

The error that you're seeing at the moment is a syntax error from Oracle, which may mean that you're currently using incorrect syntax for the parameter names, or possibly including parameter names directly in the SQL.

Hope this helps -- let me know if you still see issues.

Made some great progress with the information in your last reply! Using the example in your last reply as a guide, I was able to get my custom GenerateId() method to work for a simple test case using the stored procedure in my Oracle test DB that gives back the nextId as an out parameter.

I think one of the break throughs was the change you made from your original code...

   var dbFactory = this.UnitOfWork.Context.DataProviderObjectFactory;
        using (IDbCommand cmd = dbFactory.CreateCommand()) {

...to the following code in your updated example which creates the command from the connection vs the DataProviderObjectFactory:

   using (IDbConnection conn =
              UnitOfWork.Context.DataProviderObjectFactory.CreateConnection())
   {
      conn.ConnectionString = UnitOfWork.Context.ConnectionString;
      conn.Open();

      using (IDbCommand cmd = conn.CreateCommand())
      {
             .
             .
             .

Before I got your last reply I had come up with another version of GenerateId( ) which follows and seemed to sort of work except it did seem to 'lose' Ids, ie if last insert was say Id 5 the next one would be 7 instead of 6. Not going to use this version but just wondered if you had any comments ...

        protected override Object GeneratedId()
        {
            Int32 outIdValue = 0;
            List<ProcedureParameter> parmList = new List<ProcedureParameter>();

            string tableName = CrudUtil.GetEntityTableName(this);
            parmList.Add(new ProcedureParameter("aTableName", tableName));

            ProcedureParameter outId = new ProcedureParameter("aNewId", outIdValue,                 
                         ParameterDirection.Output, null, null);
            parmList.Add(outId);

            var procedureQuery = new ProcedureQuery("PK_GENERATOR", parmList.ToArray());

            var result = this.UnitOfWork.Calculate(procedureQuery);

            return outId.Value;
        }

Ivan, thanks for all your help! It's been a great learning experience. Now I will have to see if the
new version of GenerateId( ) method works with our MS SQL database as well as the Oracle database :)

 Hello,

I have been doing some testing with the custom GenerateId( ) method which resulted from your example and I'm seeing a behavior I can't explain and was hoping you might have some thoughts. The procedure is included below. If I run my test (which adds a single new entity) and have NO breakpoints in the GenerateId( ) method, it creates a new row in the database with the next available Id from the stored procedure as expected, ie. if current Id value in our key table is say 5 for that entity type, the new entity is created with Id of 6. However if I put a breakpoint in GenerateId( ) and the current Id value in our key table is 5, I proceed from the breakpoint (which occurs only once) and the new entity is created with Id of 7. It appears as if the stored procedure gets called twice and an Id is lost, but I only get one breakpoint - and at that point in time an Id (6) has already somehow been consumed and appears in the debugger as the value of 'this.Id'. So when I proceed the stored procedure is called, next Id (7) is returned and used apparently inside of 6. I'm at a loss as to why I only see this behavior when I have a breakpoint or how to explain the 'lost' Id. Is there a programming error, timing consideraton, or explanation you can think of that might shed some light?

Thanks in advance

        protected override Object GeneratedId()
        {

            using (IDbConnection conn = UnitOfWork.Context.DataProviderObjectFactory.CreateConnection())
            {
                conn.ConnectionString = UnitOfWork.Context.ConnectionString;
                conn.Open();
                Object returnId = null;

                using (IDbCommand cmd = conn.CreateCommand())
                {
                    // Set up a command to call the stored procedure
                    cmd.CommandText = "PK_GENERATOR";
                    cmd.CommandType = CommandType.StoredProcedure;

                    // Stored procedure has two parameters: "aNewId" (out) and "aTableName" (in)
                    IDbDataParameter idParm = cmd.CreateParameter();
                    idParm.ParameterName = "aNewId";
                    idParm.DbType = DbType.Int32;
                    idParm.Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(idParm);

                    string tableName;

                    // CAVEAT: works if NOT using LightSpeed pluarlization option
                    object[] attrs = this.GetType().GetCustomAttributes(typeof(TableAttribute), false);
                    if (attrs.Length > 0)
                        tableName = ((TableAttribute)attrs[0]).Name;
                    else
                        tableName = this.GetType().Name;

                    IDbDataParameter tableNameParm = cmd.CreateParameter();
                    tableNameParm.ParameterName = "aTableName";
                    tableNameParm.Value = tableName;

                    cmd.Parameters.Add(tableNameParm);

                    // important
                    UnitOfWork.PrepareCommand(cmd);

                    // Execute procedure and get next Id
                    cmd.ExecuteNonQuery();
                    returnId = idParm.Value;

                }

                conn.Close();

                // return the next Id for this Entity type
                return returnId;
            }
        }

I think what is happening here is that when you break in the debugger, Visual Studio is displaying various auto and local variables in the watch window.  For each thing it displays, it calls ToString() to determine how to display that thing.

In particular, it displays the this reference (i.e. the entity whose GeneratedId() method is being called), and renders the value by calling ToString() on this.  The default implementation of Entity.ToString() is:

entityType [Id=id, EntityState=state]  (e.g. Book [Id=123, EntityState=New])

So viewing the entity in the debugger causes an ID to be generated for that entity.  Normally this isn't a problem, but when you're debugging the ID allocation routine itself, it results in a confusing extra call to that routine -- hence the appearance of a "missed" ID as the debug view assigns an ID which is then overwritten when the "real" invocation of GeneratedId() returns.

One solution is to override ToString() on your entity type, and return something that doesn't use the ID, but this is a bit of a blunt instrument.  Another is to set DebuggerDisplayAttribute on your entity type, again providing a format string that doesn't use the ID.  In both cases you can remove this once you've debugged the GeneratedId() method if you want to go back to the normal debugger display.

Hello again,

I have been developing using the method override for GenerateId( ) that I provided in my last post on this thread and which is derived from the examples you posted in this same thread. I recently encountered a problem with the overridden GenerateId( ) when it comes to transactions. I would like to use TransactionScope but when I do the stored procedure called by GenerateId( ) doesn't return a next Id value, it stays 0. If however instead of using TransactionScope, I use the alternate approach discussed in the LightSpeed help, the next Id value comes back fine. Do you possibly have any idea why the stored procedure does not work the same when TransactionScope is used? I have included code for a simplified version of each scenario below:

SCENARIO 1 (works):

   using (var uow = context.CreateUnitOfWork())
   {

      using (var transaction = uow.BeginTransaction())
      {

         // create a new entity to insert
         Note newDbNote = new Note
         {
            Id = 0,
            Subject = "Note subject",
            Text = "Note text"
         };

         uow.Add(newDbNote);

        // The nextId after the stored procedure is called is
        // correct and after Add is called the entity's Id is set to it

         uow.SaveChanges();
         transaction.Commit();
      }
   }

SCENARIO 2 (does not work):

   using (var transactionScope = new TransactionScope())
   {

      using (var uow = context.CreateUnitOfWork())
      {
         // create a new entity to insert
         Note newDbNote = new Note
         {
            Id = 0,
            Subject = "Note subject",
            Text = "Note text"
         };

         uow.Add(newDbNote);

        // The nextId after the stored procedure is called
        // is 0, so after Add is called the entity's Id stays 0 !

         uow.SaveChanges();
      }

      transactionScope.Complete();
   }

Any idea why next Id doesn't seem to get returned by the stored procedure for Scenario 2??

Some additional information is that I'm running on a single XP machine with a local Oracle 10g database. Initially when I tried to use TransactionScope I was getting an error that oramts.dll was needed so I loaded that .dll from the Oracle installation DVD.

Any thoughts would be greatly appreciated.

According to our test suites, Oracle doesn't support CLR transaction scopes, and only works with BeginTransaction() (which maps down to OracleConnection.BeginTransaction()).  Our test suites may be behind the times here, so I'm open to being corrected, but that's our experience.

That said, I'd expect the this lack of support to come in the form of "transaction is ignored" rather than returning 0.  So I'm not 100% sure that this is the issue.  But it does make me think that the problem is at the ADO.NET / Oracle level rather than the LightSpeed level.

The table we are using to generate new id is bb_int_ID_Uitgifte

 

 Error     1          'DHVCortexService.MyNamingStrategy' does not implement interface member 'Mindscape.LightSpeed.INamingStrategy.GetMultiSequenceName(string, System.Type)'   C:\inetpub\wwwroot\DHVCortexService\DHVCortexService\Business models\putmodel.cs      1039     16        DHVCortexService

 

This is the code which i am using in my application.

 

 public class MyNamingStrategy : INamingStrategy {
    public string GetTableName(string defaultName, string className) {
      if (className == null && defaultName == "KeyTable")
        return "bb_int_ID_Uitgifte";
      return defaultName;
    }
    public string GetColumnName(string defaultName, string fieldName) {
      if (fieldName == null && defaultName == "NextId")
        return "OBJ_ID";
      return defaultName;
    }
}

 Does anybody have any suggestion about how to solve this kind of problems in LightSpeed.

which contains single field “OBJ_ID”. I have changed the above code as shown below. Can you please suggest me whether the code is correctly modified, if yes then where can I place this piece of code in our application. Now I am placing this code in a separate class, then i am getting following error:

Hi,

As you're implementing an interface, you need to implement all members - not just the ones you want to alter. Visual Studio will let you instruct it to stub out all the members, but in case you need a reference:

http://www.mindscape.co.nz/products/lightspeed/Help/html/AllMembers_T_Mindscape_LightSpeed_INamingStrategy.htm

In most cases, you can just return the defaultValue if you're not wanting to modify the implementation. You can store this class anywhere, and then assigning to your LightSpeedContext.NamingStrategy. Something like:

MyLightSpeedContext.NamingStrategy = new MyNamingStrategy();

This isn't an issue with LightSpeed, but just how interfaces work in .NET. 

I hope that helps.

Thank you for the reply. Now we are able to generate an id using our existing table. But the sequence to generate a new id is incremented by 10 default. We have created a new sequence "increase_obj_id" and pass this sequence into GetSequenceName property as shown below;

public string GetSequenceName(string defaultName)

        {
            return "increase_obj_id";  

      }

The program is not coming to this code to take sequence table. Can anyone has solution for this?

Have you assigned the LightSpeedContext.NamingStrategy?

_context.NamingStrategy = new MyNamingStrategy();

 Thanks for the reply. We are able to solve this issue.