Visual Studio 2013 CLR Stored Procedures

sql-server stored-procedures visual-studio-2013 sqlclr
John picture John · Aug 30, 2014 · Viewed 10.3k times · Source

I have been working with earlier versions of Visual Studio (VS2008) in conjunction with SQL Server 2008.

Recently my organization moved to VS2013 and SQL Server 2012. I am finding the database interfaces in VS2013 quite a bit different than what was present in VS2008.

My question is basically if anyone knows of an article or referenced that details how to do what is described here:

http://yassershaikh.com/how-to-create-a-clr-stored-procedure-using-c-and-visual-studio/

(Specifically the deployment of the DLL to a SQL Server 2012 database).

But using VS2013 and SQL Server 2012.

Answer

Solomon Rutzky picture Solomon Rutzky · Sep 23, 2014

(In case of minor differences between the info here and what you are seeing, I am running Visual Studio 2012 Update 4 with SSDT 11.1.31203.1 on Windows 8)

In VS 2008 / VS 2010 you could "Deploy" a database project (i.e. .csproj / .dbproj file, respectively) to get the assembly and T-SQL wrapper objects into SQL Server. The newer versions of Visual Studio still have "Deploy" options in the "Build" menu (at least VS 2012 has them in for both the solution and the project), but they do not do anything. You now have a .sqlproj file and the way to get the solution/project into the database is to use the "Publish {SolutionName}..." option in the "Build" menu.

More on publishing in a moment. First, here is some info that might help:

SQL Server / SSDT

  • SQL Server projects (i.e. .sqlproj) are explicitly handled via SQL Server Data Tools (SSDT)
  • Visual Studio / SSDT, by default, points to a local SQL Server database. The preference had been to use SQL Server Express Edition (also see SQL Server Express User Instances), but starting in SQL Server 2012 there was a new variation of Express Edition called "Express Edition LocalDB" (commonly referred to as just "LocalDB")
  • The SQL Server Express LocalDB instance is located in the following path:
    C:\Users\{User Name}\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\{Instance Name}
  • You connect to LocalDB using the following convention:
    (localdb)\{Instance Name}
  • When a new solution is created, a folder to house the database-per-project files is created in:
    C:\Users\{User Name}\AppData\Local\Microsoft\VisualStudio\SSDT\{Solution Name}
  • Within that ...\SSDT\{Solution Name} folder will be at least two files per project:
    • {Project Name}.mdf
    • {Project Name}.ldf
  • If you add more projects to that solution, new files will appear in that ...\SSDT\{Solution Name} folder
  • The solution folder and per-project-database files are created as you initial create the new project, whether you ever use them or not ;-)

