在VB中更改SQL Server数据库结构

ywsxr

笔者在开发"凉山州林业局"天然林资源保护综合管理系统中，需要为程序建立Sql Server数据库的运行环境。为了方便用户，笔者开发了这个数据库配置工具。完成在SQL Server数据库中建立设备，建立数据库，建立表格，分配权限的功能，"凉山州林业局"系统中的所有数据库配置操作都可以通过这个小工具完成。方便了数据库应用程序所需Sql Server环境的建立，根本不用启动SQL Enterprise Manager配置数据库。 　　这个小工具由VB开发，利用ADO 访问数据库，实现更改数据库结构，其他语言也可以此作为参考。启动VB6.0，新建一个工程,在菜单-工程-引用里选"Microsoft ActiveX Data Objects 2.0 Library",代码里需要有

dim conn As New ADODB.Connection 定义ADO数据库对象 conn.C & _ "server=" & ServerName & ";uid=" & UserName & "; pwd=" & Password & ";database=" & DatabaseName & "" 连接数据串 conn.open 连接数据库

　　注：ServerName为服务器名；UserName为用户名；Password为用户口令； DatabaseName要登录的数据库名，可以为空。

　　核心代码如下：
　　一、建立数据库
　　原理：建立数据库先要初始化一个数据库设备，然后在此设备上建立数据库。所有的设备名在系统表"sysdevices"里有记录，所有的数据库名在系统表"sysdatabases"里有记录。在建立之前，最好先查询这两个系统表，看名称是否已经存在。在建立设备之前，还需要的一个物理名和空闲的设备标识号。
初始化设备语法：

DISK INIT NAME="device_name",PHYNAME= "physical_name",VDEVNO=device_number, SIZE=numberofblock

　　说明：这里，NAME是数据库设备名（一个有效的标识符），PHYNAME(数据库设备的物理名)是原始的磁盘分区UNIX或外设（vms）名，或者是操作系统的文件名。VDEVNO时数据库的设备标识号，合法值为1-255，SIZE的单位是2KB的块，例如1MB（1024KB）时SIZE值为512。

　　建立数据库语法：CREATE DATABASE database_name [ON database_device]
　　说明：database_name是要建的数据库名，database_device是设备名
　　要新建立一个数据库，就需要设备名，数据库名，物理名和设备号。具体步骤如下：
　　我们假设用户要新建立设备dbName，在设备dbName上建立数据库dbName。
　　1）得到设备名。dbName是用户给出的设备名；先查询系统表sysdevices，看用户给出的设备名dbName是否已经存在，如果此设备名存在，就需要更换一个设备名，因为设备名是唯一的。

sql = "select * from sysdevices where name=" & dbName & "" Set rs = conn.Execute(sql) If Not rs.EOF Then MsgBox "设备名"" & dbName & "" 已存在！", 16, "请重新输入名称" Exit Sub End If

ywsxr

2）得到数据库名。dbName是用户给出的数据库名；查询系统表sysdatabases，看用户给出的数据库名dbName是否已经存在，如果此数据库存在，就需要更换一个数据库名，像设备名一样，数据库名也是唯一的


sql = "select * from sysdatabases
where name=" & dbName & ""
Set rs = conn.Execute(sql) 下面代码略

　　3）得到PHYNAME物理名。查询服务器上数据库文件的物理位置serverpath，典型的，我们可以从系统表sysdevices中查询master(这是SQL Server的主库名)数据库的位置，例如G:MSSQLDATAMASTER.DAT，则我们的数据库可以建在"G:MSSQLDATA"目录下。


sql = "select name,phyname from sysdevices "
low/16777216为设备号
Set rs = conn.Execute(sql)  

　　然后遍历记录对象rs，当name="master"时，取出phyname，从而可以得到物理位置serverpath =G:MSSQLDATA。

　　4)得到一个空闲的设备号vdevno。设备号合法值1~255，遍历这些号，查找出未被使用的空闲设备号,下面程序得到已有的设备号


sql = "select distinct low/16777216
from sysdevices order by low/16777216"
low/16777216为设备号

