我如何在我自己的应用程序中进行如下的建库操作，而不是在SQL的查询分析中去执行产生SQL库，同时要求
1、判断数据库是否已经存在，如果存在，要求drop database onlinebankingdb
2、建库以后进行建存储过程操作
create database onlinebankingdb
use onlinebankingdbcreate table Department
(
cDepartmentID char(4) constraint pkDepartment primary key,
vDepartmentName varchar(20),
vDepartmentHead varchar(20),
vLocation varchar(200)
)
raiserror('creating Department...',0,1)create table Employee
(
cEmployeeID char(4) constraint pkEmployee primary key,
vEmployeeName varchar(20),
vEmployeeAddress varchar(200),
vEmployeePhone varchar(15) constraint ckEmployee check(vEmployeePhone like('([0-9][0-9][0-9][0-9])-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')),
cGender char(1),
dDateOfBirth datetime,
cDepartmentID char(4) constraint fkEmployee references Department(cDepartmentID),
vRole varchar(20),
vRes varchar(200)
)
raiserror('creating Employee...',0,1)create table Operator
(
cLoginID char(4) constraint pkOperator primary key,
cEmployeeID char(4) constraint fkOperator references Employee(cEmployeeID),
vPassword varchar(20),
cPermission char(1)
)
raiserror('creating Operator...',0,1)create table AccountType
(
cAccountTypeID char(4) constraint pkAccountType primary key,
vAccountTypeName varchar(20),
vDescription varchar(100),
mMinimumBalance money,
mMaximumBalance money,
fRate float,
iLength int
)
raiserror('creating AccountType...',0,1)create table ATMType
(
cATMTypeID char(4) constraint pkATMType primary key,
vATMTypeName varchar(20),
vDescription varchar(100),
iLength int
)
raiserror('creating ATMType...',0,1)create table Customer
(
cCustomerID char(10),
vCustomerName varchar(20),
vCustomerAddress varchar(200),
dDateOfBirth datetime,
vCustomerPhone varchar(15) constraint ckCustomer_CustomerPhone check(vCustomerPhone like('([0-9][0-9][0-9][0-9])-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')),
cGender char(1),
vEmail varchar(50),
vPicture varchar(200),
cIDCardNo char(15)constraint ckCustomer_IDCardNo check(cIDCardNo like('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')),
cAccountNumber char(19) constraint pkAccount primary key,
cAccountTypeID char(4) constraint fkCustomer_AccountTypeID references AccountType(cAccountTypeID),
cAccessCode char(6),
mAccountBalance money,
cATMCardNumber char(16),
cATMTypeID char(4) constraint fkCustomer_ATMTypeID references ATMType(cATMTypeID),
cATMAccessCode char(6),
dCardOpenedDate datetime,
dCardExpiryDate datetime,
vATMCardRes varchar(200),
vCheckBookNumber varchar(20),
dDateIssued datetime,
vStartNumber varchar(20),
vEndNumber varchar(20),
dCheckBookOpenedDate datetime,
dCheckBookExpiryDate datetime,
vCheckBookRes varchar(200),
vStatus varchar(10),
dAccountOpenedDate datetime,
dAccountExpiryDate datetime,
cLoginID char(4) constraint fkCustomer_LoginID references Operator(cLoginID)
)raiserror('creating Customer...',0,1)create table TransactionsOfAccount
(
cTransactionID char(10) constraint pkTransaction primary key,
cAccountNumber char(19) constraint fkTransactionsOfAccount_AccountNumber references Customer(cAccountNumber),
dTransactionDate datetime,
vTransactionType varchar(10),
vTransactionMode varchar(10),
vCheckBookNumber varchar(20),
vCheckNumber varchar(20),
cATMCardNumber char(16),
mDebitWithdrawal money,
mCreditDeposit money,
vInstructions varchar(100),
cLoginID char(4) constraint fkTransactionsOfAccount_LoginID references Operator(cLoginID)
)
raiserror('creating TransactionsOfAccount...',0,1)create table TransactionOfCheckBook
(
cTransactionID char(10) constraint pkTransactionOfCheckBook primary key,
cAccountNumber char(19) constraint fkTransactionOfCheckBook_AccountNumber references Customer(cAccountNumber),
dTransactionDate datetime,
vTransactionType varchar(10),
vTransactionMode varchar(10),
vCheckBookNumber varchar(20),
vCheckNumber varchar(20),
mAmount money,
vInstructions varchar(100),
cLoginID char(4) constraint fkTransactionOfCheckBook_LoginID references Operator(cLoginID)
)
raiserror('creating TransactionOfCheckBook...',0,1)create table TransactionOfATMCard
(
cTransactionID char(10) constraint pkTransactionOfATMCard primary key,
cAccountNumber char(19) constraint fkTransactionOfATMCard_AccountNumber references Customer(cAccountNumber),
cATMCardNumber char(16),
dTransactionDate datetime,
vTransactionMode varchar(10),
vTransactionType varchar(10),
vCheckBookNumber varchar(20),
vCheckNumber varchar(20),
mDebitWithdrawal money,
mCreditDeposit money,
vInstructions varchar(100),
cLoginID char(4) constraint fkTransactionOfATMCard_LoginID references Operator(cLoginID)
)
raiserror('creating TransactionOfATMCard...',0,1)create table Log
(
cLogID char(10) constraint pkLog primary key,
vModeNmae varchar(50),
dLoginDatetime datetime,
dLogoutDatetime datetime,
cLoginID char(4) constraint fkLog_LoginID references Operator(cLoginID)
)
raiserror('creating Log...',0,1)//////////////////////////////////////////////////////////////
create procedure prcATMTypeAutoGen @iIncrement int output
as
declare @cATMTypeID char(4)
select @cATMTypeID=isnull(max(cATMTypeID),'0000') from ATMType
select @iIncrement=convert(int,substring(@cATMTypeID,1,4))+1
return
GOcreate procedure prcDepartmentAutoGen @iIncrement int output
as
declare @cDepartmentID char(4)
select @cDepartmentID=isnull(max(cDepartmentID),'0000') from Department
select @iIncrement=convert(int,substring(@cDepartmentID,1,4))+1
return
GOCREATE procedure prcEmployeeAutoGen @iIncrement int output
as
declare @cEmployeeID char(4)
set nocount on
select @cEmployeeID=isnull(max(cEmployeeID),'0000') from Employee
set nocount off
select @iIncrement=convert(int,substring(@cEmployeeID,1,4))+1
GO
CREATE procedure prcOperatorAutoGen @iIncrement int output
as
declare @cLoginID char(4)
select @cLoginID=isnull(max(cLoginID),'0000') from Operator
select @iIncrement=convert(int,substring(@cLoginID,1,4))+1
GOCREATE procedure prcCustomerAutoGen @iIncrement int output
as
declare @cCustomerID char(10)
select @cCustomerID=isnull(max(cCustomerID),'0000000000') from Customer
select @iIncrement=convert(int,substring(@cCustomerID,1,10))+1
GOCREATE procedure prcAccountTypeAutoGen @iIncrement int output
as
declare @cAccountTypeID char(4)
select @cAccountTypeID=isnull(max(cAccountTypeID),'0000') from AccountType
select @iIncrement=convert(int,substring(@cAccountTypeID,1,4))+1
GOCREATE procedure prcTransactionsOfAccountAutoGen @iIncrement int output
as
declare @cTransactionID char(10)
select @cTransactionID=isnull(max(cTransactionID),'0000000000') from TransactionsOfAccount
select @iIncrement=convert(int,substring(@cTransactionID,1,10))+1
GOCREATE procedure prcTransactionOfCheckBookAutoGen @iIncrement int output
as
declare @cTransactionID char(10)
select @cTransactionID=isnull(max(cTransactionID),'0000000000') from TransactionOfCheckBook
select @iIncrement=convert(int,substring(@cTransactionID,1,10))+1
GOCREATE procedure prcTransactionOfATMCardAutoGen @iIncrement int output
as
declare @cTransactionID char(10)
select @cTransactionID=isnull(max(cTransactionID),'0000000000') from TransactionOfATMCard
select @iIncrement=convert(int,substring(@cTransactionID,1,10))+1
GOCREATE procedure prcLogAutoGen @iIncrement int output
as
declare @cLogID char(10)
select @cLogID=isnull(max(cLogID),'0000000000') from Log
select @iIncrement=convert(int,substring(@cLogID,1,10))+1
GO
1、判断数据库是否已经存在，如果存在，要求drop database onlinebankingdb
2、建库以后进行建存储过程操作
create database onlinebankingdb
use onlinebankingdbcreate table Department
(
cDepartmentID char(4) constraint pkDepartment primary key,
vDepartmentName varchar(20),
vDepartmentHead varchar(20),
vLocation varchar(200)
)
raiserror('creating Department...',0,1)create table Employee
(
cEmployeeID char(4) constraint pkEmployee primary key,
vEmployeeName varchar(20),
vEmployeeAddress varchar(200),
vEmployeePhone varchar(15) constraint ckEmployee check(vEmployeePhone like('([0-9][0-9][0-9][0-9])-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')),
cGender char(1),
dDateOfBirth datetime,
cDepartmentID char(4) constraint fkEmployee references Department(cDepartmentID),
vRole varchar(20),
vRes varchar(200)
)
raiserror('creating Employee...',0,1)create table Operator
(
cLoginID char(4) constraint pkOperator primary key,
cEmployeeID char(4) constraint fkOperator references Employee(cEmployeeID),
vPassword varchar(20),
cPermission char(1)
)
raiserror('creating Operator...',0,1)create table AccountType
(
cAccountTypeID char(4) constraint pkAccountType primary key,
vAccountTypeName varchar(20),
vDescription varchar(100),
mMinimumBalance money,
mMaximumBalance money,
fRate float,
iLength int
)
raiserror('creating AccountType...',0,1)create table ATMType
(
cATMTypeID char(4) constraint pkATMType primary key,
vATMTypeName varchar(20),
vDescription varchar(100),
iLength int
)
raiserror('creating ATMType...',0,1)create table Customer
(
cCustomerID char(10),
vCustomerName varchar(20),
vCustomerAddress varchar(200),
dDateOfBirth datetime,
vCustomerPhone varchar(15) constraint ckCustomer_CustomerPhone check(vCustomerPhone like('([0-9][0-9][0-9][0-9])-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')),
cGender char(1),
vEmail varchar(50),
vPicture varchar(200),
cIDCardNo char(15)constraint ckCustomer_IDCardNo check(cIDCardNo like('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')),
cAccountNumber char(19) constraint pkAccount primary key,
cAccountTypeID char(4) constraint fkCustomer_AccountTypeID references AccountType(cAccountTypeID),
cAccessCode char(6),
mAccountBalance money,
cATMCardNumber char(16),
cATMTypeID char(4) constraint fkCustomer_ATMTypeID references ATMType(cATMTypeID),
cATMAccessCode char(6),
dCardOpenedDate datetime,
dCardExpiryDate datetime,
vATMCardRes varchar(200),
vCheckBookNumber varchar(20),
dDateIssued datetime,
vStartNumber varchar(20),
vEndNumber varchar(20),
dCheckBookOpenedDate datetime,
dCheckBookExpiryDate datetime,
vCheckBookRes varchar(200),
vStatus varchar(10),
dAccountOpenedDate datetime,
dAccountExpiryDate datetime,
cLoginID char(4) constraint fkCustomer_LoginID references Operator(cLoginID)
)raiserror('creating Customer...',0,1)create table TransactionsOfAccount
(
cTransactionID char(10) constraint pkTransaction primary key,
cAccountNumber char(19) constraint fkTransactionsOfAccount_AccountNumber references Customer(cAccountNumber),
dTransactionDate datetime,
vTransactionType varchar(10),
vTransactionMode varchar(10),
vCheckBookNumber varchar(20),
vCheckNumber varchar(20),
cATMCardNumber char(16),
mDebitWithdrawal money,
mCreditDeposit money,
vInstructions varchar(100),
cLoginID char(4) constraint fkTransactionsOfAccount_LoginID references Operator(cLoginID)
)
raiserror('creating TransactionsOfAccount...',0,1)create table TransactionOfCheckBook
(
cTransactionID char(10) constraint pkTransactionOfCheckBook primary key,
cAccountNumber char(19) constraint fkTransactionOfCheckBook_AccountNumber references Customer(cAccountNumber),
dTransactionDate datetime,
vTransactionType varchar(10),
vTransactionMode varchar(10),
vCheckBookNumber varchar(20),
vCheckNumber varchar(20),
mAmount money,
vInstructions varchar(100),
cLoginID char(4) constraint fkTransactionOfCheckBook_LoginID references Operator(cLoginID)
)
raiserror('creating TransactionOfCheckBook...',0,1)create table TransactionOfATMCard
(
cTransactionID char(10) constraint pkTransactionOfATMCard primary key,
cAccountNumber char(19) constraint fkTransactionOfATMCard_AccountNumber references Customer(cAccountNumber),
cATMCardNumber char(16),
dTransactionDate datetime,
vTransactionMode varchar(10),
vTransactionType varchar(10),
vCheckBookNumber varchar(20),
vCheckNumber varchar(20),
mDebitWithdrawal money,
mCreditDeposit money,
vInstructions varchar(100),
cLoginID char(4) constraint fkTransactionOfATMCard_LoginID references Operator(cLoginID)
)
raiserror('creating TransactionOfATMCard...',0,1)create table Log
(
cLogID char(10) constraint pkLog primary key,
vModeNmae varchar(50),
dLoginDatetime datetime,
dLogoutDatetime datetime,
cLoginID char(4) constraint fkLog_LoginID references Operator(cLoginID)
)
raiserror('creating Log...',0,1)//////////////////////////////////////////////////////////////
create procedure prcATMTypeAutoGen @iIncrement int output
as
declare @cATMTypeID char(4)
select @cATMTypeID=isnull(max(cATMTypeID),'0000') from ATMType
select @iIncrement=convert(int,substring(@cATMTypeID,1,4))+1
return
GOcreate procedure prcDepartmentAutoGen @iIncrement int output
as
declare @cDepartmentID char(4)
select @cDepartmentID=isnull(max(cDepartmentID),'0000') from Department
select @iIncrement=convert(int,substring(@cDepartmentID,1,4))+1
return
GOCREATE procedure prcEmployeeAutoGen @iIncrement int output
as
declare @cEmployeeID char(4)
set nocount on
select @cEmployeeID=isnull(max(cEmployeeID),'0000') from Employee
set nocount off
select @iIncrement=convert(int,substring(@cEmployeeID,1,4))+1
GO
CREATE procedure prcOperatorAutoGen @iIncrement int output
as
declare @cLoginID char(4)
select @cLoginID=isnull(max(cLoginID),'0000') from Operator
select @iIncrement=convert(int,substring(@cLoginID,1,4))+1
GOCREATE procedure prcCustomerAutoGen @iIncrement int output
as
declare @cCustomerID char(10)
select @cCustomerID=isnull(max(cCustomerID),'0000000000') from Customer
select @iIncrement=convert(int,substring(@cCustomerID,1,10))+1
GOCREATE procedure prcAccountTypeAutoGen @iIncrement int output
as
declare @cAccountTypeID char(4)
select @cAccountTypeID=isnull(max(cAccountTypeID),'0000') from AccountType
select @iIncrement=convert(int,substring(@cAccountTypeID,1,4))+1
GOCREATE procedure prcTransactionsOfAccountAutoGen @iIncrement int output
as
declare @cTransactionID char(10)
select @cTransactionID=isnull(max(cTransactionID),'0000000000') from TransactionsOfAccount
select @iIncrement=convert(int,substring(@cTransactionID,1,10))+1
GOCREATE procedure prcTransactionOfCheckBookAutoGen @iIncrement int output
as
declare @cTransactionID char(10)
select @cTransactionID=isnull(max(cTransactionID),'0000000000') from TransactionOfCheckBook
select @iIncrement=convert(int,substring(@cTransactionID,1,10))+1
GOCREATE procedure prcTransactionOfATMCardAutoGen @iIncrement int output
as
declare @cTransactionID char(10)
select @cTransactionID=isnull(max(cTransactionID),'0000000000') from TransactionOfATMCard
select @iIncrement=convert(int,substring(@cTransactionID,1,10))+1
GOCREATE procedure prcLogAutoGen @iIncrement int output
as
declare @cLogID char(10)
select @cLogID=isnull(max(cLogID),'0000000000') from Log
select @iIncrement=convert(int,substring(@cLogID,1,10))+1
GO
存储过程在程序里写,没听说过,如果在程序里写存储过程,那就不叫存储过程了.
（在建立了数据库后可以直接导出为脚本的）用vb的话你可以这样Private Sub CreateDataBase(cnDataBase as connect,sqlFile As String)
Dim strSql As String, strTmp As String
Open sqlFile For Input As #1
strSql = ""
Do While Not EOF(1)
Line Input #1, strTmp
If UCase(strTmp) = "GO" Then
cn.Execute strSql
strSql = ""
Else
strSql = strSql & strTmp & vbCrLf
End If
Loop
If strSql <> "" Then cnDataBase.Execute strSql
Close #1
End Sub其他的你可以定义一个到服务器的ado连接
然后可以这样：
比如：vb中
dim cn as New ADODB.Connection
dim sql as string cn.open ".......到服务器的连接"
sql="master.dbo.xp_cmdshell ' osql -U username -P password -i c:\test.sql'"
cn.execute sql
drop database onlinebankingdb
Dim createTableScript(1 To 10) As String
Dim createProcedure(1 To 10) As StringPrivate Sub MDIForm_Load()
createTableScript(1) = "create table Department " & _
"( " & _
"cDepartmentID char(4) constraint pkDepartment primary key, " & _
"vDepartmentName varchar(20), " & _
"vDepartmentHead varchar(20), " & _
"vLocation VarChar(200) " & _
")"createTableScript(2) = "create table Employee" & _
"(" & _
"cEmployeeID char(4) constraint pkEmployee primary key," & _
"vEmployeeName varchar(20)," & _
"vEmployeeAddress varchar(200)," & _
"vEmployeePhone varchar(15) constraint ckEmployee check(vEmployeePhone like('([0-9][0-9][0-9][0-9])-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))," & _
"cGender char(1)," & _
"dDateOfBirth datetime," & _
"cDepartmentID char(4) constraint fkEmployee references Department(cDepartmentID)," & _
"vRole varchar(20)," & _
"vRes VarChar(200)" & _
")"createTableScript(3) = "create table Operator" & _
"(" & _
"cLoginID char(4) constraint pkOperator primary key," & _
"cEmployeeID char(4) constraint fkOperator references Employee(cEmployeeID)," & _
"vPassword varchar(20)," & _
"cPermission Char(1)" & _
")"createTableScript(4) = "create table AccountType" & _
"(" & _
"cAccountTypeID char(4) constraint pkAccountType primary key," & _
"vAccountTypeName varchar(20)," & _
"vDescription varchar(100)," & _
"mMinimumBalance money," & _
"mMaximumBalance money," & _
"fRate float," & _
"iLength int" & _
")"createTableScript(5) = "create table ATMType" & _
"(" & _
"cATMTypeID char(4) constraint pkATMType primary key," & _
"vATMTypeName varchar(20)," & _
"vDescription varchar(100)," & _
"iLength int" & _
")"createTableScript(6) = "create table Customer" & _
"(" & _
"cCustomerID char(10),vCustomerName varchar(20),vCustomerAddress varchar(200)," & _
"dDateOfBirth datetime,vCustomerPhone varchar(15) constraint ckCustomer_CustomerPhone check(vCustomerPhone like('([0-9][0-9][0-9][0-9])-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))," & _
"cGender char(1),vEmail varchar(50),vPicture varchar(200),cIDCardNo char(15)constraint ckCustomer_IDCardNo check(cIDCardNo like('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))," & _
"cAccountNumber char(19) constraint pkAccount primary key,cAccountTypeID char(4) constraint fkCustomer_AccountTypeID references AccountType(cAccountTypeID)," & _
"cAccessCode char(6),mAccountBalance money,cATMCardNumber char(16)," & _
"cATMTypeID char(4) constraint fkCustomer_ATMTypeID references ATMType(cATMTypeID)," & _
"cATMAccessCode char(6),dCardOpenedDate datetime,dCardExpiryDate datetime," & _
"vATMCardRes varchar(200),vCheckBookNumber varchar(20),dDateIssued datetime," & _
"vStartNumber varchar(20),vEndNumber varchar(20),dCheckBookOpenedDate datetime," & _
"dCheckBookExpiryDate datetime,vCheckBookRes varchar(200),vStatus varchar(10)," & _
"dAccountOpenedDate datetime,dAccountExpiryDate datetime," & _
"cLoginID char(4) constraint fkCustomer_LoginID references Operator(cLoginID)" & _
")"createTableScript(7) = "create table TransactionsOfAccount" & _
"(" & _
"cTransactionID char(10) constraint pkTransaction primary key," & _
"cAccountNumber char(19) constraint fkTransactionsOfAccount_AccountNumber references Customer(cAccountNumber)," & _
"dTransactionDate datetime," & _
"vTransactionType varchar(10)," & _
"vTransactionMode varchar(10)," & _
"vCheckBookNumber varchar(20)," & _
"vCheckNumber varchar(20)," & _
"cATMCardNumber char(16)," & _
"mDebitWithdrawal money," & _
"mCreditDeposit money," & _
"vInstructions varchar(100)," & _
"cLoginID char(4) constraint fkTransactionsOfAccount_LoginID references Operator(cLoginID)" & _
")"createTableScript(8) = "create table TransactionOfCheckBook" & _
"(" & _
"cTransactionID char(10) constraint pkTransactionOfCheckBook primary key," & _
"cAccountNumber char(19) constraint fkTransactionOfCheckBook_AccountNumber references Customer(cAccountNumber)," & _
"dTransactionDate datetime," & _
"vTransactionType varchar(10)," & _
"vTransactionMode varchar(10)," & _
"vCheckBookNumber varchar(20)," & _
"vCheckNumber varchar(20)," & _
"mAmount money," & _
"vInstructions varchar(100)," & _
"cLoginID char(4) constraint fkTransactionOfCheckBook_LoginID references Operator(cLoginID)" & _
")"createTableScript(9) = "create table TransactionOfATMCard" & _
"(" & _
"cTransactionID char(10) constraint pkTransactionOfATMCard primary key," & _
"cAccountNumber char(19) constraint fkTransactionOfATMCard_AccountNumber references Customer(cAccountNumber)," & _
"cATMCardNumber char(16)," & _
"dTransactionDate datetime," & _
"vTransactionMode varchar(10)," & _
"vTransactionType varchar(10)," & _
"vCheckBookNumber varchar(20)," & _
"vCheckNumber varchar(20)," & _
"mDebitWithdrawal money," & _
"mCreditDeposit money," & _
"vInstructions varchar(100)," & _
"cLoginID char(4) constraint fkTransactionOfATMCard_LoginID references Operator(cLoginID)" & _
")"createTableScript(10) = "create table Log" & _
"(" & _
"cLogID char(10) constraint pkLog primary key," & _
"vModeNmae varchar(50)," & _
"dLoginDatetime datetime," & _
"dLogoutDatetime datetime," & _
"cLoginID char(4) constraint fkLog_LoginID references Operator(cLoginID)" & _
")"
"as" & Chr(13) & _
"declare @cATMTypeID char(4)" & Chr(13) & _
"select @cATMTypeID=isnull(max(cATMTypeID),'0000') from ATMType" & Chr(13) & _
"select @iIncrement=convert(int,substring(@cATMTypeID,1,4))+1"createProcedure(2) = "create procedure prcDepartmentAutoGen @iIncrement int output" & _
" as" & _
" declare @cDepartmentID char(4)" & _
" select @cDepartmentID=isnull(max(cDepartmentID),'0000') from Department" & _
" select @iIncrement=convert(int,substring(@cDepartmentID,1,4))+1"createProcedure(3) = "CREATE procedure prcEmployeeAutoGen @iIncrement int output" & _
" as" & _
" declare @cEmployeeID char(4)" & _
" set nocount on" & _
" select @cEmployeeID=isnull(max(cEmployeeID),'0000') from Employee" & _
" set nocount off" & _
" select @iIncrement=convert(int,substring(@cEmployeeID,1,4))+1"createProcedure(4) = "CREATE procedure prcOperatorAutoGen @iIncrement int output" & _
" as" & _
" declare @cLoginID char(4)" & _
" select @cLoginID=isnull(max(cLoginID),'0000') from Operator" & _
" select @iIncrement=convert(int,substring(@cLoginID,1,4))+1"createProcedure(5) = "CREATE procedure prcCustomerAutoGen @iIncrement int output" & _
" as" & _
" declare @cCustomerID char(10)" & _
" select @cCustomerID=isnull(max(cCustomerID),'0000000000') from Customer" & _
" select @iIncrement=convert(int,substring(@cCustomerID,1,10))+1"createProcedure(6) = "CREATE procedure prcAccountTypeAutoGen @iIncrement int output" & _
" as" & _
" declare @cAccountTypeID char(4)" & _
" select @cAccountTypeID=isnull(max(cAccountTypeID),'0000') from AccountType" & _
" select @iIncrement=convert(int,substring(@cAccountTypeID,1,4))+1"createProcedure(7) = "CREATE procedure prcTransactionsOfAccountAutoGen @iIncrement int output" & _
" as" & _
" declare @cTransactionID char(10)" & _
" select @cTransactionID=isnull(max(cTransactionID),'0000000000') from TransactionsOfAccount" & _
" select @iIncrement=convert(int,substring(@cTransactionID,1,10))+1"createProcedure(8) = "CREATE procedure prcTransactionOfCheckBookAutoGen @iIncrement int output" & _
" as" & _
" declare @cTransactionID char(10)" & _
" select @cTransactionID=isnull(max(cTransactionID),'0000000000') from TransactionOfCheckBook" & _
" select @iIncrement=convert(int,substring(@cTransactionID,1,10))+1"createProcedure(9) = "CREATE procedure prcTransactionOfATMCardAutoGen @iIncrement int output" & _
" as" & _
" declare @cTransactionID char(10)" & _
" select @cTransactionID=isnull(max(cTransactionID),'0000000000') from TransactionOfATMCard" & _
" select @iIncrement=convert(int,substring(@cTransactionID,1,10))+1"createProcedure(10) = "CREATE procedure prcLogAutoGen @iIncrement int output" & _
" as" & _
" declare @cLogID char(10)" & _
" select @cLogID=isnull(max(cLogID),'0000000000') from Log" & _
" select @iIncrement=convert(int,substring(@cLogID,1,10))+1"Call createDatabaseEnd Sub
Private Sub createDatabase()
Dim i As Integer
Dim cn As ADODB.Connection
Call getSQLServerName
Dim strCreateDatabae, strDropDatabase As String
strDropDatabase = "if exists (select 1 from sysdatabases where name='onlinebankingdb')" & _
"drop database onlinebankingdb"
strCreateDatabae = "CREATE DATABASE onlinebankingdb " & _
"ON (NAME = onlinebankingdb_dat," & _
"FILENAME = '" & App.Path & "\onlinebankingdb.mdf'," & _
"SIZE = 1MB,FILEGROWTH = 10% )" & _
"LOG ON (NAME = 'onlinebankingdb_log'," & _
"FILENAME = '" & App.Path & "\onlinebankingdblog.ldf'," & _
"SIZE = 1MB,FILEGROWTH = 10% )"
Set cn = New ADODB.Connection
With cn
.ConnectionString = "Provider=SQLOLEDB;Data Source=" & strServerName & ";User Id=sa;Password=;Initial Catalog=Master"
.Open
.Execute strDropDatabase
.Execute strCreateDatabae
.Execute "use onlinebankingdb"
For i = 1 To 10
.Execute createTableScript(i)
Next
For i = 1 To 10
.Execute createProcedure(i)
Next
End With
Set cn = Nothing
End Sub