public bool CalendarExists() { string strSql = ""; SqlCommand com = null; Connection con = null; try { strSql = "Select Count(*) From [CalendarEvent] " + "WHERE [EventID]=@EventID "; con = new Connection(); con.Connect(); com = new SqlCommand(); com.Connection = con.SQLCon; com.CommandText = strSql; com.Parameters.Add(new SqlParameter("@EventID", SqlDbType.Int)); com.Parameters["@EventID"].Value = EventID; object o = com.ExecuteScalar(); if (Convert.ToInt32(o) > 0) { return true; } return false; } catch (SqlException ex) { Message = ex.Message; return false; } finally { if (com != null) { com.Dispose(); com = null; con.DisConnect(); } } }
public bool UpdateData() { string sql=""; SqlCommand com=null; Connection con=null; try { sql = "Update [Contact] Set "; sql += "LastName=@LastName, "; sql += "LastNamePhonetic=@LastNamePhonetic, "; sql += "LastNameRomaji=@LastNameRomaji, "; sql += "FirstName=@FirstName, "; sql += "FirstNamePhonetic=@FirstNamePhonetic, "; sql += "FirstNameRomaji=@FirstNameRomaji, "; sql += "NickName=@NickName, "; sql += "CompanyName=@CompanyName, "; sql += "CompanyNamePhonetic=@CompanyNamePhonetic, "; sql += "CompanyNameRomaji=@CompanyNameRomaji, "; sql += "TitleForName=@TitleForName, "; sql += "TitleForJob=@TitleForJob, "; sql += "Street1=@Street1, "; sql += "Street2=@Street2, "; sql += "Street3=@Street3, "; sql += "City=@City, "; sql += "State=@State, "; sql += "PostalCode=@PostalCode, "; sql += "Country=@Country, "; sql += "ContactType=@ContactType, "; sql += "BlockCode=@BlockCode, "; sql += "Email1=@Email1, "; sql += "Email2=@Email2, "; sql += "AccountRepLastName=@AccountRepLastName, "; sql += "AccountRepLastNamePhonetic=@AccountRepLastNamePhonetic, "; sql += "AccountRepLastNameRomaji=@AccountRepLastNameRomaji, "; sql += "AccountRepFirstName=@AccountRepFirstName, "; sql += "AccountRepFirstNamePhonetic=@AccountRepFirstNamePhonetic, "; sql += "AccountRepFirstNameRomaji=@AccountRepFirstNameRomaji, "; sql += "Phone1=@Phone1, "; sql += "Phone2=@Phone2, "; sql += "PhoneMobile1=@PhoneMobile1, "; sql += "PhoneMobile2=@PhoneMobile2, "; sql += "PhoneBusiness1=@PhoneBusiness1, "; sql += "PhoneBusiness2=@PhoneBusiness2, "; sql += "PhoneFax1=@PhoneFax1, "; sql += "PhoneFax2=@PhoneFax2, "; sql += "PhoneOther=@PhoneOther, "; sql += "Url=@Url, "; sql += "DateBirth=@DateBirth, "; sql += "DateJoined=@DateJoined, "; sql += "DateEnded=@DateEnded, "; sql += "TimeStatus=@TimeStatus, "; sql += "Nationality=@Nationality, "; sql += "Married=@Married, "; sql += "NumberDependents=@NumberDependents, "; sql += "VisaStatus=@VisaStatus, "; sql += "VisaFromDate=@VisaFromDate, "; sql += "VisaUntilDate=@VisaUntilDate, "; sql += "ClosestStation1=@ClosestStation1, "; sql += "ClosestLine1=@ClosestLine1, "; sql += "MinutesToStation1=@MinutesToStation1, "; sql += "ClosestStation2=@ClosestStation2, "; sql += "ClosestLine2=@ClosestLine2, "; sql += "MinutesToStation2=@MinutesToStation2, "; sql += "ContactStatus=@ContactStatus, "; sql += "DateLastModified=@DateLastModified, "; sql += "LastModifiedByUserID=@LastModifiedByUserID, "; sql += "BasePayField=@BasePayField "; sql += "WHERE ContactID=@ContactID "; con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.CommandText = sql; com.Parameters.Add(new SqlParameter("@ContactID", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@LastNamePhonetic", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@LastNameRomaji", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@FirstNamePhonetic", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@FirstNameRomaji", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@NickName", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@CompanyName", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@CompanyNamePhonetic", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@CompanyNameRomaji", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@TitleForName", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@TitleForJob", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Street1", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Street2", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Street3", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@City", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@State", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@PostalCode", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Country", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@ContactType", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@BlockCode", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Email1", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Email2", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@AccountRepLastName", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@AccountRepLastNamePhonetic", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@AccountRepLastNameRomaji", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@AccountRepFirstName", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@AccountRepFirstNamePhonetic", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@AccountRepFirstNameRomaji", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Phone1", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Phone2", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@PhoneMobile1", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@PhoneMobile2", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@PhoneBusiness1", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@PhoneBusiness2", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@PhoneFax1", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@PhoneFax2", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@PhoneOther", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Url", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@DateBirth", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@DateJoined", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@DateEnded", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@TimeStatus", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Nationality", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Married", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@NumberDependents", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@VisaStatus", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@VisaFromDate", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@VisaUntilDate", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@ClosestStation1", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@ClosestLine1", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@MinutesToStation1", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@ClosestStation2", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@ClosestLine2", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@MinutesToStation2", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@ContactStatus", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@DateCreated", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@DateLastModified", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@LastModifiedByUserID", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@BasePayField", SqlDbType.Decimal)); com.Parameters["@ContactID"].Value = _contactid; com.Parameters["@LastName"].Value = LastName; com.Parameters["@LastNamePhonetic"].Value = LastNamePhonetic; com.Parameters["@LastNameRomaji"].Value = LastNameRomaji; com.Parameters["@FirstName"].Value = FirstName; com.Parameters["@FirstNamePhonetic"].Value = FirstNamePhonetic; com.Parameters["@FirstNameRomaji"].Value = FirstNameRomaji; com.Parameters["@NickName"].Value = NickName; com.Parameters["@CompanyName"].Value = CompanyName; com.Parameters["@CompanyNamePhonetic"].Value = CompanyNamePhonetic; com.Parameters["@CompanyNameRomaji"].Value = CompanyNameRomaji; com.Parameters["@TitleForName"].Value = TitleForName; com.Parameters["@TitleForJob"].Value = TitleForJob; com.Parameters["@Street1"].Value = Street1; com.Parameters["@Street2"].Value = Street2; com.Parameters["@Street3"].Value = Street3; com.Parameters["@City"].Value = City; com.Parameters["@State"].Value = State; com.Parameters["@PostalCode"].Value = PostalCode; com.Parameters["@Country"].Value = Country; com.Parameters["@ContactType"].Value = ContactType; com.Parameters["@BlockCode"].Value = BlockCode; com.Parameters["@Email1"].Value = Email1; com.Parameters["@Email2"].Value = Email2; com.Parameters["@AccountRepLastName"].Value = AccountRepLastName; com.Parameters["@AccountRepLastNamePhonetic"].Value = AccountRepLastNamePhonetic; com.Parameters["@AccountRepLastNameRomaji"].Value = AccountRepLastNameRomaji; com.Parameters["@AccountRepFirstName"].Value = AccountRepFirstName; com.Parameters["@AccountRepFirstNamePhonetic"].Value = AccountRepFirstNamePhonetic; com.Parameters["@AccountRepFirstNameRomaji"].Value = AccountRepFirstNameRomaji; com.Parameters["@Phone1"].Value = Phone1; com.Parameters["@Phone2"].Value = Phone2; com.Parameters["@PhoneMobile1"].Value = PhoneMobile1; com.Parameters["@PhoneMobile2"].Value = PhoneMobile2; com.Parameters["@PhoneBusiness1"].Value = PhoneBusiness1; com.Parameters["@PhoneBusiness2"].Value = PhoneBusiness2; com.Parameters["@PhoneFax1"].Value = PhoneFax1; com.Parameters["@PhoneFax2"].Value = PhoneFax2; com.Parameters["@PhoneOther"].Value = PhoneOther; com.Parameters["@Url"].Value = Url; if(DateBirth==Convert.ToDateTime(null)) com.Parameters["@DateBirth"].Value = System.DBNull.Value; else com.Parameters["@DateBirth"].Value = DateBirth; if(DateJoined==Convert.ToDateTime(null)) com.Parameters["@DateJoined"].Value = System.DBNull.Value; else com.Parameters["@DateJoined"].Value = DateJoined; if(DateEnded==Convert.ToDateTime(null)) com.Parameters["@DateEnded"].Value = System.DBNull.Value; else com.Parameters["@DateEnded"].Value = DateEnded; com.Parameters["@TimeStatus"].Value = TimeStatus; com.Parameters["@Nationality"].Value = Nationality; com.Parameters["@Married"].Value = Married; com.Parameters["@NumberDependents"].Value = NumberDependents; com.Parameters["@VisaStatus"].Value = VisaStatus; if(VisaFromDate==Convert.ToDateTime(null)) com.Parameters["@VisaFromDate"].Value = System.DBNull.Value; else com.Parameters["@VisaFromDate"].Value = VisaFromDate; if(VisaUntilDate==Convert.ToDateTime(null)) com.Parameters["@VisaUntilDate"].Value = System.DBNull.Value; else com.Parameters["@VisaUntilDate"].Value = VisaUntilDate; com.Parameters["@ClosestStation1"].Value = ClosestStation1; com.Parameters["@ClosestLine1"].Value = ClosestLine1; com.Parameters["@MinutesToStation1"].Value =MinutesToStation1; com.Parameters["@ClosestStation2"].Value = ClosestStation2; com.Parameters["@ClosestLine2"].Value = ClosestLine2; com.Parameters["@MinutesToStation2"].Value = MinutesToStation2; com.Parameters["@ContactStatus"].Value = ContactStatus; com.Parameters["@DateCreated"].Value = DateTime.Now; com.Parameters["@DateLastModified"].Value = DateTime.Now; com.Parameters["@LastModifiedByUserID"].Value = Scheduler.BusinessLayer.Common.LogonID; com.Parameters["@BasePayField"].Value = BaseRate; com.ExecuteNonQuery(); return true; } catch(SqlException ex) { Message=ex.Message; return false; } finally { if(com!=null) { com.Dispose(); com=null; con.DisConnect(); } } }
public string getOccurrenceCount(int eventid) { SqlCommand com = null; Connection con = null; //SqlDataReader Reader = null; string strSQL = string.Empty; string strResult = string.Empty; int result1; int result2; try { strSQL = "Select Count(*) from [CalendarEvent] WHERE EventId=@EventId AND StartDateTime < GetDate() AND CalendarEventStatus = 0;"; con = new Connection(); con.Connect(); com = new SqlCommand(strSQL, con.SQLCon); com.Parameters.Add(new SqlParameter("@EventId", SqlDbType.Int)); com.Parameters["@EventId"].Value = eventid; result1 = (int)com.ExecuteScalar(); //strSQL = "Select Count(*) from [CalendarEvent] WHERE EventId=@EventId AND EndDateTime >= GetDate() AND CalendarEventStatus = 0;"; strSQL = "Select Count(*) from [CalendarEvent] WHERE EventId=@EventId AND CalendarEventStatus = 0;"; com.CommandText = strSQL; //com.Parameters["@EventId"].Value = eventid; result2 = (int)com.ExecuteScalar(); //strResult = result1.ToString() + " / " + (result1 + result2).ToString(); strResult = result1.ToString() + " / " + (result2).ToString(); return strResult; } catch (SqlException ex) { Message = ex.Message; return "Error!"; } finally { if (com != null) { com.Dispose(); com = null; con.DisConnect(); } } }
public string getEventText(int eventid, ref string startdate, ref string enddate) { string Result=""; string strSql=""; SqlCommand com=null; Connection con=null; SqlDataReader Reader=null; DateTime dtStart=Convert.ToDateTime(null); DateTime dtEnd=Convert.ToDateTime(null); try { strSql = "Select StartDateTime, EndDateTime From [CalendarEvent] "; strSql += "WHERE EventID=@EventID"; con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.CommandText = strSql; com.Parameters.Add(new SqlParameter("@EventID", SqlDbType.BigInt)); com.Parameters["@EventID"].Value = eventid; Reader=com.ExecuteReader(); bool IsRecord=false; if(Reader.Read()) { IsRecord=true; if(Reader["StartDateTime"]!=System.DBNull.Value) { dtStart = Convert.ToDateTime(Reader["StartDateTime"].ToString()); } if(Reader["EndDateTime"]!=System.DBNull.Value) { dtEnd = Convert.ToDateTime(Reader["EndDateTime"].ToString()); } } Reader.Close(); if(IsRecord) { if(dtStart!=Convert.ToDateTime(null)) { Result = dtStart.ToShortDateString() + " " + dtStart.ToShortTimeString(); if(Result.IndexOf("(")>0) { Result = Result.Substring(0, Result.IndexOf("(")+1); } startdate = Result; } if(dtEnd!=Convert.ToDateTime(null)) { Result = dtEnd.ToShortDateString() + " " + dtEnd.ToShortTimeString(); if(Result.IndexOf("(")>0) { Result = Result.Substring(0, Result.IndexOf("(")+1); } enddate = Result; } } else Result="None"; return Result; } catch(SqlException ex) { Message=ex.Message; return ""; } finally { if(com!=null) { com.Dispose(); com=null; con.DisConnect(); } } }
public bool Exists() { string strSql=""; SqlCommand com=null; Connection con=null; try { strSql = "Select Count(*) From [Course] " + "WHERE [Name]=@Name and ProgramID=@ProgramID and CourseId<>" + _courseid + " "; con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.CommandText = strSql; com.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar)); com.Parameters["@Name"].Value = _name; com.Parameters.Add(new SqlParameter("@ProgramID", SqlDbType.Int)); com.Parameters["@ProgramID"].Value = _programid; object o = com.ExecuteScalar(); if(Convert.ToInt32(o)>0) { return true; } return false; } catch(SqlException ex) { Message=ex.Message; return false; } finally { if(com!=null) { com.Dispose(); com=null; con.DisConnect(); } } }
/// <summary> /// Checks whether Test Initial, Mid or Final are set for a Class or not. /// </summary> /// <returns>A boolean array with Test Event Ids or 0 if no events exist.</returns> public bool[] CheckTestEvents() { string strSql = ""; SqlDataReader Reader = null; SqlCommand com = null; Connection con = null; try { strSql = "Select TestInitialEventId,TestMidtermEventId,TestFinalEventId from [Course] WHERE CourseId=@CourseId;"; con = new Connection(); con.Connect(); com = new SqlCommand(strSql, con.SQLCon); com.Parameters.Add(new SqlParameter("@CourseId", SqlDbType.Int)); com.Parameters["@CourseId"].Value = _courseid; Reader = com.ExecuteReader(); string[] temp = new string[3]; bool[] boolArray = { false, false, false }; Reader.Read(); temp[0] = Reader[0].ToString(); temp[1] = Reader[1].ToString(); temp[2] = Reader[2].ToString(); Reader.Close(); IDataReader readerTemp = null; if (temp[0] != null && temp[0] != "" && temp[0] != "0") { readerTemp = DAC.SelectStatement("Select * From Event Where EventID = " + temp[0]); if (readerTemp.Read()) { boolArray[0] = true; } else { DAC.EXQuery("Update [Course] Set TestInitialEventId = 0 Where CourseId = " + _courseid); boolArray[0] = false; } } if (temp[1] != null && temp[1] != "" && temp[1] != "0") { readerTemp = DAC.SelectStatement("Select * From Event Where EventID = " + temp[1]); if (readerTemp.Read()) { boolArray[1] = true; } else { DAC.EXQuery("Update [Course] Set TestMidtermEventId = 0 Where CourseId = " + _courseid); boolArray[1] = false; } } if (temp[2] != null && temp[2] != "" && temp[2] != "0") { readerTemp = DAC.SelectStatement("Select * From Event Where EventID = " + temp[2]); if (readerTemp.Read()) { boolArray[2] = true; } else { DAC.EXQuery("Update [Course] Set TestFinalEventId = 0 Where CourseId = " + _courseid); boolArray[2] = false; } } return boolArray; } catch (SqlException ex) { Message = ex.Message; return null; } finally { if (com != null) { com.Dispose(); com = null; con.DisConnect(); } } }
public bool UpdateData() { string strSql=""; SqlCommand com=null; Connection con=null; try { strSql = "Update [Course] Set " + "Name=@Name, " + "NamePhonetic=@NamePhonetic, " + "NameRomaji=@NameRomaji, " + "NickName=@NickName, " + "ProgramID=@ProgramID, " + "EventID=@EventID, " + "Description=@Description, " + "SpecialRemarks=@SpecialRemarks, " + "CourseType=@CourseType, " + "Curriculam=@Curriculam, " + "NumberStudents=@NumberStudents, " + "HomeWorkMinutes=@HomeWorkMinutes, " + "TestInitialEventID=@TestInitialEventID, " + "TestMidtermEventID=@TestMidtermEventID, " + "TestFinalEventID=@TestFinalEventID, " + "TestInitialForm=@TestInitialForm, " + "TestMidtermForm=@TestMidtermForm, " + "TestFinalForm=@TestFinalForm, " + "CourseStatus=@CourseStatus, " + "DateLastModified=@DateLastModified, " + "LastModifiedByUserID=@LastModifiedByUserID, " + "BreakDuration=@BreakDuration " + "WHERE CourseId=@CourseId "; con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.CommandText = strSql; com.Parameters.Add(new SqlParameter("@CourseId", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@NamePhonetic", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@NameRomaji", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@NickName", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@ProgramID", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@EventID", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@Description", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@SpecialRemarks", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@CourseType", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Curriculam", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@NumberStudents", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@HomeWorkMinutes", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@TestInitialEventID", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@TestMidtermEventID", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@TestFinalEventID", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@TestInitialForm", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@TestMidtermForm", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@TestFinalForm", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@CourseStatus", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@DateCreated", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@DateLastModified", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@LastModifiedByUserID", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@BreakDuration", SqlDbType.Int)); com.Parameters["@CourseId"].Value = _courseid; com.Parameters["@Name"].Value = _name; com.Parameters["@NamePhonetic"].Value = _namephonetic; com.Parameters["@NameRomaji"].Value = _nameromaji; com.Parameters["@NickName"].Value = _nickname; com.Parameters["@ProgramID"].Value = _programid; com.Parameters["@EventID"].Value = _eventid; com.Parameters["@Description"].Value = _description; com.Parameters["@SpecialRemarks"].Value = _specialremarks; com.Parameters["@CourseType"].Value = _coursetype; com.Parameters["@Curriculam"].Value = _curriculam; com.Parameters["@NumberStudents"].Value = _numberstudents; com.Parameters["@HomeWorkMinutes"].Value = _homeworkminutes; com.Parameters["@TestInitialEventID"].Value = _testinieventid; com.Parameters["@TestMidtermEventID"].Value = _testmideventid; com.Parameters["@TestFinalEventID"].Value = _testfinaleventid; com.Parameters["@TestInitialForm"].Value = _testiniform; com.Parameters["@TestMidtermForm"].Value = _testmidform; com.Parameters["@TestFinalForm"].Value = _testfinalform; com.Parameters["@CourseStatus"].Value = _coursestatus; com.Parameters["@DateCreated"].Value = DateTime.Now; com.Parameters["@DateLastModified"].Value = DateTime.Now; com.Parameters["@LastModifiedByUserID"].Value = Scheduler.BusinessLayer.Common.LogonID; com.Parameters["@BreakDuration"].Value = _breakduration; com.ExecuteNonQuery(); return true; } catch(SqlException ex) { Message=ex.Message; return false; } finally { if(com!=null) { com.Dispose(); com=null; con.DisConnect(); } } }
//ViewClassEventsN public bool LoadDataN() { string strSql = "select * From ViewClassEventsN "; SqlDataReader Reader = null; SqlCommand com = null; Connection con = null; try { /* if (_courseid <= 0) { strSql += " Order By BrowseName "; } else { strSql += String.Format(" Where C.CourseId= {0} ", _courseid); } */ BuildDataTable(); /* con = new Connection(); con.Connect(); com = new SqlCommand(); com.Connection = con.SQLCon; com.CommandText = strSql; Reader = com.ExecuteReader(); */ ///* strSql = "SpClassEventsN"; con = new Connection(); con.Connect(); com = new SqlCommand(); com.CommandType = CommandType.StoredProcedure; com.Connection = con.SQLCon; com.CommandText = strSql; Reader = com.ExecuteReader(); //*/ _dtbl.Load(Reader, LoadOption.OverwriteChanges); return true; } catch (SqlException ex) { Message = ex.Message; return false; } finally { if (!Reader.IsClosed) { Reader.Close(); } if (com != null) { com.Dispose(); com = null; con.DisConnect(); } } }
public DataTable LoadData(string option) { string strSql=""; int contacttypeid=0; SqlCommand com=null; Connection con=null; SqlDataAdapter adpt=null; if(option=="User") contacttypeid=0; if(option=="Instructor") contacttypeid=1; if(option=="Client") contacttypeid=2; if(option=="Department") contacttypeid=3; if(option=="ClientContact") contacttypeid=4; if(option=="DepartmentContact") contacttypeid=5; if(_dtbl==null) { _dtbl=new DataTable(); } try { if(_contactid<=0) { strSql = "select BrowseName = CASE " + "WHEN NickName IS NULL THEN CompanyName " + "WHEN NickName = '' THEN CompanyName " + "ELSE NickName " + "END, " + "*, " + "Type = " + "CASE ContactType " + "When '0' Then 'User' " + "When '1' Then 'Teacher' " + "When '2' Then 'Client' " + "When '3' Then 'Department' " + "END, " + "Status = " + "CASE ContactStatus " + "When '0' Then 'Active' " + "When '1' Then 'Inactive' " + "END, " + "MaritalStatus = " + "CASE Married " + "When '0' Then 'Yes' " + "When '1' Then 'No' " + "ELSE '' " + "END,Email1,Email2,Phone1,Phone2,PhoneMobile1,PhoneMobile2,PhoneBusiness1,PhoneBusiness2, CASE When (AccountRepLastName + ', ' + AccountRepFirstName) <> ', ' Then (AccountRepLastName + ', ' + AccountRepFirstName) Else '' End as AccRepName " + "From Contact "; if(option!="Contact") { strSql += "Where ContactType = " + contacttypeid.ToString() + " "; if(RefID>0) { strSql += "and RefID = " + RefID.ToString() + " "; } } if(option=="Instructor") { strSql += "Order By LastName, FirstName "; } else if(option=="Client") { strSql += "Order By BrowseName "; } else if(option=="DepartmentContact") { strSql += "Order By LastName, FirstName "; } else { strSql += "Order By ContactID"; } } else { strSql = "select BrowseName = CASE " + "WHEN NickName IS NULL THEN CompanyName " + "WHEN NickName = '' THEN CompanyName " + "ELSE NickName " + "END, " + "*, " + "Type = " + "CASE ContactType " + "When '0' Then 'User' " + "When '1' Then 'Teacher' " + "When '2' Then 'Client' " + "When '3' Then 'Department' " + "END, " + "Status = " + "CASE ContactStatus " + "When '0' Then 'Active' " + "When '1' Then 'Inactive' " + "END, " + "MaritalStatus = " + "CASE Married " + "When '0' Then 'Yes' " + "When '1' Then 'No' " + "ELSE '' " + "END, CASE When (AccountRepLastName + ', ' + AccountRepFirstName) <> ', ' Then (AccountRepLastName + ', ' + AccountRepFirstName) Else '' End as AccRepName " + "From Contact " + "WHERE ContactID = " + _contactid + " "; } con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.CommandText = strSql; adpt=new SqlDataAdapter(); adpt.SelectCommand = com; adpt.Fill(_dtbl); if(contacttypeid==2) { _dtbl.Columns.Add("Contact1", Type.GetType("System.String")); _dtbl.Columns.Add("Contact2", Type.GetType("System.String")); _dtbl.Columns.Add("Contact1Phone", Type.GetType("System.String")); _dtbl.Columns.Add("Contact2Phone", Type.GetType("System.String")); //Get the contacts int contid=0; string contact1=""; string contact2=""; string phone1=""; string phone2=""; foreach(DataRow dr in _dtbl.Rows) { contid=0; contact1=""; contact2=""; phone1=""; phone2=""; contid=Convert.ToInt32(dr["ContactID"].ToString()); GetContact(contid, ref contact1, ref contact2, ref phone1, ref phone2); dr["Contact1"] = contact1; dr["Contact2"] = contact2; dr["Contact1Phone"] = phone1; dr["Contact2Phone"] = phone2; dr.AcceptChanges(); } } return _dtbl; } catch(SqlException ex) { Message=ex.Message; return null; } finally { if(com!=null) { com.Dispose(); com=null; con.DisConnect(); adpt.Dispose(); adpt=null; } } }
public bool InsertData() { string sql=""; SqlCommand com=null; Connection con=null; SqlDataReader Reader=null; try { sql = "Insert into [Contact] ("; sql += "RefID, "; sql += "LastName, "; sql += "LastNamePhonetic, "; sql += "LastNameRomaji, "; sql += "FirstName, "; sql += "FirstNamePhonetic, "; sql += "FirstNameRomaji, "; sql += "NickName, "; sql += "CompanyName, "; sql += "CompanyNamePhonetic, "; sql += "CompanyNameRomaji, "; sql += "TitleForName, "; sql += "TitleForJob, "; sql += "Street1, "; sql += "Street2, "; sql += "Street3, "; sql += "City, "; sql += "State, "; sql += "PostalCode, "; sql += "Country, "; sql += "ContactType, "; sql += "BlockCode, "; sql += "Email1, "; sql += "Email2, "; sql += "AccountRepLastName, "; sql += "AccountRepLastNamePhonetic, "; sql += "AccountRepLastNameRomaji, "; sql += "AccountRepFirstName, "; sql += "AccountRepFirstNamePhonetic, "; sql += "AccountRepFirstNameRomaji, "; sql += "Phone1, "; sql += "Phone2, "; sql += "PhoneMobile1, "; sql += "PhoneMobile2, "; sql += "PhoneBusiness1, "; sql += "PhoneBusiness2, "; sql += "PhoneFax1, "; sql += "PhoneFax2, "; sql += "PhoneOther, "; sql += "Url, "; sql += "DateBirth, "; sql += "DateJoined, "; sql += "DateEnded, "; sql += "TimeStatus, "; sql += "Nationality, "; sql += "Married, "; sql += "NumberDependents, "; sql += "VisaStatus, "; sql += "VisaFromDate, "; sql += "VisaUntilDate, "; sql += "ClosestStation1, "; sql += "ClosestLine1, "; sql += "MinutesToStation1, "; sql += "ClosestStation2, "; sql += "ClosestLine2, "; sql += "MinutesToStation2, "; sql += "ContactStatus, "; sql += "CreatedByUserId, "; sql += "DateCreated, "; sql += "DateLastModified, "; sql += "LastModifiedByUserID, "; sql += "BasePayField "; sql += ")"; sql += "Values( "; sql += "@RefID, "; sql += "@LastName, "; sql += "@LastNamePhonetic, "; sql += "@LastNameRomaji, "; sql += "@FirstName, "; sql += "@FirstNamePhonetic, "; sql += "@FirstNameRomaji, "; sql += "@NickName, "; sql += "@CompanyName, "; sql += "@CompanyNamePhonetic, "; sql += "@CompanyNameRomaji, "; sql += "@TitleForName, "; sql += "@TitleForJob, "; sql += "@Street1, "; sql += "@Street2, "; sql += "@Street3, "; sql += "@City, "; sql += "@State, "; sql += "@PostalCode, "; sql += "@Country, "; sql += "@ContactType, "; sql += "@BlockCode, "; sql += "@Email1, "; sql += "@Email2, "; sql += "@AccountRepLastName, "; sql += "@AccountRepLastNamePhonetic, "; sql += "@AccountRepLastNameRomaji, "; sql += "@AccountRepFirstName, "; sql += "@AccountRepFirstNamePhonetic, "; sql += "@AccountRepFirstNameRomaji, "; sql += "@Phone1, "; sql += "@Phone2, "; sql += "@PhoneMobile1, "; sql += "@PhoneMobile2, "; sql += "@PhoneBusiness1, "; sql += "@PhoneBusiness2, "; sql += "@PhoneFax1, "; sql += "@PhoneFax2, "; sql += "@PhoneOther, "; sql += "@Url, "; sql += "@DateBirth, "; sql += "@DateJoined, "; sql += "@DateEnded, "; sql += "@TimeStatus, "; sql += "@Nationality, "; sql += "@Married, "; sql += "@NumberDependents, "; sql += "@VisaStatus, "; sql += "@VisaFromDate, "; sql += "@VisaUntilDate, "; sql += "@ClosestStation1, "; sql += "@ClosestLine1, "; sql += "@MinutesToStation1, "; sql += "@ClosestStation2, "; sql += "@ClosestLine2, "; sql += "@MinutesToStation2, "; sql += "@ContactStatus, "; sql += "@CreatedByUserId, "; sql += "@DateCreated, "; sql += "@DateLastModified, "; sql += "@LastModifiedByUserID, "; sql += "@BasePayField "; sql += ") "; sql += "SELECT @@IDENTITY"; con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.CommandText = sql; com.Parameters.Add(new SqlParameter("@RefID", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@LastNamePhonetic", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@LastNameRomaji", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@FirstNamePhonetic", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@FirstNameRomaji", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@NickName", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@CompanyName", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@CompanyNamePhonetic", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@CompanyNameRomaji", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@TitleForName", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@TitleForJob", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Street1", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Street2", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Street3", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@City", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@State", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@PostalCode", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Country", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@ContactType", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@BlockCode", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Email1", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Email2", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@AccountRepLastName", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@AccountRepLastNamePhonetic", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@AccountRepLastNameRomaji", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@AccountRepFirstName", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@AccountRepFirstNamePhonetic", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@AccountRepFirstNameRomaji", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Phone1", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Phone2", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@PhoneMobile1", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@PhoneMobile2", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@PhoneBusiness1", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@PhoneBusiness2", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@PhoneFax1", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@PhoneFax2", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@PhoneOther", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Url", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@DateBirth", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@DateJoined", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@DateEnded", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@TimeStatus", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Nationality", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Married", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@NumberDependents", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@VisaStatus", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@VisaFromDate", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@VisaUntilDate", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@ClosestStation1", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@ClosestLine1", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@MinutesToStation1", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@ClosestStation2", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@ClosestLine2", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@MinutesToStation2", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@ContactStatus", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@CreatedByUserId", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@DateCreated", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@DateLastModified", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@LastModifiedByUserID", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@BasePayField", SqlDbType.Decimal)); com.Parameters["@RefID"].Value = RefID; com.Parameters["@LastName"].Value = LastName; com.Parameters["@LastNamePhonetic"].Value = LastNamePhonetic; com.Parameters["@LastNameRomaji"].Value = LastNameRomaji; com.Parameters["@FirstName"].Value = FirstName; com.Parameters["@FirstNamePhonetic"].Value = FirstNamePhonetic; com.Parameters["@FirstNameRomaji"].Value = FirstNameRomaji; com.Parameters["@NickName"].Value = NickName; com.Parameters["@CompanyName"].Value = CompanyName; com.Parameters["@CompanyNamePhonetic"].Value = CompanyNamePhonetic; com.Parameters["@CompanyNameRomaji"].Value = CompanyNameRomaji; com.Parameters["@TitleForName"].Value = TitleForName; com.Parameters["@TitleForJob"].Value = TitleForJob; com.Parameters["@Street1"].Value = Street1; com.Parameters["@Street2"].Value = Street2; com.Parameters["@Street3"].Value = Street3; com.Parameters["@City"].Value = City; com.Parameters["@State"].Value = State; com.Parameters["@PostalCode"].Value = PostalCode; com.Parameters["@Country"].Value = Country; com.Parameters["@ContactType"].Value = ContactType; com.Parameters["@BlockCode"].Value = BlockCode; com.Parameters["@Email1"].Value = Email1; com.Parameters["@Email2"].Value = Email2; com.Parameters["@AccountRepLastName"].Value = AccountRepLastName; com.Parameters["@AccountRepLastNamePhonetic"].Value = AccountRepLastNamePhonetic; com.Parameters["@AccountRepLastNameRomaji"].Value = AccountRepLastNameRomaji; com.Parameters["@AccountRepFirstName"].Value = AccountRepFirstName; com.Parameters["@AccountRepFirstNamePhonetic"].Value = AccountRepFirstNamePhonetic; com.Parameters["@AccountRepFirstNameRomaji"].Value = AccountRepFirstNameRomaji; com.Parameters["@Phone1"].Value = Phone1; com.Parameters["@Phone2"].Value = Phone2; com.Parameters["@PhoneMobile1"].Value = PhoneMobile1; com.Parameters["@PhoneMobile2"].Value = PhoneMobile2; com.Parameters["@PhoneBusiness1"].Value = PhoneBusiness1; com.Parameters["@PhoneBusiness2"].Value = PhoneBusiness2; com.Parameters["@PhoneFax1"].Value = PhoneFax1; com.Parameters["@PhoneFax2"].Value = PhoneFax2; com.Parameters["@PhoneOther"].Value = PhoneOther; com.Parameters["@Url"].Value = Url; if(DateBirth==Convert.ToDateTime(null)) com.Parameters["@DateBirth"].Value = DBNull.Value; else com.Parameters["@DateBirth"].Value = DateBirth; if(DateJoined==Convert.ToDateTime(null)) com.Parameters["@DateJoined"].Value = DBNull.Value; else com.Parameters["@DateJoined"].Value = DateJoined; if(DateEnded==Convert.ToDateTime(null)) com.Parameters["@DateEnded"].Value = DBNull.Value; else com.Parameters["@DateEnded"].Value = DateEnded; com.Parameters["@TimeStatus"].Value = TimeStatus; com.Parameters["@Nationality"].Value = Nationality; com.Parameters["@Married"].Value = Married; com.Parameters["@NumberDependents"].Value = NumberDependents; com.Parameters["@VisaStatus"].Value = VisaStatus; if(VisaFromDate==Convert.ToDateTime(null)) com.Parameters["@VisaFromDate"].Value = DBNull.Value; else com.Parameters["@VisaFromDate"].Value = VisaFromDate; if(VisaUntilDate==Convert.ToDateTime(null)) com.Parameters["@VisaUntilDate"].Value = DBNull.Value; else com.Parameters["@VisaUntilDate"].Value = VisaUntilDate; com.Parameters["@ClosestStation1"].Value = ClosestStation1; com.Parameters["@ClosestLine1"].Value = ClosestLine1; com.Parameters["@MinutesToStation1"].Value =MinutesToStation1; com.Parameters["@ClosestStation2"].Value = ClosestStation2; com.Parameters["@ClosestLine2"].Value = ClosestLine2; com.Parameters["@MinutesToStation2"].Value = MinutesToStation2; com.Parameters["@ContactStatus"].Value = ContactStatus; com.Parameters["@CreatedByUserId"].Value = Scheduler.BusinessLayer.Common.LogonID; com.Parameters["@DateCreated"].Value = DateTime.Now; com.Parameters["@DateLastModified"].Value = DateTime.Now; com.Parameters["@LastModifiedByUserID"].Value = Scheduler.BusinessLayer.Common.LogonID; com.Parameters["@BasePayField"].Value = BaseRate; Reader = com.ExecuteReader(); if(Reader.Read()) { _contactid = Convert.ToInt32(Reader[0].ToString()); } return true; } catch(SqlException ex) { Message=ex.Message; return false; } finally { if(Reader!=null) { if(Reader.IsClosed==false) { Reader.Close(); Reader=null; } } if(com!=null) { com.Dispose(); com=null; con.DisConnect(); } } }
public bool Exists(string strComp, int ClientID, int ContactType) { string strSql=""; SqlCommand com=null; Connection con=null; try { strSql = "Select Count(*) as cnt From [Contact] C, Department D " + "Where C.ContactID=D.ContactID and " + "C.ContactType=@ContactType and " + "C.CompanyName=@CompanyName and " + "D.ClientID=@ClientID "; con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.CommandText = strSql; com.Parameters.Add(new SqlParameter("@CompanyName", SqlDbType.NVarChar)); com.Parameters["@CompanyName"].Value = strComp; com.Parameters.Add(new SqlParameter("@ContactType", SqlDbType.NVarChar)); com.Parameters["@ContactType"].Value = ContactType; com.Parameters.Add(new SqlParameter("@ClientID", SqlDbType.BigInt)); com.Parameters["@ClientID"].Value = ClientID; object o = com.ExecuteScalar(); if(Convert.ToInt32(o)>0) { return true; } return false; } catch(SqlException ex) { Message=ex.Message; return false; } finally { if(com!=null) { com.Dispose(); com=null; con.DisConnect(); } } }
public bool Exists(string FirstName, string LastName, int ContactType) { string strSql=""; SqlCommand com=null; Connection con=null; try { strSql = "Select Count(*) From [Contact] " + "WHERE LastName=@LastName and FirstName=@FirstName And ContactType=@ContactType and ContactID<>" + _contactid + " "; con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.CommandText = strSql; com.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar)); com.Parameters["@LastName"].Value = LastName; com.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar)); com.Parameters["@FirstName"].Value = FirstName; com.Parameters.Add(new SqlParameter("@ContactType", SqlDbType.NVarChar)); com.Parameters["@ContactType"].Value = ContactType; object o = com.ExecuteScalar(); if(Convert.ToInt32(o)>0) { return true; } return false; } catch(SqlException ex) { Message=ex.Message; return false; } finally { if(com!=null) { com.Dispose(); com=null; con.DisConnect(); } } }
public void DeleteContactFromProgram() { string strSql = ""; SqlCommand com = null; Connection con = null; try { strSql = "Update [Program] SET Contact1ID=0 WHERE Contact1ID=@ContactID;"; strSql += "Update [Program] SET Contact2ID=0 WHERE Contact2ID=@ContactID;"; con = new Connection(); con.Connect(); com = new SqlCommand(); com.Connection = con.SQLCon; com.CommandText = strSql; com.Parameters.Add(new SqlParameter("@ContactID", SqlDbType.Int)); com.Parameters["@ContactID"].Value = _contactid; com.ExecuteNonQuery(); /* strSql = "Delete from contact where RefID=@ContactID AND ContactType=4"; com.Parameters["@ContactID"].Value = _contactid; com.CommandText = strSql; com.ExecuteNonQuery(); */ return; } catch (SqlException ex) { Message = ex.Message; return; } finally { if (com != null) { com.Dispose(); com = null; con.DisConnect(); } } }
public bool ContactExists(int refid, string fname, string lname) { string strSql=""; SqlCommand com=null; Connection con=null; try { strSql = "Select Count(*) From [Contact] " + "WHERE FirstName=@FName AND LastName=@LName AND ContactType=@ContactType and RefID=@RefID "; con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.CommandText = strSql; com.Parameters.Add(new SqlParameter("@FName", SqlDbType.NVarChar)); com.Parameters["@FName"].Value = fname; com.Parameters.Add(new SqlParameter("@LName", SqlDbType.NVarChar)); com.Parameters["@LName"].Value = lname; com.Parameters.Add(new SqlParameter("@ContactType", SqlDbType.NVarChar)); com.Parameters["@ContactType"].Value = ContactType; com.Parameters.Add(new SqlParameter("@RefID", SqlDbType.Int)); com.Parameters["@RefID"].Value = refid; object o = com.ExecuteScalar(); if(Convert.ToInt32(o)>0) { return true; } return false; } catch(SqlException ex) { Message=ex.Message; return false; } finally { if(com!=null) { com.Dispose(); com=null; con.DisConnect(); } } }
public bool UpdateRefID(int randomno) { string sql=""; SqlCommand com=null; Connection con=null; try { sql = "Update [Contact] Set RefID=" + RefID.ToString() + " Where RefID=" + randomno.ToString() + " "; con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.CommandText = sql; com.ExecuteNonQuery(); return true; } catch(SqlException ex) { Message=ex.Message; return false; } finally { if(com!=null) { com.Dispose(); com=null; con.DisConnect(); } } }
/// <summary> /// Determines whether the class is a single event or a series of repeating events. /// </summary> /// <returns>A boolean indicating if a class repeats or not.</returns> public bool IsRecurring() { string strSql = ""; SqlCommand com = null; Connection con = null; try { strSql = "Select Count(*) from [Event] WHERE RecurrenceText IS NOT NULL AND RecurrenceText<>'' AND EventId=" + _eventid + ";"; con = new Connection(); con.Connect(); com = new SqlCommand(strSql, con.SQLCon); int result = (int)com.ExecuteScalar(); //Means the class reocurrs alright if (result == 1) return true; else return false; } catch (SqlException ex) { Message = ex.Message; return false; } finally { if (com != null) { com.Dispose(); com = null; con.DisConnect(); } } }
public bool LoadData() { string strSql=""; SqlDataReader Reader=null; SqlCommand com=null; Connection con=null; try { if(_courseid<=0) { strSql += "Select C.*, CASE " + "WHEN C.NickName IS NULL THEN C.Name " + "WHEN C.NickName = '' THEN C.Name " + "ELSE C.NickName " + "END AS BrowseName, " + "P.ProgramID, " + "CASE " + "WHEN P.NickName IS NULL THEN P.Name " + "WHEN P.NickName = '' THEN P.Name " + "ELSE P.NickName " + "END AS Program, " + "CASE " + "WHEN CO.NickName IS NULL THEN CO.CompanyName " + "WHEN CO.NickName = '' THEN CO.CompanyName " + "ELSE CO.NickName " + "END AS Department, " + "CASE " + "WHEN CO1.NickName IS NULL THEN CO1.CompanyName " + "WHEN CO1.NickName = '' THEN CO1.CompanyName " + "ELSE CO1.NickName " + "END AS Client " + "from Course C " + "Left Join Program P on (C.ProgramID=P.ProgramID) " + "Left Join Department D on (P.DepartmentID=D.DepartmentID) " + "Left Join Contact CO on (D.ContactID=CO.ContactID) " + "Left Join Contact CO1 on (D.ClientID=CO1.ContactID) " + "Order By BrowseName "; } else { strSql += "Select C.*, CASE " + "WHEN C.NickName IS NULL THEN C.Name " + "WHEN C.NickName = '' THEN C.Name " + "ELSE C.NickName " + "END AS BrowseName, " + "P.ProgramID, " + "P.NickName, CASE " + "WHEN P.NickName IS NULL THEN P.Name " + "WHEN P.NickName = '' THEN P.Name " + "ELSE P.NickName " + "END AS Program, " + "CASE " + "WHEN CO.NickName IS NULL THEN CO.CompanyName " + "WHEN CO.NickName = '' THEN CO.CompanyName " + "ELSE CO.NickName " + "END AS Department, " + "CASE " + "WHEN CO1.NickName IS NULL THEN CO1.CompanyName " + "WHEN CO1.NickName = '' THEN CO1.CompanyName " + "ELSE CO1.NickName " + "END AS Client " + "from Course C " + "Left Join Program P on (C.ProgramID=P.ProgramID) " + "Left Join Department D on (P.DepartmentID=D.DepartmentID) " + "Left Join Contact CO on (D.ContactID=CO.ContactID) " + "Left Join Contact CO1 on (D.ClientID=CO1.ContactID) " + "Where C.CourseId=" + _courseid.ToString() + " "; } BuildDataTable(); con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.CommandText = strSql; Reader = com.ExecuteReader(); string strstatus=""; while(Reader.Read()) { strstatus=""; if(Convert.ToInt16(Reader["CourseStatus"].ToString())==0) { strstatus = "Active"; } else { strstatus = "Inactive"; } _courseid = Convert.ToInt32(Reader["CourseId"].ToString()); _name = Reader["Name"].ToString(); _namephonetic = Reader["NamePhonetic"].ToString(); _nameromaji = Reader["NameRomaji"].ToString(); if(Reader["ProgramID"]!=System.DBNull.Value) { _programid = Convert.ToInt32(Reader["ProgramID"].ToString()); } _program = Reader["Program"].ToString(); _nickname = Reader["NickName"].ToString(); _description = Reader["Description"].ToString(); _coursetype = Reader["CourseType"].ToString(); _specialremarks = Reader["SpecialRemarks"].ToString(); _curriculam = Reader["Curriculam"].ToString(); _numberstudents = Convert.ToInt32(Reader["NumberStudents"].ToString()); _homeworkminutes = Convert.ToInt32(Reader["HomeWorkMinutes"].ToString()); _eventid = Convert.ToInt32(Reader["EventID"].ToString()); _testinieventid = Convert.ToInt32(Reader["TestInitialEventID"].ToString()); _testmideventid = Convert.ToInt32(Reader["TestMidtermEventID"].ToString()); _testfinaleventid = Convert.ToInt32(Reader["TestFinalEventID"].ToString()); _testiniform = Reader["TestInitialForm"].ToString(); _testmidform = Reader["TestMidtermForm"].ToString(); _testfinalform = Reader["TestFinalForm"].ToString(); _coursestatus = Convert.ToInt32(Reader["CourseStatus"].ToString()); _breakduration = Convert.ToInt32(Reader["BreakDuration"].ToString()); _dtbl.Rows.Add(new object[] { _courseid, Reader["BrowseName"].ToString(), _name, _namephonetic, _nameromaji, _nickname, _programid, _program, Reader["Client"].ToString(), Reader["Department"].ToString(), _description, _coursetype, _specialremarks, _curriculam, _numberstudents, _homeworkminutes, _eventid, _testinieventid, _testmideventid, _testfinaleventid, Reader["TestInitialForm"].ToString(), Reader["TestMidtermForm"].ToString(), Reader["TestFinalForm"].ToString(), strstatus, _breakduration }); } Reader.Close(); int intEID=0; string startdate="", enddate=""; string instructorName = ""; foreach(DataRow dr in _dtbl.Rows) { intEID = Convert.ToInt32(dr["EventID"].ToString()); //getEventText(intEID, ref startdate, ref enddate); startdate = getEventText(intEID, true,true,ref instructorName); enddate = getEventText(intEID, false); // gad - Enable these comments 1-3 in order to allow a cloned Class that has no start or end date // to be opened instead of generating an exception //1 if (startdate.ToString() != "None") //2 { dr["EventStartDateTime"] = startdate; dr["EventEndDateTime"] = enddate; dr["OccurrenceCount"] = getOccurrenceCount(intEID); dr["ScheduledInstructor"] = instructorName; dr.AcceptChanges(); //3 } } return true; } catch(SqlException ex) { Message=ex.Message; return false; } finally { if(!Reader.IsClosed) { Reader.Close(); } if(com!=null) { com.Dispose(); com=null; con.DisConnect(); } } }
public bool DeleteData() { string strSql=""; SqlCommand com=null; Connection con=null; try { strSql = "Delete From [Department] " + "WHERE DepartmentID=@DepartmentID "; con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.CommandText = strSql; com.Parameters.Add(new SqlParameter("@DepartmentID", SqlDbType.Int)); com.Parameters["@DepartmentID"].Value = _deptid; com.ExecuteNonQuery(); strSql = "Delete from contact where RefID=@DepartmentID AND ContactType=5"; com.Parameters["@DepartmentID"].Value = _deptid; com.CommandText=strSql; com.ExecuteNonQuery(); com.CommandText = "Delete from [Contact] Where ContactID=@ContactID"; com.Parameters.Clear(); com.Parameters.Add(new SqlParameter("@ContactID", SqlDbType.Int)); com.Parameters["@ContactID"].Value = _contactid; com.ExecuteNonQuery(); return true; } catch(SqlException ex) { Message=ex.Message; return false; } finally { if(com!=null) { com.Dispose(); com=null; con.DisConnect(); } } }
//Loads Other Events (Events with 'EventType' fields set to anything but ZERO. /* Event Type Field Values: * 'Class' = Repeating or Single Occurence * 'Test Initial', 'Test Mid', 'Test Final', 'Extra Class' */ public DataTable LoadOtherEvents(bool IsRecurring) { string strSql = ""; SqlDataReader Reader = null; SqlCommand com = null; Connection con = null; try { //Here we need to make sure that we use different commands for selecting Tests and Extra Classes //because extra classes only exist for repeating class events and NOT single occuring class events! //Loading Positive Exceptions first _dtbl = new DataTable(); /* strSql = "Select Count(*) from Event WHERE RecurrenceText IS NOT NULL AND EventId=" + _eventid + ";"; con = new Connection(); con.Connect(); com = new SqlCommand(strSql, con.SQLCon); int result = (int)com.ExecuteScalar(); */ con = new Connection(); con.Connect(); //Means the class event exists and class reocurrs alright if (_eventid > 0 && IsRecurring) { strSql = "Select * from [CalendarEvent] WHERE EventId=" + _eventid + " AND EventType='Extra Class';"; com = new SqlCommand(strSql, con.SQLCon); Reader = com.ExecuteReader(); if (Reader.HasRows) _dtbl.Load(Reader); Reader.Close(); } //Now, Loading the Test Events (if any) strSql = "Select TestInitialEventId,TestMidtermEventId,TestFinalEventId from [Course] WHERE CourseId=@CourseId"; com = new SqlCommand(strSql, con.SQLCon); com.Parameters.Add(new SqlParameter("@CourseId", SqlDbType.Int)); com.Parameters["@CourseId"].Value = _courseid; Reader = com.ExecuteReader(); string[] temp = new string[3]; Reader.Read(); temp[0] = Reader[0].ToString(); temp[1] = Reader[1].ToString(); temp[2] = Reader[2].ToString(); Reader.Close(); string temp2 = ""; if (temp[0] != null && temp[0] != "") temp2 = temp[0]; if (temp[1] != null && temp[1] != "") temp2 += "," + temp[1]; if (temp[2] != null && temp[2] != "") temp2 += "," + temp[2]; strSql = "SELECT * from [CalendarEvent] WHERE EventType LIKE 'Test%' AND EventId IN (" + temp2 + ");"; //com = new SqlCommand(strSql, con.SQLCon); com.CommandText = strSql; Reader = com.ExecuteReader(); if (Reader.HasRows) { if (_dtbl.Rows.Count==0) _dtbl.Load(Reader); else { DataTable _temp = new DataTable(); _temp.Load(Reader); _dtbl.Merge(_temp); } } Reader.Close(); return _dtbl; } catch (SqlException ex) { Message = ex.Message; return null; } finally { if (com != null) { com.Dispose(); com = null; con.DisConnect(); } } }
public bool InsertData() { string strSql=""; SqlCommand com=null; Connection con=null; try { strSql = "Insert Into [Department] " + "(" + "ContactID, " + "ClientID, " + "DepartmentStatus, " + "CreatedByUserId, " + "DateCreated, " + "DateLastModified, " + "LastModifiedByUserID) " + "Values( " + "@contactid, " + "@clientid, " + "@DepartmentStatus, " + "@CreatedByUserId, " + "@DateCreated, " + "@DateLastModified, " + "@LastModifiedByUserID " + ") SELECT @@IDENTITY"; con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.CommandText = strSql; com.Parameters.Add(new SqlParameter("@ClientId", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@ContactId", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@DepartmentStatus", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@CreatedByUserId", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@DateCreated", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@DateLastModified", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@LastModifiedByUserID", SqlDbType.Int)); com.Parameters["@ContactId"].Value = _contactid; com.Parameters["@ClientId"].Value = _clientid; com.Parameters["@DepartmentStatus"].Value = _statusid; com.Parameters["@CreatedByUserId"].Value = Scheduler.BusinessLayer.Common.LogonID; com.Parameters["@DateCreated"].Value = DateTime.Now; com.Parameters["@DateLastModified"].Value = DateTime.Now; com.Parameters["@LastModifiedByUserID"].Value = Scheduler.BusinessLayer.Common.LogonID; SqlDataReader Reader = com.ExecuteReader(); if(Reader.Read()) { _deptid = Convert.ToInt32(Reader[0].ToString()); } Reader.Close(); return true; } catch(SqlException ex) { Message=ex.Message; return false; } finally { if(com!=null) { com.Dispose(); com=null; con.DisConnect(); } } }
public static int CloneData(int CourseId) { string strSql = ""; SqlCommand com = null; Connection con = null; try { strSql = "usp_CourseClone"; con = new Connection(); con.Connect(); com = new SqlCommand(); com.CommandType = CommandType.StoredProcedure; com.Connection = con.SQLCon; com.CommandText = strSql; com.Parameters.Add(new SqlParameter("CourseId", SqlDbType.Int)); com.Parameters["CourseId"].Value = CourseId; com.Parameters.Add(new SqlParameter("creatorID", SqlDbType.Int)); com.Parameters["creatorID"].Value = Common.LogonID; com.Parameters.Add(new SqlParameter("insertedID", SqlDbType.Int)); com.Parameters["insertedID"].Direction = ParameterDirection.Output; com.ExecuteNonQuery(); return (int)com.Parameters["insertedID"].Value; } finally { if (com != null) { com.Dispose(); com = null; con.DisConnect(); } } }
public bool LoadData() { string strSql=""; SqlDataReader Reader=null; SqlCommand com=null; Connection con=null; try { if(_deptid<=0) { strSql = "select D.*, "; strSql += "C.NickName, C.ContactID As ContactID, C.LastName + ', ' + C.FirstName As Contact, "; strSql += "C.TitleForName, CASE "; strSql += "WHEN C.NickName IS NULL THEN C.CompanyName "; strSql += "WHEN C.NickName = '' THEN C.CompanyName "; strSql += "ELSE C.NickName "; strSql += "END AS CompanyName, "; strSql += "C1.ContactID As ClientID, CASE "; strSql += "WHEN C1.NickName IS NULL THEN C1.CompanyName "; strSql += "WHEN C1.NickName = '' THEN C1.CompanyName "; strSql += "ELSE C1.NickName "; strSql += "END AS Client "; strSql += "From Department D "; strSql += "Left Join Contact C on(D.ContactID=C.ContactID) "; strSql += "Left Join Contact C1 on(D.ClientID=C1.ContactID) "; strSql += "Order By D.DepartmentID "; } else { strSql = "select D.*, "; strSql += "C.NickName, C.ContactID As ContactID, C.LastName + ', ' + C.FirstName As Contact, "; strSql += "C.TitleForName, CASE "; strSql += "WHEN C.NickName IS NULL THEN C.CompanyName "; strSql += "WHEN C.NickName = '' THEN C.CompanyName "; strSql += "ELSE C.NickName "; strSql += "END AS CompanyName, "; strSql += "C1.ContactID As ClientID, CASE "; strSql += "WHEN C1.NickName IS NULL THEN C1.CompanyName "; strSql += "WHEN C1.NickName = '' THEN C1.CompanyName "; strSql += "ELSE C1.NickName "; strSql += "END AS Client "; strSql += "From Department D "; strSql += "Left Join Contact C on(D.ContactID=C.ContactID) "; strSql += "Left Join Contact C1 on(D.ClientID=C1.ContactID) "; strSql += "Where D.DepartmentID=" + _deptid.ToString() + " "; } BuildDataTable(); con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.CommandText = strSql; Reader = com.ExecuteReader(); string strstatus; string strcompname; while(Reader.Read()) { strstatus=""; strcompname=""; //company if(Reader["NickName"].ToString()!="") { strcompname = Reader["NickName"].ToString() + " "; } else { if(Reader["TitleForName"].ToString()!="") strcompname = Reader["TitleForName"].ToString() + " "; if(Reader["CompanyName"].ToString()!="") strcompname += Reader["CompanyName"].ToString(); } strcompname=strcompname.Trim(); //status if(Convert.ToInt16(Reader["DepartmentStatus"].ToString())==0) { strstatus = "Active"; } else { strstatus = "Inactive"; } _deptid = Convert.ToInt32(Reader["DepartmentID"].ToString()); _contactid = Convert.ToInt32(Reader["ContactID"].ToString()); _contactname = Reader["Contact"].ToString(); _clientid = Convert.ToInt32(Reader["ClientID"].ToString()); _clientname = Reader["Client"].ToString(); _statusid = Convert.ToInt16(Reader["DepartmentStatus"].ToString()); _dtbl.Rows.Add(new object[] { _deptid, strcompname, _contactid, _contactname, _clientid, _clientname, "", "", strstatus }); } Reader.Close(); //Get the contacts int deptid=0; string contact1="",contact1Phone = ""; string contact2="",contact2Phone = ""; foreach(DataRow dr in _dtbl.Rows) { deptid=0; contact1=""; contact2=""; contact1Phone = ""; contact2Phone = ""; deptid=Convert.ToInt32(dr["DepartmentID"].ToString()); GetContact(deptid, ref contact1, ref contact2,ref contact1Phone,ref contact2Phone); dr["Contact1"] = contact1; dr["Contact2"] = contact2; dr["Contact1Phone"] = contact1Phone; dr["Contact2Phone"] = contact2Phone; dr.AcceptChanges(); } return true; } catch(SqlException ex) { Message=ex.Message; return false; } finally { if(!Reader.IsClosed) { Reader.Close(); } if(com!=null) { com.Dispose(); com=null; con.DisConnect(); } } }
public bool DeleteData() { string strSql=""; SqlCommand com=null; Connection con=null; try { con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.Parameters.Add(new SqlParameter("@CourseId", SqlDbType.Int)); com.Parameters["@CourseId"].Value = _courseid; //Test Initial Event ID strSql = "Delete From [Event] " + "WHERE EventID IN (Select TestInitialEventID From [Course] " + "WHERE CourseId=@CourseId) "; com.CommandText=strSql; com.ExecuteNonQuery(); strSql = "Delete From [CalendarEvent] " + "WHERE EventID IN (Select TestInitialEventID From [Course] " + "WHERE CourseId=@CourseId) "; com.CommandText=strSql; com.ExecuteNonQuery(); //Test MidTerm Event ID strSql = "Delete From [Event] " + "WHERE EventID IN (Select TestMidTermEventID From [Course] " + "WHERE CourseId=@CourseId) "; com.CommandText=strSql; com.ExecuteNonQuery(); strSql = "Delete From [CalendarEvent] " + "WHERE EventID IN (Select TestMidTermEventID From [Course] " + "WHERE CourseId=@CourseId) "; com.CommandText=strSql; com.ExecuteNonQuery(); //Test Final Event ID strSql = "Delete From [Event] " + "WHERE EventID IN (Select TestFinalEventID From [Course] " + "WHERE CourseId=@CourseId) "; com.CommandText=strSql; com.ExecuteNonQuery(); strSql = "Delete From [CalendarEvent] " + "WHERE EventID IN (Select TestFinalEventID From [Course] " + "WHERE CourseId=@CourseId) "; com.CommandText=strSql; com.ExecuteNonQuery(); //Event ID strSql = "Delete From [Event] " + "WHERE EventID IN (Select EventID From [Course] " + "WHERE CourseId=@CourseId) "; com.CommandText=strSql; com.ExecuteNonQuery(); strSql = "Delete From [CalendarEvent] " + "WHERE EventID IN (Select EventID From [Course] " + "WHERE CourseId=@CourseId) "; com.CommandText=strSql; com.ExecuteNonQuery(); strSql = "Delete From [Course] " + "WHERE CourseId=@CourseId "; com.CommandText=strSql; com.ExecuteNonQuery(); return true; } catch(SqlException ex) { Message=ex.Message; return false; } finally { if(com!=null) { com.Dispose(); com=null; con.DisConnect(); } } }
public bool UpdateData() { string strSql=""; SqlCommand com=null; Connection con=null; try { strSql = "Update [Department] Set " + "ContactId=@ContactId, " + "ClientId=@ClientId, " + "DepartmentStatus=@DepartmentStatus, " + "DateLastModified=@DateLastModified, " + "LastModifiedByUserID=@LastModifiedByUserID " + "WHERE DepartmentId=@DepartmentId "; con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.CommandText = strSql; com.Parameters.Add(new SqlParameter("@DepartmentId", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@ContactId", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@ClientId", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@DepartmentStatus", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@DateLastModified", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@LastModifiedByUserID", SqlDbType.Int)); com.Parameters["@DepartmentId"].Value = _deptid; com.Parameters["@ContactId"].Value = _contactid; com.Parameters["@ClientId"].Value = _clientid; com.Parameters["@DepartmentStatus"].Value = _statusid; com.Parameters["@DateLastModified"].Value = DateTime.Now; com.Parameters["@LastModifiedByUserID"].Value = Scheduler.BusinessLayer.Common.LogonID; com.ExecuteNonQuery(); return true; } catch(SqlException ex) { Message=ex.Message; return false; } finally { if(com!=null) { com.Dispose(); com=null; con.DisConnect(); } } }
public string getEventText(int eventid, bool Start,bool getInstructor,ref string instructorName) { string startdate = "", enddate = ""; string Result = ""; string strSql = ""; SqlCommand com = null; Connection con = null; SqlDataReader Reader = null; DateTime dtStart = Convert.ToDateTime(null); DateTime dtEnd = Convert.ToDateTime(null); if (Start) dtStart = Convert.ToDateTime(null); else dtEnd = Convert.ToDateTime(null); try { if (Start) { strSql = "Select Top 1 StartDateTime,ScheduledTeacherID From [CalendarEvent] "; strSql += "WHERE EventID=@EventID Order By CalendarEventID"; } else { strSql = "Select Top 1 EndDateTime From [CalendarEvent] "; strSql += "WHERE EventID=@EventID Order by CalendarEventID DESC"; } con = new Connection(); con.Connect(); com = new SqlCommand(); com.Connection = con.SQLCon; com.CommandText = strSql; com.Parameters.Add(new SqlParameter("@EventID", SqlDbType.BigInt)); com.Parameters["@EventID"].Value = eventid; Reader = com.ExecuteReader(); string id = ""; bool IsRecord = false; if (Reader.Read()) { IsRecord = true; if (Start) { if (Reader["StartDateTime"] != System.DBNull.Value) { dtStart = Convert.ToDateTime(Reader["StartDateTime"].ToString()); } } else { if (Reader["EndDateTime"] != System.DBNull.Value) { dtEnd = Convert.ToDateTime(Reader["EndDateTime"].ToString()); } } if(getInstructor) { if (Reader["ScheduledTeacherID"] != System.DBNull.Value) { id = Convert.ToString(Reader["ScheduledTeacherID"]); //instructorName = getInstructorName(id); } else { instructorName = "None"; } } if (id == "0") instructorName = "None"; } Reader.Close(); if (IsRecord) { if (Start) { if (dtStart != Convert.ToDateTime(null)) { Result = dtStart.ToShortDateString() + " " + dtStart.ToShortTimeString(); if (Result.IndexOf("(") > 0) { Result = Result.Substring(0, Result.IndexOf("(") + 1); } startdate = Result; } } else { if (dtEnd != Convert.ToDateTime(null)) { Result = dtEnd.ToShortDateString() + " " + dtEnd.ToShortTimeString(); if (Result.IndexOf("(") > 0) { Result = Result.Substring(0, Result.IndexOf("(") + 1); } enddate = Result; } } if (id != "None" && id != "0") { instructorName = getInstructorName(id,con); } else instructorName = "None"; } else { Result = "None"; instructorName = "None"; } return Result; } catch (SqlException ex) { Message = ex.Message; return ""; } finally { if (com != null) { com.Dispose(); com = null; con.DisConnect(); } } }
private bool GetContact(int mdeptid, ref string contact1, ref string contact2,ref string contactPhone1,ref string contactPhone2) { string strSql = ""; SqlCommand com = null; Connection con = null; try { strSql = "Select Top 2 LastName + ', ' + FirstName As ContactName,Phone1 From Contact Where ContactType=5 AND RefID=" + mdeptid.ToString() + " Order by ContactID"; con = new Connection(); con.Connect(); com = new SqlCommand(); com.Connection = con.SQLCon; com.CommandText = strSql; SqlDataReader Reader = com.ExecuteReader(); while (Reader.Read()) { if (contact1 == "") { contact1 = Reader["ContactName"].ToString(); contactPhone1 = Reader["Phone1"].ToString(); } else { contact2 = Reader["ContactName"].ToString(); contactPhone2 = Reader["Phone1"].ToString(); } } Reader.Close(); return true; } catch (SqlException ex) { Message = ex.Message; return false; } finally { if (com != null) { com.Dispose(); com = null; con.DisConnect(); } } }
public string getInstructorName(string id,Connection con) { string Result = ""; string strSql = ""; //SqlCommand com = null; //Connection con = null; SqlDataReader Reader = null; SqlCommand com1 = null; //con = new Connection(); //con.Connect(); strSql = "Select LastName,FirstName from Contact Where ContactID = " + id; com1 = new SqlCommand(); com1.Connection = con.SQLCon; com1.CommandText = strSql; try { Reader = com1.ExecuteReader(); if (Reader.Read()) { Result = Reader["LastName"].ToString() + ", " + Reader["FirstName"].ToString(); } Reader.Close(); } catch (Exception ex) { return ex.Message; } finally { if (com1 != null) { com1.Dispose(); //com = null; //con.DisConnect(); } } return Result; }
public static int[] CloneData(int departmentID) { string strSql = ""; SqlCommand com = null; Connection con = null; try { strSql = "usp_DepartmentClone"; con = new Connection(); con.Connect(); com = new SqlCommand(); com.CommandType = CommandType.StoredProcedure; com.Connection = con.SQLCon; com.CommandText = strSql; com.Parameters.Add(new SqlParameter("DepartmentID", SqlDbType.Int)); com.Parameters["DepartmentID"].Value = departmentID; com.Parameters.Add(new SqlParameter("creatorID", SqlDbType.Int)); com.Parameters["creatorID"].Value = Common.LogonID; com.Parameters.Add(new SqlParameter("insertedID", SqlDbType.Int)); com.Parameters["insertedID"].Direction = ParameterDirection.Output; com.Parameters.Add(new SqlParameter("out_newcontactID", SqlDbType.Int)); com.Parameters["out_newcontactID"].Direction = ParameterDirection.Output; com.ExecuteNonQuery(); int[] array = new int[2]; array[0]=(int)com.Parameters["insertedID"].Value; array[1] = (int)com.Parameters["out_newcontactID"].Value; return array; } finally { if (com != null) { com.Dispose(); com = null; con.DisConnect(); } } }
public bool InsertData() { string strSql=""; SqlCommand com=null; Connection con=null; try { strSql = "Insert Into [Course] " + "(" + "Name, " + "NamePhonetic, " + "NameRomaji, " + "NickName, " + "ProgramID, " + "EventID, " + "Description, " + "SpecialRemarks, " + "CourseType, " + "Curriculam, " + "NumberStudents, " + "HomeWorkMinutes, " + "TestInitialEventID, " + "TestMidtermEventID, " + "TestFinalEventID, " + "TestInitialForm, " + "TestMidtermForm, " + "TestFinalForm, " + "CourseStatus, " + "CreatedByUserId, " + "DateCreated, " + "DateLastModified, " + "LastModifiedByUserID, " + "BreakDuration) " + "Values( " + "@Name, " + "@NamePhonetic, " + "@NameRomaji, " + "@NickName, " + "@ProgramID, " + "@EventID, " + "@Description, " + "@SpecialRemarks, " + "@CourseType, " + "@Curriculam, " + "@NumberStudents, " + "@HomeWorkMinutes, " + "@TestInitialEventID, " + "@TestMidtermEventID, " + "@TestFinalEventID, " + "@TestInitialForm, " + "@TestMidtermForm, " + "@TestFinalForm, " + "@CourseStatus, " + "@CreatedByUserId, " + "@DateCreated, " + "@DateLastModified, " + "@LastModifiedByUserID, " + "@BreakDuration " + ")"; strSql += "SELECT @@IDENTITY"; con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.CommandText = strSql; com.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@NamePhonetic", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@NameRomaji", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@NickName", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@ProgramID", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@EventID", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@Description", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@SpecialRemarks", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@CourseType", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@Curriculam", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@NumberStudents", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@HomeWorkMinutes", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@TestInitialEventID", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@TestMidtermEventID", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@TestFinalEventID", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@TestInitialForm", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@TestMidtermForm", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@TestFinalForm", SqlDbType.NVarChar)); com.Parameters.Add(new SqlParameter("@CourseStatus", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@CreatedByUserId", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@DateCreated", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@DateLastModified", SqlDbType.DateTime)); com.Parameters.Add(new SqlParameter("@LastModifiedByUserID", SqlDbType.Int)); com.Parameters.Add(new SqlParameter("@BreakDuration", SqlDbType.Int)); com.Parameters["@Name"].Value = _name; com.Parameters["@NamePhonetic"].Value = _namephonetic; com.Parameters["@NameRomaji"].Value = _nameromaji; com.Parameters["@NickName"].Value = _nickname; com.Parameters["@ProgramID"].Value = _programid; com.Parameters["@EventID"].Value = _eventid; com.Parameters["@Description"].Value = _description; com.Parameters["@SpecialRemarks"].Value = _specialremarks; com.Parameters["@CourseType"].Value = _coursetype; com.Parameters["@Curriculam"].Value = _curriculam; com.Parameters["@NumberStudents"].Value = _numberstudents; com.Parameters["@HomeWorkMinutes"].Value = _homeworkminutes; com.Parameters["@TestInitialEventID"].Value = _testinieventid; com.Parameters["@TestMidtermEventID"].Value = _testmideventid; com.Parameters["@TestFinalEventID"].Value = _testfinaleventid; com.Parameters["@TestInitialForm"].Value = _testiniform; com.Parameters["@TestMidtermForm"].Value = _testmidform; com.Parameters["@TestFinalForm"].Value = _testfinalform; com.Parameters["@CourseStatus"].Value = _coursestatus; com.Parameters["@CreatedByUserId"].Value = Scheduler.BusinessLayer.Common.LogonID; com.Parameters["@DateCreated"].Value = DateTime.Now; com.Parameters["@DateLastModified"].Value = DateTime.Now; com.Parameters["@LastModifiedByUserID"].Value = Scheduler.BusinessLayer.Common.LogonID; com.Parameters["@BreakDuration"].Value = _breakduration; SqlDataReader Reader = com.ExecuteReader(); if(Reader.Read()) { CourseId = Convert.ToInt32(Reader[0].ToString()); } Reader.Close(); return true; } catch(SqlException ex) { Message=ex.Message; return false; } finally { if(com!=null) { com.Dispose(); com=null; con.DisConnect(); } } }
public bool IsEventExists(int evtid) { string strSql=""; SqlCommand com=null; Connection con=null; try { strSql = "Select Count(EventID) From [Event] " + "WHERE EventID=" + evtid.ToString() + " "; con=new Connection(); con.Connect(); com = new SqlCommand(); com.Connection=con.SQLCon; com.CommandText = strSql; object o = com.ExecuteScalar(); if(Convert.ToInt32(o)>0) { return true; } return false; } catch(SqlException ex) { Message=ex.Message; return false; } finally { if(com!=null) { com.Dispose(); com=null; con.DisConnect(); } } }