Querying SQL with LINQPad
I don’t remember how I came across LINQPad, but I’m glad I did.
In my work I deal with SQL databases quite often. My company’s product is based on SQL 2005 Express Edition. There are many times during troubleshooting a customer issue that I’d like to be able to look at what’s going on in the database.
Until now, I only had two options. The first is to load SQL Management Studio Express Edition (SMSEE). While this is a great tool, unfortunately it’s also rather cumbersome for taking quick peeks. Not to mention the fact that for most customers, the server in question is their primary production machine, which means they are rightly fairly resistant to installing software of any kind.
The second option is the command-line sqlcmd tool which is included in the SQL Server install. Until now, this has been my primary tool for issuing SQL queries. It has some important drawbacks, aside from not being a GUI. The biggest drawback is that, while you can redirect input and output to a file with command-line options, you cannot do so within an interactive session. So all of your command history goes out the window if you want to save results to a file, plus you have to reissue all of the commands to get the session in the proper state for the command in question. Additionally, if you redirect output to a file, the output (including errors) no longer shows up in your interactive session, so you can’t tell if you’ve issued an incorrect command along the way without consulting the output file.
Enter LINQPad. LINQPad is a lightweight (3MB) graphical database client. I say database client rather than SQL client because its native mode is to process LINQ queries rather than SQL. While I know very little about LINQ, it does process SQL queries as well, so it is fine for my purposes. In fact, it provides what looks like will be an excellent tool for learning LINQ while still providing me the go-to SQL tools when I need it.
LINQ is touted as a next-generation query language, based on SQL to a great extent but also simplified and integrated directly into Microsoft’s .NET language framework. I’m not sure of all of the ramifications of this, but SQL could sure use some simplification as it seems to me a bit long in the tooth. I’m sure there are some easy wins in a new, standard query language that takes advantage of the ideas of the last twenty-some-odd years of development advancement. See the LINQPad page for more info on LINQ.
The user interface looks a bit like SQL Management Studio with a database structure pane on the left in a tree view, query and results on the right.
You have to select your query language from a dropdown at the top, which defaults to C# LINQ. I switch it to SQL at the moment.
Adding a connection to a database is as easy as clicking the “Add a connection” link at the top of the database pane. For most purposes, the default connection settings work fine. For my database, I had to remember that I have a non-default instance name. This caused some confusion for more than a few minutes because I’m so used to the instance name used by our product that the difference didn’t even register. No slight to LINQPad, that’s just my own brain not functioning.
I also believe that you don’t have to be running LINQPad on the same machine as the database server (this is pretty important usually), but since I’m always on my database server I didn’t test this.
Once connected to the appropriate instance name (duh, me), issuing queries is pretty basic. Compose the query in the query window, press play and look at the results. You can inspect the database structure in the tree in the database pane. Very simple, very nice.
Kudos to Joe Albahari, the author of this tool. He was also responsive in the LINQPad forum when I logged my troubles. Joe is also the author of C# 3.0 in a Nutshell from O’Reilly. LINQPad comes with all of the examples from that book. Good job!
In my work I deal with SQL databases quite often. My company’s product is based on SQL 2005 Express Edition. There are many times during troubleshooting a customer issue that I’d like to be able to look at what’s going on in the database.
Until now, I only had two options. The first is to load SQL Management Studio Express Edition (SMSEE). While this is a great tool, unfortunately it’s also rather cumbersome for taking quick peeks. Not to mention the fact that for most customers, the server in question is their primary production machine, which means they are rightly fairly resistant to installing software of any kind.
The second option is the command-line sqlcmd tool which is included in the SQL Server install. Until now, this has been my primary tool for issuing SQL queries. It has some important drawbacks, aside from not being a GUI. The biggest drawback is that, while you can redirect input and output to a file with command-line options, you cannot do so within an interactive session. So all of your command history goes out the window if you want to save results to a file, plus you have to reissue all of the commands to get the session in the proper state for the command in question. Additionally, if you redirect output to a file, the output (including errors) no longer shows up in your interactive session, so you can’t tell if you’ve issued an incorrect command along the way without consulting the output file.
Enter LINQPad. LINQPad is a lightweight (3MB) graphical database client. I say database client rather than SQL client because its native mode is to process LINQ queries rather than SQL. While I know very little about LINQ, it does process SQL queries as well, so it is fine for my purposes. In fact, it provides what looks like will be an excellent tool for learning LINQ while still providing me the go-to SQL tools when I need it.
LINQ is touted as a next-generation query language, based on SQL to a great extent but also simplified and integrated directly into Microsoft’s .NET language framework. I’m not sure of all of the ramifications of this, but SQL could sure use some simplification as it seems to me a bit long in the tooth. I’m sure there are some easy wins in a new, standard query language that takes advantage of the ideas of the last twenty-some-odd years of development advancement. See the LINQPad page for more info on LINQ.
Using LINQPad
Using LINQPad is as easy as firing it up. Of course, you have to download it first (at my work, we put it in a bundle of diagnostic tools our technicians can download as a bundle). There is a standalone .exe as well as a Windows installer available. This is a Windows-only application, by the way.The user interface looks a bit like SQL Management Studio with a database structure pane on the left in a tree view, query and results on the right.
You have to select your query language from a dropdown at the top, which defaults to C# LINQ. I switch it to SQL at the moment.
Adding a connection to a database is as easy as clicking the “Add a connection” link at the top of the database pane. For most purposes, the default connection settings work fine. For my database, I had to remember that I have a non-default instance name. This caused some confusion for more than a few minutes because I’m so used to the instance name used by our product that the difference didn’t even register. No slight to LINQPad, that’s just my own brain not functioning.
I also believe that you don’t have to be running LINQPad on the same machine as the database server (this is pretty important usually), but since I’m always on my database server I didn’t test this.
Once connected to the appropriate instance name (duh, me), issuing queries is pretty basic. Compose the query in the query window, press play and look at the results. You can inspect the database structure in the tree in the database pane. Very simple, very nice.
Kudos to Joe Albahari, the author of this tool. He was also responsive in the LINQPad forum when I logged my troubles. Joe is also the author of C# 3.0 in a Nutshell from O’Reilly. LINQPad comes with all of the examples from that book. Good job!