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