Tuesday, December 27, 2011

[ASP.Net] - Using SQLDataReader and SQLCommand

1. Introduction

Almost every business web application makes use of databases to store and retrieve data. ADO.net is next generation to the ADO (Activex data Objects). In this article, we will retrieve some column data from the employee table of the NorthWnd database.

Look at the below illustration on how we are going to take data from the database.

2. About the Sample

Look at the screenshot of the sample below:

Our sample in the explorer looks like the above one. When the sample loads the page, it contacts the SQL Server NorthWnd database and retrieves the data to display the label shown in yellow colour. There is nothing more to specify here. Let us move to the next section.

3. Connection String in Web Configuration

We know that the web page is going to connect to a database and pull data from the employee table. The first thigh every database programmer should do is specifying a way to connect to the database. In our sample, we are going to connect to SQLServer database NorthWnd to pull some information from the employee table.

The Connection object tells the application how to connect to the database. Using the methods or constructor of the Connection object one can specify the information required to connect the database with your application. Here, we are going to use the connection string and we will place that constant string as part of the web configuration file.

Once you start a new website you can see the web configuration file in the Solution Explorer. This is marked in the below picture.

A web configuration file is the one in which you specify all the web application-related settings. Doing so will avoid the further builds to your application. Say for example you have the connection string to connect to the Database db1 at server Srv1. When you want to connect to a different database db2 on a different machine Srv2, all you need to do is change that information in the configuration file and your website keeps working without any code change.

The changed portion of the web configuration file is shown below:

The box shows that we added a connectionstrings section that spawns the information between the tags Open <> and End </>. Note that we are using the System.Data.SQLClient provider. A provider is a bridge between your application and the database and they are the communication channel acting as translator. In the above connection string, I specified the Server Name as System (My Local machine name). If your database is not on the local machine you should specify the name of the computer in the network or IP address of the machine name. NorthWnd is the database I am going to connect on my local machine for this example to access the employees table. And finally, the connection string contains the qualified (of course it is. As sa is an admin user) user id sa and password rohith. Now the connection string informs that you are going to connect to Northwnd database in the Local machine System using the database credential sa&rohith.

To know your system name or the name of the system in the network in which the database in running, right click on the MyComputer icon and select properties. From the displayed dialog’s second tab pick the Full Computer name. This is shown below:

4. Page Design

Once the connection string is specified in the web configuration file, the default aspx file name is changed to datareader.aspx. Then in the design view of the page three labels are added as shown below:

To change the label name go to the source (Highlighted in red) and change the ID for the control. Sometimes changing the ID in the property window of the control will not reflect back and hence it is good to make the change in the HTML source of the page. This is shown in the below video: 

OK. Now let us move to the Source code of the form. Note when the page load we are going to connect to the SQL Server database and will retrieve some data from the employees table of the NorthWnd database.

5. Source: Config File

We already have look at the configuration file. In this file we specified our connection string to the database which is shown below:

            <!-- DRead 001: Open the connectiontring section-->
                        <add name="SQLServerDatabase" providerName="System.Data.SqlClient"
                                                User ID=sa;

6. Source: Reading the configuration

First, the form load event handler is handled and in the form load event handler, we need to connect to the database. To connect to the database we need the connection string. And, we already specified our connection string in the Web Config file. So this situation now leads us to the task of reading the information from the web configuration file.

By default the namespace System.Configuration is included in your source file. If not include it using the statement as shown below:
using System.Configuration;

Then have a look at the below code:

//DRead 003: Get the connection string from the configuration file
ConnectionStringSettings appsettings = ConfigurationManager.ConnectionStrings["SQLServerDatabase"];
string ConnString = null;
if (appsettings != null)
    ConnString = appsettings.ConnectionString;

In the above code snippet, we are reading our connection string from ConfigurationManager using the Name SQLServerDatabase from the collection ConnectionStrings, which is the collection of objects of type ConnectionStringSettings.  This value is stored in the appsettings. Note that in the webconfig file we used only one connection string. But you can have multiple connection strings between the tags <connectionStrings>            </connectionStrings>

Finally, the string format of the connection string is stored in the ConnString. Now we know the connection information to the database in the form of a string.

7. Source: Connection Object

The connection object knows where your database and how to access it. So the connection object can be created easily by supplying the connectionstring as it has all the information a connection object need. Below is the piece of code, which creates the connection object in our example:

