数据库设计,Sql分页存储进程

Sql2012及以上版本的分页比较不难:

ylbtech-DatabaseDesgin:Microsoft-PetSop4.0(宠物商店)-数据库设计-Sql

   
好久不接触那一个闲暇时间回忆一下在先的根基。因为平日使用的时候都以一贯调用SqlDB.dll那几个类。先看这么些类的结构

  使用SharePoint相当短日子的话,都觉得Sql只要求最开头的配备,即不再要求管住和维护;而其实,Sql的管住和安全,都是和SharePoint环境的安居乐业巢倾卵破的,所以,要相对尊重SharePoint中Sql
Server的治本和护卫。

CREATE PROCEDURE usp_pagingin2012
@Start     INT=0, 
@PageLimit INT=10
AS
BEGIN
SELECT * FROM 表名
ORDER  BY 列名
OFFSET @Start ROW 开始位置
FETCH NEXT @PageLimit ROWS ONLY 
END

DatabaseName:PetShop(宠物商店)

 

  本文紧要介绍的有以下三点:

Sql2005-2008分页稍微复杂一点:

Model:宠物商店网站

997755.com澳门葡京 1

  一、关于Sql账号的权柄;

CREATE PROCEDURE usp_paging
@PageNumber INT=1, 
@PageSize   INT=10
AS 
BEGIN
WITH ctepaging 
     AS (SELECT *,
                Row_number() OVER(ORDER BY 列名) AS rownum 
         FROM 表名) 
SELECT * 
FROM   ctepaging 
WHERE  rownum BETWEEN ( @PageNumber - 1 ) * @PageSize + 1 AND
@PageNumber * @PageSize
END

Type:专业网站

 

  二、关于Sql Server的磁盘管理

数据库设计,Sql分页存储进程。 

Url:

纸上得来终觉浅,绝知此事要躬行。个人认为其中的正式操作就是对数据库增删查改
。特别吻合初大方操作数据库,当初自身也是那么来的。下边直接贴代码:

  三、关于Sql Server服务端口的修改

1.A,数据库关系图(Database
Diagram)
再次回到顶部

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
using System.Text;

namespace FileUpDownThroou
{
    /// <summary>
    /// SqlServer访问类
    /// </summary>
    public class SqlDB
    {
        #region <——————————定义参数——————————>
        /// <summary>
        /// 数据库链接字符串
        /// </summary>
        protected readonly string SqlConning = ConfigurationManager.ConnectionStrings["ConnSqlString"].ConnectionString;
        /// <summary>
        /// 数据库链接对象
        /// </summary>
        private SqlConnection sqlConnection;
        /// <summary>
        /// 数据库事务对象
        /// </summary>
        private SqlTransaction sqlTransaction;
        /// <summary>
        /// 构造函数创建数据库链接
        /// </summary>
        #endregion

        #region<——————————构造函数——————————>
        public SqlDB()
        {
            if(sqlConnection==null)
            {
                sqlConnection = new SqlConnection(SqlConning);
                sqlConnection.Open();
            }
        }
        #endregion

