Dynamically create and alter Database Table programmatically based on control value using C# and VB.Net in ASP.Net

Sumeet says:

Sir,

1. In my application there are currently 18 columns in SQL database table.
2. In future the columns may increase or decrease according to the requirement.
3. I want to develope it in such a way that when user clicks on Submit button then should first check that whether table exists in database or not. If yes then should check the columns first, If no.of columns are same as no.of inputs from user page then save data to respective columns, If not same then should ALTER table add new columns and then save
4. If not exists then should CREATE table and then enter data.

is it feasible to do ?

any suggestion would be really helpfull...

For 1 and 2 point

Possibility to increase or decrease column you should know when code so as per the control you add in Page You needs to assign column list. also you need to pass the ordering same as column name with value in values in Insert statement as you said the column can be increase or decrease. Because if Column name in future you alter then there is no relation with control name so be sure with the column list you must need to define each and every time whenever you increase the column or decrease the column list.

For 3 and 4 point

You need to check first Object in database for table is exists or not. If it not exists then you need to create it with default any identity column. Also If table exist then just ignore it and check columns exist in table or not. If it not exists then just create it by Alter table Add column Script. lastly you can insert the Values According to your column and Values According to their order it must need to handle as order of column and there value you must know if you add control then list need to update also if you decrease the control from page then you need to modify the script. Refer the below Code for Your understanding and Implement it by your own logic if it have different requirement according to you.

HTML

<div>
    <table>
        <tr>
            <td>
                First name:
            </td>
            <td>
                <asp:TextBox ID="txtFirstName" runat="server" />
            </td>
        </tr>
        <tr>
            <td>
                Last name:
            </td>
            <td>
                <asp:TextBox ID="txtLastName" runat="server" />
            </td>
        </tr>
        <tr>
            <td>
                Gender:
            </td>
            <td>
                <asp:RadioButtonList ID="rblGender" runat="server">
                    <asp:ListItem Text="Male" Value="M" />
                    <asp:ListItem Text="Female" Value="F" />
                </asp:RadioButtonList>
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:Button ID="btnSubmit" Text="Submit" runat="server" OnClick="Submit" />
            </td>
        </tr>
    </table>
</div>

C#

protected void Submit(object sender, EventArgs e)
{
    /* Declare Table name Variable and column name */
    string tableName = "UserRegistrationDetails";
    string columnList = "FirstName,LastName,Gender";

    /* Declare the Create Table variable with Dynamic script value */
    string createTableScript = string.Format("CREATE TABLE {0}([UserID] [int] IDENTITY(1,1) NOT NULL)", tableName);

    /* Declare the Alter Table Script with the Dynamic script value*/
    string addColumnScript = "ALTER TABLE {0} Add {1} [nvarchar](Max)";

    /* Check if Table Object Exists*/
    if (!CheckObjectExists(tableName, "CheckTableExists"))
    {
        /* If table object not exists then Create the Table*/
        ExuecuteToSQL(createTableScript);
    }
    string alterColumnScript = string.Empty;

    /* By column list You Need to make Loop to check the column exists or not*/
    foreach (var columnname in columnList.Split(','))
    {
        /* Check Column in Table */
        if (!CheckObjectExists(tableName, "CheckColumnExists", columnname))
        {
            /* If column not exists then Add it to table*/
            alterColumnScript = string.Format(addColumnScript, tableName, columnname);
            ExuecuteToSQL(alterColumnScript);
        }
    }

    /* Insert Script You Need to Maintain according to your Column with control value with proper order */
    string insertScript = string.Format(" INSERT INTO {0}({1}) VALUES('{2}','{3}','{4}') ", tableName, columnList, txtFirstName.Text, txtLastName.Text, rblGender.SelectedItem.Value);
    ExuecuteToSQL(insertScript);
}

/* Created the dynamic Method to check Table or column object from database*/
protected bool CheckObjectExists(string tableName, string spName, string columnname = "")
{
    bool isObjectExist = false;
    string constr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(spName, con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@TableName", tableName);
            if (!string.IsNullOrEmpty(columnname))
            {
                cmd.Parameters.AddWithValue("@ColumnName", columnname);
            }
            con.Open();
            object IsExists = cmd.ExecuteScalar();
            if (IsExists != null && IsExists != DBNull.Value)
            {
                isObjectExist = Convert.ToBoolean(IsExists);
            }
            con.Close();
        }
    }
    return isObjectExist;
}

