December 27, 2010

.Net Remoting - Single Call server activated Remote Object

1. Introduction

Dot net remoting is client and server based distributed object technology. In this first article about dot net remoting, we will create two applications. One is a remote server application and another one is a client application. We will go ahead and start creating the applications and I will give the explanation when we are developing the example.

2. About this Example

The server we are going to create is a console application. This console application will host our remote objects on the server. The client will make a call to the public members (Usually function) exposed by that remote object(s).

The client is a windows form application, which will get access to the remotely hosted objects and start using it. Below is the screen shot of the client application.

Net Remorting - Serialisation Example
Fig 1. .Net Remorting - Serialisation Example

When we click the Get Details button, the client makes a call to the remote server console application to get the Customer Id, Customer Name, Last Deposit and Last Withdraw from the remote object LastTrans. I will explain other concepts when we move along.

First, we will go ahead and start our Remote server console application.

3. Remote Server Console App

1) Create a new console application and name it ConServer.
2) Right click the ConServer project and choose Add reference
3) From the dialog displayed Select "System.Runtime.Remoting" from the .net tab and click the OK button. This step provides access to the dot net remoting API.
4) Next, add a class named LastTrans to the Project. Add one more class and name it as RemCustomer.


We have created a console application project and then got access to the dot net remoting assemblies. Then we added classes to the project. RemoteObject is the one which will keep in the Server’s memory. And LastTrans object is created on the server but it will be sent to the client through serialisation using the RemoteObject.

"Serialisation" is a technique, which converts your class in the form of "bit streams" and these steam in the receiving end is collected to form the actual object. In our example, we are going to send the LastTrans to the client through serialisation .

All the skeleton code for our server is kept ready by the IDE. Now we will go ahead and add code.

4. The LastTrans Class

1) Locate the LastTrans class created by the IDE and Mark it as "Serializable" by adding the serializable attribute before the class. Below is the code for it:

//RemoteServer_018: Make the Class Serializable
public class LastTrans

2) Add a private variable to this class.

//RemoteServer_011: Private field for the Newly added class
private int LastDeposit;
private int LastWithdraw;

3) In the constructor initialize the above declared private variables. Note that here I hard-coded the values. In the real world, it will be queried from the database to get the latest result. Below is the code:

//RemoteServer_012: Constructor for Last Transactions. At present we will
//Default it to some constant value
public LastTrans()
    //Hard Coded here. In real world make a query to the Database to get
    //the latest information.
    LastDeposit = 68800;
    LastWithdraw = 12000;

4) Next, provide a function to return the private variables. The client uses these functions to get the Last Deposit amount and as well as Last Withdrawal amount.

//RemoteServer_013: Get the Last Transaction amount
public int GetLastDeposit()
    Console.WriteLine("Last deposit Queried...");
    return LastDeposit;

public int GetLastWithdraw()
    Console.WriteLine("Last Withdrawl amount Queried...");
    return LastWithdraw;

Note that the console.WriteLine does not print anything on our Remote Server. Because the object is serialised and sent to a client. The client creates this object and subsequent call on the object gets executed on the client machine.

5. The RemCustomer Class

1) Include the below-specified namespace.

//Remote_003: Include the Namespace
using System.Runtime;

2) This class acts as the remote object. To make the IDE created object remote server compatible, (Refer Step 4) locate the class and derive it from the "MarshalByrefObject". OK. Why that derivation is required. It is required as we are planning to keep this object in server’s remote pool. Below is the Second step of the code:

//RemoteServer_004: Make the class Remotable by inheritance.
public class RemCustomer : System.MarshalByRefObject

3) Next, declare the members of this class. Note our remote object is going to serve the client through standard as well as user-defined data types. User-defined type here in our case is the LastTrans which you already saw that we marked it as serializable.

//RemoteServer_005: Feilds on the remote Class
private string CustName;
private int CustId;
//RemoteServer_014: Instance for LastTrans declared
private LastTrans trans;

4) The constructor will initialize the members declared. Constructor is below:

