private void deleteItem()
{
try
{
openConnection();
command = new SqlCommand("Select * From VoucherLog where VoucherItemID=" + ID.ToString(), conn);
reader = command.ExecuteReader();
//int found = command.ExecuteNonQuery();
//found > 0
if (reader.HasRows)
{
reader.Close();
command = new SqlCommand("Update VoucherItems set Inactive = 1 where UID = " + ID.ToString(), conn);
command.ExecuteNonQuery();
}
else
{
reader.Close();
command = new SqlCommand("Delete From VoucherItems where UID = " + ID.ToString(), conn);
command.ExecuteNonQuery();
}
closeConnection();
loadList(0);
}
catch (System.Exception ex)
{
closeConnection();
CCFBGlobal.appendErrorToErrorReport("Command Text = " + command.CommandText, ex.GetBaseException().ToString(),
CCFBGlobal.serverName);
}
}
public void openWhere(string whereClause)
{
try
{
openConnection();
command = new SqlCommand("SELECT * FROM " + tbName + " " + whereClause + " ORDER BY RptGroup, Grouping,[Display Name]", conn);
dadAdpt.SelectCommand = command;
dset.Clear();
iRowCount = dadAdpt.Fill(dset, tbName);
closeConnection();
if (iRowCount > 0)
{
dRow = dset.Tables[tbName].Rows[0];
return;
}
dRow = null;
}
catch (SqlException ex)
{
closeConnection();
iRowCount = 0;
CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString(),
CCFBGlobal.serverName);
}
}
public void openForDate(DateTime TrxDate)
{
try
{
closeConnection();
command = new SqlCommand("SELECT * FROM " + tbName + " WHERE TrxDate='" + TrxDate.ToString() + "'", conn);
dadAdpt.SelectCommand = command;
dset.Clear();
iRowCount = dadAdpt.Fill(dset, tbName);
closeConnection();
if (iRowCount > 0)
{
dRow = dset.Tables[tbName].Rows[0];
}
isValid = false;
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("Command Text = " + command.CommandText, ex.GetBaseException().ToString(),
CCFBGlobal.serverName);
iRowCount = 0;
closeConnection();
isValid = false;
}
}
public int[] getVolList(string sql)
{
int[] volList = new int[1];
try
{
int rows;
openConnection();
volGrpComm = new SqlCommand(sql, conn);
volGrpsDadAdapt.SelectCommand = volGrpComm;
volGrpsDset.Clear();
rows = volGrpsDadAdapt.Fill(volGrpsDset);
volList = new int[rows];
closeConnection();
for (int i = 0; i < rows; i++)
{
volList[i] = volGrpsDset.Tables[0].Rows[i].Field <int>("GroupID");
}
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("volList=" + sql, ex.GetBaseException().ToString());
closeConnection();
}
return(volList);
}
public void update(bool bReload)
{
if (dset.HasChanges() == true)
{
try
{
openConnection();
if (dadAdpt.UpdateCommand == null || dadAdpt.InsertCommand == null)
{
SqlCommandBuilder commBuild = new SqlCommandBuilder(dadAdpt);
}
dadAdpt.Update(dset, tbName);
if (bReload == true)
{
dset.Clear();
iRowCount = dadAdpt.Fill(dset, tbName);
}
closeConnection();
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString());
closeConnection();
}
}
}
public void openWhere(string sWhereClause)
{
try
{
swhereclause = sWhereClause;
openConnection();
command = new SqlCommand("SELECT * FROM " + tbName
+ " " + sWhereClause, conn);
dadAdpt.SelectCommand = command;
dset.Clear();
iRowCount = dadAdpt.Fill(dset, tbName);
closeConnection();
isValid = false;
if (iRowCount > 0)
{
drow = dset.Tables[tbName].Rows[0];
}
}
catch (SqlException ex)
{
closeConnection();
CCFBGlobal.appendErrorToErrorReport(sWhereClause, ex.GetBaseException().ToString());
isValid = false;
iRowCount = 0;
}
}
/// <summary>
/// Gets the ditinct values for any collumn
/// </summary>
/// <param name="columnName">Collumn name</param>
public void getDistincts(string columnName, string whereClause)
{
try
{
openConnection();
sqlCmd = new SqlCommand("SELECT " + columnName + ", COUNT(*) FROM "
+ tblName + whereClause
+ " Group By " + columnName + " Order By " + columnName, conn);
dadAdpt.SelectCommand = sqlCmd;
dset.Clear();
dset.Tables.Clear();
iRowCount = dadAdpt.Fill(dset, tblName);
closeConnection();
isValid = false;
if (iRowCount > 0)
{
drow = dset.Tables[tblName].Rows[0];
}
}
catch (SqlException ex)
{
closeConnection();
CCFBGlobal.appendErrorToErrorReport("columnName=" + columnName, ex.GetBaseException().ToString());
iRowCount = 0;
}
}
public bool open(string toFind, string collumnName)
{
try
{
openConnection();
command = new SqlCommand("SELECT * FROM " + tbName + " WHERE " + collumnName + "='" + toFind + "' "
+ "AND State='" + CCFBPrefs.DefaultState + "'", conn);
dadAdpt.SelectCommand = command;
dset.Clear();
iRowCount = dadAdpt.Fill(dset, tbName);
closeConnection();
if (iRowCount > 0)
{
drow = dset.Tables[tbName].Rows[0];
return(isValid = true);
}
return(isValid = false);
}
catch (SqlException ex)
{
closeConnection();
CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString(),
CCFBGlobal.serverName);
return(isValid = false);
}
}
private void loadDescriptions()
{
try
{
conn.Open();
command = new SqlCommand("Select * From VoucherItems Order By Description ASC", conn);
reader = command.ExecuteReader();
rowIndex = 0;
if (reader.HasRows)
{
while (reader.Read())
{
gridVouchers.Rows.Add();
gridVouchers["clmDesc", rowIndex].Tag = reader.GetValue(0);
gridVouchers["clmDesc", rowIndex].Value = reader.GetValue(1);
gridVouchers["clmAmount", rowIndex].Value = 0;
gridVouchers["clmComments", rowIndex].Value = "";
rowIndex++;
}
}
conn.Close();
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("Command Text= " + command.CommandText,
ex.GetBaseException().ToString(), CCFBGlobal.serverName);
}
}
public bool update()
{
if (dset.HasChanges() == true)
{
try
{
openConnection();
if (dadAdpt.UpdateCommand == null)
{
//Sets the Commands in the DataAdapter
SqlCommandBuilder commBuilder = new SqlCommandBuilder(dadAdpt);
}
drow["ModifiedBy"] = CCFBGlobal.dbUserName;
drow["Modified"] = DateTime.Now;
dadAdpt.Update(dset, "HouseholdMembers");
closeConnection();
return(true);
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString());
return(false);
}
}
return(false);
}
public void openWhere(string sWhereClause = "")
{
try
{
openConnection();
if (sWhereClause == "")
{
command = new SqlCommand("SELECT * FROM " + tbName, conn);
}
else
{
command = new SqlCommand("SELECT * FROM " + tbName + " WHERE " + sWhereClause, conn);
}
dadAdpt.SelectCommand = command;
dset.Clear();
iRowCount = dadAdpt.Fill(dset, tbName);
closeConnection();
if (iRowCount > 0)
{
drow = dset.Tables[tbName].Rows[0];
}
isValid = false;
}
catch (SqlException ex)
{
closeConnection();
CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString(),
CCFBGlobal.serverName);
iRowCount = 0;
isValid = false;
}
}
private void sqlErrorOccured(string commandText, string errorText)
{
closeConnection();
CCFBGlobal.appendErrorToErrorReport(commandText, errorText);
drow = null;
iRowCount = 0;
}
public void openForWeekDay(int weekday)
{
string sql = "SELECT jp.*, vp.Name PrimaryName, vb.Name BackupName "
+ " FROM FBJobsPlan jp "
+ " LEFT JOIN Volunteers vp ON jp.VolIDPrimary = vp.ID "
+ " LEFT JOIN Volunteers vb ON jp.VolIDBackup = vb.ID "
+ " WHERE jp.WeekDay =" + weekday.ToString()
+ " ORDER BY jp.JobTitle, jp.ShiftStart";
try
{
command = new SqlCommand(sql, conn);
if (dadAdpt == null)
{
dadAdpt = new SqlDataAdapter(command);
}
else
{
dadAdpt.SelectCommand = command;
}
dset.Clear();
iRowCount = dadAdpt.Fill(dset, tblName);
if (iRowCount > 0)
{
drow = dset.Tables[tblName].Rows[0];
}
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("sql=" + sql,
ex.GetBaseException().ToString());
closeConnection();
iRowCount = 0;
}
}
public void open(System.Int32 key)
{
try
{
openConnection();
command = new SqlCommand("SELECT * FROM " + tbName + " WHERE TrxID=" + key.ToString(), conn);
dadAdpt.SelectCommand = command;
dset.Clear();
iRowCount = dadAdpt.Fill(dset, tbName);
closeConnection();
if (iRowCount > 0)
{
dRow = dset.Tables[tbName].Rows[0];
}
isValid = false;
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("Command Text = " + command.CommandText, ex.GetBaseException().ToString());
iRowCount = 0;
closeConnection();
isValid = false;
}
}
private void insertIntoLog()
{
try
{
//conn.Open();
//command = new SqlCommand("Insert Into VoucherLog (TrxDate, HouseholdID, Amount, Notes, VoucherItemID,"
// + "Infants, Youth, Teens, Eighteen, Adults, Seniors, TotalFamily, Disabled, Homeless, Transient, Created, CreatedBy"
// + ", FiscalFirstTime, CalFirstTime, MonthFirstTime) "
// + "Values('" + dtpTrxDate.Value.ToShortDateString() + "', " + curHHID.ToString() + ", "
// + amount.ToString() + ", '" + comments + "', " + gridVouchers["clmDesc", rowIndex].Tag.ToString()
// + ", " + tbInfants.Text + ", " + tbYouth.Text + ", " + tbTeens.Text + ", " + "0," + tbAdults.Text + ", "
// + tbSeniors.Text + ", " + tbTotalFam.Text + ", " + tbDisabled.Text
// + ", " + Convert.ToInt32( chkHomeless.Checked).ToString() + "," + cboClientType.SelectedValue
// + ",'" + DateTime.Now.ToString() + "','" + CCFBGlobal.dbUserName + "',0,0,0)"
// , conn);
//command.ExecuteNonQuery();
//conn.Close();
//loadVItmsList();
}
catch (System.Exception ex)
{
CCFBGlobal.appendErrorToErrorReport("Command Text = " + command.CommandText,
ex.GetBaseException().ToString());
conn.Close();
}
}
public void getFavorites()
{
try
{
openConnection();
string sql = "SELECT TOP 21 fd.DonorID,d.Name,sum(Pounds)"
+ " FROM FoodDonations fd INNER JOIN Donors d ON fd.DonorID=d.ID"
+ " WHERE TrxDate > DateAdd(yy,-1,GetDate()) "
+ " GROUP BY fd.DonorID,d.Name "
+ "HAVING sum(pounds) > 500"
+ "ORDER BY Sum(Pounds) DESC";
command = new SqlCommand(sql, conn);
sqldadAdpt = new SqlDataAdapter(command);
//sqldadAdpt.SelectCommand = command;
dset = new DataSet();
iRowCount = sqldadAdpt.Fill(dset, tbName);
if (iRowCount > 0)
{
dRow = dset.Tables[tbName].Rows[0];
}
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("GetFavorites", ex.GetBaseException().ToString());
iRowCount = 0;
}
closeConnection();
}
public void update()
{
if (dset.HasChanges() == true)
{
try
{
openConnection();
if (dadAdpt.UpdateCommand == null)
{
SqlCommandBuilder commands = new SqlCommandBuilder(dadAdpt);
}
drow["Modified"] = DateTime.Now.ToString();
drow["ModifiedBy"] = CCFBGlobal.dbUserName;
dadAdpt.Update(dset, "EmailRecipients");
closeConnection();
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString());
closeConnection();
}
}
}
public bool open(int TrxID)
{
try
{
openConnection();
command = new SqlCommand("SELECT * FROM " + tbName + " WHERE TrxID=" + TrxID.ToString(), conn);
dadAdpt = new SqlDataAdapter(command);
dadAdpt.SelectCommand = command;
dset = new DataSet();
iRowCount = dadAdpt.Fill(dset, tbName);
closeConnection();
if (iRowCount > 0)
{
dRow = dset.Tables[tbName].Rows[0];
return(isValid = true);
}
return(isValid = false);
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("TrxID=" + TrxID.ToString(), ex.GetBaseException().ToString());
iRowCount = 0;
closeConnection();
return(isValid = false);
}
}
/// <summary>
/// Runs the query.
/// </summary>
/// <param name="connectionString"></param>
/// <param name="sqlSelectQuery"></param>
/// <returns>DataTable containing the result</returns>
public virtual DataTable sqlSelectQuery(string connectionString, string sqlQuery)
{
try
{
SqlConnection databaseConnection = new SqlConnection(connectionString);
databaseConnection.Open();
SqlCommand dataCommand = new SqlCommand(sqlQuery, databaseConnection);
DataTable dTable = new DataTable();
SqlDataAdapter dadAdpt = new SqlDataAdapter();
dadAdpt.SelectCommand = dataCommand;
dadAdpt.Fill(dTable);
databaseConnection.Close();
return(dTable);
}
catch (Exception ex)
{
string param = "Con str: " + connectionString + " Sql Query: " + sqlQuery;
CCFBGlobal.appendErrorToErrorReport(param, ex.ToString());
return(new DataTable());
}
}
public void openForDate(int selectfield, DateTime TrxDate)
{
try
{
openConnection();
string queryText = "SELECT fd.*, d.Name DonorName FROM " + tbName + " fd LEFT JOIN Donors d ON fd.DonorID = d.ID"
+ " WHERE Convert(varchar(10), fd." + selectFieldName(selectfield) + ",101)"
+ " = '" + CCFBGlobal.formatDate(TrxDate) + "' ORDER BY TrxDate, DonorName, fd.Created";
command = new SqlCommand(queryText, conn);
sqldadAdpt = new SqlDataAdapter(command);
//sqldadAdpt.SelectCommand = command;
dset = new DataSet();
iRowCount = sqldadAdpt.Fill(dset, tbName);
closeConnection();
if (iRowCount > 0)
{
dRow = dset.Tables[tbName].Rows[0];
}
isValid = false;
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("TrxID=" + TrxDate.ToString(), ex.GetBaseException().ToString());
iRowCount = 0;
closeConnection();
isValid = false;
}
}
public void openWithGroupIdWhere(string whereClause, string grouplist)
{
try
{
if (grouplist == "")
{
sqlCmd = new SqlCommand("SELECT * FROM " + tblName + whereClause + " Order By [Name]", conn);
}
else
{
sqlCmd = new SqlCommand("SELECT * FROM " + tblName + " v " + whereClause
+ " AND EXISTS(SELECT * FROM VolunteerGroups vg WHERE GroupID IN (" + grouplist + ") AND vg.Volid = v.Id)"
+ " Order By [Name]", conn);
}
dadAdpt.SelectCommand = sqlCmd;
dset.Clear();
iRowCount = dadAdpt.Fill(dset, tblName);
if (iRowCount > 0)
{
drow = dset.Tables[tblName].Rows[0];
}
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("whereClause=" + whereClause, ex.GetBaseException().ToString());
closeConnection();
iRowCount = 0;
}
}
public void openDistinctDonationDates(int selectfield)
{
string fldname = selectFieldName(selectfield);
try
{
openConnection();
command = new SqlCommand("SELECT Convert(varchar(10)," + fldname + ",101), Convert(varchar(10)," + fldname + ",111) FROM " + tbName
+ " Group By Convert(varchar(10)," + fldname + ",101), Convert(varchar(10)," + fldname + ",111)"
+ " Order By Convert(varchar(10)," + fldname + ",111) DESC", conn);
sqldadAdpt = new SqlDataAdapter(command);
sqldadAdpt.SelectCommand = command;
dset = new DataSet();
iRowCount = sqldadAdpt.Fill(dset, tbName);
if (iRowCount > 0)
{
dRow = dset.Tables[tbName].Rows[0];
}
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString());
iRowCount = 0;
}
catch (Exception e)
{
iRowCount = 0;
}
closeConnection();
}
public bool openWhere(String WhereClause)
{
try
{
openConnection();
command = new SqlCommand("SELECT * FROM " + tbName);
if (WhereClause != "")
{
command.CommandText += " WHERE " + WhereClause;
}
command.CommandText += " ORDER BY ItemType, ItemDesc";
dadAdpt = new SqlDataAdapter(command);
dadAdpt.SelectCommand = command;
dset.Clear();
dadAdpt.SelectCommand.Connection = conn;
iRowCount = dadAdpt.Fill(dset, tbName);
closeConnection();
if (iRowCount > 0)
{
drow = dset.Tables[tbName].Rows[0];
}
return(false);
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("",
ex.GetBaseException().ToString());
closeConnection();
return(isValid = false);
}
}
public void openDonationForMonth(string sDonorId, string sDonationType, string sBetween)
{
try
{
closeConnection();
command = new SqlCommand("SELECT *, DATEPART(ww,TrxDate) WeekOfYear,DATEPART(dw,TrxDate) DayOfWeek FROM " + tbName
+ " WHERE DonationType = " + sDonationType
+ " AND DonorId = " + sDonorId
+ " AND TrxDate Between " + sBetween
+ " ORDER BY WeekOfYear, DayOfWeek,FoodClass", conn);
sqldadAdpt = new SqlDataAdapter(command);
sqldadAdpt.SelectCommand = command;
dset = new DataSet();
iRowCount = sqldadAdpt.Fill(dset, tbName);
if (iRowCount > 0)
{
dRow = dset.Tables[tbName].Rows[0];
}
closeConnection();
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString());
closeConnection();
iRowCount = 0;
}
}
public bool open(System.Int32 key)
{
try
{
openConnection();
command = new SqlCommand("SELECT * FROM " + tbName + " WHERE ID=" + key.ToString(), conn);
dadAdpt.SelectCommand = command;
dset.Clear();
iRowCount = dadAdpt.Fill(dset, tbName);
closeConnection();
if (iRowCount > 0)
{
dRow = dset.Tables[0].Rows[0];
return(isValid = true);
}
return(isValid = false);
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("key=" + ID.ToString(), ex.GetBaseException().ToString(),
CCFBGlobal.serverName);
closeConnection();
iRowCount = 0;
return(isValid = false);
}
}
public bool update()
{
if (dset.HasChanges() == true)
{
try
{
openConnection();
if (dadAdpt.UpdateCommand == null)
{
SqlCommandBuilder commBuilder = new SqlCommandBuilder(dadAdpt);
}
dadAdpt.Update(dset, tbName);
closeConnection();
return(true);
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString());
return(false);
}
}
return(false);
}
public void openWhere(string whereClause)
{
try
{
openConnection();
command = new SqlCommand("SELECT * FROM " + tbName + whereClause + " Order By TrxDate ASC, DonorID", conn);
dadAdpt.SelectCommand = command;
dset.Clear();
iRowCount = dadAdpt.Fill(dset, tbName);
closeConnection();
if (iRowCount > 0)
{
dRow = dset.Tables[tbName].Rows[0];
}
isValid = false;
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("Command Text = " + command.CommandText, ex.GetBaseException().ToString(),
CCFBGlobal.serverName);
iRowCount = 0;
closeConnection();
isValid = false;
}
}
public bool insertMember()
{
try
{
if (datAdptHhM.InsertCommand == null)
{
SqlCommandBuilder commBuild = new SqlCommandBuilder(datAdptHhM);
datAdptHhM.MissingSchemaAction = MissingSchemaAction.AddWithKey;
}
if (datAdptDemo.InsertCommand == null)
{
SqlCommandBuilder commBldrDemog = new SqlCommandBuilder(datAdptDemo);
datAdptDemo.MissingSchemaAction = MissingSchemaAction.AddWithKey;
}
openConnection();
datAdptHhM.Update(dset, tblHhMName);
datAdptDemo.Update(dset, tblDemoName);
dset.Clear();
datAdptHhM.Fill(dset, tblHhMName);
datAdptDemo.Fill(dset, tblDemoName);
closeConnection();
return(true);
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString(),
CCFBGlobal.serverName);
closeConnection(); return(false);
}
}
public int LoadDateList(int ApptmntStatus)
{
apptStatus = ApptmntStatus;
string StatusList = "";
SqlCommand command;
SqlDataAdapter dataAdpt;
rowCount = 0;
if (apptStatus == 0)
{
StatusList = "0";
}
else
{
StatusList = "1,2";
}
try
{
command = new SqlCommand("SELECT DISTINCT TrxDate, Count(*) FROM TrxLog WHERE TrxStatus IN ("
+ StatusList + ") GROUP BY TrxDate ORDER BY TrxDate");
dataAdpt = new SqlDataAdapter(command);
dataAdpt.SelectCommand.Connection = conn;
dSet.Clear();
rowCount = dataAdpt.Fill(dSet, tbName);
CurRowNumber = 0;
return(rowCount);
}
catch (SqlException ex)
{
CCFBGlobal.appendErrorToErrorReport("",
ex.GetBaseException().ToString(), CCFBGlobal.serverName);
return(rowCount);
}
}
/// <summary>
/// Updates the hosuehold in the database with any changes made to the DataSet
/// </summary>
public void update(bool changeModified)
{
if (dset.HasChanges() == true)
{
try
{
if (drow.RowState != DataRowState.Unchanged && changeModified == true)
{
drow["ModifiedBy"] = CCFBGlobal.dbUserName;
drow["Modified"] = DateTime.Now;
}
if (dadAdpt.UpdateCommand == null)
{
SqlCommandBuilder commBuild = new SqlCommandBuilder(dadAdpt);
}
openConnection();
dadAdpt.Update(dset, "Household");
closeConnection();
}
catch (SqlException ex)
{
closeConnection();
CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString());
}
}
}
