<%@ 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