3

I am running out of ideas on this little problem:

I need to execute a query on a LocalDB database directly on user's computer. I do not want to install anything there and there is only .NET (4.5.2) and SQL Server 2014 Express LocalDB installed.

I do not want to copy database file to my computer and back againt with a little modification, I need to run just one or two queries directly on that machine.

Is there any portable command line tool that would allow me to do this? SQLCMD is not usable, since it requires installation of ODBC driver, SSMS requires installation too.

  • Tried Database.NET tool and there it works well and it is portable, but 11 MB app is too big for this task. – Vojtěch Dohnal Nov 6 '15 at 12:51
  • Can you write your own .NET console app? You can do this in just a few lines of code and it will be very small. I can post an example in a little bit when I get to a computer. – Solomon Rutzky Nov 6 '15 at 12:55
  • @srutzky Of course, but there must be something more robust than one purpose .NET app. – Vojtěch Dohnal Nov 6 '15 at 13:27
  • 1
    Why do you need something robust to execute 1 statement? Your requirements seem to negate that need. – Solomon Rutzky Nov 6 '15 at 13:55
4

I suggest just writing a simple .NET console app to do just that. It can accept parameters for "ConnectionString" and "Query". This will be flexible enough to use in various situations.

using System;
using System.Data;
using System.Data.SqlClient;

namespace SimpleSqlCmd
{
    class Program
    {
        static void Main(string[] args)
        {
            using(SqlConnection _Connection = new SqlConnection(args[0]))
            {
                using(SqlCommand _Command = new SqlCommand(args[1], _Connection))
                {    
                   _Connection.Open();

                   _Command.ExecuteNonQuery();
                }
            }

            return;
        }
    }
}

Usage would be:

SimpleSqlCmd "Server=(localdb)\InstanceName;" "Do some SQLs;"

UPDATE:

I have created a project on GitHub to host a more functional / complete version of the code shown above. The code on GitHub will be updated over time whereas the code here will not be updated (since that is really not the purpose of this site). The repository URL for Simple SQL Exec (renamed, to avoid confusion, from "SimpleSqlCmd" as it is shown above):

https://github.com/SqlQuantumLeap/SimpleSqlExec

  • Why do you presume that this query is NonQuery? I need sometimes some return values to check stuff, and writing the whole connection string as a parameter is also not very fast. I imagine some solution like SQLCMD (or something with visual interface), that would be small to copy and fast to use and also flexible like SQLCMD. I have tried e.g. HeidiSQL but named pipes provider does not work for me. – Vojtěch Dohnal Nov 6 '15 at 14:10
  • 1
    @Vojt it looks like you know what you are doing In terms of c#, so maybe modify it so that the parameters are nice and short to fit your requirement? some variables and branching logic seem sufficient for that. Also, I think Srutzky answered your "original" question pretty well, based on what you wrote. For answers for unstated requirement, maybe edit your question and that might get you a more robust answer you are after. – Kentaro Nov 6 '15 at 14:47
  • 1
    @VojtěchDohnal Yes, please update your question with the exact requirements so that I (or anyone) can provide a more accurate / complete answer. Also, I am not sure what you mean by "writing the whole connection string as a parameter is also not very fast". Are you expecting to have that hard-coded in the code? Were you looking for it to come from an environment variable or app.Config file? Even with SQLCMD you still need to provide that info. What functionality from SQLCMD are you looking for? Please be specific since SQLCMD can do a lot. – Solomon Rutzky Nov 6 '15 at 14:56
  • 1
    @VojtěchDohnal I am also in the process of modifying it to handle more of what SQLCMD can do. It gave me a reason to finally start using GitHub :) github.com/SqlQuantumLeap/SimpleSqlExec But now I am again confused with the info regarding LocalDB-Manager since that is a GUI and so far we have been talking about a command-line utility. That GUI project doesn't do anything that you can't already do via SqlLocalDB.exe. Am I missing something? – Solomon Rutzky Nov 7 '15 at 16:10
  • 1
    @VojtěchDohnal In order to connect, you just specify Server=(localdb)\mssqllocaldb or whatever the instance name is if not "mssqllocaldb". Being able to use the (localdb)\InstanceName syntax requires the .NET Framework v 4.5 (or newer) being loaded on the machine (which you have). And I agree about the usefulness of having the Rows Affected and will add that functionality. I am just saying that the functionality being in the code does not guarantee that the value will be reported since that is controlled by the NOCOUNT session property. – Solomon Rutzky Nov 7 '15 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.