H
Hans Michael Rupp
I am experimenting with ADO.NET
I use MySQL 3.23 with MyODBC 3.51
At the moment I simply try an insert a select and an update. I use the
usual DataSet approach. The select works and retrieves the data from the
database. When I try an update I always get a
System.Data.DBConcurrencyException
I have looked thru some Microsoft documentation and as far as I
understood it a DBConcurrencyException is only thrown when the DataSet
is out of sync with the database.
I am pretty sure that I don't change the database between the filling of
the DataSet and testing the Update statement. I have already looked at
the DataRow which I try to update immediately before
dataAdapter.Update(dataSetChanged);
it seems hold the same data as as the database.
Relevant code at the end of the message.
Does anybody know what could cause this behavior, or give my some hints
what to investigate?
Many thanks,
Hans
-------------------------------------------------------------------------
CREATE TABLE Personen (
PNr INTEGER(32) NOT NULL AUTO_INCREMENT,
Typen_Tid INTEGER(32) NOT NULL,
Name VARCHAR(100) NOT NULL,
Vorname VARCHAR(100) NOT NULL,
Geburtstag DATE NOT NULL,
Bemerkungen TEXT NULL,
Foto BLOB NULL,
Aktiv BOOL NULL,
PRIMARY KEY(PNr),
INDEX Personen_FKIndex1(Typen_Tid),
INDEX Personen_Name(Name, Vorname),
INDEX Personen_Geburtstag(Geburtstag)
);
INSERT INTO Personen (Typen_Tid, Name, Vorname, Geburtstag, Bemerkungen,
Foto, Aktiv)
VALUES (1, "Renschler", "Melanie", "1985-12-10", "blafasel", NULL, 1);
--------------------------------------------------------------------------
The update statement I use in the code:
private static String UPDATE = "UPDATE Personen SET Typen_Tid
=@Typen_Tid, " +
"Name =
@Name, " +
"Vorname =
@Vorname, " +
"Geburtstag
= @Geburtstag, " +
"Bemerkungen = @Bemerkungen, " +
"Foto =
@Foto, " +
"Aktiv =
@Aktiv " +
"Where PNr
= @PNr";
--------------------------------------------------------------------------
Just the data object:
using System;
using System.Text;
namespace FechtenBusinessLayer {
/// <summary>
/// Zusammenfassung für Person.
/// </summary>
public class Person {
private int pNr;
private string name;
private string vorname;
private DateTime geburtstag;
private string bemerkungen;
private Typ typ;
private Funktion[] funktionen;
private Adresse[] adressen;
private Qualifikation[] qualifikationen;
private bool aktiv;
public Person() {
}
public Person(int pNr, string name, string vorname, DateTime
geburtstag, string bemerkungen, Typ typ, Funktion[] funktionen,
Adresse[] adressen, Qualifikation[] qualifikationen, bool aktiv) {
this.pNr = pNr;
this.name = name;
this.vorname = vorname;
this.geburtstag = geburtstag;
this.bemerkungen = bemerkungen;
this.typ = typ;
this.funktionen = funktionen;
this.adressen = adressen;
this.qualifikationen = qualifikationen;
this.aktiv = aktiv;
}
public int PNr {
get {
return this.pNr;
}
set {
this.pNr = value;
}
}
public string Name {
get {
return this.name;
}
set {
this.name = value;
}
}
public string Vorname {
get {
return this.vorname;
}
set {
this.vorname = value;
}
}
public DateTime Geburtstag {
get {
return this.geburtstag;
}
set {
this.geburtstag = value;
}
}
public string Bemerkungen {
get {
return this.bemerkungen;
}
set {
this.bemerkungen = value;
}
}
public Typ Typ {
get {
return this.typ;
}
set {
this.typ = value;
}
}
public Funktion[] Funktionen {
get {
return this.funktionen;
}
set {
this.funktionen = value;
}
}
public Adresse[] Adressen {
get {
return this.adressen;
}
set {
this.adressen = value;
}
}
public Qualifikation[] Qualifikationen {
get {
return this.qualifikationen;
}
set {
this.qualifikationen = value;
}
}
public bool Aktiv {
get {
return this.aktiv;
}
set {
this.aktiv = value;
}
}
public override string ToString() {
StringBuilder buff = new StringBuilder();
buff.Append("Class: Person\n");
buff.Append("PNr: ");
buff.Append(pNr + "\n");
buff.Append("Name: ");
buff.Append(name + "\n");
buff.Append("Vorname: ");
buff.Append(vorname + "\n");
buff.Append("Geburtstag: ");
buff.Append(geburtstag + "\n");
buff.Append("Bemerkungen: ");
buff.Append(bemerkungen + "\n");
buff.Append("Typ: ");
buff.Append(typ + "\n");
buff.Append("Funktionen: ");
buff.Append(funktionen + "\n");
buff.Append("Adressen: ");
buff.Append(adressen + "\n");
buff.Append("Qualifikationen: ");
buff.Append(qualifikationen + "\n");
buff.Append("Aktiv: ");
buff.Append(aktiv + "\n");
return buff.ToString();
}
}
}
---------------------------------------------------------------------------
The class which is supposed to handle the persistance:
using System;
using FechtenBusinessLayer;
using System.Data;
using System.Collections;
namespace FechtenPersistanceLayer
{
/// <summary>
/// Zusammenfassung für PersonenDAO.
/// </summary>
public class PersonenDAO : GenericDAO
{
private static string DATE_FORMAT = "yyy-MM-dd";
private IDbDataAdapter dataAdapter;
private DataSet dataSet;
private DataTable personenTable;
private static String SELECT = "SELECT * FROM Personen";
private static String INSERT = "INSERT INTO Personen
(Typen_Tid, Name, Vorname, Geburtstag, Bemerkungen, Foto, Aktiv)" +
"VALUES (@Typen_Tid, @Name,
@Vorname, @Geburtstag, @Bemerkungen, @Foto, @Aktiv)";
private static String UPDATE = "UPDATE Personen SET Typen_Tid
=@Typen_Tid, " +
"Name =
@Name, " +
"Vorname =
@Vorname, " +
"Geburtstag
= @Geburtstag, " +
"Bemerkungen = @Bemerkungen, " +
"Foto =
@Foto, " +
"Aktiv =
@Aktiv " +
"Where PNr
= @PNr";
private static String DELETE = "DELETE FROM Personen WHERE PNr
= @PNr";
public PersonenDAO() : base()
{
IDbCommand selectCommand = factory.GetCommand(SELECT,
connection);
dataAdapter = factory.GetDataAdapter(selectCommand);
dataAdapter.InsertCommand = connection.CreateCommand();
dataAdapter.InsertCommand.CommandText = INSERT;
AddStringParams(dataAdapter.InsertCommand, "PNr",
"Typen_Tid", "Name", "Vorname", "Geburtstag", "Bemerkungen", "Foto",
"Aktiv");
dataAdapter.UpdateCommand = connection.CreateCommand();
dataAdapter.UpdateCommand.CommandText = UPDATE;
AddStringParams(dataAdapter.UpdateCommand, "Typen_Tid",
"Name", "Vorname", "Geburtstag", "Bemerkungen", "Foto", "Aktiv", "PNr");
dataAdapter.DeleteCommand = connection.CreateCommand();
dataAdapter.DeleteCommand.CommandText = DELETE;
AddStringParams(dataAdapter.DeleteCommand, "PNr");
dataSet = new DataSet();
dataAdapter.Fill(dataSet);
personenTable = dataSet.Tables[0];
DataColumn key = personenTable.Columns["PNr"];
DataColumn[] keys = {key};
personenTable.PrimaryKey = keys;
}
public Person[] GetPersonen() {
Person[] personen = null;
ArrayList personenList = new ArrayList();
foreach(DataRow row in personenTable.Rows) {
Person person = new Person();
person.PNr = Int32.Parse(row["PNr"].ToString());
person.Name = row["Name"].ToString();
person.Vorname = row["Vorname"].ToString();
person.Geburtstag =
MySQLDateUtil.getDate(row["Geburtstag"].ToString());
Typ type = new Typ();
type.Tid = Int32.Parse(row["Typen_Tid"].ToString());
person.Typ = type;
person.Bemerkungen = row["Bemerkungen"].ToString();
int aktiv = Int32.Parse(row["Aktiv"].ToString());
if (aktiv == 1) {
person.Aktiv = true;
}
else {
person.Aktiv = false;
}
personenList.Add(person);
}
personen = (Person[]) personenList.ToArray(typeof(Person));
return personen;
}
public void UpdatePersonen(Person[] personen) {
Console.WriteLine("UpdatePersonen");
foreach (Person person in personen) {
//get the primary keys
Object[] keys = {person.PNr};
Console.WriteLine("PersonenTable has " +
personenTable.Rows.Count + " rows");
DataRow targetRow = personenTable.Rows.Find(keys);
Console.WriteLine("Keys: " + keys[0]);
Console.WriteLine("TargetRow: " + targetRow.ToString());
Console.WriteLine("TargetRow Table Name: " +
targetRow.Table.TableName);
printRow(targetRow);
targetRow.BeginEdit();
targetRow["Name"] = person.Name;
targetRow["Vorname"] = person.Vorname;
targetRow["Geburtstag"] = person.Geburtstag;
targetRow["Typen_Tid"] = person.Typ.Tid;
targetRow["Bemerkungen"] = person.Bemerkungen;
targetRow["Foto"] = DBNull.Value;
int aktiv = 0;
if (person.Aktiv) {
aktiv = 1;
}
targetRow["Aktiv"] = aktiv;
targetRow.EndEdit();
}
DataSet dataSetChanged =
dataSet.GetChanges(DataRowState.Modified);
if (dataSetChanged == null) {
Console.WriteLine("DataSetChanged is null");
}
Console.WriteLine("Datenset has changes: " +
dataSetChanged.HasChanges());
ArrayList primaryKeysOfErrorRowList = new ArrayList();
bool ok = true;
if (dataSetChanged.HasErrors) {
ok = false;
string errorMessage = "Erros in row(s) with PNr:";
foreach (DataTable table in dataSetChanged.Tables) {
if (table.HasErrors) {
DataRow[] errorRows = table.GetErrors();
foreach (DataRow row in errorRows) {
Object[] key = {row["PNr"]};
primaryKeysOfErrorRowList.Add(key);
errorMessage += " " + key;
}
}
}
Object[][] primaryKeysOfErrorRows = (Object[][])
primaryKeysOfErrorRowList.ToArray(typeof(Object[]));
throw new UpdateException("Update Exception on table
Personen", "Personen", primaryKeysOfErrorRows);
}
if (ok) {
try {
dataAdapter.Update(dataSetChanged);
}
catch (DBConcurrencyException dbConEx) {
string customErrorMessage;
customErrorMessage = "Concurrency violation at row ";
customErrorMessage += dbConEx.Row[0].ToString();
Console.WriteLine(customErrorMessage);
Console.WriteLine(dbConEx.StackTrace);
Console.WriteLine(dbConEx.Message);
Console.WriteLine(dbConEx.InnerException);
throw new UpdateException(customErrorMessage, dbConEx);
}
dataSet.AcceptChanges();
}
else dataSet.RejectChanges();
}
private void printRow(DataRow targetRow) {
Console.WriteLine("PNr " + targetRow["PNr"]);
Console.WriteLine("Name " + targetRow["Name"]);
Console.WriteLine("Vorname " + targetRow["Vorname"]);
Console.WriteLine("Geburtstag " + targetRow["Geburtstag"]);
Console.WriteLine("Typen_Tid " + targetRow["Typen_Tid"]);
Console.WriteLine("Bemerkungen " + targetRow["Bemerkungen"]);
Console.WriteLine("Aktiv " + targetRow["Aktiv"]);
Console.WriteLine("Foto " + targetRow["Foto"]);
}
}
}
--------------------------------------------------------------------------
Parent class:
using System;
using System.Data;
using System.IO;
using PersistanceFactory;
using System.Windows.Forms;
using System.Xml;
namespace FechtenPersistanceLayer {
/// <summary>
/// Base class for DAO (Data Access Object) classes
/// </summary>
public class GenericDAO {
protected GenericPersistanceFactory factory;
//To Do: Connection Pooling
protected IDbConnection connection;
public GenericDAO() {
factory = GenericPersistanceFactory.GetPersistanceFactory();
connection = this.getConnection(factory);
}
protected void AddParams(IDbCommand command, DbType type,
params string[] cols) {
foreach (String column in cols) {
IDbDataParameter parameter =
factory.GetDataParameter("@" + column, type, 0, column);
command.Parameters.Add(parameter);
}
}
protected void AddStringParams(IDbCommand command, params
string[] cols) {
foreach (String column in cols) {
IDbDataParameter parameter =
factory.GetDataParameter("@" + column, DbType.String, 0, column);
command.Parameters.Add(parameter);
}
}
protected void AddIntParams(IDbCommand command, params string[]
cols) {
foreach (String column in cols) {
IDbDataParameter parameter =
factory.GetDataParameter("@" + column, DbType.Int32, 0, column);
command.Parameters.Add(parameter);
}
}
private IDbConnection getConnection(GenericPersistanceFactory
factory) {
IDbConnection connection = null;
//string xmlFileName =
Path.Combine(Application.StartupPath, "configuration.xml");
string xmlFileName =
@"E:\Programmierung\Projekte\Fechtabteilung\Source\PersistanceTest\bin\Debug\configuration.xml";
XmlTextReader xmlReader = null;
String connectionString = null;
try {
xmlReader = new XmlTextReader(xmlFileName);
while (xmlReader.Read()) {
if (xmlReader.NodeType == XmlNodeType.Element) {
if (xmlReader.Name == "ConnectionString") {
if (xmlReader.MoveToContent() !=
XmlNodeType.None) {
connectionString = xmlReader.ReadString();
}
}
}
}
}
catch (Exception ex) {
Console.WriteLine(ex.StackTrace);
throw new ConfigurationException("Could not read in
configuration.xml, Configuration/Persistence/ConnectionString", ex);
}
if (connectionString == null || "".Equals(connectionString)) {
throw new ConfigurationException("Could not read in
configuration.xml, Configuration/Persistence/ConnectionString");
}
MessageBox.Show(connectionString);
connection = factory.GetConnection(connectionString);
return connection;
}
}
}
------------------------------------------------------------------------
The NUnit test:
using System;
using System.Data;
using System.IO;
using PersistanceFactory;
using System.Windows.Forms;
using System.Xml;
namespace FechtenPersistanceLayer {
/// <summary>
/// Base class for DAO (Data Access Object) classes
/// </summary>
public class GenericDAO {
protected GenericPersistanceFactory factory;
//To Do: Connection Pooling
protected IDbConnection connection;
public GenericDAO() {
factory = GenericPersistanceFactory.GetPersistanceFactory();
connection = this.getConnection(factory);
}
protected void AddParams(IDbCommand command, DbType type,
params string[] cols) {
foreach (String column in cols) {
IDbDataParameter parameter =
factory.GetDataParameter("@" + column, type, 0, column);
command.Parameters.Add(parameter);
}
}
protected void AddStringParams(IDbCommand command, params
string[] cols) {
foreach (String column in cols) {
IDbDataParameter parameter =
factory.GetDataParameter("@" + column, DbType.String, 0, column);
command.Parameters.Add(parameter);
}
}
protected void AddIntParams(IDbCommand command, params string[]
cols) {
foreach (String column in cols) {
IDbDataParameter parameter =
factory.GetDataParameter("@" + column, DbType.Int32, 0, column);
command.Parameters.Add(parameter);
}
}
private IDbConnection getConnection(GenericPersistanceFactory
factory) {
IDbConnection connection = null;
//string xmlFileName =
Path.Combine(Application.StartupPath, "configuration.xml");
string xmlFileName =
@"E:\Programmierung\Projekte\Fechtabteilung\Source\PersistanceTest\bin\Debug\configuration.xml";
XmlTextReader xmlReader = null;
String connectionString = null;
try {
xmlReader = new XmlTextReader(xmlFileName);
while (xmlReader.Read()) {
if (xmlReader.NodeType == XmlNodeType.Element) {
if (xmlReader.Name == "ConnectionString") {
if (xmlReader.MoveToContent() !=
XmlNodeType.None) {
connectionString = xmlReader.ReadString();
}
}
}
}
}
catch (Exception ex) {
Console.WriteLine(ex.StackTrace);
throw new ConfigurationException("Could not read in
configuration.xml, Configuration/Persistence/ConnectionString", ex);
}
if (connectionString == null || "".Equals(connectionString)) {
throw new ConfigurationException("Could not read in
configuration.xml, Configuration/Persistence/ConnectionString");
}
MessageBox.Show(connectionString);
connection = factory.GetConnection(connectionString);
return connection;
}
}
}
I use MySQL 3.23 with MyODBC 3.51
At the moment I simply try an insert a select and an update. I use the
usual DataSet approach. The select works and retrieves the data from the
database. When I try an update I always get a
System.Data.DBConcurrencyException
I have looked thru some Microsoft documentation and as far as I
understood it a DBConcurrencyException is only thrown when the DataSet
is out of sync with the database.
I am pretty sure that I don't change the database between the filling of
the DataSet and testing the Update statement. I have already looked at
the DataRow which I try to update immediately before
dataAdapter.Update(dataSetChanged);
it seems hold the same data as as the database.
Relevant code at the end of the message.
Does anybody know what could cause this behavior, or give my some hints
what to investigate?
Many thanks,
Hans
-------------------------------------------------------------------------
CREATE TABLE Personen (
PNr INTEGER(32) NOT NULL AUTO_INCREMENT,
Typen_Tid INTEGER(32) NOT NULL,
Name VARCHAR(100) NOT NULL,
Vorname VARCHAR(100) NOT NULL,
Geburtstag DATE NOT NULL,
Bemerkungen TEXT NULL,
Foto BLOB NULL,
Aktiv BOOL NULL,
PRIMARY KEY(PNr),
INDEX Personen_FKIndex1(Typen_Tid),
INDEX Personen_Name(Name, Vorname),
INDEX Personen_Geburtstag(Geburtstag)
);
INSERT INTO Personen (Typen_Tid, Name, Vorname, Geburtstag, Bemerkungen,
Foto, Aktiv)
VALUES (1, "Renschler", "Melanie", "1985-12-10", "blafasel", NULL, 1);
--------------------------------------------------------------------------
The update statement I use in the code:
private static String UPDATE = "UPDATE Personen SET Typen_Tid
=@Typen_Tid, " +
"Name =
@Name, " +
"Vorname =
@Vorname, " +
"Geburtstag
= @Geburtstag, " +
"Bemerkungen = @Bemerkungen, " +
"Foto =
@Foto, " +
"Aktiv =
@Aktiv " +
"Where PNr
= @PNr";
--------------------------------------------------------------------------
Just the data object:
using System;
using System.Text;
namespace FechtenBusinessLayer {
/// <summary>
/// Zusammenfassung für Person.
/// </summary>
public class Person {
private int pNr;
private string name;
private string vorname;
private DateTime geburtstag;
private string bemerkungen;
private Typ typ;
private Funktion[] funktionen;
private Adresse[] adressen;
private Qualifikation[] qualifikationen;
private bool aktiv;
public Person() {
}
public Person(int pNr, string name, string vorname, DateTime
geburtstag, string bemerkungen, Typ typ, Funktion[] funktionen,
Adresse[] adressen, Qualifikation[] qualifikationen, bool aktiv) {
this.pNr = pNr;
this.name = name;
this.vorname = vorname;
this.geburtstag = geburtstag;
this.bemerkungen = bemerkungen;
this.typ = typ;
this.funktionen = funktionen;
this.adressen = adressen;
this.qualifikationen = qualifikationen;
this.aktiv = aktiv;
}
public int PNr {
get {
return this.pNr;
}
set {
this.pNr = value;
}
}
public string Name {
get {
return this.name;
}
set {
this.name = value;
}
}
public string Vorname {
get {
return this.vorname;
}
set {
this.vorname = value;
}
}
public DateTime Geburtstag {
get {
return this.geburtstag;
}
set {
this.geburtstag = value;
}
}
public string Bemerkungen {
get {
return this.bemerkungen;
}
set {
this.bemerkungen = value;
}
}
public Typ Typ {
get {
return this.typ;
}
set {
this.typ = value;
}
}
public Funktion[] Funktionen {
get {
return this.funktionen;
}
set {
this.funktionen = value;
}
}
public Adresse[] Adressen {
get {
return this.adressen;
}
set {
this.adressen = value;
}
}
public Qualifikation[] Qualifikationen {
get {
return this.qualifikationen;
}
set {
this.qualifikationen = value;
}
}
public bool Aktiv {
get {
return this.aktiv;
}
set {
this.aktiv = value;
}
}
public override string ToString() {
StringBuilder buff = new StringBuilder();
buff.Append("Class: Person\n");
buff.Append("PNr: ");
buff.Append(pNr + "\n");
buff.Append("Name: ");
buff.Append(name + "\n");
buff.Append("Vorname: ");
buff.Append(vorname + "\n");
buff.Append("Geburtstag: ");
buff.Append(geburtstag + "\n");
buff.Append("Bemerkungen: ");
buff.Append(bemerkungen + "\n");
buff.Append("Typ: ");
buff.Append(typ + "\n");
buff.Append("Funktionen: ");
buff.Append(funktionen + "\n");
buff.Append("Adressen: ");
buff.Append(adressen + "\n");
buff.Append("Qualifikationen: ");
buff.Append(qualifikationen + "\n");
buff.Append("Aktiv: ");
buff.Append(aktiv + "\n");
return buff.ToString();
}
}
}
---------------------------------------------------------------------------
The class which is supposed to handle the persistance:
using System;
using FechtenBusinessLayer;
using System.Data;
using System.Collections;
namespace FechtenPersistanceLayer
{
/// <summary>
/// Zusammenfassung für PersonenDAO.
/// </summary>
public class PersonenDAO : GenericDAO
{
private static string DATE_FORMAT = "yyy-MM-dd";
private IDbDataAdapter dataAdapter;
private DataSet dataSet;
private DataTable personenTable;
private static String SELECT = "SELECT * FROM Personen";
private static String INSERT = "INSERT INTO Personen
(Typen_Tid, Name, Vorname, Geburtstag, Bemerkungen, Foto, Aktiv)" +
"VALUES (@Typen_Tid, @Name,
@Vorname, @Geburtstag, @Bemerkungen, @Foto, @Aktiv)";
private static String UPDATE = "UPDATE Personen SET Typen_Tid
=@Typen_Tid, " +
"Name =
@Name, " +
"Vorname =
@Vorname, " +
"Geburtstag
= @Geburtstag, " +
"Bemerkungen = @Bemerkungen, " +
"Foto =
@Foto, " +
"Aktiv =
@Aktiv " +
"Where PNr
= @PNr";
private static String DELETE = "DELETE FROM Personen WHERE PNr
= @PNr";
public PersonenDAO() : base()
{
IDbCommand selectCommand = factory.GetCommand(SELECT,
connection);
dataAdapter = factory.GetDataAdapter(selectCommand);
dataAdapter.InsertCommand = connection.CreateCommand();
dataAdapter.InsertCommand.CommandText = INSERT;
AddStringParams(dataAdapter.InsertCommand, "PNr",
"Typen_Tid", "Name", "Vorname", "Geburtstag", "Bemerkungen", "Foto",
"Aktiv");
dataAdapter.UpdateCommand = connection.CreateCommand();
dataAdapter.UpdateCommand.CommandText = UPDATE;
AddStringParams(dataAdapter.UpdateCommand, "Typen_Tid",
"Name", "Vorname", "Geburtstag", "Bemerkungen", "Foto", "Aktiv", "PNr");
dataAdapter.DeleteCommand = connection.CreateCommand();
dataAdapter.DeleteCommand.CommandText = DELETE;
AddStringParams(dataAdapter.DeleteCommand, "PNr");
dataSet = new DataSet();
dataAdapter.Fill(dataSet);
personenTable = dataSet.Tables[0];
DataColumn key = personenTable.Columns["PNr"];
DataColumn[] keys = {key};
personenTable.PrimaryKey = keys;
}
public Person[] GetPersonen() {
Person[] personen = null;
ArrayList personenList = new ArrayList();
foreach(DataRow row in personenTable.Rows) {
Person person = new Person();
person.PNr = Int32.Parse(row["PNr"].ToString());
person.Name = row["Name"].ToString();
person.Vorname = row["Vorname"].ToString();
person.Geburtstag =
MySQLDateUtil.getDate(row["Geburtstag"].ToString());
Typ type = new Typ();
type.Tid = Int32.Parse(row["Typen_Tid"].ToString());
person.Typ = type;
person.Bemerkungen = row["Bemerkungen"].ToString();
int aktiv = Int32.Parse(row["Aktiv"].ToString());
if (aktiv == 1) {
person.Aktiv = true;
}
else {
person.Aktiv = false;
}
personenList.Add(person);
}
personen = (Person[]) personenList.ToArray(typeof(Person));
return personen;
}
public void UpdatePersonen(Person[] personen) {
Console.WriteLine("UpdatePersonen");
foreach (Person person in personen) {
//get the primary keys
Object[] keys = {person.PNr};
Console.WriteLine("PersonenTable has " +
personenTable.Rows.Count + " rows");
DataRow targetRow = personenTable.Rows.Find(keys);
Console.WriteLine("Keys: " + keys[0]);
Console.WriteLine("TargetRow: " + targetRow.ToString());
Console.WriteLine("TargetRow Table Name: " +
targetRow.Table.TableName);
printRow(targetRow);
targetRow.BeginEdit();
targetRow["Name"] = person.Name;
targetRow["Vorname"] = person.Vorname;
targetRow["Geburtstag"] = person.Geburtstag;
targetRow["Typen_Tid"] = person.Typ.Tid;
targetRow["Bemerkungen"] = person.Bemerkungen;
targetRow["Foto"] = DBNull.Value;
int aktiv = 0;
if (person.Aktiv) {
aktiv = 1;
}
targetRow["Aktiv"] = aktiv;
targetRow.EndEdit();
}
DataSet dataSetChanged =
dataSet.GetChanges(DataRowState.Modified);
if (dataSetChanged == null) {
Console.WriteLine("DataSetChanged is null");
}
Console.WriteLine("Datenset has changes: " +
dataSetChanged.HasChanges());
ArrayList primaryKeysOfErrorRowList = new ArrayList();
bool ok = true;
if (dataSetChanged.HasErrors) {
ok = false;
string errorMessage = "Erros in row(s) with PNr:";
foreach (DataTable table in dataSetChanged.Tables) {
if (table.HasErrors) {
DataRow[] errorRows = table.GetErrors();
foreach (DataRow row in errorRows) {
Object[] key = {row["PNr"]};
primaryKeysOfErrorRowList.Add(key);
errorMessage += " " + key;
}
}
}
Object[][] primaryKeysOfErrorRows = (Object[][])
primaryKeysOfErrorRowList.ToArray(typeof(Object[]));
throw new UpdateException("Update Exception on table
Personen", "Personen", primaryKeysOfErrorRows);
}
if (ok) {
try {
dataAdapter.Update(dataSetChanged);
}
catch (DBConcurrencyException dbConEx) {
string customErrorMessage;
customErrorMessage = "Concurrency violation at row ";
customErrorMessage += dbConEx.Row[0].ToString();
Console.WriteLine(customErrorMessage);
Console.WriteLine(dbConEx.StackTrace);
Console.WriteLine(dbConEx.Message);
Console.WriteLine(dbConEx.InnerException);
throw new UpdateException(customErrorMessage, dbConEx);
}
dataSet.AcceptChanges();
}
else dataSet.RejectChanges();
}
private void printRow(DataRow targetRow) {
Console.WriteLine("PNr " + targetRow["PNr"]);
Console.WriteLine("Name " + targetRow["Name"]);
Console.WriteLine("Vorname " + targetRow["Vorname"]);
Console.WriteLine("Geburtstag " + targetRow["Geburtstag"]);
Console.WriteLine("Typen_Tid " + targetRow["Typen_Tid"]);
Console.WriteLine("Bemerkungen " + targetRow["Bemerkungen"]);
Console.WriteLine("Aktiv " + targetRow["Aktiv"]);
Console.WriteLine("Foto " + targetRow["Foto"]);
}
}
}
--------------------------------------------------------------------------
Parent class:
using System;
using System.Data;
using System.IO;
using PersistanceFactory;
using System.Windows.Forms;
using System.Xml;
namespace FechtenPersistanceLayer {
/// <summary>
/// Base class for DAO (Data Access Object) classes
/// </summary>
public class GenericDAO {
protected GenericPersistanceFactory factory;
//To Do: Connection Pooling
protected IDbConnection connection;
public GenericDAO() {
factory = GenericPersistanceFactory.GetPersistanceFactory();
connection = this.getConnection(factory);
}
protected void AddParams(IDbCommand command, DbType type,
params string[] cols) {
foreach (String column in cols) {
IDbDataParameter parameter =
factory.GetDataParameter("@" + column, type, 0, column);
command.Parameters.Add(parameter);
}
}
protected void AddStringParams(IDbCommand command, params
string[] cols) {
foreach (String column in cols) {
IDbDataParameter parameter =
factory.GetDataParameter("@" + column, DbType.String, 0, column);
command.Parameters.Add(parameter);
}
}
protected void AddIntParams(IDbCommand command, params string[]
cols) {
foreach (String column in cols) {
IDbDataParameter parameter =
factory.GetDataParameter("@" + column, DbType.Int32, 0, column);
command.Parameters.Add(parameter);
}
}
private IDbConnection getConnection(GenericPersistanceFactory
factory) {
IDbConnection connection = null;
//string xmlFileName =
Path.Combine(Application.StartupPath, "configuration.xml");
string xmlFileName =
@"E:\Programmierung\Projekte\Fechtabteilung\Source\PersistanceTest\bin\Debug\configuration.xml";
XmlTextReader xmlReader = null;
String connectionString = null;
try {
xmlReader = new XmlTextReader(xmlFileName);
while (xmlReader.Read()) {
if (xmlReader.NodeType == XmlNodeType.Element) {
if (xmlReader.Name == "ConnectionString") {
if (xmlReader.MoveToContent() !=
XmlNodeType.None) {
connectionString = xmlReader.ReadString();
}
}
}
}
}
catch (Exception ex) {
Console.WriteLine(ex.StackTrace);
throw new ConfigurationException("Could not read in
configuration.xml, Configuration/Persistence/ConnectionString", ex);
}
if (connectionString == null || "".Equals(connectionString)) {
throw new ConfigurationException("Could not read in
configuration.xml, Configuration/Persistence/ConnectionString");
}
MessageBox.Show(connectionString);
connection = factory.GetConnection(connectionString);
return connection;
}
}
}
------------------------------------------------------------------------
The NUnit test:
using System;
using System.Data;
using System.IO;
using PersistanceFactory;
using System.Windows.Forms;
using System.Xml;
namespace FechtenPersistanceLayer {
/// <summary>
/// Base class for DAO (Data Access Object) classes
/// </summary>
public class GenericDAO {
protected GenericPersistanceFactory factory;
//To Do: Connection Pooling
protected IDbConnection connection;
public GenericDAO() {
factory = GenericPersistanceFactory.GetPersistanceFactory();
connection = this.getConnection(factory);
}
protected void AddParams(IDbCommand command, DbType type,
params string[] cols) {
foreach (String column in cols) {
IDbDataParameter parameter =
factory.GetDataParameter("@" + column, type, 0, column);
command.Parameters.Add(parameter);
}
}
protected void AddStringParams(IDbCommand command, params
string[] cols) {
foreach (String column in cols) {
IDbDataParameter parameter =
factory.GetDataParameter("@" + column, DbType.String, 0, column);
command.Parameters.Add(parameter);
}
}
protected void AddIntParams(IDbCommand command, params string[]
cols) {
foreach (String column in cols) {
IDbDataParameter parameter =
factory.GetDataParameter("@" + column, DbType.Int32, 0, column);
command.Parameters.Add(parameter);
}
}
private IDbConnection getConnection(GenericPersistanceFactory
factory) {
IDbConnection connection = null;
//string xmlFileName =
Path.Combine(Application.StartupPath, "configuration.xml");
string xmlFileName =
@"E:\Programmierung\Projekte\Fechtabteilung\Source\PersistanceTest\bin\Debug\configuration.xml";
XmlTextReader xmlReader = null;
String connectionString = null;
try {
xmlReader = new XmlTextReader(xmlFileName);
while (xmlReader.Read()) {
if (xmlReader.NodeType == XmlNodeType.Element) {
if (xmlReader.Name == "ConnectionString") {
if (xmlReader.MoveToContent() !=
XmlNodeType.None) {
connectionString = xmlReader.ReadString();
}
}
}
}
}
catch (Exception ex) {
Console.WriteLine(ex.StackTrace);
throw new ConfigurationException("Could not read in
configuration.xml, Configuration/Persistence/ConnectionString", ex);
}
if (connectionString == null || "".Equals(connectionString)) {
throw new ConfigurationException("Could not read in
configuration.xml, Configuration/Persistence/ConnectionString");
}
MessageBox.Show(connectionString);
connection = factory.GetConnection(connectionString);
return connection;
}
}
}