In my company there is a sophisticate logic layer in Sql Server DB. In order to write tests that will check this code we need to write some T-SQL code. The test methods including a serious of initialization script before and after each one of them. I’m going to share you with a very helpful way to debuging this important code by using SQLCLR abilities.The first thing we should do is to open a new Sql Database Project.
Create a new SQL Server Project, by adding a new project and selecting SQL Server Project Template.
Then we need to add our stored procedure that will run inside the db(using SQL-CLR engine). Lets write a very simple stored procedure (It help us to focus on the global concept).
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertCustomerStoredProcedure(SqlString name)
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "insert into customers(name) values(@name)";
command.Parameters.Add(new SqlParameter("name",name));
connection.Open();
command.ExecuteNonQuery();
}
}
};
The next step will be a compliation and deploy to the central db.In order to test this code we should used a sql file from the “Test script” library(One of the solution directories).
I added an executable command there. This command is excute our stored procedure:
As you can see, I added a breakpoint in the stored procedure execution line. When I will run this project in “debug” mode the visual studio will stop here.
Ok, something is missing. When I tried to execute it I got this error:Msg 6263, Level 16, State 1, Line 6
Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option. This stored procedure will get a customer name as an input and insert it into the customers table.
That's means that we must enable the SQLCLR debug option in our DB if we want to debug code that is being stored inside it.
The “flag” that indicates about this configuration key exist inside the db. You can look at the current Sql CLR flag by execute this command.
In order to enable the SQL CLR debugging just executes this stored procedure:
exec sp_configure 'clr enabled', 1 reconfigure with override
It's time to start debugging our code. Click the “start debuging” button. The visual studio will stop on the fisrt breakpoint. And after we click on the “Step into” button we will enter directly into our stored procedure implementation but this time in debug mode.
When I'll open the Customers table, we will see that the insertion operation that has been executed in the stored procedure was succeeded.
Furthermore, inside a test script file you can also debug existing stored procedures even if they have been created as normal stored procedures directly in the db. You can step into them in debug mode and see all the parameters that you gave them in the watch window.
So, if you need to debug a sophisticated code that was written in stored procedures it’s really recommended to use this debug abilities to track the flow easily.
In this post, I want to share with you a great solution to one of the biggest problem in the development life cycle. Almost every system work with a data. We usually store it in db and when we do it we will get all db capabilities (Multiple users, Security, Performance, and much more). But one of the most hurtful thing in development process is the db source control. We always have some troubles in managing database entities. Furthermore, when we want to do some maintenance operation on our database, like cleaning all unnecessary tables or just to be sure that the column type in the development table is equal to the one on the production, it could be an adventurer mission.
In past I worked a lot with Oracle db (version 8i, 9i and 10g). We managed our db entities by ourselves. We accomplish this task by writing an organized documentation for each change we did in the db scheme. In addition, we created it as a Sql script file and putted it in the source control with the global excel document that give us some details about the changes that have been made. This solution had some problems. It took some effort to insert a complete table with all foreign keys, triggers, grants, synonyms, constraints and more. In addition, if someone made changes in the table definition which had an existing creation script (and the table already contained a data), the update process became much sophisticated. When I started a new workplace I noticed that they don't have a lot of problem in this process. They are using the Sql Server Project. The Sql Server Project gives us the abilities to manage all db entities. All db scripts can be stored directly in the source control and deploy into a remote db. One of the great features that the Sql Server Project gives us is the abilities to compare between schemes and compare between data tables. For example, if you want to create a new table, you can create it in the visual studio in a new Sql script file and to deploy it to the db in just a couple of seconds. Furthermore, the Sql Server Project can be built, and you get errors if the db scheme wasn't built correctly.
Now let's see some screenshots that help us to figure how to work with it:
Open a new Sql Server 2005 db project:
Then choose the “New Scheme Comparison” in the “Scheme compare” toolbar:
Because we are working on existing db and we want to create a new project that will contains all the db entities, we should choose the target scheme to be the source scheme and our new project to be the target.
After clicking on the “ok” button we’ll get all the db entities with the creation option (This option define the operation that will execute when we want to write the changes to another source).
The next step will be to create a full db project that will contain all existing db entities. To accomplish this task, we need to click on the “Write Updates” button in the “Scheme compare” toolbar. After a confirmation message our project will contain all the necessary data from the db.
Now you can do some powerful things with your project. For example you can deploy it into a new db, and it will look exactly like the original one (You can define your target db in the build tab in the project properties page).
If you are developing with Oracle db you should check the ODT.NET it contains almost all these feature (Maybe without the compare tool)…
We have a powerful tool to manage our db entities. This tool helps us in development and in the deployment processes and it has a full integration with the source control. So, in the next time you are going to think what should be the best way to increase the db management work, just try it.