ADO.NET Cookbook

10.7. Creating a New SQL Server Database

Problem

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

Solution

Use the CREATE DATABASE statement.

The sample code executes the DDL statement—using the ExecuteNonQuery( ) method of the Command object—to create a new database named MyDatabase in SQL Server.

The C# code is shown in Example 10-7.

Example 10-7. File: CreateServerDatabaseForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Text; using System.Data; using System.Data.SqlClient; // . . . StringBuilder sb = new StringBuilder( ); // SQL DDL command text to create database. String sqlText = "CREATE DATABASE MyDatabase ON PRIMARY " + "(NAME = MyDatabase_Data, " + "FILENAME = '" + DATAFILENAME + "', " + "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " + "LOG ON (NAME = MyDatabase_Log, " + "FILENAME = '" + LOGFILENAME + "', " + "SIZE = 1MB, " + "MAXSIZE = 5MB, " + "FILEGROWTH = 10%)"; sb.Append(sqlText + Environment.NewLine + Environment.NewLine); // Create a connection. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_Master_ConnectString"]); // Create the command to create the database. SqlCommand cmd = new SqlCommand(sqlText, conn); // Create the new database. try { conn.Open( ); cmd.ExecuteNonQuery( ); sb.Append("DataBase created successfully."); } catch (System.Exception ex) { sb.Append(ex.ToString( )); } finally { if (conn.State == ConnectionState.Open) conn.Close( ); conn.Close( ); } resultTextBox.Text ...