Auto Increment Patient ID

Question

I have been creating C# project for Hospital Management in which I have considered some basic text values like Patient name, PatientID, Gender etc., plus some buttons for functioning saving etc. I have used SqlClient to store the values. I just wanted to know how to set the PatientID so that PatientID automatically increases by 1 everytime I enter a new record. The PatientID text has to be disabled for user and needs to be automatically incremented for every new record on its own.

Can anyone please help? Thanks in advance :)

check this http://stackoverflow.com/questions/5083846/sql-server-2005-using-generated-sequences-instead-of-identity-columns, it might help you — Monah, Nov 24 '14 at 13:48
Why `PatientID` is a *text*? It's a good candidate to be *auto-incremented primary index*. — Sinatr, Nov 24 '14 at 13:49
@POHH how do i do that ? Sorry I have just started with C# so weak in it. — Coder, Nov 24 '14 at 13:50
are you using datatable? then you can set the column to autoincrement = true; — Harry, Nov 24 '14 at 13:53
To clarify what the others have said: Unique IDs should be generated by the SQL database, not by your application code. You are asking an X-Y Problem. http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem — Foo Bar, Nov 24 '14 at 14:03

score 2 · Answer 1 · answered Nov 24 '14 at 13:58

2

Create table like this :

CREATE TABLE [dbo].[Patient](
    [PatientID] [int] IDENTITY(1,1) NOT NULL,
    [PatientName] [nvarchar](max) NOT NULL,
    [Gender] [bit] NULL,
 CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED 
(
    [PatientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

answered Nov 24 '14 at 13:58

Amol Bavannavar

2,062
2
15
36

Do you really need nvarchar max for a person's name. I have never heard of a name that exceeds 8,000 characters. And a bit for gender??? Which sex is 1? Why not make it a char(1) instead with a constraint to only allow 'M' or 'F'. I realize your answer is dealing with the original question but if you are going to suggest table structures then they should be reasonable. – Sean Lange Nov 24 '14 at 14:54
Look Sean lange, he haven't shared his table structure so how could I know his actual requirement... Yes off course you gave me good suggestion.. Thank you for sharing your knowledge with me... I will take care of it from next answer... – Amol Bavannavar Nov 24 '14 at 17:32

score 1 · Answer 2 · answered Nov 24 '14 at 14:05

1

you can do this @ table level while creating table set Identity to YES and increament it by 1

here is the reference for you enter image description here

answered Nov 24 '14 at 14:05

Ameya Deshpande

3,580
4
30
46

score 0 · Answer 3 · answered Nov 24 '14 at 13:59

If you are using a DataTable then you can set AutoIncrementColumn = True which will increment on each entry.

    Dim column As DataColumn = New DataColumn
    column.DataType = System.Type.GetType("System.Int32")
    With column
        .AutoIncrement = True
        .AutoIncrementSeed = 1000
        .AutoIncrementStep = 10
    End With 

    ' Add the column to a new DataTable. 
    Dim table As DataTable
    table = New DataTable
    table.Columns.Add(column)

DataColumn.AutoIncrement

score -2 · Answer 4 · answered Nov 24 '14 at 14:14

I created the table programmatically like this in one of my projects

    public void CreateTables()
    {
        SqlConnection conn1 = new SqlConnection();

        try
        {

            // Drop and Create a new Patient Table
            string queryDrop = "IF OBJECT_ID('Patient', 'U') IS NOT NULL DROP TABLE Patient";


            conn1.ConnectionString = GetConnectionString();
            conn1.Open();

            SqlCommand cmdDrp = new SqlCommand(queryDrop, conn1);
            cmdDrp.ExecuteNonQuery();


            string query = "CREATE TABLE Patient(" +
                      "PatientId uniqueidentifier DEFAULT NEWSEQUENTIALID()," +
                      "PatientName varchar(50) NOT NULL," +
                      "Gender varchar(50) NOT NULL," +
                   ")";
            SqlCommand cmd1 = new SqlCommand(query, conn1);

            cmd1.ExecuteNonQuery()
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            conn1.Close();//close the connection
        }
 }

    public string GetConnectionString()
    {
        string folderpath = Environment.GetFolderPath
        (Environment.SpecialFolder.Personal);      

        string connStr = "Data Source=.\\SQLEXPRESS;AttachDbFilename=" + folderpath
        + "\\FolderName\\Hospitaldata.mdf;Integrated Security=True;" +
        "Connect Timeout=30;User Instance=True";

        return connStr;

    }

The number of things not good in here are kind of staggering. Why bother with a uniqueidentifier if you are going to use NEWSEQUENTIALID? It is a lot wider than an int and provides no benefit. This table has no primary key. Gender as varchar(50)? There are only 2 possible values and neither of them are anywhere close to 50 characters. Then there is logic of checking for and creating tables in an application. Connection string hard coded in the app...the list of bad goes on and on. — Sean Lange, Nov 24 '14 at 14:58

Auto Increment Patient ID

4 Answers4