How to add new column to the existing database by giving a column from frontend?

I am not sure whether this would correct approach or not.


As Tomas suggested, use ALTER TABLE and use it inside a store procedure.

This store procedure must have three input parameters 1. Column Name 2. Data type of column 3. Nullable or not.

Now decorate your alter table query with this parameters and make the SP ready.


In your UI, one text box where Column Name user will give, One Dropdown for data type and one checkbox for nullable. Get all those values from UI and pass it to BA->DA layer.

There call that Store procedure you created and passs this as input parameters.


Hopefull this will works.

Edited:

CREATE PROCEDURE MYPROC
    @COLUMNNAME  nvarchar(50),
    @DATATYPE  nvarchar(50) = 'nvarchar(50)'    
AS
BEGIN
declare @cmd nvarchar(max)
    
    set @cmd = 'ALTER TABLE TEST
    ADD' + @COLUMNNAME + ' ' + @DATATYPE

    exec sp_executesql @cmd
END;

ADO.NET Component

Use a method with paramater which will be set in your Ui, and pass the values to BA and DA method. In this DA method use the below code. Add/Remove as per your requirement. For SQL data type use a variable and fill/assign different SQL data type based on condition and use it in @DATATYPE parameter add value place.

SqlConnection con = null;
try
            {
         string ConnectionString = "server=XXX;uid=sa;"+
                    "pwd=XXXX; database=northwind";
         con = new SqlConnection(ConnectionString);
         con.Open()
        SqlCommand cmdProc = new SqlCommand("MYPROC", con);
        cmdProc.CommandType = CommandType.StoredProcedure;
        cmdProc.Parameters.AddWithValue("@COLUMNNAME", "Sub4"); // Pass the text value  you got from UI here.
        cmdProc.Parameters.AddWithValue("@DATATYPE", System.Data.SqlDbType.NVarChar);      // Add proper type as per your requirement. 
        cmdProc.ExecuteNonQuery();
}