        #region <——————————事务处理——————————>
        public SqlTransaction SqlTran
        {
            get { return sqlTransaction; }
            set { sqlTransaction = value; }
        }
        /// <summary>
        /// 打开数据链接
        /// </summary>
        public void OpenConnection()
        {
            if(sqlConnection != null && sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }
        }
        /// <summary>
        /// 关闭数据库链接
        /// </summary>
        public void CloseConnection()
        {
            if(sqlConnection != null && sqlConnection.State==ConnectionState.Open)
            {
                sqlConnection.Close();
            }
        }
        /// <summary>
        /// 开始一个数据库事务
        /// </summary>
        /// <param name="isolationlevel">链接的事务锁定行为</param>
        public void BeginTransaction(IsolationLevel isolationlevel)
        {
            if(sqlConnection.State==ConnectionState.Closed)
            {
                sqlConnection.Open();
            }
            try
            {
                SqlTran = sqlConnection.BeginTransaction(isolationlevel);
            }
            catch(SqlException ex)
            {
                throw new Exception(ex.StackTrace);
            }
            catch(Exception e)
            {
                throw new Exception(e.StackTrace);
            }
        }
        /// <summary>
        /// 开始一个数据库事务
        /// </summary>
        public void BeginTransaction()
        {
            if (sqlConnection.State == ConnectionState.Closed)
            {
                sqlConnection.Open();
            }

            try
            {
                SqlTran = sqlConnection.BeginTransaction();
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.StackTrace);
            }
            catch (Exception e)
            {
                throw new Exception(e.StackTrace);
            }
        }
        /// <summary>
        /// 提交一个数据库事务
        /// </summary>
        public void CommitTransaction()
        {
            try
            {
                sqlTransaction.Commit();
                CloseConnection();
            }
            catch(SqlException ex)
            {
                throw new Exception(ex.StackTrace);
            }
            catch(Exception e)
            {
                throw new Exception(e.StackTrace);
            }
        }
        /// <summary>
        /// 回退一个数据库事务
        /// </summary>
        public void RollBackTransaction()
        {
            try
            {
                SqlTran.Rollback();
                CloseConnection();

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.StackTrace);
            }
            catch (Exception e)
            {
                throw new Exception(e.StackTrace);
            }
        }
        #endregion

        #region <——————————标准操作——————————>

        /// <summary>
        /// 执行Sql语句返回SqlDataReader
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandBehavior">查询影响说明</param>
        /// <returns>满足条件的SqlDataReader</returns>
        public SqlDataReader ExecuteDataReader(string Sql, List<SqlParameter> parameters, CommandType commandType, CommandBehavior commandBehavior)
        {
            return CreateSqlCommand(Sql, parameters, commandType).ExecuteReader(commandBehavior);
        }

        /// <summary>
        /// 执行Sql语句返回SqlDataReader
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>满足条件的SqlDataReader</returns>
        public SqlDataReader ExecuteDataReader(string Sql, List<SqlParameter> parameters, CommandType commandType)
        {
            return ExecuteDataReader(Sql, parameters, commandType, CommandBehavior.Default);
        }

        /// <summary>
        /// 执行Sql语句返回SqlDataReader
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>满足条件的SqlDataReader</returns>
        public SqlDataReader ExecuteDataReader(string Sql, List<SqlParameter> parameters)
        {
            return ExecuteDataReader(Sql, parameters, CommandType.Text, CommandBehavior.Default);
        }

        /// <summary>
        /// 执行Sql语句返回SqlDataReader
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>满足条件的SqlDataReader</returns>
        public SqlDataReader ExecuteDataReader(string Sql)
        {
            return ExecuteDataReader(Sql, null, CommandType.Text, CommandBehavior.Default);
        }

        /// <summary>
        /// 执行Sql语句返回单个对象
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>满足条件的单个对象</returns>
        public object ExecuteScalar(string Sql, List<SqlParameter> parameters, CommandType commandType)
        {
            return CreateSqlCommand(Sql, parameters, commandType).ExecuteScalar();
        }

        /// <summary>
        /// 执行Sql语句返回单个对象
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>满足条件的单个对象</returns>
        public object ExecuteScalar(string Sql, List<SqlParameter> parameters)
        {
            return ExecuteScalar(Sql, parameters, CommandType.Text);
        }

        /// <summary>
        /// 执行Sql语句返回单个对象
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>满足条件的单个对象</returns>
        public object ExecuteScalar(string Sql)
        {
            return ExecuteScalar(Sql, null, CommandType.Text);
        }

        /// <summary>
        /// 执行非查询类的Sql语句
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>成功执行所影响的记录数</returns>
        public int ExecuteNonQuery(string Sql, List<SqlParameter> parameters, CommandType commandType)
        {
            return CreateSqlCommand(Sql, parameters, commandType).ExecuteNonQuery();
        }

        /// <summary>
        /// 执行非查询类的Sql语句z
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>成功执行所影响的记录数</returns>
        public int ExecuteNonQuery(string Sql, List<SqlParameter> parameters)
        {
            return ExecuteNonQuery(Sql, parameters, CommandType.Text);
        }

        /// <summary>
        /// 执行非查询类的Sql语句
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>成功执行所影响的记录数</returns>
        public int ExecuteNonQuery(string Sql)
        {
            return ExecuteNonQuery(Sql, null, CommandType.Text);
        }

        /// <summary>
        /// 执行Sql语句返回DataTable
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string Sql, List<SqlParameter> sqlParameters, CommandType commandType)
        {
            SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql, sqlParameters, commandType));
            DataTable dataTable = new DataTable();
            dataAdapter.Fill(dataTable);
            return dataTable;
        }

        /// <summary>
        /// 执行Sql语句返回DataTable
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string Sql, List<SqlParameter> sqlParameters)
        {
            return ExecuteDataTable(Sql, sqlParameters, CommandType.Text);
        }

        /// <summary>
        /// 执行Sql语句返回DataTable
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string Sql)
        {
            return ExecuteDataTable(Sql, null, CommandType.Text);
        }

        /// <summary>
        /// 执行Sql语句返回DataSet
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, CommandType commandType)
        {
            SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql, sqlParameters, commandType));
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            return dataSet;
        }

        /// <summary>
        /// 执行Sql语句返回DataSet
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters)
        {
            return ExecuteDataSet(Sql, sqlParameters, CommandType.Text);
        }

        /// <summary>
        /// 执行Sql语句返回DataSet
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string Sql)
        {
            return ExecuteDataSet(Sql, null, CommandType.Text);
        }

        /// <summary>
        /// 执行查询,返回查询的分页结果数据集
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="PageIndex">页索引</param>
        /// <param name="PageSize">每页的记录数</param>
        /// <returns>分页结果数据集</returns>
        public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, CommandType commandType, int PageIndex, int PageSize)
        {
            //设置导入的起始地址
            int firstPage = PageIndex * PageSize;

            SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql, sqlParameters, commandType));
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, firstPage, PageSize, "PranaTable");

            return dataSet;
        }

        /// <summary>
        /// 执行查询,返回查询的分页结果数据集
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="PageIndex">页索引</param>
        /// <param name="PageSize">每页的记录数</param>
        /// <returns>分页结果数据集</returns>
        public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, int PageIndex, int PageSize)
        {
            return ExecuteDataSet(Sql, sqlParameters, CommandType.Text, PageIndex, PageSize);
        }

        /// <summary>
        /// 执行查询,返回查询的分页结果数据集
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="PageIndex">页索引</param>
        /// <param name="PageSize">每页的记录数</param>
        /// <returns>分页结果数据集</returns>
        public DataSet ExecuteDataSet(string Sql, int PageIndex, int PageSize)
        {
            return ExecuteDataSet(Sql, null, CommandType.Text, PageIndex, PageSize);
        }

        /// <summary>
        /// 创建Sql命令对象
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>成功创建的SqlCommand对象</returns>
        private SqlCommand CreateSqlCommand(string Sql, List<SqlParameter> parameters, CommandType commandType)
        {
            SqlCommand sqlCommand = new SqlCommand(Sql, sqlConnection, SqlTran);
            sqlCommand.CommandType = commandType;
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    sqlCommand.Parameters.Add(parameter);
                }
            }
            return sqlCommand;
        }

        #endregion

        #region <——————————缓存操作——————————>

        /// <summary>
        /// Hashtable中缓存的参数
        /// </summary>
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// 缓存查询的OleDb参数对象.
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <param name="cmdParms"></param>
        public void CacheParameters(string cacheKey, params SqlParameter[] cmdParms)
        {
            parmCache[cacheKey] = cmdParms;
        }

        /// <summary>
        /// 从缓存获取指定的参数对象数组.
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <returns></returns>
        public SqlParameter[] GetCachedParameters(string cacheKey)
        {
            SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
            {
                return null;
            }

            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
            for (int i = 0, j = cachedParms.Length; i < j; i++)
            {
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
            }

            return clonedParms;
        }

        #endregion

        #region <——————————增删改查——————————>

        private static bool haveTransaction = false;

        public static bool HaveTransaction
        {
            get
            {
                return haveTransaction;
            }
            set
            {
                haveTransaction = value;
            }
        }

        /// <summary>
        /// 查询字符串的默认长度
        /// </summary>
        private const int defaultCommandTextLength = 256;

        /// <summary>
        /// 根据表名等参数创建数据库查询语句
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="topNum">返回的记录数</param>
        /// <param name="field">字段名称</param>
        /// <param name="condition">条件</param>
        /// <param name="order">排序字段</param>
        /// <returns>数据库查询语句</returns>
        private StringBuilder CreateSqlCommandText(string tableName, int topNum, string field, string condition, string order)
        {
            StringBuilder Sql = new StringBuilder(defaultCommandTextLength);
            Sql.Append("SELECT ");
            if (topNum > 0)
            {
                Sql.AppendFormat("TOP {0} ", topNum);
            }
            Sql.AppendFormat("{0} FROM {1} ", field, tableName);
            if (condition != null && condition.Trim().Length != 0)
            {
                Sql.AppendFormat("WHERE {0} ", condition);
            }
            if (order != null && order.Trim().Length != 0)
            {
                Sql.AppendFormat(" ORDER BY {0}", order);
            }
            return Sql;
        }

        /// <summary>
        /// 向表中插入数据
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="field">字段名称字符串</param>
        /// <param name="fieldValue">字段值参数字符串</param>
        /// <param name="parameters">字段参数集合</param>
        /// <returns>成功插入的记录数</returns>
        public int Insert(string tableName, string field, string fieldValue, List<SqlParameter> parameters)
        {
            try
            {
                if (HaveTransaction == false)
                {
                    OpenConnection();
                }
                StringBuilder Sql = new StringBuilder(defaultCommandTextLength);
                Sql.AppendFormat("INSERT INTO {0}({1}) VALUES({2});", tableName, field,
                    fieldValue);
                return ExecuteNonQuery(Sql.ToString(), parameters);
            }
            finally
            {
                if (HaveTransaction == false)
                {
                    CloseConnection();
                }
            }

        }

        /// <summary>
        /// 从表中删除数据
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="condition">删除条件</param>
        /// <param name="parameters">删除条件参数集合</param>
        /// <returns>成功删除的记录数</returns>
        public int Delete(string tableName, string condition, List<SqlParameter> parameters)
        {
            try
            {
                if (HaveTransaction == false)
                {
                    OpenConnection();
                }
                StringBuilder Sql = new StringBuilder(defaultCommandTextLength);
                Sql.AppendFormat("DELETE FROM {0} ", tableName);
                if (condition != null && condition.Trim().Length != 0)
                {
                    Sql.AppendFormat("WHERE {0}", condition);
                }
                return ExecuteNonQuery(Sql.ToString(), parameters);
            }
            finally
            {
                if (HaveTransaction == false)
                {
                    CloseConnection();
                }
            }
        }

        /// <summary>
        /// 根据条件修改表中的数据
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="fieldValue">字段名城</param>
        /// <param name="condition">修改条件</param>
        /// <param name="parameters">修改参数集合</param>
        /// <returns>成功修改的记录数</returns>
        public int Update(string tableName, string fieldValue, string condition, List<SqlParameter> parameters)
        {
            try
            {
                if (HaveTransaction == false)
                {
                    OpenConnection();
                }
                StringBuilder Sql = new StringBuilder(defaultCommandTextLength);
                Sql.AppendFormat("UPDATE {0} SET {1} ", tableName, fieldValue);
                if (condition != null && condition.Trim().Length != 0)
                {
                    Sql.AppendFormat("WHERE {0}", condition);
                }
                return ExecuteNonQuery(Sql.ToString(), parameters);
            }
            finally
            {
                if (HaveTransaction == false)
                {
                    CloseConnection();
                }
            }
        }

        #endregion

    }
}

997755.com澳门葡京 , 

 

其中的各类方法都有声明,应该都看的敞亮。增删查改都有两样的重载

  一、关于Sql账号的权力

1.B,数据库设计脚本(Database Design
Script) – 批处理创设数量
归来顶部

如同那么些DetSet就有5个重载 用的时候依照须求就可以。

  首先,要说的就是有关账号的难题,无论是SharePoint服务器场配置账号,还是各个服务的账号,都以索要对此Sql的权位的。在此此前为了便利,也并不曾授予更甚的切磋,都以向来予以具有权力。其实,SharePoint对于Sql
Server的权力仅仅须要dbcreator和securityadmin权限即可。

1.B.1,Creating Database…

 

997755.com澳门葡京 2

1.B.1.1,Sql/CreateDatabase1.sql

/// <summary>
/// 执行Sql语句再次来到DataSet
/// </summary>
/// <param name=”Sql”>Sql语句</param>
/// <param name=”parameters”>参数集合</param>
/// <param name=”commandType”>命令类型</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(string Sql, List<SqlParameter>
sqlParameters, CommandType commandType)
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql,
sqlParameters, commandType));
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
return dataSet;
}

 

USE [master];

CREATE DATABASE [MSPetShop4];

/// <summary>
/// 执行Sql语句重临DataSet
/// </summary>
/// <param name=”Sql”>Sql语句</param>
/// <param name=”parameters”>参数集合</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(string Sql, List<SqlParameter>
sqlParameters)
{
return ExecuteDataSet(Sql, sqlParameters, CommandType.Text);
}

  二、关于Sql Server的磁盘管理

1.B.1.2,Sql/CreateDatabase2.sql

/// <summary>
/// 执行Sql语句再次来到DataSet
/// </summary>
/// <param name=”Sql”>Sql语句</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(string Sql)
{
return ExecuteDataSet(Sql, null, CommandType.Text);
}

  其次,要说的就是关于Sql的管住,当看到msdn文档中如此设计的时候,自个儿真正感到从前对于Sql
Server领悟太少。理想图景下,还应在独立的大体硬盘上停放 tempdb
数据库、内容数据库、使用率数据库、搜索数据库和 SQL Server 事务日志。

USE [master]

CREATE DATABASE [MSPetShop4Orders]

