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

In this tutorial you will learn:

1. How to create, alter, rename and delete a table using C# ADO.NET?

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)
 );
Design Table

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();
            }
        }
    }
}
Product Table

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();
            }
        }
    }
}
  Rename Table

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.

Adding a New Column


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();
            }
        }
    }
}
Adding a Column

Editing or Altering a Column


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();
            }
        }
    }
}
Edit Column  
Drop or Delete a Column


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();
            }
        }
    }
}
Drop Column

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();
            }
        }
    }
}
Delete Table

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.

More Articles

 
 

Share your thought



PLEASE DISABLE ADBLOCKER AND SUPPORT US!

Please support us by enabling ads on this page. Refresh

YOU DON'T LIKE ADS, WE ALSO DON'T LIKE ADS!
 
But we have to show ads on our site to keep it free and updated. We have to pay huge server costs, domain costs, CDN Costs, Developer Costs, Electricity and Internet Bill. Your little contribution will encourage us to regularly update this site.