Working with XML (Part I)

Introduction:
XML is one of the most fast developing technologies in internet based applications. XML is used not only in internet but also in many other applications. Now a days Microsoft technologies concentrates more in XML. XML standards are developed by W3C (World Wide Web Consortium). XML standards are very simple and are formatted well for data access. Since most of the communication channels are through internet, there may be some transfer of data and the data should be well structured and formatted, XML is helpful for transferring these types of data. .NET provides a great support to the XML. You have separate Namespace for accessing the XML. System.Xml is the name space accessing the XML documents; it has a huge set of objects, methods and properties.

Visual Studio .NET tool provides a great support for creating the XML file and their schema using editor. To create a XML file, right click your project in Solution Explorer  Add  Add New Items this opens a dialog box.

Figure 1

Select the XML file from the templates and name the XML file. This XML file is stored in the project and only XML directives are loaded in the XML file. Now you need to structure the XML format for your data. The sample data is structured in the format as shown below

<?xml version="1.0" encoding="utf-8" ?>
<UserName>
<UserId Id="1">
<LoginName>Bill </LoginName>
<Password>Password</Password>
</UserId>
<UserId Id="2">
<LoginName>Gates</LoginName>
<Password>Password</Password>
</UserId>
</UserName>

You can also add the check the data in design view. The design view is as show in

Figure 2

Reading and writing in XML is done using XMLTextReader and XMLTextWriter. These are present in the object System.Xml namespace. This type of process can also be done using XMLDocument object. But XMLTextReader and XMLTextWriter is simpler when compared to document objects. XMLTextReader is just like an DataReader objects it is a forward only and read-only cursor. Let’s see an example to Read the Xml using XMLTextReader.

VB Code:

Dim strPath As String = "D:\MyProject\MyFirstApp\MyXML.xml"
Dim sr As New StreamReader(strPath)
Dim Reader As New XmlTextReader(sr)
Dim iCount As Integer = 1
While Reader.Read()
If (Reader.NodeType = XmlNodeType.Text) Then
iCount = iCount + 1
Response.Write(Reader.Value+"<hr>");
End If
End While
Reader.Close()
sr.Close()


C# Code:

string strPath = @"D:\MyProject\MyFirstApp\MyXML.xml";
StreamReader sr = new StreamReader(strPath);
XmlTextReader Reader = new XmlTextReader(sr);
int iCount=1;
while (Reader.Read())
{
if(Reader.NodeType == XmlNodeType.Text)
{
Response.Write(Reader.Value+"<hr>");
iCount++;
}
}
Reader.Close();
sr.Close();

The above code helps to read the values in the XML files. Now let’s see how you can write some values in XML files
VB Code:

Dim strPath As String = "D:\MyProject\ MyFirstApp\MyXML.xml"
Dim writer As New XmlTextWriter(strPath, Nothing)
writer.WriteStartDocument(True)
writer.WriteStartElement("UserId")
writer.WriteAttributeString("ID", "1")
writer.WriteElementString("LoginName", "Bill")
writer.WriteElementString("Password", "Password1")
writer.WriteEndElement()
writer.WriteEndDocument()
writer.Close()

C# Code:

string strPath = @"D:\MyProject\MyFirstApp\MyXML.xml";
XmlTextWriter writer = new XmlTextWriter(strPath,null);
writer.WriteStartDocument(True);
writer.WriteStartElement("UserId");
writer.WriteAttributeString("ID", "1");
writer.WriteElementString("LoginName", "Bill");
writer.WriteElementString("Password", "Password1");
writer.WriteEndElement();
writer.WriteEndDocument();
writer.Close();

Output:

<?xml version="1.0" standalone="yes"?>
<UserId ID="1">
<LoginName>Bill</LoginName>
<Password>Password1</Password>
</UserId>

