Is MSSQLSERVER installed on server? Check from vb.net or c# with System.ServiceProcess

We often need to check whether SQL Server installed on the server before installing our application developed in VB.NET or in C#. To find out any service installed on your server, you have to use ServiceProcess class from System. You can’t directly call System.ServiceProcess in your VB.NET or in C#, you have to first ADD REFERENCE to your application from “.NET” tab of ADD REFERENCE dilog box. Once you are done with adding reference, you can call serviceProcess

VB.NET
Imports System.ServiceProcess

C#
using System.ServiceProcess

Basically, ServiceProcess will give an array of all processes running on the box, you have to identify your service from that array. Generally SQL Server installed with default instance, which is MSSQLSERVER, you can simply check that instance name from array, if client machine has not installed with SQL server with default instance than there one small ray of hope is, you can check array item with substring item “SQL”, this way is not 100% sure shot, if named instance is completely different and doesn’t contain “SQL” word at all, this method won’t work, you are out of luck in this case.
Let us have a look at code in VB.NET, however, you can simply modify your syntax and made it work in C# as well.

Imports System.ServiceProcess

Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim services As ServiceController()
        services = ServiceController.GetServices()
        For i As Integer = 0 To services.Length
            If (services(i).ServiceName.ToLower() = “mssqlserver”) Then
                Label1.Text = “Service Found!!!”
                Exit For
            End If
        Next i
    End Sub
End Class

Create one windows application in VB.NET, call System.ServiceProcess namespace and have above code in your Form1’s page load event. Result will be printed on the label1 of the form1.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Advertisements

Use of SQL Server Stored Procedure in VB.NET for Client/Server application.

After booming of internet, people have divert concentration from client/server application but client/server windows application is not died so far and Microsoft have taken the steps to remove syntax and logic difference between web as well as windows application. They have not achieved it 100% even this step is really appreciable. Today I would like to show you demonstration about how you can get record set from SQL Stored procedure and use its value in VB.NET windows application to write employee name in windows form on dynamic label.

We will have two steps for this demo.

1.) SQL Server coding

–create table

USE [adventureworks]

GO

CREATE TABLE [dbo].[emps](

[Name] [varchar](50),

[Dept] [varchar](10),

[Company] [varchar](15)

) ON [PRIMARY]

GO

–insert records

INSERT INTO emps

SELECT ‘rITESH’,‘MIS’,‘ECHEM’ UNION ALL

SELECT ‘Rajan’,‘MIS’,‘mar’

–creating stored procedure which return emp name

CREATE PROC [dbo].[getEmpList]

AS

SELECT Name FROM emps

–checking SP

EXEC getemplist

2.) VB.NET code for windows application to utilize SP of SQL Server 2005.

‘draw one button name ‘Button1’ in your windows form and use below event for it

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim conn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection

conn.ConnectionString = “Data Source=.;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=sa”

conn.Open()

Dim cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand

cmd.CommandType = System.Data.CommandType.StoredProcedure

cmd.Connection = conn

cmd.CommandText = “[getEmpList]”

Dim reader As System.Data.SqlClient.SqlDataReader

reader = cmd.ExecuteReader

Dim XPos As Integer = 0

Dim YPos As Integer = 0

Do While reader.Read()

XPos = 9

YPos = YPos + 35

Dim lblfieldname As System.Windows.Forms.Label = New System.Windows.Forms.Label

lblfieldname.Text = reader(“Name”).ToString()

lblfieldname.Location = New System.Drawing.Point(XPos, YPos)

lblfieldname.Size = New System.Drawing.Size(120, 23)

Me.Controls.Add(lblfieldname)

XPos = XPos + 20

YPos = YPos + 35

Loop

End Sub

Happy Programming!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com