private void meetingCategoryGrid_UserAddedRow(object sender, GridViewRowEventArgs e)
{
MeetingCategory category = (MeetingCategory)e.Row.DataBoundItem;
category.CategoryOwner = NewPrioritizer.loggedInUserID;
}
public MeetingCategory InsertCategoryAndActionItems(MeetingCategory meetingCategory)
{
string sqlSelLastIdCategory = "SELECT MAX(id) FROM [dbo].[meeting_category]";
string sqlInsCategory = @"INSERT INTO [dbo].[meeting_category]
([id]
,[name]
,[created_by_id])
VALUES
(@id
,@name
,@createdbyid);";
string sqlSelLastIdActItems = "SELECT MAX(id) FROM [dbo].[meeting_action_items]";
string sqlInsActItems = @"INSERT INTO [dbo].[meeting_action_items]
([id]
,[meeting_category_id]
,[name]
,[description]
,[is_adhoc]
,[created_by_id])
VALUES
(@id
,@meetingcategoryid
,@name
,@description
,@isadhoc
,@createdbyid);";
using (IDbConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (var tran = conn.BeginTransaction())
{
int nextCatId = 1;
var lastId = conn.QuerySingle <int?>(sqlSelLastIdCategory, commandType: CommandType.Text, transaction: tran);
nextCatId = (lastId == null) ? nextCatId : (int)lastId + 1;
DynamicParameters dp = new DynamicParameters();
dp.Add("id", nextCatId, DbType.Int32, ParameterDirection.Input);
dp.Add("name", meetingCategory.Name, DbType.String, ParameterDirection.Input);
dp.Add("createdbyid", meetingCategory.CreatedById, DbType.Int32, ParameterDirection.Input);
var affRows = conn.Execute(sqlInsCategory, dp, transaction: tran);
if (meetingCategory.MeetingActionItems != null && meetingCategory.MeetingActionItems.Any())
{
var meetingActionItems = meetingCategory.MeetingActionItems.ToList();
meetingActionItems.ForEach(meetingActionItem =>
{
int nextId = 1;
lastId = conn.QuerySingle <int?>(sqlSelLastIdActItems, commandType: CommandType.Text, transaction: tran);
nextId = (lastId == null) ? nextId : (int)lastId + 1;
DynamicParameters dp2 = new DynamicParameters();
dp2.Add("id", nextId, DbType.Int32, ParameterDirection.Input);
dp2.Add("meetingcategoryid", nextCatId, DbType.Int32, ParameterDirection.Input);
dp2.Add("name", meetingActionItem.Name, DbType.String, ParameterDirection.Input);
dp2.Add("description", meetingActionItem.Description, DbType.String, ParameterDirection.Input);
dp2.Add("isadhoc", meetingActionItem.IsAdhoc, DbType.String, ParameterDirection.Input);
dp2.Add("createdbyid", meetingActionItem.CreatedById, DbType.Int32, ParameterDirection.Input);
var affRows2 = conn.Execute(sqlInsActItems, dp2, transaction: tran);
});
}
tran.Commit();
meetingCategory.Id = nextCatId;
}
return(meetingCategory);
}
}
