Create, Alter & Drop Stored Procedures using C#

I always wonder how can one Create, Alter or Drop Stored Procedures using C# and personal user interface. I came up with the solution using SQL Server Management Objects (SMO). It is quite simple using SMOs.

I got a situation in which I usually need to Create & Alter same Stored Procedures on several SQL Servers simultaneously. It was a hectic job to connect each server and then execute the script for creating or altering SP. Solution was quite simple. Below is the step wise solution to the problem.

I named this solution as Stored Procedure Management

Step 1:
Add reference to:
Microsoft.SqlServer.Management.Smo
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.sfc


To Add Reference to given Assemblies, right click your solution and select 'Add reference' and then browse and select the following dlls:

C:\Program Files\Microsoft SQL Server\XXX\SDK\Assemblies\Microsoft.SqlServer.SMO.dll
C:\Program Files\Microsoft SQL Server\XXX\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
C:\Program Files\Microsoft SQL Server\XXX\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.sfc.dll 

*where  XXX is the SQL Server version number (90 or 100).




Step 2:
There should be a Data Source which would provide database credentials for your desired database server(s).

In this example I am retrieving this information from a common admin database server as I am going to use this application to Create/Alter SPs on multiple servers.



Step 3:
Create a page similar to this one:



Its Code Behind is:

<div class="container">
    <table align="left" border="0" cellpadding="0" cellspacing="0" style="width: 900px;">
        <tr style="height: 30px;">
            <td class="fv16 bold" align="center">
                Stored Procedure Management
            </td>
        </tr>
        <tr>
            <td valign="top">
                <asp:ScriptManager AsyncPostBackTimeout="216000" ID="ScriptManager1" runat="server">
                </asp:ScriptManager>
            </td>
        </tr>
        <tr>
            <td align="center" valign="top">
                <asp:UpdatePanel ChildrenAsTriggers="true" ID="UpdatePanel1" runat="server" UpdateMode="Always">
                    <ContentTemplate>
                        <table border="0" cellpadding="0" cellspacing="0">
                            <tbody>
                                <tr>
                                    <td>
                                        <asp:TextBox ID="TxtAdmin" runat="server" TextMode="Password"></asp:TextBox>
                                    </td>
                                </tr>
                                <tr class="h-shad1">
                                    <td class="bo">
                                        <strong>
                                            <asp:CheckBoxList Height="150px" ID="chklstDatabaseServers" RepeatColumns="5" RepeatDirection="Horizontal"
                                                runat="server">
                                            </asp:CheckBoxList>
                                            <asp:CheckBox ID="chkAll" onclick="return checkAllClients();" runat="server" Text="Select All" />
                                        </strong>
                                    </td>
                                </tr>
                                <tr>
                                    <td>
                                    </td>
                                </tr>
                                <tr>
                                    <td align="center">
                                        <asp:UpdateProgress ID="UpdateProgress1" runat="server">
                                            <ProgressTemplate>
                                            </ProgressTemplate>
                                        </asp:UpdateProgress>
                                    </td>
                                </tr>
                                <tr>
                                    <td align="right">
                                        <asp:RadioButtonList ID="rdobtnlstExecutionMode" RepeatDirection="Horizontal" runat="server">
                                            <asp:ListItem Text="Create" Value="1"></asp:ListItem>
                                            <asp:ListItem Selected="True" Text="Alter" Value="2"></asp:ListItem>
                                        </asp:RadioButtonList>
                                    </td>
                                </tr>
                                <tr>
                                    <td>
                                        <b style="font-size: large;">Stored Procedure Name: </b>
                                        <asp:TextBox ID="txtSPName" runat="server" Width="99%"></asp:TextBox>
                                    </td>
                                </tr>
                                <tr>
                                    <td>
                                        <b style="font-size: large;">SP Parameters: </b>(comma separated parameters with
                                        data type, just copt paste only SP Parameters from SQL Server Management Studio)
                                        <b style="color: red;">Note: Nullable parameters are not allowed in this version of
                                            SP Management i.e. (@Student_Code INT = NULL) is not allowed.</b>
                                        <asp:TextBox Height="200px" ID="txtSPParams" runat="server" TextMode="MultiLine"
                                            Width="99%"></asp:TextBox>>
                                    </td>
                                </tr>
                                <tr>
                                    <td>
                                        <b style="font-size: large;">SP Body: </b>(Do not include parameters or create/alter
                                        statement, just write body of your stored procedure)
                                        <asp:TextBox Height="600px" ID="txtSPBody" runat="server" TextMode="MultiLine" Width="99%"></asp:TextBox>
                                    </td>
                                </tr>
                                <tr>
                                    <td>
                                    </td>
                                </tr>
                                <tr>
                                    <td align="center">
                                        <asp:Button ID="btnExecute" OnClick="btnExecute_Click" runat="server" Style="font-size: medium;
                                            font-weight: bold;" Text="Execute" type="submit" Width="150px" />
                                    </td>
                                </tr>
                                <tr>
                                    <td>
                                    </td>
                                </tr>
                                <tr>
                                    <td align="center">
                                        <asp:UpdateProgress ID="UpdateProgress2" runat="server">
                                            <ProgressTemplate>
                                                <img align="middle" alt="" src="http://www.blogger.com/images/Loading-Text-Animation.gif" /></ProgressTemplate>
                                        </asp:UpdateProgress>
                                    </td>
                                </tr>
                                <tr>
                                    <td>
                                    </td>
                                </tr>
                                <tr>
                                    <td align="left">
                                        <asp:Label Font-Bold="true" ID="totalResultsCount" runat="server"></asp:Label>
                                        <div style="overflow: auto; width: 900px;">
                                            <table align="left" border="0" cellpadding="0" cellspacing="0" id="dynamicTable"
                                                runat="server">
                                                <tbody>
                                                </tbody>
                                            </table>
                                        </div>
                                    </td>
                                </tr>
                                <tr>
                                    <td>
                                    </td>
                                </tr>
                                <tr>
                                    <td align="left">
                                        <div id="divSuccess" runat="server" style="color: green;">
                                        </div>
                                        <div id="divError" runat="server" style="color: red;">
                                        </div>
                                    </td>
                                </tr>
                                <tr>
                                    <td>
                                    </td>
                                </tr>
                            </tbody>
                        </table>
                    </ContentTemplate>
                </asp:UpdatePanel>
            </td>
        </tr>
    </table>
