Auto Generate Procedure


<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Default.aspx.vb" Inherits="_Default" %>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Atuo Generated Stored Procedure</title>

</head>

<body>

    <form id="form1" runat="server">

        <asp:ScriptManager ID="ScriptManager1" runat="server" />

        <table width="100%">

            <tr>

                <td align="left" style="color: #076895; font-size: 20px; font-family: Verdana">

                    Auto Generated Stored Procedure.

                    <hr />

                </td>

            </tr>

            <tr>

                <td align="center" valign="top" style="height: 550px">

                    <table width="600px" style="color: #076895; font-size: 13px; font-family: Verdana">

                        <tr>

                            <td align="right">

                                Server Name :

                            </td>

                            <td align="left">

                                <asp:TextBox ID="txtServerName" runat="server"></asp:TextBox>

                            </td>

                        </tr>

                        <tr>

                            <td align="right">

                                User Name :

                            </td>

                            <td align="left">

                                <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>

                            </td>

                        </tr>

                        <tr>

                            <td align="right">

                                Password :

                            </td>

                            <td align="left">

                                <asp:TextBox ID="txtPassword" runat="server" TextMode="Password"></asp:TextBox>

                            </td>

                        </tr>

                        <tr>

                            <td>

                            </td>

                            <td align="left">

                                <asp:Button ID="btnTestConnection" runat="server" Text="Connect Database" />

                            </td>

                        </tr>

                        <tr id="trDatabase" runat="server" visible="false">

                            <td align="right">

                                Select Database :

                            </td>

                            <td align="left">

                                <asp:DropDownList ID="ddlDatabase" runat="server" AutoPostBack="true" Width="250px"

                                    Font-Names="Verdana" Font-Size="12px">

                                </asp:DropDownList>

                            </td>

                        </tr>

                        <tr id="trTable" runat="server" visible="false">

                            <td align="right">

                                Select Table :

                            </td>

                            <td align="left">

                                <asp:DropDownList ID="ddlTable" runat="server" AutoPostBack="true" Width="250px"

                                    Font-Names="Verdana" Font-Size="12px">

                                </asp:DropDownList>

                            </td>

                        </tr>

                        <tr id="trProcedureName" runat="server" visible="false">

                            <td align="right">

                                ProcedureName

                            </td>

                            <td align="left">

                                <asp:TextBox ID="txtProcedureName" runat="server" Width="250px"></asp:TextBox>

                            </td>

                        </tr>

                        <tr id="trButton" runat="server" visible="false">

                            <td>

                            </td>

                            <td align="left">

                                <asp:Button ID="btnGenerateProcedure" runat="server" Text="Generate Stored Procedure" />

                            </td>

                        </tr>

                    </table>

                </td>

            </tr>

            <tr>

                <td align="left" style="color: #076895; font-size: 10px; font-family: Verdana">

                    <hr />

                    Developed By : Rachit Patel

                </td>

            </tr>

        </table>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server"></asp:SqlDataSource>

    </form>

</body>

</html>

 

 

 

Imports System.Data

Imports System.Data.SqlClient

Imports System.IO

