Saturday, January 28

Dynamically Create tables and columns in sqlserver database by using asp.net c# code

Page source:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AnotherTest.aspx.cs" Inherits="AnotherTest" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        .style1
        {
            width: 26%;
            border: 10px solid #800000;
            background-color: #666666;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div align="center">
   
        <table class="style1"
            style="font-size: medium; font-weight: bold; color: #FFFFFF; margin-left: 0px;">
            <tr>
                <td>
                    Table Name:</td>
                <td>
                    <asp:TextBox ID="TxtTableName" runat="server" Width="200px"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Column Name:</td>
                <td>
                    <asp:TextBox ID="TxtColumnName" runat="server" Width="200px"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td align="left">
                    <asp:ImageButton ID="ImageButton1" runat="server"
                        ImageUrl="~/Image/mc_create_button.gif" onclick="ImageButton1_Click" />
                </td>
            </tr>
        </table>
   
    </div>
    </form>
</body>
</html>

CodeBehind:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class AnotherTest : System.Web.UI.Page
{ string connc = ConfigurationManager.ConnectionStrings["CONN"].ConnectionString.ToString();

      

    protected void Page_Load(object sender, EventArgs e)
        {
          
         }

    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        SqlConnection sqlcon = new SqlConnection(connc);
        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" + TxtTableName.Text + "'", sqlcon);
        DataSet ds = new DataSet();
        da.Fill(ds);

        foreach (DataTable dt in ds.Tables)
        {
            // checking whether the table selected from the dataset exists in the database or not

            string exists = null;
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '"+TxtTableName.Text+"'", sqlcon);
                sqlcon.Open();
                exists = cmd.ExecuteScalar().ToString();
            }
            catch (Exception exce)
            {
                exists = null;
            }

            // if does not exist

            if (exists == null)
            {
                // selecting each column of the datatable to create a table in the database

                foreach (DataColumn dc in dt.Columns)
                {
                    if (exists == null)
                    {
                        SqlCommand createtable = new SqlCommand("CREATE TABLE " + TxtTableName.Text + " (" + TxtTableName.Text.Substring(0, 1) + "ID" + " varchar(MAX))", sqlcon);
                        createtable.ExecuteNonQuery();
                        exists = dt.TableName;
                    }
                    else
                    {
                        SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD "+TxtColumnName.Text+" varchar(MAX)", sqlcon);
                        addcolumn.ExecuteNonQuery();
                        break;
                    }
                }

                // copying the data from datatable to database table

                //using (SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlcon))
                //{
                //    bulkcopy.DestinationTableName = "rama";
                //    bulkcopy.WriteToServer(dt);
                //}
            }
            // if table exists, just copy the data to the destination table in the database

            else
            {
                SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD " + TxtColumnName.Text + " varchar(MAX)", sqlcon);
                addcolumn.ExecuteNonQuery();
                break;
                //using (SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlcon))
                //{
                //    bulkcopy.DestinationTableName = dt.TableName;
                //    bulkcopy.WriteToServer(dt);
                //}
            }
        } 
    }
}

Web.config

<connectionStrings>
    <add name="CONN" connectionString="Server=SWASH-DBS\SWASHSQLINT;Database=Test;Uid=ken;pwd=kc@2011" providerName="System.Data.SqlClient"/>
        <!--<add name="CONN" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Shibashish\Desktop\SANJAY\UsesofWebNotification\App_Data\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>-->
        <!--<add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>-->
    </connectionStrings>

Page View:-


