C# alter stored procedure programmatically

Question

I have the following C# code that should allow me to modify (alter) a stored procedure of mine:

SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConnection"].ConnectionString);
ServerConnection srvCon = new ServerConnection(sqlCon);

sqlCon.Open();

Server srv = new Server(srvCon);
Database db = srv.Databases[sqlCon.Database];
StoredProcedure sp = new StoredProcedure(db, "spRDLDataFetcher");

sp.TextMode = false;
sp.AnsiNullsStatus = false;
sp.QuotedIdentifierStatus = false;
sp.TextBody = "SELECT blah FROM MyTable WHERE ID=1";
sp.Alter();

However, the sp.Alter()call throws this error:

Microsoft.SqlServer.Management.Smo.FailedOperationException: 'Alter failed for StoredProcedure 'dbo.spRDLDataFetcher'. '

Inner Exception: InvalidSmoOperationException: You cannot perform operation Alter on an object in state Creating.

What am I missing in order to get it to alter (update) that stored procedure?

Answer 1

Alright so I found out why it was not updating it. Seems, for whatever reason, it needed sp.Refresh(); First before I overwrote the textBody.

SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConnection"].ConnectionString);
ServerConnection srvCon = new ServerConnection(sqlCon);

sqlCon.Open();

Server srv = new Server(srvCon);
Database db = srv.Databases[sqlCon.Database];
StoredProcedure sp = new StoredProcedure(db, "spRDLDataFetcher");

sp.TextMode = false;
sp.AnsiNullsStatus = false;
sp.QuotedIdentifierStatus = true;
sp.ImplementationType = ImplementationType.TransactSql;
sp.Schema = "dbo";

sp.Refresh(); //What was needed to make work
string orgSPText = sp.TextBody;

sp.TextBody = "SELECT blah FROM MyTable WHERE ID=1";
sp.Recompile = true;
sp.Alter();

The sp.Recompile = true; really is not needed. It will work without it but I like to keep that in there just for kicks and giggles.