So, I have written before about how to put your database into version control using database projects in Visual Studio. Even while having the scripts in the solution, there can be times when you can’t exactly remember what you changed and needs to go out with your project deployment to the production database. Deploying files is easy because of tools like WinMerge, however deploying things to a database can get quite complicated. You could script both schemas and use WinMerge to see the differences between the development database and the production database. But even doing that, you will still have to write a custom script to get the production database schema up to date.

Enter xSql

xSQLSoftware This is where a tool such as xSQL Object can be extremely helpful. All that need be done is set up the connections, run the comparison, and then you can visually see what has changed between your development and live databases! No need to remember what you changed or any of that, just run the comparison and execute the change script. It will even allow you to save database snapshots before running your change scripts. You can see an excellent walkthrough here. xSQL Object also comes in a bundle with another one of their products, xSQL Data or by it’s self. xSQL Data allows you to compare data differences between two databases.

The Best Part

All the goodness of the xSQL Bundle (xSQL Object and xSQL Data) Lite Edition comes at a very affordable price FREE! If you only use SQL express edition then you can get the full bundle lite edition and it will work without any restrictions at all! However, if you need to use it against other versions of SQL Server it does have the following limitations:

• up to 25 tables
• up to 40 views
• up to 40 stored procedures
• up to 40 functions

If you are using it against a small database then you shouldn’t have any problems. Now if you have databases larger than this and are using SQL Server editions other than express, the product costs $399.00 for a single user license. BUT, after downloading it I was sent an email offering 30% off if I purchased the product within 7 days of the download. That brings the cost down to $280! Not too bad when you compare it with the prices of other comparable tools. So I ask you to go to the website and check it out if you haven’t already! http://www.xsqlsoftware.com

Update

After contacting the company about licensing, I was shown another one of their great tools. A little while back I wrote a post titled Finding Text in SQL Server Stored Procedures. They have a tool called xSQL Object Search that allows you to search for all object types, through the names and definitions, for strings. It will also do a search using regular expressions! Pretty powerful stuff for a **FREE **tool! Check it out here: xSQL Object Search

Happy Deployments!

So you may have found yourself in a similar situation, needing to make a TCP/IP request to a 3rd party API possibly using SSL. Well, that is a quite simple task. It can however, be complicated if this 3rd party requires the use of certificates for communication to its API server. I found myself in some sort of certificate hell where I had the certificate, added it to the request and somehow it still wasn’t working. If you know what I’m talking about and had as many hurdles as I did, my condolences to you. I will try to explain in this article how I started, the problems I ran into and then the overall solution that ended up working for me.

To start with, you should have some kind of certificate. Most likely a *.pfx or *.p12 file. This can also come with a private key or password for the certificate’s encryption. This is what a standard WebRequest over SSL might look like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

public string GetData(string inputData)
{
    //will hold the result
    string result = string.Empty;
    //build the request object
    HttpWebRequest request = (HttpWebRequest)WebRequest.Create("https://someapi.com/");
    //write the input data (aka post) to a byte array
    byte[] requestBytes = new ASCIIEncoding().GetBytes(inputData);
    //get the request stream to write the post to
    Stream requestStream = request.GetRequestStream();
    //write the post to the request stream
    requestStream.Write(requestBytes, 0, requestBytes.Length);
    //build a response object to hold the response
    //submit the request by calling get response
    HttpWebResponse response = (HttpWebResponse)request.GetResponse();
    //get the response stream to read the response from
    Stream responseStream = response.GetResponseStream();
    //now read it out to our result
    using (StreamReader rdr = new StreamReader(responseStream))
    {
        //set the result to the contents of the stream
        result = rdr.ReadToEnd();
    }
    //return
    return result;
}

The example above is missing the portion where you add the certificate to the request. You may receive a 403 Forbidden error from the server if a certificate is required to make the request to the API server. A simple way of adding a certificate to the request would be like so:

1
2

//add certificate to the request
request.ClientCertificates.Add(new X509Certificate(@"C:\certs\Some Cert.p12", @"SecretP@$$w0rd"));

