this blog contains information for .net and sql stuffs. You can find various tips and tricks to overcome problem you may be facing in ...

Thursday, February 25, 2010

Open Different Application (word,excel,pdf etc.) From your .net Application

I think it’s very nice to launch, word document from your software and when require also launch jpg viewer software as you want to open Document from the internet and don’t wish to save it.

Firstly understand what you require then step by step we go through it.

Requirement: you are displaying different files to the user. If user wish to open any of the listed file, it should launch supported software and file should be readable there. If supported software is not installed in machine, it should give user friendly message.

Okay, now requirement is clear to our mind, first we need to know one thing that file can only be open if you have already opened, in internet first temporary files are created then you can watch it with your installed software.

In our application we need to decide where we store these temporary files. I think base directory is preferred place or you may store in the temporary file storage of your pc.

Below line first sets the Where to store files.

Dim fileName As String = AppDomain.CurrentDomain.BaseDirectory + "TempFiles\"

First create directory when you go to open file.


Decide file name, it may be come from the data base or anything like it.

Dim flName As String = fileName + FileNamewithExtension.(i.e abc.doc, x.pdf)

Write data to created file

File.WriteAllBytes(flName, Me.dsFile.Attachment(0).Data)

Here dsFile is a dataset and has attachment table so we are writing bytes to file that we want to create. This data is stored in the image,or binary in the sql server. You can have directly byte array of file.

Now you have file on your machine, you just need to give path to that file and you can able to view it.


At end, either the form closing or application close you need to delete all of the files created in the Temporary Directory.

If Directory.Exists(fileName) Then

Dim fi As String

For Each fi In Directory.GetFiles(fileName)




End If

We assumed that we are just storing files in the temporary directory,if you have directory that you need to delete it too.

If on the machine you don’t have application that can open the file, it will throw exception you can handle that window32 exception with giving a user friendly message.

Catch ex As System.ComponentModel.Win32Exception

MessageBox.Show("Required Software is not insalled on your machine.", "Can't Open", MessageBoxButtons.OK, MessageBoxIcon.Warning)

Friday, February 19, 2010

Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances.

You might get this error if you try to attach an mdf file resides in the data directory (App_Data folder) in websites.

This is because your sql server does not have permission to create user instance of file to be attached.

Your connection string might look like this.

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"

It can be fixed by executing following statement in the sql server, where you wish to attach.

exec sp_configure 'user instances enabled', 1


Restart your SQL server and try to run your application.

Note: this can be useful if you work with the local database. In most cases remote server does not allow us to run above query, you need to contact your hosting company for it.

Thursday, February 18, 2010

Get Quotient and reminder in single statement

For the calculation purpose we need to calculate Quotient and reminder. We mostly uses divide and then mod operation to get quotient and remainder respectively but .net has provided in built function in the Math class that performs all this in the one statement.

Let’s take an example.

Dim num As Integer = 100

Dim rim As Integer = 0

Dim qut As Integer = Math.DivRem(num, 3, rim)

In above code snippet, we have num and we want to divide it by 3 and at the same time we need remainder of it.

Math.DivRem function takes three arguments

331)Number you want to Divide, here we have num variable

332)Number by which you want to Divide (divisor),here we have 3

333) Variable where it will store remainder, here we have rim variable

Let me know, if you know anything better than this.

Monday, February 15, 2010

Set Timeout in WebClient Class’s object

It occurs that we want some content from URL but that is in the provided time. To achieve things, we use web request or Webclient object. Web request provides timeout property but you will not find anything for the Webclient.

In Webclient you need to write custom class that is derived from the Webclient and add timeout property. Timeout is very important when you required to know result in the time duration.

Below is the new class derived from the WebClient and added timeout property to it.

public class CGWebClient : System.Net.WebClient


private int timeout;

public int Timeout


get { return timeout; }

set { timeout = value; }


public CGWebClient()


timeout = -1;


protected override System.Net.WebRequest GetWebRequest(Uri address)


System.Net.WebRequest request = base.GetWebRequest(address);

if (request.GetType() == typeof(System.Net.HttpWebRequest))


((System.Net.HttpWebRequest)request).Timeout = timeout;


return request;



Now when ever you require just initiatie the object of CGWebClient instead of WebClient.

Sunday, February 7, 2010

Isolated Storage in .net

It’s a one type of file system provided by .net framework. Isolated storage provides protection on the user, by application domain, assembly that is not provided by the standard file system.

To access file in Isolated storage is restricted to user who have created it. i.e Assembly X can’t access the file created in the Isolated storage of Assembly Y ,same in reverse is not possible.

Even we can restrict in application domain level, same assembly running in the different application domain can’t access the file of each others.

To Use Isolated storage we need to import System.IO.IsolatedStorage namespace. It provides the required classes for Isolated Storage.

You can create the Isolated Storage file by creating instance of IsolatedStorageFileStream class. Pass the name of your file name in the constructor. You will also find different overloaded constructor in it.

Now one common question arise in mind , Where does the file store in our file system? Because we don’t see in the our application’s folder or anywhere…

You can check that file created in the below location..

Go to your root directory from where operation system is loaded (Simply operating system is installed)

Yes now firstly from tools check to see hidden files and system files because isolated storage is hidden.

Click on the Documents and Settings, this folder contains the various users on the computer and some of the services folder all these can be viewed if you have selected to show hidden files.

Click on the user with you have logged to machine. Now you have many hidden files and folder. In that click on the Local Settings -- > Application Data, that contains the folder IsolatedStorage.

This IsolatedStorage folder may have one or many folder inside of it, go to inside at last you will have Files folder and that contains your file which you have created programmatically by your application.

If you don’t find your file in the Local Settings you can also check in the Application Data folder in side the User’s folder.

Tuesday, February 2, 2010

Query on schema of database

In sql server we create database object like tables, function, procedure etc. Some time we need to know how many tables or procedure are there.. at that time below sql query will be useful.

SQL provides query to execute on the object of itself.

List all the Database

SELECT * FROM sys.databases

SELECT * FROM master.dbo.sysdatabases

List all the tables in the Database

SELECT * FROM sysobjects WHERE type = 'U'

SELECT * FROM information_schema.tables

SELECT * FROM sys.tables

List all the Procedure in the Database

SELECT * FROM sys.procedures

SELECT * FROM sysobjects WHERE type = 'P'

List all the Function in the Database

SELECT * FROM sysobjects where type='fn'

Above syntax work correct in different version of sql server. Hope it will be useful.