//RemoteServer_006: Constructor for the Remote Object
public RemCustomer()
    CustName = "Ram Gopal";
    CustId = 1235;
    Console.WriteLine("Instance of Remote object Created...");

    //RemoteServer_015: Instance for LastTrans created
    trans = new LastTrans();
    Console.WriteLine("Instance of Last Transaction object created. ");

5) Other member functions are shown below. These can be easily understandable.

//RemoteServer_007: Get the Customer id
public int Get_id()
    Console.WriteLine("Customer Id requested...");
    return CustId;

//RemoteServer_008: Get the Customer Name
public string Get_Name()
    Console.WriteLine("Customer Name requested...");
    return CustName;

//RemoteServer_017: Instance for LastTrans declared
public LastTrans GetLastTrans()
    Console.WriteLine("Transaction object requested...");
    return trans;

Now our remote class also ready to serve the windows form based client. Note that the MarshalByrefObject states that this RemCustomer will reside on the server and a reference to it will be sent to the client. The client calls that reference as a "proxy". We will move to our last step, which is the program entry point of a console application.

6. Hosting the Remote Object on the Server

1) Locate the static void main, which is the program entry point for the console application. In the top of the file (Program.cs if you had not changed the file name) include the assemblies as shown. Below is the code:

//RemoteServer_001: Required Assemblies
using System.Runtime;
using System.Runtime.Remoting;
using System.Runtime.Remoting.Channels;
using System.Runtime.Remoting.Channels.Tcp;

2) Next, a "TCP channel" with a "port number" 13340 is created and registered in the server machine. The client application will use this port to communicate with the remote object. The below code creates a TCP communication channel and registers that with the server machine in which the server application is going to run.

//RemoteServer_002: Create a communication channel (Server) and register it
TcpServerChannel SrvrChnl = new TcpServerChannel(13340);
ChannelServices.RegisterChannel(SrvrChnl, false);

3) Once the communication method is specified it is time to register our remote object on the server machine. The first parameter to the function specifies the type of the object that is registered. The second one gives a unique name for the registered object. I do keep the registration name same as the class name. But, you can use a different name. The client should use the same name when they ask for the Proxy of the remote object. The third parameter says how the server should activate the remote object. In this example, we are using the "Single Call method". That means for each client call an object will be created and maintained in the pool.

We will look at singleton method in next article.

//RemoteServer_009: Register the Remote Class so that the Object can go
//and sit on the Remote pool

4) The server is ready. Break the execution using the ReadKey method.

//RemoteServer_010: Halt the server so that Remote client can access the object
Console.WriteLine("Server is Running...");
Console.WriteLine("Press Any key to halt the Server");

If you want you can run the server now. But, there is nobody to consume the registered remote object on your server. We will now move on creating the Client. Below is screen shot:

 Remote Server session
Fig 2. Remote Server session

Note: The remote object is not created as the activation method is SingleCall. For each client call, a separate instance of the remCustomer will be created and kept in the remote object pool.

7. Prepare the Client Application

As you already know a client is a Windows form application. Follow the steps below to create the client application that uses the remote object hosted by our console server.

Make sure the Console server project is still in open state.

  1. Click on File|Add|New Project.
  2. Select Visual C# in the project Types and Select Windows Application from the available template.
  3. Name the project as RemClient and click ok.
  4. Design your form by referring the first screen shot. Download the attached project for your reference and set the properties by referring it. ConServer.sln will open both the project. In your case, this solution will be created now as you are adding one more project. A solution file is a combination two or more projects.
  5. Add reference for Remoting assembly as you did in the server step 3.
  6. Once your form is designed, double click the Button control to get the Click event handler for it.
  7. Right click the RemClient Project and select add reference. Go to the project tab and select ConServer Project.


In the above steps, you created a windows application and designed the form. You added a reference to the Dot net’s Remoting assembly and added a reference to the ConServer application. If we use the interfaces we can avoid this project reference. We will see that in a separate article.

8. Start coding the client

