Csharp/C Sharp/Database ADO.net/Create table
Create table through SqlConnection
<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient;
class CommandExampleCreateDb { static void Main() { SqlConnection thisConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"); SqlCommand nonqueryCommand = thisConnection.CreateCommand(); try { thisConnection.Open(); nonqueryCommand.rumandText = "CREATE DATABASE MyDb"; Console.WriteLine(nonqueryCommand.rumandText); nonqueryCommand.ExecuteNonQuery(); Console.WriteLine("Database created, now switching"); thisConnection.ChangeDatabase("MyDb"); nonqueryCommand.rumandText = "CREATE TABLE MynfexTable (COL1 integer)"; Console.WriteLine(nonqueryCommand.rumandText); Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery()); nonqueryCommand.rumandText = "INSERT INTO MynfexTable VALUES (99)"; Console.WriteLine(nonqueryCommand.rumandText); Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery()); } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { thisConnection.Close(); Console.WriteLine("Connection Closed."); } } }
</source>
Use ExecuteNonQuery() to run DDL statements: create table
<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient; class ExecuteDDL {
public static void Main() { SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.rumandText = "CREATE TABLE MyPersons (" + " PersonID int CONSTRAINT PK_Persons PRIMARY KEY," + " FirstName nvarchar(15) NOT NULL," + " LastName nvarchar(15) NOT NULL," + " DateOfBirth datetime" + ")"; mySqlConnection.Open(); Console.WriteLine("Creating MyPersons table"); int result = mySqlCommand.ExecuteNonQuery(); Console.WriteLine("mySqlCommand.ExecuteNonQuery() = " + result); mySqlCommand.rumandText = "ALTER TABLE MyPersons " + "ADD EmployerID nchar(5) CONSTRAINT FK_Persons_Customers " + "REFERENCES Customers(CustomerID)"; result = mySqlCommand.ExecuteNonQuery(); Console.WriteLine("mySqlCommand.ExecuteNonQuery() = " + result); mySqlCommand.rumandText = "DROP TABLE MyPersons"; result = mySqlCommand.ExecuteNonQuery(); Console.WriteLine("mySqlCommand.ExecuteNonQuery() = " + result); mySqlConnection.Close(); }
}
</source>
use the ExecuteNonQuery() method to run DDL statements
<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient; class ExecuteDDL {
public static void Main() { SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.rumandText = "CREATE TABLE MyEmployee (" + " ID int CONSTRAINT PK_Persons PRIMARY KEY," + " FirstName nvarchar(15) NOT NULL," + " LastName nvarchar(15) NOT NULL," + " DateOfBirth datetime" + ")"; mySqlConnection.Open(); Console.WriteLine("Creating MyEmployee table"); int result = mySqlCommand.ExecuteNonQuery(); Console.WriteLine("mySqlCommand.ExecuteNonQuery() = " + result); mySqlCommand.rumandText = "ALTER TABLE MyEmployee " + "ADD EmployerID nchar(5) CONSTRAINT FK_Persons_Customers " + "REFERENCES Employee(ID)"; Console.WriteLine("Altering MyEmployee table"); result = mySqlCommand.ExecuteNonQuery(); Console.WriteLine("mySqlCommand.ExecuteNonQuery() = " + result); mySqlCommand.rumandText = "DROP TABLE MyEmployee"; Console.WriteLine("Dropping MyEmployee table"); result = mySqlCommand.ExecuteNonQuery(); Console.WriteLine("mySqlCommand.ExecuteNonQuery() = " + result); mySqlConnection.Close(); }
}
</source>