none
Save stored procedure in Visual Studio folder and run on sql server RRS feed

  • Question

  • Is there any way i can create Stored Procedure inside Visual Studio c# and Run when i want it rather than Creating inside Sql Server or creating static queries within code.

    Please Suggest..

    Friday, March 29, 2019 9:03 AM

Answers

  • Yes it can be done, no it doesn't make sense in most cases. A stored procedure has to be created on the SQL instance before it can be run. Think of it like the compilation process you'd use for your C# code. Until it is created (compiled) it cannot be called (run).

    Yes you can write C# code to create a procedure in SQL. You'd just use an adhoc query to run the "CREATE PROCEDURE" script. But once it is installed you don't need to install it anymore. This is basically what setup scripts are for. Of course to do that you need sufficient permissions to create procedures which a normal app probably doesn't have. 

    Once the sproc is created you'd just call it normally. I guess you could delete it when you're done but why would you bother? One of the big benefits of a sproc is separating how the DB logic works from your app. Sprocs make it much easier to adjust the DB schema/behavior without breaking your code. Moving data to another table can be hidden behind the sproc without the app ever knowing. Changing the underlying data type of a column becomes easier if the app always sees it the same via the sproc. Sprocs are an encapsulation technique. So putting the encapsulation directly in your app doesn't really make much sense. Your app might as well use adhoc queries instead because you're not gaining any benefit in using sprocs anymore.


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, March 29, 2019 1:57 PM
    Moderator
  • Then you need to look into database migration scripts. Entity Framework supports database migration but you can implement it yourself as well. You'll need to implement versioning in the DB and you'll need to keep your database changes in a migration script that can be replayed on machines that may have differing versions. It isn't trivial but if done careful it works well. You can look at many of the sample projects on GitHub that go this route.

    Also note that to run the scripts the app will need to be run under a user account that has the appropriate permissions.

    https://www.red-gate.com/simple-talk/sql/database-administration/using-migration-scripts-in-database-deployments/

    https://knowledgebase.apexsql.com/migration-scripts-introduction-and-general-review/

    https://stackoverflow.com/questions/6264801/managing-db-migration-scripts-vs-tools


    Michael Taylor http://www.michaeltaylorp3.net

    Monday, April 1, 2019 2:13 PM
    Moderator

All replies

  • Hello,

    Select "Server Explorer" in Visual Studio.

    Select a database, expand to the "Stored Procedures" node, right click, select "Add New Stored Procedure"

    Write your stored procedure. Connect and run via these buttons


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, March 29, 2019 9:50 AM
    Moderator
  • If you have installed Microsoft SQL Server Management Stuido, then if you create a text file that has T-TQL in it and give it a file extension of 'sql' and when you double-click the file SSMS will start and load the T-SQL script into SSMS's Query Execution pane where you can execute the T-SQL script.
    Friday, March 29, 2019 10:12 AM
  • What do i mean is 

    1. the way we write sql queries in c# within double quotes, similarly is there any way, we can write sql scripts within code and call the same way we call stored procedures?

    I know that using server explorer we can connect to sql server and work.

    Friday, March 29, 2019 11:51 AM
  • Yes it can be done, no it doesn't make sense in most cases. A stored procedure has to be created on the SQL instance before it can be run. Think of it like the compilation process you'd use for your C# code. Until it is created (compiled) it cannot be called (run).

    Yes you can write C# code to create a procedure in SQL. You'd just use an adhoc query to run the "CREATE PROCEDURE" script. But once it is installed you don't need to install it anymore. This is basically what setup scripts are for. Of course to do that you need sufficient permissions to create procedures which a normal app probably doesn't have. 

    Once the sproc is created you'd just call it normally. I guess you could delete it when you're done but why would you bother? One of the big benefits of a sproc is separating how the DB logic works from your app. Sprocs make it much easier to adjust the DB schema/behavior without breaking your code. Moving data to another table can be hidden behind the sproc without the app ever knowing. Changing the underlying data type of a column becomes easier if the app always sees it the same via the sproc. Sprocs are an encapsulation technique. So putting the encapsulation directly in your app doesn't really make much sense. Your app might as well use adhoc queries instead because you're not gaining any benefit in using sprocs anymore.


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, March 29, 2019 1:57 PM
    Moderator
  • Point is that, i have developed a c# application and delivered to more than 100 people to use. 

    To provide updates i have to call every one and open management studio and update the procedures.

    C# code can be updated any time but what about Stored Procedures???

    Please suggest....

    Monday, April 1, 2019 5:43 AM
  • Hi

    Thank you for posting here.

    For your question, I want to confirm some questions with you.

    >> delivered to more than 100 people to use.

    How do you deliver the application?

    >> To provide updates i

    What do you want to update, whether it is a database or an application?

    Best regards,

    Jack 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 1, 2019 7:57 AM
    Moderator
  • Just delivered .exe file, it includes database files.

    If updates are related to code then i send .exe file again. If DB related then OMG.....need to share each update separate. Open Management Studio then update the SP 

    Monday, April 1, 2019 8:23 AM
  • Then you need to look into database migration scripts. Entity Framework supports database migration but you can implement it yourself as well. You'll need to implement versioning in the DB and you'll need to keep your database changes in a migration script that can be replayed on machines that may have differing versions. It isn't trivial but if done careful it works well. You can look at many of the sample projects on GitHub that go this route.

    Also note that to run the scripts the app will need to be run under a user account that has the appropriate permissions.

    https://www.red-gate.com/simple-talk/sql/database-administration/using-migration-scripts-in-database-deployments/

    https://knowledgebase.apexsql.com/migration-scripts-introduction-and-general-review/

    https://stackoverflow.com/questions/6264801/managing-db-migration-scripts-vs-tools


    Michael Taylor http://www.michaeltaylorp3.net

    Monday, April 1, 2019 2:13 PM
    Moderator