The X509Certificate class is found in the System.Security.Cryptography.X509Certificates namespace. Simply add a new certificate to the client certificates before calling for the response, and it should be sent with the request. However, you may encounter an exception with the message “The system cannot find the file specified”. I encountered this error after I got the application off my local machine and onto the development server. After doing some research I stumbled upon this kb article. This article opened my eyes to how using certificates is a little more complicated than I initially thought. Turns out the problem was that the user trying to access the certificate does not have a profile loaded.

After stepping through the article, installing the certificate to the local machine’s personal certificate store, and then granting rights to the certificate using the WinHttpCertCfg.exe tool, and putting in a little more code found in the kb article, I was well on my way. The article describes how to use C# to open a certificate store and use the certificate directly out of the store. This presents a bit more elegant, and in my opinion more secure, way of getting to and using the certificate.

1
2
3
4
5
6

//add it in a better way
X509Store certStore = new X509Store("My", StoreLocation.LocalMachine);
certStore.Open(OpenFlags.ReadOnly | OpenFlags.OpenExistingOnly);
X509Certificate2 cert = certStore.Certificates.Find(X509FindType.FindBySubjectName, "My cert subject", false)[0];
certStore.Close();
request.ClientCertificates.Add(cert);

This method will not only give access to the certificate regardless of having a loaded profile, but it also takes the certificate’s private key password out of the code and/or configuration. This snippet above took me out of the certificate hell that was crushing my life for a couple days!

Putting it all together:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

public string GetData(string inputData)
{
    //will hold the result
    string result = string.Empty;
    //build the request object
    HttpWebRequest request = (HttpWebRequest)WebRequest.Create("https://someapi.com/");
    //add certificate to the request
    //request.ClientCertificates.Add(new X509Certificate(@"C:\certs\Some Cert.p12", @"SecretP@$$w0rd"));
    //add it in a better way
    X509Store certStore = new X509Store("My", StoreLocation.LocalMachine);
    certStore.Open(OpenFlags.ReadOnly | OpenFlags.OpenExistingOnly);
    X509Certificate2 cert = certStore.Certificates.Find(X509FindType.FindBySubjectName, "My cert subject", false)[0];
    certStore.Close();
    request.ClientCertificates.Add(cert);
    //write the input data (aka post) to a byte array
    byte[] requestBytes = new ASCIIEncoding().GetBytes(inputData);
    //get the request stream to write the post to
    Stream requestStream = request.GetRequestStream();
    //write the post to the request stream
    requestStream.Write(requestBytes, 0, requestBytes.Length);
    //build a response object to hold the response
    //submit the request by calling get response
    HttpWebResponse response = (HttpWebResponse)request.GetResponse();
    //get the response stream to read the response from
    Stream responseStream = response.GetResponseStream();
    //now read it out to our result
    using (StreamReader rdr = new StreamReader(responseStream))
    {
        //set the result to the contents of the stream
        result = rdr.ReadToEnd();
    }
    //return
    return result;
}

Hope this helps!

So, I’m sure you have been met with a similar scenario during development. You know the one that you have to rename a column or even drop a column in the database. This can be quite annoying if you are doing stored procedure based data access for your application. Once you change the column on the table, you have to figure out which stored procedures reference the column. They aren’t always tough to find most of the time, but sometimes you are dealing with a column that may be referenced in many stored procedures. Well thanks to my boss Cliff’s research and knowledge sharing, your search can be as easy as ours!

The query:

1
2
3
4
5
6
7
8
9
10
11

USE Northwind
GO 
DECLARE @SearchText AS VARCHAR(50)
SET @SearchText = 'CustomerID'

SELECT
ROUTINE_NAME,
ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME LIKE '%' + @SearchText + '%'
OR ROUTINE_DEFINITION LIKE '%' + @SearchText + '%'

This query will return all the names and routine definitions of stored procedures that contain certain text. It is not really bound by column names but I needed a true development scenario. Run the query with what you are looking for and presto! All the stored procedures you will need to modify.

Hope this helps!