How can I use a dynamic database name in SQL for my vb.net app?

Question

I need to run the below script from my vb.net app & would like to know how would I make the database name (mynewdatabase_db) a parameter?

Basically I need to run this script on certain databases so I need the database name to be dynamic.

    Dim MySqlQuery2 As String = "USE mynewdatabase_db " & _
        "TRUNCATE TABLE MyTable " & _
        " " & _
        "INSERT INTO MyTable ([MyNumber], [HTTPAlias]) VALUES (0, @MyDomainAlias1) " & _
        "INSERT INTO MyTable ([MyNumber], [HTTPAlias]) VALUES (0, @MyDomainAlias2) " & _
        "INSERT INTO MyTable ([MyNumber], [HTTPAlias]) VALUES (0, @MyDomainAlias3) "

    Using cmd2 = New SqlCommand(MySqlQuery2.ToString, myConnection)
        cmd2.Parameters.AddWithValue("@MyDomainAlias1", MyDomainName)
        cmd2.Parameters.AddWithValue("@MyDomainAlias2", "www." & MyDomainName)
        cmd2.Parameters.AddWithValue("@MyDomainAlias3", MyDomainName & "." & MyTempDomainName)
        cmd2.ExecuteNonQuery()

    End Using

Thanks in advance ;)

Daniel A. White · Accepted Answer · 2012-08-05 20:32:34Z

1

I would make it part of your connection string(s). That way it wouldn't be hard coded into your application.

answered Aug 5, 2012 at 20:32

Daniel A. White

192k49 gold badges389 silver badges474 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MWD Over a year ago

That did the trick - thanks. I did my first sql command, closed the database connection then opened up a new database connection & did my second sql command.. Thanks for your help :)

Steve · Accepted Answer · 2012-08-05 22:05:30Z

1

There is a very usefull class named SqlConnectionStringBuilder

Supposing you have a initial connection string stored in your app.config or web.config.
Then you could write a method like this, which load your stored connection string and
change the Initial Catalog to a new one passed as parameter

Public Function GetNewConnectionString(newDataName as String) as String
    Dim cnnString as String = ConfigurationManager.ConnectionStrings["YourConnName"].ConnectionString
    Dim builder As SqlConnectionStringBuilder = new SqlConnectionStringBuilder(cnnString);
    builder.InitialCatalog = newDataName
    return builder.ConnectionString
End Function

You could change your connection string calling this method and then executing the query above dropping the part that try to use a different database.

Using myConnection = new SqlConnection(GetNewConnectionString("mynewdatabase-db"))
Using cmd2 = New SqlCommand(MySqlQuery2.ToString, myConnection)     
    cmd2.Parameters.AddWithValue("@MyDomainAlias1", MyDomainName)     
    cmd2.Parameters.AddWithValue("@MyDomainAlias2", "www." & MyDomainName)     
    cmd2.Parameters.AddWithValue("@MyDomainAlias3", MyDomainName & "." & MyTempDomainName)     
    cmd2.ExecuteNonQuery()     
End Using
End Using

edited Aug 5, 2012 at 22:05

answered Aug 5, 2012 at 20:57

Steve

217k22 gold badges242 silver badges296 bronze badges

2 Comments

MWD Over a year ago

Thanks for the info - I appreciate it! FYI: I didn't delete the table in my query, I deleted it's contents.

Steve Over a year ago

Oh yes a bit confused. Time to go to bed.

Collectives™ on Stack Overflow

How can I use a dynamic database name in SQL for my vb.net app?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related