update or alter a stored procedure programmatically through C# code

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

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;