/// <summary>
/// 执行查询,重临查询的分页结果数据集
/// </summary>
/// <param name=”Sql”>Sql语句</param>
/// <param name=”parameters”>参数集合</param>
/// <param name=”commandType”>命令类型</param>
/// <param name=”PageIndex”>页索引</param>
/// <param name=”PageSize”>每页的记录数</param>
/// <returns>分页结果数据集</returns>
public DataSet ExecuteDataSet(string Sql, List<SqlParameter>
sqlParameters, CommandType commandType, int PageIndex, int PageSize)
{
//设置导入的开场面址
int firstPage = PageIndex * PageSize;

  在进程较快的磁盘中设置数据优先级时,请使用以下分别:
    · Tempdb 数据文件和事情日志
    · 数据库事务日志文件
    · 搜索数据库(搜索管理数据库除外)
    · 数据库数据文件

1.B.1.3,Sql/CreateDatabase3.sql

SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql,
sqlParameters, commandType));
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, firstPage, PageSize, “PranaTable”);

  当然,除了前期的装置,规律的田间管理和监视数据库服务场地,也是SharePoint环境保险的一大因素。

USE [master]

CREATE DATABASE [MSPetShop4Profile]

return dataSet;
}

 

 

/// <summary>
/// 执行查询,再次回到查询的分页结果数据集
/// </summary>
/// <param name=”Sql”>Sql语句</param>
/// <param name=”parameters”>参数集合</param>
/// <param name=”PageIndex”>页索引</param>
/// <param name=”PageSize”>每页的笔录数</param>
/// <returns>分页结果数据集</returns>
public DataSet ExecuteDataSet(string Sql, List<SqlParameter>
sqlParameters, int PageIndex, int PageSize)
{
return ExecuteDataSet(Sql, sqlParameters, CommandType.Text, PageIndex,
PageSize);
}

  三、关于Sql
Server服务端口的修改

1.B.2,Configuring Logins…

/// <summary>
/// 执行查询,重临查询的分页结果数据集
/// </summary>
/// <param name=”Sql”>Sql语句</param>
/// <param name=”PageIndex”>页索引</param>
/// <param name=”PageSize”>每页的笔录数</param>
/// <returns>分页结果数据集</returns>
public DataSet ExecuteDataSet(string Sql, int PageIndex, int PageSize)
{
return ExecuteDataSet(Sql, null, CommandType.Text, PageIndex,
PageSize);
}

  最终,想要说的是关于Sql端口的题材,威名昭著,Sql
Server暗中同意用的就是1433端口,所以,会将SharePoint的数据库暴露出来,越多时候,我们会手动修改这一端口,防止不安全因素。

1.B.2.1,Sql/CreateDBLogin1.sql

 

  修改章程主要有二,一是通过sql 配置工具修改:

997755.com澳门葡京 3997755.com澳门葡京 4

  打开Sql Server Configuration
Manager,找到Aliases的布置,修改默许的1433端口,据本身打听只修改第一个就可以,当然为了幸免难点,都修改了也无妨。如下图:

USE [MSPetShop4]

if not exists (select * from master.dbo.syslogins where loginname = N'mspetshop')
BEGIN
    exec sp_addlogin 'mspetshop' ,'pass@word1', 'MSPetShop4'
END

exec sp_grantdbaccess 'mspetshop'

exec sp_addrolemember 'db_owner', 'mspetshop'

997755.com澳门葡京 5

View Code

  二就是通过修改注册表,因为即使改动Sql
Server的通讯端口,就非得服务器和客户端同时修改,而SharePoint前段平时不设置Sql,当然你也足以只设置管理工具,或许通过修改注册表的办法:

1.B.2.2,Sql/CreateDBLogin2.sql

997755.com澳门葡京 6

997755.com澳门葡京 7997755.com澳门葡京 8

总  结

USE [MSPetShop4Orders]

if not exists (select * from master.dbo.syslogins where loginname = N'mspetshop')
BEGIN
    exec sp_addlogin 'mspetshop' ,'pass@word1', 'MSPetShop4Orders'
END

exec sp_grantdbaccess 'mspetshop'

exec sp_addrolemember 'db_owner', 'mspetshop'

  以上就是近些年打探到的SharePoint中有关Sql
Server安全保管的多少个要点,拿出来分享给大家,以上内容不仅涉及2013本子,对于此前版本照旧适用。当然只怕还有不足,须求后续增强,希望通晓越多的人给予辅导,也指望可以带给急需的人以赞助。

View Code

参照链接

1.B.2.3,Sql/CreateDBLogin3.sql

997755.com澳门葡京 9997755.com澳门葡京 10

USE [MSPetShop4Profile]

if not exists (select * from master.dbo.syslogins where loginname = N'mspetshop')
BEGIN
    exec sp_addlogin 'mspetshop' ,'pass@word1', 'MSPetShop4Profile'
END

exec sp_grantdbaccess 'mspetshop'

exec sp_addrolemember 'db_owner', 'mspetshop'

View Code

1.B.2.4,Sql/CreateDBLogin4.sql

997755.com澳门葡京 11997755.com澳门葡京 12

USE [MSPetShop4Services]

if not exists (select * from master.dbo.syslogins where loginname = N'mspetshop')
BEGIN
    exec sp_addlogin 'mspetshop' ,'pass@word1', 'MSPetShop4Services'
END

exec sp_grantdbaccess 'mspetshop'

exec sp_addrolemember 'db_owner', 'mspetshop'

View Code

1.B.3,Creating Tables…

1.B.3.1, Sql/CreateTables1.sql

997755.com澳门葡京 13997755.com澳门葡京 14

USE [MSPetShop4]

CREATE TABLE [Category] (
    [CategoryId] varchar(10) PRIMARY KEY,
    [Name] varchar(80) NULL,
    [Descn] varchar(255) NULL
)

CREATE TABLE [Inventory] (
    [ItemId] varchar(10) PRIMARY KEY,
    [Qty] int NOT NULL
)

CREATE TABLE [Supplier] (
    [SuppId] int PRIMARY KEY,
    [Name] varchar(80) NULL,
    [Status] varchar(2) NOT NULL,
    [Addr1] varchar(80) NULL,
    [Addr2] varchar(80) NULL,
    [City] varchar(80) NULL,
    [State] varchar(80) NULL,
    [Zip] varchar(5) NULL,
    [Phone] varchar(40) NULL 
)

CREATE TABLE [Product] (
    [ProductId] varchar(10) PRIMARY KEY,
    [CategoryId] varchar(10) NOT NULL REFERENCES [Category]([CategoryId]),
    [Name] varchar(80) NULL,
    [Descn] varchar(255) NULL,
    [Image] varchar(80) NULL
)

CREATE TABLE [Item] (
    [ItemId] varchar(10) PRIMARY KEY,
    [ProductId] varchar(10) NOT NULL REFERENCES [Product]([ProductId]),
    [ListPrice] decimal(10, 2) NULL,
    [UnitCost] decimal(10, 2) NULL,
    [Supplier] int NULL REFERENCES [Supplier]([SuppId]),
    [Status] varchar(2) NULL,
    [Name] varchar(80) NULL,
    [Image] varchar(80) NULL
)

CREATE INDEX [IxItem] ON [Item]([ProductId], [ItemId], [ListPrice], [Name])

CREATE INDEX [IxProduct1] ON [Product]([Name])
CREATE INDEX [IxProduct2] ON [Product]([CategoryId])
CREATE INDEX [IxProduct3] ON [Product]([CategoryId], [Name])
CREATE INDEX [IxProduct4] ON [Product]([CategoryId], [ProductId], [Name])

View Code

1.B.3.2, Sql/CreateTables2.sql

997755.com澳门葡京 15997755.com澳门葡京 16

USE [MSPetShop4Orders]

CREATE TABLE [Orders] (
    [OrderId] int IDENTITY PRIMARY KEY,
    [UserId] varchar(20) NOT NULL,
    [OrderDate] datetime NOT NULL,
    [ShipAddr1] varchar(80) NOT NULL,
    [ShipAddr2] varchar(80) NULL,
    [ShipCity] varchar(80) NOT NULL,
    [ShipState] varchar(80) NOT NULL,
    [ShipZip] varchar(20) NOT NULL,
    [ShipCountry] varchar(20) NOT NULL,
    [BillAddr1] varchar(80) NOT NULL,
    [BillAddr2] varchar(80) NULL,
    [BillCity] varchar(80) NOT NULL,
    [BillState] varchar(80) NOT NULL,
    [BillZip] varchar(20) NOT NULL,
    [BillCountry] varchar(20) NOT NULL,
    [Courier] varchar(80) NOT NULL,
    [TotalPrice] decimal(10, 2) NOT NULL,
    [BillToFirstName] varchar(80) NOT NULL,
    [BillToLastName] varchar(80) NOT NULL,
    [ShipToFirstName] varchar(80) NOT NULL,
    [ShipToLastName] varchar(80) NOT NULL,
    [AuthorizationNumber] int NOT NULL,
    [Locale] varchar(20) NOT NULL
)

