public static bool BatchAdd(string UserIDs, string date, decimal Duration, bool InWell, int NightWork, out string Message)
{
SqlParameter[] parameters = new SqlParameter[5];
parameters[0] = new SqlParameter("@UserIDs", SqlDbType.Text);
parameters[0].Value = UserIDs;
parameters[1] = new SqlParameter("@Date", SqlDbType.VarChar, 20);
parameters[1].Value = date;
parameters[2] = new SqlParameter("@Duration", SqlDbType.Decimal);
parameters[2].Value = Duration;
parameters[3] = new SqlParameter("@InWell", SqlDbType.Int);
parameters[3].Value = InWell ? 1 : 0;
parameters[4] = new SqlParameter("@NightWork", SqlDbType.Int);
parameters[4].Value = NightWork;
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT DISTINCT userId FROM AttendanceMng");
sql.AppendLine("WHERE DATE = @Date AND userId IN (SELECT value FROM dbo.sfTableFromItemList(@UserIDs))");
DataTable table = helper.ExecuteDataTable(sql.ToString(), parameters.ToArray());
StringBuilder userIDs = new StringBuilder();
foreach (DataRow row in table.Rows) {
if (userIDs.Length > 0) { userIDs.Append(","); }
userIDs.Append(row["userId"].ToString().Trim());
}
Message = userIDs.Length > 0 ? userIDs.ToString() : "";
int rowCount = 0;
if (Message.Length == 0) {
sql.Clear();
sql.AppendLine("INSERT INTO AttendanceMng (userId, DATE, workDurs, ifIn, nightWork)");
sql.AppendLine("SELECT value, @Date, @Duration, @InWell, @NightWork FROM dbo.sfTableFromItemList(@UserIDs)");
rowCount = (int)helper.ExecuteNonQuery(sql.ToString(), parameters.ToArray());
}
return rowCount > 0;
}
public void UpdateConsented(long patientId,string fileExt)
{
SqlParameter[] sqlParms = new SqlParameter[]{
new SqlParameter("Id",patientId),
new SqlParameter("Ext",fileExt)
};
SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.StoredProcedure, "UpdateConsented", sqlParms.ToArray());
}
public long AddSummary(string PeerId, string UserId)
{
SqlParameter[] sqlParms = new SqlParameter[]{
new SqlParameter("UserId",Convert.ToInt64(UserId)),
new SqlParameter("PeerId",Convert.ToInt64(PeerId))
};
var newId = SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.StoredProcedure, "Encounters_AddSummary", sqlParms.ToArray());
return (long)newId;
}
public static bool Add(DeptInfo dept)
{
SqlParameter[] parameters = new SqlParameter[4];
parameters[0] = new SqlParameter("@ID", SqlDbType.Int);
parameters[0].Value = dept.ID;
parameters[1] = new SqlParameter("@Name", SqlDbType.VarChar, 50);
parameters[1].Value = dept.Name;
parameters[2] = new SqlParameter("@UpID", SqlDbType.Int);
parameters[2].Value = dept.UpID;
parameters[3] = new SqlParameter("@Level", SqlDbType.Int);
parameters[3].Value = dept.Level;
StringBuilder sql = new StringBuilder();
sql.AppendLine("INSERT INTO DeptInfo (deptId, deptName, upID, [level])");
sql.AppendLine("VALUES (@ID, @Name, @UpID, @Level)");
int rowCount = helper.ExecuteNonQuery(sql.ToString(), parameters.ToArray());
return rowCount > 0;
}
public void DeleteMedication(string Id)
{
SqlParameter[] sqlParms = new SqlParameter[]{
new SqlParameter("Id",Convert.ToInt64(Id))
};
SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.StoredProcedure, "Encounters_DeleteMedication", sqlParms.ToArray());
}
public void EditBabyPCP(long babyPCPId, string Name, string Address1, string Address2, string Phone, string City, string State, string ZipCode)
{
SqlParameter[] sqlParms = new SqlParameter[]{
new SqlParameter("Id",babyPCPId),
new SqlParameter("Name",Name),
new SqlParameter("Address1",Address1),
new SqlParameter("Address2",Address2),
new SqlParameter("Phone",Phone),
new SqlParameter("City",City),
new SqlParameter("State",State),
new SqlParameter("ZipCode",ZipCode)
};
SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.StoredProcedure, "Encounters_EditBabyPCP", sqlParms.ToArray());
}
public static void ForceCloseRFQ(int RFQID)
{
List<RFQDetail> rfqdetails = RFQManager.GetDetail(RFQID);
if (rfqdetails.Count > 0)
{
RFQDetail rfqdetail = rfqdetails[0];
if (rfqdetail.RFQID > 0)
{
WF.WFTemplate wf = new WF.WFTemplate("DefaultWF", rfqdetail == null ? 0 : rfqdetail.RFQID);
if (rfqdetail.HitRateStatus != "Pending" && string.IsNullOrEmpty(rfqdetail.HitRateStatus) == false && wf.CurrentActivity.ID != 7 && rfqdetail.RFQID > 0)
{
string strsql1 = "update SGP_RFQ set ActivityID=7,StatusID=9 where id=@RFQID";
string strsql2 = "insert into SYS_WFProcessLog select @RFQID,1,@FromActivityID,@ToActivityID,0,GetDate(),@UserID,@Comment";
SqlParameter[] sp1 = new SqlParameter[] { new SqlParameter("@RFQID", rfqdetail.RFQID) };
SqlParameter[] sp2 = new SqlParameter[] { new SqlParameter("@RFQID",rfqdetail.RFQID),
new SqlParameter("@FromActivityID",wf.CurrentActivity.ID),
new SqlParameter("@ToActivityID",7),
new SqlParameter("@UserID", BI.SGP.BLL.Utils.AccessControl.CurrentLogonUser.Uid),
new SqlParameter("@Comment", "Force Close RFQ")};
DbHelperSQL.ExecuteSql(strsql1, sp1.ToArray());
DbHelperSQL.ExecuteSql(strsql2, sp2.ToArray());
}
}
}
}
public static bool Update(RankInfo rank)
{
SqlParameter[] parameters = new SqlParameter[2];
parameters[0] = new SqlParameter("@ID", SqlDbType.Int);
parameters[0].Value = rank.ID;
parameters[1] = new SqlParameter("@Name", SqlDbType.VarChar, 30);
parameters[1].Value = rank.Name;
StringBuilder sql = new StringBuilder();
sql.AppendLine("UPDATE RankInfo SET Rank = @Name");
sql.AppendLine("WHERE ID = @ID");
int rowCount = helper.ExecuteNonQuery(sql.ToString(), parameters.ToArray());
return rowCount > 0;
}
public static bool Update(AttendanceInfo attendance)
{
SqlParameter[] parameters = new SqlParameter[5];
parameters[0] = new SqlParameter("@Date", SqlDbType.VarChar, 20);
parameters[0].Value = attendance.Date;
parameters[1] = new SqlParameter("@Duration", SqlDbType.Decimal);
parameters[1].Value = attendance.Duration;
parameters[2] = new SqlParameter("@InWell", SqlDbType.Int);
parameters[2].Value = attendance.InWell ? 1 : 0;
parameters[3] = new SqlParameter("@NightWork", SqlDbType.Int);
parameters[3].Value = attendance.NightWork;
parameters[4] = new SqlParameter("@ID", SqlDbType.Int);
parameters[4].Value = attendance.ID;
StringBuilder sql = new StringBuilder();
sql.AppendLine("UPDATE AttendanceMng SET DATE = @Date, workDurs = @Duration, ifIn = @InWell, nightWork = @NightWork");
sql.AppendLine("WHERE ID = @ID");
int rowCount = helper.ExecuteNonQuery(sql.ToString(), parameters.ToArray());
return rowCount > 0;
}
public bool CreateIgniteContact(Contact contact)
{
SqlParameter[] sqlParms = new SqlParameter[]{
new SqlParameter("FirstName",contact.FirstName),
new SqlParameter("UserName",(Convert.ToString(contact.FirstName).Substring(0, 1) + Convert.ToString(contact.LastName)).ToLower().Trim()),
new SqlParameter("LastName",contact.LastName),
new SqlParameter("DOB",contact.DOB),
new SqlParameter("ProviderId",contact.ProviderId),
new SqlParameter("PhysicianId",contact.PhysicianId),
new SqlParameter("ReferenceId",contact.ReferenceId),
new SqlParameter("PhoneNumber",contact.PhoneNumber),
new SqlParameter("EmailId",contact.Email),
new SqlParameter("Password",contact.Password)
};
int ret = (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.StoredProcedure, "SignUp_CreateNewContact", sqlParms.ToArray());
if (ret > 0)
{
return true;
}
return false;
}
public SystemMessages AssignVVIData(int ID, string suppliercode, SystemMessages sysmgs)
{
this.ID = ID;
//SystemMessages sysmgs = new SystemMessages();
try
{
string strField = "", strValue = "";
FieldInfoCollecton supplierfields = FieldCategory.GetAllFields(FieldCategory.Category_TYPE_SGPFORSUPPLIER);
string number = "";
foreach (FieldInfo fi in AllMasterFields)
{
number += fi.FieldName == "Number" ? fi.DataValue.ToString() + "-" + suppliercode : "";
foreach (FieldInfo supplierfi in supplierfields)
{
if (fi.DisplayName == supplierfi.DisplayName)
{
if (supplierfi.FieldName == "NVARCHAR1")
{
fi.DataValue = number;
strField += supplierfi.FieldName + ",";
strValue += "'" + number + "',";
}
else
{
strField += supplierfi.FieldName + ",";
strValue += "'" + fi.DataValue.ToString() + "',";
}
}
}
}
strField += "DATETIME2,";
strValue += "GetDate(),";
string vendorname = DbHelperSQL.GetSingle<string>("select SupplierName from SYS_Supplier where SupplyCode=@SupplyCode", new SqlParameter("@SupplyCode",suppliercode));
vendorname = suppliercode + "[" + vendorname + "]";
strField += "NVARCHAR7,";
strValue += "'" + vendorname + "',";
strField = strField.TrimEnd(',');
strValue = strValue.TrimEnd(',');
bool existsRFQNumber = DbHelperSQL.Exists("select count(*) from SGP_SubData where NVARCHAR1=@Number and EntityName='VVIDETAIL' ", new SqlParameter("@Number", number));
if (existsRFQNumber == false)
{
try
{
int maxdataindex = -1;
maxdataindex = DbHelperSQL.GetSingle<int>("select Max(DataIndex) from SGP_SubData where EntityID=" + ID.ToString() + " and EntityName='VVIDETAIL'");
maxdataindex++;
string strSql = "insert into SGP_SubData(DataIndex,EntityID,EntityName," + strField + ") values(" + maxdataindex.ToString() + "," + ID.ToString() + ",'VVIDETAIL'," + strValue + ")";
DbHelperSQL.ExecuteSql(strSql);
string vendormail = "";
try
{
WF.WFUser user = new WF.WFUser(suppliercode);
if (user != null)
{
vendormail = user.Email;
}
else
{
vendormail = "*****@*****.**";
}
}
catch
{
vendormail = "*****@*****.**";
}
// wf.Run();
sysmgs.isPass = true;
sysmgs.MessageType = "Success";
sysmgs.Messages.Add("OK", number);
BI.SGP.BLL.WF.WFTemplate wftemplate = new WF.WFTemplate("SUPPLIERWF", ID, number);
BI.SGP.BLL.WF.WFActivity wfactivity = wftemplate.FirstActivity;
BI.SGP.BLL.WF.Action.SendMailAction sendmail = new WF.Action.SendMailAction();
sendmail.DoActionForVVI(wfactivity, vendorname, vendormail);
string strsql1 = "update SGP_RFQForVVI set ActivityID=102 where (ActivityID in(101) or ActivityID is null) and RFQID=@RFQID";
string strsql2 = "insert into SYS_WFProcessLog select @RFQID,3,@FromActivityID,@ToActivityID,0,GetDate(),@UserID,@Comment";
SqlParameter[] sp1 = new SqlParameter[] { new SqlParameter("@RFQID", ID) };
SqlParameter[] sp2 = new SqlParameter[] { new SqlParameter("@RFQID",ID),
new SqlParameter("@FromActivityID",101),
new SqlParameter("@ToActivityID",102),
new SqlParameter("@UserID", BI.SGP.BLL.Utils.AccessControl.CurrentLogonUser.Uid),
new SqlParameter("@Comment", "Assign RFQ")};
DbHelperSQL.ExecuteSql(strsql1, sp1.ToArray());
DbHelperSQL.ExecuteSql(strsql2, sp2.ToArray());
AddHistory();
}
catch (Exception ex)
{
sysmgs.isPass = false;
sysmgs.MessageType = "Error";
sysmgs.Messages.Add("Error",ex.Message);
}
}
else
{
sysmgs.isPass = false;
sysmgs.MessageType = "already exists";
sysmgs.Messages.Add("already exists", number);
}
}
catch (Exception ex)
{
sysmgs.isPass = false;
sysmgs.MessageType = "Error";
sysmgs.Messages.Add("Error", ex.Message);
}
return sysmgs;
}
public static bool Update(LogonUserInfo user)
{
SqlParameter[] parameters = new SqlParameter[6];
parameters[0] = new SqlParameter("@Code", SqlDbType.VarChar, 20);
parameters[0].Value = user.Code;
parameters[1] = new SqlParameter("@Password", SqlDbType.VarChar, 20);
parameters[1].Value = user.Password;
parameters[2] = new SqlParameter("@RoleType", SqlDbType.Int);
parameters[2].Value = user.RoleType;
parameters[3] = new SqlParameter("@DeptID", SqlDbType.Int);
parameters[3].Value = user.DeptID >= 0 ? (object)user.DeptID : DBNull.Value;
parameters[4] = new SqlParameter("@Flag", SqlDbType.Int);
parameters[4].Value = user.Active ? 1 : 0;
parameters[5] = new SqlParameter("@ID", SqlDbType.Int);
parameters[5].Value = user.ID;
StringBuilder sql = new StringBuilder();
sql.AppendLine("UPDATE LogonUser SET logonUser = @Code, passwd = @Password, roleType = @RoleType, depId = @DeptID, flag = @Flag");
sql.AppendLine("WHERE ID = @ID");
return helper.ExecuteNonQuery(sql.ToString(), parameters.ToArray()) > 0;
}
public static bool Update(HolidayInfo holiday)
{
SqlParameter[] parameters = new SqlParameter[6];
parameters[0] = new SqlParameter("@Name", SqlDbType.VarChar, 20);
parameters[0].Value = holiday.Name;
parameters[1] = new SqlParameter("@StartDate", SqlDbType.VarChar, 20);
parameters[1].Value = holiday.StartDate;
parameters[2] = new SqlParameter("@EndDate", SqlDbType.VarChar, 20);
parameters[2].Value = holiday.EndDate;
parameters[3] = new SqlParameter("@Flag", SqlDbType.Int);
parameters[3].Value = holiday.Active ? 1 : 0;
parameters[4] = new SqlParameter("@Days", SqlDbType.Int);
parameters[4].Value = holiday.Days;
parameters[5] = new SqlParameter("@ID", SqlDbType.Int);
parameters[5].Value = holiday.ID;
StringBuilder sql = new StringBuilder();
sql.AppendLine("UPDATE HolidayInfo SET holidayName = @Name, startDate = @StartDate, endDate = @EndDate, flag = @Flag, days = @Days");
sql.AppendLine("WHERE ID = @ID");
int rowCount = helper.ExecuteNonQuery(sql.ToString(), parameters.ToArray());
return rowCount > 0;
}
public static bool UpdateCard(string ID, int CopyType)
{
SqlParameter[] parameters = new SqlParameter[2];
parameters[0] = new SqlParameter("@ID", SqlDbType.VarChar, 6);
parameters[0].Value = ID;
parameters[1] = new SqlParameter("@CopyType", SqlDbType.Int);
parameters[1].Value = CopyType;
StringBuilder sql = new StringBuilder();
sql.AppendLine("UPDATE UserInfo SET CopyType = @CopyType, flag = 0");
sql.AppendLine("WHERE userId = @ID");
int rowCount = helper.ExecuteNonQuery(sql.ToString(), parameters.ToArray());
return rowCount > 0;
}
public static bool Update(UserInfo user)
{
SqlParameter[] parameters = new SqlParameter[6];
parameters[0] = new SqlParameter("@ID", SqlDbType.VarChar, 6);
parameters[0].Value = user.ID;
parameters[1] = new SqlParameter("@Name", SqlDbType.VarChar, 10);
parameters[1].Value = user.Name;
parameters[2] = new SqlParameter("@DeptID", SqlDbType.Int);
parameters[2].Value = user.DeptID;
parameters[3] = new SqlParameter("@RankID", SqlDbType.Int);
parameters[3].Value = user.RankID;
parameters[4] = new SqlParameter("@SenderID", SqlDbType.VarChar, 6);
parameters[4].Value = user.SenderID;
parameters[5] = new SqlParameter("@Type", SqlDbType.Int);
parameters[5].Value = user.Type;
StringBuilder sql = new StringBuilder();
sql.AppendLine("UPDATE UserInfo SET userName = @Name");
sql.AppendLine(" , deptId = @DeptID");
sql.AppendLine(" , rankId = @RankID");
sql.AppendLine(" , senderId = @SenderID");
sql.AppendLine(" , [type] = @Type");
sql.AppendLine("WHERE userId = @ID");
int rowCount = helper.ExecuteNonQuery(sql.ToString(), parameters.ToArray());
return rowCount > 0;
}
public long AddMomOB(long assessmentId, string Name, string Address1, string Address2, string Phone, string City, string State, string ZipCode)
{
SqlParameter[] sqlParms = new SqlParameter[]{
new SqlParameter("AssessmentId",assessmentId),
new SqlParameter("Name",Name),
new SqlParameter("Address1",Address1),
new SqlParameter("Address2",Address2),
new SqlParameter("Phone",Phone),
new SqlParameter("City",City),
new SqlParameter("State",State),
new SqlParameter("ZipCode",ZipCode)
};
object id = SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.StoredProcedure, "Encounters_AddMomOB", sqlParms.ToArray());
return Convert.ToInt64(id);
}
public static bool Update(LeaveInfo leave)
{
SqlParameter[] parameters = new SqlParameter[5];
parameters[0] = new SqlParameter("@StartDate", SqlDbType.VarChar, 20);
parameters[0].Value = leave.StartDate;
parameters[1] = new SqlParameter("@EndDate", SqlDbType.VarChar, 20);
parameters[1].Value = leave.EndDate;
parameters[2] = new SqlParameter("@Type", SqlDbType.Int);
parameters[2].Value = leave.Type;
parameters[3] = new SqlParameter("@Days", SqlDbType.Int);
parameters[3].Value = leave.Days;
parameters[4] = new SqlParameter("@ID", SqlDbType.Int);
parameters[4].Value = leave.ID;
StringBuilder sql = new StringBuilder();
sql.AppendLine("UPDATE LeaveInfo SET startDate = @StartDate, endDate = @EndDate, type = @Type, days = @Days");
sql.AppendLine("WHERE ID = @ID");
int rowCount = helper.ExecuteNonQuery(sql.ToString(), parameters.ToArray());
return rowCount > 0;
}
public static bool Update(RosteringInfo rostering, RosteringInfo rostering2)
{
SqlParameter[] parameters = new SqlParameter[11];
parameters[0] = new SqlParameter("@Name", SqlDbType.VarChar, 20);
parameters[0].Value = rostering.Name;
parameters[1] = new SqlParameter("@StartTime", SqlDbType.VarChar, 20);
parameters[1].Value = DateUtility.FormatTime(rostering.StartTime);
parameters[2] = new SqlParameter("@EarlyRange", SqlDbType.Int);
parameters[2].Value = rostering.EarlyRange;
parameters[3] = new SqlParameter("@EndTime", SqlDbType.VarChar, 20);
parameters[3].Value = DateUtility.FormatTime(rostering.EndTime);
parameters[4] = new SqlParameter("@LateRange", SqlDbType.Int);
parameters[4].Value = rostering.LateRange;
parameters[5] = new SqlParameter("@Duration", SqlDbType.Decimal);
parameters[5].Value = rostering.Duration;
parameters[6] = new SqlParameter("@NightWork", SqlDbType.Int);
parameters[6].Value = rostering.NightWork;
parameters[7] = new SqlParameter("@Flag", SqlDbType.Int);
parameters[7].Value = rostering.Active ? 1 : 0;
parameters[8] = new SqlParameter("@RealStartTime", SqlDbType.VarChar, 20);
parameters[8].Value = rostering.RealStartTime;
parameters[9] = new SqlParameter("@RealEndTime", SqlDbType.VarChar, 20);
parameters[9].Value = rostering.RealEndTime;
parameters[10] = new SqlParameter("@ID", SqlDbType.Int);
parameters[10].Value = rostering.ID;
StringBuilder sql = new StringBuilder();
sql.AppendLine("UPDATE Rostering SET bcName = @Name");
sql.AppendLine(" , startTime = @StartTime");
sql.AppendLine(" , earlyRange = @EarlyRange");
sql.AppendLine(" , endTime = @EndTime");
sql.AppendLine(" , lateRange = @LateRange");
sql.AppendLine(" , mulripleDur = @Duration");
sql.AppendLine(" , nightWork = @NightWork");
sql.AppendLine(" , flag = @Flag");
sql.AppendLine(" , realStartTime = @RealStartTime");
sql.AppendLine(" , realEndTime = @RealEndTime");
sql.AppendLine("WHERE ID = @ID");
int rowCount = helper.ExecuteNonQuery(sql.ToString(), parameters.ToArray());
//Update the record of 'afternoon'
if (rostering2 != null) {
SqlParameter[] parameters2 = new SqlParameter[11];
parameters2[0] = new SqlParameter("@Name", SqlDbType.VarChar, 20);
parameters2[0].Value = rostering.Name;
parameters2[1] = new SqlParameter("@StartTime", SqlDbType.VarChar, 20);
parameters2[1].Value = DateUtility.FormatTime(rostering2.StartTime);
parameters2[2] = new SqlParameter("@EarlyRange", SqlDbType.Int);
parameters2[2].Value = rostering2.EarlyRange;
parameters2[3] = new SqlParameter("@EndTime", SqlDbType.VarChar, 20);
parameters2[3].Value = DateUtility.FormatTime(rostering2.EndTime);
parameters2[4] = new SqlParameter("@LateRange", SqlDbType.Int);
parameters2[4].Value = rostering2.LateRange;
parameters2[5] = new SqlParameter("@Duration", SqlDbType.Decimal);
parameters2[5].Value = rostering2.Duration;
parameters2[6] = new SqlParameter("@NightWork", SqlDbType.Int);
parameters2[6].Value = rostering2.NightWork;
parameters2[7] = new SqlParameter("@Flag", SqlDbType.Int);
parameters2[7].Value = rostering.Active ? 1 : 0;
parameters2[8] = new SqlParameter("@RealStartTime", SqlDbType.VarChar, 20);
parameters2[8].Value = rostering2.RealStartTime;
parameters2[9] = new SqlParameter("@RealEndTime", SqlDbType.VarChar, 20);
parameters2[9].Value = rostering2.RealEndTime;
parameters2[10] = new SqlParameter("@ID", SqlDbType.Int);
parameters2[10].Value = rostering.ID;
sql.Clear();
sql.AppendLine("UPDATE Rostering SET bcName = @Name");
sql.AppendLine(" , startTime = @StartTime");
sql.AppendLine(" , earlyRange = @EarlyRange");
sql.AppendLine(" , endTime = @EndTime");
sql.AppendLine(" , lateRange = @LateRange");
sql.AppendLine(" , mulripleDur = @Duration");
sql.AppendLine(" , nightWork = @NightWork");
sql.AppendLine(" , flag = @Flag");
sql.AppendLine(" , realStartTime = @RealStartTime");
sql.AppendLine(" , realEndTime = @RealEndTime");
sql.AppendLine("WHERE ID = (SELECT nextID FROM Rostering WHERE ID = @ID)");
rowCount = helper.ExecuteNonQuery(sql.ToString(), parameters2.ToArray());
}
return rowCount > 0;
}
public void CreateCourse(Conference newConference)
{
string connection = ConfigurationManager.ConnectionStrings["EVENTSConnectionString"].ToString();
SqlConnection dbConnection = new SqlConnection(connection);
try
{
dbConnection.Open();
SqlParameter ConferenceIDParam = new SqlParameter("@EVENT_ID", System.Data.SqlDbType.Int);
ConferenceIDParam.Value = Int32.Parse(newConference.ConferenceID);
SqlParameter CourseNameParam = new SqlParameter("@CLASS_NAME", System.Data.SqlDbType.NVarChar, 50);
CourseNameParam.Value = this.courseName;
SqlParameter CourseStartTime = new SqlParameter("@START_DTTM", System.Data.SqlDbType.DateTime);
CourseStartTime.Value = this.startDttm;
SqlParameter CourseEndTime = new SqlParameter("@END_DTTM", System.Data.SqlDbType.DateTime);
CourseEndTime.Value = this.endDttm;
SqlParameter CostParam = new SqlParameter("@COST", System.Data.SqlDbType.Float);
CostParam.Value = this.Cost;
SqlParameter MaxEnrollment = new SqlParameter("@MAX_ENROLLMENT", System.Data.SqlDbType.Float);
MaxEnrollment.Value = this.MaxEnrollment;
SqlParameter courseID = new SqlParameter("@COURSE_ID", System.Data.SqlDbType.Int);
courseID.Direction = System.Data.ParameterDirection.Output;
SqlParameter[] CreateCourseParameters = new SqlParameter[] { ConferenceIDParam, CourseNameParam, CourseStartTime, CourseEndTime, CostParam, MaxEnrollment };
SqlCommand createCourse = new SqlCommand("CREATE_COURSE", dbConnection);
createCourse.CommandType = System.Data.CommandType.StoredProcedure;
createCourse.Parameters.AddRange(CreateCourseParameters.ToArray());
createCourse.ExecuteNonQuery();
this.courseID = createCourse.Parameters["EVENT_ID"].Value.ToString();
dbConnection.Close();
}
catch (SqlException exception)
{
// "<p> Error Code " + exception.Number + ": " + exception.Message + "</p>";
}
}
public static bool Update(DeptInfo dept)
{
SqlParameter[] parameters = new SqlParameter[4];
parameters[0] = new SqlParameter("@ID", SqlDbType.Int);
parameters[0].Value = dept.ID;
parameters[1] = new SqlParameter("@Name", SqlDbType.VarChar, 50);
parameters[1].Value = dept.Name;
parameters[2] = new SqlParameter("@UpID", SqlDbType.Int);
parameters[2].Value = dept.UpID;
parameters[3] = new SqlParameter("@Level", SqlDbType.Int);
parameters[3].Value = dept.Level;
StringBuilder sql = new StringBuilder();
sql.AppendLine("UPDATE DeptInfo SET deptName = @Name, upID = @UpID, [level] = @Level");
sql.AppendLine("WHERE deptId = @ID");
int rowCount = helper.ExecuteNonQuery(sql.ToString(), parameters.ToArray());
return rowCount > 0;
}
public static bool Update(DeviceInfo device)
{
SqlParameter[] parameters = new SqlParameter[10];
parameters[0] = new SqlParameter("@IP", SqlDbType.VarChar, 30);
parameters[0].Value = device.IP;
parameters[1] = new SqlParameter("@Port", SqlDbType.VarChar, 10);
parameters[1].Value = device.Port;
parameters[2] = new SqlParameter("@DeviceType", SqlDbType.VarChar, 2);
parameters[2].Value = device.DeviceType;
parameters[3] = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
parameters[3].Value = device.UserName;
parameters[4] = new SqlParameter("@Password", SqlDbType.VarChar, 50);
parameters[4].Value = device.Password;
parameters[5] = new SqlParameter("@AntNo", SqlDbType.Int);
parameters[5].Value = device.AntNo;
parameters[6] = new SqlParameter("@AccessFlag", SqlDbType.Int);
parameters[6].Value = device.AccessFlag;
parameters[7] = new SqlParameter("@Flag", SqlDbType.Int);
parameters[7].Value = device.Active ? 1 : 0;
parameters[8] = new SqlParameter("@ID", SqlDbType.Int);
parameters[8].Value = device.ID;
parameters[9] = new SqlParameter("@Location", SqlDbType.NVarChar, 50);
parameters[9].Value = device.Location;
StringBuilder sql = new StringBuilder();
sql.AppendLine("UPDATE DevInfo SET devIp = @IP, devPort = @Port");
sql.AppendLine(" , devType = @DeviceType, devUserName = @UserName, devPassword = @Password");
sql.AppendLine(" , antNo = @AntNo, accessFlag = @AccessFlag, flag = @Flag, Location = @Location");
sql.AppendLine("WHERE ID = @ID");
return helper.ExecuteNonQuery(sql.ToString(), parameters.ToArray()) > 0;
}
public void CreateConference(List<Course> NewCourses)
{
string connection = ConfigurationManager.ConnectionStrings["EVENTSConnectionString"].ToString();
SqlConnection dbConnection = new SqlConnection(connection);
try
{
dbConnection.Open();
SqlParameter ConferenceNameParam = new SqlParameter("@EVENT_NAME", System.Data.SqlDbType.NVarChar, 70);
ConferenceNameParam.Value = this.conferenceName;
SqlParameter LocationParam = new SqlParameter("@LOCATION", System.Data.SqlDbType.NVarChar, 70);
LocationParam.Value = this.location;
SqlParameter StartDateParam = new SqlParameter("@START_DT", System.Data.SqlDbType.DateTime);
StartDateParam.Value = this.startDate;
SqlParameter EndDateParam = new SqlParameter("@END_DT", System.Data.SqlDbType.DateTime);
StartDateParam.Value = this.endDate;
SqlParameter CostParam = new SqlParameter("@COST", System.Data.SqlDbType.Float);
CostParam.Value = this.wholeConferenceCost;
SqlParameter[] CreateConferenceParameters = new SqlParameter[] { ConferenceNameParam, LocationParam, StartDateParam, EndDateParam, CostParam };
SqlCommand createConference = new SqlCommand("CREATE_CONFERENCE", dbConnection);
createConference.CommandType = System.Data.CommandType.StoredProcedure;
createConference.Parameters.AddRange(CreateConferenceParameters.ToArray());
createConference.ExecuteNonQuery();
this.conferenceID = createConference.Parameters["EVENT_ID"].Value.ToString();
dbConnection.Close();
foreach (Course newCourse in coursesInConference)
newCourse.CreateCourse(this);
}
catch (SqlException exception)
{
// "<p> Error Code " + exception.Number + ": " + exception.Message + "</p>";
}
}