</div>


Step 4:
The Code Behind:

C# Code:

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.Text;
using System.Configuration;
using Microsoft.SqlServer.Management.Smo;

namespace StoreProcedureManagement
{
    public partial class SPManagement : System.Web.UI.Page
    {
        #region Variables
        SqlConnection adminConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["YourConnectionStringFromWebConfig"].ConnectionString);
        #endregion

        #region Events

        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                divSuccess.InnerHtml = string.Empty;
                divError.InnerHtml = string.Empty;
                if (!IsPostBack)
                {
                    adminConnection.Open();
                    BindDatabaseClients();
                }
            }
            catch (Exception ex)
            {
                divError.InnerHtml += " " + ex.Message;
            }
            finally
            {
                adminConnection.Close();
            }
        }

        protected void btnExecute_Click(object sender, EventArgs e)
        {
            try
            {
                StringBuilder strbSuccessClientName;
                StringBuilder strbFailedClientName;
                int countSuccess = 0;
                int countAccessed = 0;
                //Check for Restricted Instructions
                if (CheckString(txtSPBody.Text.ToLower()))
                {
                    strbSuccessClientName = new StringBuilder();
                    strbFailedClientName = new StringBuilder();
                    DataTable dtClients = (DataTable)ViewState["dtClients"];

                    for (int i = 0; i > chklstDatabaseServers.Items.Count; i++)
                    {
                        if (chklstDatabaseServers.Items[i].Selected)
                        {
                            countAccessed++;
                            string connectionString = string.Empty;
                            DataTable dtClient = new DataTable();
                            FilterTable(dtClients, ref dtClient, "Client_Code = " + chklstDatabaseServers.Items[i].Value);

                            connectionString = dtClient.Rows[0]["Client_Database_Credentials"].ToString();

                            bool errorOccured = ExecuteSP(connectionString, dtClient.Rows[0]["Client_Name"].ToString());
                            if (errorOccured)
                            {
                                strbFailedClientName.Append(dtClient.Rows[0]["Client_Name"].ToString() + "");
                            }
                            else
                            {
                                strbSuccessClientName.Append(dtClient.Rows[0]["Client_Name"].ToString() + "");
                                countSuccess++;
                            }
                        }
                    }
                    if (countSuccess > 0)
                        divSuccess.InnerHtml = "Successfully executed on following client(s): " + strbSuccessClientName.ToString();

                    if (!string.IsNullOrEmpty(strbFailedClientName.ToString()))
                        divError.InnerHtml = "Error Occured On Following Client(s): " + strbFailedClientName.ToString();
                }
            }
            catch (Exception ex)
            {
                divError.InnerHtml = "Following Error Occured : " + Environment.NewLine + ex.Message;
            }
        }

        #endregion

        #region Methods

        private void BindDatabaseClients()
        {
            DataTable dtClients = new DataTable();
            BindAllClients(adminConnection, ref dtClients);

            ViewState["dtClients"] = dtClients;

            chklstDatabaseServers.DataSource = dtClients;
            chklstDatabaseServers.DataTextField = "Client_Name";
            chklstDatabaseServers.DataValueField = "Client_Code";
            chklstDatabaseServers.DataBind();

        }

        protected bool CheckString(string str)
        {

            if (!TxtAdmin.Text.Equals("AdminTalha"))
            {
                ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('You are not authorized to use this screen')", true);
                return false;
            }
            else
            {
                char[] delimiters = new char[] { '\r', '\n', ' ', ';', ':', '"', ',', '!', '@', '#', '$', '%', '^', '&', '*', '(', ')', '-', '_', '+', '=', '~', '`', '{', '}', '[', ']', '|', '\\', '<', '>', '?', '/' };
                string[] strA = str.Split(delimiters);
                if (strA.Contains("alter proc"))
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('alter procedure command is not allowed')", true);
                    return false;
                }
                else if (strA.Contains("create proc"))
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('create procedure command is not allowed')", true);
                    return false;
                }
                else if (string.IsNullOrEmpty(txtSPName.Text))
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('SP Name cannot be empty')", true);
                    return false;
                }
                else if (string.IsNullOrEmpty(txtSPBody.Text))
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('SP Body cannot be empty')", true);
                    return false;
                }
            }

            //its ok
            return true;
        }

        private bool ExecuteSP(string connectionString, string clientName)
        {
            bool error = false;
            SqlConnection sqlCon = new SqlConnection(connectionString);
            Microsoft.SqlServer.Management.Common.ServerConnection srvCon = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlCon);

            try
            {
                sqlCon.Open();
                //objSql
                Server srv = new Server(srvCon);

                //Reference the database.
                Database db = srv.Databases[sqlCon.Database];

                //Define a StoredProcedure object variable by supplying the parent database and name arguments in the constructor.
                StoredProcedure sp = null;

                if (rdobtnlstExecutionMode.SelectedValue == "1")
                    sp = new StoredProcedure(db, txtSPName.Text);
                else if (rdobtnlstExecutionMode.SelectedValue == "2" || rdobtnlstExecutionMode.SelectedValue == "3")
                    sp = db.StoredProcedures[txtSPName.Text];

                if (sp == null)
                {
                    sqlCon.Close();
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Stored Procedure Not Found in '" + clientName + "')", true);
                    error = true;
                }
                else
                {
                    //Set the TextMode property to false and then set the other object properties.
                    sp.AnsiNullsStatus = false;
                    sp.QuotedIdentifierStatus = false;
                    sp.TextMode = false;


                    #region SP Parameters

                    if (!string.IsNullOrEmpty(txtSPParams.Text))
                    {
                        char[] splitter = { ',' };
                        char[] internalSplitter = { ' ' };
                        string[] spParameters = txtSPParams.Text.Replace(Environment.NewLine, string.Empty).Replace("\n", string.Empty).Split(splitter, StringSplitOptions.RemoveEmptyEntries);

                        foreach (string spParam in spParameters)
                        {
                            string[] p = spParam.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries);

                            string paramName = p[0];
                            string paramDataType = p[1].ToLower();

                            Microsoft.SqlServer.Management.Smo.DataType thisDt = null;

                            int scaleLength = 0;
                            int precision = 0;

                            string intermediateDT = paramDataType;
                            if (paramDataType.Contains("varchar"))
                            {
                                paramDataType = paramDataType.Replace("(", " ");
                                paramDataType = paramDataType.Replace(")", " ");
                                paramDataType = paramDataType.Replace(",", " ");
                                paramDataType = paramDataType.Replace("  ", " ");

                                scaleLength = Convert.ToInt32((paramDataType.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries))[1]);
                                paramDataType = "varchar";
                            }
                            else if (paramDataType.Contains("decimal"))
                            {
                                paramDataType = paramDataType.Replace("(", " ");
                                paramDataType = paramDataType.Replace(")", " ");
                                paramDataType = paramDataType.Replace(",", " ");
                                paramDataType = paramDataType.Replace("  ", " ");

                                scaleLength = Convert.ToInt32((paramDataType.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries))[1]);
                                precision = Convert.ToInt32((paramDataType.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries))[2]);
                                paramDataType = "decimal";
                            }
                            else if (paramDataType.Contains("numeric"))
                            {
                                paramDataType = paramDataType.Replace("(", " ");
                                paramDataType = paramDataType.Replace(")", " ");
                                paramDataType = paramDataType.Replace(",", " ");
                                paramDataType = paramDataType.Replace("  ", " ");

                                scaleLength = Convert.ToInt32((paramDataType.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries))[1]);
                                precision = Convert.ToInt32((paramDataType.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries))[2]);
                                paramDataType = "numeric";
                            }

                            switch (paramDataType)
                            {
                                case "int":
                                    thisDt = DataType.Int;
                                    break;
                                case "bit":
                                    thisDt = DataType.Bit;
                                    break;
                                case "text":
                                    thisDt = DataType.Text;
                                    break;
                                case "datetime":
                                    thisDt = DataType.DateTime;
                                    break;
                                case "bigint":
                                    thisDt = DataType.BigInt;
                                    break;
                                case "varchar":
                                    thisDt = DataType.VarChar(scaleLength);
                                    paramDataType = intermediateDT;
                                    break;
                                case "decimal":
                                    thisDt = DataType.Decimal(scaleLength, precision);
                                    paramDataType = intermediateDT;
                                    break;
                                case "numeric":
                                    thisDt = DataType.Numeric(scaleLength, precision);
                                    paramDataType = intermediateDT;
                                    break;
                                default:
                                    ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('SP Parameter with unknown datatype found.')", true);
                                    error = true;
                                    break;
                            }
                            if (error)
                            {
                                sqlCon.Close();
                                return error;
                            }

                            sp.Parameters.Add(new StoredProcedureParameter(sp, paramName, thisDt));
                        }
                    }

                    #endregion

                    //Set the TextBody property to define the stored procedure.
                    sp.TextBody = txtSPBody.Text.Replace("\n", Environment.NewLine);

                    if (rdobtnlstExecutionMode.SelectedValue == "1")
                    {
                        sp.QuotedIdentifierStatus = true;
                        //Create the stored procedure on the instance of SQL Server.
                        sp.Create();
                    }
                    else if (rdobtnlstExecutionMode.SelectedValue == "2")
                    {
                        sp.QuotedIdentifierStatus = true;
                        //Modify a property and run the Alter method to make the change on the instance of SQL Server.
                        sp.Alter();
                    }
                    else if (rdobtnlstExecutionMode.SelectedValue == "3")
                    {
                        //Remove the stored procedure.
                        sp.Drop();
                    }
                }
            }
            catch (Exception ex)
            {
                sqlCon.Close();
                divError.InnerHtml = "Following Error Occured : " + Environment.NewLine + ex.Message;
            }
            return error;
        }
        public static void FilterTable(DataTable dtSource, ref DataTable dtFiltered, string filterString)
        {
            dtFiltered = dtSource.Clone();
            dtSource.DefaultView.RowFilter = filterString;
            dtFiltered = dtSource.DefaultView.ToTable();
        }
        public static void BindAllClients(SqlConnection sqlConnection, ref DataTable dtClients)
        {
            SqlCommand command;
            command = new SqlCommand();
            command.Connection = sqlConnection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "PL_Select_AllDatabaseServersCredentials";
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(dtClients);
        }

        #endregion
    }
}


