Modyfikacja procedury składowanej przy pomocy C#

Witam,

Chciałbym się dowiedzieć, czy istnieje możliwość modyfikacji procedury składowanej przy pomocy C#. Chciałbym za pomocą aplikacji C# mieć możliwość tworzenia nowej bazy MSSQL i potrzebuję, aby przy tym tworzeniu bazy można było w procedurze zmienić w kilku miejscach adres serwera oraz nazwę bazy danych. Nie mogę tego zrobić w SQL wstawiając w te miejsca parametry, ponieważ baza jest zlinkowana z drugą bazą na innym serwerze i nie da się takiej procedury skompilować.

Ręczna zmiana za każdym razem tworzenia nowej bazy odpada.

Ma ktoś pomysł ?

Napisz jak inicjalizujesz tą bazę (migracje z aplikacji .NET, skrypt SQL, coś innego?).

Jeżeli używasz skryptu lub migracji to na bank można tam umiejścić placeholdery, zmienne które będą ustawiane SET XYZ = ... na początku skryptu. sqlcmd też ma opcje ustawiania zmiennych.

Ostatnia rzecz jakiej bym próbował to modyfikacja procedury at tak z kodu aplikacji...

Najprostszy sposób to dodać procedurę do bazy o nazwie SetupDB która dokona niezbędnych modyfikacji, ale nawet tego bał bym się puszczać na produkcyjnej bazie. Można też zrobić tabelkę konfiguracji w DB, i po prostu w nie trzymać ustawienia. Wtedy modyfikacja ustawień to zwykły update. Można dać na tą tabelkę super uprawnienia tak żeby tylko admin mógł to ruszać...

Mozesz spróbować z.dynamicznym sqlem:
https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/

Ale nie zadobry to pomysł żeby trzymać takie rzeczy w kodzie a nie w bazie.

@0xmarcin: Procedura jest w bazie MSSQL. Z aplikacji napisanej w C# uruchamiam tą procedurę.

Jeśli deklaruję w procedurze (w bazie) dodatkowy parametr @NAZWA_SERWERA i następnie uruchamiam EXECUTE w SSMS to są błędy bo tak jak pisałem baza jest zlinkowana z drugą bazą która jest na innym serwerze.

W kodzie procedury jak jest np. EXEC [@NAZWA_SERWERA].[@NAZWA_BAZY].dbo.Tabela1 param1, param2, param3 błąd podczas EXECUTE nie pojawia się, czyli tu by to przeszło, ale jak jest w kilku miejscach np. SELECT @ID=ID FROM [@NAZWA_SERWERA].[@NAZWA_BAZY].dbo.Tabel2 ID to w tych linijkach już jest problem bo nie rozpoznaje nazwy bazy, która jest zlinkowana

OK rozumiem ale to wypadało by raczej z adminem załatwić tak żeby w źródłowej bazie nie było NAZWA_SERWERA w kodzie. Powinno być napisane tak żeby odpalało się wszędzie.

Jak można takiego SELECT zastąpić w kodzie tej procedury ? Ja mam uprawnienia admina w tej bazie, więc tu nie ma problemu, tylko jak ten problem rozwiązać ?

W kodzie procedury są trzy linijki z SELECT, które wyglądają tak:

SELECT @ID=ID FROM [111.111.111.111\TEST].[BAZA_TESTOWA].dbo.Tabel2 
UPDATE [111.111.111.111\TEST].[BAZA_TESTOWA].dbo.TABELA.....

Ja próbuję w miejsce [111.111.111.111\TEST] podstawić parameter @NAZWA_SERWERA oraz w miejsce [BAZA_TESTOWA] podstawić @baza_danych. Niestety nie widzę rozwiązania, ponieważ z poziomu SQL wykonanie przez EXECUTE kończy się błędem, że nie ma zlinkowanego serwera o nazwie @_NAZWA_SERWERA oraz bazie o nazwie @baza_danych.

Z poziomu C# wiem, że można tworzyć taką procedurę (kod procedury trzeba trzymać w kodzie C#), ale procedura nie utworzyć się, gdy nie będzie pierw w bazie SQL zlinkowanej bazy z innego serwera.

Może ktoś mi podpowie jak można wykonać prawidłowo ALTER PROCEDURE z poziomu kodu C#, bo wykorzystując poniższy kod mam błąd (oczywiście procedura jest już w bazie):

public void ZmienProcedure()
{
 
Server srv;
srv = new Server();
 
Database db;
db = srv.Databases["BAZA_TESTOWA"];
 
StoredProcedure sp;
sp = new StoredProcedure(db, "PROCEDURA_TESTOWA");
  
sp.TextMode = false;
sp.AnsiNullsStatus = false;
sp.QuotedIdentifierStatus = false;
 
//StoredProcedureParameter param;
//param = new StoredProcedureParameter(sp, "@ID", DataType.Int);
//sp.Parameters.Add(param);
//StoredProcedureParameter param2;
//param2 = new StoredProcedureParameter(sp, "@ID_M1", DataType.Int);
//sp.Parameters.Add(param2);
//StoredProcedureParameter param3;
//param3 = new StoredProcedureParameter(sp, "@NAZWISKO", DataType.NVarChar(50));
//param3.IsOutputParameter = true;
//sp.Parameters.Add(param3);
  
string stmt;
stmt =
  "SELECT * FROM TABELA1";
sp.TextBody = stmt;
  
//sp.Create();
//Modify a property and run the Alter method to make the change on the instance of SQL Server.   
sp.QuotedIdentifierStatus = true;
sp.Alter();
//usuń stored procedure.   
//sp.Drop();

}

Jakim błędem się kończy? Czy to samo ssms działa poprawnie?

W SQL Studio wszystko jest ok. Poniżej błąd wykonania z poziomu C#:

**Microsoft.SqlServer.Management.Smo.FailedOperationException: „Alter failed for StoredProcedure 'dbo.PROCEDURA_TESTOWA'. ”

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

Powinno być tak:

public void ZmienProcedure()
{

Server srv;
srv = new Server();

Database db;
db = srv.Databases["BAZA_TESTOWA"];

StoredProcedure sp;
sp = new StoredProcedure(db, "PROCEDURA_TESTOWA");

sp.TextMode = false;
sp.AnsiNullsStatus = false;
sp.QuotedIdentifierStatus = false;

sp.Refresh(); //ta linijka dodana

//StoredProcedureParameter param;
//param = new StoredProcedureParameter(sp, "@ID", DataType.Int);
//sp.Parameters.Add(param);
//StoredProcedureParameter param2;
//param2 = new StoredProcedureParameter(sp, "@ID_M1", DataType.Int);
//sp.Parameters.Add(param2);
//StoredProcedureParameter param3;
//param3 = new StoredProcedureParameter(sp, "@NAZWISKO", DataType.NVarChar(50));
//param3.IsOutputParameter = true;
//sp.Parameters.Add(param3);

string stmt;
stmt =
  "SELECT * FROM TABELA1";
sp.TextBody = stmt;

//sp.Create();
//Modify a property and run the Alter method to make the change on the instance of SQL Server.   
sp.QuotedIdentifierStatus = true;

sp.Recompile = true; //ta linijka dodana

sp.Alter();
//usuń stored procedure.   
//sp.Drop();

}