public List <CalendarStageService> GetClendarStageServices(int configId)
{
var sql = @"select css.Id, css.CalendarStageId, css.ServiceId from pq.[CalendarStageService] css
INNER JOIN pq.CalendarStage cs on css.CalendarStageId = cs.Id where cs.CalendarStageConfigId = @CalendarStageConfigId";
var result = new List <CalendarStageService>();
using (SqlConnection connection = new SqlConnection(_settings.QFlowConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand(sql, connection))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.AddWithValue("@CalendarStageConfigId", configId);
connection.Open();
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
{
while (sqlDataReader.Read())
{
var item = new CalendarStageService();
item.Id = Converter.ToInt32(sqlDataReader["Id"]);
item.CalendarStageId = Converter.ToInt32(sqlDataReader["CalendarStageId"]);
item.ServiceId = Converter.ToInt32(sqlDataReader["ServiceId"]);
result.Add(item);
}
}
}
}
return(result);
}
public void InsertCalendarStageServices(int stageId, List <CustomizeStageService> services)
{
foreach (var stageServiceData in services)
{
var stageService = new CalendarStageService()
{
CalendarStageId = stageId, ServiceId = stageServiceData.ServiceId
};
InsertCalendarStageService(stageService);
}
}
public int InsertCalendarStageService(CalendarStageService calendarStage)
{
var sql = $@"INSERT INTO [pq].[CalendarStageService] ([CalendarStageId] ,[ServiceId]) OUTPUT INSERTED.ID
VALUES (@CalendarStageId, @ServiceId) ";
int result = 0;
using (SqlConnection connection = new SqlConnection(_settings.QFlowConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand(sql, connection))
{
sqlCommand.Parameters.AddWithValue("@CalendarStageId", calendarStage.CalendarStageId);
sqlCommand.Parameters.AddWithValue("@ServiceId", calendarStage.ServiceId);
sqlCommand.CommandType = CommandType.Text;
connection.Open();
result = (int)sqlCommand.ExecuteScalar();
}
}
return(result);
}