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