Visual Studio

  • There are 3 main tabs within "Project Properties" that will control what is scripted and possibly published
  • Project Settings
    • Under "Output Types", the "Create script (.sql file)" check-box controls whether or not the Build operation creates the SQL script to drop and recreate DB with at least the Assembly:
      {Build Output Path}\{Build Output File Name}_Create.sql
  • SQL CLR
    • The "Generate DDL" check-box controls whether or not to create the T-SQL wrapper objects:
      CREATE {Object Type} {Object Name} ... AS EXTERNAL NAME {Assembly Name}.{Class Name}.{Method Name OR "name" property, if specified, in the [SqlProcedure] / [SqlFunction] / [SqlUserDefinedAggregate] / [SqlUserDefinedType] / [SqlTrigger] attribute}
    • The "Include Symbols" check-box controls whether or not to import the {Assembly Name}.pdb file into SQL Server (in sys.assembly_files); the .pdb file will be created in the {Build Output Path} even if this option is unchecked.
  • Build
    • These are not SQLCLR specific
    • "Build output path:" controls where the finalized DLL, PDB, SQL, and DACPAC files will be located
    • "Build output file name:" controls the name of the SQL scripts and the DACPAC file
  • The Deploy tab has been removed
    • This tab had a check-box for "Deploy Code" that would include the .pdb file and the source code (.cs and/or .vb.) files as ALTER ASSEMBLY {Assembly Name} ADD FILE FROM 0x... AS {File Name} entries. This option seems to have been replaced by the "Include Symbols" check-box on the SQL CLR tab which only includes the .pdb file and not the source code files. [I see this as an improvement as I never liked the idea of deploying the source code since some people (maybe many?) promoted all of that stuff into Production :-(.]

Selecting the "Publish {Solution Name}..." option in the "Build" menu will bring up a "Publish Database" dialog asking for database details. For "Target database connection:", click the "Edit..." button to the right. A dialog for "Connection Properties" will open. There will be a drop-down for "Server name:" that you can either pull down to discover instances or just type in the name. If you have a default instance running locally, you should be able to just type in "(local)" [but without the double-quotes]. The default authentication in the next section, "Log on to the server", is "Use Windows Authentication". If you are using a SQL Server login then you can select the other radio button and enter in the "User name:" and "Password:" details. The next section is "Connect to a database" and the top radio-button for "Select or enter a database name:" is already selected. There is a drop-down that, if pulled down, will populate with a list of databases on the server that was selected (or entered) in the "Server name:" drop-down. Select, or enter in, the desired database and click the "Test Connection" button on the bottom left of the dialog. You should get a pop-up stating "Test connection succeeded" so click the OK button in that pop-up and then the "OK" button in the "Connection Properties" dialog.

Now you can:

  1. Click the "Save Profile As..." button (bottom left of the "Publish Database" dialog)
  2. Next time, just click the "Load Profile..." button (it will have the visible settings as well as whatever is configured within the "Advanced..." button)
  3. Click the
    • "Publish" button to push immediately to the database
    • "Generate Script" button to simply create the publication SQL script:
      {Build Output Path}\{Build Output File Name}.publish.sql
  4. You can also deploy the DACPAC file: Data-tier Applications

Just FYI: the base T-SQL pieces to create the objects can be found in the following location, but you should be careful not to run the SQL script as is since the items are not in a runnable order:
{ProjectDir}\obj\{Configuration Name}\{Assembly Name}.generated.sql

For several of the Visual Studio-specific references above, those are the names of the fields that you see in the UI. Their equivalents within the .sqlproj file are:

  • {Project Name} == <Name>
  • {Assembly Name} == <AssemblyName>
  • {Build Output Path} == <OutputPath>
  • {Build Output File Name} == <SqlTargetName>
  • "Create script (.sql file)" check-box == <GenerateCreateScript>
  • "Generate DDL" check-box == <GenerateSqlClrDdl>
  • "Include Symbols" check-box == <GenerateSqlClrSymbols>

For more information on SQLCLR in general, I am writing a series on SQL Server Central (free registration required) called "Stairway to SQLCLR". (Currently there are only 4 articles published out of what will eventually be about 12. The next one, coincidentally, is on Development & Visual Studio :-) )

EDIT:
One interesting, and mostly good, new feature is the verification of dependencies to give compile-time errors of T-SQL objects, just like the .Net languages have. The downside is that there doesn't seem to be a way to disable it. This doesn't usually affect SQL CLR projects, except in the case of CLR-based triggers. The problem here is that if you have the "Generate DDL" option enabled, in order to do the "Build" you need to have the Target and Event properties of the SqlTrigger attribute filled out. And, the model (i.e. database model) verification step will check to make that the table named in the Target property actually exists. If not, you will get an unresolved reference to object error. The only way to get passed this, it seems, is to a) have that object in your project, or b) have that object in a project that is referenced as a "Database Reference":

  • If you have only a few tables needing to be referenced, it could be easiest to just generate their CREATE TABLE statements in SSMS, and then right-click on the project in Solution Explorer (or go to the "Project" menu) and go to "Import -> Script (*.sql)...".
  • If you have lots of triggers on lots of different tables, then you can:
    • Create a new project in the same solution:
      • Right-click on project name in Solution Explorer (or go to Project menu)
      • Go to "Import ->"
      • Select "Database..."
      • Enter the connection info
    • Extract the schema to a .dacpac file:
      • Go to "SQL Server Object Explorer" in VS
      • Select your server and expand the list of databases
      • Right-click on the database in question
      • Select "Extract Data-tier application..."
      • Choose a location and enter in a file name (in the "File on disk" field).
      • Click the "OK" button
    • Whichever method you chose, you need to add it as a "Database Reference":
      • Right-click on the "References" folder in your main project (or go to the "Project" menu)
      • Go to "Add Database Reference..."
      • Depending on which method you chose, pick either "Database projects in the current solution" or "Data-tier Application (.dacpac)"
      • Chose the newly created project or .dacpac file
      • For the "Database location:" drop-down, you must select "Same database"!
      • Click the "OK" button

Keep in mind:

  • If importing individual tables you will likely need to also import any tables referenced in any foreign keys that might be on any table being imported
  • The full CREATE TABLE DDL will be generated in the _Create script each time, but the "Publish" script will only contain DDL for changes, and so if there are no changes in the definition of the table, then the table DDL will not appear in the "Publish" .sql script. Changes are determined by comparing the table definition in your project or .dacpac file with the destination database defined in the Connection Settings of the "Publish" dialog / wizard.

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More