asp.net usage

We have seen what a delimited text file looks like; now let us have a look at other types of usage of a delimited file:

1. Data import into a database
2. Used as a database

Let us go into detail on how these delimited files are used in both of the cases above:
Data Import into a Database

In a DBMS/RDBMS we create databases and tables. In a huge database with millions of records, in order to transfer data from one database to another or to transport a database from one location to another, data is normally exported into a text file. When we try to export data to a text file, the system will ask for a delimiter to be used. When we specify the delimiter, the data stored in the fields will be appended with the delimiter. When importing the data back to the database, the system will ask for the delimiter, and based on the specified delimiter, the data will be imported back into the database.
Used as a Database

In certain environments, where there is no database support, delimited text files are used as a replacement. With limited functionality, data is read directly from the delimited text file into arrays and then manipulated. Usually these types of delimited text files are used in web app where hiring database support along with hosting can be a costly affair. In order to emulate a database, these delimited text files are used.

We have seen what a delimited text file is, as well as the important usage and applications these delimited text files can be used for.

We will now move on to see how to read these delimited text files using ASP.Net and VB.Net.

Delimiters are sometimes referred to as splitters.

ASP.NET1

New to .NET? Wondering how to split contents using a delimiter?

The following article will detail how to read a delimited text file using ASP.Net or VB.Net, which will explain various aspects of a delimited file, code explanation and source code.

We must be aware of importing data into a database. Data which is used for importing is normally in the form of a text file. These files are also called comma separated value files, which will normally have a .csv extension.

In either case, whether it’s a text file or a .csv file, all the values are delimited using a character. This particular character, which is used for separating the values, is called the delimiter. This delimiter may be a word, a letter, a tab, or a space.

Let us have a look at an example of how these delimited files may be laid-out:

Example: Comma-Delimited File:

Value1, Value2, Value3, Value4, Value5

In the above example, we can see a comma between the words Value1 Value2 until Value 5.

Example: Tab-Delimited File

Value1 Value2 Value3 Value4 Value5

In the above example we are able to see a tab between all the values.

The Execution of a Command

The Execution of a Command:
r creating the command and passing it to the database through the connection, you can then execute it. To support this, the OleDbCommand class is equipped with a method named ExecuteNonQuery(). Its syntax is:

Public Overridable Function ExecuteNonQuery()
As Integer Implements
 IDbCommand.ExecuteNonQuery

This method is normally called to execute a command that carries a SQL statement.

The Command Timed Out:

The OleDbCommand.CommandTimeOut property allows you to specify the time to wait before trying to execute a command. The default value of this property is 30 (seconds).

The Command Text

The Command Text:

two main alternatives to specify this string.

Instead of declaring an OleDbCommand variable using the default constructor, you can use the second constructor whose syntax is:

Public Sub New(ByVal cmdText As String)

This constructor takes as argument a string that is
the object of the command. If you had declared the OleDbCommand
variable using the default constructor, to specify the command, you can
assign a string to the OleDbCommand.CommandText property. We will
see various examples of creating such a command text and using it.

The Connection to the Command
After creating a command, you must pass it to the database but the command has no way of find that database.
Remember that the connection knows where the database is, through its data source factor.

Public Sub New(ByVal cmdText As String, ByVal connection As OleDbConnection)

If you had declared an OleDbCommand variable
using its default constructor, to specify the connection, you can assign
it to the OleDbCommand.Connection property. In both cases, you
must provide an appropriate OleDbConnection value using the
techniques we reviewed earlier

Commanding a Database

you can perform actions or operations on it. To make this possible and probably easier, the System.Data.OleDb namespace provides the OleDbCommand class.class is used to create commands or actions to be performed on the database. To create a command, the OleDbCommand class is equipped with four constructors. The first constructor is the default. It allows you to declare an OleDbCommand variable without specifying its assignment.


The Type of Command:

A command is usually created as a string but a command can also be created as a function, also called a stored procedure.
specify the type of command you want to create, the OleDbCommand class is equipped with a property named CommandType. This property is of type OleDbCommand, which is a structure that holds values that specify the type of command to be carried.
The CommandType enumerator has three members:
1.StoredProcedure
2.TableDirect
3.Text.

For an OleDbCommand variable, the default isText.

Operations on database connection

Opening a Connection:
Before performing any necessary operation, you must open the connection. To support this, the OleDbConnection class is equipped with the Open() method whose syntax is:

Public Overridable Sub Open() Implements IDbConnection.Open

(or)

Dim Conn As System.Data.OleDb.OleDbConnection
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles MyBase.Load
Conn = New System.Data.OleDb.OleDbConnection
Conn.ConnectionString = "Provider=Microsoft.JET.
OLEDB.4.0;" &"Data Source=C:\Programs\Exercise.mdb;"
   Conn.Open()
End Sub 

Closing a Connection:

you should close it to release its resources and make them available to other applications .