CREATE TABLE [LineItem] (
    [OrderId] int NOT NULL REFERENCES [Orders]([OrderId]),
    [LineNum] int NOT NULL,
    [ItemId] varchar(10) NOT NULL,
    [Quantity] int NOT NULL,
    [UnitPrice] decimal(10, 2) NOT NULL,
    CONSTRAINT [PkLineItem] PRIMARY KEY ([OrderId], [LineNum])
)

CREATE TABLE [OrderStatus] (
    [OrderId] int NOT NULL REFERENCES [Orders]([OrderId]),
    [LineNum] int NOT NULL,
    [Timestamp] datetime NOT NULL,
    [Status] varchar(2) NOT NULL,
    CONSTRAINT [PkOrderStatus] PRIMARY KEY ([OrderId], [LineNum])
)

View Code

1.B.3.3, Sql/CreateTables3.sql

997755.com澳门葡京 17997755.com澳门葡京 18

USE [MSPetShop4Profile]

CREATE TABLE [Profiles] (
    [UniqueID] [int] IDENTITY (1, 1) NOT NULL ,
    [Username] [varchar] (256)  NOT NULL ,
    [ApplicationName] [varchar] (256)  NOT NULL ,
    [IsAnonymous] [bit] NULL ,
    [LastActivityDate] [datetime] NULL ,
    [LastUpdatedDate] [datetime] NULL ,
    CONSTRAINT [PK_Profiles_1] PRIMARY KEY  NONCLUSTERED 
    (
        [UniqueID]
    )  ON [PRIMARY] ,
    CONSTRAINT [PK_Profiles] UNIQUE  CLUSTERED 
    (
        [Username],
        [ApplicationName]
    )  ON [PRIMARY] 
) ON [PRIMARY]

