录播教室预约系统(3)-DepTable表[普通表]

/****** Object:  Stored Procedure [dbo].DepTable_Delete    Script Date: 2015年2月8日 ******/if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[DepTable_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[DepTable_Delete]GO/****** Object:  Stored Procedure [dbo].DepTable_SelectOne    Script Date: 2015年2月8日 ******/if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[DepTable_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[DepTable_SelectOne]GO /****** Object:  Stored Procedure [dbo].DepTable_GetCount    Script Date: 2015年2月8日 ******/if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[DepTable_GetCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[DepTable_GetCount]GO /****** Object:  Stored Procedure [dbo].DepTable_SelectAll    Script Date: 2015年2月8日 ******/if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[DepTable_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[DepTable_SelectAll]GO/****** Object:  Stored Procedure [dbo].DepTable_Insert    Script Date: 2015年2月8日 ******/if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[DepTable_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[DepTable_Insert]GO/****** Object:  Stored Procedure [dbo].DepTable_Update    Script Date: 2015年2月8日 ******/if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[DepTable_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[DepTable_Update]GOSET ANSI_NULLS OFF GOSET QUOTED_IDENTIFIER ON GOCREATE PROCEDURE [dbo].DepTable_Delete/*Author:               msdcCreated:             2015-2-8Last Modified:         2015-2-8*/@Id intASDELETE FROM [dbo].[DepTable]WHERE    [Id] = @IdGOSET ANSI_NULLS OFF GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GOSET QUOTED_IDENTIFIER ON GOCREATE PROCEDURE [dbo].DepTable_GetCount/*Author:               msdcCreated:             2015-2-8Last Modified:         2015-2-8*/ASSELECT COUNT(*) FROM [dbo].[DepTable]GOSET ANSI_NULLS OFF GOSET QUOTED_IDENTIFIER OFF GO SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GOCREATE PROCEDURE [dbo].DepTable_SelectOne/*Author:               msdcCreated:             2015-2-8Last Modified:         2015-2-8*/@Id intASSELECT        [Id],        [Department]        FROM        [dbo].[DepTable]        WHERE        [Id] = @IdGOSET ANSI_NULLS OFF GOSET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GOSET QUOTED_IDENTIFIER ON GOCREATE PROCEDURE [dbo].DepTable_SelectAll/*Author:               msdcCreated:             2015-2-8Last Modified:         2015-2-8*/ASSELECT        [Id],        [Department]        FROM        [dbo].[DepTable]GOSET ANSI_NULLS OFF GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GOSET QUOTED_IDENTIFIER ON GOCREATE PROCEDURE [dbo].DepTable_Insert/*Author:               msdcCreated:             2015-2-8Last Modified:         2015-2-8*/@Department nvarchar(200)    ASINSERT INTO     [dbo].[DepTable] (                [Department]) VALUES (                @Department                )SELECT @@IDENTITY GOSET ANSI_NULLS OFF GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GOSET QUOTED_IDENTIFIER ON GOCREATE PROCEDURE [dbo].DepTable_Update/*Author:               msdcCreated:             2015-2-8Last Modified:         2015-2-8*/    @Id int, @Department nvarchar(200) ASUPDATE         [dbo].[DepTable] SET            [Department] = @Department            WHERE            [Id] = @IdGOSET ANSI_NULLS OFF GOSET QUOTED_IDENTIFIER OFF GO/****** Object:  Stored Procedure [dbo].DepTable_SelectPage   Script Date: 2015年2月8日 ******/if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[DepTable_SelectPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[DepTable_SelectPage]GOCREATE PROCEDURE [dbo].DepTable_SelectPage-- Author:               msdc-- Created:             2015-2-8-- Last Modified:         2015-2-8@PageNumber             int,@PageSize             intASDECLARE @PageLowerBound intDECLARE @PageUpperBound intSET @PageLowerBound = (@PageSize * @PageNumber) - @PageSizeSET @PageUpperBound = @PageLowerBound + @PageSize + 1/*Note: temp tables use the server default for collation not the database defaultso if adding character columns be sure and specify to use the database collation like thisto avoid collation errors:CREATE TABLE #PageIndexForUsers(IndexID int IDENTITY (1, 1) NOT NULL,UserName nvarchar(50) COLLATE DATABASE_DEFAULT,LoginName nvarchar(50) COLLATE DATABASE_DEFAULT) */CREATE TABLE #PageIndex (    IndexID int IDENTITY (1, 1) NOT NULL,Id Int)BEGININSERT INTO #PageIndex ( Id)SELECT        [Id]        FROM        [dbo].[DepTable]        -- WHERE-- ORDER BYENDSELECT        t1.*        FROM        [dbo].[DepTable] t1JOIN            #PageIndex t2ON                    t1.[Id] = t2.[Id]        WHERE        t2.IndexID > @PageLowerBound         AND t2.IndexID < @PageUpperBound        ORDER BY t2.IndexIDDROP TABLE #PageIndexGOSET ANSI_NULLS OFF GOSET QUOTED_IDENTIFIER OFF GO

// Author:                    msdc// Created:                    2015-2-8// Last Modified:            2015-2-8 using System;using System.IO;using System.Text;using System.Data;using System.Data.Common;using System.Data.SqlClient;using System.Configuration;using mojoPortal.Data;    namespace mojoPortal.Data{        public static class DBDepTable    {        /// <summary>        /// Gets the connection string for read.        /// </summary>        /// <returns></returns>        private static string GetReadConnectionString()        {            return ConfigurationManager.AppSettings["MSSQLConnectionString"];        }        /// <summary>        /// Gets the connection string for write.        /// </summary>        /// <returns></returns>        private static string GetWriteConnectionString()        {            if (ConfigurationManager.AppSettings["MSSQLWriteConnectionString"] != null)            {                return ConfigurationManager.AppSettings["MSSQLWriteConnectionString"];            }            return ConfigurationManager.AppSettings["MSSQLConnectionString"];        }        /// <summary>        /// Inserts a row in the DepTable table. Returns new integer id.        /// </summary>        /// <param name="department"> department </param>        /// <returns>int</returns>        public static int Create(            string department)         {            SqlParameterHelper sph = new SqlParameterHelper(GetWriteConnectionString(), "DepTable_Insert", 1);            sph.DefineSqlParameter("@Department", SqlDbType.NVarChar, 200, ParameterDirection.Input, department);            int newID = Convert.ToInt32(sph.ExecuteScalar());            return newID;        }        /// <summary>        /// Updates a row in the DepTable table. Returns true if row updated.        /// </summary>        /// <param name="id"> id </param>        /// <param name="department"> department </param>        /// <returns>bool</returns>        public static bool Update(            int  id,             string department)         {            SqlParameterHelper sph = new SqlParameterHelper(GetWriteConnectionString(), "DepTable_Update", 2);            sph.DefineSqlParameter("@Id", SqlDbType.Int, ParameterDirection.Input, id);            sph.DefineSqlParameter("@Department", SqlDbType.NVarChar, 200, ParameterDirection.Input, department);            int rowsAffected = sph.ExecuteNonQuery();            return (rowsAffected > 0);                    }                /// <summary>        /// Deletes a row from the DepTable table. Returns true if row deleted.        /// </summary>        /// <param name="id"> id </param>        /// <returns>bool</returns>        public static bool Delete(            int id)         {            SqlParameterHelper sph = new SqlParameterHelper(GetWriteConnectionString(), "DepTable_Delete", 1);            sph.DefineSqlParameter("@Id", SqlDbType.Int, ParameterDirection.Input, id);            int rowsAffected = sph.ExecuteNonQuery();            return (rowsAffected > 0);                    }                         /// <summary>        /// Gets an IDataReader with one row from the DepTable table.        /// </summary>        /// <param name="id"> id </param>        public static IDataReader GetOne(            int  id)          {            SqlParameterHelper sph = new SqlParameterHelper(GetReadConnectionString(), "DepTable_SelectOne", 1);            sph.DefineSqlParameter("@Id", SqlDbType.Int, ParameterDirection.Input, id);            return sph.ExecuteReader();                    }                                     /// <summary>        /// Gets a count of rows in the DepTable table.        /// </summary>        public static int GetCount()        {                        return Convert.ToInt32(SqlHelper.ExecuteScalar(                GetReadConnectionString(),                CommandType.StoredProcedure,                "DepTable_GetCount",                null));            }                                     /// <summary>        /// Gets an IDataReader with all rows in the DepTable table.        /// </summary>        public static IDataReader GetAll()        {                        return SqlHelper.ExecuteReader(                GetReadConnectionString(),                CommandType.StoredProcedure,                "DepTable_SelectAll",                null);            }                /// <summary>        /// Gets a page of data from the DepTable table.        /// </summary>        /// <param name="pageNumber">The page number.</param>        /// <param name="pageSize">Size of the page.</param>        /// <param name="totalPages">total pages</param>        public static IDataReader GetPage(            int pageNumber,             int pageSize,            out int itemCount)        {            itemCount = GetCount();                                    SqlParameterHelper sph = new SqlParameterHelper(GetReadConnectionString(), "DepTable_SelectPage", 2);            sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);            sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);            return sph.ExecuteReader();                }                      }}

// Author:                    msdc// Created:                    2015-2-8// Last Modified:            2015-2-8using System;using System.Collections;using System.Collections.Generic;using System.Data;using mojoportal.Data;    namespace mojoportal.Business{     [ProtoContract]    public class DepTable    {#region Constructors        public DepTable()        {}         #endregion#region Private Properties        private int id = -1;        private string department = string.Empty;        #endregion#region Public Properties        [ProtoMember(1)]        public int Id         {            get { return id; }            set { id = value; }        }        [ProtoMember(2)]        public string Department         {            get { return department; }            set { department = value; }        }#endregion     }    }

// Author:                    msdc// Created:                    2015-2-8// Last Modified:            2015-2-8using System;using System.Collections;using System.Collections.Generic;using System.Data;using mojoportal.Data;    namespace mojoportal.Business{        public class DoDepTable    {      #region Private Methods        /// <summary>        /// Gets an instance of DepTable.        /// </summary>        /// <param name="id"> id </param>        private static DepTable GetDepTable(            int id)         {            using(IDataReader reader = DBDepTable.GetOne(                id))             {            return PopulateFromReader(reader);            }                    }                        private static DepTable PopulateFromReader(IDataReader reader)         {            DepTable depTable = new DepTable();            if(reader.Read())            {                depTable.Id = Convert.ToInt32(reader["Id"]);                depTable.Department = reader["Department"].ToString();                        }            return depTable;         }                /// <summary>        /// Persists a new instance of DepTable. Returns true on success.        /// </summary>        /// <returns></returns>        private static bool Create(DepTable depTable)        {             int newID = 0;                        newID = DBDepTable.Create(                depTable.Department);                             depTable.Id = newID;                                return (newID > 0);        }                /// <summary>        /// Updates this instance of DepTable. Returns true on success.        /// </summary>        /// <returns>bool</returns>        private static bool Update(DepTable depTable)        {            return DBDepTable.Update(                depTable.Id,                 depTable.Department);                         }                        #endregion#region Public Methods        /// <summary>        /// Saves this instance of DepTable. Returns true on success.        /// </summary>        /// <returns>bool</returns>        public static bool Save(DepTable depTable)        {            if( depTable.Id > 0)            {                return Update(depTable);            }            else            {                return Create(depTable);            }        }                            #endregion#region Static Methods        /// <summary>        /// Deletes an instance of DepTable. Returns true on success.        /// </summary>        /// <param name="id"> id </param>        /// <returns>bool</returns>        public static bool Delete(            int  id)         {            return DBDepTable.Delete(                id);         }                        /// <summary>        /// Gets a count of DepTable.         /// </summary>        public static int GetCount()        {            return DBDepTable.GetCount();        }            private static IList<DepTable> LoadListFromReader(IDataReader reader)        {            IList<DepTable> depTableList = new List<DepTable>();            try            {                while (reader.Read())                {                    DepTable depTable = new DepTable();                    depTable.Id = Convert.ToInt32(reader["Id"]);                    depTable.Department = reader["Department"].ToString();                    depTableList.Add(depTable);                                    }            }            finally            {                reader.Close();            }                return depTableList;                }                         /// <summary>        /// Gets an IList with all instances of DepTable.        /// </summary>        public static IList<DepTable> GetAll()        {            IDataReader reader = DBDepTable.GetAll();            return LoadListFromReader(reader);            }            /// <summary>        /// Gets an IList with page of instances of DepTable.        /// </summary>        /// <param name="pageNumber">The page number.</param>        /// <param name="pageSize">Size of the page.</param>        /// <param name="totalPages">total pages</param>        public static IList<DepTable> GetPage(int pageNumber, int pageSize, out int itemCount)        {            itemCount = 1;            IDataReader reader = DBDepTable.GetPage(pageNumber, pageSize, out itemCount);                return LoadListFromReader(reader);        }                         #endregion     }    }