The above code writes the values into an XML files. Here you use WriteStartDocument for XML document; it helps to open a directive in the xml file. WriteStartElement helps to open a starting element of the node and then the attribute string and elementstring is added in it. Both XMLTextReader and XMLTextWriter are the simple method for accessing the data in XML files.
Conclusion:
In Part II you will see how to work with DataSet and XML Document Object Model(DOM)

Accessing data from Relational Database and flat files.

Introduction:
Accessing the data from relational database is similar to accessing the data from MS SQL Sever. Currently you have lots of relational database like MS SQL, MY SQL, Oracle DB2 ect., you can access these database using ADO.NET OLEDB or ODBC provider. As all these providers have huge types of different data source, all that you need to do is to import the namespace for the provider that you are planning to use.

VB Code :
Imports System.Data.Odbc
Imports System.Data.OleDb

C# Code :
using System.Data.Odbc;
using System.Data.OleDb;


All the objects in ADO.NET ODBC and OLEDB provider are same as that of ADO.NET SQL provider. That is here we have DataAdapter, Command, Connection.etc.
The available objects for ODBC provider are
- ODBCDataAdapter
- ODBCConnection
- ODBCCommand
- ODBCDataReader

The available objects for OLEDB provider are
- OLEDBDataAdapter
- OLEDBConnection
- OLEDBCommand
- OLEDBDataReader

Accessing the data is same as what you do in SQL provider. The following example shows how to use this.

VB Code:

Dim dsetMydata As New DataSet
Dim connString As String = "DRIVER={MySQL ODBC 3.51 Driver};Server=local;Database=MyDB; UID=XXXX;pwd=XXXX;"
Dim strQuery As String = "select * from authors"
Dim myAdapter As New OdbcDataAdapter(strQuery, myConn)
myAdapter.Fill(dsetMydata)

C# Code:

DataSet dsetMydata = new DataSet();
string connString = "DRIVER={MySQL ODBC 3.51 Driver};Server=local;Database=MyDB; UID=XXXX;pwd=XXXX;";
string strQuery = "select * from authors";
OdbcDataAdapter myAdapter = new OdbcDataAdapter(strQuery, myConn);
myAdapter.Fill(dsetMydata);

The above code gets the record from the data source. Here the data source is MySql and you have to be careful while writing the connection string. If the connection string is not correct, you will get an error message. For OLEDB data provider is also as same as this. You need to set proper connection string to it.

Now you will see how to read the data from flat files. In olden days flat files were considered as a data storage medium. The flat files were placed in some location and could be accessed through the application. One of the major advantages of using flat file is accessing of data will be more fast, but has disadvantages too like
- It is not a secured one.
- Cannot set relationship between data
- Huge amount of data reduces the performance.
Since there are lot of databases now, you don’t use these flat files more often. Sometime it is required to access the data from flat files. In .NET you have separate namespace for accessing the files, System.IO. This namespace has lot of objects for accessing the flat files and binary files.

You will now see an example to write some text in the flat file. Here you will use .txt file type. The code below helps to write text in text file

VB Code:

Dim strPath As String = "C:\MyFile.txt"
Dim sw As New StreamWriter(strPath)
sw.Write("This is test for writing some text in Flat files")
sw.Close()

C# Code :

stirng strPath = @"C:\MyFile.txt";
StreamWriter sw = new StreamWriter(strPath);
sw.Write("This is test for writing some text in Flat files");
sw.Close();

In the above code you get the path where you need to write the file. StreamWriter object is used to write the flat file. The StreamWriter object should be closed after the execution, if not it returns error when you attempt to open the file.

The below code is used to read the flat file. This is similar to the writing of the file. Here you use StreamReader object. This helps to read the content of the file.

VB Code:

Dim strPath As String = "C:\MyFile.txt"
Dim sr As New StreamReader(strPath)
MessageBox.Show(sr.ReadToEnd())
sr.Close()


C# Code:

stirng strPath = @"C:\MyFile.txt";
StreamReader sr = new StreamReader(strPath);
Response.Write(sr.ReadToEnd());
sr.Close();

