public bool SaveGrants(ref GrantsDto dto)
{
SQLParamCollection p = new SQLParamCollection();
p.AddWithValue("@ID", dto.ID);
p.AddWithValue("@GrantID", dto.GrantID);
p.AddWithValue("@ProgramName", dto.ProgramName);
p.AddWithValue("@AgencyName", dto.AgencyName);
p.AddWithValue("@ContactInformation", dto.ContactInformation);
p.AddWithValue("@GrantSubject", dto.GrantSubject);
p.AddWithValue("@EligibleActivities", dto.EligibleActivities);
p.AddWithValue("@LastUpdated", dto.LastUpdated);
p.AddWithValue("@CostShareType", dto.CostShareType);
p.AddWithValue("@CostShareDescription", dto.CostShareDescription);
p.AddWithValue("@OtherProgramCharacteristics", dto.OtherProgramCharacteristics);
p.AddWithValue("@ApplicationDueDate", dto.ApplicationDueDate);
p.AddWithValue("@DueDate", dto.DueDate);
p.AddWithValue("@ApplicationSubType", dto.ApplicationSubType);
p.AddWithValue("@FundingType", dto.FundingType);
p.AddWithValue("@Active", dto.Active);
p.AddWithValue("@CreatedByUserID", dto.CreatedByUserID);
p.AddWithValue("@LastUpdatedByUserID", dto.LastUpdatedByUserID);
p.AddWithValue("@IdentityVal", 0, System.Data.SqlDbType.Int, ParameterDirection.Output);
return(SaveCommand("SaveGrants", p));
}
bool UpdateCommand(string queryName, SQLParamCollection Params)
{
bool retVal = false;
SqlParameter s = new SqlParameter();
try
{
Identity = 0;
Cmd = new SqlCommand(queryName, Conn);
foreach (SqlParameter p in Params.ParamCollection)
{
Cmd.Parameters.AddWithValue(p.ParameterName, p.Value);
}
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.ExecuteNonQuery();
retVal = true;
}
catch (Exception ex)
{
ErrMsg = "UpdateCommand: " + Constants.vbCrLf + queryName + Constants.vbCrLf + ex.Message;
l.LogIt(ErrMsg, true, "DB Error", Params);
}
return(retVal);
}
// verify a user can successfully login
public DataSet VerifyLogin(string email, string password)
{
SQLParamCollection p = new SQLParamCollection("@Email", email);
p.AddWithValue("@Password", password);
return(SelectCommand("VerifyLogin", p));
}
public bool UpdateUserActive(int tblUserID, bool Active)
{
SQLParamCollection p = new SQLParamCollection();
p.AddWithValue("@tblUserID", tblUserID);
p.AddWithValue("@Active", Active);
return(UpdateCommand("UpdateUserActive", p));
}
public bool UpdateGrantActive(ref GrantsDto dto)
{
SQLParamCollection p = new SQLParamCollection();
p.AddWithValue("@ID", dto.ID);
p.AddWithValue("@Active", dto.Active);
return(UpdateCommand("UpdateGrantActive", p));
}
public bool UpdateUserVerified(int tblUserID, bool Verified)
{
SQLParamCollection p = new SQLParamCollection();
p.AddWithValue("@tblUserID", tblUserID);
p.AddWithValue("@Verified", Verified);
return(UpdateCommand("UpdateUserVerified", p));
}
public bool SavePassword(ref UserDto dto)
{
SQLParamCollection p = new SQLParamCollection();
p.AddWithValue("@tblUserID", dto.tblUserID);
p.AddWithValue("@Password", dto.Password);
p.AddWithValue("@IdentityVal", 0, SqlDbType.Int, ParameterDirection.Output);
return(SaveCommand("SavePassword", p));
}
public bool SaveGrantRecords(ref SearchGrantsDto dto)
{
SQLParamCollection p = new SQLParamCollection();
p.AddWithValue("@ID", dto.ID);
p.AddWithValue("@tblUserID", dto.tblUserID);
p.AddWithValue("@moreInfoID", dto.moreInfoID);
p.AddWithValue("@IdentityVal", 0, SqlDbType.Int, ParameterDirection.Output);
return(SaveCommand("SaveGrantRecords", p));
}
// selects all grants that pertain to a specific filter, used in main search for search table
public DataSet SelectAllGrantsFilter(int ID, string ProgramName, string GrantSubject, string AgencyName, string LastUpdated, string CostShareType, string FundingType)
{
SQLParamCollection p = new SQLParamCollection("@ID", ID);
p.AddWithValue("@ProgramName", ProgramName);
p.AddWithValue("@GrantSubject", GrantSubject);
p.AddWithValue("@AgencyName", AgencyName);
p.AddWithValue("@LastUpdated", LastUpdated);
p.AddWithValue("@CostShareType", CostShareType);
p.AddWithValue("@FundingType", FundingType);
return(SelectCommand("SelectAllGrantsFilter", p));
}
public bool SaveProfile(ref UserDto dto)
{
SQLParamCollection p = new SQLParamCollection();
p.AddWithValue("@tblUserID", dto.tblUserID);
p.AddWithValue("@Email", dto.Email);
p.AddWithValue("@Alias", dto.Alias);
p.AddWithValue("@FirstName", dto.FirstName);
p.AddWithValue("@LastName", dto.LastName);
p.AddWithValue("@IdentityVal", 0, SqlDbType.Int, ParameterDirection.Output);
return(SaveCommand("SaveProfile", p));
}
private bool SaveCommand(string queryName, SQLParamCollection Params)
{
bool retVal = false;
SqlParameter s = new SqlParameter();
try
{
Identity = 0;
Cmd = new SqlCommand(queryName, Conn);
foreach (SqlParameter p in Params.ParamCollection)
{
if (p.Direction == ParameterDirection.Output)
{
SqlParameter pOut = Cmd.CreateParameter();
pOut.ParameterName = p.ParameterName;
pOut.Direction = ParameterDirection.Output;
pOut.SqlDbType = p.SqlDbType;
Cmd.Parameters.Add(pOut);
}
else
{
if (p.SqlDbType == SqlDbType.NVarChar)
{
Cmd.Parameters.AddWithValue(p.ParameterName, p.Value);
}
else
{
SqlParameter sp = new SqlParameter
{
ParameterName = p.ParameterName,
Value = p.Value,
SqlDbType = p.SqlDbType
};
Cmd.Parameters.Add(sp);
}
}
}
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.ExecuteNonQuery();
retVal = true;
Identity = Int32.Parse(Cmd.Parameters["@IdentityVal"].Value.ToString());
}
catch (Exception ex)
{
ErrMsg = "SaveCommand: " + Constants.vbCrLf + queryName + Constants.vbCrLf + ex.Message;
l.LogIt(ErrMsg, true, "DB Error", Params);
}
return(retVal);
}
public bool SaveSearchRecords(ref SearchRecordsDto dto)
{
SQLParamCollection p = new SQLParamCollection();
p.AddWithValue("@ID", dto.ID);
p.AddWithValue("@tblUserID", dto.tblUserID);
p.AddWithValue("@ProgramName", dto.ProgramName);
p.AddWithValue("@GrantSubject", dto.GrantSubject);
p.AddWithValue("@AgencyName", dto.AgencyName);
p.AddWithValue("@LastUpdated", dto.LastUpdated);
p.AddWithValue("@CostShareType", dto.CostShareType);
p.AddWithValue("@FundingType", dto.FundingType);
p.AddWithValue("@IdentityVal", 0, SqlDbType.Int, ParameterDirection.Output);
return(SaveCommand("SaveSearchRecords", p));
}
public bool SaveUser(ref UserDto dto)
{
SQLParamCollection p = new SQLParamCollection();
p.AddWithValue("@tblUserID", dto.tblUserID);
p.AddWithValue("@Email", dto.Email);
p.AddWithValue("@Password", dto.Password);
p.AddWithValue("@Alias", dto.Alias);
p.AddWithValue("@FirstName", dto.FirstName);
p.AddWithValue("@LastName", dto.LastName);
p.AddWithValue("@luRoleID", dto.luRoleID);
p.AddWithValue("@OrganizationShortName", "MES");
p.AddWithValue("@OrganizationFullName", "Maryland Environmental Service");
p.AddWithValue("@Active", dto.Active);
p.AddWithValue("@CreatedByUserID", dto.CreatedByUserID);
p.AddWithValue("@DateCreated", DateTime.Now);
p.AddWithValue("@LastUpdatedByUserID", dto.LastUpdatedByUserID);
p.AddWithValue("@DateLastUpdated", DateTime.Now);
p.AddWithValue("@Verified", dto.Verified);
p.AddWithValue("@IdentityVal", 0, SqlDbType.Int, ParameterDirection.Output);
return(SaveCommand("SaveUser", p));
}
public DataSet SelectByIDGrants(int ID)
{
SQLParamCollection p = new SQLParamCollection("@ID", ID);
return(SelectCommand("SelectByIDGrants", p));
}
public DataSet SelectByAliasUsers(string Alias)
{
SQLParamCollection p = new SQLParamCollection("@Alias", Alias);
return(SelectCommand("SelectByAliasUsers", p));
}
public DataSet SelectByIDUsers(int tblUserID)
{
SQLParamCollection p = new SQLParamCollection("@tblUserID", tblUserID);
return(SelectCommand("SelectByIDUsers", p));
}
/*This is the main function that is used to retreive data from a SQL select stored procedure
* QueryName is the name of SQL stored procedure, Params is the SQLParamCollection generated from
* the "child function"*/
private DataSet SelectCommand(string SPName, SQLParamCollection Params = null /* TODO Change to default(_) if this is not a reference type */)
{
DataSet ds = new DataSet();
string ErrMsg = ""; //used in case there is an error message
OutputIdentities.Clear();
try
{
/*Set up a new SQL command
* Call the stored procedure to the server on the connection string, indicated it is
* a stored procedure*/
Identity = 0;
Cmd = new SqlCommand(SPName, Conn)
{
CommandType = CommandType.StoredProcedure
};
//Set the sql data adapter to a new version
da = new SqlDataAdapter(Cmd);
/*If the SQL Param Collection isn't empty then add the parameter to the data
* adapator select command that will be performed*/
if ((Params != null))
{
foreach (SqlParameter p in Params.ParamCollection)
{
// da.SelectCommand.Parameters.AddWithValue(p.ParameterName, p.Value)
SqlParameter pOut = Cmd.CreateParameter();
if (p.Direction == ParameterDirection.Output)
{
pOut.ParameterName = p.ParameterName;
pOut.Direction = ParameterDirection.Output;
pOut.SqlDbType = p.SqlDbType;
da.SelectCommand.Parameters.Add(pOut);
}
else if (p.SqlDbType == SqlDbType.NVarChar)
{
da.SelectCommand.Parameters.AddWithValue(p.ParameterName, p.Value);
}
else
{
SqlParameter sp = new SqlParameter
{
ParameterName = p.ParameterName,
Value = p.Value,
SqlDbType = p.SqlDbType
};
da.SelectCommand.Parameters.Add(sp);
}
}
}
RowCount = da.Fill(ds);
//This is the goal of this function, returning the dataset that will be used to present data
return(ds);
}
//Try to return a slightly more detailed message if there is an error
catch (Exception ex)
{
ErrMsg = "SelectCommand: " + Constants.vbCrLf + SPName + Constants.vbCrLf + ex.Message;
l.LogIt(ErrMsg, true, "DB Error", Params);
}
return(ds);
}
public DataSet SelectByEmailUsers(string Email)
{
SQLParamCollection p = new SQLParamCollection("@Email", Email);
return(SelectCommand("SelectByEmailUsers", p));
}