I'm new to VB.net !!
My question is : How do I create a new database using sql script generated by Sql Server 2000 using vb.net code ?
Printable View
I'm new to VB.net !!
My question is : How do I create a new database using sql script generated by Sql Server 2000 using vb.net code ?
Try this:
'SET THE CONNECTION OBJECT
Dim _connectionstring as string = "server=SERVERNAME;database=DATABASENAME;uid=USERID;pwd=PASSWORD"
'SET THE SQL QUERY
Dim Sql as string = "CREATE DATABASE DATABASENAME"
'CREATE THE CONNECTION OBJECT
Dim dbConn As New SqlClient.SqlConnection(_connectionstring)
'CREATE THE COMMAND OBJECT
Dim Command As New SqlClient.SqlCommand(Sql, dbConn)
Command.CommandTimeout = 120
Command.Connection.Open()
Command.ExecuteNonQuery()
I find that an interesting approach since, there isn't a DB to connect to.....Which means you'd have to connect to the master db....for which the user had better have access to....
That said.... I know you could also use SQLDMO (which is a COM component) to do it via code OR SQL Script.
You can use the process class and run the script from the command line.
what I'm looking for is something to create database and its tables,procedures,users...
using the script generated by SQL Server.
similar to the line command "osql".
Sorry about that. Try this code. It executes scripts through osql.
Dim proc As New Process()
Dim sCmdLine As String
Dim sDatabase as String = "master"
Dim _password as String = "put your password here"
Dim _server as String = "Put Server Name here"
Dim sFileName as String = "Put Name of .sql file here"
Dim _path as String = "Put location of the file here"
proc.StartInfo.FileName = "osql"
sCmdLine += " -Usa "
sCmdLine += "-d" & sDatabase & " "
sCmdLine += "-P" & _password & " "
sCmdLine += "-S" & _server & " "
sCmdLine += "-i" & sFileName
proc.StartInfo.WorkingDirectory = _path
proc.StartInfo.WindowStyle = ProcessWindowStyle.Minimized
proc.StartInfo.Arguments = sCmdLine
proc.StartInfo.UseShellExecute = True
proc.Start()
proc.WaitForExit()
If (Not proc.HasExited) Then
proc.Kill()
End If
Thanks bontyboy !!!