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

httpRuntime – Increase file size limit from 4 MB to bigger size in ASP.NET

I wrote an article on how to upload image to SQL Server database BLOB fileld and retrieve it back. You can refer that article at:

http://www.sqlhub.com/2009/03/image-store-in-sql-server-2005-database.html

After trying to implement this functionality, one of my readers has observed that he can’t upload the file big file. He came to me for solution. Actually in ASP.NET you can upload file with maximum size of 4MB. That is the default settings. Than how could you upload bigger file than 4096 kb? Isn’t there any way around? Because this is somehow very common need.

Well, they is a will, there is way!!! You can set maximum file size with httpRuntime tag. You can set this at machine.config or web.config but I insist, not to touch machine.config file as it has web server wide effect, you should set it in web.config so that it affect one website or one subdirectory in which web.config reside.

You have to add one line under <system.web> element of your web.config. The line is

<httpRuntime maxRequestLength=”20480″ />

This will increase file limit from 4 MB to 20MB.

Enjoy!!!!

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

Use of System.IO.DriveInfo class in Windows application using C#

I came across one task recently which needs interaction with windows drive. Today I finished it and thought to share one small part of that script with you. This script will simply find out all the available drive in your windows system and display its information about drive letter, drive type, free available space, format of drive. I have used C# windows application in my example so simply create one windows application, draw one button and one label on your windows form.

Once you prepare with your application, have a look at below given code which suppose to be placed in button1’s click event.

label1.Text = “”;

foreach (System.IO.DriveInfo drive in System.IO.DriveInfo.GetDrives())

{

if (drive.DriveType.ToString().ToLower() == “fixed” drive.DriveType.ToString().ToLower() == “removable”)

label1.Text = label1.Text + drive.Name + “==>” + drive.DriveType + “==>” + drive.DriveFormat + “==>” + drive.AvailableFreeSpace + “\n”;

else

label1.Text = label1.Text + drive.Name + “==>” + drive.DriveType + “==>0==>0\n”;

}

So here you finished your small yet useful script.

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

Dynamic word document with SQL Server data from C Sharp .NET

I have seen many time when question comes to generate dynamic word document with SQL Server’s data, developer feel confusion, not because it is difficult but because unawareness. .NET framework has made life so much easier than ever in programming world. Generating dynamic word document is really very easy. Let us see one example which will get employee name and department in which they have appointed and write separate page for each employee.

We will have two task, 1.) Create one table in SQL Server 2.) Use that SQL table in ASP.NET C# application and generate dynamic DOCX file.

1.) Creating table in SQL Server and insert some data in it.

–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’

2.) Now let us create on ASP.NET C# web application in VISUAL STUDIO 2008. Once creating website, first task we will do is, add reference of WORD library in our application from Website Menu of VS 2008 and “Add Reference” option. It will open one dialog box. You will have to move to “COM” table in that dialog box and find “Microsoft Word 12.0 Object Library”, select it and click on “OK”.

Note: You may have different WORD library other than 12.0, as per your system’s configuration.

After adding reference, create on button in your web form and write following code in that button’s click event.

//create connection to database

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();

conn.ConnectionString = “Data Source=.;Initial Catalog=AdventureWorks;Integrated Security=True”;

conn.Open();

//setup SqlCommand and assign SQL query in command

System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();

command.CommandType = System.Data.CommandType.Text;

command.Connection = conn;

command.CommandText = “Select name,dept From emps”;

//create one data adapter which will execute the command and fill the data into data set

System.Data.SqlClient.SqlDataAdapter recordAdp = new System.Data.SqlClient.SqlDataAdapter();

recordAdp.SelectCommand = command;

System.Data.DataSet recordSet = new System.Data.DataSet();

recordAdp.Fill(recordSet);

command.Dispose();

conn.Close();

object missing = System.Reflection.Missing.Value;

object visible = true;

object start1 = 0;

object end1 = 0;

try

{

//create one word application

Microsoft.Office.Interop.Word.ApplicationClass wordApp = new Microsoft.Office.Interop.Word.ApplicationClass();

//create one document for above word application

Microsoft.Office.Interop.Word.Document wordDoc = wordApp.Documents.Add(ref missing, ref missing, ref missing, ref missing);

for (int i = 0; i < recordSet.Tables[0].Rows.Count; i++)

{

//creating paragraph to add in word document

Microsoft.Office.Interop.Word.Paragraph wPara = wordDoc.Content.Paragraphs.Add(ref missing);

wPara.Range.Text = “Hello “ + recordSet.Tables[0].Rows[i][“name”].ToString() + “\n you are appointed in “ + recordSet.Tables[0].Rows[i][“dept”].ToString()+ ” department”;

wPara.Range.InsertParagraphAfter();

//making page break

wPara.Range.InsertBreak(ref missing);

}

object fileName = “D:\\SQLHub.DOCX”;

wordDoc.SaveAs(ref fileName, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing);

}

