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.