How to add new column to the database by inserting a record?

Question

Hai Sir, How to add new column for the existing database table by inserting a column from the front end in .Net?

Pankaj Kumar Choudhary · Answer

Try this method Create Proc [dbo].[Alter_Table](@Table_Name nvarchar(50),@Col_Name nvarchar(50),@Data_Type nvarchar(50),@IsNull bit) as begin Declare @Query nvarchar(max); if(@IsNull=0) Set @Query=N'Alter Table '+QUOTENAME(@Table_Name)+' Add '+QUOTENAME(@Col_Name)+' '+QUOTENAME(@Data_Type) else Set @Query=N'Alter Table '+QUOTENAME(@Table_Name)+'Add'+QUOTENAME(@Col_Name)+QUOTENAME(@Data_Type)+'Not Null' EXECUTE sp_executesql @Query end

Pankaj Kumar Choudhary · Answer

Hello @Sureshma You can try this stored procedure USE [Extra] GO /****** Object: StoredProcedure [dbo].[Alter_Table] Script Date: 5/22/2015 3:44:29 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Proc [dbo].[Alter_Table](@Table_Name nvarchar(50),@Col_Name nvarchar(50),@Data_Type nvarchar(50),@IsNull bit) as begin Declare @Query nvarchar(max); if(@IsNull=0) Set @Query=N'Alter Table '+QUOTENAME(@Table_Name)+' Add '+QUOTENAME(@Col_Name)+' '+QUOTENAME(@Data_Type) else Set @Query=N'Alter Table '+QUOTENAME(@Table_Name)+'Add'+QUOTENAME(@Col_Name)+QUOTENAME(@Data_Type)+'Not Null' EXECUTE sp_executesql @Query end

Sivaraman Dhamodaran · Answer

create procedure usp_altertable as begin alter table add end

Sivaraman Dhamodaran · Answer

Sureshma, Your question stated how Do I add Column from the Front End. That is why all are replying you doing it the Ado.Net & C# way.

Sanjay Sabariya · Answer

you can use store procedure to alter table. create procedure usp_altertable as begin alter table [tablename] add [ProductId] int default 0 NOT NULL end

Kanaparthi Sureshma Reddy · Answer

That query belongs to backend only but I want to add/alter the table column from frontend. Can you please Tell me the stored procedure for that....

Pankaj Kumar Choudhary · Answer

Hello @Sureshma You can use this method Con.Open(); String Str="Alter Table Table_Name Add Column_Name Datatype"; SqlCommand Com = new SqlCommand( Str,Con); Com.ExecuteNonQuery() ; Con.Close();

Sanjay Sabariya · Answer

hello sureshma you can use this code to add new column into table. using (DbConnection connection = new SqlConnection("Your connection string")) { connection .Open(); using (DbCommand command = new SqlCommand("alter table [Product] add [ProductId] int default 0 NOT NULL",connection)) { command .ExecuteNonQuery(); connection.close(); } }

How to add new column to the database by inserting a record?

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (8)