ALTER PROCEDURE [dbo].[USP_IUD_MST_STATEWISE_COURSE_LEVEL]
(
@Course_level_detail_id INT = NULL
,@Course_level_id INT = NULL
,@state_id INT = NULL
,@LevelPercentage FLOAT = NULL
,@created_date DATE = NULL
,@type char(1)
)
AS
BEGIN
BEGIN TRY
BEGIN TRAN
DECLARE @PREV_LVL_PERCENTAGE FLOAT = 0;
SET @PREV_LVL_PERCENTAGE =ISNULL((SELECT ISNULL(SUM(LevelPercentage),0) FROM MST_STATEWISE_COURSE_LEVEL WHERE state_id =@state_id GROUP BY state_id),0)
--SELECT @PREV_LVL_PERCENTAGE;
IF @type LIKE 'I'
BEGIN
IF
(@PREV_LVL_PERCENTAGE + ISNULL(@LevelPercentage,0) )<101
BEGIN
SELECT @Course_level_detail_id=ISNULL(MAX(Course_level_detail_id),0)+1 FROM MST_STATEWISE_COURSE_LEVEL;
INSERT INTO MST_STATEWISE_COURSE_LEVEL
(Course_level_detail_id
,Course_level_id
,state_id
,LevelPercentage
,created_date
)
VALUES
( @Course_level_detail_id
,@Course_level_id
,@state_id
,@LevelPercentage
,@created_date
)
END
ELSE
RAISERROR( 'Percentage of Level can not cross more than 100 Percentage.',16,1)
END
IF @type LIKE 'U'
BEGIN
DECLARE @prev_LevelPercentage float=0;
SELECT @prev_LevelPercentage=LevelPercentage FROM MST_STATEWISE_COURSE_LEVEL WHERE Course_level_detail_id = @Course_level_detail_id
SET @PREV_LVL_PERCENTAGE=@PREV_LVL_PERCENTAGE-@prev_LevelPercentage;
IF (@PREV_LVL_PERCENTAGE + ISNULL(@LevelPercentage,0) )<101
BEGIN
UPDATE MST_STATEWISE_COURSE_LEVEL
SET Course_level_id = CASE WHEN @Course_level_id IS NULL THEN Course_level_id ELSE @Course_level_id END
,state_id = CASE WHEN @state_id IS NULL THEN state_id ELSE @state_id END
,LevelPercentage = CASE WHEN @LevelPercentage IS NULL THEN LevelPercentage ELSE @LevelPercentage END
,created_date = CASE WHEN @created_date IS NULL THEN created_date ELSE @created_date END
,update_date=getdate()
WHERE Course_level_detail_id = @Course_level_detail_id ;
END
ELSE
RAISERROR( 'Percentage of Level can not cross more than 100 Percentage.',16,1)
END
IF @type LIKE 'D'
BEGIN
DELETE FROM MST_STATEWISE_COURSE_LEVEL WHERE Course_level_detail_id = @Course_level_detail_id
END
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK TRAN
DECLARE @ErrorNumber_INT INT,@ErrorSeverity_INT INT,@ErrorProcedure_VC VARCHAR(200), @ErrorLine_INT INT
, @ErrorMessage_NVC NVARCHAR(4000);
SELECT
@ErrorMessage_NVC = ERROR_MESSAGE(),
@ErrorSeverity_INT = ERROR_SEVERITY(),
@ErrorNumber_INT = ERROR_NUMBER(),
@ErrorProcedure_VC = ERROR_PROCEDURE(),
@ErrorLine_INT = ERROR_LINE()
RAISERROR(@ErrorMessage_NVC,@ErrorSeverity_INT,1);
END CATCH
---------------
aspx.cs page
try
{
SqlParameter[] paramarr = new SqlParameter[]
{
new SqlParameter("@Course_level_detail_id",null)
,new SqlParameter("@Course_level_id",Convert.ToInt32(drp_level.SelectedValue))
,new SqlParameter("@state_id",Convert.ToInt32(drp_state.SelectedValue))
,new SqlParameter("@LevelPercentage",Convert.ToSingle(txt_percentage.Text))
,new SqlParameter("@created_date",DateTime.Now)
,new SqlParameter("@type","I")
};
result = Luminious.DataAcessLayer.SqlHelper.ExecuteNonQuery(Luminious.Connection.Configuration.ConnectionString, CommandType.StoredProcedure, UserQuery, paramarr);
}
catch (SqlException ee)
{
lbl_msg.Text = ee.Message;
lbl_msg.ForeColor = System.Drawing.Color.Red;
ExceptionHandler.WriteException(ee.Message + "Error Line Number:" + ee.LineNumber);
}
catch (Exception ee)
{
ExceptionHandler.WriteException(ee.Message);
}
finally
{
ShowGrid(Convert.ToInt32(drp_state.SelectedValue));
drp_level.SelectedIndex = -1;
txt_percentage.Text = string.Empty;
}
if (result.HasValue && result.Value > 0)
{
lbl_msg.Text = "Record Inserted Sucessfully..";
lbl_msg.ForeColor = System.Drawing.Color.Green;
}
}
else
{
lbl_msg.Text = "Duplicate Exists..";
lbl_msg.ForeColor = System.Drawing.Color.Red;
}
}
else
{
int coursedetailid = Convert.ToInt32(ViewState["updateid"].ToString());
string UserQuery = @"USP_IUD_MST_STATEWISE_COURSE_LEVEL";
int? result = null;
try
{
SqlParameter[] paramarr = new SqlParameter[]
{
new SqlParameter("@Course_level_detail_id",coursedetailid)
,new SqlParameter("@Course_level_id",Convert.ToInt32(drp_level.SelectedValue))
,new SqlParameter("@state_id",Convert.ToInt32(drp_state.SelectedValue))
,new SqlParameter("@LevelPercentage",Convert.ToSingle(txt_percentage.Text))
,new SqlParameter("@created_date",DateTime.Now)
,new SqlParameter("@type","U")
};
result = Luminious.DataAcessLayer.SqlHelper.ExecuteNonQuery(Luminious.Connection.Configuration.ConnectionString, CommandType.StoredProcedure, UserQuery, paramarr);
}
catch (SqlException ee)
{
lbl_msg.Text = ee.Message;
lbl_msg.ForeColor = System.Drawing.Color.Red;
ExceptionHandler.WriteException(ee.Message + "Error Line Number:" + ee.LineNumber);
}
catch (Exception ee)
{
ExceptionHandler.WriteException(ee.Message);
}
finally
{
if (result.HasValue && result.Value>0)
{
lbl_msg.Text = "Update Sucessfully...";
lbl_msg.ForeColor = System.Drawing.Color.Green;
}
ShowGrid(Convert.ToInt32(drp_state.SelectedValue));
}
btnSave.Text = "Save";
drp_level.SelectedIndex = -1;
txt_percentage.Text = string.Empty;
}
END
No comments:
Post a Comment