//DRead 004: Create the Connection to SQL Server
SqlConnection Connection_toSQL = new SqlConnection(ConnString);

8. Source: Command Object

The command object will say what you want to do with the database. It usually contains SQL Statement that needs to be executed in the database and well as connection information to know where it should be executed. In the below piece of code, a table select query is stored in the string. Then supplying the query string and connection object creates a command object. Now command object is capable enough to retrieve some fields from the employee table of the Northwnd database in SQL Server. 

//DRead 005: Form the command object
string Query = "Select FirstName, TitleOfCourtesy, Title from employees";
SqlCommand command = new SqlCommand(Query, Connection_toSQL);

9. Source: DataReader and Iterating through it

OK. The command is capable of executing the SQL statements. When the SQL statement is a Select statement, then the database will give back one more rows of information. Where to we store that information? In our sample (There are other ways too. We will see that in some other article) we are making use the DataReader object to collect that table of information. The datareader is forwardonly and that means you can read the information only once and move forward. This tells you that once you read something to store it in your local variable, as you cannot read it again. Below is the piece of code, which first executes the command object, gets the resultant record collection in the reader of type SqlDataReader and then it iterates through the reader to get the column name queried.

//DRead 006: Open the connection and get result in DataReader
SqlDataReader reader = command.ExecuteReader();

//DRead 007: Iterate through the reader
while (reader.Read())
    string line = "";
    line = string.Format("{0} {1} is Working as {2} </br>",
        reader["TitleOfCourtesy"], reader["FirstName"], reader["Title"]);

    lblResult.Text = lblResult.Text + line;


The resultant string is the combination some constant with the data retrieved from the database. And the string assigned to the label with the </br> tag to insert a new line after reading one complete record. Also, note that the column Read method of the X will return a column of columns that forms a complete a row of what is queried. To refer a particular column, you can use the column name or the index. The reader["FirstName"] states that we want to get the value for the column FirstName.

Source Code : Download

Tuesday, November 22, 2011

SQL 2005 - Bulk insert from Text File and Global Temp Tables

1. Introduction

In this article, we will look at some more techniques of inserting data. First, we will explore the usage of the "Temp tables". Then we will look at the "bulk insert using the text files".

2. The Temp Table

As the name suggests the table is temporary and it will get deleted after the usage. SQL developer uses a temp table to store some temporary result in it and query them later. Say for example, if your computation involves data from 6 or 7 tables, apart from other techniques Temp table also one nice technique to make calculations and store it temporarily for later use in that session.

To create the temp table, use the following syntax Example:
Select <Column_list> into #<Temp_Table_name> From <OriginalTableName>

In the above syntax:
Column_List: The list of the column from the main table. You can use comma separated column names or * for all columns
#<Temp_Table_name>: Name of the temporary table. Ex: #MyTemp
 <OriginalTableName>: Name of the original table from which the data is retrieved

Below is the example screen shot for the usage of the temp table. The NorthWnd database is used in two different query window.

Query Session 1

Fig 1. Temp Table at session 1

Query Session 2

Fig 2. Temp table at session 2

We have two queries windows shown in screen shots Fig1 and Fig2. In one query window we are filtering the employees from Seattle and in another window we are filtering the employees from London. The information retrieved is stored in the #EmpTemp temp table. The table with the # sign is known as "Temporary Table". In our example, two temporary table with name #EmpTemp is created for the query session 1 and 2. The two query sessions are shown in the above screenshots with red and green boxes.

The temp table will get deleted automatically when the Query window is closed. That means, the scope of the temporary table is until the end of connected session. And note that each query window maintains a separate connection to the database. This is why you get an error (Stating temp table already exists) when you try to execute the query shown above again in the same query window. 

Now If you look, I used same #EmpTemp in the second query window (Marked as green). Does it effects the content of the #EmpTemp created in the query window marked in red? No. Because, the temp table scope is session based and SQL see both the temporary table as two different instances even though they have the same name.

Note that you can also use create table query to create the temporary table and then insert the rows inside it.

3. Global Temp Table

Like temp table, you can create the "Global Temp Table" using the ## token before the table name. Right. What is global temp table? The global table is one, which is visible to all the active sessions. Let us say there are 12 users (Or connection. Remember it includes client code connecting to the database) when the global table is created.  Now, this global temp table is available for all 12 users or connected sessions. Now consider the situation below that will make you understand this:

1) All 12 Users active.
The Global table is available for all the 12 users