To close a connection, the OleDbConnection class provides the Close() method. Its syntax is:

Public Overridable Sub Close() Implements IDbConnection.Close


Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Dim oleConn As System.Data.OleDb.OleDbConnection
      oleConn = New System.Data.OleDb.OleDbConnection

      oleConn.ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;" & _
                                 "Data Source=C:\Programs\Exercise.mdb;"
      oleConn.Open()

      oleConn.Close()
End Sub

data source1

After specifying the provider, an important factor is the name of the database you want to connect to.After specifying the provider, an important factor is the name of the database you want to connect to.

Dim oleConn As System.Data.OleDb.OleDbConnection
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

oleConn=NewSystem.Data.OleDb.OleDbConnection
("Provider=Microsoft.JET.OLEDB.4.0;"
& "Data Source=C:\Programs\Exercise.mdb;")

End Sub

(or)

If you had declared an OleDbConnection variable using the other constructor, to specify the data source, you can include its section in the connectionString argument of the constructor. Here is an example:

Dim oleConn As System.Data.OleDb.OleDbConnection

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
 Handles MyBase.Load

oleConn = New System.Data.OleDb.OleDbConnection

oleConn.ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;" &
                    "Data Source=C:\Programs\Exercise.mdb;"
End Sub

If the connection has already been established and the data source was specified, to know the name of the data source, you can get the value of the OleDbConnection.DataSource property.

The Database Provider

The first parameter or one of the parameters of a connection is the database provider. To specify the provider, you create its section with:

Provider=ProviderValue;

There are various providers available. The one used for Microsoft Access is:

Microsoft.Jet.OLEDB.4.0

If you had declared an OleDbConnection variable using the default constructor, to specify the provider, you can include a "Provider=Microsoft.Jet.OLEDB.4.0;" string to your OleDbConnection.ConnectionString property.

Here is an example:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim oleConn As System.Data.OleDb.OleDbConnection

oleConn = New System.Data.OleDb.OleDbConnection oleConn.ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;"
End Sub

If you are using an OleDbConnection variable declared using the second constructor, you can include the provider in the connectionString argument of the constructor. Here is an example:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim oleConn As System.Data.OleDb.OleDbConnection

oleConn=NewSystem.Data.OleDb.OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;")
Sub

If the connection was already established and you want to find out what provider it is using, you can get the value of the OleDbConnection.Provider property. Here is an example:

Dim oleConn As System.Data.OleDb.OleDbConnection Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

oleConn=NewSystem.Data.OleDb.OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;") End Sub Private Sub btnEnquire_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnquire.Click

Dim strProvider As String = oleConn.Provider MsgBox(strProvider)
End Sub

The Connection String

The connection string is used to specify how the connection would be performed. It is a string made of different sections.Each section is created using the formula Argument=Value. The sections are separated by semi-colons. This would produce:"Argument1=Value;Argument=Value2;Argument_n=Value_n;"

Everything in this string is case-insensitive. If you had declared an OleDbConnection variable using the default constructor, to create the connection string, you can declare a String variable, "fill it up" with the necessary information, and assign it to the OleDbConnection.ConnectionString property.

This would be done as follows:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim oleConn As System.Data.OleDb.OleDbConnection
oleConn = New System.Data.OleDb.OleDbConnection

oleConn.ConnectionString = " "
End Sub

Application Connection to the Database

The classes you can use to connect your application to a Microsoft Access database are stored in the System.Data.OleDb namespace. To use these classes, besides including this namespace in the file where needed, you should also import the System.Data.OleDb.dll library in your project.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim oleConn As System.Data.OleDb.OleDbConnection
oleConn = New System.Data.OleDb.OleDbConnection
End Sub

If you are planning to access the connection from more than one event or method, you should declare it at the class level. Here is an example:

Dim oleConn As System.Data.OleDb.OleDbConnection

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
oleConn = New System.Data.OleDb.OleDbConnection
End Sub

As opposed to using the default constructor, to establish a connection, you can use the other constructor of the class. Its syntax is:

Public Sub New(ByVal connectionString As String)

The argument to this constructor is a string that specifies how the connection would be established.

oledb creation

Database Creation

If you are planning to use a Microsoft Access database in a .NET Framework application, probably the first thing you do is to create the database. You can do this directly in Microsoft Access .To make it possible to connect an application to a database, the .NET Framework provides a series of classes through ADO.NET and each class is made for a specific purpose. The classes of ADO.NET are stored in a namespace named System.Data. Therefore, to make sure your application has access to these classes, you can first include this namespace.

Introduction to OLE DB

Introduction


Until recently, to use a Microsoft Access database in an external application
, programmers based their code on ADO or previous libraries related to OLE DB. With the release of the .NET Framework, you can now use such a database
in any language that is part of this huge library. To use and maintain a Microsoft Access database, you can use the .NET Framework data provider for OLE DB.