Partial Class _Default

    Inherits System.Web.UI.Page

    Private Shared conn As SqlConnection

    Private Shared cmd As SqlCommand

    Private Shared adp As SqlDataAdapter

    Dim i As Int32 = 0

    Dim strLine As String

    Dim strIdentityColumn As String = Nothing

    Dim strPrimaryKeyColumn As String = Nothing

    Dim StreamWriter1 As StreamWriter

 

    Protected Sub btnTestConnection_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnTestConnection.Click

        Dim strConnectinString As String

 

        Try

 

            strConnectinString = "Data Source=" + txtServerName.Text + ";Persist Security Info=True;User ID=" + txtUserName.Text + ";Password=" + txtPassword.Text

            ViewState("ServerName") = txtServerName.Text

            ViewState("UserId") = txtUserName.Text

            ViewState("Password") = txtPassword.Text

 

            conn = New SqlConnection()

            conn.ConnectionString = strConnectinString

 

            cmd = New SqlCommand()

            Dim ds As DataSet

            ds = New DataSet()

            cmd.Connection = conn

            cmd.CommandText = "SELECT * FROM master..sysdatabases ORDER BY name"

            cmd.CommandType = CommandType.Text

            adp = New SqlDataAdapter(cmd)

            adp.Fill(ds)

 

            trDatabase.Visible = True

 

            ddlDatabase.DataSource = ds

            ddlDatabase.DataTextField = "name"

            ddlDatabase.DataValueField = "name"

            ddlDatabase.DataBind()

 

        Catch ex As Exception

            Page.ClientScript.RegisterStartupScript(Me.GetType(), "ErroMessage", "<script type='text/javascript'>alert('Login failed with user name " + txtUserName.Text + ".');</script>")

        End Try

    End Sub

 

    Protected Sub ddlDatabase_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlDatabase.SelectedIndexChanged

        conn = New SqlConnection()

        conn.ConnectionString = "Data Source=" + ViewState("ServerName") + ";Initial Catalog=" + ddlDatabase.SelectedValue + ";Persist Security Info=True;User ID=" + ViewState("UserId") + ";Password=" + ViewState("Password")

        ViewState("ConnectionString") = "Data Source=" + ViewState("ServerName") + ";Initial Catalog=" + ddlDatabase.SelectedValue + ";Persist Security Info=True;User ID=" + ViewState("UserId") + ";Password=" + ViewState("Password")

        cmd = New SqlCommand()

        Dim ds As DataSet

        ds = New DataSet()

        cmd.Connection = conn

        cmd.CommandText = "SELECT name FROM SYS.OBJECTS WHERE type = 'U' ORDER BY name"

        cmd.CommandType = CommandType.Text

        adp = New SqlDataAdapter(cmd)

        adp.Fill(ds)

 

        ddlTable.DataSource = ds

        ddlTable.DataTextField = "name"

        ddlTable.DataValueField = "name"

        ddlTable.DataBind()

 

        trTable.Visible = True

        txtProcedureName.Text = "[dbo].[usp_Insert_Update_Delete_" + ddlTable.SelectedValue + "]"

    End Sub

    Protected Sub ddlTable_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlTable.SelectedIndexChanged

        trButton.Visible = True

        trProcedureName.Visible = True

        txtProcedureName.Text = "usp_Insert_Update_Delete_" + ddlTable.SelectedValue

    End Sub

    Protected Sub btnGenerateProcedure_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGenerateProcedure.Click

        conn = New SqlConnection()

        conn.ConnectionString = ViewState("ConnectionString")

 

        Try

 

            cmd = New SqlCommand()

            Dim ds As DataSet

            ds = New DataSet()

            cmd.Connection = conn

            cmd.CommandText = "SELECT *  FROM information_schema.columns where table_name = '" + ddlTable.SelectedValue + "' order by ordinal_position"

            cmd.CommandType = CommandType.Text

            adp = New SqlDataAdapter(cmd)

            adp.Fill(ds)

 

 

 

            strIdentityColumn = getIdentityColumn()

            strPrimaryKeyColumn = getPrimarykeyColumn()

 

 

            If strIdentityColumn = strPrimaryKeyColumn Then

            ElseIf strIdentityColumn = "" Then

                strIdentityColumn = strPrimaryKeyColumn

            ElseIf strPrimaryKeyColumn = "" Then

 

            End If

            StreamWriter1 = New StreamWriter(Server.MapPath("sp.sql"))

 

 

 

 

            If ds.Tables(0).Rows.Count > 0 Then

                strLine = "CREATE PROCEDURE " + txtProcedureName.Text

                StreamWriter1.WriteLine(strLine)

 

 

                'Create Parameter List

                strLine = " @Flag INT = 0"

                StreamWriter1.WriteLine(strLine)

                For i = 0 To ds.Tables(0).Rows.Count - 1

 

                    If ds.Tables(0).Rows(i)("IS_NULLABLE") = "NO" Then

                        If ds.Tables(0).Rows(i)("DATA_TYPE") = "int" Then

                            strLine = " ,@" + ds.Tables(0).Rows(i)("COLUMN_NAME") + " " + ds.Tables(0).Rows(i)("DATA_TYPE") + " = 0"

                        ElseIf ds.Tables(0).Rows(i)("DATA_TYPE") = "varchar" Then

                            strLine = " ,@" + ds.Tables(0).Rows(i)("COLUMN_NAME") + " " + ds.Tables(0).Rows(i)("DATA_TYPE") + "(" + Convert.ToString(ds.Tables(0).Rows(i)("CHARACTER_MAXIMUM_LENGTH")) + ") = ''"

                        ElseIf ds.Tables(0).Rows(i)("DATA_TYPE") = "nvarchar" Then

                            strLine = " ,@" + ds.Tables(0).Rows(i)("COLUMN_NAME") + " " + ds.Tables(0).Rows(i)("DATA_TYPE") + "(" + Convert.ToString(ds.Tables(0).Rows(i)("CHARACTER_MAXIMUM_LENGTH")) + ") = ''"

                        Else

                            strLine = " ,@" + ds.Tables(0).Rows(i)("COLUMN_NAME") + " " + ds.Tables(0).Rows(i)("DATA_TYPE") + " = 0"

                        End If

 

                    Else

                        If ds.Tables(0).Rows(i)("DATA_TYPE") = "int" Then

                            strLine = " ,@" + ds.Tables(0).Rows(i)("COLUMN_NAME") + " " + ds.Tables(0).Rows(i)("DATA_TYPE") + " = NULL"

                        ElseIf ds.Tables(0).Rows(i)("DATA_TYPE") = "varchar" Then

                            strLine = " ,@" + ds.Tables(0).Rows(i)("COLUMN_NAME") + " " + ds.Tables(0).Rows(i)("DATA_TYPE") + "(" + Convert.ToString(ds.Tables(0).Rows(i)("CHARACTER_MAXIMUM_LENGTH")) + ") = NULL"

                        ElseIf ds.Tables(0).Rows(i)("DATA_TYPE") = "nvarchar" Then

                            strLine = " ,@" + ds.Tables(0).Rows(i)("COLUMN_NAME") + " " + ds.Tables(0).Rows(i)("DATA_TYPE") + "(" + Convert.ToString(ds.Tables(0).Rows(i)("CHARACTER_MAXIMUM_LENGTH")) + ") = NULL"

                        Else

                            strLine = " ,@" + ds.Tables(0).Rows(i)("COLUMN_NAME") + " " + ds.Tables(0).Rows(i)("DATA_TYPE") + " = 0"

                        End If

                    End If

 

                    StreamWriter1.WriteLine(strLine)

 

                Next

 

                strLine = "AS"

                StreamWriter1.WriteLine(strLine)

                strLine = "BEGIN --Begining of procedure"

                StreamWriter1.WriteLine(strLine)

 

                strLine = "-------------------------------------------------------------------------------------------------------------------------------------"

                StreamWriter1.WriteLine(strLine)

 

                generateInsertBlock(ds)

                strLine = "-------------------------------------------------------------------------------------------------------------------------------------"

                StreamWriter1.WriteLine(strLine)

                generateUpdateBlock(ds)

                strLine = "-------------------------------------------------------------------------------------------------------------------------------------"

                StreamWriter1.WriteLine(strLine)

                generateDeleteBlock()

                strLine = "-------------------------------------------------------------------------------------------------------------------------------------"

                StreamWriter1.WriteLine(strLine)

                generateSelectBlock(ds)

                strLine = "-------------------------------------------------------------------------------------------------------------------------------------"

                StreamWriter1.WriteLine(strLine)

                generateSelectByIdBlock(ds)

                strLine = "-------------------------------------------------------------------------------------------------------------------------------------"

                StreamWriter1.WriteLine(strLine)

 

                strLine = "END --End of procedure"

                StreamWriter1.WriteLine(strLine)

 

                StreamWriter1.Close()

 

                Dim file As System.IO.FileInfo = New System.IO.FileInfo(Server.MapPath("sp.sql"))

 

                Response.Clear()

                Response.AddHeader("Content-Disposition", "attachment; filename=" & file.Name)

                Response.AddHeader("Content-Length", file.Length.ToString())

                Response.ContentType = "application/octet-stream"

                Response.WriteFile(file.FullName)

                Response.End() 'if file does not exist 

 

            End If

        Catch ex As Exception

            StreamWriter1.Close()

        End Try

 

    End Sub

    Private Function getIdentityColumn() As String

 

        cmd = New SqlCommand()

        Dim dsIdentityColumn As DataSet

        dsIdentityColumn = New DataSet()

        cmd.Connection = conn

        cmd.CommandText = "SELECT name FROM sys.columns WHERE [is_identity] > 0 AND [Object_id] = (SELECT top 1 [object_Id] FROM sys.tables WHERE name = '" + ddlTable.SelectedValue + "')"

        cmd.CommandType = CommandType.Text

        adp = New SqlDataAdapter(cmd)

        adp.Fill(dsIdentityColumn)

 

        If dsIdentityColumn.Tables(0).Rows.Count > 0 Then

            Return dsIdentityColumn.Tables(0).Rows(0)("name")

        Else

            Return ""

        End If

 

    End Function

    Private Function getPrimarykeyColumn() As String

 

        cmd = New SqlCommand()

        Dim dsIdentityColumn As DataSet

        dsIdentityColumn = New DataSet()

        cmd.Connection = conn

        cmd.CommandText = "SELECT  cu.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu WHERE EXISTS ( SELECT tc.* FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc WHERE tc.CONSTRAINT_CATALOG = '" + ddlDatabase.SelectedValue + "' AND tc.TABLE_NAME = '" + ddlTable.SelectedValue + "' AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY' AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME )"

        cmd.CommandType = CommandType.Text

        adp = New SqlDataAdapter(cmd)

        adp.Fill(dsIdentityColumn)

 

        If dsIdentityColumn.Tables(0).Rows.Count > 0 Then

            Return dsIdentityColumn.Tables(0).Rows(0)("COLUMN_NAME")

        Else

            Return ""

        End If

 

    End Function

 

    Private Sub generateInsertBlock(ByVal ds As DataSet)

        strLine = " IF @Flag = 1"

        StreamWriter1.WriteLine(strLine)

        strLine = " BEGIN"

        StreamWriter1.WriteLine(strLine)

 

        'Insert Statement

        strLine = "     INSERT INTO [" + ddlTable.SelectedValue + "]"

        StreamWriter1.WriteLine(strLine)

        strLine = "     ("

        StreamWriter1.WriteLine(strLine)

        Dim flag As Boolean = False

        For i = 0 To ds.Tables(0).Rows.Count - 1

            If strIdentityColumn <> ds.Tables(0).Rows(i)("COLUMN_NAME") Then

                If flag = False Then

                    strLine = "         " + ds.Tables(0).Rows(i)("COLUMN_NAME")

                    flag = True

                Else

                    strLine = "         ," + ds.Tables(0).Rows(i)("COLUMN_NAME")

                End If

                StreamWriter1.WriteLine(strLine)

            End If

        Next

        flag = False

        strLine = "     )"

        StreamWriter1.WriteLine(strLine)

 

        strLine = "     VALUES"

        StreamWriter1.WriteLine(strLine)

        strLine = "     ("

        StreamWriter1.WriteLine(strLine)

 

        For i = 0 To ds.Tables(0).Rows.Count - 1

            If strIdentityColumn <> ds.Tables(0).Rows(i)("COLUMN_NAME") Then

                If flag = False Then

                    strLine = "         @" + ds.Tables(0).Rows(i)("COLUMN_NAME")

                    flag = True

                Else

                    strLine = "         ,@" + ds.Tables(0).Rows(i)("COLUMN_NAME")

                End If

                StreamWriter1.WriteLine(strLine)

            End If

 

        Next

        flag = False

 

        strLine = "     )"

        StreamWriter1.WriteLine(strLine)

 

        strLine = " END  --End of @Flag = 1"

        StreamWriter1.WriteLine(strLine)

    End Sub

    Private Sub generateUpdateBlock(ByVal ds As DataSet)

        strLine = " ELSE IF @Flag = 2"

        StreamWriter1.WriteLine(strLine)

        strLine = " BEGIN"

        StreamWriter1.WriteLine(strLine)

 

        'Insert Statement

        strLine = "     UPDATE  [" + ddlTable.SelectedValue + "]"

        StreamWriter1.WriteLine(strLine)

        Dim flag As Boolean = False

        For i = 0 To ds.Tables(0).Rows.Count - 1

            If strIdentityColumn <> ds.Tables(0).Rows(i)("COLUMN_NAME") Then

                If flag = False Then

                    strLine = "     SET " + ds.Tables(0).Rows(i)("COLUMN_NAME") + " = @" + ds.Tables(0).Rows(i)("COLUMN_NAME")

                    flag = True

                Else

                    strLine = "         ," + ds.Tables(0).Rows(i)("COLUMN_NAME") + " = @" + ds.Tables(0).Rows(i)("COLUMN_NAME")

                End If

                StreamWriter1.WriteLine(strLine)

            End If

        Next

        strLine = "     WHERE " + strIdentityColumn + " = @" + strIdentityColumn

        StreamWriter1.WriteLine(strLine)

 

        strLine = " END --End of @Flag = 2"

        StreamWriter1.WriteLine(strLine)

    End Sub

    Private Sub generateDeleteBlock()

        strLine = " ELSE IF @Flag = 3"

        StreamWriter1.WriteLine(strLine)

        strLine = " BEGIN"

        StreamWriter1.WriteLine(strLine)

 

        'Insert Statement

        strLine = "     DELETE FROM  [" + ddlTable.SelectedValue + "]"

        StreamWriter1.WriteLine(strLine)

 

        strLine = "     WHERE " + strIdentityColumn + " = @" + strIdentityColumn

        StreamWriter1.WriteLine(strLine)

 

        strLine = " END --End of @Flag = 3"

        StreamWriter1.WriteLine(strLine)

    End Sub

    Private Sub generateSelectBlock(ByVal ds As DataSet)

        strLine = " ELSE IF @Flag = 4"

        StreamWriter1.WriteLine(strLine)

        strLine = " BEGIN"

        StreamWriter1.WriteLine(strLine)

 

        'Insert Statement

        strLine = "     SELECT "

        StreamWriter1.WriteLine(strLine)

        For i = 0 To ds.Tables(0).Rows.Count - 1

            If i = 0 Then

                strLine = "         " + ds.Tables(0).Rows(i)("COLUMN_NAME")

            Else

                strLine = "         ," + ds.Tables(0).Rows(i)("COLUMN_NAME")

            End If

            StreamWriter1.WriteLine(strLine)

        Next

        strLine = "     FROM [" + ddlTable.SelectedValue + "]"

        StreamWriter1.WriteLine(strLine)

 

        strLine = "END --End of @Flag = 4"

        StreamWriter1.WriteLine(strLine)

    End Sub

    Private Sub generateSelectByIdBlock(ByVal ds As DataSet)

        strLine = " ELSE IF @Flag = 5"

        StreamWriter1.WriteLine(strLine)

        strLine = " BEGIN"

        StreamWriter1.WriteLine(strLine)

 

        'Insert Statement

        strLine = "     SELECT "

        StreamWriter1.WriteLine(strLine)

        For i = 0 To ds.Tables(0).Rows.Count - 1

            If i = 0 Then

                strLine = "         " + ds.Tables(0).Rows(i)("COLUMN_NAME")

            Else

                strLine = "         ," + ds.Tables(0).Rows(i)("COLUMN_NAME")

            End If

            StreamWriter1.WriteLine(strLine)

        Next

        strLine = "     FROM [" + ddlTable.SelectedValue + "]"

        StreamWriter1.WriteLine(strLine)

        strLine = "     WHERE " + strIdentityColumn + " = @" + strIdentityColumn

        StreamWriter1.WriteLine(strLine)

 

        strLine = " END --End of @Flag = 5"

        StreamWriter1.WriteLine(strLine)

    End Sub

 

End Class