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>