ADO.NET 3.5 Cookbook, 2nd Edition

11.8. Creating a New SQL Server Database

Problem

You need to create a new database in your SQL Server.

Solution

Create the database using either SQL Management Objects (SMO) or using the CREATE DATABASE T-SQL data definition language (DDL) statement. The solution demonstrates both approaches.

The solution first uses SMO objects to create a new database called MySmoDatabase. Next, the solution executes a CREATE DATABASE T-SQL DDL statement to create a new database named MyDdlDatabase.

The solution needs a reference to the Microsoft.SqlServer.ConnectionInfo and Microsoft.SqlServer.Smo assemblies.

The C# code in Program.cs in the project CreateSqlServerDatabase is shown in Example 11-8.

Example 11-8. File: Program.cs for CreateSqlServerDatabase solution

using System; using System.Data; using System.Data.SqlClient; using Microsoft.SqlServer.Management.Smo; namespace CreateSqlServerDatabase { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;"; // Use SMO to create a database Console.WriteLine("---Create database using SMO---"); Server server = new Server("(local)"); Database db = new Database(server, "MySmoDatabase"); db.Create( ); Console.WriteLine("=> Database {0} created.", db.ToString( )); // DDL command text to create database, with minimal specification. string sqlText = "CREATE DATABASE MyDdlDatabase ON PRIMARY " + "(NAME = MyDdlDatabase_Data, " + "FILENAME = 'C:\\MyDdlDatabaseData.mdf') " + "LOG ON (NAME = ...