locked
How can I create this type of key? RRS feed

  • Question

  • User1001065250 posted

    I am tasked with creating a new web application as a replacement for a MS Access form application. The existing application uses a primary key on its main table that is in the following format Gcurrentyear-incrementingnumber for example G2019-02115. I need to keep this format as I will be importing all existing data (around 9000 records) into the new application. This is also a foreign key on a number of other tables. I would like to use EF for the new application but am concerned with how I could create primary keys that would fit my requirement. Does anyone have any idea on how I could accomplish this?

    Thursday, February 14, 2019 6:47 PM

All replies

  • User475983607 posted

    I would like to use EF for the new application but am concerned with how I could create primary keys that would fit my requirement.

    Write code to generate the key; TSQL or C#.  Can you explain why you cannot meet this requirement?  Can you show the code you have written and explain the problem you are encountering?

    Thursday, February 14, 2019 6:58 PM
  • User1120430333 posted

    IMO, you would have to use EF code first for this and make the virtual model classes manually.

    You would be able to make your own primary-keys.  

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string FooId { get; set; }
    

    For your increamentingnumber as part of the primary-key, you could do this.

    You have a Count table that has a table column call it CountNo , a primary-key as an Integer that is an  Identity auto-increament column,  and you have a Dummy string column on the table. You're going to make a virtual model class for the Count table record call it Counter.. 

    So on the insert of a new primary record in the application, you first make a new Counter object, put some dummy string data into the Dummy column of the object, and you insert the Count object into the table. EF is going to insert a new Count record into the table, an integer auto incremented Identity number is going to be assigned to the record as the primary-key. The primary-key of the inserted record is going to be populated back to primary key property of the Counter object sitting there in memory after the SaveChanges()/

    You got the number in Count object from the Count table sitting there in memory  so that you can formulate your primary-key of "G2019" + Count.CountNo.ToString() and inset the record in your primary table. The Identity column for the Count table can be started at 2116 as an example.

    You would load the primary table with existing records, and then let the Web application do its thing.

    I did regional counters and the Count table functionality for each region  using EF  for records being inserted into a Customer table that had a Reginal column on the table. The whole thing was put into a System.Transaction scope. However, if a exception was thrown, the count for the auto increamented count numner used was gone even on a rollback with count record and all rolled back,  and the next sequential count number  was ready to be used.

    It's just a way you can do it with no problems I think.

     

    Thursday, February 14, 2019 8:16 PM
  • User1001065250 posted

    I like your suggestion but perhaps I failed to mention that the 2019 in G2019 is the current year. So next year the records would start again at G2020-00001. I was thinking that perhaps I could not auto increment on the database and rather get the year and the MAX in the db and then increment in my code. Would something like that work?

    Tuesday, February 19, 2019 5:36 PM
  • User475983607 posted

    I like your suggestion but perhaps I failed to mention that the 2019 in G2019 is the current year. So next year the records would start again at G2020-00001. I was thinking that perhaps I could not auto increment on the database and rather get the year and the MAX in the db and then increment in my code. Would something like that work?

    Yes, design and write code to meet the requirement.  I assume the existing VBA code can be used as a template.  I would write write the code in TSQL.  Either a stored procedure, a trigger, a function.  For example.

    IF OBJECT_ID('tempdb..#AccessDb') IS NOT NULL
    	DROP TABLE #AccessDb
    
    CREATE TABLE #AccessDb (Id VARCHAR(11) PRIMARY KEY)
    
    INSERT INTO #AccessDb (Id)
    VALUES('G2018-02115'),('G2018-02116'),('G2018-02117')
    
    DECLARE @Id VARCHAR(11)
    SELECT @Id = MAX(Id) FROM #AccessDb
    SELECT @Id
    
    DECLARE @year INT 
    DECLARE @inc INT
    SELECT @year = SUBSTRING(@Id, 2, 4)
    SELECT @inc = SUBSTRING(@id, 7, 5)
    
    SELECT @year, @inc
    
    IF @year = DATEPART(year, GETDATE())
    	BEGIN
    		SET @inc = @inc + 1
    		INSERT INTO #AccessDb (Id) VALUES ('G' + CAST(@year AS VARCHAR(4)) + + '-' + RIGHT('00000' + CAST(@inc AS VARCHAR(5)),5))
    	END
    ELSE
    	BEGIN
    		INSERT INTO #AccessDb (Id) VALUES ('G' +  CAST(DATEPART(year, GETDATE()) AS VARCHAR(4)) + '-00001')
    	END
    
    SELECT * 
    FROM #AccessDb

    Tuesday, February 19, 2019 6:33 PM
  • User1120430333 posted

    I like your suggestion but perhaps I failed to mention that the 2019 in G2019 is the current year. So next year the records would start again at G2020-00001. I was thinking that perhaps I could not auto increment on the database and rather get the year and the MAX in the db and then increment in my code. Would something like that work?

    The problem as I see it is that the data increment number must be done by you in code and tracked instead of just letting the database do it by just adding a record to a count table and having the code to get the auto-incremented number that is tracked by the DB engine..

    About the year, you can do a Date.Now().ToString("yyyymmdd") to get date in a string format and use a Substring() to get the yyyy data out of the string data.

    So you have function you created in C# or VB that gets the next sequential number, do a Date.Now.ToString() to get the yyyy and you put the G in front of the string key and return the string key to be used for the object you are about to insert into the database using EF.

    It's that simple -- make a function and put the function and the insert of the object into the database in a System.Transaction scope.

    Tuesday, February 19, 2019 7:04 PM
  • User1120430333 posted

    One other thing, you should come away from using MS Access. Becuase IMO, it's not an enterprise level database solution suitable for a Web base solution, which would probably force you to start using datasets and datatables. I would also go with the ADO.NET Entity Framework.

    https://www.codingblocks.net/programming/boxing-and-unboxing-7-deadly-sins/

    https://dzone.com/articles/reasons-move-datatables

    http://lauteikkehn.blogspot.com/2012/03/datatable-vs-list.html

    You can use MS SQL Server Express or SQLite, becuase each work with EF. EF doesn't work Access.

    Tuesday, February 19, 2019 9:07 PM
  • User1001065250 posted

    Completely agree and this is the reason for this project to exist. We are moving applications off of MS Access to web/.Net. 

    Tuesday, February 19, 2019 9:48 PM
  • User1001065250 posted

    IF OBJECT_ID('tempdb..#AccessDb') IS NOT NULL
    	DROP TABLE #AccessDb
    
    CREATE TABLE #AccessDb (Id VARCHAR(11) PRIMARY KEY)
    
    INSERT INTO #AccessDb (Id)
    VALUES('G2018-02115'),('G2018-02116'),('G2018-02117')
    
    DECLARE @Id VARCHAR(11)
    SELECT @Id = MAX(Id) FROM #AccessDb
    SELECT @Id
    
    DECLARE @year INT 
    DECLARE @inc INT
    SELECT @year = SUBSTRING(@Id, 2, 4)
    SELECT @inc = SUBSTRING(@id, 7, 5)
    
    SELECT @year, @inc
    
    IF @year = DATEPART(year, GETDATE())
    	BEGIN
    		SET @inc = @inc + 1
    		INSERT INTO #AccessDb (Id) VALUES ('G' + CAST(@year AS VARCHAR(4)) + + '-' + RIGHT('00000' + CAST(@inc AS VARCHAR(5)),5))
    	END
    ELSE
    	BEGIN
    		INSERT INTO #AccessDb (Id) VALUES ('G' +  CAST(DATEPART(year, GETDATE()) AS VARCHAR(4)) + '-00001')
    	END
    
    SELECT * 
    FROM #AccessDb

    So in the context of my trying to use an EF Code First approach, how would you recommend I use SQL similar to this? For instance, would I stick this in a stored procedure and then call this in my Create method and return generated Id to use in my C# code? I am assuming you were suggesting to run this sql against a dummy table simply used for tracking the current key since I would be using Linq to insert the rest of the values into my records table.   

    Tuesday, February 19, 2019 10:05 PM
  • User475983607 posted

    So in the context of my trying to use an EF Code First approach, how would you recommend I use SQL similar to this? For instance, would I stick this in a stored procedure and then call this in my Create method and return generated Id to use in my C# code?

    A raw query would work.

    https://docs.microsoft.com/en-us/ef/ef6/querying/raw-sql

    I would use an insert trigger to set the key after importing the data.  That way the code is in one spot.   You'll probably want to wrap the code in a transaction.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017

    I am assuming you were suggesting to run this sql against a dummy table simply used for tracking the current key since I would be using Linq to insert the rest of the values into my records table.   

    No.  The code above is TSQL built from your requirements.  The temp table is used to test the code.

    Tuesday, February 19, 2019 10:36 PM
  • User1120430333 posted

    So in the context of my trying to use an EF Code First approach, how would you recommend I use SQL similar to this? For instance, would I stick this in a stored procedure and then call this in my Create method and return generated Id to use in my C# code? I am assuming you were suggesting to run this sql against a dummy table simply used for tracking the current key since I would be using Linq to insert the rest of the values into my records table.

    Linq only queries. Linq doesn't persist data to the database. EF looks at the EF entity you have populated for data persistence  and the state is set by you, or  in some cases, EF knows the state by state tracking. It depends upon how you are working with the EF entity as EF sets the state,  or you must set the state of the entity/object for add, update or delete.  EF generates the T-SQL needed to persist the entity/object to the database table when SaveChanges() is called within the Dbcontext in the C# code..

    Remember this with using EF in a Web based solution, becuase most likely, the EF entity or entities may be in a disconnected state since Web applications are stateless. You may have to set the EF entity's state in order to persist it.

    https://www.c-sharpcorner.com/UploadFile/d87001/connected-and-disconnected-scenario-in-entity-framework/

    If you have plans to come up with your own method to  track and supply the sequence numbers, then by all means, use a stored procedure to do so using EF code first. Myself, I don't use sprocs with an ORM unless there is some situation that is forcing me to use a sproc for something the EF cannot do, becuase an ORM is not panacea solution.

    IMO, using an sproc kind of defeats the purpose of using an ORM like EF, becuase the EF engine generates the T-SQL it submits to the DB engine, and the DB engine is using its internal sproc to run the submitted T-SQL the EF engine generated with all the benefits of using a sproc.

    Regardless of how you get the next sequence number by letting EF and the MS SQL Server DB engine supply the next sequential number by the method I have discussed, or you use EF Code First using a sproc, it's your choice either way works.

    Wednesday, February 20, 2019 12:23 AM