CREATE TABLE [Cart] (
    [UniqueID] [int] NOT NULL ,
    [ItemId] [varchar] (10)  NOT NULL ,
    [Name] [varchar] (80)  NOT NULL ,
    [Type] [varchar] (80)  NOT NULL ,
    [Price] [decimal](10, 2) NOT NULL ,
    [CategoryId] [varchar] (10)  NOT NULL ,
    [ProductId] [varchar] (10)  NOT NULL ,
    [IsShoppingCart] [bit]  NOT NULL ,
    [Quantity] [int] NOT NULL ,
    CONSTRAINT [FK_Cart_Profiles] FOREIGN KEY 
    (
        [UniqueID]
    ) REFERENCES [Profiles] (
        [UniqueID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE 
) ON [PRIMARY]

CREATE TABLE [Account] (
    [UniqueID] [int] NOT NULL ,
    [Email] [varchar] (80)  NOT NULL ,
    [FirstName] [varchar] (80)  NOT NULL ,
    [LastName] [varchar] (80)  NOT NULL ,
    [Address1] [varchar] (80)  NOT NULL ,
    [Address2] [varchar] (80)  NULL ,
    [City] [varchar] (80)  NOT NULL ,
    [State] [varchar] (80)  NOT NULL ,
    [Zip] [varchar] (20)  NOT NULL ,
    [Country] [varchar] (20)  NOT NULL ,
    [Phone] [varchar] (20)  NULL ,
    CONSTRAINT [FK_Account_Profiles] FOREIGN KEY (
        [UniqueID]
    ) REFERENCES [Profiles] (
        [UniqueID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE 
) ON [PRIMARY]

CREATE  CLUSTERED  INDEX [FK_Cart_UniqueID] ON [dbo].[Cart]([UniqueID]) ON [PRIMARY]

CREATE  CLUSTERED  INDEX [FK_Account_UniqueID] ON [dbo].[Account]([UniqueID]) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_SHOPPINGCART] ON [dbo].[Cart] (
    [IsShoppingCart] ASC
)

View Code

1.B.4,Loading Data…

1.B.4.1,Sql/LoadTables1.sql

997755.com澳门葡京 19997755.com澳门葡京 20

USE [MSPetShop4]

INSERT INTO [Category] VALUES ('FISH', 'Fish', 'Fish')
INSERT INTO [Category] VALUES ('BYARD', 'Backyard','Backyard')
INSERT INTO [Category] VALUES ('BIRDS', 'Birds', 'Birds')
INSERT INTO [Category] VALUES ('BUGS', 'Bugs', 'Bugs')
INSERT INTO [Category] VALUES ('EDANGER', 'Endangered', 'Endangered')

INSERT INTO [Supplier] VALUES (1, 'XYZ Pets', 'AC', '600 Avon Way', '', 'Los Angeles', 'CA', '94024', '212-947-0797')
INSERT INTO [Supplier] VALUES (2, 'ABC Pets', 'AC', '700 Abalone Way', '', 'San Francisco', 'CA', '94024', '415-947-0797')

INSERT INTO [Product] VALUES ('FI-01', 'FISH', 'Meno', 'Your worried tiny friend warns you about life''s dangers', '~/Prod_Images/Fish/icon-meno.gif')
INSERT INTO [Product] VALUES ('FI-02', 'FISH', 'Balloonfish', 'It''s your thermometer - the hotter it gets the bigger it gets', '~/Prod_Images/Fish/icon-ballonfish.gif')
INSERT INTO [Product] VALUES ('FI-03', 'FISH', 'Blindfish', 'Likes pressure - ideal for divorcing couples', '~/Prod_Images/Fish/icon-blindfish.gif')
INSERT INTO [Product] VALUES ('FI-04', 'FISH', 'Crabfish', 'Dances and sings every time you feed it!', '~/Prod_Images/Fish/icon-Crabfish.gif')
INSERT INTO [Product] VALUES ('FI-05', 'FISH', 'Eucalyptus', 'For the tickle on your hands: you''ll love the massage', '~/Prod_Images/Fish/icon-eucalyptus.gif')
INSERT INTO [Product] VALUES ('FI-06', 'FISH', 'Mister No', 'Need a companion for the dark times?', '~/Prod_Images/Fish/icon-misterno.gif')
INSERT INTO [Product] VALUES ('FI-07', 'FISH', 'Nosyfish', 'Don''t underestimate this one - it bites!', '~/Prod_Images/Fish/icon-nosyfish.gif')
INSERT INTO [Product] VALUES ('FI-08', 'FISH', 'Tooth Ferry', 'Very sensitive vegetarian, needs food every two months', '~/Prod_Images/Fish/icon-toothferry.gif')
INSERT INTO [Product] VALUES ('BY-01', 'BYARD', 'Sheep', 'Your soft hugging buddy', '~/Prod_Images/Backyard/icon-sheep.gif')
INSERT INTO [Product] VALUES ('BY-02', 'BYARD', 'Cat', 'The friend you will never see', '~/Prod_Images/Backyard/icon-cat.gif')
INSERT INTO [Product] VALUES ('BY-03', 'BYARD', 'Raccoon', 'Always keeps your dishes clean', '~/Prod_Images/Backyard/icon-raccoon.gif')
INSERT INTO [Product] VALUES ('BY-04', 'BYARD', 'Goose', 'For your protection delivered in our special safety-bag', '~/Prod_Images/Backyard/icon-goose.gif')
INSERT INTO [Product] VALUES ('BY-05', 'BYARD', 'Crab', 'The common house crab which lives in the refrigerator', '~/Prod_Images/Backyard/icon-crab.gif')
INSERT INTO [Product] VALUES ('BY-06', 'BYARD', 'Skunk', 'You will love it - especially when you have your in-laws visiting', '~/Prod_Images/Backyard/icon-skunk.gif')
INSERT INTO [Product] VALUES ('BY-07', 'BYARD', 'Zebra', 'The horse of the modern girl', '~/Prod_Images/Backyard/icon-zebra.gif')
INSERT INTO [Product] VALUES ('BD-01', 'BIRDS', 'Pelican', 'Will sit in your garden and admire nature', '~/Prod_Images/Birds/icon-pelican.gif')
INSERT INTO [Product] VALUES ('BD-02', 'BIRDS', 'Penguin', 'Guaranteed to stay by your side', '~/Prod_Images/Birds/icon-penguin.gif')
INSERT INTO [Product] VALUES ('BD-03', 'BIRDS', 'Pteranodon', 'Can''t let go of the past? This is your bird', '~/Prod_Images/Birds/icon-pteranodon.gif')
INSERT INTO [Product] VALUES ('BD-04', 'BIRDS', 'Owl', 'Your personal dictionary ?night & day', '~/Prod_Images/Birds/icon-owl.gif')
INSERT INTO [Product] VALUES ('BD-05', 'BIRDS', 'Duck', 'Lisps but otherwise sings well', '~/Prod_Images/Birds/icon-duck.gif')
INSERT INTO [Product] VALUES ('BG-01', 'BUGS', 'Ant', 'Trash your vacuum cleaner', '~/Prod_Images/Bugs/icon-ant.gif')
INSERT INTO [Product] VALUES ('BG-02', 'BUGS', 'Butterfly', 'Increased beauty with age', '~/Prod_Images/Bugs/icon-butterfly.gif')
INSERT INTO [Product] VALUES ('BG-03', 'BUGS', 'Spider', 'Loves a good massage', '~/Prod_Images/Bugs/icon-spider.gif')
INSERT INTO [Product] VALUES ('BG-04', 'BUGS', 'Slug', 'Your soft hugging buddy', '~/Prod_Images/Bugs/icon-slug.gif')
INSERT INTO [Product] VALUES ('BG-05', 'BUGS', 'Frog', 'Want to get rid of an insect previously bought?', '~/Prod_Images/Bugs/icon-frog.gif')
INSERT INTO [Product] VALUES ('BG-06', 'BUGS', 'Dragonfly', 'Beware of the meat lover', '~/Prod_Images/Bugs/icon-dragonfly.gif')
INSERT INTO [Product] VALUES ('DR-01', 'EDANGER', 'Skeleton', 'Dumb but hollow', '~/Prod_Images/Endangered/icon-skeleton.gif')
INSERT INTO [Product] VALUES ('DR-02', 'EDANGER', 'Pet', 'The originals ?honestly!', '~/Prod_Images/Endangered/icon-pet.gif')
INSERT INTO [Product] VALUES ('DR-03', 'EDANGER', 'Dino', 'Special offer: only for a limited time', '~/Prod_Images/Endangered/icon-dino.gif')
INSERT INTO [Product] VALUES ('DR-04', 'EDANGER', 'Panda', 'Last one ?go for it!', '~/Prod_Images/Endangered/icon-panda.gif')
INSERT INTO [Product] VALUES ('DR-05', 'EDANGER', 'Fish', 'They are waiting for your help', '~/Prod_Images/Endangered/icon-fish.gif')

INSERT INTO [Item] VALUES ('EST-1', 'FI-01', 16.50, 10.00, 1, 'P', 'Happy', '~/Prod_Images/Fish/item-meno-happy.gif')
INSERT INTO [Item] VALUES ('EST-2', 'FI-01', 17.50, 10.00, 1, 'P', 'Camouflage', '~/Prod_Images/Fish/item-meno-camouflage.gif')
INSERT INTO [Item] VALUES ('EST-3', 'FI-01', 15.90, 10.00, 1, 'P', 'Worried', '~/Prod_Images/Fish/item-meno-worried.gif')
INSERT INTO [Item] VALUES ('EST-4', 'FI-02', 17.50, 12.00, 1, 'P', 'Extra Stretch', '~/Prod_Images/Fish/item-balloon-extra-stretch.gif')
INSERT INTO [Item] VALUES ('EST-5', 'FI-02', 18.90, 12.00, 1, 'P', 'Natural', '~/Prod_Images/Fish/item-balloon-natural.gif')
INSERT INTO [Item] VALUES ('EST-6', 'FI-02', 19.50, 12.00, 1, 'P', 'Flammable', '~/Prod_Images/Fish/item-balloon-flammable.gif')
INSERT INTO [Item] VALUES ('EST-7', 'FI-03', 21.50, 15.00, 1, 'P', 'Blind', '~/Prod_Images/Fish/item-blindfish-blind.gif')
INSERT INTO [Item] VALUES ('EST-8', 'FI-03', 22.50, 15.00, 1, 'P', 'Short Sighted', '~/Prod_Images/Fish/item-blindfish-shortsighted.gif')
INSERT INTO [Item] VALUES ('EST-9', 'FI-03', 24.50, 15.00, 1, 'P', 'Far Sighted', '~/Prod_Images/Fish/item-blindfish-farsighted.gif')
INSERT INTO [Item] VALUES ('EST-10', 'FI-04', 18.50, 12.00, 1, 'P', 'Tap Dance', '~/Prod_Images/Fish/item-Crabfish-tabdance.gif')
INSERT INTO [Item] VALUES ('EST-11', 'FI-04', 19.50, 12.00, 1, 'P', 'Ballet', '~/Prod_Images/Fish/item-Crabfish-ballet.gif')
INSERT INTO [Item] VALUES ('EST-12', 'FI-04', 18.90, 12.00, 1, 'P', 'Ballroom', '~/Prod_Images/Fish/item-Crabfish-ballroom.gif')
INSERT INTO [Item] VALUES ('EST-13', 'FI-05', 16.50, 10.00, 1, 'P', 'Long Arms', '~/Prod_Images/Fish/item-eucalyptus-longarms.gif')
INSERT INTO [Item] VALUES ('EST-14', 'FI-05', 16.90, 10.00, 1, 'P', 'Short Arms', '~/Prod_Images/Fish/item-eucalyptus-shortarms.gif')
INSERT INTO [Item] VALUES ('EST-15', 'FI-06', 13.90, 8.00, 1, 'P', 'Black', '~/Prod_Images/Fish/item-misterno-black.gif')
INSERT INTO [Item] VALUES ('EST-16', 'FI-06', 14.50, 8.00, 1, 'P', 'Sepia', '~/Prod_Images/Fish/item-misterno-sepia.gif')
INSERT INTO [Item] VALUES ('EST-17', 'FI-06', 15.50, 8.00, 1, 'P', 'Sable', '~/Prod_Images/Fish/item-misterno-sable.gif')
INSERT INTO [Item] VALUES ('EST-18', 'FI-07', 16.50, 10.00, 1, 'P', 'Invidious', '~/Prod_Images/Fish/item-nosyfish-invidious.gif')
INSERT INTO [Item] VALUES ('EST-19', 'FI-07', 17.50, 10.00, 1, 'P', 'Beastly', '~/Prod_Images/Fish/item-nosyfish-beastly.gif')
INSERT INTO [Item] VALUES ('EST-20', 'FI-07', 18.50, 10.00, 1, 'P', 'Mean', '~/Prod_Images/Fish/item-nosyfish-mean.gif')
INSERT INTO [Item] VALUES ('EST-21', 'FI-07', 19.50, 10.00, 1, 'P', 'Sneaky', '~/Prod_Images/Fish/item-nosyfish-sneaky.gif')
INSERT INTO [Item] VALUES ('EST-22', 'FI-08', 28.50, 20.00, 1, 'P', 'Toothless', '~/Prod_Images/Fish/item-toothferry-toothless.gif')
INSERT INTO [Item] VALUES ('EST-23', 'FI-08', 29.50, 20.00, 1, 'P', 'With Teeth', '~/Prod_Images/Fish/item-toothferry-withteeth.gif')
INSERT INTO [Item] VALUES ('EST-24', 'BY-01', 120.95, 99.00, 1, 'P', 'Fuzzy', '~/Prod_Images/Backyard/item-sheep-fuzzy.gif')
INSERT INTO [Item] VALUES ('EST-25', 'BY-01', 130.95, 99.00, 1, 'P', 'Ironed', '~/Prod_Images/Backyard/item-sheep-ironed.gif')
INSERT INTO [Item] VALUES ('EST-26', 'BY-02', 14.95, 2.00, 1, 'P', 'Transparent', '~/Prod_Images/Backyard/item-cat-transparent.gif')
INSERT INTO [Item] VALUES ('EST-27', 'BY-02', 15.95, 2.00, 1, 'P', 'Patterned', '~/Prod_Images/Backyard/item-cat-patterned.gif')
INSERT INTO [Item] VALUES ('EST-28', 'BY-02', 18.95, 2.00, 1, 'P', 'Uncolored', '~/Prod_Images/Backyard/item-cat-uncolored.gif')
INSERT INTO [Item] VALUES ('EST-29', 'BY-03', 42.95, 30.00, 1, 'P', 'Long Tongue', '~/Prod_Images/Backyard/item-raccoon-long-tongue.gif')
INSERT INTO [Item] VALUES ('EST-30', 'BY-03', 45.95, 30.00, 1, 'P', 'Rough Tongue', '~/Prod_Images/Backyard/item-raccoon-rough-tongue.gif')
INSERT INTO [Item] VALUES ('EST-31', 'BY-03', 48.95, 30.00, 1, 'P', 'Hairy Tongue', '~/Prod_Images/Backyard/item-raccoon-hairy-tongue.gif')
INSERT INTO [Item] VALUES ('EST-32', 'BY-04', 20.95, 12.00, 1, 'P', 'Feathered', '~/Prod_Images/Backyard/item-goose-feathered.gif')
INSERT INTO [Item] VALUES ('EST-33', 'BY-04', 22.95, 12.00, 1, 'P', 'Plucked', '~/Prod_Images/Backyard/item-goose-plucked.gif')
INSERT INTO [Item] VALUES ('EST-34', 'BY-05', 12.95, 10.00, 1, 'P', 'Red', '~/Prod_Images/Backyard/item-crab-red.gif')
INSERT INTO [Item] VALUES ('EST-35', 'BY-05', 13.95, 10.00, 1, 'P', 'Orange', '~/Prod_Images/Backyard/item-crab-orange.gif')
INSERT INTO [Item] VALUES ('EST-36', 'BY-05', 14.95, 10.00, 1, 'P', 'Dotted', '~/Prod_Images/Backyard/item-crab-dotted.gif')
INSERT INTO [Item] VALUES ('EST-37', 'BY-06', 18.95, 12.00, 1, 'P', 'Bad Smell', '~/Prod_Images/Backyard/item-skunk-bad-smell.gif')
INSERT INTO [Item] VALUES ('EST-38', 'BY-06', 20.95, 12.00, 1, 'P', 'Really Bad Smell', '~/Prod_Images/Backyard/item-skunk-really-bad-smell.gif')
INSERT INTO [Item] VALUES ('EST-39', 'BY-06', 22.95, 12.00, 1, 'P', 'Worst Smell', '~/Prod_Images/Backyard/item-skunk-worst-smell.gif')
INSERT INTO [Item] VALUES ('EST-40', 'BY-07', 859.95, 500.00, 1, 'P', 'Tiny', '~/Prod_Images/Backyard/item-zebra-tiny.gif')
INSERT INTO [Item] VALUES ('EST-41', 'BY-07', 879.95, 500.00, 1, 'P', 'Small', '~/Prod_Images/Backyard/item-zebra-small.gif')
INSERT INTO [Item] VALUES ('EST-42', 'BY-07', 899.95, 500.00, 1, 'P', 'Medium', '~/Prod_Images/Backyard/item-zebra-medium.gif')
INSERT INTO [Item] VALUES ('EST-43', 'BY-07', 949.95, 500.00, 1, 'P', 'Large', '~/Prod_Images/Backyard/item-zebra-large.gif')
INSERT INTO [Item] VALUES ('EST-44', 'BD-01', 41.95, 30.00, 1, 'P', 'Flower Loving', '~/Prod_Images/Birds/item-pelican-flowerloving.gif')
INSERT INTO [Item] VALUES ('EST-45', 'BD-01', 45.95, 30.00, 1, 'P', 'Grass Loving', '~/Prod_Images/Birds/item-pelican-grassloving.gif')
INSERT INTO [Item] VALUES ('EST-46', 'BD-02', 120.99, 99.00, 1, 'P', 'Adventurous', '~/Prod_Images/Birds/item-penguine-adventurous.gif')
INSERT INTO [Item] VALUES ('EST-47', 'BD-02', 130.99, 99.00, 1, 'P', 'Homey', '~/Prod_Images/Birds/item-penguine-homey.gif')
INSERT INTO [Item] VALUES ('EST-48', 'BD-03', 130.99, 99.00, 1, 'P', 'Old', '~/Prod_Images/Birds/item-pteranodon-old.gif')
INSERT INTO [Item] VALUES ('EST-49', 'BD-03', 130.99, 99.00, 1, 'P', 'Ancient', '~/Prod_Images/Birds/item-pteranodon-ancient.gif')
INSERT INTO [Item] VALUES ('EST-50', 'BD-04', 80.99, 50.00, 1, 'P', 'Day', '~/Prod_Images/Birds/item-owl-day.gif')
INSERT INTO [Item] VALUES ('EST-51', 'BD-04', 85.99, 50.00, 1, 'P', 'Night', '~/Prod_Images/Birds/item-owl-night.gif')
INSERT INTO [Item] VALUES ('EST-52', 'BD-05', 33.99, 20.00, 1, 'P', 'Domestic', '~/Prod_Images/Birds/item-duck-domestic.gif')
INSERT INTO [Item] VALUES ('EST-53', 'BD-05', 38.99, 20.00, 1, 'P', 'Wild', '~/Prod_Images/Birds/item-duck-wild.gif')
INSERT INTO [Item] VALUES ('EST-54', 'BG-01', 0.25, 0.02, 1, 'P', 'Worker', '~/Prod_Images/Bugs/item-ant-worker.gif')
INSERT INTO [Item] VALUES ('EST-55', 'BG-01', 0.30, 0.02, 1, 'P', 'Queen', '~/Prod_Images/Bugs/item-ant-queen.gif')
INSERT INTO [Item] VALUES ('EST-56', 'BG-01', 0.50, 0.02, 1, 'P', 'Soldier', '~/Prod_Images/Bugs/item-ant-soldier.gif')
INSERT INTO [Item] VALUES ('EST-57', 'BG-02', 0.70, 0.10, 1, 'P', 'Larva', '~/Prod_Images/Bugs/item-butterfly-larva.gif')
INSERT INTO [Item] VALUES ('EST-58', 'BG-02', 0.80, 0.10, 1, 'P', 'Pupa', '~/Prod_Images/Bugs/item-butterfly-pupa.gif')
INSERT INTO [Item] VALUES ('EST-59', 'BG-02', 0.90, 0.10, 1, 'P', 'Adult', '~/Prod_Images/Bugs/item-butterfly-adult.gif')
INSERT INTO [Item] VALUES ('EST-60', 'BG-03', 2.50, 1.00, 1, 'P', 'Arniladisplicata', '~/Prod_Images/Bugs/item-spider-aranielladisplicata.gif')
INSERT INTO [Item] VALUES ('EST-61', 'BG-03', 2.60, 1.00, 1, 'P', 'Dysdera Crocata', '~/Prod_Images/Bugs/item-spider-dysderacrocata.gif')
INSERT INTO [Item] VALUES ('EST-62', 'BG-04', 0.89, 0.15, 1, 'P', 'Naked', '~/Prod_Images/Bugs/item-slug-naked.gif')
INSERT INTO [Item] VALUES ('EST-63', 'BG-04', 0.99, 0.15, 1, 'P', 'Habitat', '~/Prod_Images/Bugs/item-slug-habitat.gif')
INSERT INTO [Item] VALUES ('EST-64', 'BG-05', 8.99, 2.50, 1, 'P', 'False', '~/Prod_Images/Bugs/item-frog-false.gif')
INSERT INTO [Item] VALUES ('EST-65', 'BG-05', 9.99, 2.50, 1, 'P', 'True', '~/Prod_Images/Bugs/item-frog-true.gif')
INSERT INTO [Item] VALUES ('EST-66', 'BG-06', 1.20, 0.80, 1, 'P', 'Omnivore', '~/Prod_Images/Bugs/item-dragonfly-omnivore.gif')
INSERT INTO [Item] VALUES ('EST-67', 'BG-06', 1.30, 0.80, 1, 'P', 'Vegetarian', '~/Prod_Images/Bugs/item-dragonfly-vegetarian.gif')
INSERT INTO [Item] VALUES ('EST-68', 'BG-06', 1.40, 0.80, 1, 'P', 'Vegan', '~/Prod_Images/Bugs/item-dragonfly-vegan.gif')
INSERT INTO [Item] VALUES ('EST-69', 'DR-01', 150.00, 100.00, 1, 'P', 'Male', '~/Prod_Images/Endangered/item-skeleton-male.gif')
INSERT INTO [Item] VALUES ('EST-70', 'DR-01', 160.00, 100.00, 1, 'P', 'Female', '~/Prod_Images/Endangered/item-skeleton-female.gif')
INSERT INTO [Item] VALUES ('EST-71', 'DR-01', 170.00, 100.00, 1, 'P', 'Aphrodite', '~/Prod_Images/Endangered/item-skeleton-aphrodite.gif')
INSERT INTO [Item] VALUES ('EST-72', 'DR-01', 180.00, 100.00, 1, 'P', 'Hermaphrodite', '~/Prod_Images/Endangered/item-skeleton-hermaphrodite.gif')
INSERT INTO [Item] VALUES ('EST-73', 'DR-02', 45.00, 22.00, 1, 'P', 'Rover', '~/Prod_Images/Endangered/item-pet-rover.gif')
INSERT INTO [Item] VALUES ('EST-74', 'DR-02', 48.00, 22.00, 1, 'P', 'Trumpet', '~/Prod_Images/Endangered/item-pet-thumper.gif')
INSERT INTO [Item] VALUES ('EST-75', 'DR-02', 49.00, 22.00, 1, 'P', 'Kitty', '~/Prod_Images/Endangered/item-pet-kitty.gif')
INSERT INTO [Item] VALUES ('EST-76', 'DR-03', 349.00, 220.00, 1, 'P', 'Spiky', '~/Prod_Images/Endangered/item-dino-spiky.gif')
INSERT INTO [Item] VALUES ('EST-77', 'DR-03', 379.00, 220.00, 1, 'P', 'Shaved', '~/Prod_Images/Endangered/item-dino-shaved.gif')
INSERT INTO [Item] VALUES ('EST-78', 'DR-03', 399.00, 220.00, 1, 'P', 'Pointy', '~/Prod_Images/Endangered/item-dino-pointy.gif')
INSERT INTO [Item] VALUES ('EST-79', 'DR-04', 1999.00, 1500.00, 1, 'P', 'Exclusive', '~/Prod_Images/Endangered/item-panda-exclusive.gif')
INSERT INTO [Item] VALUES ('EST-80', 'DR-05', 22.95, 16.00, 1, 'P', 'Lost', '~/Prod_Images/Endangered/item-fish-lost.gif')
INSERT INTO [Item] VALUES ('EST-81', 'DR-05', 22.95, 16.00, 1, 'P', 'Drunk', '~/Prod_Images/Endangered/item-fish-drunk.gif')
INSERT INTO [Item] VALUES ('EST-82', 'DR-05', 22.95, 16.00, 1, 'P', 'Caught', '~/Prod_Images/Endangered/item-fish-caught.gif')

INSERT INTO [Inventory] VALUES ('EST-1', 10000)
INSERT INTO [Inventory] VALUES ('EST-2', 10000)
INSERT INTO [Inventory] VALUES ('EST-3', 10000)
INSERT INTO [Inventory] VALUES ('EST-4', 10000)
INSERT INTO [Inventory] VALUES ('EST-5', 10000)
INSERT INTO [Inventory] VALUES ('EST-6', 10000)
INSERT INTO [Inventory] VALUES ('EST-7', 10000)
INSERT INTO [Inventory] VALUES ('EST-8', 10000)
INSERT INTO [Inventory] VALUES ('EST-9', 10000)
INSERT INTO [Inventory] VALUES ('EST-10', 10000)
INSERT INTO [Inventory] VALUES ('EST-11', 10000)
INSERT INTO [Inventory] VALUES ('EST-12', 10000)
INSERT INTO [Inventory] VALUES ('EST-13', 10000)
INSERT INTO [Inventory] VALUES ('EST-14', 10000)
INSERT INTO [Inventory] VALUES ('EST-15', 10000)
INSERT INTO [Inventory] VALUES ('EST-16', 10000)
INSERT INTO [Inventory] VALUES ('EST-17', 10000)
INSERT INTO [Inventory] VALUES ('EST-18', 10000)
INSERT INTO [Inventory] VALUES ('EST-19', 10000)
INSERT INTO [Inventory] VALUES ('EST-20', 10000)
INSERT INTO [Inventory] VALUES ('EST-21', 10000)
INSERT INTO [Inventory] VALUES ('EST-22', 10000)
INSERT INTO [Inventory] VALUES ('EST-23', 10000)
INSERT INTO [Inventory] VALUES ('EST-24', 10000)
INSERT INTO [Inventory] VALUES ('EST-25', 10000)
INSERT INTO [Inventory] VALUES ('EST-26', 10000)
INSERT INTO [Inventory] VALUES ('EST-27', 10000)
INSERT INTO [Inventory] VALUES ('EST-28', 10000)
INSERT INTO [Inventory] VALUES ('EST-29', 10000)
INSERT INTO [Inventory] VALUES ('EST-30', 10000)
INSERT INTO [Inventory] VALUES ('EST-31', 10000)
INSERT INTO [Inventory] VALUES ('EST-32', 10000)
INSERT INTO [Inventory] VALUES ('EST-33', 10000)
INSERT INTO [Inventory] VALUES ('EST-34', 10000)
INSERT INTO [Inventory] VALUES ('EST-35', 10000)
INSERT INTO [Inventory] VALUES ('EST-36', 10000)
INSERT INTO [Inventory] VALUES ('EST-37', 10000)
INSERT INTO [Inventory] VALUES ('EST-38', 10000)
INSERT INTO [Inventory] VALUES ('EST-39', 10000)
INSERT INTO [Inventory] VALUES ('EST-40', 10000)
INSERT INTO [Inventory] VALUES ('EST-41', 10000)
INSERT INTO [Inventory] VALUES ('EST-42', 10000)
INSERT INTO [Inventory] VALUES ('EST-43', 10000)
INSERT INTO [Inventory] VALUES ('EST-44', 10000)
INSERT INTO [Inventory] VALUES ('EST-45', 10000)
INSERT INTO [Inventory] VALUES ('EST-46', 10000)
INSERT INTO [Inventory] VALUES ('EST-47', 10000)
INSERT INTO [Inventory] VALUES ('EST-48', 10000)
INSERT INTO [Inventory] VALUES ('EST-49', 10000)
INSERT INTO [Inventory] VALUES ('EST-50', 10000)
INSERT INTO [Inventory] VALUES ('EST-51', 10000)
INSERT INTO [Inventory] VALUES ('EST-52', 10000)
INSERT INTO [Inventory] VALUES ('EST-53', 10000)
INSERT INTO [Inventory] VALUES ('EST-54', 10000)
INSERT INTO [Inventory] VALUES ('EST-55', 10000)
INSERT INTO [Inventory] VALUES ('EST-56', 10000)  
INSERT INTO [Inventory] VALUES ('EST-57', 10000)
INSERT INTO [Inventory] VALUES ('EST-58', 10000)
INSERT INTO [Inventory] VALUES ('EST-59', 10000)
INSERT INTO [Inventory] VALUES ('EST-60', 10000)
INSERT INTO [Inventory] VALUES ('EST-61', 10000)
INSERT INTO [Inventory] VALUES ('EST-62', 10000)
INSERT INTO [Inventory] VALUES ('EST-63', 10000)
INSERT INTO [Inventory] VALUES ('EST-64', 10000)
INSERT INTO [Inventory] VALUES ('EST-65', 10000)
INSERT INTO [Inventory] VALUES ('EST-66', 10000)
INSERT INTO [Inventory] VALUES ('EST-67', 10000)
INSERT INTO [Inventory] VALUES ('EST-68', 10000)
INSERT INTO [Inventory] VALUES ('EST-69', 10000)
INSERT INTO [Inventory] VALUES ('EST-70', 10000)
INSERT INTO [Inventory] VALUES ('EST-71', 10000)
INSERT INTO [Inventory] VALUES ('EST-72', 10000)
INSERT INTO [Inventory] VALUES ('EST-73', 10000)
INSERT INTO [Inventory] VALUES ('EST-74', 10000)
INSERT INTO [Inventory] VALUES ('EST-75', 10000)
INSERT INTO [Inventory] VALUES ('EST-76', 10000)
INSERT INTO [Inventory] VALUES ('EST-77', 10000)
INSERT INTO [Inventory] VALUES ('EST-78', 10000)   
INSERT INTO [Inventory] VALUES ('EST-79', 10000)
INSERT INTO [Inventory] VALUES ('EST-80', 10000)
INSERT INTO [Inventory] VALUES ('EST-81', 10000)
INSERT INTO [Inventory] VALUES ('EST-82', 10000)  

View Code

 

1.C,数据库设计脚本(Database Design Script) – 批处理删除数据返回顶部

1.C.1,Uninstalling Database…

1.C.1.1, Sql/CreateTables1.sql

997755.com澳门葡京 21997755.com澳门葡京 22

USE [master]

DECLARE @spid smallint
DECLARE @sql varchar(4000)

DECLARE crsr CURSOR FAST_FORWARD FOR
    SELECT spid FROM sysprocesses p INNER JOIN sysdatabases d ON d.[name] = 'MSPetShop4' AND p.dbid = d.dbid

OPEN crsr
FETCH NEXT FROM crsr INTO @spid

WHILE @@FETCH_STATUS != -1
BEGIN
    SET @sql = 'KILL ' + CAST(@spid AS varchar)
    EXEC(@sql)
    FETCH NEXT FROM crsr INTO @spid
END

CLOSE crsr
DEALLOCATE crsr

DROP DATABASE [MSPetShop4]

View Code

1.C.1.2, Sql/CreateTables2.sql

997755.com澳门葡京 23997755.com澳门葡京 24

USE [master]

DECLARE @spid smallint
DECLARE @sql varchar(4000)

DECLARE crsr CURSOR FAST_FORWARD FOR
    SELECT spid FROM sysprocesses p INNER JOIN sysdatabases d ON d.[name] = 'MSPetShop4Orders' AND p.dbid = d.dbid

OPEN crsr
FETCH NEXT FROM crsr INTO @spid

WHILE @@FETCH_STATUS != -1
BEGIN
    SET @sql = 'KILL ' + CAST(@spid AS varchar)
    EXEC(@sql)
    FETCH NEXT FROM crsr INTO @spid
END

CLOSE crsr
DEALLOCATE crsr

DROP DATABASE [MSPetShop4Orders]

View Code

1.C.1.3, Sql/CreateTables3.sql

997755.com澳门葡京 25997755.com澳门葡京 26

USE [master]

DECLARE @spid smallint
DECLARE @sql varchar(4000)

DECLARE crsr CURSOR FAST_FORWARD FOR
    SELECT spid FROM sysprocesses p INNER JOIN sysdatabases d ON d.[name] = 'MSPetShop4Profile' AND p.dbid = d.dbid

OPEN crsr
FETCH NEXT FROM crsr INTO @spid

WHILE @@FETCH_STATUS != -1
BEGIN
    SET @sql = 'KILL ' + CAST(@spid AS varchar)
    EXEC(@sql)
    FETCH NEXT FROM crsr INTO @spid
END

CLOSE crsr
DEALLOCATE crsr

DROP DATABASE [MSPetShop4Profile]

View Code

1.C.1.4, Sql/CreateTables3.sql

997755.com澳门葡京 27997755.com澳门葡京 28

USE [master]

DECLARE @spid smallint
DECLARE @sql varchar(4000)

DECLARE crsr CURSOR FAST_FORWARD FOR
    SELECT spid FROM sysprocesses p INNER JOIN sysdatabases d ON d.[name] = 'MSPetShop4Services' AND p.dbid = d.dbid

OPEN crsr
FETCH NEXT FROM crsr INTO @spid

WHILE @@FETCH_STATUS != -1
BEGIN
    SET @sql = 'KILL ' + CAST(@spid AS varchar)
    EXEC(@sql)
    FETCH NEXT FROM crsr INTO @spid
END

CLOSE crsr
DEALLOCATE crsr

DROP DATABASE [MSPetShop4Services]

View Code

D,? 这八个在批处理是怎么处理的

?D.1, Sql/CreateDemoUsers.sql

997755.com澳门葡京 29997755.com澳门葡京 30

USE [MSPetShop4Profile]

DECLARE @userid int

INSERT INTO [Profiles] (
    [Username], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdatedDate]
)
VALUES (
     'AdamBarr', '.NET Pet Shop 4.0', 0, GETDATE(), GETDATE()
)
SET @userid = (SELECT @@IDENTITY)
INSERT INTO [Account] ([UniqueID], [Email], [FirstName], [LastName], [Address1], [Address2], [City], [State], [Zip], [Country], [Phone])
     VALUES
           (@userid, 'someone@microsoft.com', 'Adam', 'Barr', 'Vertigo Software, Inc.', '503A Canal Blvd.', 'Point Richmond', 'CA', '94804', 'USA', '(510) 307-8200')

INSERT INTO [Profiles] (
    [Username], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdatedDate]
)
VALUES (
     'KimAbercrombie', '.NET Pet Shop 4.0', 0, GETDATE(), GETDATE()
)
SET @userid = (SELECT @@IDENTITY)
INSERT INTO [Account] ([UniqueID], [Email], [FirstName], [LastName], [Address1], [Address2], [City], [State], [Zip], [Country], [Phone])
     VALUES
           (@userid, 'someone1@microsoft.com', 'Kim', 'Abercrombie', 'Vertigo Software, Inc.', '503A Canal Blvd.', 'Point Richmond', 'CA', '94804', 'USA', '(510) 307-8200')

INSERT INTO [Profiles] (
    [Username], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdatedDate]
)
VALUES (
     'RobYoung', '.NET Pet Shop 4.0', 0, GETDATE(), GETDATE()
)
SET @userid = (SELECT @@IDENTITY)
INSERT INTO [Account] ([UniqueID], [Email], [FirstName], [LastName], [Address1], [Address2], [City], [State], [Zip], [Country], [Phone])
     VALUES
           (@userid, 'someone2@microsoft.com', 'Rob', 'Young', 'Vertigo Software, Inc.', '503A Canal Blvd.', 'Point Richmond', 'CA', '94804', 'USA', '(510) 307-8200')           

