C# Code Example
//To test this
sample you will need to download and Install ODBC.NET Data Provider
//http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/MSDN-FILES/027/001/668/msdncompositedoc.xml&frame=true
//created by
using System;
using System.IO;
using System.Data;
using System.Xml;
using System.Collections;
using System.Data.SqlClient;
using Microsoft.Data.Odbc;
using System.Data.Common;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Xml.Serialization;
namespace school{
[Serializable]
[XmlRoot ("PAYMENTS")]
public
class PAYMENTS{
IDbConnection dbconn;
IDbDataAdapter
da;
DataSet
ds;
/// <summary>
/// Access properties for PaymentID field of Payments
table
/// </summary>
private Double
i_PaymentID;
[XmlElement(ElementName
= "PaymentID", DataType="int", Namespace
= "school")]
public Double
PaymentID{
get{ return
i_PaymentID;}
set{ i_PaymentID = value;}
}
/// <summary>
/// Access properties for ContactID field of Payments
table
/// </summary>
private Double
i_ContactID;
[XmlElement(ElementName
= "ContactID", DataType="int", Namespace
= "school")]
public Double
ContactID{
get{ return
i_ContactID;}
set{ i_ContactID = value;}
}
/// <summary>
/// Access properties for PaymentDate field of Payments
table
/// </summary>
private object
d_PaymentDate;
[XmlElement(ElementName
= "PaymentDate", DataType="date", Namespace
= "school")]
public object
PaymentDate{
get{ return
d_PaymentDate;}
set{ d_PaymentDate = value;}
}
/// <summary>
/// Access properties for PaymentlTime field of Payments
table
/// </summary>
private object
d_PaymentlTime;
[XmlElement(ElementName
= "PaymentlTime", DataType="date", Namespace
= "school")]
public object
PaymentlTime{
get{ return
d_PaymentlTime;}
set{ d_PaymentlTime = value;}
}
/// <summary>
/// Access properties for PaymentForMonth field of
Payments table
/// </summary>
private object
d_PaymentForMonth;
[XmlElement(ElementName
= "PaymentForMonth", DataType="date", Namespace
= "school")]
public object
PaymentForMonth{
get{ return
d_PaymentForMonth;}
set{ d_PaymentForMonth = value;}
}
/// <summary>
/// Access properties for Amount field of Payments table
/// </summary>
private String
s_Amount;
[XmlElement(ElementName
= "Amount", DataType="string", Namespace
= "school")]
public String
Amount{
get{ return
s_Amount;}
set{ s_Amount = value;}
}
/// <summary>
/// Access properties for AmountCurrency field of
Payments table
/// </summary>
private String
s_AmountCurrency;
[XmlElement(ElementName
= "AmountCurrency", DataType="string", Namespace
= "school")]
public String
AmountCurrency{
get{ return
s_AmountCurrency;}
set{ s_AmountCurrency = value;}
}
/// <summary>
/// Access properties for Notes field of Payments table
/// </summary>
private String
s_Notes;
[XmlElement(ElementName
= "Notes", DataType="string", Namespace
= "school")]
public String
Notes{
get{ return
s_Notes;}
set{ s_Notes = value;}
}
#region
Constructors
/// <summary>
/// Default constructor creates ODBCConnection and
initialize object.
/// </summary>
public PAYMENTS()
{
getODBCConnection();
da
= new OdbcDataAdapter();
init();
OdbcCommandBuilder
cb = new
OdbcCommandBuilder((OdbcDataAdapter)da);
cb.RefreshSchema();
}
/// <summary>
/// Constructor uses external SqlConnection to initialize
object.
/// </summary>
/// <param name='extDbConn'>SqlServerClient
Connection object</param>
public PAYMENTS(SqlConnection extDbConn)
{
dbconn=extDbConn;
da
= new SqlDataAdapter();
init();
SqlCommandBuilder
cb = new
SqlCommandBuilder((SqlDataAdapter)da);
cb.RefreshSchema();
}
/// <summary>
/// Constructor uses external OleDbConnection to
initialize object.
/// </summary>
/// <param name='extDbConn'>OleDb Connection
object</param>
public PAYMENTS(OleDbConnection extDbConn)
{
dbconn=extDbConn;
da
= new OleDbDataAdapter();
init();
OleDbCommandBuilder
cb = new
OleDbCommandBuilder((OleDbDataAdapter)da);
cb.RefreshSchema();
}
/// <summary>
/// Constructor uses external ODBCConnection to
initialize object.
/// </summary>
/// <param name='extDbConn'>ODBC Connection
object</param>
public PAYMENTS(OdbcConnection extDbConn)
{
dbconn=extDbConn;
da
= new OdbcDataAdapter();
init();
OdbcCommandBuilder
cb = new
OdbcCommandBuilder((OdbcDataAdapter)da);
cb.RefreshSchema();
}
#endregion
/// <summary>
/// Loads object properties and DataSet by primary key.
/// </summary>
/// <param name='Double i_PaymentID_PK'>Primary
Key</param>
public void
load(Double i_PaymentID_PK)
{
try
{
((IDataParameter)(da.SelectCommand.Parameters[0])).Value=
i_PaymentID_PK;
da.Fill(ds);
ds2object();
}
catch( Exception e )
{
handleException(e);
return;
}
}
/// <summary>
/// Updates the DataSet from the object
/// properties and submit changes to the database.
/// </summary>
public void
update()
{
try{
object2ds();
da.Update(ds);
}
catch( Exception e ){
handleException(e);
return;
}
}
/// <summary>
/// Creates new record in the DataSet, populate it from
object
/// properties and submit changes to the database.
/// </summary>
public void
insert()
{
try{
ds.Tables[0].Clear();
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
object2ds();
da.Update(ds);
}
catch( Exception e ){
handleException(e);
return;
}
}
/// <summary>
/// Populate Object properties from the DataSet.
/// </summary>
private void
ds2object(){
DataRow
row = ds.Tables[0].Rows[0];
i_PaymentID
= (row.IsNull("PaymentID") ? 0
:Double.Parse(row["PaymentID"].ToString()));
i_ContactID
= (row.IsNull("ContactID") ? 0
:Double.Parse(row["ContactID"].ToString()));
d_PaymentDate
= (row.IsNull("PaymentDate") ?
Convert.DBNull : DateTime.Parse(row["PaymentDate"].ToString()));
d_PaymentlTime
= (row.IsNull("PaymentlTime") ?
Convert.DBNull : DateTime.Parse(row["PaymentlTime"].ToString()));
d_PaymentForMonth
= (row.IsNull("PaymentForMonth") ?
Convert.DBNull : DateTime.Parse(row["PaymentForMonth"].ToString()));
s_Amount
= row["Amount"].ToString();
s_AmountCurrency
= row["AmountCurrency"].ToString();
s_Notes
= row["Notes"].ToString();
}
/// <summary>
/// Populate DataSet from Object properties.
/// </summary>
private void
object2ds()
{
DataRow
row = ds.Tables[0].Rows[0];
row.BeginEdit();
row["PaymentID"]=i_PaymentID;
row["ContactID"]=i_ContactID;
row["PaymentDate"]=d_PaymentDate;
row["PaymentlTime"]=d_PaymentlTime;
row["PaymentForMonth"]=d_PaymentForMonth;
row["Amount"]=s_Amount;
row["AmountCurrency"]=s_AmountCurrency;
row["Notes"]=s_Notes;
row.EndEdit();
}
/// <summary>
/// Initialize commands.
/// </summary>
private void
init()
{
ds
= new DataSet("PAYMENTSDoc");
da.SelectCommand
= dbconn.CreateCommand();
da.SelectCommand.CommandText
= "SELECT * FROM Payments where PaymentID = ?
";
addParameter(da.SelectCommand,
"@PaymentID",DbType.Double, "PaymentID");
}
/// <summary>
/// Add database parameter to the select command
/// </summary>
private IDbDataParameter addParameter(IDbCommand
command, string parameterName, DbType parameterType,
string sourceColumn)
{
IDbDataParameter
param = command.CreateParameter();
param.ParameterName
= parameterName;
param.DbType
= parameterType;
param.Direction
= ParameterDirection.Input;
param.SourceVersion
= DataRowVersion.Current;
param.SourceColumn
= sourceColumn;
command.Parameters.Add(param);
return param;
}
/// <summary>
/// Generate XML string from the current DataSet
/// </summary>
public String
getXml(){
if(ds != null){
return ds.GetXml();
}
else
return "";
}
/// <summary>
/// Set properties of the object from XML string
/// </summary>
/// <param name='sXml'>Xml representation of the
object</param>
public void
setXml(String sXml)
{
if(ds != null)
{
ds.ReadXml(new StringReader(sXml));
ds2object();
}
}
/// <summary>
/// Get xml schema from current DataSet
/// </summary>
public string
getXmlSchema(){
if(ds != null){
return ds.GetXmlSchema();
}
else
return"";
}
/// <summary>
/// Get Database connection from OleDb connection string
/// </summary>
private IDbConnection getOleDbConnection(){
if(dbconn
== null){
try
{
dbconn
= new OleDbConnection("DSN=school; user_id=; password=;");
dbconn.Open();
}catch( Exception e ){
handleException(e);
return null;
}
}
return
dbconn;
}
/// <summary>
/// Get Database connection from ODBC connection string
/// </summary>
private IDbConnection getODBCConnection(){
if(dbconn
== null){
try
{
dbconn
= new OdbcConnection("DSN=school; user_id=; password=;");
dbconn.Open();
}catch( Exception e ){
handleException(e);
return null;
}
}
return
dbconn;
}
/// <summary>
/// Overwrite this method with standard exception
handling
/// procedure for your application.
/// </summary>
private void
handleException(Exception e){
MessageBox.Show(e.ToString());
}
/*
///
<summary>
///
Test Main method for the PAYMENTS class.
///
</summary>
[STAThread]
static
void Main(){
PAYMENTS
me;
me
= new PAYMENTS(new ODBCConnection("DSN=school; user_id=;
password=;"));
me.load(1);
MessageBox.Show(me.getXml());
}
*/
}
}
