[RESOLVED] Creat Database Programatically

[zubairkhan]

Hi Friends

I have to create an auto installer, which create database at installation time, i created sql database script file and add in vb project. Now i want to read and execute this script file, is it possible? then how?, thanks in advance.

[spyk3]

I think you first read it's content in a string and then execute CommandText command.

But this will work as long you don't have GO statement in your sql script. From your post it looks like that SqlCommand is not usefull because scripts that include DDL command are completed with batch finalizer GO command.

I think what you're looking for is somthing like this.
If you are using SQL 05 or greater you can use the SMO Lib.
I'm not 100% on this as i dont have a need to use it, but i'm pretty positive it should be somthing like this: (Doesnt use batch command and osql connection)

Imports System.Data.SqlClient
Imports System.IO
Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo
 
  Public Sub someSub()
            Dim sqlConnectionString As String = "Data Source=(local);Initial Catalog=Somthing;Integrated Security=True"
            Dim file As New FileInfo("C:\myscript.sql")
            Dim script As String = file.OpenText().ReadToEnd()
            Dim conn As New SqlConnection(sqlConnectionString)
            Dim server As New Server(New ServerConnection(conn))
 
            server.ConnectionContext.ExecuteNonQuery(script)
        End Sub

However, if you're script is of older style and has a ddl command you'll find that won't work do to GO initializer. Course I don't think you should have that issue.

[zubairkhan]

Thanks for reply,

Actually i found a solution which will create database from text file, which is located some where in hard disk, but i want to read text file which is added in project.

Thanks

[Hack]

Actually i found a solution which will create database from text file, which is located some where in hard disk, but i want to read text file which is added in project.

So is your issue resolved?

[zubairkhan]

Half part of my issued is solved, but i do need to read text file which i added in my project.

[zubairkhan]

Finally i got solution, i created a text file and added in solution resources and read from there.

[formlesstree4]

Since this is resolved, please mark the topic as resolved from the Thread Tools menu at the top of the page.