catch (Exception ex)

{

Response.Write(ex.ToString());

}

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

Stored Procedure return value handle and record set handle in C# ASP.NET

If you are SQL Server geek, you must be aware with Stored Procedure. While question comes to implement stored procedure in front end language, many developers makes mistake or feel confusion as per my recent observation so I am tempted to write one article which can show how to handle stored procedure in C# when it return some value and when it return some record sets.

–CREATE TABLE

CREATE TABLE empDemo

(

ID INT NOT NULL,

NAME VARCHAR(50)

)

GO

–INSERT DATA

INSERT INTO empDemo

SELECT 1,‘Ritesh’ UNION ALL

SELECT 2,‘Rajan’

–create SP which will return list of employee

–NOTE: this is just basic SP, you can create

–your own for your custom need.

CREATE PROC ListEmp (@id INT)

AS

BEGIN

SELECT * FROM empDemo WHERE ID=@id

END

–check SP whether it works.

EXEC ListEmp 2

Now, we will see C# code (in asp.net code behind) to return the list of employee:

protected void Button1_Click(object sender, EventArgs e)

{

//create connection string and assign it to SqlConnection object

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

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(strConn);

conn.Open();

//setup SqlCommand and assign SP name along with input parameter variable name and value

System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Connection = conn;

command.CommandText = “ListEmp”;

command.Parameters.AddWithValue(“@id”, “2”);

//create one data adapter which will execute the command and fill the data into data set

System.Data.SqlClient.SqlDataAdapter recordAdp = new System.Data.SqlClient.SqlDataAdapter();

recordAdp.SelectCommand = command;

System.Data.DataSet recordSet = new System.Data.DataSet();

recordAdp.Fill(recordSet);

command.Dispose();

conn.Close();

//bind gridview1 with our dataset

GridView1.DataSource = recordSet.Tables[0].DefaultView;

GridView1.DataBind();

}

This seems basic but really useful for newbie, now what if we have some other DML command and we want to return value from SP and handle it in our C# ASP.NET.

–create SP to insert record and retun value

–we will handle return value in C# code behind

CREATE PROC InsertEmp(@ID INT, @Name VARCHAR(20))

AS

BEGIN

BEGIN TRY

INSERT INTO empDemo(ID,Name) VALUES(@ID,@Name)

return 1

END TRY

BEGIN CATCH

return 0

END CATCH

END

–check SP works or not

EXEC InsertEmp 3,‘Alka’

Now let us move to ASP.NET C# code behind.

protected void Button1_Click(object sender, EventArgs e)

{

//create connection string and assign it to SqlConnection object

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

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(strConn);

conn.Open();

//setup SqlCommand and assign SP name along with input parameter variable name and value

System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Connection = conn;

command.CommandText = “InsertEmp”;

command.Parameters.Add(“@ReturnValue”,System.Data.SqlDbType.Int);

command.Parameters[“@ReturnValue”].Direction = System.Data.ParameterDirection.ReturnValue;

command.Parameters.AddWithValue(“@id”, “4”);

command.Parameters.AddWithValue(“@Name”, “Bihag”);

command.ExecuteScalar();

int i = Convert.ToInt32(command.Parameters[“@ReturnValue”].Value);

if (i == 1)

{

Response.Write(“Successfull!!!!”);

}

else

{

Response.Write(“Not Successfull!!!!”);

}

}

Hope you have enjoyed it!!!!

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

Environment.CurrentDirectory in C#

Well, this blog is mainly focuses on SQL Server technology but sometime I would like to give tips on other Microsoft Technology as I did it in past as well.

I have seen many C# developer use full path to access some file or folder insider the root directory of project. They might be unaware with Environment.CurrentDirectory property.

Have a look at below small tips for that.

string var1;
var1 = Environment.CurrentDirectory;
var1 = var1.Substring(0, var1.Length – 9);

Note: default CurrentDirectory property point to Bin\Debug folder if you are running your application in Debug mode so I removed last nine character to get root path. You can make it more customize as per your need. Hope this will help.

Cheers!!!

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