Create, Alter & Drop Stored Procedures using C#

Posted by Talha June 10, 2011

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:

Stored Procedure Management

</td>

</tr>

<tr>

<asp:ScriptManager AsyncPostBackTimeout="216000" ID="ScriptManager1" runat="server">

</asp:ScriptManager>

</td>

</tr>

<tr>

<asp:UpdatePanel ChildrenAsTriggers="true" ID="UpdatePanel1" runat="server" UpdateMode="Always">

<tbody>

<tr>

<td>

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

</td>

</tr>

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

<asp:UpdateProgress ID="UpdateProgress1" runat="server">

</ProgressTemplate>

</asp:UpdateProgress>

</td>

</tr>

<tr>

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

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

<asp:UpdateProgress ID="UpdateProgress2" runat="server">

</asp:UpdateProgress>

</td>

</tr>

<tr>

<td>

</td>

</tr>

<tr>

<asp:Label Font-Bold="true" ID="totalResultsCount" runat="server"></asp:Label>

<table align="left" border="0" cellpadding="0" cellspacing="0" id="dynamicTable"

runat="server">

<tbody>

</tbody>

</table>

</div>

</td>

</tr>

<tr>

<td>

</td>

</tr>

<tr>

</div>

</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

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

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

'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))

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

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.)

Search This Blog

Code Shode

Create, Alter & Drop Stored Procedures using C#

Popular Posts

Validate File Extension for ASP.NET FileUpload Control

MS SQL Server Function to Insert Line Break in String