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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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 element of your web.config. The line is

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Use of Silver light, very good article for Music lover by John Papa.

I am guitar lover and when I see article of John Papa at http://simple-talk.com/ I was very excited to run the snippet given in that article. I never ever even think about that I can use programming knowledge for my guitar.

http://www.simple-talk.com/dotnet/.net-framework/using-silverlight-to-build-a-guitar-chord-calculator/

Every music lover who is in programming field should read this article.

Thanks John!!!!

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Image store in SQL-Server 2005 database and retrieve it in asp.net application with C#:

Image store in SQL-Server 2005 database and retrieve it in asp.net application with C#:
Storing image in sql-server 2005 database is quiet simple task. All you need to deal with binary datatype. One more benefit you will get by storing image in database is that you are free from burden of managing lots of image folder and its path consistency.
Herewith, I am describing one simple web application in ASP.NET which will store the image in database and will retrieve it right from there.
Let us start our journey to this interesting topic.
First we will create one table in AdventureWorks database or in any other DB you have.
use adventureworks
GO
CREATE TABLE [dbo].[ImageStore] (
[ID] [int] NOT NULL ,
[ImageContent] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Note: You can create couple more field in above table in order to get file name and its extension. I will be using “.JPG” extension in my code while retrieving the image but you can make it more dynamic.
Now, we have done with SQL-Server and let us move to our Visual Studio 2008. We will be creating one new web application of C#.
As soon as you will get your web form, draw one button on that with text property to “Upload Image”.
After setting text property of button, double click on that so it will open code behind of C# with click event button. Before we go further, uses following namespaces in your code behind as those are mandatory.
using System.Drawing;
using System.Data.SqlClient;
using System.IO;
Now, let us move to uploading the file.
Note: I will be going to upload one file from my “C” drive, you can make it more dynamic by using open file dialog box and select file from users computer.
Once you set the reference of above given name spaces, you have to write the following code in button1’s click event.
protected void Button1_Click(object sender, EventArgs e)
{
string strImageName= @”C:\Conversion.jpg”;
Bitmap bNewImage = new Bitmap(strImageName);

FileStream fs = new FileStream(strImageName, FileMode.Open, FileAccess.Read);

//creating byte array to read image
byte[] bImage = new byte[fs.Length];

//this will store conversion.jp in bImage byte array
fs.Read(bImage, 0, Convert.ToInt32(fs.Length));
fs.Close();
fs = null;

//open the database using odp.net and insert the data
string connstr = @”Data Source=.;Initial Catalog=AdventureWorks;
Persist Security Info=True;User ID=sa;Password=sa”;

SqlConnection conn = new SqlConnection(connstr);
conn.Open();

string strQuery;
strQuery = “insert into [dbo].[ImageStore](id,[ImageContent]) values(“ + “1,” + ” @pic)”;

SqlParameter ImageParameter= new SqlParameter();
ImageParameter.SqlDbType = SqlDbType.Image;
ImageParameter.ParameterName = “pic”;
ImageParameter.Value = bImage;

SqlCommand cmd = new SqlCommand(strQuery, conn);
cmd.Parameters.Add(ImageParameter);
cmd.ExecuteNonQuery();

Response.Write(“Image has been added to database successfully”);
cmd.Dispose();
conn.Close();
conn.Dispose();
}
So far, you have finished half of the journey. You have select the image from your hard drive, convert it in byte and insert it in SQL-Server table, now we are going to do reverse procedure to get image back.
Now, create another button on the same web form and set the text property with “Retrieve Image” value and also put one image box or grid or whatever tool in which you want to retrieve the image or else, you can simply save it at your hard drive. I am saving it in my hard drive with .JPG extension, you can make more dynamic as I specified in starting of this article.
Finally, write down the image retrieval code in button2’s click event as follows.
protected void Button2_Click(object sender, EventArgs e)
{
string connstr = @”Data Source=.;Initial Catalog=AdventureWorks;
Persist Security Info=True;User ID=sa;Password=sa”;

SqlConnection conn = new SqlConnection(connstr);
conn.Open();

//selecting image from sqldataadapter to dataset.
SqlDataAdapter sdImageSource = new SqlDataAdapter();
sdImageSource.SelectCommand = new SqlCommand(“SELECT * FROM [dbo].[ImageStore]”, conn);

DataSet dsImage = new DataSet();
sdImageSource.Fill(dsImage);

string strfn = Convert.ToString(DateTime.Now.ToFileTime());
strfn = @”D:\StoreIMG\” + strfn + “.jpg”;
FileStream fs = new FileStream(strfn, FileMode.CreateNew, FileAccess.Write);

//retrieving binary data of image from dataset to byte array
byte[] blob = (byte[])dsImage.Tables[0].Rows[0][“imageContent”];
//saving back our image to D:\StoreIMG folder
fs.Write(blob, 0, blob.Length);
fs.Close();
fs = null;
}
Note: You have to have write permission in D:\StoreIMG for ASP.Net user or else it will show you an error of permission.
Hope you have enjoyed it!!!!
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah