/// <summary>
/// Select all Creators for a BibId.
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <returns>Object of type Creator.</returns>
private Stats StatsSelect(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
bool expanded,
bool names)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(
CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("StatsSelect", connection, transaction,
CustomSqlHelper.CreateInputParameter("Expanded", SqlDbType.Bit, null, false, expanded),
CustomSqlHelper.CreateInputParameter("Names", SqlDbType.Bit, null, false, names)))
{
CustomGenericList <CustomDataRow> list = CustomSqlHelper.ExecuteReaderAndReturnRows(command);
CustomDataRow row = list[0];
Stats stats = new Stats();
stats.TitleCount = (int)row["TitleCount"].Value;
stats.VolumeCount = (int)row["VolumeCount"].Value;
stats.PageCount = (int)row["PageCount"].Value;
stats.PageTotal = (int)row["PageTotal"].Value;
stats.TitleTotal = (int)row["TitleTotal"].Value;
stats.VolumeTotal = (int)row["VolumeTotal"].Value;
stats.UniqueCount = (int)row["UniqueCount"].Value;
stats.UniqueTotal = (int)row["UniqueTotal"].Value;
return(stats);
}
}
public CustomGenericList <PDFStats> PDFStatsSelectExpanded(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("PDFStatsSelectExpanded", connection, transaction))
{
CustomGenericList <CustomDataRow> list = CustomSqlHelper.ExecuteReaderAndReturnRows(command);
CustomGenericList <PDFStats> listOfStats = new CustomGenericList <PDFStats>();
foreach (CustomDataRow row in list)
{
PDFStats stats = new PDFStats();
stats.Year = (int)row["Year"].Value;
stats.Week = (int)row["Week"].Value;
stats.PdfStatusID = (int)row["PdfStatusID"].Value;
stats.PdfStatusName = row["PdfStatusName"].Value.ToString();
stats.NumberofPdfs = (int)row["NumberOfPDFs"].Value;
stats.PdfsWithOcr = (int)row["PDFsWithOCR"].Value;
stats.PdfsWithArticleMetadata = (int)row["PDFsWithArticleMetadata"].Value;
stats.PdfsWithMissingImages = (int)row["PDFsWithMissingImages"].Value;
stats.PdfsWithMissingOcr = (int)row["PDFsWithMissingOCR"].Value;
stats.TotalMissingImages = (int)row["TotalMissingImages"].Value;
stats.TotalMissingOcr = (int)row["TotalMissingOCR"].Value;
stats.TotalMinutesToGenerate = (int?)row["TotalMinutesToGenerate"].Value;
stats.AveMinutesToGenerate = Convert.ToDouble(row["AvgMinutesToGenerate"].Value);
listOfStats.Add(stats);
}
return(listOfStats);
}
}
public CustomGenericList <OpenUrlCitation> OpenUrlCitationSelectByTitleDetails(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
string title,
string authorLast,
string authorFirst,
string authorCorporation,
string publisherName,
string publisherPlace,
string publisher)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("OpenUrlCitationSelectByTitleDetails", connection, transaction,
CustomSqlHelper.CreateInputParameter("Title", SqlDbType.NVarChar, 2000, true, title),
CustomSqlHelper.CreateInputParameter("AuthorLast", SqlDbType.NVarChar, 150, true, authorLast),
CustomSqlHelper.CreateInputParameter("AuthorFirst", SqlDbType.NVarChar, 100, true, authorFirst),
CustomSqlHelper.CreateInputParameter("AuthorCorporation", SqlDbType.NVarChar, 255, true, authorCorporation),
CustomSqlHelper.CreateInputParameter("PublisherName", SqlDbType.NVarChar, 255, true, publisherName),
CustomSqlHelper.CreateInputParameter("PublisherPlace", SqlDbType.NVarChar, 150, true, publisherPlace),
CustomSqlHelper.CreateInputParameter("Publisher", SqlDbType.NVarChar, 255, true, publisher)))
{
CustomGenericList <CustomDataRow> list = CustomSqlHelper.ExecuteReaderAndReturnRows(command);
return(this.GetOpenUrlCitationList(list));
}
}
public CustomGenericList <CustomDataRow> TitleTagSelectNewLocations(SqlConnection sqlConnection,
SqlTransaction sqlTransaction)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(
CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("TitleTagSelectNewLocations", connection, transaction))
{
return(CustomSqlHelper.ExecuteReaderAndReturnRows(command));
}
}
/// <summary>
/// Select values from Language by primary key(s).
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="languageCode">Code for a language.</param>
/// <returns>CustomGenericList<CustomDataRow></returns>
public CustomGenericList <CustomDataRow> LanguageSelectAutoRaw(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
string languageCode)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("LanguageSelectAuto", connection, transaction,
CustomSqlHelper.CreateInputParameter("LanguageCode", SqlDbType.NVarChar, 10, false, languageCode)))
{
return(CustomSqlHelper.ExecuteReaderAndReturnRows(command));
}
}
/// <summary>
/// Select values from CreatorRoleType by primary key(s).
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="creatorRoleTypeID">Unique identifier for each Creator Role Type.</param>
/// <returns>CustomGenericList<CustomDataRow></returns>
public CustomGenericList <CustomDataRow> CreatorRoleTypeSelectAutoRaw(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int creatorRoleTypeID)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("CreatorRoleTypeSelectAuto", connection, transaction,
CustomSqlHelper.CreateInputParameter("CreatorRoleTypeID", SqlDbType.Int, null, false, creatorRoleTypeID)))
{
return(CustomSqlHelper.ExecuteReaderAndReturnRows(command));
}
}
public CustomGenericList <OpenUrlCitation> OpenUrlCitationSelectByCitationDetails(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int titleID,
string oclc,
string lccn,
string isbn,
string issn,
string abbreviation,
string coden,
string title,
string authorLast,
string authorFirst,
string authorCorporation,
string publisherName,
string publisherPlace,
string publisher,
string volume,
string issue,
string year,
string startPage)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("OpenUrlCitationSelectByCitationDetails", connection, transaction,
CustomSqlHelper.CreateInputParameter("TitleID", SqlDbType.Int, null, true, titleID),
CustomSqlHelper.CreateInputParameter("OCLC", SqlDbType.NVarChar, 125, true, oclc),
CustomSqlHelper.CreateInputParameter("LCCN", SqlDbType.NVarChar, 125, true, lccn),
CustomSqlHelper.CreateInputParameter("ISBN", SqlDbType.NVarChar, 125, true, isbn),
CustomSqlHelper.CreateInputParameter("ISSN", SqlDbType.NVarChar, 125, true, issn),
CustomSqlHelper.CreateInputParameter("Abbreviation", SqlDbType.NVarChar, 125, true, abbreviation),
CustomSqlHelper.CreateInputParameter("CODEN", SqlDbType.NVarChar, 125, true, coden),
CustomSqlHelper.CreateInputParameter("Title", SqlDbType.NVarChar, 2000, true, title),
CustomSqlHelper.CreateInputParameter("AuthorLast", SqlDbType.NVarChar, 150, true, authorLast),
CustomSqlHelper.CreateInputParameter("AuthorFirst", SqlDbType.NVarChar, 100, true, authorFirst),
CustomSqlHelper.CreateInputParameter("AuthorCorporation", SqlDbType.NVarChar, 255, true, authorCorporation),
CustomSqlHelper.CreateInputParameter("PublisherName", SqlDbType.NVarChar, 255, true, publisherName),
CustomSqlHelper.CreateInputParameter("PublisherPlace", SqlDbType.NVarChar, 150, true, publisherPlace),
CustomSqlHelper.CreateInputParameter("Publisher", SqlDbType.NVarChar, 255, true, publisher),
CustomSqlHelper.CreateInputParameter("Volume", SqlDbType.NVarChar, 100, true, volume),
CustomSqlHelper.CreateInputParameter("Issue", SqlDbType.NVarChar, 20, true, issue),
CustomSqlHelper.CreateInputParameter("Year", SqlDbType.NVarChar, 20, true, year),
CustomSqlHelper.CreateInputParameter("StartPage", SqlDbType.NVarChar, 20, true, startPage)))
{
CustomGenericList <CustomDataRow> list = CustomSqlHelper.ExecuteReaderAndReturnRows(command);
return(this.GetOpenUrlCitationList(list));
}
}
public CustomGenericList <OpenUrlCitation> OpenUrlCitationSelectByTitleID(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int titleID)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("OpenUrlCitationSelectByTitleID", connection, transaction,
CustomSqlHelper.CreateInputParameter("TitleID", SqlDbType.Int, null, true, titleID)))
{
CustomGenericList <CustomDataRow> list = CustomSqlHelper.ExecuteReaderAndReturnRows(command);
return(this.GetOpenUrlCitationList(list));
}
}
public CustomGenericList <CustomDataRow> PageNameSearchForTitles(SqlConnection sqlConnection,
SqlTransaction sqlTransaction, string name, string languageCode)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(
CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("PageNameSearchForTitles", connection, transaction,
CustomSqlHelper.CreateInputParameter("NameConfirmed", SqlDbType.NVarChar, 100, false, name),
CustomSqlHelper.CreateInputParameter("LanguageCode", SqlDbType.NVarChar, 10, false, languageCode)))
{
CustomGenericList <CustomDataRow> list = CustomSqlHelper.ExecuteReaderAndReturnRows(command);
return(list);
}
}
/// <summary>
/// Select values from MarcImportBatch by primary key(s).
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="connectionKeyName">Connection key name located in config file.</param>
/// <param name="marcImportBatchID"></param>
/// <returns>CustomGenericList<CustomDataRow></returns>
public CustomGenericList <CustomDataRow> MarcImportBatchSelectAutoRaw(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
string connectionKeyName,
int marcImportBatchID)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings(connectionKeyName), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("MarcImportBatchSelectAuto", connection, transaction,
CustomSqlHelper.CreateInputParameter("MarcImportBatchID", SqlDbType.Int, null, false, marcImportBatchID)))
{
return(CustomSqlHelper.ExecuteReaderAndReturnRows(command));
}
}
/// <summary>
/// Select values from TitleAssociation_TitleIdentifier by primary key(s).
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="connectionKeyName">Connection key name located in config file.</param>
/// <param name="titleAssociation_TitleIdentifierID"></param>
/// <returns>CustomGenericList<CustomDataRow></returns>
public CustomGenericList <CustomDataRow> TitleAssociation_TitleIdentifierSelectAutoRaw(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
string connectionKeyName,
int titleAssociation_TitleIdentifierID)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings(connectionKeyName), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("TitleAssociation_TitleIdentifierSelectAuto", connection, transaction,
CustomSqlHelper.CreateInputParameter("TitleAssociation_TitleIdentifierID", SqlDbType.Int, null, false, titleAssociation_TitleIdentifierID)))
{
return(CustomSqlHelper.ExecuteReaderAndReturnRows(command));
}
}
/// <summary>
/// Select values from TitleTag by primary key(s).
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="titleID"></param>
/// <param name="tagText"></param>
/// <returns>CustomGenericList<CustomDataRow></returns>
public CustomGenericList <CustomDataRow> TitleTagSelectAutoRaw(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int titleID,
string tagText)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("TitleTagSelectAuto", connection, transaction,
CustomSqlHelper.CreateInputParameter("TitleID", SqlDbType.Int, null, false, titleID),
CustomSqlHelper.CreateInputParameter("TagText", SqlDbType.NVarChar, 50, false, tagText)))
{
return(CustomSqlHelper.ExecuteReaderAndReturnRows(command));
}
}
/// <summary>
/// Select values from IndicatedPage by primary key(s).
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="pageID">Unique identifier for each Page record.</param>
/// <param name="sequence">A number to separately identify various series of Indicated Pages.</param>
/// <returns>CustomGenericList<CustomDataRow></returns>
public CustomGenericList <CustomDataRow> IndicatedPageSelectAutoRaw(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int pageID,
short sequence)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("IndicatedPageSelectAuto", connection, transaction,
CustomSqlHelper.CreateInputParameter("PageID", SqlDbType.Int, null, false, pageID),
CustomSqlHelper.CreateInputParameter("Sequence", SqlDbType.SmallInt, null, false, sequence)))
{
return(CustomSqlHelper.ExecuteReaderAndReturnRows(command));
}
}
public CustomGenericList <CustomDataRow> TitleTagSelectCountByInstitution(SqlConnection sqlConnection,
SqlTransaction sqlTransaction, int numberToReturn, string institutionCode, string languageCode)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(
CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("TitleTagSelectCountByInstitution", connection,
transaction,
CustomSqlHelper.CreateInputParameter("Number", SqlDbType.Int, null, false, numberToReturn),
CustomSqlHelper.CreateInputParameter("InstitutionCode", SqlDbType.NVarChar, 10, false, institutionCode),
CustomSqlHelper.CreateInputParameter("LanguageCode", SqlDbType.NVarChar, 10, false, languageCode)))
{
CustomGenericList <CustomDataRow> list = CustomSqlHelper.ExecuteReaderAndReturnRows(command);
return(list);
}
}
public CustomGenericList <OpenUrlCitation> OpenUrlCitationSelectByTitleIdentifier(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
string identifierName,
string identifierValue)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("OpenUrlCitationSelectByTitleIdentifier", connection, transaction,
CustomSqlHelper.CreateInputParameter("IdentifierName", SqlDbType.NVarChar, 40, true, identifierName),
CustomSqlHelper.CreateInputParameter("IdentifierValue", SqlDbType.NVarChar, 125, true, identifierValue)))
{
CustomGenericList <CustomDataRow> list = CustomSqlHelper.ExecuteReaderAndReturnRows(command);
return(this.GetOpenUrlCitationList(list));
}
}
public CustomGenericList <CustomDataRow> PageResolve(SqlConnection sqlConnection, SqlTransaction sqlTransaction,
int titleID, string volume, string issue, string year, string startPage)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(
CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("PageResolve", connection, transaction,
CustomSqlHelper.CreateInputParameter("TitleID", SqlDbType.Int, 4, false, titleID),
CustomSqlHelper.CreateInputParameter("Volume", SqlDbType.VarChar, 20, false, volume),
CustomSqlHelper.CreateInputParameter("Issue", SqlDbType.VarChar, 20, false, issue),
CustomSqlHelper.CreateInputParameter("Year", SqlDbType.VarChar, 20, false, year),
CustomSqlHelper.CreateInputParameter("StartPage", SqlDbType.VarChar, 20, false, startPage)))
{
CustomGenericList <CustomDataRow> list = CustomSqlHelper.ExecuteReaderAndReturnRows(command);
return(list);
}
}
/// <summary>
/// Select values from MonthlyStats by primary key(s).
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="connectionKeyName">Connection key name located in config file.</param>
/// <param name="year"></param>
/// <param name="month"></param>
/// <param name="institutionName"></param>
/// <param name="statType"></param>
/// <returns>CustomGenericList<CustomDataRow></returns>
public CustomGenericList <CustomDataRow> MonthlyStatsSelectAutoRaw(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
string connectionKeyName,
int year,
int month,
string institutionName,
string statType)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings(connectionKeyName), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("MonthlyStatsSelectAuto", connection, transaction,
CustomSqlHelper.CreateInputParameter("Year", SqlDbType.Int, null, false, year),
CustomSqlHelper.CreateInputParameter("Month", SqlDbType.Int, null, false, month),
CustomSqlHelper.CreateInputParameter("InstitutionName", SqlDbType.NVarChar, 255, false, institutionName),
CustomSqlHelper.CreateInputParameter("StatType", SqlDbType.NVarChar, 100, false, statType)))
{
return(CustomSqlHelper.ExecuteReaderAndReturnRows(command));
}
}
public CustomGenericList <CustomDataRow> PageNameSearchByPageType(SqlConnection sqlConnection,
SqlTransaction sqlTransaction, string name, int pageTypeId, string languageCode)
{
if (pageTypeId == 0)
{
return(PageNameSearch(sqlConnection, sqlTransaction, name, 0, languageCode));
}
else
{
SqlConnection connection = CustomSqlHelper.CreateConnection(
CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("PageNameSearchByPageType", connection, transaction,
CustomSqlHelper.CreateInputParameter("NameConfirmed", SqlDbType.NVarChar, 100, false, name),
CustomSqlHelper.CreateInputParameter("PageTypeID", SqlDbType.Int, null, false, pageTypeId),
CustomSqlHelper.CreateInputParameter("LanguageCode", SqlDbType.NVarChar, 10, false, languageCode)))
{
CustomGenericList <CustomDataRow> list = CustomSqlHelper.ExecuteReaderAndReturnRows(command);
return(list);
}
}
}
