C# ADO.NET - Create, Rename, Alter and Delete Table

Creating a table using ado.net c# is very easy and you just need to know the way to send table query to the database using c# ado.net. In this chapter, you will learn how to create, rename, update and delete sql table using c# ado.net.

In this example, I am going to use following table script to create Products table in ComputerShop Database. In the previous chapter, I have already explained how to Create, Select, Rename and Delete Database using C# ADO.NET.

SQL Script

USE [ComputerShop]
GO

CREATE TABLE dbo.Products
(
    ID int IDENTITY(1,1) NOT NULL,
    Name nvarchar(50) NULL,
    Price nvarchar(50) NULL,
    Date datetime NULL,
 CONSTRAINT pk_id PRIMARY KEY (ID)
 );

Create a Table using C# ADO.NET

You can execute above query using the following block of codes.

using System;
using System.Data.SqlClient;

namespace CreateTable
{
    class Program
    {
        static void Main(string[] args)
        {            
            SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True");
            string query =
            @"CREATE TABLE dbo.Products
                (
                    ID int IDENTITY(1,1) NOT NULL,
                    Name nvarchar(50) NULL,
                    Price nvarchar(50) NULL,
                    Date datetime NULL,
                    CONSTRAINT pk_id PRIMARY KEY (ID)
                );";
            SqlCommand cmd = new SqlCommand(query, con);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                Console.WriteLine("Table Created Successfully");
            }
            catch(SqlException e)
            {
                Console.WriteLine("Error Generated. Details: " + e.ToString());
            }
            finally
            {
                con.Close();
                Console.ReadKey();
            }
        }
    }
}

Rename a Table using C# ADO.NET

You can rename a SQL Table using the following block of codes.

SQL Script

EXEC sp_rename 'Products', 'Accessories'

Program

using System;
using System.Data.SqlClient;

namespace RenameTable
{
    class Program
    {
        static void Main(string[] args)
        {            
            SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True");
            string query = @"EXEC sp_rename 'Products', 'Accessories'";
            SqlCommand cmd = new SqlCommand(query, con);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                Console.WriteLine("Table Renamed Successfully");
            }
            catch(SqlException e)
            {
                Console.WriteLine("Error Generated. Details: " + e.ToString());
            }
            finally
            {
                con.Close();
                Console.ReadKey();
            }
        }
    }
}

 

Alter Table using ADO.NET C#

In this example, I will show you how can you add, update or remove a column from SQL Table.

SQL Script

ALTER TABLE Accessories
ADD Stock nvarchar(50);

Executing above SQL Script using c# ado.net will create a new table Stock navarchar(50) in Accessories Table.

Programming Example for Adding a New Column

using System;
using System.Data.SqlClient;

namespace CreateColumn
{
    class Program
    {
        static void Main(string[] args)
        {            
            SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True");
            string query = 
                @"ALTER TABLE Accessories
                  ADD Stock nvarchar(50);";

            SqlCommand cmd = new SqlCommand(query, con);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                Console.WriteLine("Column Created Successfully");
            }
            catch(SqlException e)
            {
                Console.WriteLine("Error Generated. Details: " + e.ToString());
            }
            finally
            {
                con.Close();
                Console.ReadKey();
            }
        }
    }
}

SQL Script

ALTER TABLE Accessories
ALTER COLUMN Stock int;

In this example, I will change column Stock nvarchar(50) to Stock int.

Programming Example for Editing or Altering a Column

using System;
using System.Data.SqlClient;

namespace CreateColumn
{
    class Program
    {
        static void Main(string[] args)
        {            
            SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True");
            string query =
                @"ALTER TABLE Accessories
                  ALTER COLUMN Stock int;";

            SqlCommand cmd = new SqlCommand(query, con);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                Console.WriteLine("Column Edited Successfully");
            }
            catch(SqlException e)
            {
                Console.WriteLine("Error Generated. Details: " + e.ToString());
            }
            finally
            {
                con.Close();
                Console.ReadKey();
            }
        }
    }
}

 


SQL Script

ALTER TABLE Accessories
DROP COLUMN Stock;

You can delete existing column using the following code.

Programming Example

using System;
using System.Data.SqlClient;

namespace DeleteColumn
{
    class Program
    {
        static void Main(string[] args)
        {            
            SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True");
            string query =
                @"ALTER TABLE Accessories
                  DROP COLUMN Stock;";

            SqlCommand cmd = new SqlCommand(query, con);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                Console.WriteLine("Column Deleted Successfully");
            }
            catch(SqlException e)
            {
                Console.WriteLine("Error Generated. Details: " + e.ToString());
            }
            finally
            {
                con.Close();
                Console.ReadKey();
            }
        }
    }
}

Deleting a SQL Table using C# ADO.NET

In order to DROP or DELETE SQL Table, you need to execute the following c# ado.net code.

SQL Script

DROP TABLE Accessories

Programming Example

using System;
using System.Data.SqlClient;

namespace DropTable
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True");
            string query =
                @"DROP TABLE Accessories";

            SqlCommand cmd = new SqlCommand(query, con);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                Console.WriteLine("Table Deleted Successfully");
            }
            catch (SqlException e)
            {
                Console.WriteLine("Error Generated. Details: " + e.ToString());
            }
            finally
            {
                con.Close();
                Console.ReadKey();
            }
        }
    }
}

Summary

In this chapter, you learned SQL Table Manipulation in ADO.NET with complete programming example. I have added complete c# code for Create, Rename, Alter and Delete SQL Table. In the next chapter, you will learn Insert, Update, Read and Delete data from SQL Table.