INSERT INTO [Profiles] (
    [Username], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdatedDate]
)
VALUES (
     'TomYoutsey', '.NET Pet Shop 4.0', 0, GETDATE(), GETDATE()
)
SET @userid = (SELECT @@IDENTITY)
INSERT INTO [Account] ([UniqueID], [Email], [FirstName], [LastName], [Address1], [Address2], [City], [State], [Zip], [Country], [Phone])
     VALUES
           (@userid, 'someone3@microsoft.com', 'Tom', 'Youtsey', 'Vertigo Software, Inc.', '503A Canal Blvd.', 'Point Richmond', 'CA', '94804', 'USA', '(510) 307-8200')           

INSERT INTO [Profiles] (
    [Username], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdatedDate]
)
VALUES (
     'GaryWYukish', '.NET Pet Shop 4.0', 0, GETDATE(), GETDATE()
)
SET @userid = (SELECT @@IDENTITY)
INSERT INTO [Account] ([UniqueID], [Email], [FirstName], [LastName], [Address1], [Address2], [City], [State], [Zip], [Country], [Phone])
     VALUES
           (@userid, 'someone4@microsoft.com', 'Gary W.', 'Yukish', 'Vertigo Software, Inc.', '503A Canal Blvd.', 'Point Richmond', 'CA', '94804', 'USA', '(510) 307-8200')           

