Auto number generation using C# and SQL

Auto number generation using C# and SQL
------------------------------------------------------

Req:  Create a Autonumber for Particular Application form(Eg:Order or Quote)

Approach:
-------------
1.Create a Separate autonumber configuration table which looks like below

AutoNumConfig table
--------------------------
 
AutoNumberId  AutoNumber   FormNameorFormId  
-------------------------------------------
   1             ORD-0002     Order or 1066
   2             QUO-0002     Quote or 1067


2.Create Transaction table for Application  Form(Eg:Order/Quote) which looks like below

OrderTable
--------------

OrderId   OrderName  OrderAutoNum
-------------------------------------------
  1       Spartan      ORD-0001
  2       Xamarin      ORD-0002


QuoteTable
--------------

QuoteId   QuoteName  QuoteAutoNum
-------------------------------------------
  1       Spartan      QUO-0001
  2       Xamarin      QUO-0002


3.When User X logged in and Create a New Order/Ouote for the First time
 
4.Check the table Contains order/quote has value,if not it should be first Value which means ORD-00001

5.If User X logged in second time and create a new Order.

6.Reterive the newly generated autonumber from autonumconfig table and increment the value by 1 and update the AutoNumConfig table and lock the table.
  reason behind to lock the table is, another user to prevent to read the previous generated autonumber.If another user logged in and try to create order,in this case the user gets error as it locks.

7.Update the newly generated autonumber in the transation table.(Eg:ORD-00002)

8.After Successful inserted record in Order table release the lock of autonumconfig table.

9.U can use DB lock or C# lock.My preference is best to go C# lock



Advantage of use above Design
---------------------------------------

1.Extensiblity,Suppose if req change like,want to generate autonumber based on Organization
like below

  Eg:  ORD-ORG1-00001
         ORD-ORG2-00001
         ORD-ORG1-00002

the above design will be suitable and best way to go.

2.You can Generate new auto number for any new Form as autonumber configuartion table is separate from transacation table which is loosly coupling.

3.Also there is concept called Autonumber generate based on primary key in SQL,this will generate automatically without any hard stuff,but the thing is Point no:1 requriment will not satisfy if it change based on Organization.


                                          Happy Coding!!!!

 

Comments

Post a Comment

Popular posts from this blog

Asp.net- Encrypt and Decrypt connection strings in web.config file

Image
In this article I will explain how to encrypt or decrypt connectionStrings in web.config file using asp.net. If we are using applications in our internal servers with security then it’s ok if we deploy our applications in shared host environment then we have chance to arise security problems to avoid these problems asp.net 2.0 provided built in protected configuration model functionality to encrypt or decrypt few sections of web.config file those are RSAProtectedConfigurationProvider :   This is default provider and uses the RSA public key encryption algorithm to encrypt and decrypt data. DataProtectionConfgurationProvider : This provider uses windows data protection application programming interface to encrypt and decrypt the data. The encrypting and decrypting of connection strings in web.config file will do by using aspnet_regiis.exe  command line tool and  code behind . First Method : First we will do encryption and decryption using  aspnet_regiis.exe  comman
Read more

CRM Automate build Solution using Powershell commands

In CRM if there is any solution movement from other enviornment like DEV,PROD,STG we use to export the solution and import the solution to the respective environment by logging into MS Dynamics CRM. This will tends to extra effort to do manually for developers or release managers. So what if release user or test user can build and deploy the Solution deployment without CRM intervention(CRM loggin in) We need to do some automate build and deployment. In this article i write about how Import/Export solution automate to the various environment without intervention of logging into MS Dynamics CRM. XRM CI Framework,this is one tool use to automate the build in CRM       Below link is for download the XRM CI Framework https://xrmciframework.codeplex.com/releases/view/125516 Download and extract the ZIP file . First HelloWorld example “WhoAmIRequest” 1.Open "Powershell" and "Run as a Administrator" 2.Navigate to Extrac
Read more

Access the fields on Quick View Form – Dynamics CRM 2015

Image
Quick view forms in Dynamics CRM 2013 allow you to display all the related entity information. Is there a way to retrieve the field value on Quick View Form using JavaScript without additional OData call to the associated entity? The answer is Yes and here is the sample of the form and JavaScript:   Here is the Following steps to get the value from the QuickView form     Sample Quick Form     Eg: We need to get the attribute value called AccountNumber from QuickForm Step 1: Open the Customization->Entity->Form     Step 2:  Double click the customer pane window to see the properties and note the quickformname,and related entity name.Click Edit.       Step 3:   Our goal is to get the attribute name of AccountNumber.   After Click Edit the following dialog box with Field Properties and note down the attribute name Code: below is the format //QuickFormname_QuickFormname_EntityName_AttributeName if(Xrm.Page.getControl('customerpane_q
Read more