　　5）建立数据库。所需的信息都准备完毕，可以建立数据库了（注：下面的"" & Chr(34) & ""就是一个"""双引号,这样处理后，才能满足语法要求;数据库为20M，则dbSize=512*20）



sql = "DISK INIT NAME=" & Chr(34) & ""
& dbName & "" & Chr(34) & ",PHYSNAME="
& Chr(34) & "" & serverpath & "" & dbName
& ".dat" & Chr(34) & ",VDEVNO=" & vdevno
& ",SIZE=" & dbSize & ""
Set rs = conn.Execute(sql) 初始化设备
sql = "CREATE DATABASE " & dbName & "
on " & dbName & "=" & dbSize & ""


注：

　　第一个dbName是数据库名，第二个dbName是设备名Set rs = conn.Execute(sql)

　　在设备dbName上建立数据库dbName

　　 MsgBox "数据库"" & dbName & ""建在服务器上"" & serverpath & "" & dbName & ".dat"，建立成功！", 64, "成功"

ywsxr

二、建立表格

　　建立表格比较简单，这里用到了自动计数字段和缺省值字段类型，语法如下：



CREATE TABLE table_name
(field_name data_type [NOT NULL|NULL],…)


　　说明：table_name为新建的表名，field_name为字段名，data_type为数据类型。

（注意下面的fileid int IDENTITY字段自动计数，datetime NOT NULL DEFAULT(GETDATE())字段每当入库时有个缺省值，由数据库生成当时的时间）。



sql = "CREATE TABLE " & TableName & "
(fileid int IDENTITY, filetime datetime NOT
NULL DEFAULT(GETDATE()),fileimage image NULL )"
conn.Execute sql 建立表格

　　三、建立用户组用户

　　建立用户组和用户不能直接通过SQL语句完成，需要执行SQL Server的存储过程sp_addlogin,sp_addgroup, sp_adduser。我们假设新建登录账号是username1,用户名是username1,组名是group1，则步骤如下：

　　1）建立用户的登录账号

　　语法：sp_addlogin login_name,password[,defdb]

　　其中，login_name是用户的登录名，password是用户的口令，defdb上登录的缺省数据库名称。建立数据库DatabaseName的登录账号：



sql = "EXECUTE sp_addlogin " & username1 & ","
& password1 & "," & DatabaseName & ""
Set rs = conn.Execute(sql)

　　2）增加用户组

　　语法：sp_addgroup group_name
　　其中，group_name 是新建组名sql = "EXECUTE sp_addgroup " & group1 & "" Set rs = conn.Execute(sql)

ywsxr

3）增加用户

语法：


sp_adduser login_name
[,name_in_db[,grpname]]

　　渲校琹ogin_name用户名，name_in_db是用户在当前数据库中的名字（这里是第一步建立的登录账号
username1）,grpname是要将用户加入的那个组的组名。

　　在数据库DatabaseName增加用户username1：



sql = "EXECUTE sp_adduser " & username1 & "," &
username1 & "," & group1& ""

　　注：第一个username1是用户名，第二个username1是数据库DatabaseName的登录账号Set rs = conn.Execute(sql)

　　四、分配权限

　　语法：grant permission_list on object_name to who

　　其中，permission_list是所要分配的权限清单，object_name是在这个对象上的权限，who是接受授权的用户。

　　凉山州林业局"系统需要将特殊用户建立的表授权给其他用户，所以先从系统表sysobjects得到所有的用户建立表格名（type=U）

sql = "select name from sysobjects where type=U"
Set rs = conn.Execute(sql)

　　然后从中选取所需要的表格来分配权限给其他用户。例如，这里选择将tablename3的读取权限分配给组group1。


sql = "grant select on " & tablename3 &
" to " & group1 & ""
conn.Execute sql  

　　由于这个小工具的使用，使SQL Server数据库配置变得简单、方便了。

xrdg123

路过
学习
顶过  
感谢

bingyunxq

够详细，看了，学习了，也学会了！

box_ren

很实用的编程技术啊，收藏了

hidon

真高深，须要学习VB

sky82

不错,,呵呵,学习学习.......

wum

VB编程知识看来我还得好好学习一下。谢谢楼主给我的启示