INSERT INTO [Profiles] (
    [Username], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdatedDate]
)
VALUES (
     'RobCaron', '.NET Pet Shop 4.0', 0, GETDATE(), GETDATE()
)
SET @userid = (SELECT @@IDENTITY)
INSERT INTO [Account] ([UniqueID], [Email], [FirstName], [LastName], [Address1], [Address2], [City], [State], [Zip], [Country], [Phone])
     VALUES
           (@userid, 'someone5@microsoft.com', 'Rob', 'Caron', 'Vertigo Software, Inc.', '503A Canal Blvd.', 'Point Richmond', 'CA', '94804', 'USA', '(510) 307-8200')           

INSERT INTO [Profiles] (
    [Username], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdatedDate]
)
VALUES (
     'KarinZimprich', '.NET Pet Shop 4.0', 0, GETDATE(), GETDATE()
)
SET @userid = (SELECT @@IDENTITY)
INSERT INTO [Account] ([UniqueID], [Email], [FirstName], [LastName], [Address1], [Address2], [City], [State], [Zip], [Country], [Phone])
     VALUES
           (@userid, 'someone6@microsoft.com', 'Karin', 'Zimprich', 'Vertigo Software, Inc.', '503A Canal Blvd.', 'Point Richmond', 'CA', '94804', 'USA', '(510) 307-8200')           