Another Type:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AnotherTest.aspx.cs" Inherits="AnotherTest" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <h1 align="center" style="font-weight: bold; color: #800000">Shibashish mohanty</h1>
    <style type="text/css">
        .style1
        {
            width: 36%;
            border: 10px solid #800000;
            background-color: #666666;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div align="center">
   
        <table class="style1"
            style="font-size: medium; font-weight: bold; color: #FFFFFF; margin-left: 0px;">
            <tr>
                <td>
                    Table Name:</td>
                <td>
                    <asp:TextBox ID="TxtTableName" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    Column Name:</td>
                <td>
                    <asp:TextBox ID="TxtColumnName" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td>
                    <asp:TextBox ID="TxtDatatype" runat="server" Width="103px">DataType</asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td align="left">
                    <asp:ImageButton ID="ImageButton1" runat="server"
                        ImageUrl="~/Image/mc_create_button.gif" onclick="ImageButton1_Click" />
                </td>
                <td align="left">
                    &nbsp;</td>
            </tr>
        </table>
   
    </div>
    </form>
</body>
</html>
Page Behind:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class AnotherTest : System.Web.UI.Page
{ string connc = ConfigurationManager.ConnectionStrings["CONN"].ConnectionString.ToString();

      

    protected void Page_Load(object sender, EventArgs e)
        {
          
         }

    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        SqlConnection sqlcon = new SqlConnection(connc);
        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" + TxtTableName.Text + "'", sqlcon);
        DataSet ds = new DataSet();
        da.Fill(ds);

        foreach (DataTable dt in ds.Tables)
        {
            // checking whether the table selected from the dataset exists in the database or not

            string exists = null;
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '"+TxtTableName.Text+"'", sqlcon);
                sqlcon.Open();
                exists = cmd.ExecuteScalar().ToString();
            }
            catch (Exception exce)
            {
                exists = null;
            }

            // if does not exist

            if (exists == null)
            {
                // selecting each column of the datatable to create a table in the database

                foreach (DataColumn dc in dt.Columns)
                {
                    try
                    {
                        if (exists == null)
                        {
                            SqlCommand createtable = new SqlCommand("CREATE TABLE " + TxtTableName.Text + " (" + TxtTableName.Text.Substring(0, 1) + "ID" + " " + TxtDatatype.Text + ")", sqlcon);
                            createtable.ExecuteNonQuery();
                            exists = dt.TableName;
                        }
                        else
                        {
                            SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD " + TxtColumnName.Text + " " + TxtDatatype.Text, sqlcon);
                            addcolumn.ExecuteNonQuery();
                            break;
                        }
                    }
                    catch
                    {
                        Page.RegisterStartupScript("ScriptDescription", "<script type=\"text/javascript\">alert('Invalid DataTypes');</script>");
                    }
                }

                // copying the data from datatable to database table

                //using (SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlcon))
                //{
                //    bulkcopy.DestinationTableName = "rama";
                //    bulkcopy.WriteToServer(dt);
                //}
            }
            // if table exists, just copy the data to the destination table in the database

            else
            {
                try
                {
                    SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD " + TxtColumnName.Text + " " + TxtDatatype.Text, sqlcon);
                    addcolumn.ExecuteNonQuery();
                    break;
                }
                catch
                {
                    Page.RegisterStartupScript("ScriptDescription", "<script type=\"text/javascript\">alert('Invalid DataTypes');</script>");
                }
                //using (SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlcon))
                //{
                //    bulkcopy.DestinationTableName = dt.TableName;
                //    bulkcopy.WriteToServer(dt);
                //}
            }
        } 
    }
}


 Try Another:-
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AnotherTest.aspx.cs" Inherits="AnotherTest" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <h1 align="center" style="font-weight: bold; color: #800000">Shibashish mohanty</h1>
    <style type="text/css">
        .style1
        {
            width: 43%;
            border: 10px solid #800000;
            background-color: #666666;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div align="center">
   
        <table class="style1"
            style="font-size: medium; font-weight: bold; color: #FFFFFF; margin-left: 0px;">
            <tr>
                <td>
                    Table Name:</td>
                <td>
                    <asp:TextBox ID="TxtTableName" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    Column Name:</td>
                <td>
                    <asp:TextBox ID="TxtColumnName" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td>
                    <asp:TextBox ID="TxtDatatype" runat="server" Width="103px">DataType</asp:TextBox>
                </td>
                <td>
                    <asp:CheckBox ID="ChkPrimaryKey" runat="server" AutoPostBack="True"
                        Text="Primary Key" />
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td align="left">
                    <asp:ImageButton ID="ImageButton1" runat="server"
                        ImageUrl="~/Image/mc_create_button.gif" onclick="ImageButton1_Click" />
                </td>
                <td align="left">
                    &nbsp;</td>
                <td align="left">
                    &nbsp;</td>
            </tr>
        </table>
   
    </div>
    </form>
</body>
</html>


Code Behind:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class AnotherTest : System.Web.UI.Page
{ string connc = ConfigurationManager.ConnectionStrings["CONN"].ConnectionString.ToString();

      

    protected void Page_Load(object sender, EventArgs e)
        {
          
         }

    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        SqlConnection sqlcon = new SqlConnection(connc);
        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" + TxtTableName.Text + "'", sqlcon);
        DataSet ds = new DataSet();
        da.Fill(ds);

        foreach (DataTable dt in ds.Tables)
        {
            // checking whether the table selected from the dataset exists in the database or not

            string exists = null;
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '"+TxtTableName.Text+"'", sqlcon);
                sqlcon.Open();
                exists = cmd.ExecuteScalar().ToString();
            }
            catch (Exception exce)
            {
                exists = null;
            }

            // if does not exist

            if (exists == null)
            {
                // selecting each column of the datatable to create a table in the database

                foreach (DataColumn dc in dt.Columns)
                {
                    try
                    {
                        if (exists == null)
                        {
                            SqlCommand createtable = new SqlCommand("CREATE TABLE " + TxtTableName.Text + " (" + TxtTableName.Text.Substring(0, 1) + "ID" + " " +"int"+ " IDENTITY(1,1) NOT NULL" + ")", sqlcon);
                            createtable.ExecuteNonQuery();
                            exists = dt.TableName;
                        }
                        else
                        {
                            if (ChkPrimaryKey.Checked)
                            {
                                SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD " + TxtColumnName.Text + " " + TxtDatatype.Text + " " + "PRIMARY KEY ", sqlcon);
                                addcolumn.ExecuteNonQuery();
                                break;
                            }
                            else
                            {
                                SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD " + TxtColumnName.Text + " " + TxtDatatype.Text, sqlcon);
                                addcolumn.ExecuteNonQuery();
                                break;
                            }
                        }
                    }
                    catch
                    {
                        Page.RegisterStartupScript("ScriptDescription", "<script type=\"text/javascript\">alert('Invalid DataTypes or Already you have a primary key constraint');</script>");
                    }
                }

                // copying the data from datatable to database table

                //using (SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlcon))
                //{
                //    bulkcopy.DestinationTableName = "rama";
                //    bulkcopy.WriteToServer(dt);
                //}
            }
            // if table exists, just copy the data to the destination table in the database

            else
            {
                try
                {
                    if (ChkPrimaryKey.Checked)
                    {
                        SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD " + TxtColumnName.Text + " " + TxtDatatype.Text + " " + "PRIMARY KEY ", sqlcon);
                        addcolumn.ExecuteNonQuery();
                        break;
                    }
                    else
                    {
                        SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD " + TxtColumnName.Text + " " + TxtDatatype.Text, sqlcon);
                        addcolumn.ExecuteNonQuery();
                        break;
                    }
                }
                catch
                {
                    Page.RegisterStartupScript("ScriptDescription", "<script type=\"text/javascript\">alert('Invalid DataTypes or Already you have a primary key constraint');</script>");
                }
                //using (SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlcon))
                //{
                //    bulkcopy.DestinationTableName = dt.TableName;
                //    bulkcopy.WriteToServer(dt);
                //}
            }
        } 
    }
}


