private void BuildPlanCases() { const string sql = "SELECT casename, countyid, committed, commitdate, commitbasedate, commitdaystill, capp, " + "updatedby, updateddate " + "FROM PlanCase " + "WHERE planid = @planid " + "AND defendantid = @defendantid " + "ORDER BY updateddate; "; using (SqlCommand cmd = new SqlCommand(sql)) { cmd.Parameters.Add("@planid", SqlDbType.Int).Value = ParentId; cmd.Parameters.Add("@defendantid", SqlDbType.Int).Value = _intDefendantId; using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { while (dr.Read()) { PlanCase plancase = PlanCase.CreateCase(dr, ParentId, _intDefendantId); this.Add(plancase); } dr.Close(); } } }
private void BuildPlanFees() { const string sql = "SELECT FeeTypes.feetypeid, PlanFee.amount, " + "PlanFee.updatedby, PlanFee.updateddate " + "FROM PlanFee " + "LEFT OUTER JOIN FeeTypes ON PlanFee.feetypeid = FeeTypes.feetypeid " + "WHERE planid = @planid " + "AND defendantid = @defendantid " + "ORDER BY paymentorder ASC; "; using (SqlCommand cmd = new SqlCommand(sql)) { cmd.Parameters.Add("@planid", SqlDbType.Int).Value = ParentId; cmd.Parameters.Add("@defendantid", SqlDbType.Int).Value = _intDefendantId; using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { while (dr.Read()) { PlanFee planfee = PlanFee.CreateFee(dr, ParentId, _intDefendantId); this.Add(planfee); } dr.Close(); } } }
private void LoadAccountStatusReport() { DataTable dt = new DataTable(); LocalUser user = new LocalUser(); string sql = (user.LenientBilling) ? "Report_AccountStatusLenientBilling" : "Report_AccountStatus"; using (SqlConnection con = DBSettings.NewSqlConnectionClosed) using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@input_DateTime", SqlDbType.DateTime).Value = this.ucCriteriaAccountStatus.InputDate; dt = DBSettings.ExecuteDataAdapter(this.GetType().ToString(), cmd); } dt.TableName = "AccountStatusTable"; _ds = new DataSet(); _ds.Tables.Add(dt); _ds.AcceptChanges(); BindingSource binding = new BindingSource(_ds, "AccountStatusTable"); ReportParameter[] parameters = new ReportParameter[] { new ReportParameter("SnapShotDate", ucCriteriaAccountStatus.InputDate.ToShortDateString()) }; this.reportViewer.LocalReport.ReportEmbeddedResource = "county.feecollections.reports.rptAccountStatus.rdlc"; this.reportViewer.LocalReport.DataSources.Add(new ReportDataSource("DataSet_sp_account_status_data", binding)); this.reportViewer.LocalReport.SetParameters(parameters); }
private void Select() { if (base.ID > 0) { const string sql = "SELECT feetype, paymentorder, billable, updatedby, updateddate " + "FROM FeeTypes " + "WHERE feetypeid = @id; "; using (SqlCommand cmd = new SqlCommand(sql)) { cmd.Parameters.Add("@id", SqlDbType.Int).Value = base.ID; using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { if (dr.Read()) { InitializeProperties(); _strFeeTypeName = dr["feetype"].ToString(); if (!dr.IsDBNull(dr.GetOrdinal("paymentorder"))) { _intPaymentOrder = Convert.ToInt32(dr["paymentorder"].ToString()); } _Billable = Convert.ToBoolean(dr["billable"].ToString()); base.SetNewUpdateProperties(dr["updatedby"].ToString(), Convert.ToDateTime(dr["updateddate"])); } else { dr.Close(); throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectNotExists, new Exception()); } dr.Close(); } } } }
private void BuildFeePayments() { const string sql = "SELECT receiveddate, amount, " + "updatedby, updateddate " + "FROM FeePayment " + "WHERE defendantid = @id " + "AND planid = @planid " + "AND feetypeid = @feetypeid " + "ORDER BY receiveddate DESC; "; using (SqlCommand cmd = new SqlCommand(sql)) { cmd.Parameters.Add("@id", SqlDbType.Int).Value = ParentId; cmd.Parameters.Add("@planid", SqlDbType.Int).Value = _intPlanId; cmd.Parameters.Add("@feetypeid", SqlDbType.Int).Value = _intFeeTypeId; using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { while (dr.Read()) { FeePayment payment = FeePayment.CreateFeePayment(dr, _intPlanId, ParentId, _intFeeTypeId); this.Add(payment); } dr.Close(); } } }
private void LoadJailRoomAndBoard() { DataTable dt = new DataTable(); const string sql = "Report_JailRoomBoard"; using (SqlConnection con = DBSettings.NewSqlConnectionClosed) using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@input_DateTime", SqlDbType.DateTime).Value = this.ucCriteriaAccountStatus.InputDate; dt = DBSettings.ExecuteDataAdapter(this.GetType().ToString(), cmd); } dt.TableName = "JailRoomAndBoardTable"; _ds = new DataSet(); _ds.Tables.Add(dt); _ds.AcceptChanges(); BindingSource binding = new BindingSource(_ds, "JailRoomAndBoardTable"); ReportParameter[] parameters = new ReportParameter[] { new ReportParameter("SnapShotDate", ucCriteriaAccountStatus.InputDate.ToShortDateString()) }; this.reportViewer.LocalReport.ReportEmbeddedResource = "county.feecollections.reports.rptJailReport.rdlc"; this.reportViewer.LocalReport.DataSources.Add(new ReportDataSource("DataSet_sp_prison_room_and_board_data", binding)); this.reportViewer.LocalReport.SetParameters(parameters); }
private void Select() { if (base.ID > 0) { const string sql = "SELECT payperiodtype, updatedby, updateddate " + "FROM PayPeriodTypes " + "WHERE payperiodtypeid = @id; "; using (SqlCommand cmd = new SqlCommand(sql)) { cmd.Parameters.Add("@id", SqlDbType.Int).Value = base.ID; using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { if (dr.Read()) { InitializeProperties(); _strPayPeriodType = dr["payperiodtype"].ToString(); base.SetNewUpdateProperties(dr["updatedby"].ToString(), Convert.ToDateTime(dr["updateddate"])); } else { dr.Close(); throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectNotExists, new Exception()); } dr.Close(); } } } }
private void BuildEmployerDefendantsList() { const string sql = "SELECT defendant.defendantid, defendant.firstname, defendant.middlename, defendant.lastname, " + "defendant.aka, defendant.ssn, defendant.birthdate, defendant.driverslicense, " + "defendant.street1, defendant.street2, defendant.city, defendant.stateid, defendant.zip, " + "defendant.phonehome, defendant.phonemobile, " + "defendant.hasprobationofficer, defendant.probationofficer, " + "defendant.barreduntil, defendant.notes, defendant.active, " + "defendantemployers.updatedby, defendantemployers.updateddate " + "FROM Employer " + "LEFT OUTER JOIN DefendantEmployers ON employer.employerid = defendantemployers.employerid " + "INNER JOIN Defendant ON defendantemployers.defendantid = defendant.defendantid " + "WHERE employer.employerid = @employerid " + "ORDER BY lastname, firstname DESC; "; using (SqlCommand cmd = new SqlCommand(sql)) { cmd.Parameters.Add("@employerid", SqlDbType.Int).Value = ParentId; using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { while (dr.Read()) { Defendant defendant = Defendant.CreateCurrentDefendant(dr); this.Add(defendant); } dr.Close(); } } }
protected void Delete(SqlCommand cmd) { cmd.Parameters.Add("@id", SqlDbType.Int).Value = _intID; cmd.Parameters.Add("@updatedby", SqlDbType.VarChar).Value = _strUpdatedBy; cmd.Parameters.Add("@updateddate", SqlDbType.DateTime).Value = _updatedDate; if (!(DBSettings.ExecuteNonQuery(this.GetType().Name, cmd) > 0)) { throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectUpdate, new Exception()); } }
/// <summary> /// This function executes the cmd paramenter by using ExecuteNonQuery. In addition, /// this fuction sets the following SqlParameters: /// /// @id - base object id /// @updatedby - Current windows username /// @updateddate - the update parameter /// /// throws a MyErrorType.ConCurrentcyObjectUpdate exception if the update /// doesn't return a value > 0. /// </summary> /// <param name="cmd">A SqlCommand object with update statement. (function will update @id, @updatedby, and @updateddate paramenters.)</param> /// <param name="updateDate">The date the @updateddate parameter should be updated to in the database</param> protected void Update(SqlCommand cmd, DateTime updateDate) { cmd.Parameters.Add("@id", SqlDbType.Int).Value = _intID; cmd.Parameters.Add("@updatedby", SqlDbType.VarChar).Value = LocalUser.WindowsUserName; cmd.Parameters.Add("@updateddate", SqlDbType.DateTime).Value = updateDate; if (DBSettings.ExecuteNonQuery(this.GetType().Name, cmd) <= 0) { throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectUpdate, new Exception()); } }
private void Select() { if (base.ID > 0) { const string sql = "SELECT casename, countyid, committed, commitdate, commitdaystill, commitbasedate, capp, updatedby, updateddate " + "FROM PlanCase " + "WHERE planid = @id " + "AND defendantid = @defendantid " + "AND casename = @casename; "; using (SqlCommand cmd = new SqlCommand(sql)) { cmd.Parameters.Add("@id", SqlDbType.Int).Value = base.ID; cmd.Parameters.Add("@defendantid", SqlDbType.Int).Value = _intDefendantId; cmd.Parameters.Add("@casename", SqlDbType.VarChar).Value = _strCaseName_orig; using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { if (dr.Read()) { InitializeProperties(); _strCaseName = dr["casename"].ToString(); if (!dr.IsDBNull(dr.GetOrdinal("countyid"))) { _intCountyId = Convert.ToInt32(dr["countyid"]); } _Committed = Convert.ToBoolean(dr["committed"].ToString()); if (!dr.IsDBNull(dr.GetOrdinal("commitdate"))) { _CommitDate = (DateTime)dr["commitdate"]; } if (!dr.IsDBNull(dr.GetOrdinal("commitdaystill"))) { _dblCommitDaysTill = Convert.ToDouble(dr["commitdaystill"]); } if (!dr.IsDBNull(dr.GetOrdinal("commitbasedate"))) { _CommitBaseDate = (DateTime)dr["commitbasedate"]; } _CAPP = Convert.ToBoolean(dr["capp"].ToString()); base.SetNewUpdateProperties(dr["updatedby"].ToString(), (DateTime)dr["updateddate"]); } else { dr.Close(); throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectNotExists, new Exception()); } dr.Close(); } } } }
private void Select() { if (base.ID > 0) { const string sql = "SELECT payperiodtypeid, paymentarrangementtypeid, amount, startdate, enddate, updatedby, updateddate " + "FROM PlanPaymentArrangement " + "WHERE paymentarrangementid = @id " + "AND planid = @planid " + "AND defendantid = @defendantid; "; using (SqlCommand cmd = new SqlCommand(sql)) { cmd.Parameters.Add("@id", SqlDbType.Int).Value = base.ID; cmd.Parameters.Add("@planid", SqlDbType.Int).Value = _intPlanId; cmd.Parameters.Add("@defendantid", SqlDbType.Int).Value = _intDefendantId; using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { if (dr.Read()) { InitializeProperties(); if (!dr.IsDBNull(dr.GetOrdinal("payperiodtypeid"))) { _intPayPeriodTypeId = Convert.ToInt32(dr["payperiodtypeid"]); } if (!dr.IsDBNull(dr.GetOrdinal("paymentarrangementtypeid"))) { _intPaymentArrangementTypeId = Convert.ToInt32(dr["paymentarrangementtypeid"]); } if (!dr.IsDBNull(dr.GetOrdinal("amount"))) { _dblAmount = Convert.ToDouble(dr["amount"]); } if (!dr.IsDBNull(dr.GetOrdinal("startdate"))) { _StartDate = (DateTime)dr["startdate"]; } if (!dr.IsDBNull(dr.GetOrdinal("enddate"))) { _EndDate = (DateTime)dr["enddate"]; } base.SetNewUpdateProperties(dr["updatedby"].ToString(), (DateTime)dr["updateddate"]); } else { dr.Close(); throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectNotExists, new Exception()); } dr.Close(); } } } }
private void btnDBTest_Click(object sender, EventArgs e) { bool success = DBSettings.TestConnection(txtDBServer.Text.Trim(), txtDBName.Text.Trim(), txtDBUser.Text.Trim(), txtDBPassword.Text.Trim(), chkIntegratedSecurity.Checked); if (success) { MyMessageBox.Show(this, "Database Settings", MyDisplayMessage.TestSuccess); InitializePreferencesTab(); } else { MessageBox.Show("A connection could not be made to the database. Please check your connection information.", "Database Error", MessageBoxButtons.OK); } }
private bool TestConnection() { while (true) { //try //{ if (DBSettings.TestConnection()) { return(true); } else //{ //} //catch (MyException myex) { try { if (DialogResult.No == MessageBox.Show("A connection could not be made to the database. Please check your connection information. Click \"Yes\" to try again or \"No\" to exit.", "Database Error", MessageBoxButtons.YesNo)) { Environment.Exit(0); } frmOptions frm = new frmOptions(); frm.ShowInTaskbar = true; frm.StartPosition = FormStartPosition.CenterScreen; frm.Text = Application.CompanyName + " " + Application.ProductName + " Database Connection"; frm.ShowDialog(); if (frm.IsEmpty()) { return(false); } } catch (ArgumentException) { frmOptions frm = new frmOptions(); frm.ShowInTaskbar = true; frm.StartPosition = FormStartPosition.CenterScreen; frm.Text = Application.CompanyName + " " + Application.ProductName + " Database Connection"; frm.ShowDialog(); if (frm.IsEmpty()) { return(false); } } } } }
private void Select() { if (base.ID > 0) { const string sql = "SELECT employername, street1, street2, city, stateid, zip, phone, " + "separationdate, defendantemployers.updatedby, defendantemployers.updateddate " + "FROM DefendantEmployers JOIN Employer ON defendantemployers.employerid = employer.employerid " + "WHERE defendantid = @defendantid " + "AND employerid = @id; "; using (SqlCommand cmd = new SqlCommand(sql)) { cmd.Parameters.Add("@defendantid", SqlDbType.Int).Value = _intDefendantId; cmd.Parameters.Add("@id", SqlDbType.Int).Value = base.ID; using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { if (dr.Read()) { InitializeProperties(); this.RaiseChangedEvents = false; EmployerName = dr["employername"].ToString(); Street1 = dr["street1"].ToString(); Street2 = dr["street2"].ToString(); City = dr["city"].ToString(); StateID = (dr.IsDBNull(dr.GetOrdinal("stateid"))) ? -1 : Convert.ToInt32(dr["stateid"].ToString()); Zip = dr["zip"].ToString(); Phone = dr["phone"].ToString(); if (!dr.IsDBNull(dr.GetOrdinal("separationdate"))) { _SeparationDate = (DateTime)dr["separationdate"]; } base.SetNewUpdateProperties(dr["updatedby"].ToString(), (DateTime)dr["updateddate"]); this.RaiseChangedEvents = true; } else { dr.Close(); throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectNotExists, new Exception()); } dr.Close(); } } } }
private void Select() { if (base.ID > 0) { const string sql = "SELECT feetypeid, receiveddate, amount, updatedby, updateddate " + "FROM FeePayment " + "WHERE defendantid = @id " + "AND planid = @planid " + "AND feetypeid = @feetypeid " + "AND receiveddate = @receiveddate; "; using (SqlCommand cmd = new SqlCommand(sql)) { cmd.Parameters.Add("@id", SqlDbType.Int).Value = base.ID; cmd.Parameters.Add("@planid", SqlDbType.Int).Value = _intPlanId_orig; cmd.Parameters.Add("@feetypeid", SqlDbType.Int).Value = _intFeeTypeId_orig; cmd.Parameters.Add("@receiveddate", SqlDbType.DateTime).Value = _ReceivedDate_orig; using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { if (dr.Read()) { InitializeProperties(); if (!dr.IsDBNull(dr.GetOrdinal("feetypeid"))) { _intFeeTypeId = Convert.ToInt32(dr["feetypeid"]); } if (!dr.IsDBNull(dr.GetOrdinal("receiveddate"))) { _ReceivedDate = Convert.ToDateTime(dr["receiveddate"]); } if (!dr.IsDBNull(dr.GetOrdinal("amount"))) { _dblAmount = Convert.ToDouble(dr["amount"]); } base.SetNewUpdateProperties(dr["updatedby"].ToString(), (DateTime)dr["updateddate"]); } else { dr.Close(); throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectNotExists, new Exception()); } dr.Close(); } } } }
private void BuildFeeTypeList() { const string sql = "SELECT feetypeid, feetype, paymentorder, billable, updatedby, updateddate " + "FROM FeeTypes " + "ORDER BY paymentorder ASC; "; using (SqlCommand cmd = new SqlCommand(sql)) using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { while (dr.Read()) { FeeType feetype = FeeType.CreateFeeType(dr); this.Add(feetype); } dr.Close(); } }
private void InitializePreferencesTab() { try { DBSettings.TestConnection(); cbbCounties.DataSource = Helper.IowaCountyList; cbbCounties.DisplayMember = "county"; cbbCounties.ValueMember = "countyid"; cbbCounties.SelectedValue = _user.HomeCountyId; cbxLenientBilling.Checked = _user.LenientBilling; } catch { } }
private void BuildUnrelatedEmployerList(string employerIdsCsv) { string sql = string.Format("SELECT employerid, employername, street1, street2, city, stateid, zip, phone, updatedby, updateddate " + "FROM Employer WHERE employerid NOT IN ( {0} ); ", employerIdsCsv); using (SqlCommand cmd = new SqlCommand(sql)) { using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { while (dr.Read()) { Employer employer = Employer.CreateEmployer(dr); this.Add(employer); } dr.Close(); } } }
private void Select() { if (base.ID > 0) { const string sql = "SELECT planname, capp, noncapp, isfiled, hasinsurance, " + "incontempt, noncompliancenotice, fileddate, updatedby, updateddate " + "FROM DefendantPlans " + "WHERE planid = @id AND defendantid = @defendantid; "; using (SqlCommand cmd = new SqlCommand(sql)) { cmd.Parameters.Add("@id", SqlDbType.Int).Value = base.ID; cmd.Parameters.Add("@defendantid", SqlDbType.Int).Value = _intDefendantId; using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { if (dr.Read()) { InitializeProperties(); _strPlanName = dr["planname"].ToString(); _bolCAPP = (dr.IsDBNull(dr.GetOrdinal("capp"))) ? false : Convert.ToBoolean(dr["capp"].ToString()); _bolNonCAPP = (dr.IsDBNull(dr.GetOrdinal("noncapp"))) ? false : Convert.ToBoolean(dr["noncapp"].ToString()); _bolIsFiled = (dr.IsDBNull(dr.GetOrdinal("isfiled"))) ? false : Convert.ToBoolean(dr["isfiled"].ToString()); _bolNonComplianceNotice = (dr.IsDBNull(dr.GetOrdinal("noncompliancenotice"))) ? false : Convert.ToBoolean(dr["noncompliancenotice"].ToString()); _bolHasInsurance = (dr.IsDBNull(dr.GetOrdinal("hasinsurance"))) ? false : Convert.ToBoolean(dr["hasinsurance"].ToString()); _bolInContempt = (dr.IsDBNull(dr.GetOrdinal("incontempt"))) ? false : Convert.ToBoolean(dr["incontempt"].ToString()); if (!dr.IsDBNull(dr.GetOrdinal("fileddate"))) { _FiledDate = dr.GetDateTime(dr.GetOrdinal("fileddate")); } base.SetNewUpdateProperties(dr["updatedby"].ToString(), (DateTime)dr["updateddate"]); } else { dr.Close(); throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectNotExists, new Exception()); } dr.Close(); } } } }
private void BuildRestrictedCasePrefixList() { const string sql = "SELECT prefixid, prefix, updatedby, updateddate " + "FROM RestrictedCasePrefixes " + "ORDER BY prefix DESC; "; using (SqlCommand cmd = new SqlCommand(sql)) { using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { while (dr.Read()) { RestrictedCasePrefix prefix = RestrictedCasePrefix.CreateCasePrefix(dr); this.Add(prefix); } dr.Close(); } } }
private void BuildEmployerList() { const string sql = "SELECT employerid, employername, street1, street2, city, stateid, zip, phone, updatedby, updateddate " + "FROM Employer " + "ORDER BY employername DESC; "; using (SqlCommand cmd = new SqlCommand(sql)) { using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { while (dr.Read()) { Employer employer = Employer.CreateEmployer(dr); this.Add(employer); } dr.Close(); } } }
private void BuildPaymentArrangementTypeList() { const string sql = "SELECT paymentarrangementtypeid, paymentarrangementtype, updatedby, updateddate " + "FROM PaymentArrangementTypes " + "ORDER BY paymentarrangementtype DESC; "; using (SqlCommand cmd = new SqlCommand(sql)) { using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { while (dr.Read()) { PaymentArrangementType paymentarrangementtype = PaymentArrangementType.CreatePaymentArrangementType(dr); this.Add(paymentarrangementtype); } dr.Close(); } } }
private DataSet getData(DateTime date) { DataSet ds = new DataSet(); System.Data.DataTable dt = new System.Data.DataTable(); string sql = "Print_DelinquentNotices"; using (SqlConnection con = DBSettings.NewSqlConnectionClosed) using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@input_DateTime", SqlDbType.DateTime).Value = date; dt = DBSettings.ExecuteDataAdapter(this.GetType().ToString(), cmd); dt.TableName = "AccountStatusTable"; ds.Tables.Add(dt); } return(ds); }
private void BuildDefendantPlansList() { const string sql = "SELECT planid, planname, capp, noncapp, isfiled, hasinsurance, " + "incontempt, noncompliancenotice, fileddate, updatedby, updateddate " + "FROM DefendantPlans " + "WHERE defendantid = @defendantid " + "ORDER BY planname ASC; "; using (SqlCommand cmd = new SqlCommand(sql)) { cmd.Parameters.Add("@defendantid", SqlDbType.Int).Value = ParentId; using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { while (dr.Read()) { Plan plan = Plan.CreateCurrentPlan(dr, ParentId); this.Add(plan); } dr.Close(); } } }
private void LoadBalanceLess100Report() { DataTable dt = new DataTable(); const string sql = "Report_BalanceLess100"; using (SqlConnection con = DBSettings.NewSqlConnectionClosed) using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.CommandType = CommandType.StoredProcedure; dt = DBSettings.ExecuteDataAdapter(this.GetType().ToString(), cmd); } dt.TableName = "CaseTable"; _ds = new DataSet(); _ds.Tables.Add(dt); _ds.AcceptChanges(); BindingSource binding = new BindingSource(_ds, "CaseTable"); this.reportViewer.LocalReport.ReportEmbeddedResource = "county.feecollections.reports.rptBalanceLess100.rdlc"; this.reportViewer.LocalReport.DataSources.Add(new ReportDataSource("DataSet_vw_AccntStatus", binding)); }
private void BuildDefendantList() { const string sql = "SELECT defendantid, firstname, middlename, lastname, aka, ssn, birthdate, driverslicense, " + "street1, street2, city, stateid, zip, phonehome, phonemobile, " + "hasprobationofficer, probationofficer, " + "barreduntil, notes, active, updatedby, updateddate " + "FROM Defendant " + "WHERE active = 1 " + "ORDER BY lastname, firstname; "; using (SqlCommand cmd = new SqlCommand(sql)) { using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { while (dr.Read()) { Defendant defendant = Defendant.CreateCurrentDefendant(dr); this.Add(defendant); } dr.Close(); } } }
private void BuildDefendantEmployersList() { const string sql = "SELECT employer.employerid, employername, street1, street2, city, stateid, zip, phone, " + "separationdate, defendantemployers.updatedby, defendantemployers.updateddate " + "FROM DefendantEmployers JOIN Employer ON defendantemployers.employerid = employer.employerid " + "WHERE defendantid = @defendantid " + "ORDER BY employername DESC; "; using (SqlCommand cmd = new SqlCommand(sql)) { cmd.Parameters.Add("@defendantid", SqlDbType.VarChar).Value = ParentId; using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { while (dr.Read()) { EmployerDefendant defendant = EmployerDefendant.CreateEmployerDefendant(dr, ParentId); this.Add(defendant); } dr.Close(); } } }
private void Select() { if (base.ID > 0) { const string sql = "SELECT employername, street1, street2, city, stateid, zip, phone, updatedby, updateddate " + "FROM Employer " + "WHERE employerid = @id; "; using (SqlCommand cmd = new SqlCommand(sql)) { cmd.Parameters.Add("@id", SqlDbType.Int).Value = base.ID; using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd)) { if (dr.Read()) { InitializeProperties(); _strName = dr["employername"].ToString(); _strStreet1 = dr["street1"].ToString(); _strStreet2 = dr["street2"].ToString(); _strCity = dr["city"].ToString(); _intStateId = (dr.IsDBNull(dr.GetOrdinal("stateid"))) ? -1 : Convert.ToInt32(dr["stateid"].ToString()); _strZip = dr["zip"].ToString(); _strPhone = dr["phone"].ToString(); base.SetNewUpdateProperties(dr["updatedby"].ToString(), (DateTime)dr["updateddate"]); } else { dr.Close(); throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectNotExists, new Exception()); } dr.Close(); } } } }
protected void Insert(SqlCommand cmd, DateTime updateDate) { cmd.CommandText += "SET @ident = SCOPE_IDENTITY(); "; cmd.Parameters.Add("@updatedby", SqlDbType.VarChar).Value = LocalUser.WindowsUserName; cmd.Parameters.Add("@updateddate", SqlDbType.DateTime).Value = updateDate; SqlParameter paramId = new SqlParameter("@ident", SqlDbType.Int); paramId.Direction = ParameterDirection.Output; cmd.Parameters.Add(paramId); if (DBSettings.ExecuteNonQuery(this.GetType().Name, cmd) > 0) { if (!DBNull.Value.Equals(paramId.Value)) { _intID = (int)paramId.Value; } } else { throw new ArgumentNullException(this.GetType().Name + "Id", "The insert failed to return a unique identifier for the " + this.GetType().Name); } }