Monday, September 27, 2010

How to issue a id value to a web page without having duplicates

Stored Procedure:
CREATE PROCEDURE GetNewId
AS
BEGIN
    DECLARE @NewId INT;
    SET @NewId = -1;
    SELECT TOP 1 @NewId = Id FROM dbo.DataTable WHERE IsSaved = 0 ORDER BY ID ASC;
    IF( @NewId = -1)
    BEGIN
        SELECT @NewId = MAX(Id) + 1 FROM dbo.DataTable;
        INSERT INTO dbo.DataTable(Id, IsSaved) VALUES (@NewId, 0);
    END
    SELECT @NewId
END
Code:
protected override void OnLoad(EventArgs e)
{
    base.OnLoad(e);
    ///
    /// Get
    ///        
    if (!this.IsPostBack)
    {
        using (SqlConnection con = new SqlConnection("ConnectionString"))
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("GetNewId", con);
            cmd.CommandType = CommandType.StoredProcedure;
            string newNumber = cmd.ExecuteScalar().ToString();
            this.txtNumber.Text = newNumber;
            con.Close();
        }
    }
}
protected void Save(object sender, EventArgs e)
{
    ///
    /// Save, actually by that time we have a record for newNumber
    /// So again in the save method we have to update the relevent record
    ///
    using (SqlConnection con = new SqlConnection("ConnectionString"))
    {
        string sql = @"
    UPDATE dbo.DataTable
    SET 
     @IsSaved = 1,
        ...
        ...
    WHERE Id = @Id;
";
        con.Open();
        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.Add(new SqlParameter("@Id"int.Parse(this.txtNumber.Text)));
        ///
        /// add other parameters
        /// 
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

No comments:

Azure Storage Account Types

Defferent Types of Blobs Block blobs store text and binary data. Block blobs are made up of blocks of data that can be managed individually...