Creating a New SQL Server Database

[ Team LiB ] Recipe 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( )
Nội dung trích xuất từ tài liệu:
Creating a New SQL Server Database [ Team LiB ]Recipe 10.7 Creating a New SQL Server DatabaseProblemYou need to create a new database in your SQL Server.SolutionUse the CREATE DATABASE statement.The sample code executes the DDL statement—using the ExecuteNonQuery( ) method ofthe 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 constantsusing 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 = sb.ToString( );DiscussionThere are two categories of SQL statements:Database Definition Language (DDL) Used to manage all objects in the database, generally with CREATE, ALTER, and DROP statements to create, modify, and delete objects, respectively. These statements generally require DBA permissions to execute.Database Management Language (DML) Used to manipulate—select, insert, update, and delete—data in the database objects. Database objects are defined using DDL.The solution executes a DDL CREATE DATABASE statement to create a new databaseon a SQL Server.You can programmatically drop the database by using the DROP DATABASE statementin a similar way. To drop the database created in the previous example, use the followingcode:DROP DATABASE MyDatabaseThe DROP DATABASE statement will fail if the database is in use; therefore, it mightbe necessary to restart the SQL Server in order to drop the database. System databases—master, model, msdb, and tempdb—cannot be dropped.For more information about the CREATE DATABASE statement, the DROPDATABASE statement, or DDL, see Microsoft SQL Server Books Online.The solution for Oracle databases and other databases is similar to that shown for SQLServer although the DDL syntax for each database varies slightly because of differencesin database server capabilities and architecture. For more information about Oracle SQLsyntax, see Oracle in a Nutshell (OReilly).[ Team LiB ]