CODEWITHSUNDEEP
c#sqlsql-server
Harald
Harald

Reputation: 889

Starting SQL Server via C#

When I start my PC, Sql Server (SQLExpress) isn´t running, it starts when I try to compile my program in Visual Studios 2010.

Is it possible to start it via C#? My problem is, that if I use the .exe without Visual Studios, it tells me that Sql Server isn´t running.

Upvotes: 4

Views: 14038

Answers (6)

user966172
user966172

Reputation: 1

Here is a SMO WMI C# code sample which lists SQL services, check service state, start SQL service, etc.

How to use SMO WMI in C#

Useful SQL Tools

Upvotes: 0

RBarryYoung
RBarryYoung

Reputation: 56735

Here's how to do it with SMO in C#:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Wmi;
using Microsoft.SqlServer.Management.Common;

static class SQLStart
{
    public static void StartSQLService()
    {
        //Declare and create an instance of the ManagedComputer object that represents the WMI Provider services.
        ManagedComputer mc = default(ManagedComputer);
        mc = new ManagedComputer();

        //Iterate through each service registered with the WMI Provider.
        Service svc = default(Service);
        foreach ( svc in mc.Services) {
            Console.WriteLine(svc.Name);
        }

        //Reference the Microsoft SQL Server service.
        svc = mc.Services("MSSQLSERVER");

        //Stop the service if it is running and report on the status continuously until it has stopped.
        if (svc.ServiceState == ServiceState.Running) {
            svc.Stop();

            Console.WriteLine(string.Format("{0} service state is {1}", svc.Name, svc.ServiceState));
            while (!(string.Format("{0}", svc.ServiceState) == "Stopped")) {
                Console.WriteLine(string.Format("{0}", svc.ServiceState));
                svc.Refresh();
            }
            Console.WriteLine(string.Format("{0} service state is {1}", svc.Name, svc.ServiceState));
            //Start the service and report on the status continuously until it has started.
            svc.Start();
            while (!(string.Format("{0}", svc.ServiceState) == "Running")) {
                Console.WriteLine(string.Format("{0}", svc.ServiceState));
                svc.Refresh();
            }
            Console.WriteLine(string.Format("{0} service state is {1}", svc.Name, svc.ServiceState));

        } else {
            Console.WriteLine("SQL Server service is not running.");
        }
    }
}

This is just a conversion of MS's VB.net example. It's all explained here: http://msdn.microsoft.com/en-us/library/ms162139(v=sql.90).aspx

Upvotes: 5

Paul Sasik
Paul Sasik

Reputation: 81459

Don't do it in code. It is very rare that this would be necessary/useful. And do not reinvent the wheel:

Look into your computer's Services manager in the Administrative Tools. You should see your instance of SQL Server Express there. You can start it from there manually and you can also set it up to start automatically from a setting in the service properties. Set it to Automatic in the Startup Type combo box.

Follow up: As has been suggested there are many different ways to start the server/service. The issue I see with the non-Services suggestion is that you would be introducing security, configuration and other management overhead unnecessarily. SQL Server running as a service is a well-understood paradigm. Again, do not reinvent the wheel.

Upvotes: 2

Gregory A Beamer
Gregory A Beamer

Reputation: 17010

There are quite a few ways to tackle this. Overall, unless you need this at runtime on another person's box, I would just set the service to automatic, as dknaack has suggested. Even on another person's box, I would set the "set the service to automagic" in the installer and not as part of the application proper.

To control services in C# code in SQL Server, the Microsoft.SqlServer.Management.Smo.Wmi.Service class is one option. For a more generic option, there is System.ServiceProcess.ServiceController. You see WMI in the SQL object? That is a good indicator scripting is another decent option (and perhaps even more reusable?)

Upvotes: 0

Kuberchaun
Kuberchaun

Reputation: 30324

You could use a batch script that you store in your task bar in Win7 or on the desktop that you click. That assumes you only wanted to do it via C# to avoid going into another app. If you need it in app your writing in C# than this probably won't be the solution your looking for.

Look here http://www.bidn.com/blogs/briankmcdonald/bidn-blog/1011/restarting-sql-server-using-a-batch-script

Upvotes: 0

dknaack
dknaack

Reputation: 60466

I would change the start mode of the Sql Server Windows Service to automatic. But you can do this in c# too, but i dont recommend it. There are other problems like access security and more. 

        System.Diagnostics.Process process = new System.Diagnostics.Process();
        process.StartInfo.FileName = "net start \"Sql Server (SQLEXPRESS)\"";
        process.Start();

Sql Server (SQLEXPRESS) is the name of your service.

Upvotes: 7

Related Questions