/* Created the dynamic ExuecuteToSQL script which will use to Create table, add column also to insert statement */
protected void ExuecuteToSQL(string sqlQuery)
{
    string constr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(sqlQuery, con))
        {
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

VB.Net

Protected Sub Submit(sender As Object, e As EventArgs)
	'Declare Table name Variable and column name 
	Dim tableName As String = "UserRegistrationDetails"
	Dim columnList As String = "FirstName,LastName,Gender"

	'Declare the Create Table variable with Dynamic script value 
	Dim createTableScript As String = String.Format("CREATE TABLE {0}([UserID] [int] IDENTITY(1,1) NOT NULL)", tableName)

	'Declare the Alter Table Script with the Dynamic script value
	Dim addColumnScript As String = "ALTER TABLE {0} Add {1} [nvarchar](Max)"

	'Check if Table Object Exists
	If Not CheckObjectExists(tableName, "CheckTableExists") Then
		' If table object not exists then Create the Table
		ExuecuteToSQL(createTableScript)
	End If
	Dim alterColumnScript As String = String.Empty

	' By column list You Need to make Loop to check the column exists or not
	For Each columnname As var In columnList.Split(","C)
		'Check Column in Table 
		If Not CheckObjectExists(tableName, "CheckColumnExists", columnname) Then
			'If column not exists then Add it to table
			alterColumnScript = String.Format(addColumnScript, tableName, columnname)
			ExuecuteToSQL(alterColumnScript)
		End If
	Next

	'Insert Script You Need to Maintain according to your Column with control value with proper order 
	Dim insertScript As String = String.Format(" INSERT INTO {0}({1}) VALUES('{2}','{3}','{4}') ", tableName, columnList, txtFirstName.Text, txtLastName.Text, rblGender.SelectedItem.Value)
	ExuecuteToSQL(insertScript)
End Sub

'Created the dynamic Method to check Table or column object from database
Protected Function CheckObjectExists(tableName As String, spName As String, Optional columnname As String = "") As Boolean
	Dim isObjectExist As Boolean = False
	Dim constr As String = ConfigurationManager.ConnectionStrings("ConStr").ConnectionString
	Using con As New SqlConnection(constr)
		Using cmd As New SqlCommand(spName, con)
			cmd.CommandType = CommandType.StoredProcedure
			cmd.Parameters.AddWithValue("@TableName", tableName)
			If Not String.IsNullOrEmpty(columnname) Then
				cmd.Parameters.AddWithValue("@ColumnName", columnname)
			End If
			con.Open()
			Dim IsExists As Object = cmd.ExecuteScalar()
			If IsExists IsNot Nothing AndAlso IsExists <> DBNull.Value Then
				isObjectExist = Convert.ToBoolean(IsExists)
			End If
			con.Close()
		End Using
	End Using
	Return isObjectExist
End Function

'Created the dynamic ExuecuteToSQL script which will use to Create table, add column also to insert statement 
Protected Sub ExuecuteToSQL(sqlQuery As String)
	Dim constr As String = ConfigurationManager.ConnectionStrings("ConStr").ConnectionString
	Using con As New SqlConnection(constr)
		Using cmd As New SqlCommand(sqlQuery, con)
			con.Open()
			cmd.ExecuteNonQuery()
			con.Close()
		End Using
	End Using
End Sub

SQL

Stored procedure to check table and column object in database

/* To check table Object in Database*/
CREATE PROCEDURE CheckTableExists
	@TableName VARCHAR(MAX)
AS
	BEGIN
		IF EXISTS (SELECT Name FROM sys.tables
				   WHERE UPPER(Name) = UPPER(@TableName))
			BEGIN
				SELECT 'True'
			END
		ELSE
			BEGIN
			    SELECT 'False'
			END
	END
GO

/* To check Column in table Object in Database*/
CREATE PROCEDURE CheckColumnExists
	@TableName VARCHAR(MAX)
	,@ColumnName VARCHAR(MAX)
AS
	BEGIN
		IF EXISTS (SELECT name FROM sys.columns
				   WHERE sys.columns.object_id = (SELECT sys.tables.object_id
												  FROM sys.tables
												  WHERE UPPER(Name) = UPPER(@TableName))
                   AND UPPER(name) = UPPER(@ColumnName))

			BEGIN
				SELECT 'True'
			END
		ELSE
			BEGIN
				SELECT 'False'
			END
	END
GO

Screenshoot