public List <Patron> searchPatrons(PatronSearchSdi patronSearchSdi)
{
return(patronRepoService.searchPatrons(patronSearchSdi));
}
public List <Patron> searchPatrons(PatronSearchSdi patronSearchSdi)
{
con = SqlServerConnection.getConnnection();
con.Open();
List <Patron> patrons = new List <Patron>();
StringBuilder sql = new StringBuilder();
Dictionary <String, Object> param = new Dictionary <String, Object>();
sql.Append("select libraryCardNumber," +
" CONCAT(fName,' ',mName,' ',lName) as name," +
" DOB," +
"phoneNumber," +
"Address from Patron");
sql.Append(" where 1 = 1 ");
if (patronSearchSdi.LibraryCardNumber != null && !"".Equals(patronSearchSdi.LibraryCardNumber))
{
sql.Append(" and libraryCardNumber = @librarycardnumber ");
param.Add("librarycardnumber", patronSearchSdi.LibraryCardNumber);
}
if (patronSearchSdi.Fname != null && !"".Equals(patronSearchSdi.Fname))
{
sql.Append(" and fName = @fname ");
param.Add("fname", patronSearchSdi.Fname);
}
if (patronSearchSdi.Lname != null && !"".Equals(patronSearchSdi.Lname))
{
sql.Append(" and lName = @lname ");
param.Add("lname", patronSearchSdi.Lname);
}
if (patronSearchSdi.Mname != null && !"".Equals(patronSearchSdi.Mname))
{
sql.Append(" and mName = @mname ");
param.Add("mname", patronSearchSdi.Mname);
}
if (patronSearchSdi.Address != null && !"".Equals(patronSearchSdi.Address))
{
sql.Append(" and Address = @address ");
param.Add("address", patronSearchSdi.Address);
}
if (patronSearchSdi.PhoneNumber != null && !"".Equals(patronSearchSdi.PhoneNumber))
{
sql.Append(" and phoneNumber = @phonenumber ");
param.Add("phonenumber", patronSearchSdi.PhoneNumber);
}
if (patronSearchSdi.Username != null && !"".Equals(patronSearchSdi.Username))
{
sql.Append(" and Pusername = @username ");
param.Add("username", patronSearchSdi.Username);
}
SqlCommand command = new SqlCommand(sql.ToString(), con);
foreach (KeyValuePair <String, Object> item in param)
{
command.Parameters.AddWithValue(item.Key, item.Value);
}
SqlDataReader data = command.ExecuteReader();
while (data.Read())
{
Patron patron = new Patron();
patron.LibraryCardNumber = data.GetString(0);
patron.Name = data.GetString(1);
patron.DOB1 = data.GetDateTime(2);
patron.PhoneNumber = data.GetString(3);
patron.Address = data.GetString(4);
patrons.Add(patron);
}
data.Close();
con.Close();
return(patrons);
}
private void search_Click(object sender, EventArgs e)
{
PatronSearchSdi patronSearchSdi = new PatronSearchSdi();
if ("".Equals(lcnTextBox.Text.Trim()))
{
patronSearchSdi.LibraryCardNumber = null;
}
else
{
patronSearchSdi.LibraryCardNumber = lcnTextBox.Text.Trim();
}
if ("".Equals(fnameTextBox.Text.Trim()))
{
patronSearchSdi.Fname = null;
}
else
{
patronSearchSdi.Fname = fnameTextBox.Text.Trim();
}
if ("".Equals(mnameTextBox.Text.Trim()))
{
patronSearchSdi.Mname = null;
}
else
{
patronSearchSdi.Mname = mnameTextBox.Text.Trim();
}
if ("".Equals(lnameTextBox.Text.Trim()))
{
patronSearchSdi.Lname = null;
}
else
{
patronSearchSdi.Lname = lnameTextBox.Text.Trim();
}
if ("".Equals(phoneNumberTextBox.Text.Trim()))
{
patronSearchSdi.PhoneNumber = null;
}
else
{
patronSearchSdi.PhoneNumber = phoneNumberTextBox.Text.Trim();
}
if ("".Equals(usernameTextBox.Text.Trim()))
{
patronSearchSdi.Username = null;
}
else
{
patronSearchSdi.Username = usernameTextBox.Text.Trim();
}
if ("".Equals(addressTextBox.Text.Trim()))
{
patronSearchSdi.Address = null;
}
else
{
patronSearchSdi.Address = addressTextBox.Text.Trim();
}
List <Patron> patrons = patronService.searchPatrons(patronSearchSdi);
PatronSearchResultForm patronSearchResultForm = new PatronSearchResultForm();
patronSearchResultForm.Patrons = patrons;
patronSearchResultForm.ShowDialog();
}