1) First, include the below-specified Name Spaces on top of the file which has the click event handler for the button. The code is below:

//RemoteClient_001: Include the required namespace
using System.Runtime.Remoting;
using System.Runtime.Remoting.Channels;
using System.Runtime.Remoting.Channels.Tcp;
using ConServer;

2) Now we will implement the click event handler. The button click will invoke the public functions exposed by the Remote object. Make sure the RemCustomer on your server application is Public. The first step is to get the Proxy for the remote object hosted by the Console Application, which runs on the Local or Remote machine. Below is the code for doing that:

//RemoteClient_002: Get the remote object on the server
RemCustomer remoteCust = (RemCustomer)Activator.GetObject(

In the first parameter, we specified what object we are looking for. In our case, it is the actual object type. The project reference we added is just to resolve this type (RemCustomer) name. But, in the interface approach, it is not required to reference the server project. Just an interface type is required on both server and client. If the client develops the application to invoke the third party remote server object then the server developers usually provide the interface. By doing that the server piece of implementation not revealed to the client developers. In our case, let us assume the same party develops server and the client.

The Second string parameter has three parts on it. The first one is machine name. As the client and server applications are on the same machine, I kept localhost instead of specifying the machine name. If the Server application is running on the machine name say; C-AshCorner_01 then the localhost in the string should be replaced by the machine name; that is; C-AshCorner_01. Note one can also use the IP address of that machine.

Well. The client now knows which machine it need make a connection to get the RemCustomer proxy. But the server may have lot of network communication right? It may be an Oracle server or Sql2005 server or even a Jboss web server. How does the server make communication? That is why we registered the server with a port number 13340. This port on TCP communication is called "Dedicated Port". Oracle has its dedicated port. Jboss has it an own dedicated port. Our application server registered the port 13340 saying “This port is in use like 80 for HTTP”.

Our client application by specifying this port number next to the machine name, sets-up the communication for a connection request. The last part specifies the registered name of the remote object. Look at Section 6 point 3.

Finally, we performed a type cast and stored the proxy in the remoteCust.

In summary to get the remote object,
1) First, a machine name in the network needs to be resolved
2) A port number that specifies which particular Server Application needs to be contacted.
3) The registered name specifies a single object type upon several registered remote objects (We registered only one)

3) Once the proxy is with us, make a call to the functions through a proxy. Note proxy just shows that the object is with you, but it is not. All the call is transmitted to the server to get the result. Below is the code, which makes use of the proxy object to make a call on the public functions, exposed by the remote object remCutomer.

//RemoteClient_003: Get Customer Id and Name exposed by the remote object itself
int id = remoteCust.Get_id();
string name = remoteCust.Get_Name();

//RemoteClient_004: Get Some other object from the server through the remote
//object. Note the object should be serializable.
LastTrans trans = remoteCust.GetLastTrans();
int lastdeposit = trans.GetLastDeposit();
int lastwithdraw = trans.GetLastWithdraw();

//RemoteClient_005: Display the information retrieved
txtCustId.Text  = id.ToString();
txtCustName.Text  = name;
txtLastDeposit.Text = lastdeposit.ToString();
txtLastWithdraw.Text = lastwithdraw.ToString();
btnInvokeRemote.Enabled = false;

The picture below explains how the application will work.

9. Testing the Application

Test it on Same Machine
1) Right click the solution and Click rebuild all.
2) Now navigate to both the exe using windows explore.
3) Run the Console Server
4) Run the Windows application
5) Click the button on the Windows application and Observe the result on the Client.

To Test it on different Machine, Place the Server project on the different machine. Keep the Client on your machine. Search for localhost in the client project and replace it with your machine name or IP Address. Run the Server first and then the client.

Below is the screen shot when Run both the application in the same machine.

10. Closing Note

1) To see the result live, download the Screen cam: Download
2) To know how to debug, download and see the Steps: Download

Source Code : Download

December 25, 2010

ASP.Net - Control Validation and Absolute Layout

1. Introduction