2) 3 Users disconnected and a new user, say B is connected to SQL Server.
The Global table is still available for remaining 9 Users as well as the newly connected user/Connection B

3) All users disconnected except B
The Global table is still available as the user B is active. Note that this user established a session when the global table is alive. That means he is also possibly using it. So SQL still allows Global temp table to be alive.

4) User/Connection B is also terminated and there is no user at present.
The global temporary table is deleted and it is no longer available.

Below is the usage of the Global temp table and note the ##sign before the table name.

Fig 3. Global Temp Table Example

The QLQuery4 window is making a connection to SQL Server and the “into ##” statement is creating the global temporary table. Now, I opened one more Query window QLQuery5 and this will create one more connection to the SQL server. Then the global table created is accessed in this new session:

Fig 4. Accessing Global Temp table from a dif

It proves that the Global table is accessible by other connection also. Think like this; the second query window (QLQuery5) is opened on a different machine and the global table is accessible there as well without any problem. Also, note that the Global Table also temporary and it does not belong to any schema.  You can access this table when you are in NorthWnd or Pubs or master. It does not matter.

Now close all the SQL Window (I am assuming that you do not have any client application/network users still maintains a connection to the server). Execute the query shown in the QLQuery5 by opening a new session. What happens? Object does not exist, right?

  1. Once you close the entire query window, the global temp table ##EmpTemp is deleted (In our case)
  2. When you opened a new query window, a new session is created and the Global table becomes no more for it.

4. Bulk insert from text file

As the title suggested we are going to see how do we insert a bulk number of records from a text file to an SQL Server table. The technique is; we should have the records in the text file in such a way that columns and rows of records are terminated by unique letters. We call these letters as "Field Terminator" and "Row Terminator". We will experiment this with a quick walk through.

1) Connect to the NorthWnd database. (You can download the T-SQL for it from SQL 2005 Page of this web-site)
2) Then Query the product table as shown below:

Fig 5. Prepare Text file content for Bulk Insert

3) Next, Click on the No Column name to select the entire row under this single column.
4) Then, Right click and copy the selected rows and paste it to a notepad.
5) Save it as Products.txt in D:\

The text file now has the data for the bulk insert explanation. Note that the field terminator is ‘~’ and row terminator is new line character ‘\n’. Now we will create a table that will consume the data from this text file.

6) Create a table as shown below in the NorthWnd DB

Create table BLKInsertTest(ProdId int, Prodname varchar(40), UnitMeasure varchar(21));

7) Run the below Query to insert the data from the text file to the table created in the previous step

Fig 6. Bulk insert from text file - Example


1: Specifies the name of the file from which we are going to pull the data for the table BLKInsertTest.
2: Field terminator character that tells where each column value ends in the text file for each row.
3: Row Terminator tells what is the row terminator. In our example new line is the row terminator.

Note that executing the query inserts 77 rows at once.

See U in the Next article.

Saturday, November 19, 2011

SQL 2005 - How do I use various Constraints in a database?

1. Introduction

"Constraints" are rules that decide what kind of data can enter into the database tables. SQL server has six types of constraints and we will explore all these constraints here with suitable examples. The constraints that we are going to explore are listed below:

  1. Primary Key Constraint
  2. Foreign Key Constraint
  3. Not Null Constraint
  4. Unique constraint
  5. Default Constraint
  6. Check Constraint

2. First Create two tables

To explain all these constraints, we need two tables. First, let us create these tables. Run the below scripts to create the tables. Copy paste the code on the new Query Editor window then execute it.

CREATE TABLE Student(StudId smallint, StudName varchar(50), Class tinyint);
CREATE TABLE TotalMarks(StudentId smallint, TotalMarks smallint);

Note that there are no constraints at present on these tables. We will add the constraints one by one.

3. Primary Key Constraint

A table column with "Primary Key" constraint is called as the "key column" for the table. This constraint helps the table to make sure that the value is not repeated and also no null entries. We will mark the StudId column of the Student table as a primary key. Follow these steps:

  1. Right click the student table and click on the modify button
  2. From the displayed layout select the StudId row by clicking the Small Square like button on the left side of the row.
  3. Click on the "Set Primary Key" toolbar button to set the StudId column as a primary key column.
Fig 1. Setting key column for the Table
Now StudId column does not allow null values and duplicate values. You can try inserting values to violate these conditions and see what happens. A table can have only one Primary key. "Multiple columns" can also participate on the primary key and in that we call that as "Composite Primary Key". When the key is composite, the uniqueness is considered among all the participant columns by combining their values.

