locked
How to generate Medical record number in c# for hospital database management system RRS feed

  • Question

  • Hi

    i am new to software side.i am working on hospital database management system project with front end visual studio (c#) and backend is sql-2008.

    i want a code to generate M.R number (medical record number) automatically as i click the save button.

    the M.r number should have some nomenclature like for example M.R number=859320

    where 8 represent gender,5 represent age,9 represent blood gp,3 represent in-patient etc

    i dont know how to code it to get this kindof M.R number.

    plx help...

    Saturday, July 12, 2014 11:52 AM

Answers

  • Hello,

    Adding to Joel's recommendations, some will use two trips to the database, one to insert, one to retrieve the new primary key. The following shows how to do one trip. Focus on I am using a connection and command where the SQL statement first does the insert then retrieves the new key which is returned via ExecuteScalar so ignore the variables around this.

    using (SqlConnection conn = new SqlConnection(Config.Instance.Connection))
    {
        conn.Open();
        try
        {
    
            sql = @"INSERT INTO [AppRole]
                    (
                        RoleName,
                        RoleDescription,
                        AppID
                    )
                    VALUES
                    (
                        @RoleName,
                        @RoleDescription,
                        @AppID
                    );
                    SELECT CAST(scope_identity() AS int);
                ";
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.Parameters.AddWithValue("@AppID", this.AppID);
    
                cmd.Parameters.AddWithValue("@RoleName", DataHelper.TrimToEmpty(this.Name));
                cmd.Parameters.AddWithValue("@RoleDescription", DataHelper.TrimToEmpty(this.Description));
    
                this.AppRoleID = cmd.ExecuteScalar().ToString();
    
                ret = true;
            }
        }
        catch
        {
            throw new Exception("Error in ApplicationRole.Create");
        }
        finally
        {
            conn.Close();
        }
    }
    
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Proposed as answer by Ioana Vasilescu Sunday, July 13, 2014 9:04 AM
    • Marked as answer by Fred Bao Friday, July 18, 2014 10:00 AM
    Saturday, July 12, 2014 12:52 PM
  • I would suggest something like a bit mask. Your Medical Record ID in the database could be a binary column that encodes all the values. Say length 24 as an example, I don't know how much data you need to encode.

    Assuming each piece of data will not be more than the value 255, you can use one byte per item. For instance, the first byte of the column will be the gender value, the second will be age, etc. If you need values greater than 255 use can use more bytes for that field.

    Finally, you need to add a unique value at the end to make sure no two MR ids are the same. Below I show an example of using a GUID which I think would be easiest. But you could use a database sequence too.

    When you read the MR id out of the database, it will be a byte array. Just pluck out the values you need by index to retrieve gender, age, etc.

    using System;
    using System.Linq;
    
    namespace BitMaskID
    {
        class Program
        {
            static void Main(string[] args)
            {
                byte gender = 8;
                byte age = 48;
                byte bgp = 3;
    
                //This is your MR id, store it in a binary(24) 
                //column in the database
                byte[] theID = new byte[24];
    
                //Set the applicable values
                theID[0] = gender;
                theID[1] = age;
                theID[2] = bgp;
    
                //Generate a GUID - globally unique identifier 
                var theGuid = Guid.NewGuid();
    
                //Write out the guid to prove later that we can
                //recover it intact 
                Console.WriteLine(string.Format(
                    "GUID created as: {0}",theGuid.ToString()));
    
                //Convert it to array for appending to the MR id
                byte[] uid = theGuid.ToByteArray();
                for (int i = 0; i < uid.Length; i++)
                {
                    theID[i + 8] = uid[i];
                }
    
                Console.WriteLine(string.Format(
                    "gender: {0}", theID[0]));
                Console.WriteLine(string.Format(
                    "Age: {0}", theID[1]));
                Console.WriteLine(string.Format(
                    "bgp: {0}", theID[2]));
    
                //Grab the last 16 bytes of the id, these are 
                //the GUID bytes
                var last16Bytes = 
                    theID.Skip(8).Take(16).ToArray<byte>();
    
                //Assert that we get the guid back from 
                //positions 8 thru 16 of the MR id    
                Console.WriteLine(
                    string.Format("unique id: {0}", 
                    new Guid(last16Bytes).ToString()));
    
                Console.ReadKey();
            }
        }
    }
    


    Bob - www.ContrivedExample.com

    • Proposed as answer by Ioana Vasilescu Sunday, July 13, 2014 9:04 AM
    • Marked as answer by Fred Bao Friday, July 18, 2014 10:00 AM
    Saturday, July 12, 2014 2:57 PM
  • The key you generated from the below method can be appended to a unique

    primary key obtained from the database. So that you will also be

    able to maintain database consistency for CRUD operations.

    private Int64 function() { string key = ""; char gender = 'm'; int age = 40; string BG = "A+"; /// Adding Gender if(gender == 'm') key = "8"; else key = "9"; ///Adding Age if(age < 10) key += "00" + age; else if(age < 100) key += "0"+age; else key += age.ToString(); ///Adding Blood group switch(BG) { case "A+": key += 2; break; case "B+": key += 3; break; . . . } return Convert.ToInt64(key); }


    • Edited by Coder Rajat Monday, July 21, 2014 6:27 PM
    • Marked as answer by anwar adam Thursday, July 24, 2014 10:41 AM
    Monday, July 21, 2014 6:15 PM
  • Hello,

    Using a TextBox to get user input for male/female/not specified should be done with a ComboBox or RadioButtons in a container such as a GroupBox or Panel as typing text in is error prone. The professional method to accept numbers only in a TextBox is to use a custom numeric only which includes checking if a user pasted data in from the Windows Clipboard. I would provide code examples but current on a computer w/o Visual Studio.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Marked as answer by anwar adam Thursday, July 24, 2014 10:41 AM
    Tuesday, July 22, 2014 11:15 AM

All replies

  • The recommended method is to write a procedure in SQL Server to create a unique primary key.  Then run the SQL procedure from VS.  When you have multiple users siomultaneaously generating a primary key you want to make sure there are no duplicates.  The only way of ensuring no duplicates is to use a procedure in the Server.   The primary key can be an incremental number of a custom sequence.  See webpage below.

    http://msdn.microsoft.com/en-us/library/ms179610.aspx


    jdweng

    Saturday, July 12, 2014 12:04 PM
  • Hello,

    Adding to Joel's recommendations, some will use two trips to the database, one to insert, one to retrieve the new primary key. The following shows how to do one trip. Focus on I am using a connection and command where the SQL statement first does the insert then retrieves the new key which is returned via ExecuteScalar so ignore the variables around this.

    using (SqlConnection conn = new SqlConnection(Config.Instance.Connection))
    {
        conn.Open();
        try
        {
    
            sql = @"INSERT INTO [AppRole]
                    (
                        RoleName,
                        RoleDescription,
                        AppID
                    )
                    VALUES
                    (
                        @RoleName,
                        @RoleDescription,
                        @AppID
                    );
                    SELECT CAST(scope_identity() AS int);
                ";
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.Parameters.AddWithValue("@AppID", this.AppID);
    
                cmd.Parameters.AddWithValue("@RoleName", DataHelper.TrimToEmpty(this.Name));
                cmd.Parameters.AddWithValue("@RoleDescription", DataHelper.TrimToEmpty(this.Description));
    
                this.AppRoleID = cmd.ExecuteScalar().ToString();
    
                ret = true;
            }
        }
        catch
        {
            throw new Exception("Error in ApplicationRole.Create");
        }
        finally
        {
            conn.Close();
        }
    }
    
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Proposed as answer by Ioana Vasilescu Sunday, July 13, 2014 9:04 AM
    • Marked as answer by Fred Bao Friday, July 18, 2014 10:00 AM
    Saturday, July 12, 2014 12:52 PM
  • I would suggest something like a bit mask. Your Medical Record ID in the database could be a binary column that encodes all the values. Say length 24 as an example, I don't know how much data you need to encode.

    Assuming each piece of data will not be more than the value 255, you can use one byte per item. For instance, the first byte of the column will be the gender value, the second will be age, etc. If you need values greater than 255 use can use more bytes for that field.

    Finally, you need to add a unique value at the end to make sure no two MR ids are the same. Below I show an example of using a GUID which I think would be easiest. But you could use a database sequence too.

    When you read the MR id out of the database, it will be a byte array. Just pluck out the values you need by index to retrieve gender, age, etc.

    using System;
    using System.Linq;
    
    namespace BitMaskID
    {
        class Program
        {
            static void Main(string[] args)
            {
                byte gender = 8;
                byte age = 48;
                byte bgp = 3;
    
                //This is your MR id, store it in a binary(24) 
                //column in the database
                byte[] theID = new byte[24];
    
                //Set the applicable values
                theID[0] = gender;
                theID[1] = age;
                theID[2] = bgp;
    
                //Generate a GUID - globally unique identifier 
                var theGuid = Guid.NewGuid();
    
                //Write out the guid to prove later that we can
                //recover it intact 
                Console.WriteLine(string.Format(
                    "GUID created as: {0}",theGuid.ToString()));
    
                //Convert it to array for appending to the MR id
                byte[] uid = theGuid.ToByteArray();
                for (int i = 0; i < uid.Length; i++)
                {
                    theID[i + 8] = uid[i];
                }
    
                Console.WriteLine(string.Format(
                    "gender: {0}", theID[0]));
                Console.WriteLine(string.Format(
                    "Age: {0}", theID[1]));
                Console.WriteLine(string.Format(
                    "bgp: {0}", theID[2]));
    
                //Grab the last 16 bytes of the id, these are 
                //the GUID bytes
                var last16Bytes = 
                    theID.Skip(8).Take(16).ToArray<byte>();
    
                //Assert that we get the guid back from 
                //positions 8 thru 16 of the MR id    
                Console.WriteLine(
                    string.Format("unique id: {0}", 
                    new Guid(last16Bytes).ToString()));
    
                Console.ReadKey();
            }
        }
    }
    


    Bob - www.ContrivedExample.com

    • Proposed as answer by Ioana Vasilescu Sunday, July 13, 2014 9:04 AM
    • Marked as answer by Fred Bao Friday, July 18, 2014 10:00 AM
    Saturday, July 12, 2014 2:57 PM
  • can you tell on windows form application not on console
    Sunday, July 20, 2014 12:26 PM
  • its not working giving error
    Sunday, July 20, 2014 12:27 PM
  • Who's code isn't working? Can you give details what isn't working along with the error message and the line of code where the failure is occurring.


    jdweng

    Sunday, July 20, 2014 12:39 PM
  • The same code will run on a form. You may want to log to a TextBox instead of the console, but you can literally just paste the body of the main() method I provided into a winforms project and use it.

    That said, Console.ReadKey() would serve no purpose so you would probably want to remove that.


    Bob - www.ContrivedExample.com

    Sunday, July 20, 2014 3:12 PM
  • this is my code to generate Medical record number using if-else statement .is this the right way to generate medical record number like this?

    string gender = textBox4.Text;

                string roll = textBox2.Text;
                string k = "";
                if (textBox4.Text == "male")
                {
                     k = "m";
                }
                else if( textBox4.Text == "not specified")
                     k = "n";

                if (textBox4.Text == "female")
                {
                    k = "f";
                }
                else if(textBox4.Text =="not specified")
                 k = "n";

                int i = int.Parse(textBox2.Text);
                if (i == 25)
                {
                    textBox3.Text = k +"B2";
                }

                
    Monday, July 21, 2014 5:56 PM
  • The key you generated from the below method can be appended to a unique

    primary key obtained from the database. So that you will also be

    able to maintain database consistency for CRUD operations.

    private Int64 function() { string key = ""; char gender = 'm'; int age = 40; string BG = "A+"; /// Adding Gender if(gender == 'm') key = "8"; else key = "9"; ///Adding Age if(age < 10) key += "00" + age; else if(age < 100) key += "0"+age; else key += age.ToString(); ///Adding Blood group switch(BG) { case "A+": key += 2; break; case "B+": key += 3; break; . . . } return Convert.ToInt64(key); }


    • Edited by Coder Rajat Monday, July 21, 2014 6:27 PM
    • Marked as answer by anwar adam Thursday, July 24, 2014 10:41 AM
    Monday, July 21, 2014 6:15 PM
  • Thank you so much Mr.coder Rajat my problem is solved.
    Tuesday, July 22, 2014 10:00 AM
  • Hello,

    Using a TextBox to get user input for male/female/not specified should be done with a ComboBox or RadioButtons in a container such as a GroupBox or Panel as typing text in is error prone. The professional method to accept numbers only in a TextBox is to use a custom numeric only which includes checking if a user pasted data in from the Windows Clipboard. I would provide code examples but current on a computer w/o Visual Studio.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Marked as answer by anwar adam Thursday, July 24, 2014 10:41 AM
    Tuesday, July 22, 2014 11:15 AM
  • Hello

    I did the same as you are telling me,the above was just an example using textbox.

    Thursday, July 24, 2014 10:44 AM