INSERT INTO [Profiles] (
    [Username], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdatedDate]
)
VALUES (
     'RandallBoseman', '.NET Pet Shop 4.0', 0, GETDATE(), GETDATE()
)
SET @userid = (SELECT @@IDENTITY)
INSERT INTO [Account] ([UniqueID], [Email], [FirstName], [LastName], [Address1], [Address2], [City], [State], [Zip], [Country], [Phone])
     VALUES
           (@userid, 'someone7@microsoft.com', 'Randall', 'Boseman', 'Vertigo Software, Inc.', '503A Canal Blvd.', 'Point Richmond', 'CA', '94804', 'USA', '(510) 307-8200')           

INSERT INTO [Profiles] (
    [Username], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdatedDate]
)
VALUES (
     'KevinKennedy', '.NET Pet Shop 4.0', 0, GETDATE(), GETDATE()
)
SET @userid = (SELECT @@IDENTITY)
INSERT INTO [Account] ([UniqueID], [Email], [FirstName], [LastName], [Address1], [Address2], [City], [State], [Zip], [Country], [Phone])
     VALUES
           (@userid, 'someone8@microsoft.com', 'Kevin', 'Kennedy', 'Vertigo Software, Inc.', '503A Canal Blvd.', 'Point Richmond', 'CA', '94804', 'USA', '(510) 307-8200')           

INSERT INTO [Profiles] (
    [Username], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdatedDate]
)
VALUES (
     'DianeTibbott', '.NET Pet Shop 4.0', 0, GETDATE(), GETDATE()
)
SET @userid = (SELECT @@IDENTITY)
INSERT INTO [Account] ([UniqueID], [Email], [FirstName], [LastName], [Address1], [Address2], [City], [State], [Zip], [Country], [Phone])
     VALUES
           (@userid, 'someone9@microsoft.com', 'Diane', 'Tibbott', 'Vertigo Software, Inc.', '503A Canal Blvd.', 'Point Richmond', 'CA', '94804', 'USA', '(510) 307-8200')           

INSERT INTO [Profiles] (
    [Username], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdatedDate]
)
VALUES (
     'GarrettYoung', '.NET Pet Shop 4.0', 0, GETDATE(), GETDATE()
)
SET @userid = (SELECT @@IDENTITY)
INSERT INTO [Account] ([UniqueID], [Email], [FirstName], [LastName], [Address1], [Address2], [City], [State], [Zip], [Country], [Phone])
     VALUES
           (@userid, 'someone10@microsoft.com', 'Garrett', 'Young', 'Vertigo Software, Inc.', '503A Canal Blvd.', 'Point Richmond', 'CA', '94804', 'USA', '(510) 307-8200')           

INSERT INTO [Profiles] (
    [Username], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdatedDate]
)
VALUES (
     'demo', '.NET Pet Shop 4.0', 0, GETDATE(), GETDATE()
)
SET @userid = (SELECT @@IDENTITY)
INSERT INTO [Account] ([UniqueID], [Email], [FirstName], [LastName], [Address1], [Address2], [City], [State], [Zip], [Country], [Phone])
     VALUES
           (@userid, 'someone@microsoft.com', 'Adam', 'Barr', 'Vertigo Software, Inc.', '503A Canal Blvd.', 'Point Richmond', 'CA', '94804', 'USA', '(510) 307-8200')

View Code

?D.2, Sql/DropDBLogin.sql

997755.com澳门葡京 31997755.com澳门葡京 32

USE [master]
IF  EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = N'mspetshop')
EXEC master.dbo.sp_droplogin @loginame = N'mspetshop'

View Code

 

作者:ylbtech
出处:http://ylbtech.cnblogs.com/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website