Generating VarChar Sequence Number for Primary Key Columns in any Database Table

The Requirement

In Relational Database, every table should have a Primary Key Column. Sometimes it is required to generate a sequence number automatically for that column, whenever a record is inserted into the table. For example an Employee ID primary key can have a sequence number like E001, E002, E003, …, E999.

protected void Page_Load(object sender, EventArgs e)
{
        string id = GenerateId("TblStudents", "sid", 4, "S", true);
        Response.Write(id);
}

public string GenerateId(string TableName, string ColumnName, int ColumnLength, string Prefix, bool Padding)
{
        SqlConnection con = new SqlConnection("server=.;integrated security=true;database=DotNetSpider");
        string Query, Id;
        int PrefixLength, PadLength;
        PrefixLength = Convert.ToInt32(Prefix.Length);
        PadLength = ColumnLength - PrefixLength;
        if (Padding == true)
        {
            Query = "SELECT '" + Prefix + "' + REPLACE(STR(MAX(CAST(SUBSTRING(" + ColumnName + "," + Convert.ToString(PrefixLength + 1) + "," + PadLength + ") AS INTEGER))+1," + PadLength + "),' ',0) FROM " + TableName;
        }
        else
        {
            Query = "SELECT '" + Prefix + "' + CAST(MAX(CAST(SUBSTRING(" + ColumnName + "," + Convert.ToString(PrefixLength + 1) + "," + PadLength + ") AS INTEGER))+1 AS VARCHAR) FROM " + TableName;
        }
        SqlCommand com = new SqlCommand(Query, con);
        con.Open();
        if (com.ExecuteScalar().ToString() == "")
        {
            Id = Prefix;
            if (Padding == true)
            {
                for (int i = 1; i <= PadLength - 1; i++)
                {
                    Id += "0";
                }
            }
            Id += "1";
        }
        else
        {
            Id = Convert.ToString(com.ExecuteScalar());
        }
        con.Close();
        return Id;
}