This article makes you understand what is validation control, how will you use server side controls. We will create simple asp dot net page to create a user account creation page. Below is screen shot of the form design:

How it Works

The user must enter his or her name in the first textbox. Then using the combo box the account type should be selected. Then the resident country combo box is used to specify the country. If name and account type is not specified, the form should report it so that user should make the correction. When account type selected is Forever the other country radio button should be hidden.

2. Designing the Form

2.1 Design Steps

1) As you see the controls are not in the default layout. If you already placed two or more controls you are aware the default flow layout. To make it appear like the above screenshot; change the layout to an absolute position. To do that click on "Layout|Position|Auto Position Option"
2) From the dialog displayed, select the Absolutely positioned option from the combo box.
3) After this, Place four Label, One text box, one Dropdownlist, 4 Radio buttons, One push button control on the form. Place it as shown in the above screenshot.
4) Place "Required Field Validation Control" next to the Name text box.
5) Place the "Compare Validation Control" next to the Drop Down List control.
6) Place "Validation Summary" at the end of the form.

2.2 Control Properties

To Set the Properties: Open the attached website for your reference, select each control and set the Property values that appears in bold for your web form. Or if you already downloaded the video you can sit back and watch it. I will explain some important properties below:

Account Type Dropdown:  The items are added using the items collection. Then the Auto-Post back property is set to false. If you note down, the How it works in the introduction section tells you that a radio button visibility is set to false when the last account type is selected. So the auto postback will send all the form information back to the web server when the selection in the combo box is changed.

Resident Radio button: For all the radio buttons, Group Name is set to grpResident. This allows all the four radio buttons in one group.

Required field: If you remember this control is placed next to the Name text box. The "Control To Validate" property tells which control needs to be validated by this validation control. In our example text box is selected for this property. Error Message property specifies what error message should be displayed when the validation control reports a failure. However, this error text will be displayed on the Validation summary control. The text property tells what needs to be displayed in the validation control itself when the validation fails. The error message is set to: Name is a required field. Do not leave it Empty. And text property is set to: *

In our example when the done button is clicked leaving the text box empty, an asterisk is displayed next to the text box and error message set to this control is displayed in the validation summary control added at the end of the form.

Compare Field: This validation control as the name states compares a value and reports success or failure about it. In our example, if you do not make any selection, the error is shown in the validation summary control displayed at the end of the form. In the reference, the application has a look at the bold property values for this control. Below is the explanation:

We do specify to this what needs to be compared. So Pick your Account which is string specified in the ValuetoCompare property. Then, as the type property is already a string, we no need to do anything. Set the Operator to Not Equal. Other property I hope you can understand as you already did it for the required field validation control. Now, when you change the selection, the Not Equal comparison is performed against the constant string Pick your Account.

3. Coding

Click the drop down list control in the property view and double click the Selected Index changed property. This will bring you to event handler code. Now place the following code in the event handler:

//Postback_001: Hide the Radio Button 3, when the Accout type is Forever
if (DropDownList1.Text == "Forever")
    RadioButton3.Visible = false;
    RadioButton3.Visible = true;

Note that we are setting the visibity of the Other Radio to false when the selection is “Forever”. As you set the Auto-Post back property to true, the control sends the post back message from the client to the server and this event run in the server machine and the html is pushed back to the client with a hided radio button.

4. Run the Application and Test it

1) When the form is visible, click the Done button. Note the required field and Compare validation is performed and the error message are displayed on the summary validation control.
2) Fill the name and Select Forever from the combo box control. Note that Server Auto post back by the push button is performed and other radio button is disabled.

Note: The attached application is created in VS2005 ide. If you have latest version say “yes” to the conversion wizard.
To use the attached application,
1) Extract the attachment
2) Select File | Open | Web Site…
3) Select the extracted folder

To See the Form Design live, Download the Screen Cam here.

Source Code : Download

December 19, 2010

C# - Picture Box and Progressbar Control

1. Introduction