Finally i got the better solution:-




Page source:-
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AnotherTest.aspx.cs" Inherits="AnotherTest" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <h1 align="center" style="font-weight: bold; color: #800000">Shibashish mohanty</h1>
    <style type="text/css">
        .style1
        {
            width: 43%;
            border: 10px solid #800000;
            background-color: #666666;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div align="center">
   
        <table class="style1"
            style="font-size: medium; font-weight: bold; color: #FFFFFF; margin-left: 0px;">
            <tr>
                <td>
                    Table Name:</td>
                <td>
                    <asp:TextBox ID="TxtTableName" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    Column Name:</td>
                <td>
                    <asp:TextBox ID="TxtColumnName" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td>
                    <asp:TextBox ID="TxtDatatype" runat="server" Width="103px">DataType</asp:TextBox>
                </td>
                <td>
                    <asp:CheckBox ID="ChkPrimaryKey" runat="server" AutoPostBack="True"
                        oncheckedchanged="ChkPrimaryKey_CheckedChanged" Text="Primary Key" />
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td align="left">
                    <asp:ImageButton ID="ImageButton1" runat="server"
                        ImageUrl="~/Image/mc_create_button.gif" onclick="ImageButton1_Click" />
                </td>
                <td align="left">
                    &nbsp;</td>
                <td align="left">
                    &nbsp;</td>
            </tr>
        </table>
   
    </div>
    </form>
</body>
</html>Code Behind:-


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class AnotherTest : System.Web.UI.Page
{ string connc = ConfigurationManager.ConnectionStrings["CONN"].ConnectionString.ToString();

      

    protected void Page_Load(object sender, EventArgs e)
        {
          
         }

    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        SqlConnection sqlcon = new SqlConnection(connc);
        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" + TxtTableName.Text + "'", sqlcon);
        DataSet ds = new DataSet();
        da.Fill(ds);

        foreach (DataTable dt in ds.Tables)
        {
            // checking whether the table selected from the dataset exists in the database or not

            string exists = null;
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '"+TxtTableName.Text+"'", sqlcon);
                sqlcon.Open();
                exists = cmd.ExecuteScalar().ToString();
            }
            catch (Exception exce)
            {
                exists = null;
            }

            // if does not exist

            if (exists == null)
            {
                // selecting each column of the datatable to create a table in the database

                foreach (DataColumn dc in dt.Columns)
                {
                    try
                    {
                        if (exists == null)
                        {
                            SqlCommand createtable = new SqlCommand("CREATE TABLE " + TxtTableName.Text + " (" + TxtTableName.Text.Substring(0, 1) + "ID" + " " +"int"+ " IDENTITY(1,1) NOT NULL" + ")", sqlcon);
                            createtable.ExecuteNonQuery();
                            exists = dt.TableName;
                        }
                        else
                        {
                            if (ChkPrimaryKey.Checked)
                            {
                                SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD " + TxtColumnName.Text + " " + TxtDatatype.Text + " " + "PRIMARY KEY ", sqlcon);
                                addcolumn.ExecuteNonQuery();
                                break;
                            }
                            else
                            {
                                SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD " + TxtColumnName.Text + " " + TxtDatatype.Text, sqlcon);
                                addcolumn.ExecuteNonQuery();
                                break;
                            }
                        }
                    }
                    catch
                    {
                        Page.RegisterStartupScript("ScriptDescription", "<script type=\"text/javascript\">alert('Invalid DataTypes');</script>");
                    }
                }

                // copying the data from datatable to database table

                //using (SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlcon))
                //{
                //    bulkcopy.DestinationTableName = "rama";
                //    bulkcopy.WriteToServer(dt);
                //}
            }
            // if table exists, just copy the data to the destination table in the database

            else
            {
                try
                {
                    if (ChkPrimaryKey.Checked)
                    {
                        SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD " + TxtColumnName.Text + " " + TxtDatatype.Text + " " + "PRIMARY KEY ", sqlcon);
                        addcolumn.ExecuteNonQuery();
                        break;
                    }
                    else
                    {
                        SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD " + TxtColumnName.Text + " " + TxtDatatype.Text, sqlcon);
                        addcolumn.ExecuteNonQuery();
                        break;
                    }
                }
                catch
                {
                    Page.RegisterStartupScript("ScriptDescription", "<script type=\"text/javascript\">alert('Invalid DataTypes');</script>");
                }
                //using (SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlcon))
                //{
                //    bulkcopy.DestinationTableName = dt.TableName;
                //    bulkcopy.WriteToServer(dt);
                //}
            }
        } 
    }
    protected void ChkPrimaryKey_CheckedChanged(object sender, EventArgs e)
    {
        if (ChkPrimaryKey.Checked)
        {
            string PrimaryKey = null;

            PrimaryKey = GetprimaryKey(TxtTableName.Text, connc);
            if (PrimaryKey != null && PrimaryKey!="")
            {
                Page.RegisterStartupScript("ScriptDescription", "<script type=\"text/javascript\">alert(' Already you have a primary key constraint');</script>");

            }
        }  //Response.Write("This is your tablePrimary Key Column" + PrimaryKey);
    }
    public string GetprimaryKey(string tableName, string cnnString)
    {
        string names,
         ID = "";
        SqlDataReader mReader;
        SqlConnection mSqlConnection = new SqlConnection();
        SqlCommand mSqlCommand = new SqlCommand();

        mSqlConnection = new SqlConnection(connc);
        mSqlConnection.Open();
        // sp_pkeys is SQL Server default stored procedure
        // you pass it only table Name, it will return
        // primary key column
        mSqlCommand = new SqlCommand("sp_pkeys", mSqlConnection);
        mSqlCommand.CommandType = CommandType.StoredProcedure; mSqlCommand.Parameters.Add
                 ("@table_name", SqlDbType.NVarChar).Value = tableName;
        mReader = mSqlCommand.ExecuteReader();
        while (mReader.Read())
        {
            //the primary key column resides at index 4
            ID = mReader[3].ToString();
        }
        return ID;
    }
}


