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

NetworkComms网络通信框架序言

DepTable表 主要作用 存放单位名称

如图：

模板下载地址CodeSmith版本为v6.5

第一步：用CodeSmith模板生成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

DepTable_Insert 插入数据DepTable_Update 更新数据

DepTable_SelectPage 获取分页数据

DepTable_SelectAll 获取所有数据

DepTable_SelectOne 获取某个数据

DepTable_GetCount 获取数量

DepTable_Delete 删除某个数据

第二步：用codesmith模板生成数据层代码：

// 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.DefineSqlParam