In this article we will explore the "PictureBox" and "Progressbar" controls with an example walk through. Picturebox control is mainly used to an image. The image type can be bmp, jpg, gif, png etc. Progressbar control is used to show the progress of long running process visually.

I will walkthrough an example and explain the control properties and methods and events when it is used in the application.

2. Example - Preview

In the above screen shot, the image is displayed on the picturebox control when the form is displayed. Below you will see a set of "linklable" controls, which changes the "SizeMode" property of the picturebox control when a click event is produced on it. In the right side Group, we will try to load the image to the picturebox control at runtime both from your local machine and from the remote machine through Internet access.

The radio button shows how the image should be loaded. "Asynchronous" means a thread takes care of loading an image from the Internet or even local machine. If the image in the Internet is of huge size and load process does not block the user interface. You can still move the window for a test when the Asynchronous option is selected.

The progressbar is used in the above application to show the progress of loading the image and label (Not visible above) next to the progressbar shows the percentage of loading completed. OK. We will start this application.

3. Designing the Form

1) Open the attached application for your reference. I will call this as "reference application".
2) Create a new Visual C# windows application using visual studio 2005
3) Place all the required control discussed in the previous section.
4) First set the properties for the Picturebox control. You can do that by checking the property for the picturebox control on your solution opened in the first step. Check for the Bold Font property values in the reference application and those are all the properties changed by me.

Let me explain some of the properties for the Picturebox before you move to the next step.
If you click the Image property … button a dialog shown below is displayed.

Select the Resource file option and click the import button. Now you can pick any image on your system that you want to add as resource. When the form is displayed for the first time, we are going to use this image added as a resource. Once you selected the image click ok to this dialog.

In the explorer window you can see the picture loaded as a resource. This is shown below:

Now we mark this resource as "Embedded Resource". Once we make a resource as embedded resources, on the client deployment machine, we no need to copy the image file to a physical location. But, note that the file should exist on your build and packaging machine. Continue from the Step 4 now to mark this resource as embedded to the executable.

5) Right click the loaded resource (In my case it is Sunset.jpg) and select properties.
6) Select Embedded Resource for the "Build Action" property.
7) Nothing special with the LinkLabels. You can easily set it using the reference application.
8) Same holds true for Groupbox, Radiobutton and couple of button controls.
9) For Progressbar I accepted all the default values.
10) Do not forget to add a label control next to the progressbar, as it is not shown in the Preview section.

We completed our form design. Cross check the control names from the reference application, before we move to the coding.

4. Start the coding

1) For all the linklabel control provide handler for the "Linkclicked event". Inside the handler we are going to the set the sizemode property to some constant values. Below is the code for the entire Link label handlers:

//Image_001: Set the size mode by responding to the
//Label Click
private void LlblNormal_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
    pict.SizeMode = PictureBoxSizeMode.Normal;

private void LlblStertchMode_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
    pict.SizeMode = PictureBoxSizeMode.StretchImage;

private void lblAutoSize_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
    pict.SizeMode = PictureBoxSizeMode.AutoSize;

private void llblCenterImage_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
    pict.SizeMode = PictureBoxSizeMode.CenterImage;

private void llblZoom_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
    pict.SizeMode = PictureBoxSizeMode.Zoom;

Normal: Sets the image as it is and the picture box control is not resized.
StretchImage: PictureBox control is not resized. But, image size is adjusted to fit the full picturebox window.
Autosize: Image size is fixed and control is resized to hold entire image
CenterImage:  Both Image size and Picture box size is not changed. But the image on its original size is move to align the center of the image with the picturebox center.
Zoom: Fits the image to the full picture box control. But, unlike the stretch, the aspect ratio (Height:Width) is maintained.

Now Run the application and check these properties by clicking the LinkLabel buttons.

2) Provide the button click handler for the LoadLocal button. We are going to load the image from the local system. Copy the Image Winter.jpg from the sample application to the same relative location. If you chose to store it in a different path then you should change the relative path or specify the full path to the image. Below is the code that loads the image to picture box from the local machine.