VB.NET Code:

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text
Imports System.Configuration
Imports Microsoft.SqlServer.Management.Smo

Namespace StoreProcedureManagement
    Partial Public Class SPManagement
        Inherits System.Web.UI.Page
#Region "Variables"
        Private adminConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("YourConnectionStringFromWebConfig").ConnectionString)
#End Region

#Region "Events"

        Protected Sub Page_Load(sender As Object, e As EventArgs)
            Try
                divSuccess.InnerHtml = String.Empty
                divError.InnerHtml = String.Empty
                If Not IsPostBack Then
                    adminConnection.Open()
                    BindDatabaseClients()
                End If
            Catch ex As Exception
                divError.InnerHtml += " " + ex.Message
            Finally
                adminConnection.Close()
            End Try
        End Sub

        Protected Sub btnExecute_Click(sender As Object, e As EventArgs)
            Try
                Dim strbSuccessClientName As StringBuilder
                Dim strbFailedClientName As StringBuilder
                Dim countSuccess As Integer = 0
                Dim countAccessed As Integer = 0
                'Check for Restricted Instructions
                If CheckString(txtSPBody.Text.ToLower()) Then
                    strbSuccessClientName = New StringBuilder()
                    strbFailedClientName = New StringBuilder()
                    Dim dtClients As DataTable = DirectCast(ViewState("dtClients"), DataTable)

                    Dim i As Integer = 0
                    While i > chklstDatabaseServers.Items.Count
                        If chklstDatabaseServers.Items(i).Selected Then
                            countAccessed += 1
                            Dim connectionString As String = String.Empty
                            Dim dtClient As New DataTable()
                            FilterTable(dtClients, dtClient, "Client_Code = " + chklstDatabaseServers.Items(i).Value)

                            connectionString = dtClient.Rows(0)("Client_Database_Credentials").ToString()

                            Dim errorOccured As Boolean = ExecuteSP(connectionString, dtClient.Rows(0)("Client_Name").ToString())
                            If errorOccured Then
                                strbFailedClientName.Append(dtClient.Rows(0)("Client_Name").ToString() + "")
                            Else
                                strbSuccessClientName.Append(dtClient.Rows(0)("Client_Name").ToString() + "")
                                countSuccess += 1
                            End If
                        End If
                        i += 1
                    End While
                    If countSuccess > 0 Then
                        divSuccess.InnerHtml = "Successfully executed on following client(s): " + strbSuccessClientName.ToString()
                    End If

                    If Not String.IsNullOrEmpty(strbFailedClientName.ToString()) Then
                        divError.InnerHtml = "Error Occured On Following Client(s): " + strbFailedClientName.ToString()
                    End If
                End If
            Catch ex As Exception
                divError.InnerHtml = "Following Error Occured : " + Environment.NewLine + ex.Message
            End Try
        End Sub

