C# CustomSqlHelper类代码示例

        public Institution InstitutionSelectByItemID(
            SqlConnection sqlConnection,
            SqlTransaction sqlTransaction,
            int itemID)
        {
            SqlConnection connection = CustomSqlHelper.CreateConnection( CustomSqlHelper.GetConnectionStringFromConnectionStrings( "BHL" ), sqlConnection );
            SqlTransaction transaction = sqlTransaction;

            using ( SqlCommand command = CustomSqlHelper.CreateCommand( "InstitutionSelectByItemID", connection, transaction,
                    CustomSqlHelper.CreateInputParameter( "ItemID", SqlDbType.Int, null, false, itemID ) ) )
            {
                using ( CustomSqlHelper<Institution> helper = new CustomSqlHelper<Institution>() )
                {
                    CustomGenericList<Institution> list = helper.ExecuteReader( command );
                    if ( list.Count > 0 )
                    {
                        Institution o = list[ 0 ];
                        list = null;
                        return o;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
        }

        public static int PageNameCountUniqueConfirmed(SqlConnection sqlConnection, SqlTransaction sqlTransaction)
        {
            SqlConnection connection = sqlConnection;
            SqlTransaction transaction = sqlTransaction;

            if (connection == null)
            {
                connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"));
            }

            using (SqlCommand command = CustomSqlHelper.CreateCommand("PageNameCountUniqueConfirmed", connection, transaction))
            {
                using (CustomSqlHelper<int> helper = new CustomSqlHelper<int>())
                {
                    CustomGenericList<int> list = helper.ExecuteReader(command);

                    if (list.Count == 0)
                    {
                        return default(int);
                    }
                    else
                    {
                        return list[0];
                    }
                }
            }
        }

        public static int PageNameCountUniqueConfirmedBetweenDates(SqlConnection sqlConnection, SqlTransaction sqlTransaction, 
            DateTime startDate, DateTime endDate)
        {
            SqlConnection connection = sqlConnection;
            SqlTransaction transaction = sqlTransaction;

            if (connection == null)
            {
                connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"));
            }

            using (SqlCommand command = CustomSqlHelper.CreateCommand("PageNameCountUniqueConfirmedBetweenDates", connection, transaction,
                CustomSqlHelper.CreateInputParameter("StartDate", SqlDbType.DateTime, null, false, startDate),
                CustomSqlHelper.CreateInputParameter("EndDate", SqlDbType.DateTime, null, false, endDate)))
            {
                using (CustomSqlHelper<int> helper = new CustomSqlHelper<int>())
                {
                    CustomGenericList<int> list = helper.ExecuteReader(command);

                    if (list.Count == 0)
                    {
                        return default(int);
                    }
                    else
                    {
                        return list[0];
                    }
                }
            }
        }

        /// <summary>
        /// Select values from PageTitleSummaryView by MARC Bib Id.
        /// </summary>
        /// <param name="sqlConnection">Sql connection or null.</param>
        /// <param name="sqlTransaction">Sql transaction or null.</param>
        /// <param name="bibID"></param>
        /// <returns>Object of type Title.</returns>
        public PageSummaryView PageTitleSummarySelectByBibId(
            SqlConnection sqlConnection,
            SqlTransaction sqlTransaction,
            string bibID)
        {
            SqlConnection connection = CustomSqlHelper.CreateConnection( CustomSqlHelper.GetConnectionStringFromConnectionStrings( "BHL" ), sqlConnection );
              SqlTransaction transaction = sqlTransaction;

              using ( SqlCommand command = CustomSqlHelper.CreateCommand( "PageTitleSummarySelectByBibId", connection, transaction,
              CustomSqlHelper.CreateInputParameter( "BibID", SqlDbType.VarChar, 50, false, bibID ) ) )
              {
              using (CustomSqlHelper<PageSummaryView> helper = new CustomSqlHelper<PageSummaryView>())
            {
            CustomGenericList<PageSummaryView> list = helper.ExecuteReader(command);
              if ( list.Count > 0 )
              {
            return list[ 0 ];
              }
              else
              {
            return null;
              }
            }
              }
        }

        public static Item ItemSelectByBarCodeOrItemID( SqlConnection sqlConnection, SqlTransaction sqlTransaction,
            int? itemId, string barCode)
        {
            SqlConnection connection = CustomSqlHelper.CreateConnection(
                CustomSqlHelper.GetConnectionStringFromConnectionStrings( "BHL" ), sqlConnection );
            SqlTransaction transaction = sqlTransaction;

            using ( SqlCommand command = CustomSqlHelper.CreateCommand( "ItemSelectByBarCodeOrItemID", connection, transaction,
                CustomSqlHelper.CreateInputParameter( "ItemID", SqlDbType.Int, null, true, itemId ),
                CustomSqlHelper.CreateInputParameter( "BarCode", SqlDbType.NVarChar, 40, true, barCode ) ) )
            {
                using ( CustomSqlHelper<Item> helper = new CustomSqlHelper<Item>() )
                {
                    CustomGenericList<Item> list = helper.ExecuteReader( command );
                    if ( list.Count > 0 )
                    {
                        Item item = (Item)list[ 0 ];
                        item.Pages = new PageDAL().PageSelectByItemID( connection, transaction, item.ItemID );
                        //item.Titles = new TitleDAL().TitleSelectByItem(connection, transaction, item.ItemID);
                        item.TitleItems = new TitleItemDAL().TitleItemSelectByItem(connection, transaction, item.ItemID);
                        item.ItemLanguages = new ItemLanguageDAL().ItemLanguageSelectByItemID(connection, transaction, item.ItemID);
                        return item;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
        }

        public Creator CreatorSelectByCreatorName(
            SqlConnection sqlConnection,
            SqlTransaction sqlTransaction,
            string creatorName)
        {
            SqlConnection connection = CustomSqlHelper.CreateConnection( CustomSqlHelper.GetConnectionStringFromConnectionStrings( "BHL" ), sqlConnection );
              SqlTransaction transaction = sqlTransaction;

              using ( SqlCommand command = CustomSqlHelper.CreateCommand( "CreatorSelectByCreatorName", connection, transaction,
              CustomSqlHelper.CreateInputParameter( "CreatorName", SqlDbType.NVarChar, 255, false, creatorName ) ) )
              {
            using ( CustomSqlHelper<Creator> helper = new CustomSqlHelper<Creator>() )
            {
              CustomGenericList<Creator> list = helper.ExecuteReader( command );
              if ( list.Count > 0 )
              {
            return list[ 0 ];
              }
              else
              {
            return null;
              }
            }
              }
        }

 public static CustomGenericList<ItemStatus> SelectAll( SqlConnection sqlConnection, SqlTransaction sqlTransaction )
 {
     SqlConnection connection = CustomSqlHelper.CreateConnection(
         CustomSqlHelper.GetConnectionStringFromConnectionStrings( "BHL" ), sqlConnection );
     SqlTransaction transaction = sqlTransaction;
     using ( SqlCommand command = CustomSqlHelper.CreateCommand( "ItemStatusSelectAll", connection, transaction ) )
     {
         using ( CustomSqlHelper<ItemStatus> helper = new CustomSqlHelper<ItemStatus>() )
         {
             CustomGenericList<ItemStatus> list = helper.ExecuteReader( command );
             return ( list );
         }
     }
 }

        public CustomGenericList<Location> LocationSelectAllValid(SqlConnection sqlConnection, SqlTransaction sqlTransaction)
        {
            SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
            SqlTransaction transaction = sqlTransaction;

            using (SqlCommand command = CustomSqlHelper.CreateCommand("LocationSelectAllValid", connection, transaction))
            {
                using (CustomSqlHelper<Location> helper = new CustomSqlHelper<Location>())
                {
                    CustomGenericList<Location> list = helper.ExecuteReader(command);
                    return (list);
                }
            }
        }

        public CustomGenericList<MonthlyStats> MonthlyStatsSelectCurrentYearSummary(SqlConnection sqlConnection, SqlTransaction sqlTransaction)
        {
            SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
            SqlTransaction transaction = sqlTransaction;

            using (SqlCommand command = CustomSqlHelper.CreateCommand("MonthlyStatsSelectCurrentYearSummary", connection, transaction))
            {
                using (CustomSqlHelper<MonthlyStats> helper = new CustomSqlHelper<MonthlyStats>())
                {
                    CustomGenericList<MonthlyStats> list = helper.ExecuteReader(command);
                    return (list);
                }
            }
        }

 public CustomGenericList<Language> LanguageSelectWithPublishedItems(
     SqlConnection sqlConnection,
     SqlTransaction sqlTransaction)
 {
     SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
     SqlTransaction transaction = sqlTransaction;
     using (SqlCommand command = CustomSqlHelper.CreateCommand("LanguageSelectWithPublishedItems", connection, transaction))
     {
         using (CustomSqlHelper<Language> helper = new CustomSqlHelper<Language>())
         {
             CustomGenericList<Language> list = helper.ExecuteReader(command);
             return (list);
         }
     }
 }

        public CustomGenericList<PageSummaryView> PDFPageSummaryViewSelectByPdfID(SqlConnection sqlConnection, SqlTransaction sqlTransaction,
            int PdfId)
        {
            SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
            SqlTransaction transaction = sqlTransaction;

            using (SqlCommand command = CustomSqlHelper.CreateCommand("PDFPageSummaryViewSelectByPdfId", connection, transaction,
                CustomSqlHelper.CreateInputParameter("PdfID", SqlDbType.Int, null, false, PdfId)))
            {
                using (CustomSqlHelper<PageSummaryView> helper = new CustomSqlHelper<PageSummaryView>())
                {
                    CustomGenericList<PageSummaryView> list = helper.ExecuteReader(command);
                    return list;
                }
            }
        }

        public static CustomGenericList<NameCloud> PageNameSelectTop(SqlConnection sqlConnection,
            SqlTransaction sqlTransaction, int top)
        {
            SqlConnection connection = CustomSqlHelper.CreateConnection(
                CustomSqlHelper.GetConnectionStringFromConnectionStrings( "BHL" ), sqlConnection );
            SqlTransaction transaction = sqlTransaction;

            using ( SqlCommand command = CustomSqlHelper.CreateCommand( "PageNameSelectTop", connection, transaction,
                CustomSqlHelper.CreateInputParameter( "Number", SqlDbType.Int, null, false, top ) ) )
            {
                using ( CustomSqlHelper<NameCloud> helper = new CustomSqlHelper<NameCloud>() )
                {
                    return helper.ExecuteReader( command );
                }
            }
        }

        public static CustomGenericList<CreatorRoleType> CreatorRoleTypeSelectAll( SqlConnection sqlConnection, 
            SqlTransaction sqlTransaction)
        {
            SqlConnection connection = CustomSqlHelper.CreateConnection(
                CustomSqlHelper.GetConnectionStringFromConnectionStrings( "BHL" ), sqlConnection );
            SqlTransaction transaction = sqlTransaction;

            using ( SqlCommand command = CustomSqlHelper.CreateCommand( "CreatorRoleTypeSelectAll", connection, transaction ) )
            {
                using ( CustomSqlHelper<CreatorRoleType> helper = new CustomSqlHelper<CreatorRoleType>() )
                {
                    CustomGenericList<CreatorRoleType> list = helper.ExecuteReader( command );
                    return list;
                }
            }
        }

        public CustomGenericList<Location> LocationSelectValidByInstitution(SqlConnection sqlConnection, SqlTransaction sqlTransaction, string institutionCode, string languageCode)
        {
            SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
            SqlTransaction transaction = sqlTransaction;

            using (SqlCommand command = CustomSqlHelper.CreateCommand("LocationSelectValidByInstitution", connection, transaction,
                CustomSqlHelper.CreateInputParameter("InstitutionCode", SqlDbType.NVarChar, 10, false, institutionCode),
                CustomSqlHelper.CreateInputParameter("LanguageCode", SqlDbType.NVarChar, 10, false, languageCode)))
            {
                using (CustomSqlHelper<Location> helper = new CustomSqlHelper<Location>())
                {
                    CustomGenericList<Location> list = helper.ExecuteReader(command);
                    return (list);
                }
            }
        }

        public CustomGenericList<ItemLanguage> ItemLanguageSelectByItemID(SqlConnection sqlConnection,
            SqlTransaction sqlTransaction, int itemId)
        {
            SqlConnection connection = CustomSqlHelper.CreateConnection(
                CustomSqlHelper.GetConnectionStringFromConnectionStrings("BHL"), sqlConnection);
            SqlTransaction transaction = sqlTransaction;

            using (SqlCommand command = CustomSqlHelper.CreateCommand("ItemLanguageSelectByItemID", connection, transaction,
                            CustomSqlHelper.CreateInputParameter("ItemID", SqlDbType.Int, null, false, itemId)))
            {
                using (CustomSqlHelper<ItemLanguage> helper = new CustomSqlHelper<ItemLanguage>())
                {
                    CustomGenericList<ItemLanguage> list = helper.ExecuteReader(command);
                    return list;
                }
            }
        }