Another one Something Different: 

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AnotherTest.aspx.cs" Inherits="AnotherTest" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <h1 align="center" style="font-weight: bold; color: #800000">Shibashish mohanty</h1>
    <style type="text/css">
        .style1
        {
            width: 52%;
            border: 10px solid #800000;
            background-color: #666666;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div align="center">
   
        <table class="style1"
           
            style="font-size: medium; font-weight: bold; color: #FFFFFF; margin-left: 0px; height: 227px;">
            <tr>
                <td>
                    Table Name:</td>
                <td>
                    <asp:TextBox ID="TxtTableName" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    Column Name:</td>
                <td>
                    <asp:TextBox ID="TxtColumnName" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td>
                    <asp:TextBox ID="TxtDatatype" runat="server" Width="103px">DataType</asp:TextBox>
                </td>
                <td>
                    <asp:CheckBox ID="ChkPrimaryKey" runat="server" AutoPostBack="True"
                        oncheckedchanged="ChkPrimaryKey_CheckedChanged" Text="Primary Key" />
                </td>
                <td>
                    <asp:CheckBox ID="ChkAllowNull" runat="server" AutoPostBack="True"
                        Checked="True" Text="Allow Null" />
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td align="left">
                    &nbsp;</td>
                <td align="left">
                    <asp:ImageButton ID="ImageButton1" runat="server"
                        ImageUrl="~/Image/mc_create_button.gif" onclick="ImageButton1_Click" />
                </td>
                <td align="left">
                    &nbsp;</td>
                <td align="left">
                    &nbsp;</td>
            </tr>
        </table>
   
    </div>
    </form>
</body>
</html> Code Behind:



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class AnotherTest : System.Web.UI.Page
{ string connc = ConfigurationManager.ConnectionStrings["CONN"].ConnectionString.ToString();

      

    protected void Page_Load(object sender, EventArgs e)
        {
            if (ChkPrimaryKey.Checked==false)
            {
                ChkAllowNull.Checked = true;
            }
         }

    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        SqlConnection sqlcon = new SqlConnection(connc);
        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" + TxtTableName.Text + "'", sqlcon);
        DataSet ds = new DataSet();
        da.Fill(ds);

        foreach (DataTable dt in ds.Tables)
        {
            // checking whether the table selected from the dataset exists in the database or not

            string exists = null;
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '"+TxtTableName.Text+"'", sqlcon);
                sqlcon.Open();
                exists = cmd.ExecuteScalar().ToString();
            }
            catch (Exception exce)
            {
                exists = null;
            }

            // if does not exist

            if (exists == null)
            {
                // selecting each column of the datatable to create a table in the database

                foreach (DataColumn dc in dt.Columns)
                {
                    try
                    {
                        if (exists == null)
                        {
                            SqlCommand createtable = new SqlCommand("CREATE TABLE " + TxtTableName.Text + " (" + TxtTableName.Text.Substring(0, 1) + "ID" + " " +"int"+ " IDENTITY(1,1) NOT NULL" + ")", sqlcon);
                            createtable.ExecuteNonQuery();
                            exists = dt.TableName;
                        }
                        else
                        {
                            if (ChkPrimaryKey.Checked)
                            {
                                SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD " + TxtColumnName.Text + " " + TxtDatatype.Text + " " + "PRIMARY KEY ", sqlcon);
                                addcolumn.ExecuteNonQuery();
                                break;
                            }
                            else
                            {
                                if (ChkAllowNull.Checked)
                                {
                                    SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD " + TxtColumnName.Text + " " + TxtDatatype.Text, sqlcon);
                                    addcolumn.ExecuteNonQuery();
                                    break;
                                }
                                else
                                {
                                    SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD " + TxtColumnName.Text + " " + TxtDatatype.Text + " NOT NULL", sqlcon);
                                    addcolumn.ExecuteNonQuery();
                                    break;
                                }
                            }
                        }
                    }
                    catch
                    {
                        Page.RegisterStartupScript("ScriptDescription", "<script type=\"text/javascript\">alert('Invalid DataTypes');</script>");
                    }
                }

                // copying the data from datatable to database table

                //using (SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlcon))
                //{
                //    bulkcopy.DestinationTableName = "rama";
                //    bulkcopy.WriteToServer(dt);
                //}
            }
            // if table exists, just copy the data to the destination table in the database

            else
            {
                try
                {
                    if (ChkPrimaryKey.Checked)
                    {
                        SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD " + TxtColumnName.Text + " " + TxtDatatype.Text + " " + "PRIMARY KEY ", sqlcon);
                        addcolumn.ExecuteNonQuery();
                        break;
                    }
                    else
                    {
                        if (ChkAllowNull.Checked)
                        {
                            SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD " + TxtColumnName.Text + " " + TxtDatatype.Text, sqlcon);
                            addcolumn.ExecuteNonQuery();
                            break;
                        }
                        else
                        {
                            SqlCommand addcolumn = new SqlCommand("ALTER TABLE " + TxtTableName.Text + " ADD " + TxtColumnName.Text + " " + TxtDatatype.Text + " NOT NULL", sqlcon);
                            addcolumn.ExecuteNonQuery();
                            break;
                        }
                    }
                }
                catch
                {
                    Page.RegisterStartupScript("ScriptDescription", "<script type=\"text/javascript\">alert('Invalid DataTypes');</script>");
                }
                //using (SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlcon))
                //{
                //    bulkcopy.DestinationTableName = dt.TableName;
                //    bulkcopy.WriteToServer(dt);
                //}
            }
        }
        ChkPrimaryKey.Checked = false;
    }
    protected void ChkPrimaryKey_CheckedChanged(object sender, EventArgs e)
    {
        if (ChkPrimaryKey.Checked)
        {
            string PrimaryKey = null;

            PrimaryKey = GetprimaryKey(TxtTableName.Text, connc);
            if (PrimaryKey != null && PrimaryKey!="")
            {
                Page.RegisterStartupScript("ScriptDescription", "<script type=\"text/javascript\">alert(' Already you have a primary key constraint');</script>");
                ChkPrimaryKey.Checked = false;

            }
            ChkAllowNull.Checked = false;
        }  //Response.Write("This is your tablePrimary Key Column" + PrimaryKey);
    }
    public string GetprimaryKey(string tableName, string cnnString)
    {
        string names,
         ID = "";
        SqlDataReader mReader;
        SqlConnection mSqlConnection = new SqlConnection();
        SqlCommand mSqlCommand = new SqlCommand();

        mSqlConnection = new SqlConnection(connc);
        mSqlConnection.Open();
        // sp_pkeys is SQL Server default stored procedure
        // you pass it only table Name, it will return
        // primary key column
        mSqlCommand = new SqlCommand("sp_pkeys", mSqlConnection);
        mSqlCommand.CommandType = CommandType.StoredProcedure; mSqlCommand.Parameters.Add
                 ("@table_name", SqlDbType.NVarChar).Value = tableName;
        mReader = mSqlCommand.ExecuteReader();
        while (mReader.Read())
        {
            //the primary key column resides at index 4
            ID = mReader[3].ToString();
        }
        return ID;
    }
  
}
 

Thanks shibashish mohanty
Posted by at
Labels: , ,

No comments:

Post a Comment

Please don't spam, spam comments is not allowed here.

Subscribe to: Post Comments (Atom)

.

.
ShibashishMnty
shibashish mohanty