public bool PDFUpdatePdfStatus(SqlConnection sqlConnection, SqlTransaction sqlTransaction,
int PdfId,
int PdfStatusId
)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("PDFUpdatePdfStatus", connection, transaction,
CustomSqlHelper.CreateInputParameter("PdfID", SqlDbType.Int, null, false, PdfId),
CustomSqlHelper.CreateInputParameter("PdfStatusID", SqlDbType.Int, null, false, PdfStatusId),
CustomSqlHelper.CreateOutputParameter("RowsUpdated", SqlDbType.Int, null, false)))
{
command.ExecuteNonQuery();
// If RowsUpdated > 0, then a row was updated (return true)
return((int)command.Parameters[2].Value > 0);
}
}
/// <summary>
/// Select all Items that have expired Page Names.
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="maxAge">Maximum age (in days) of Page Names.</param>
/// <returns>List of objects of type Item.</returns>
/// <remarks>
/// Page Names are considered to be expired if the LastPageNameLookupDate on the
/// Item object is older than the specified number of days.
/// </remarks>
public CustomGenericList <Item> ItemSelectWithExpiredPageNames(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int maxAge)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("ItemSelectWithExpiredPageNames", connection, transaction,
CustomSqlHelper.CreateInputParameter("MaxAge", SqlDbType.Int, null, false, maxAge)))
{
using (CustomSqlHelper <Item> helper = new CustomSqlHelper <Item>())
{
CustomGenericList <Item> list = helper.ExecuteReader(command);
return(list);
}
}
}
public CustomGenericList <Institution> InstitutionSelectWithPublishedItems(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
bool onlyMemberLibraries)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("InstitutionSelectWithPublishedItems", connection, transaction,
CustomSqlHelper.CreateInputParameter("OnlyMemberLibraries", SqlDbType.Bit, null, false, onlyMemberLibraries)))
{
using (CustomSqlHelper <Institution> helper = new CustomSqlHelper <Institution>())
{
CustomGenericList <Institution> list = helper.ExecuteReader(command);
return(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);
}
}
public CustomGenericList <Page> PageMetadataSelectByItemID(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int itemID)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("PageMetadataSelectByItemID", connection, transaction,
CustomSqlHelper.CreateInputParameter("ItemID", SqlDbType.Int, null, false, itemID)))
{
using (CustomSqlHelper <Page> helper = new CustomSqlHelper <Page>())
{
CustomGenericList <Page> list = helper.ExecuteReader(command);
return(list);
}
}
}
/// <summary>
/// Select all Creators starting with a certain letter.
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <returns>Object of type Creator.</returns>
public CustomGenericList <Creator> CreatorSelectByCreatorStartsWith(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
string creatorName)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("CreatorSelectNameStartsWith", connection, transaction,
CustomSqlHelper.CreateInputParameter("CreatorName", SqlDbType.NVarChar, 255, false, creatorName)))
{
using (CustomSqlHelper <Creator> helper = new CustomSqlHelper <Creator>())
{
CustomGenericList <Creator> list = helper.ExecuteReader(command);
return(list);
}
}
}
public CustomGenericList <TitleItem> TitleItemSelectByTitle(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int titleID)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("TitleItemSelectByTitle", connection, transaction,
CustomSqlHelper.CreateInputParameter("TitleID", SqlDbType.Int, null, false, titleID)))
{
using (CustomSqlHelper <TitleItem> helper = new CustomSqlHelper <TitleItem>())
{
CustomGenericList <TitleItem> list = helper.ExecuteReader(command);
return(list);
}
}
}
/// <summary>
/// Select values from PageSummaryView by File Name Prefix.
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="prefixList"></param>
/// <returns>Object of type Title.</returns>
public CustomGenericList <PageSummaryView> PageSummarySelectByPrefixList(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
string prefixList)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("PageSummarySelectByPrefixes", connection, transaction,
CustomSqlHelper.CreateInputParameter("Prefixes", SqlDbType.Text, 1073741823, false, prefixList)))
{
using (CustomSqlHelper <PageSummaryView> helper = new CustomSqlHelper <PageSummaryView>())
{
CustomGenericList <PageSummaryView> list = helper.ExecuteReader(command);
return(list);
}
}
}
public CustomGenericList <PageName> PageNameSelectByNameLike(SqlConnection sqlConnection,
SqlTransaction sqlTransaction, string name, string languageCode, int returnCount)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(
CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("PageNameSelectByNameLike", connection, transaction,
CustomSqlHelper.CreateInputParameter("NameConfirmed", SqlDbType.NVarChar, 100, false, name),
CustomSqlHelper.CreateInputParameter("LanguageCode", SqlDbType.NVarChar, 10, false, languageCode),
CustomSqlHelper.CreateInputParameter("ReturnCount", SqlDbType.Int, null, false, returnCount)))
{
using (CustomSqlHelper <PageName> helper = new CustomSqlHelper <PageName>())
{
return(helper.ExecuteReader(command));
}
}
}
public CustomGenericList <CreatorTitle> TitleSimpleSelectByCreator(SqlConnection sqlConnection,
SqlTransaction sqlTransaction, int creatorId)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(
CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("TitleSimpleSelectByCreator", connection, transaction,
CustomSqlHelper.CreateInputParameter("CreatorId", SqlDbType.Int, null, false, creatorId)))
{
using (CustomSqlHelper <CreatorTitle> helper = new CustomSqlHelper <CreatorTitle>())
{
CustomGenericList <CreatorTitle> list = helper.ExecuteReader(command);
return(list);
}
}
}
public CustomGenericList <MonthlyStats> MonthlyStatsSelectByStatType(SqlConnection sqlConnection, SqlTransaction sqlTransaction,
string statType, string institutionName, bool showMonthly)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("MonthlyStatsSelectByStatType", connection, transaction,
CustomSqlHelper.CreateInputParameter("StatType", SqlDbType.NVarChar, 100, false, statType),
CustomSqlHelper.CreateInputParameter("InstitutionName", SqlDbType.NVarChar, 255, false, institutionName),
CustomSqlHelper.CreateInputParameter("ShowMonthly", SqlDbType.Bit, null, false, showMonthly)))
{
using (CustomSqlHelper <MonthlyStats> helper = new CustomSqlHelper <MonthlyStats>())
{
CustomGenericList <MonthlyStats> list = helper.ExecuteReader(command);
return(list);
}
}
}
public static void Save(SqlConnection sqlConnection, SqlTransaction sqlTransaction, Vault vault)
{
SqlConnection connection = sqlConnection;
SqlTransaction transaction = sqlTransaction;
if (connection == null)
{
connection =
CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"));
}
bool isTransactionCoordinator = CustomSqlHelper.IsTransactionCoordinator(transaction);
try
{
transaction = CustomSqlHelper.BeginTransaction(connection, transaction, isTransactionCoordinator);
if (vault.VaultID == 0)
{
using (SqlCommand command = CustomSqlHelper.CreateCommand("VaultSelectMaxID", connection, transaction))
{
using (CustomSqlHelper <int> helper = new CustomSqlHelper <int>())
{
CustomGenericList <int> list = helper.ExecuteReader(command);
vault.VaultID = list[0] + 1;
}
}
}
new VaultDAL().VaultManageAuto(connection, transaction, vault);
CustomSqlHelper.CommitTransaction(transaction, isTransactionCoordinator);
}
catch (Exception ex)
{
CustomSqlHelper.RollbackTransaction(transaction, isTransactionCoordinator);
throw new Exception("Exception in Save", ex);
}
finally
{
CustomSqlHelper.CloseConnection(connection, isTransactionCoordinator);
}
}
/// <summary>
/// Select all values from Title that are published and are related to the
/// specified institution.
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="institutionCode">ID of the institution for which to retrieve titles</param>
/// <returns>List of objects of type Title.</returns>
public CustomGenericList <Title> TitleSelectPublishedByInstitution(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
String institutionCode)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("TitleSelectPublishedByInstitution", connection, transaction,
CustomSqlHelper.CreateInputParameter("IsPublished", SqlDbType.Bit, 1, false, true),
CustomSqlHelper.CreateInputParameter("InstitutionCode", SqlDbType.NVarChar, 10, false, institutionCode)))
{
using (CustomSqlHelper <Title> helper = new CustomSqlHelper <Title>())
{
CustomGenericList <Title> list = helper.ExecuteReader(command);
return(list);
}
}
}
public CustomGenericList <MarcImportBatch> MarcImportBatchSelectStatsByInstitution(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
String institutionCode)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(
CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("MarcImportBatchSelectStatsByInstitution", connection, transaction,
CustomSqlHelper.CreateInputParameter("InstitutionCode", SqlDbType.NVarChar, 10, false, institutionCode)))
{
using (CustomSqlHelper <MarcImportBatch> helper = new CustomSqlHelper <MarcImportBatch>())
{
CustomGenericList <MarcImportBatch> list = helper.ExecuteReader(command);
return(list);
}
}
}
public CustomGenericList <TitleTag> MarcSelectTitleTagsByMarcID(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int marcId)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(
CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("MarcSelectTitleTagsByMarcID", connection, transaction,
CustomSqlHelper.CreateInputParameter("MarcID", SqlDbType.Int, null, false, marcId)))
{
using (CustomSqlHelper <TitleTag> helper = new CustomSqlHelper <TitleTag>())
{
CustomGenericList <TitleTag> list = helper.ExecuteReader(command);
return(list);
}
}
}
public CustomGenericList <Marc> MarcSelectForImportByBatchID(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int batchID)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(
CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("MarcSelectForImportByBatchID", connection, transaction,
CustomSqlHelper.CreateInputParameter("MarcImportBatchID", SqlDbType.Int, null, false, batchID)))
{
using (CustomSqlHelper <Marc> helper = new CustomSqlHelper <Marc>())
{
CustomGenericList <Marc> list = helper.ExecuteReader(command);
return(list);
}
}
}
/// <summary>
/// Update values in PageName. Returns an object of type PageName.
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="pageNameID"></param>
/// <param name="pageID"></param>
/// <param name="source"></param>
/// <param name="nameFound"></param>
/// <param name="nameConfirmed"></param>
/// <param name="nameBankID"></param>
/// <param name="active"></param>
/// <param name="isCommonName"></param>
/// <returns>Object of type PageName.</returns>
public PageName PageNameUpdateAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int pageNameID,
int pageID,
string source,
string nameFound,
string nameConfirmed,
int?nameBankID,
bool active,
bool?isCommonName)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("PageNameUpdateAuto", connection, transaction,
CustomSqlHelper.CreateInputParameter("PageNameID", SqlDbType.Int, null, false, pageNameID),
CustomSqlHelper.CreateInputParameter("PageID", SqlDbType.Int, null, false, pageID),
CustomSqlHelper.CreateInputParameter("Source", SqlDbType.NVarChar, 50, true, source),
CustomSqlHelper.CreateInputParameter("NameFound", SqlDbType.NVarChar, 100, false, nameFound),
CustomSqlHelper.CreateInputParameter("NameConfirmed", SqlDbType.NVarChar, 100, true, nameConfirmed),
CustomSqlHelper.CreateInputParameter("NameBankID", SqlDbType.Int, null, true, nameBankID),
CustomSqlHelper.CreateInputParameter("Active", SqlDbType.Bit, null, false, active),
CustomSqlHelper.CreateInputParameter("IsCommonName", SqlDbType.Bit, null, true, isCommonName),
CustomSqlHelper.CreateReturnValueParameter("ReturnCode", SqlDbType.Int, null, false)))
{
using (CustomSqlHelper <PageName> helper = new CustomSqlHelper <PageName>())
{
CustomGenericList <PageName> list = helper.ExecuteReader(command);
if (list.Count > 0)
{
PageName o = list[0];
list = null;
return(o);
}
else
{
return(null);
}
}
}
}
public CustomGenericList <PDF> PDFSelectForWeekAndStatus(SqlConnection sqlConnection, SqlTransaction sqlTransaction,
int year,
int week,
int pdfStatusId)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("PDFSelectForWeekAndStatus", connection, transaction,
CustomSqlHelper.CreateInputParameter("Year", SqlDbType.Int, null, false, year),
CustomSqlHelper.CreateInputParameter("Week", SqlDbType.Int, null, false, week),
CustomSqlHelper.CreateInputParameter("PdfStatusID", SqlDbType.Int, null, false, pdfStatusId)))
{
using (CustomSqlHelper <PDF> helper = new CustomSqlHelper <PDF>())
{
CustomGenericList <PDF> list = helper.ExecuteReader(command);
return(list);
}
}
}
public static int TitleSearchCount(SqlConnection sqlConnection, SqlTransaction sqlTransaction,
TitleSearchCriteria tsc)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(
CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("TitleSearchCount", connection, transaction,
CustomSqlHelper.CreateInputParameter("TitleID", SqlDbType.Int, null, true, tsc.TitleID),
CustomSqlHelper.CreateInputParameter("MARCBibID", SqlDbType.NVarChar, 50, true, tsc.MARCBibID),
CustomSqlHelper.CreateInputParameter("Title", SqlDbType.NVarChar, 255, true, tsc.Title)))
{
using (CustomSqlHelper <int> helper = new CustomSqlHelper <int>())
{
CustomGenericList <int> k = helper.ExecuteReader(command);
return(k[0]);
}
}
}
public CustomGenericList <MonthlyStats> MonthlyStatsSelectByDateAndInstitution(SqlConnection sqlConnection, SqlTransaction sqlTransaction,
int startYear, int startMonth, int endYear, int endMonth, String institutionName)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("MonthlyStatsSelectByDateAndInstitution", connection, transaction,
CustomSqlHelper.CreateInputParameter("StartYear", SqlDbType.Int, null, false, startYear),
CustomSqlHelper.CreateInputParameter("StartMonth", SqlDbType.Int, null, false, startMonth),
CustomSqlHelper.CreateInputParameter("EndYear", SqlDbType.Int, null, false, endYear),
CustomSqlHelper.CreateInputParameter("EndMonth", SqlDbType.Int, null, false, endMonth),
CustomSqlHelper.CreateInputParameter("InstitutionName", SqlDbType.NVarChar, 255, false, institutionName)))
{
using (CustomSqlHelper <MonthlyStats> helper = new CustomSqlHelper <MonthlyStats>())
{
CustomGenericList <MonthlyStats> list = helper.ExecuteReader(command);
return(list);
}
}
}
/// <summary>
/// Select all Creators associated with title contributed by the specified institution.
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <returns>Object of type Creator.</returns>
public CustomGenericList <Creator> CreatorSelectByInstitution(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
string institutionCode,
string languageCode)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("CreatorSelectByInstitution", connection, transaction,
CustomSqlHelper.CreateInputParameter("InstitutionCode", SqlDbType.NVarChar, 10, false, institutionCode),
CustomSqlHelper.CreateInputParameter("LanguageCode", SqlDbType.NVarChar, 10, false, languageCode)))
{
using (CustomSqlHelper <Creator> helper = new CustomSqlHelper <Creator>())
{
CustomGenericList <Creator> list = helper.ExecuteReader(command);
return(list);
}
}
}
public static CustomGenericList <PageDetail> PageSelectByNameBankID(SqlConnection sqlConnection, SqlTransaction sqlTransaction,
int nameBankID)
{
SqlConnection connection = sqlConnection;
SqlTransaction transaction = sqlTransaction;
if (connection == null)
{
connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"));
}
using (SqlCommand command = CustomSqlHelper.CreateCommand("PageSelectByNameBankID", connection, transaction,
CustomSqlHelper.CreateInputParameter("NameBankID", SqlDbType.Int, null, false, nameBankID)))
{
using (CustomSqlHelper <PageDetail> helper = new CustomSqlHelper <PageDetail>())
{
return(helper.ExecuteReader(command));
}
}
}
/// <summary>
/// Returns a list of associations that have suspected character encoding problems.
/// </summary>
/// <param name="sqlConnection"></param>
/// <param name="sqlTransaction"></param>
/// <param name="institutionCode">Institution for which to return associations</param>
/// <param name="maxAge">Age in days of associations to consider (i.e. associations new in the last 30 days)</param>
/// <returns></returns>
public CustomGenericList <TitleAssociationSuspectCharacter> TitleAssociationSelectWithSuspectCharacters(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
String institutionCode,
int maxAge)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("TitleAssociationSelectWithSuspectCharacters", connection, transaction,
CustomSqlHelper.CreateInputParameter("InstitutionCode", SqlDbType.NVarChar, 10, false, institutionCode),
CustomSqlHelper.CreateInputParameter("MaxAge", SqlDbType.Int, null, false, maxAge)))
{
using (CustomSqlHelper <TitleAssociationSuspectCharacter> helper = new CustomSqlHelper <TitleAssociationSuspectCharacter>())
{
CustomGenericList <TitleAssociationSuspectCharacter> list = helper.ExecuteReader(command);
return(list);
}
}
}
/// <summary>
/// Update values in Marc. Returns an object of type Marc.
/// </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="marcID"></param>
/// <param name="marcImportStatusID"></param>
/// <param name="marcImportBatchID"></param>
/// <param name="marcFileLocation"></param>
/// <param name="institutionCode"></param>
/// <param name="leader"></param>
/// <param name="titleID"></param>
/// <returns>Object of type Marc.</returns>
public Marc MarcUpdateAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
string connectionKeyName,
int marcID,
int marcImportStatusID,
int marcImportBatchID,
string marcFileLocation,
string institutionCode,
string leader,
int?titleID)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings(connectionKeyName), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("MarcUpdateAuto", connection, transaction,
CustomSqlHelper.CreateInputParameter("MarcID", SqlDbType.Int, null, false, marcID),
CustomSqlHelper.CreateInputParameter("MarcImportStatusID", SqlDbType.Int, null, false, marcImportStatusID),
CustomSqlHelper.CreateInputParameter("MarcImportBatchID", SqlDbType.Int, null, false, marcImportBatchID),
CustomSqlHelper.CreateInputParameter("MarcFileLocation", SqlDbType.NVarChar, 500, false, marcFileLocation),
CustomSqlHelper.CreateInputParameter("InstitutionCode", SqlDbType.NVarChar, 10, true, institutionCode),
CustomSqlHelper.CreateInputParameter("Leader", SqlDbType.NVarChar, 200, false, leader),
CustomSqlHelper.CreateInputParameter("TitleID", SqlDbType.Int, null, true, titleID),
CustomSqlHelper.CreateReturnValueParameter("ReturnCode", SqlDbType.Int, null, false)))
{
using (CustomSqlHelper <Marc> helper = new CustomSqlHelper <Marc>())
{
CustomGenericList <Marc> list = helper.ExecuteReader(command);
if (list.Count > 0)
{
Marc o = list[0];
list = null;
return(o);
}
else
{
return(null);
}
}
}
}
/// <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));
}
}
/// <summary>
/// Insert values into IndicatedPage.
/// </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>
/// <param name="pagePrefix">Prefix portion of Indicated Page.</param>
/// <param name="pageNumber">Page Number portion of Indicated Page.</param>
/// <param name="implied"></param>
/// <param name="creationUserID"></param>
/// <param name="lastModifiedUserID"></param>
/// <returns>Object of type IndicatedPage.</returns>
public IndicatedPage IndicatedPageInsertAuto(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int pageID,
short sequence,
string pagePrefix,
string pageNumber,
bool implied,
int?creationUserID,
int?lastModifiedUserID)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("IndicatedPageInsertAuto", connection, transaction,
CustomSqlHelper.CreateInputParameter("PageID", SqlDbType.Int, null, false, pageID),
CustomSqlHelper.CreateInputParameter("Sequence", SqlDbType.SmallInt, null, false, sequence),
CustomSqlHelper.CreateInputParameter("PagePrefix", SqlDbType.NVarChar, 20, true, pagePrefix),
CustomSqlHelper.CreateInputParameter("PageNumber", SqlDbType.NVarChar, 20, true, pageNumber),
CustomSqlHelper.CreateInputParameter("Implied", SqlDbType.Bit, null, false, implied),
CustomSqlHelper.CreateInputParameter("CreationUserID", SqlDbType.Int, null, true, creationUserID),
CustomSqlHelper.CreateInputParameter("LastModifiedUserID", SqlDbType.Int, null, true, lastModifiedUserID),
CustomSqlHelper.CreateReturnValueParameter("ReturnCode", SqlDbType.Int, null, false)))
{
using (CustomSqlHelper <IndicatedPage> helper = new CustomSqlHelper <IndicatedPage>())
{
CustomGenericList <IndicatedPage> list = helper.ExecuteReader(command);
if (list.Count > 0)
{
IndicatedPage o = list[0];
list = null;
return(o);
}
else
{
return(null);
}
}
}
}
public PageName PageNameSelectByNameBankID(SqlConnection sqlConnection, SqlTransaction sqlTransaction, int nameBankID)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("PageNameSelectByNameBankID", connection, transaction,
CustomSqlHelper.CreateInputParameter("NameBankID", SqlDbType.Int, null, false, nameBankID)))
{
using (CustomSqlHelper <PageName> helper = new CustomSqlHelper <PageName>())
{
CustomGenericList <PageName> list = helper.ExecuteReader(command);
if (list.Count > 0)
{
return(list[0]);
}
else
{
return(null);
}
}
}
}
public static CustomGenericList <Name> PageNameListActive(SqlConnection sqlConnection, SqlTransaction sqlTransaction,
int startRow, int batchSize)
{
SqlConnection connection = sqlConnection;
SqlTransaction transaction = sqlTransaction;
if (connection == null)
{
connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"));
}
using (SqlCommand command = CustomSqlHelper.CreateCommand("PageNameListActive", connection, transaction,
CustomSqlHelper.CreateInputParameter("StartRow", SqlDbType.Int, null, false, startRow),
CustomSqlHelper.CreateInputParameter("BatchSize", SqlDbType.Int, null, false, batchSize)))
{
using (CustomSqlHelper <Name> helper = new CustomSqlHelper <Name>())
{
return(helper.ExecuteReader(command));
}
}
}
public bool Page_PageTypeDeleteAllForPage(SqlConnection sqlConnection, SqlTransaction sqlTransaction, int pageID)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(
CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("Page_PageTypeDeleteAllForPage", connection, transaction,
CustomSqlHelper.CreateInputParameter("PageID", SqlDbType.Int, null, false, pageID),
CustomSqlHelper.CreateReturnValueParameter("ReturnCode", SqlDbType.Int, null, false)))
{
int returnCode = CustomSqlHelper.ExecuteNonQuery(command, "ReturnCode");
if (returnCode == 0)
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// Select recent values from Item.
/// </summary>
/// <param name="sqlConnection">Sql connection or null.</param>
/// <param name="sqlTransaction">Sql transaction or null.</param>
/// <param name="top">Number of values to return</param>
/// <param name="languageCode">Language of items to be included</param>
/// <param name="institutionCode">Contributing institution of items to be included</param>
/// <returns>List of objects of type Item.</returns>
public CustomGenericList <Item> ItemSelectRecent(
SqlConnection sqlConnection,
SqlTransaction sqlTransaction,
int top,
string languageCode,
string institutionCode)
{
SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
SqlTransaction transaction = sqlTransaction;
using (SqlCommand command = CustomSqlHelper.CreateCommand("ItemSelectRecent", connection, transaction,
CustomSqlHelper.CreateInputParameter("Top", SqlDbType.Int, null, false, top),
CustomSqlHelper.CreateInputParameter("LanguageCode", SqlDbType.NVarChar, 10, false, languageCode),
CustomSqlHelper.CreateInputParameter("InstitutionCode", SqlDbType.NVarChar, 10, false, institutionCode)))
{
using (CustomSqlHelper <Item> helper = new CustomSqlHelper <Item>())
{
CustomGenericList <Item> list = helper.ExecuteReader(command);
return(list);
}
}
}