The ADO.NET Mapping Mechanism

Naveenmanam
Posted by Naveenmanam under ASP.NET category on | Views : 2859
The ADO.NET Mapping Mechanism


Now let's take a look at the new mapping mechanisms provided by ADO.NET. We will introduce the objects by using mappings when filling a DataSet. Then we will move on to using mappings when making updates to the data source.
There is a big difference between the SQL method and the ADO.NET method. The mapping objects allow developers to manage DataSet data and schemas that have been created using XML documents and XML schemas. Using Mapping when Retrieving Data When we fill a DataSet, the DataAdapter looks at its own TableMappings property to see if the developer has defined mapping rules. By default, the TableMappings property is empty, so the same column names used in the database are used in the dataset table.

Let's take a look at how we would use the ADO.NET mapping mechanism to rename very terse column names
in a DataSet to more meaningful alternatives. We will use the same Microsoft Access database that we used in the previous example. To use ADO.NET mappings, we create a DataTableMapping object. This object enables us to map between two names for the same table, and also contains the ColumnMappings property - a collection of DataColumnMapping objects that map between names of the column in the table. Once we have created this object, and added all of the required column mappings, we add it to the DataAdapter object's TableMappings property.

Let's look at an example that puts this into practice. The following is just a class outline. To keep the new code clear, we will use a separate method to handle the mapping - DoDataMappings. Notice that w e introduce the System.Data.Common namespace, which we
need to use the mapping classes:


using System;
using System.Data;
using System.Data.OleDb;
using System.Data.Common;
namespace Example2
{

class Class1
{
static void Main(string[] args)
{
implemented later
}
static void DoDataMappings(DataAdapter da)
{
implemented later
}
}
}
static void Main(string[] args)
{
try
{

OleDbConnection dbConn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password=;"+ "User ID=Admin;Data Source=" +"F:\\PRO.ADO.NET\\Mapping\\Example2\\db.mdb");

OleDbDataAdapter daUsers = new OleDbDataAdapter("SELECT " +"ID,fn,ln,cty,st FROM tabUsers", dbConn);
DataSet dsUsers = new DataSet("User");

DoDataMappings(daUsers);

daUsers.Fill(dsUsers);

foreach(DataRow r in dsUsers.Tables[0].Rows)
Console.WriteLine("ID: {0}, FirstName: {1}, LastName: {2}," +" City: {3}, State: {4}",r["UserID"],r["FirstName"],r["LastName"],r["City"],r["State"]);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}



The DataColumnMapping object contains the relation between the column within the database and the
column inside the DataSet. We construct it by providing two strings. The first specifies the column name in
the data source; the second defines the column name that will appear in the DataSet.

Inserting Records Using Mapped Names

Until now, we have focused our attention on retrieving records. But mapping makes even the database's other
operations, such as inserting a new record or updating an existing one, easier to write and manage. After mapping
column names, we can use them throughout our code to accomplish every kind of database access. Let's see an
example of adding a new record inside the Microsoft Access DB.MDB database:


using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
namespace Example7
{

class Class1
{
static void DoDataMappings(DataAdapter da)
{
same implementation as above
}
static void Main(string[] args)
{
DataSet dsUsers = new DataSet("Users");
try
{

OleDbConnection dbConn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password=;"+ "User ID=Admin;Data Source=" +"F:\\PRO.ADO.NET\\Mapping\\Example2\\db.mdb");

OleDbDataAdapter daUsers = new OleDbDataAdapter("SELECT" +"ID,fn,ln,cty,st FROM tabUsers", dbConn);

DoDataMappings(daUsers);

daUsers.Fill(dsUsers);

OleDbCommandBuilder cb = new OleDbCommandBuilder(daUsers);

DataRow r = dsUsers.Tables[0].NewRow();
r["FirstName"] = "Eddie";
r["LastName"] = "Robinson";
r["City"] = "Houston";
r["State"] = "Texas";
dsUsers.Tables[0].Rows.Add(r);

daUsers.Update(dsUsers.GetChanges());

dsUsers.AcceptChanges();

Console.WriteLine("A new record has been added to the " +"database.");
}
catch (Exception ex)
{

Console.WriteLine(ex.Message);
s
dsUsers.RejectChanges();
}
}
}
}



In the highlighted snippet of code above a new row has been created and filled using the mapped column
names. we update a column using the mapping mechanism: in next artical update mapping..

Comments or Responses

Login to post response