ASP.NET系统数据库部署策略主要是将创建数据库、Membership数据库、添加Membership用户、创建数据库表、向数据库中插入数据等脚本,分别组织在单独的脚本文件中,然后由一个入口批处理文件通过osql命令调用执行各个脚本,这样,只需要运行批处理文件数据库部署就可以完成,下面是这个过程中比较细节的一些技术。
1、Membership数据库
在很多中小型应用中使用Membership数据库是一个很好的选择,这样可以使得工作的重点更多地放在业务逻辑中,而不用考虑用户权限等问题,Membership数据库部署的批处理命令如下:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql -S localhost -E -A all -d [dbName]
2、导入Membership用户
添加了Membership数据库之后我们需要添加初始用户,即管理员用户,在一键式部署中,我们需要用脚本进行创建,用脚本创建自然是调用membership的SP aspnet_Membership_CreateUser,然后这个存储过程最复杂的是构造其密码和salt,密码需要进行sha1加密,这是推荐的做法,而这种加密的过程并不在数据库层次上,因此我们需要在自己的脚本中写加密过程,下面是添加用户的脚本。
USE [db]
SET QUOTED_IDENTIFIER ON;
GO
declare @salt nvarchar(128)
declare @password varbinary(256)
declare @input varbinary(512)
declare @hash varchar(64)
-- Change these values (@salt should be Base64 encoded)
set @salt = N'eyhKDP858wdrYHbBmFoQ6DXzFE1FB+RDP4ULrpoZXt6f'
set @password = convert(varbinary(256),N'Password123')
set @input = hashbytes('sha1',cast('' as xml).value('xs:base64Binary(sql:variable(''@salt''))','varbinary(256)') + @password)
set @hash = cast('' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable(''@input'')))','varchar(64)')
-- @hash now contains a suitable password hash
-- Now create the user using the value of @salt as the salt, and the value of @hash as the password (with the @PasswordFormat set to 1)
DECLARE @return_value int,
@UserId uniqueidentifier
EXEC @return_value = [dbo].[aspnet_Membership_CreateUser]
@ApplicationName = N'/',
@UserName = N'Admin',
@Password = @hash,
@PasswordSalt = @salt,
@Email = N'Admin@icardpay.com',
@PasswordQuestion = N'Whats your favorite color',
@PasswordAnswer = N'Green',
@IsApproved = 1,
@CurrentTimeUtc = '2012-02-01',
@CreateDate = '2012-02-01',
@UniqueEmail = 1,
@PasswordFormat = 1,
@UserId = @UserId OUTPUT
SELECT @UserId as N'@UserId'
SELECT 'Return Value' = @return_value
3、用Osql运行脚本
在批处理文件中可以直接调用osql命令调用各脚本文件执行sql,下面是批处理文件的例子:
@Echo Off
cls
REM ***************************************************************************
REM **
REM ** Name: InstallDatabases
REM ** Desc: SQL Server Database Setup for Pet Shop 4 Application.
REM **
REM ** Date: 10/24/2005
REM **
REM **************************************************************************/
@Echo.
@Echo *******************************************************************************
@Echo * *
@Echo * Pet Shop 4 Database Setup Script *
@Echo * *
@Echo * *
@Echo * This script will create Pet Shop 4 databases and register them *
@Echo * for Sql Cache Dependency. You should have SQL Server 2005 installed *
@Echo * on you local machine as default instance. *
@Echo * *
@Echo * Modify this script to reflect the way you connect to the SQL Server *
@Echo * as well as if location of the .NET 2.0 runtime on your computer *
@Echo * is different from C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727. *
@Echo * *
@Echo * If you wish to cancel, press [CTRL]-C to terminate the batch job. *
@Echo * *
@Echo *******************************************************************************
@Echo.
pause
@Echo.
@Echo.
@Echo *******************************************************************************
@Echo * Creating Databases... *
@Echo *******************************************************************************
@Echo.
osql -E -i CreateDatabase.sql
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql -S localhost -E -A all -d ICardPay
@Echo.
@Echo.
@Echo *******************************************************************************
@Echo * Configuring Logins... *
@Echo *******************************************************************************
@Echo.
osql -E -i CreateUser.sql
@Echo.
@Echo.
@Echo *******************************************************************************
@Echo * Creating Tables... *
@Echo *******************************************************************************
@Echo.
osql -E -i CraateTable.sql
@Echo.
@Echo.
@Echo *******************************************************************************
@Echo * *
@Echo * Database Setup Complete *
@Echo * *
@Echo *******************************************************************************
@Echo.
pause