//Image_002: Load the Image from the Local Path
private void btnLoadLocal_Click(object sender, EventArgs e)
    if (radNormal.Checked == true)
        pict.WaitOnLoad = true;

Note that the relative path ../../ means, move two directories above to locate the jpg from the path in which the application runs. This is known of relative path. That is; relative path to the path in which application is running. The below screen shot shows the new image loaded at the runtime from the local path.

3) Now provide the handler for the Load From Internet button. This handler will load the image from some Internet site. Setting the waitonload property to false, makes the loading process asynchronous so that your form can still receive the user input and responds to that event. Below is the code that loads the image from the Internet location:

//Image_003: Load the Image from the Internet
private void btnLoadInternet_Click(object sender, EventArgs e)
        if (radNormal.Checked == true)
            pict.WaitOnLoad = false;
        MessageBox.Show("Check that Internet Connection is available");

Note that we just provided the web URL to the image. Based on the option button we decided how we want to load the image.

4) Now provide the handler for the "LoadProgressChanged" event of the Picture box control. Unlike load method, the LoadAsync will raise the LoadProgressChanged event when some amount of image bits are download from the http path. Inside the handler we will use the event argument to decide how much of image is loaded. And the progress bar value is set with the percentage of completion retrieved from the event argument. Also the label control shows the number of percentage.

//Progress_001: Increment the value of the progress bar to show the image
//Load progress
private void pict_LoadProgressChanged(object sender, ProgressChangedEventArgs e)
    progLoadStatus.Value = e.ProgressPercentage;
    lblProgress.Text = e.ProgressPercentage.ToString() + "%";

The below picture shows Load from Internet button handler in the middle of the progress [Async option button selected]:

Below picture shows control when the Load process is fully completed:

Note: The attached application is created in Visual Studio 2005. If you have latest version say Yes to the conversion dialog. 

Source Code : Download

C# - Numeric Updown and LinkLabel Control

1. Introduction

"Linklabel" is kind of label control but it serves the concept of a hyperlink. With this control, you can mark portion the label text or even the entire label text with an underline. Clicking on the underlined text raises the event "LinkClicked". By providing the handler for this event you can take an action.

"NumericUpDown Control" is a combination of the textbox and a pair of arrows pointing is opposite direction. Clicking the arrow or holding the mouse pointer on the arrow will increment or decrement the associated value. The current value is displayed on the text box part of the control. When we click the arrow an event "ValueChanged" is raised for taking the action.

2. NumericUpdown control

The "value property" of this control is used to retrieve the current numeric value of the control. This value property is changed when the up arrow or down arrow buttons are clicked.

Minimum and Maximum properties are used to set the limits of a numeric value. Say for example we want the value can be changed from 0 to 255 (For changing the colour value), then Minimum property is set to 0 and Maximum property is set to 255.

"Increment Property" is used to increment or decrement the values in steps. For example, if we set the Increment to 5, and if the current value of the control is 0, clicking twice the up arrow button will change the value property to 0 to 5 first and then 5 to 10 next.

The importance of "ValueChanged Event" is already discussed in the introduction.

3. LinkLabel Control

"LinkColor property" is used to set the colour of the Underlined portion of the label text. The underlined portion of the label text is called as a link. So "linkcolor" sets the colour for the link when it is not visited yet.

"LinkVisitedColor Property" is used to set the colour of the link when it is visited. When the "LinkVisited Property" is set to true, the control picks the colour from the LinkVisitedColor property and sets that colour to link.

The "LinkArea Property" is used to set portion of the label text as a link. This property determines that based on the letters involved in the label text and specifying the link starting location in terms of character and length of character from that location.

We can take the action for clicking a link portion of the label by providing a handler for the LinkClicked event.

4. Example – Take a Try

Launch “visual studio 2005” and Create Visual C# Windows application. Once the application is created design the form as shown below:

Use the table below to set the properties for the control shown in the above screenshot.  Each control number in the above screen shot is referred in the C.Number column.

