This article describes how to use ADO.NET and Visual Basic .NET to programmatically create a Microsoft SQL Server database.
Original product version: ADO.NET, Visual Basic .NET
Original KB number: 305079
Summary
Programmers often need to create databases programmatically. This article describes how to use ADO.NET and Visual Basic .NET to programmatically create a SQL Server database.
Steps to create the sample
-
Create a new Visual Basic .NET Windows Application project. Form1 is added to the project by default.
-
Place a Command button on Form1, and change its
Nameproperty tobtnCreateDatabaseand itsTextproperty toCreate Database. -
Copy and paste the following line of code into Form1's "general declaration" section:
Imports System.Data.SqlClient -
Copy and paste the following code after the region "Windows Form Designer generated code":
Private Sub btnCreateDatabase_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnCreateDatabase.Click Dim str As String Dim myConn As SqlConnection = New SqlConnection("Server=(local)\netsdk;" & _ "uid=sa;pwd=;database=master") str = "CREATE DATABASE MyDatabase ON PRIMARY " & _ "(NAME = MyDatabase_Data, " & _ " FILENAME = 'D:\MyFolder\MyDatabaseData.mdf', " & _ " SIZE = 2MB, " & _ " MAXSIZE = 10MB, " & _ " FILEGROWTH = 10%)" & _ " LOG ON " & _ "(NAME = MyDatabase_Log, " & _ " FILENAME = 'D:\MyFolder\MyDatabaseLog.ldf', " & _ " SIZE = 1MB, " & _ " MAXSIZE = 5MB, " & _ " FILEGROWTH = 10%)" Dim myCommand As SqlCommand = New SqlCommand(str, myConn) Try myConn.Open() myCommand.ExecuteNonQuery() MessageBox.Show("Database is created successfully", _ "MyProgram", MessageBoxButtons.OK, _ MessageBoxIcon.Information) Catch ex As Exception MessageBox.Show(ex.ToString()) Finally If (myConn.State = ConnectionState.Open) Then myConn.Close() End If End Try End Sub -
Change the connection string to point to your SQL Server, and make sure that the Database argument is set to Master or blank.
-
Press F5 or CTRL+F5 to run the project, and then click Create Database.
Additional notes
-
This code creates a custom database with specific properties.
-
The folder that will hold the created .mdf and .ldf files must already exist before you run the code or an exception will be generated.
-
If you want to create a database that is similar to SQL Server's Model database and in the default location, then change the str variable in the code:
str = "CREATE DATABASE MyDatabase"
References
-
How To Create a SQL Server Database Programmatically by Using ADO.NET and Visual C++ .NET
-
How To Create a SQL Server Database Programmatically by Using ADO.NET and Visual C# .NET
Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.
