Updating Records Using Mapped Names

Updating Records Using Mapped Names

TableMappings and ColumnMappings help you bridge this translation gap of different column/table names in fetching data. The same concepts can be applied to other database operations, such as inserting a new record or updating an existing

Example:


Example of Adding a Record Using C#

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data.Common;
using System.Data;

namespace udatingrecord
{
class Program
{
static void Main(string[] args)
{
DataSet ds = new DataSet("Users");
try
{
OleDbConnection dbConn =
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Password=;User ID=Admin;Data Source=db.mdb");
// Create a data adapter to retrieve records from db
OleDbDataAdapter dataadapt =
new OleDbDataAdapter("SELECT ID,fn,ln,cty,st" +
" FROM tabUsers", dbConn);
// Define each column to map
DataColumnMapping DmUserID =
new DataColumnMapping("ID", "UserID");
DataColumnMapping DmFirstName =
new DataColumnMapping("fn", "FirstName");
DataColumnMapping DmLastName =
new DataColumnMapping("ln", "LastName");
DataColumnMapping DmCity =
new DataColumnMapping("cty", "City");
DataColumnMapping DmState =
new DataColumnMapping("st", "State");
// Define the table containing the mapped columns

new DataTableMapping("Table", "User");
dtmUsers.ColumnMappings.Add(DmUserID);
dtmUsers.ColumnMappings.Add(DmFirstName);
dtmUsers.ColumnMappings.Add(DmLastName);
dtmUsers.ColumnMappings.Add(DmCity);
dtmUsers.ColumnMappings.Add(DmState);
// Activate the mapping mechanism
daUsers.TableMappings.Add(dtmUsers);
// Fill the dataset
daUsers.Fill(ds);
DataColumn[] dckey = { ds.Tables["User"].Columns["UserID"] };
ds.Tables["User"].PrimaryKey = dckey;
// Declare a command builder to create SQL instructions
// to create and update records.
OleDbCommandBuilder cb = new OleDbCommandBuilder(daUsers);
// Update an existing record in the DataSet
DataRow r = ds.Tables["User"].Rows.Find(8);
if (r != null)
{
r["FirstName"] = "Ashish";
r["LastName"] = "Gupta";
r["City"] = "URAI";
r["State"] = "UP";
// Update the record in the database
daUsers.Update(ds.GetChanges());
// Align in-memory data with the data source ones
ds.AcceptChanges();
// Print success message
Console.WriteLine("The record has been updated " +
"successfully.");
}
else
{
Console.WriteLine("No record found...");
}
}
catch (System.Exception ex)
{

ds.RejectChanges();
// An error occurred. Show the error message
Console.WriteLine(ex.Message);
}
}
}
}