4) Not Null Constraint

The "Not Null" constraint is useful to stop storing the null entries in the specified columns. We will mark student name column as a not null column. This allows us always having some entries in the student name column of the student table without having NULL. Follow the steps below:

  1. As you did previously, bring up the table design view by clicking the modify context menu for the table.
  2. Remove the check mark as shown in the below picture. This action will enable the Not Null constraint for the StudName column.

Fig 2. Setting Not Null Constraint

5. Default Constraint

"Default" constraint allows you set a default value for the column. That means when a row is created for the first time, and there is no entry specified for the column that has default constraint set, then the default value is stored in the column. Note that this is not a Not Null constraint and do not confuse the default value always enters the column disallowing the Null entries. The default value for the column is set only when the row is created for the first time and column value is ignored on the Insert. Modification to the column with NULL value  or even the Insert operation specifying the Null value for the column is allowed.

Let us set the Default value of 1 for the Class column of Student table. Follow these steps:

  1. Bring up the table designer
  2. Select the Class Row as you already did.
  3. At the bottom of the layout, you will see a Column properties as shown in the below picture. Set the default as shown below:
Fig 3. Setting default constraint

6. Unique constraint

A "Unique Constraint" does not allow the duplicate values. But, the column can have multiple Null values. For our example, we do not require any unique constraints. Follow the below example if you want to set a unique constraint for a column:


The above T-SQL sets a unique constraint for the column StudName. UQ_CONSName is the name given to the constraint. It will be useful if we want to delete the constraint later.

7. Check Constraints

"Check Constraints" are custom rules that can be applied on the column to take the decision of value is allowed or not. Let us set some check constraint for the TotalMarks column of the Student table. The data can be stored in this column only when the data is greater than zero or less than 1200.

1) First, set the not null constraint for both the columns in the table.

Fig 4. Set Not Null

2) Expand the TotalMarks table and right click the Constraints folder. From the displayed context menu select the "New Constraint"

Fig 5. Add new check constraint

3) From the displayed check constraints dialog, we set the above said check rules for the column. To do that, type the expression for the column TotalMarks in the Expression field. The expression is: (TotalMarks < 1201) AND (TotalMarks > 0)

Fig 6. Add check condition for check constraint

4) Change the name of the constraint to CK_TotalMarks. Then click the close button. Refresh the Constraints folder by selecting the refresh from the context menu of the Constraints folder. The added constraints with the name is shown below:

Fig 7. Check constraint in the database object tree

Now the Total Marks column does not allow the negative marks or marks more than 1200.

8. Foreign Key Constraint

"Foreign key" constraints are useful for maintaining the relationship between two tables. If a column allows the values only when the value exists on the column of some other table then the column is said to have the foreign key.  A foreign key should refer primary key or unique key column of some other tables.

In our example, we will create a foreign key for the column StudentId of the TotalMarks table. Let me introduce the Database diagram to do this task. You can play around it as the diagrams are not only to show the existing database relationship, they exist actually to design your database in an easy way. You can change the data types, add relationships (That we are going to see now), introduce constraints etc..

Follow these steps to create the Foreign Key constraint using the database diagram [The easy way]:
1) Expand the database in which you created the two tables for this example. Then Right click the Database diagram and choose "New Database Diagram"

Fig 8. New database diagram

2) In the add table dialog select both the tables. If you use a different database then select the tables that we created for this example. Then click the Add button. Click the close button to dismiss the dialog.

Fig 9. Add Tables to database diagram

3) You will see the following two tables on the design surface

Fig 10. Selected tables in the database diagram

4) Now hold down the left mouse button on the StudId and drag & drop the mouse pointer on the StudentId. This will create a foreign key in the Total Marks table.

Fig 11. Add Table relationship using database diagram

5) Accept the default  by clicking the OK button from the displayed dialog after reviewing the details it displayed

Fig 12. Set Name for Foreign Key

6) The database diagram now shows the relationship between the tables

Fig 13. Table relationship as shown in the database diagram

Note: All the Steps are done using the SQL Server 2005. I am stopping it here. You can go ahead and examine each constraint by inserting some sample data to the tables.

Tuesday, November 01, 2011

[ Dot Net Remoting ] - Accessing Http Remote objects using SoapSud Generated Metadata

1. Introduction

