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");
cmdProc.Parameters.AddWithValue("@DATATYPE", System.Data.SqlDbType.NVarChar);
cmdProc.ExecuteNonQuery();
}