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:
Post a Comment