5. Programming LinkLabel and NumericUpDown Control

I hope by looking at the dialog design you can understand how it will work and no more explanation is required.

1) Create an event handler for the ValueChanged event of the numericupdown control. In this handler, we get the changed value of the control to adjust the width of the text box. Below is the code for that:

//Updown_001: Increase width of the Text box based on the current value
//of the UpDown Control
private void nuUpDown_ValueChanged(object sender, EventArgs e)
    txtBx.Width = (int) nuUpDown.Value;               

Now, if you click the up or down arrow button the text box will be resized to its width.

2) Handle the linkclicked event for the control number 1.  In the handler, we first show a message box and then set the LinkVisited property to true. This will set the different colour for the link. Code is given below:

//LinkLabel_001: Handler for the Link clicked event. Display a message when
//the link clicked.
private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
    MessageBox.Show("Hello There..");
    linkLabel1.LinkVisited = true;

3) Handle the LinkClicked event for the control number 2. In the handler resize the height of the form to show more controls also set "LinkVisited Property" to true. Code is below:

//LinkLabel_002: Handler for the Link clicked event. Display the remaining
private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
    //001: Set that the link is visited
    linkLabel2.LinkVisited = true;

    //002: Resize the form to show all the controls
    this.Height = 200;

 Source Code : Download

SQL 2005 - Creating database in SQL Server and understanding Data storage


This article shows how do you create a SQL Server 2005 database. Here, we will use SQL server management studio to generate SQL Script that creates the database. Also it explains, how storage is allocated for the database created and explains files involved in creating the database.

Steps to Create a Database

1) Launch the SQL Server 2005 by using the sa login.
2) In the left side pane, right click the Database Node and Select New Database from the Context menu.

New Database...
Fig 1. New Database...

3) The New Database option will open a dialog for creating the database. Portion of the dialog is shown below:

Create Database Dialog
Fig 2. Database Creation Dialog

4) In the Database name field type Sample. This will fill the Data and Log file names for you in the Database files grid. But, you can override these default file names supplied by the dialog by your own database file names. The screen shot below shows typing the Sample in the database name field:

Database Name
Fig 3. Provide Database Name

In the above picture, blue box shows the files created by the dialog based on supplied database name Sample. File type data denotes the Sample.mdf file and Log denotes the Sample_Log.ldf file. MDF is the Main Database File and LDF is used for logging purpose. Do not confuse LDF file with the Transaction Log used for the Database recovery.

5) Scroll the database files grid to the left to see the path in which these files are stored. If required, you can provide a different path by clicking the ellipsis button(marked in red) shown below:

Data Files
Fig 4. Database File Names

6) If you click the OK button on the dialog, the database will be created for you. But, do not click the OK button now. Instead, Click on the Down Arrow button next to Script  and select the First option "Script Action to New Query Window" as shown below:

Script Action
Fig 5. Script Action

7) Now you can use this script to create the database. This script will take care of all the actions you did in the dialog. Also, it is useful when you deploy the database application on your client machine. Running the script through a setup program will create the database on the client machine. OK., Now, Click the Execute button and create the database. 

If the database is not displayed under the database folder, right click the database folder and select the refresh.

T-SQL for SQL Database Create
Fig 6. Generated T-SQL Script for Database Creation

How the data stored in the MDF file

The database Sample is now ready. When you create a table and insert data into it, the size of the Sample.mdf gets increased as all your information goes to this file. The data is stored in the form of something called extents. A database file has multiple extends when the data is increased. A Maximum of 8 pages constitutes a single extent. A Page is a block of storage, which has a maximum of 8 KB in size. A database row can be a maximum of 8 KB in size. This excludes the large data type columns say Text, Image, Varchar(max) etc. That means a database row excluding the large data type can fit into a single page. Also note that large data type values are stored in a separate page and location offset is stored in the page which has normal data like integer and char.  Below picture shows how the storage in physical media (Disc) is organized:

Pages and extends
Fig 7. Pages and Extends

Like this site? Tell it to your Friend :)