//This method is called after the CheckExisting() method and inserts the verse into the database.
public void InsertVerse()
{
try
{
BenchLogger.getInstance().Info("Invoked InsertVerse method in the verse DAO");
query = "INSERT INTO dbo.Verses (TESTAMENT, BOOK, CHAPTER, VERSE, TEXT) VALUES (@test," +
" @book, @chp, @ver, @txt)";
cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@test", SqlDbType.NVarChar, 50).Value = testament;
cmd.Parameters.Add("@book", SqlDbType.NVarChar, 50).Value = book;
cmd.Parameters.Add("@chp", SqlDbType.Int).Value = chapter;
cmd.Parameters.Add("@ver", SqlDbType.Int).Value = verse;
cmd.Parameters.Add("@txt", SqlDbType.NVarChar).Value = text;
conn.Open();
cmd.ExecuteReader();
BenchLogger.getInstance().Info("Leaving InserVerse method in verse DAO");
conn.Close();
}
catch (SqlException e)
{
BenchLogger.getInstance().Error("Sql Exception occured in InsertVerse DAO method: " + e.ToString());
throw e;
}
}
//This method calls the insert method from the DAO and returns true or false.
public bool InsertVerse()
{
BenchLogger.getInstance().Info("Invoked InsertVerse method in the verse service");
if (vd.CheckExisting())
{
vd.InsertVerse();
BenchLogger.getInstance().Info("Leaving InsertVerse method in the verse service. User's verse added.");
return(true);
}
else
{
BenchLogger.getInstance().Error("Leaving InsertVerse method in the verse service. Verse existent.");
return(false);
}
}
//This method searches the database for the verse the user is trying to find and then returns it as
//a list of strings.
public List <string> SearchVerse()
{
try
{
BenchLogger.getInstance().Info("Invoked SearchVerse method in the verse DAO");
query = "SELECT * FROM dbo.Verses WHERE TESTAMENT=@test AND BOOK=@book AND CHAPTER=@chp" +
" AND VERSE=@ver";
cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@test", SqlDbType.NVarChar, 50).Value = testament;
cmd.Parameters.Add("@book", SqlDbType.NVarChar, 50).Value = book;
cmd.Parameters.Add("@chp", SqlDbType.Int).Value = chapter;
cmd.Parameters.Add("@ver", SqlDbType.Int).Value = verse;
conn.Open();
dr = cmd.ExecuteReader();
List <string> list = new List <string>();
if (!dr.HasRows)
{
list.Add("No results found!");
BenchLogger.getInstance().Info("Leaving SearchVerse method with no results");
return(list);
}
//Reads the results from the sql command and then adds the results to the list
while (dr.Read())
{
list.Add(dr["TESTAMENT"].ToString());
list.Add(dr["BOOK"].ToString());
list.Add(dr["CHAPTER"].ToString());
list.Add(dr["VERSE"].ToString());
list.Add(dr["TEXT"].ToString());
}
BenchLogger.getInstance().Info("Leaving SearchVerse method with results");
conn.Close();
return(list);
}
catch (SqlException e)
{
BenchLogger.getInstance().Error("Sql Exception occured in SearchVerse method: " + e.ToString());
throw e;
}
}
//This method calls the VerseDAO search method and returns the list retrieved.
public List <string> SearchVerses()
{
BenchLogger.getInstance().Info("User invoked SearchVerses method in the VerseService class");
List <string> verse = vd.SearchVerse();
if (verse[0] != "No results found!")
{
BenchLogger.getInstance().Info("User search returned the verse: " + verse[4] + ". (Note this will fail" +
" if the user's search did not yield results).");
}
else
{
BenchLogger.getInstance().Info("User's search yielded 0 results");
}
BenchLogger.getInstance().Info("Leaving SearchVerses method in service");
return(verse);
}
//This method checks to see if the verse the user is trying to add has already been added to the
//database.
public bool CheckExisting()
{
try
{
BenchLogger.getInstance().Info("Invoked CheckExisting in the verse DAO");
//There's 3 wheres here because there are multiple duplicate verses in the bible,
//some in the same book, some in the same verse#, but none in the same book and verse #
query = "SELECT * FROM dbo.Verses WHERE BOOK=@book AND VERSE=@verse AND TEXT=@text";
cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@book", SqlDbType.NVarChar, 50).Value = book;
cmd.Parameters.Add("@verse", SqlDbType.Int).Value = verse;
cmd.Parameters.Add("@text", SqlDbType.NVarChar).Value = text;
conn.Open();
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
conn.Close();
BenchLogger.getInstance().Info("Leaving CheckExisting Method. Verse existed");
return(false);
}
else
{
conn.Close();
BenchLogger.getInstance().Info("Leaving CheckExisting Method. Verse did not exist in database");
return(true);
}
}
catch (SqlException e)
{
BenchLogger.getInstance().Error("Sql Exception occured in CheckExisting DAO method: " + e.ToString());
throw e;
}
}
