Adding Records Using Mapped Names

Adding 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

Examople:


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 addrecord
{
class Program
{
static void Main(string[] args)

{
DataSet ds = new DataSet("Users");
try
{
// Define a connection object
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 dataadp =
new OleDbDataAdapter("SELECT ID,Ename,En0,cty,st" +
" FROM Emp", dbConn);
// Define each column to map
DataColumnMapping dcmUserID =
new DataColumnMapping("ID", "UserID");
DataColumnMapping dcmEmpName =
new DataColumnMapping("Ename", "Name");
DataColumnMapping dcmEmpNumber =
new DataColumnMapping("En0", "Eno");
DataColumnMapping dcmCity =
new DataColumnMapping("cty", "City");
DataColumnMapping dcmState =
new DataColumnMapping("st", "State");
// Define the table containing the mapped columns
DataTableMapping dtmUsers = new DataTableMapping("Table", "User");
dtmUsers.ColumnMappings.Add(dcmUserID);
dtmUsers.ColumnMappings.Add(dcmEmpName);
dtmUsers.ColumnMappings.Add(dcmEmpNumber);
dtmUsers.ColumnMappings.Add(dcmCity);
dtmUsers.ColumnMappings.Add(dcmState);
// Activate the mapping mechanism
dataadp.TableMappings.Add(dtmUsers);
// Fill the dataset
dataadp.Fill(ds);
// Declare a command builder to create SQL instructions
// to create and update records.
OleDbCommandBuilder cb = new OleDbCommandBuilder(dataadp);
// Insert a new record in the DataSet
DataRow r = ds.Tables["User"].NewRow();
r["Eno"] = "1050";
r["City"] = "Delhi";
r["State"] = "NewDelhi";
ds.Tables["User"].Rows.Add(r);
// Insert the record in the database
dataadp.Update(ds.GetChanges());
// Align in-memory data with the data source ones
ds.AcceptChanges();
// Print successfully message
Console.WriteLine("A new record has been"
+ " added to the database.");
}
catch (Exception ex)
{
// Reject DataSet changes
ds.RejectChanges();
// An error occurred. Show the error message
Console.WriteLine(ex.Message);
}
}
}
}