Conclusion:
Hope this article will help you to understand how to get work with Relational Database and Flat files. In next Article I will come up with other new basic stuffs.

Getting Output Parameter from SQL Store Procedure

Introduction:
In this article you can learn how to use store procedure and to get the Identity column value as an out parameter in SQL server using c#. Some beginners feel very hard to get the out parameter in SQL store procedure using VB or C# as a back end and this article will overcome this scenario. In this article, I have used very simple coding for understanding the usages of store procedure.

The store procedure is the best method of getting, inserting, updating and deleting the records in the database table in SQL server. It will be fast in manipulating the data when compare to writing a SQL Query in VB or C# code.

Before going to the article, first we can create a table in Pubs database and a store procedure in the same. Execute the following SQL query in the query analyzer at Pubs Database.
For Creating Table:
CREATE TABLE [dbo].[DummyTable] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[User_FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[User_LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

after executing this query, three column will be created User_FirstName, User_LastName
and Id column as Identity column.
For Creating Store Procedure:
CREATE PROCEDURE [InsertDummy]
@User_FirstName varchar(50),
@User_LastName varchar(50),
@OutParamID int output
AS
insert into DummyTable (User_FirstName, User_LastName) values(@User_FirstName, @User_LastName)
set @OutParamID = @@IDENTITY

On executing the above query, table and store procedure will be created and we can access that using code.
In aspx page add two textbox and a button. these TextBoxs will get the first name and last name of a user and if you Submit it (click it), the values in the TextBox will insert to the table.

In this article I will explain accessing the store procedure in C# & VB. Following code will help to insert a record in to the table and get the Identity column value of the inserted row.

C# Code:

public int InsertDummyTable()
{
SqlConnection cn = new SqlConnection(constring);
SqlCommand cm = new SqlCommand("InsertDummy",cn);
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.Add(new SqlParameter("@User_FirstName",SqlDbType.VarChar)).Value = txtFirstName.Text;
cm.Parameters.Add(new SqlParameter("@User_LastName",SqlDbType.VarChar)).Value = txtFirstName.Text;
SqlParameter outPar = new SqlParameter("@OutParamID",SqlDbType.Int);
int ourPhar =0;
outPar.Value = ourPhar;
outPar.Direction = ParameterDirection.Output;
cm.Parameters.Add(outPar);
cn.Open();
cm.ExecuteNonQuery();
int returnId;
returnId = (int)cm.Parameters["@OutParamID"].Value;
cn.Close();
return returnId;
}

private void cmdSubmit_Click(object sender, System.EventArgs e)
{
Response.Write(InsertDummyTable().ToString());
}

VB Code:
Private Function InsertDummyTable() As Integer
Dim connstring As String
connstring = "" ' Declare connection string.
Dim cn As New SqlConnection(connstring)
Dim cm As New SqlCommand("InsertDummy", cn)
cm.CommandType = CommandType.StoredProcedure
cm.Parameters.Add(New SqlParameter("@User_FirstName", SqlDbType.VarChar)).Value = txtFirstName.Text
cm.Parameters.Add(New SqlParameter("@User_LastName", SqlDbType.VarChar)).Value = txtFirstName.Text
Dim outPar As New SqlParameter("@OutParamID", SqlDbType.Int)
Dim ourPhar As Integer = 0
outPar.Value = ourPhar
outPar.Direction = ParameterDirection.Output
cm.Parameters.Add(outPar)
cn.Open()
cm.ExecuteNonQuery()
Dim returnId As Integer
returnId = Convert.ToInt32(cm.Parameters["@OutParamID"].Value)
cn.Close()
Return returnId
End Function

Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Click
Response.Write(InsertDummyTable().ToString())
End Sub
The above code will insert and print the Identity column of the record.
There is there type of parameter direct. Input, InputOutput, Output, ReturnValue.
For getting the output parameter we can use only the output as the parameter direct as in the code below.

outPar.Direction = ParameterDirection.Output;

Conclusion:
Hope this article will help you to understand how to get the output Parameter from SQL Store Procedure using C# and VB.NET. In next Article I will come up with new basic stuffs.

Using of stored procedures and ad hoc queries.

Introduction:
Well in the previous article you were introduced on how to use dataset. Now we will be seeing about how to work with stored procedures and ad Hoc queries.

Stored procedures and ad Hoc queries are the two way of accessing the database and are important while working with DataBase. Usually stored procedure are preferred over the ad hoc queries as it is easy to hack the database information in ad hoc queries where as it is not possible in stored procedure as you use parameter to access the query. Let’s see how to access the data source using stored procedure.

Stored procedure is a parameterized method let’s look at an example to see how to write a stored procedure and how it is used in .NET.

Store Procedure:
Create Procedure SelectAuthorInfo
@au_Id Varchar(50)
AS
Select * from Authors where au_id =@au_Id
GO

This is a simple stored procedure; instead of this Insert, Update or Delete queries can be used in the stored procedure. In the above example the Pubs database is used. This is an in-build data base in MS SQL server.

You will now see how to use the stored procedure in .NET application. As the procedure is the select statement it returns some records.

VB Code:

Dim dsetMydata As New DataSet
Dim connString As String = "server=.; database=pubs; UID=sa; PWD=;"
Dim myConn As New SqlConnection(connString)
Dim strQuery As String = "SelectAuthorInfo"
Dim myCommand As New SqlCommand(strQuery, myConn)
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add(New SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176"
Dim myAdapter As New SqlDataAdapter(myCommand)
myAdapter.Fill(dsetMydata)
DataGrid1.DataSource = dsetMydata.Tables(0)

C# Code:

DataSet dsetMydata = new DataSet();
string connString = "server=.; database=pubs; UID=sa; PWD=;";
string strQuery = "SelectAuthorInfo";
SqlConnection myConn = new SqlConnection();
SqlCommand myCommand = new SqlCommand(strQuery,myConn);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add(new SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176";
SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
myAdapter.Fill(dsetMydata);
DataGrid1.DataSource = dsetMydata.Tables(0);

The above code gets the records for the au_id = “172-32-1176” this is done by using stored procedure. The Stored procedure has a parameter called @au_id, you are passing the value for the parameter from code behind

VB Code:

myCommand.Parameters.Add(New SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176"

C# Code:

myCommand.Parameters.Add(new SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176";


You have to note that the command object is used to pass the parameter for the SQL stored procedure. For passing parameter you have to use SqlParameter member as shown in code. The above code executes the stored procedure and returns a record for that au_id

Figure 1

Now you will see how the ad hoc query is generated. This type of query is nothing but appending the filter value to the main query. This type of ad hoc query is used in search pages or forms. For example if you want to find user information according to the criteria that the user fills in the form. That is he may fill the au_id or au_fName or au_lName etc so you need to query according to this. For which you need to append the query according to user inputs.

strQuery = "select * from Author where au_lname = " & TextBox1.Text & " or state =" & TextBox2.Text & " or au_fname like '%" & TextBox3.Text & "%'"

This type of query is called ad hoc queries. Here you append the query string according to the user input.

Conclusion:
Hope this article will help you to understand how to use stored procedures and ad hoc queries. In next Article we can see something more about use of SQL Store Procedure.

Manipulating data with Dataview

Introduction:
Now i am come up with the DataView object in .NET frame work. The DataView is only helps to view the resultant records in the datasource. In DataView you can do any editing or updating work, if you want to edit or update the records, you have to do it in datatable and repopulate it in the dataview. One of the major advantages of DataView control is Sorting with in the records, you dont want to go to datasource for sorting. Let us get in to the DataView Object.

Manipulating data with Dataview:
DataView is used to show the data in different format and helps to bind datas in the controls. The data available in the DataView is in the form of table and are arranged in a fashion. Let’s see how to declare a DataView.
Vb Code:

Dim dv As New DataView
Or
Dim dv As New DataView(dsetMydata.Tables(0))

C# Code:

DataView dv = new DataView();
OR
DataView dv = new DataView(dsetMydata.Table[0]);

You cannot assign a data source directly to the DataView, but you can assign a DataTable to the DataView. The following code shows how a DataSet can be populated.

VB Code:

Dim dv As New DataView
dv = dsetMydata.Tables(0).DefaultView
DataGrid1.DataSource = dv

C# Code:

DataView dv = new DataView();
dv = edsetMydata.Tables[0].DefaultView;
DataGrid1.DataSource = dv;

The DataView is populated from the DataTable and binds in a DataGrid as shown in Figure1.

Figure 1

The record are arranged in au_id order and is a default view.


You can sort the records in ascending or descending order.
VB Code:
dv.Sort = "au_fname desc"

C# Code:
dv.Sort = "au_fname desc”;

Figure 2

The Records are sorted by au_fname in deceding order as shown in Figure 2

Conclusion:
Hope this article will help you to understand the DataView object in .NET. Now Buddies, give your comment about this article.

Manipulating data with Dataset Part II

Introduction:
In the previous part you came to know about generating and Creating the DataSet. Now come on BUDDIES let us take dive into dataset in depth like Editing and Updating in DataSet and EATING DATASET :), and how it will affect the data source.

Editing and Updating in a DataSet
In DataSet you can get the records from the data source and edit the records with in the DataSet and update back to the data source. Let’s see an example for editing and updating the data.
Try
Dim dsetMydata As New DataSet
Dim connString As String = "server=.; database=pubs; UID=sa; PWD=;"
Dim myConn As New SqlConnection(connString)
Dim strQuery As String = "select * from authors"
Dim myAdapter As New SqlDataAdapter(strQuery, myConn)
myAdapter.Fill(dsetMydata)
Dim dr As DataRow = dsetMydata.Tables(0).Rows(0)
dr("au_lName") = "Bill"
dr("au_fName") = "Gates"
DataGrid1.DataSource = dsetMydata.Tables(0)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
The above code returns all the rows from the data source and stores it in DataSet

Dim dr As DataRow = dsetMydata.Tables(0).Rows(0)
dr("au_lName") = "Bill"
dr("au_fName") = "Gates"

This code helps to edit the DataTable. Before editing you should specify the row number that has to be edited. In the above example you have specified Rows (0) which is the first record or row that has to be edited.Now this change is updated in the DataSet, if you don’t want to update this information in the DataSet, you can add the following code
dsetMydata.RejectChanges()

This rejects the changes that happened in the DataSet. As the DataSet holds both original and edited version of records in memory, a call to RejectChages() gets the original version of records.

Let us now come to the most advance part of this section that is updating the changed records using DataAdapter Update method..

Once you change the records in the dataset, it has to be updated into the data source. Instead of doing row by row updating, you have a Update method in DataAdapter object which helps in bulk update in to the data source. To achieve this you need to use parameter method to pass the values to the data source. The above code helps to do it. The step by step process briefed below helps to update DataSet in to data source.

• Creates Command object with command text and connection to data source as parameter as shown below.

Dim mycom As New SqlCommand("update authors set au_lname=@lname, au_fname=@fname where au_id=@id", myConn)

• Using parameter member in the command object you can create a new parameter for that command object. The below code shows how to implement this. In the Parameter you need to specify the parameter variable, DataType, Size and the column to be affected in the data source.

mycom.Parameters.Add(New SqlParameter("@lname", SqlDbType.VarChar, 50, "au_lname"))

• Assign the command object to the DataAdapter Update command type as below

myAdapter.UpdateCommand = mycom

• Using DataAdapter.Upadate method you can update the modified data in to the data source. And also can check if the data has been properly updated, if not then you can roll back the DataSet by dsetMydata.RejectChanges()


If (dsetMydata.Tables(0).HasErrors) Then
dsetMydata.RejectChanges()
Else
dsetMydata.AcceptChanges()

The below code shows how to update the records to the dataset.

Dim dsetMydata As New DataSet
Dim connString As String = "server=.; database=pubs; UID=sa; PWD=;"
Dim myConn As New SqlConnection(connString)
Dim strQuery As String = "select * from authors"
Dim myAdapter As New SqlDataAdapter(strQuery, myConn)
myAdapter.Fill(dsetMydata)
Dim dr As DataRow = dsetMydata.Tables(0).Rows(0)
dr("au_lName") = "Bill"
dr("au_fName") = "Gates"
Dim mycom As New SqlCommand("update authors set au_lname=@lname, au_fname=@fname where au_id=@id", myConn)
mycom.Parameters.Add(New SqlParameter("@lname", SqlDbType.VarChar, 50, "au_lname"))
mycom.Parameters.Add(New SqlParameter("@fname", SqlDbType.VarChar, 50, "au_fname"))
mycom.Parameters.Add(New SqlParameter("@id", SqlDbType.VarChar, 50, "au_id"))
myAdapter.UpdateCommand = mycom
myAdapter.Update(dsetMydata.Tables(0))
If (dsetMydata.Tables(0).HasErrors) Then
dsetMydata.RejectChanges()
Else
dsetMydata.AcceptChanges()
End If
Conclusion
So, hope my article have helped you better understand dataset, soon will be coming up with something good for you. Be sure to place comments on the article

Manipulating data with Dataset Part I

Introduction:
Working with DataSet will be quiet interesting. In this article we can look at generating the dataset using DataAdapter, setting relationship and constrains to the DataSet and populate DataSet in windows form controls, modifying the DataSet, Updating DataSet to the datasource.

Generating DataSet:
Let us take an example to generate a DataSet using DataAdapter. As we said DataSet is a disconnected Architecture for data access so it don’t required a connection to the database is required, just you can pass the connecting string and no need to open a connection. Before we start working with DataSet you should know how to create a new instant for DataSet.

Dim dsetMydata As New DataSet

Or

Dim dsetMydata As New DataSet("MyDataset")

Here we can declare the DataSet name as the optional parameter to the DataSet
Now let see a simple example to generate a DataSet from a data source like MS SQL server.

Try
Dim connString As String = "server=local; database=xx; UID=xx; PWD=xx;"
Dim myConn As New SqlConnection(connString)
Dim strQuery As String = "select Firstname, LastName from EmployeeInfo"
Dim myAdapter As New SqlDataAdapter(strQuery, myConn)
myAdapter.Fill(dsetMydata)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

The above code will help to create the DataSet and store the data from the selected data source.
Here you have used the DataAdapter object. Command text and connection is passed as the parameter for the DataAdapter. Using Fill method in the DataAdapter you can store the retrieved information from the selected data source. As we discussed before DataSet is the collection of DataTable so we can append one or more than one DataTable from the data source.
Let see an example to retrieve two DataTable from the data source and bind it in a DataGrid

Try
Dim dsetMydata As New DataSet
Dim connString As String = "server=.; database=pubs; UID=sa; PWD=;"
Dim myConn As New SqlConnection(connString)
Dim strQuery As String = "select * from authors;select * from employee"
Dim myAdapter As New SqlDataAdapter(strQuery, myConn)
myAdapter.Fill(dsetMydata)
DataGrid1.DataSource = dsetMydata.Tables(0)
DataGrid2.DataSource = dsetMydata.Tables(1)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

The above code will return two record set and it is binds in the DataSet as two tables, that is table1 and table2. You can get the two table by dsetMydata.Tables(0) and dsetMydata.Tables(1) this two table is binded in the DataGrid as shown in Figure

Figure 1

Creating DataSet Manually:
Now let us create a DataSet without using DataAdapter. For creating these type of DataSet you need to do following steps
· Create Number of DataTables that the DataSet going to have.
· Create the column for each DataTable’s
· If you want to set the relationship between DataTable’s, assign it using DataRelation class
· Then add the appropriate data to each DataTable’s

Before start you have to know how to declare a DataTable and DataColumn. This is as similar to DataSet declaration. The below code show is use to declare a DataTable

Dim dtMyTable1 As New DataTable("Employee")
Dim dtMyTable2 As New DataTable("Salary")

While declaring the DataColumn, you can specify the column Name, Datatype of the column and other specifications like primary key, auto increment column and more. The below code will help you to understand more about DataColumn.

Dim dcColumnEmpId As New DataColumn("EmpId",_ System.Type.GetType("System.Int32"))
dcColumnEmpId.AutoIncrement = True
dcColumnEmpId.AutoIncrementSeed = 1
dcColumnEmpId.AutoIncrementStep = 1
Dim dcColumnName As New DataColumn("Name",System.Type.GetType("System.String"))

The above code will create two DataColumn and add some property to the column. We can set the properties if we required
After creating the column you need to add the column to the DataTable. . And also can specify a column as the primary key for the DataTable
The below code will do it.

dtMyTable1.Columns.Add(dcColumnEmpId)
dtMyTable1.PrimaryKey = New DataColumn() {dcColumnEmpId}

Now this table can be added to the DataSet as shown.

dsetMyDataSet.Tables.Add(dtMyTable1)

Let us take a complete look of the code.

Dim dsetMyDataSet As New DataSet
Dim dtMyTable1 As New DataTable("Employee")
Dim dtMyTable2 As New DataTable("Salary")
Dim dcColumnEmpId As New DataColumn("EmpId", System.Type.GetType("System.Int32"))
dcColumnEmpId.AutoIncrement = True
dcColumnEmpId.AutoIncrementSeed = 1
dcColumnEmpId.AutoIncrementStep = 1
Dim dcColumnName As New DataColumn("Name", System.Type.GetType("System.String"))
dtMyTable1.Columns.Add(dcColumnEmpId)
dtMyTable1.PrimaryKey = New DataColumn() {dcColumnEmpId}
dtMyTable1.Columns.Add(dcColumnName)
Dim dcColumnSalEmpId As New DataColumn("EmpId", System.Type.GetType("System.Int32"))
Dim dcColumnSalary As New DataColumn("EmpSalary", System.Type.GetType("System.String"))
dtMyTable2.Columns.Add(dcColumnSalEmpId)
dtMyTable2.Columns.Add(dcColumnSalary)
dsetMyDataSet.Tables.Add(dtMyTable1)
dsetMyDataSet.Tables.Add(dtMyTable2)dsetMyDataSet.Relations.Add("EmployeeMaping", dsetMyDataSet.Tables("Employee").Columns("EmpId"), dsetMyDataSet.Tables("Salary").Columns("EmpId"))

In this example we created two tables Employee, Salary. Employee has EmpId and Name column and Salary has EmpId and EmpSalary column. Each column is added to appropriate DataTables and the tables is attached to the DataSet. And you can set the relationship to the table as in code below

dsetMyDataSet.Relations.Add("EmployeeMaping",_ dsetMyDataSet.Tables("Employee").Columns("EmpId"),_ dsetMyDataSet.Tables("Salary").Columns("EmpId"))

You can able to create a DataSet with any number of DataTables. Now we can see how to add Datarow to a created DataTable. For adding a row in the DataTable, first you need to create a new DataRow for that tables and then you need to add a value for it. The below sample code will helps to add the rows to the DataTable in the DataSet in a loop.

For i As Integer = 1 To 5
Dim dr As DataRow = dsetMyDataSet.Tables("Employee").NewRow()
Dim dr1 As DataRow = dsetMyDataSet.Tables("Salary").NewRow()
dr("Name") = "MyName" & i.ToString()
dr1("EmpId") = i.ToString()
dr1("EmpSalary") = (5000 * i).ToString()
dsetMyDataSet.Tables("Employee").Rows.Add(dr)
dsetMyDataSet.Tables("Salary").Rows.Add(dr1)
Next

The above code will insert records in the DataTable Employee and Salary. Since you assigned the DataRelation to both the table, you can able to retrieve the information from the tables. The below code will help to retrieve the information from the table.
Dim MyChildRow() As DataRow
Dim MyParentrow As DataRow
MyChildRow = dsetMyDataSet.Tables("Employee").Rows(0).GetChildRows("EmployeeMaping")
MyParentrow = dsetMyDataSet.Tables("Salary").Rows(0).GetParentRow("EmployeeMaping")

MyChildRow will get all the columns in the Salary Table for the EmpId of Employee table for the first row.
MyParentrow will get all the columns in the Employee table for the EmpId of Salary table for the first row. Now you can understood how to set the relationship between tables and how to retrieve
the data using the relationships.
Now we can see how to set constrains to a DataColumn. You can set some constraints like Primary key, Unique key and Foreign key to the DataColumn. You had seen how to set a primary key to a column. The below code will help to set Unique Key constraints to the DataColumn. The Unique Key can be declaring in two ways. Let see an example to implement the Unique Key in two ways.

dcColumnEmpId.Unique = True

OR

Dim myUniqueKey As New UniqueConstraint(dcColumnEmpId)
dtMyTable1.Constraints.Add(myUniqueKey)

Now we can see how to set Foreign Key Constraints to your DataColumns. This is similar to setting a Primary key.

Dim myForeignKey As New ForeignKeyConstraint("EmployeeMaping", dtMyTable1.Columns("EmpId"), dtMyTable1.Columns("EmpId"))_
dtMyTable2.Constraints.Add(myForeignKey)

Summary:
In Part I you have seen generating and Creating the DataSet. In Part II you will see how to Editing and Updating in DataSet and how it will affects the datasource.

Accessing data using DataReader

Introduction:
In this article you can see how to access the data using DataReader from the data source.

About DataReader:
DataReader is a readonly, forward only and connected recordset from the database. In DataReader, database connection is opened until the object is closed unlike DataSet. Using DataReader we can able to access one row at a time so there it is not required storing it in memory. In one DataReader we can get more than one result set and access it one by one. It will be faster for using simple purpose like populating the form. We should not use it for manipulating the records since it will always connect to database.
Let’s see few examples to use DataReader from accessing the data.


The below exaple will get the employee Firstname and Last name from the table EmployeeInfo form MS SQL server database. Using While loop the records are Fetched row by row and displayed in message box. After the last record While loop will end and then we need to close both the DataReader object and also connection object as show in the code.

CODE:

Dim connString As String = "server=local; database=xx; UID=xx; PWD=xx;"
Dim myConn As New SqlConnection(connString)
Dim strQuery As String = "select Firstname, LastName from EmployeeInfo"
Dim myCommand As New SqlCommand(strQuery, myConn)
myConn.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader()
While (myReader.Read())
MessageBox.Show(myReader.GetString(0))
MessageBox.Show(myReader.GetString(1))
End While
myReader.Close()
myConn.Close()

In the above example we used myReader.GetString(0) this will return the first column value of the record set. The above code will result only one record set. Now let us consider an example the resultant set has more than one record set.

Code:

Dim connString As String = "server=local; database=xx; UID=xx; PWD=xx;"
Dim myConn As New SqlConnection(connString)
Dim strQuery As String = "select Firstname, LastName from EmployeeInfo;Select * from EmployeeMaster"
Dim myCommand As New SqlCommand(strQuery, myConn)
myConn.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader()
Dim bFlag As Boolean = True
Do Until Not bFlag
While (myReader.Read())
MessageBox.Show(vbTab & myReader.GetName(0) & vbTab & myReader.GetString(1))
End While
bFlag = myReader.NextResult()
Loop

The above code will return two record set. myReader.NextResult() will helps to move from one record set to another record set. From the above two example you will know how to use DataReader in you application.

Summray:
From this article you can able to learn how to uses the DataReader for accessing the data.