#End Region

#Region "Methods"

        Private Sub BindDatabaseClients()
            Dim dtClients As New DataTable()
            BindAllClients(adminConnection, dtClients)

            ViewState("dtClients") = dtClients

            chklstDatabaseServers.DataSource = dtClients
            chklstDatabaseServers.DataTextField = "Client_Name"
            chklstDatabaseServers.DataValueField = "Client_Code"
            chklstDatabaseServers.DataBind()

        End Sub

        Protected Function CheckString(str As String) As Boolean

            If Not TxtAdmin.Text.Equals("AdminTalha") Then
                ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "alert", "alert('You are not authorized to use this screen')", True)
                Return False
            Else
                Dim delimiters As Char() = New Char() {ControlChars.Cr, ControlChars.Lf, " "c, ";"c, ":"c, """"c, _
                 ","c, "!"c, "@"c, "#"c, "$"c, "%"c, _
                 "^"c, "&"c, "*"c, "("c, ")"c, "-"c, _
                 "_"c, "+"c, "="c, "~"c, "`"c, "{"c, _
                 "}"c, "["c, "]"c, "|"c, "\"c, "<"c, _
                 ">"c, "?"c, "/"c}
                Dim strA As String() = str.Split(delimiters)
                If strA.Contains("alter proc") Then
                    ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "alert", "alert('alter procedure command is not allowed')", True)
                    Return False
                ElseIf strA.Contains("create proc") Then
                    ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "alert", "alert('create procedure command is not allowed')", True)
                    Return False
                ElseIf String.IsNullOrEmpty(txtSPName.Text) Then
                    ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "alert", "alert('SP Name cannot be empty')", True)
                    Return False
                ElseIf String.IsNullOrEmpty(txtSPBody.Text) Then
                    ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "alert", "alert('SP Body cannot be empty')", True)
                    Return False
                End If
            End If

            'its ok
            Return True
        End Function

        Private Function ExecuteSP(connectionString As String, clientName As String) As Boolean
            Dim [error] As Boolean = False
            Dim sqlCon As New SqlConnection(connectionString)
            Dim srvCon As New Microsoft.SqlServer.Management.Common.ServerConnection(sqlCon)

            Try
                sqlCon.Open()
                'objSql
                Dim srv As New Server(srvCon)

                'Reference the database.
                Dim db As Database = srv.Databases(sqlCon.Database)

                'Define a StoredProcedure object variable by supplying the parent database and name arguments in the constructor.
                Dim sp As StoredProcedure = Nothing

                If rdobtnlstExecutionMode.SelectedValue = "1" Then
                    sp = New StoredProcedure(db, txtSPName.Text)
                ElseIf rdobtnlstExecutionMode.SelectedValue = "2" OrElse rdobtnlstExecutionMode.SelectedValue = "3" Then
                    sp = db.StoredProcedures(txtSPName.Text)
                End If

                If sp Is Nothing Then
                    sqlCon.Close()
                    ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "alert", "alert('Stored Procedure Not Found in '" + clientName + "')", True)
                    [error] = True
                Else
                    'Set the TextMode property to false and then set the other object properties.
                    sp.AnsiNullsStatus = False
                    sp.QuotedIdentifierStatus = False
                    sp.TextMode = False


                    '#Region "SP Parameters"

                    If Not String.IsNullOrEmpty(txtSPParams.Text) Then
                        Dim splitter As Char() = {","c}
                        Dim internalSplitter As Char() = {" "c}
                        Dim spParameters As String() = txtSPParams.Text.Replace(Environment.NewLine, String.Empty).Replace(vbLf, String.Empty).Split(splitter, StringSplitOptions.RemoveEmptyEntries)

                        For Each spParam As String In spParameters
                            Dim p As String() = spParam.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries)

                            Dim paramName As String = p(0)
                            Dim paramDataType As String = p(1).ToLower()

                            Dim thisDt As Microsoft.SqlServer.Management.Smo.DataType = Nothing

                            Dim scaleLength As Integer = 0
                            Dim precision As Integer = 0

                            Dim intermediateDT As String = paramDataType
                            If paramDataType.Contains("varchar") Then
                                paramDataType = paramDataType.Replace("(", " ")
                                paramDataType = paramDataType.Replace(")", " ")
                                paramDataType = paramDataType.Replace(",", " ")
                                paramDataType = paramDataType.Replace("  ", " ")

                                scaleLength = Convert.ToInt32((paramDataType.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries))(1))
                                paramDataType = "varchar"
                            ElseIf paramDataType.Contains("decimal") Then
                                paramDataType = paramDataType.Replace("(", " ")
                                paramDataType = paramDataType.Replace(")", " ")
                                paramDataType = paramDataType.Replace(",", " ")
                                paramDataType = paramDataType.Replace("  ", " ")

                                scaleLength = Convert.ToInt32((paramDataType.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries))(1))
                                precision = Convert.ToInt32((paramDataType.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries))(2))
                                paramDataType = "decimal"
                            ElseIf paramDataType.Contains("numeric") Then
                                paramDataType = paramDataType.Replace("(", " ")
                                paramDataType = paramDataType.Replace(")", " ")
                                paramDataType = paramDataType.Replace(",", " ")
                                paramDataType = paramDataType.Replace("  ", " ")

                                scaleLength = Convert.ToInt32((paramDataType.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries))(1))
                                precision = Convert.ToInt32((paramDataType.Split(internalSplitter, StringSplitOptions.RemoveEmptyEntries))(2))
                                paramDataType = "numeric"
                            End If

                            Select Case paramDataType
                                Case "int"
                                    thisDt = DataType.Int
                                    Exit Select
                                Case "bit"
                                    thisDt = DataType.Bit
                                    Exit Select
                                Case "text"
                                    thisDt = DataType.Text
                                    Exit Select
                                Case "datetime"
                                    thisDt = DataType.DateTime
                                    Exit Select
                                Case "bigint"
                                    thisDt = DataType.BigInt
                                    Exit Select
                                Case "varchar"
                                    thisDt = DataType.VarChar(scaleLength)
                                    paramDataType = intermediateDT
                                    Exit Select
                                Case "decimal"
                                    thisDt = DataType.[Decimal](scaleLength, precision)
                                    paramDataType = intermediateDT
                                    Exit Select
                                Case "numeric"
                                    thisDt = DataType.Numeric(scaleLength, precision)
                                    paramDataType = intermediateDT
                                    Exit Select
                                Case Else
                                    ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "alert", "alert('SP Parameter with unknown datatype found.')", True)
                                    [error] = True
                                    Exit Select
                            End Select
                            If [error] Then
                                sqlCon.Close()
                                Return [error]
                            End If

                            sp.Parameters.Add(New StoredProcedureParameter(sp, paramName, thisDt))
                        Next
                    End If

                    '#End Region

                    'Set the TextBody property to define the stored procedure.
                    sp.TextBody = txtSPBody.Text.Replace(vbLf, Environment.NewLine)

                    If rdobtnlstExecutionMode.SelectedValue = "1" Then
                        sp.QuotedIdentifierStatus = True
                        'Create the stored procedure on the instance of SQL Server.
                        sp.Create()
                    ElseIf rdobtnlstExecutionMode.SelectedValue = "2" Then
                        sp.QuotedIdentifierStatus = True
                        'Modify a property and run the Alter method to make the change on the instance of SQL Server.
                        sp.Alter()
                    ElseIf rdobtnlstExecutionMode.SelectedValue = "3" Then
                        'Remove the stored procedure.
                        sp.Drop()
                    End If
                End If
            Catch ex As Exception
                sqlCon.Close()
                divError.InnerHtml = "Following Error Occured : " + Environment.NewLine + ex.Message
            End Try
            Return [error]
        End Function
        Public Shared Sub FilterTable(dtSource As DataTable, ByRef dtFiltered As DataTable, filterString As String)
            dtFiltered = dtSource.Clone()
            dtSource.DefaultView.RowFilter = filterString
            dtFiltered = dtSource.DefaultView.ToTable()
        End Sub
        Public Shared Sub BindAllClients(sqlConnection As SqlConnection, ByRef dtClients As DataTable)
            Dim command As SqlCommand
            command = New SqlCommand()
            command.Connection = sqlConnection
            command.CommandType = CommandType.StoredProcedure
            command.CommandText = "PL_Select_AllDatabaseServersCredentials"
            Dim adapter As New SqlDataAdapter(command)
            adapter.Fill(dtClients)
        End Sub

#End Region
    End Class
End Namespace


And that’s that. You are now free to use this web page to Create, Alter or Drop Stored Procedures using C# even on multiple database servers.


Happy Coding!


PS: To ensure authenticated Creation or Alteration of Stored Procedures, a textbox on Top Left of the page is used to enter a "hard-coded" password. (Check code behind for it.)

Popular Posts