Related Tags:
Asp.Net It was developed by Microsoft to allow programmers to build dynamic web sites, web applications and web services. It was first released in January 2002 with version 1.0 of the .NET Framework, and is the successor to Microsoft's Active Server Pages (ASP) technology. Learn More, C# C# (pronounced "C-sharp") is an object-oriented programming language from Microsoft that aims to combine the computing power of C++ with the programming ease of Visual Basic. C# is based on C++ and contains features similar to those of Java. Learn More, SQL SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. Learn More, MSSQL Microsoft SQL Server is a relational database management system, or RDBMS, that supports a wide variety of transaction processing, business intelligence and analytics applications in corporate IT environments. It's one of the three market-leading database technologies, along with Oracle Database and IBM's DB2. Learn More, Stored Procedure A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name, which are stored in a relational database management system as a group, so it can be reused and shared by multiple programs. Learn More,

update or alter a stored procedure programmatically through C# code

Description:I have to update a store procedure on live site and we don't have the access of the database at our development environment so we need to do this through code in C#. I have search a lot but unable to find a proper solution For example ,
I have a following stored procedure,


ALTER PROCEDURE [dbo].[SelectCustomers]
AS
BEGIN
SELECT * from Customer order by Customer.DateCreated DESC
END

And I want to update it to the following one

ALTER PROCEDURE [dbo].[SelectCustomers]
AS
BEGIN
SELECT * from Customer where Customer.Status=1 order by Customer.DateCreated DESC
END

Posted by: | Posted on: May 09, 2019

2 answers

Replies

5

Hi,
The code provided by Peter will update the stored procedure but it also adds WITH RECOMPILE to the stored procedure as well which is obviously unwanted addition, so I recommend to do it by sending the complete query batch using SqlClient. For the purpose you can use the following method.

public void ExecuteNonQueryBatch(string connectionString, string sqlStatements)
{
if (sqlStatements == null) throw new ArgumentNullException("sqlStatements");
if (connectionString == null) throw new ArgumentNullException("connectionString");

using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
Regex r = new Regex(@"^(\s|\t)*go(\s\t)?.*", RegexOptions.Multiline | RegexOptions.IgnoreCase);
foreach (string s in r.Split(sqlStatements))
{
//Skip empty statements, in case of a GO and trailing blanks or something
string thisStatement = s.Trim();
if (String.IsNullOrEmpty(thisStatement)) continue;

using (SqlCommand cmd = new SqlCommand(thisStatement, connection))
{
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
}
}

To make the above updates to the stored procedure you can call the method as shown below.

string strSQLStatements = @"ALTER PROCEDURE [dbo].[SelectCustomers]
AS
BEGIN
SELECT * from Customer where Customer.Status=1 order by Customer.DateCreated DESC
END";
ExecuteNonQueryBatch("YourConnectionString", strSQLStatements);

Replied by: | Replied on: May 13, 2019



2

You can easily achieve by utilizing the following code chunk.

using (SqlConnection connection = new SqlConnection("YourConnectionString"))
{
connection.Open();
ServerConnection srvCon = new ServerConnection(connection);
Server srv = new Server(srvCon);
Database db = srv.Databases[connection.Database];
StoredProcedure sp = new StoredProcedure(db, "SelectCustomers");
sp.TextMode = false;
sp.AnsiNullsStatus = false;
sp.QuotedIdentifierStatus = true;
sp.ImplementationType = ImplementationType.TransactSql;
sp.Schema = "dbo";
sp.Refresh();
sp.TextBody = "SELECT * from Customer where Customer.Status=1 order by Customer.DateCreated DESC";
sp.Alter();
}

Note: For above code you need to add the following namespaces as well.

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

Replied by: | Replied on: May 10, 2019



Reply
×

Preview