In the previous examples, we used "TCP Channel" to communicate the remote object in the server machines. Also, we have the reference to the server project in the client development projects.  Giving the code to the client is not advisable as the client can go ahead and strip the given to know some of the implementation details.

In this article, we will look at how do we use the "Http channel" for communication and how do we create the Metadata proxy from the server deployed remote objects using the "SoapSuds" command line utility. OK. Let us start. The explanation is reduced here. Read my first article on Dot.Net remoting link for basic details on remoting.

2. The Http remote Server

1) The server is a C# console application. It has a simple remote object called RServer. Please note that the namespace name also RServer. It is not good having namespace and class name in the same name. So I would recommend separating the class name from the Server namespace. As usual, our RServer class is derived from the MarshalByRefObject class to make it a remote object.  Below is the code for it:

//Server 01: Required Declarations
using System.Runtime.Remoting;
using System.Runtime.Remoting.Channels;
using System.Runtime.Remoting.Channels.Http;

//Server 02: Http based Server
class RServer : MarshalByRefObject
    public void TestObject()
        Console.WriteLine("Object Test. Test Object Function called" + Environment.NewLine);

In the constructor, we are just printing some message so that we can ensure whether server object is created or not just by looking at the console window of the server. Also, note that this time we are going to use the HTTP channel and hence included the channels.http (using System.Runtime.Remoting.Channels.Http;)

2) In the server application main, we are creating http channel and then registering the remote object as a SingleCall object. As the basic example (First article on DotNet remoting) has all the required explanation, I am skipping those repeated details here. Below is the code:

static void Main(string[] args)
    //Server 03: Create a http channel for soap and register it.
    HttpChannel httpchnl = new HttpChannel(7212);
    ChannelServices.RegisterChannel(httpchnl, false);

    // Server 04: Register the server object
    RemotingConfiguration.RegisterWellKnownServiceType(typeof(RServer), "RServer",

    // Server 05: Make the server running
    Console.WriteLine("Server Started" + Environment.NewLine);

3. Generating MetaData dll to share with client

Once the server is ready we are all set to go ahead and create the Metadata assembly that can be shared with the client piece code. Remember in the previous example when I used the TCP communication channel, I usually add whole server project as the reference. The alternate technique to that is having the declaration in a separate assembly (dll) and shipping that to the client. Here we are going to generate separate metadata dll for our server and going to ship that dll to the client.

To generate the Metadata dll, first, run the server project or Exe. When the server is in the running state, launch the Dot net command prompt and access the "SoapSuds" utility. Below is the option switches used to generate the metadata assembly for our server example:

SoapSuds –oa:<OutputName>.dll –Url:http://Localhost:<PortNo>/<RemoteObject>?Wsdl

In the above command,
oa - option specified the output path and name of the dll
-URL – option specifies the http url to the remote object for which we want to create the meta data dll

Creating the DLL is shown in the below video.


4. Consuming the metadata dll in the client

The client is also Visual C# console application. Once the client project is created, after giving the reference to dotnet remoting, the reference to the metadata dll is given using the browse tab of the add reference dialog box. Once the reference is given, we have access to the remote object and we can create it using the new operator.

Getting the reference to the metadata dll in the client project is shown in the below video.


5. Accessing the Remote object through Http

Once you created the reference to the Meta dll formed on the server machine using soapsuds, you can simply access the remote object using the new operator just like you create normal objects. Below is the explanation for the code:

1) RServer is the namespace in the server. Note that in the server implementation the Remote class name, as well as the namespace name both are same.

//Client 01: Use the Meta data dll.
using RServer;

2) The remote object is created using the new operator. But, in the background, through the Meta data DLL, we are making a call to the wrapped assembly to get the proxy. This proxy object actually calls the real object at the server end. And it knows the communication protocol, server address and port number of the communication.

//Client 02: As we have proxy to the remote as wrapped meta data, you can use new oprator
//              to create the object
Console.WriteLine("Creating the Instance of Remote object\n");
RServer.RServer RemoteObj = new RServer.RServer();

3) The remaining lines of code are simple as it just makes a call to the function exposed by the above-created object. In user perspective, it is just an object. But, the function call is executed on the server. You can observe that by looking at the server machine’s console window. Below is the piece of code that does not require much explanation:

//Client 03: Calling the Remote method
Console.WriteLine("Press any key to Make a call to Remote function\n");
Console.WriteLine("Press any key to Close");


Source Code : Download
Note: The sample is created using the VS2005 IDE. 
Like this site? Tell it to your Firend :)