Problem of MySqlParameter in c#

Here I created a generic class called Object which is supposed to be able to load / save / change any of my business objects. Each one of my business objects are inherited a generic class called Table, it possesses an attribute that most defines the name of the table and the primary key of the table base. Each of the properties have an attribute correspondent to the name and type of field base. That's the context.

My problem is with the backup. Whenever I want to save an object, I loop on the attribute and I get the name of fields and the associated value, I built my request like this.

The structure of the request is correct but the valuers are not replaced. For example, to type a query I will insert:

Code:

---

INSERT INTO test (id, value) values (@ID, @value)

---

It does not replace IDs parameter values.
Here is my code, I hope I have been quite clear:

Code:

---

public static void SaveObject (AGDDataTable poObject)
{
MySqlConnection cn;
MySqlParameter param;
MySqlCommand cmd;
String sSqlUpdate;
String sSqlInsertFields;
String sSqlInsertValues;

Type type = poObject.GetType ();

AGDTable atrTable = ((AGDTable []) type.GetCustomAttributes (typeof (AGDTable), true)) [0];
String sTableName = atrTable.TableName;
String sPrimaryKey = atrTable.PrimaryKey;
String sVirgule = "";

sSqlUpdate = "UPDATE " + sTableName + " SET ";

sSqlInsertFields = "INSERT INTO " + sTableName + "(" + sPrimaryKey;
sSqlInsertValues = "VALUES (@ ID";

foreach (PropertyInfo pi in type.GetProperties ()) / / Construction of the request from the properties in my class
{
AGDField [] aoField = (AGDField []) pi.GetCustomAttributes (typeof (AGDField), false);
if (aoField.Length> 0)
{
String sFieldName = aoField[0].FieldName;

sSqlUpdate += sVirgule + sFieldName + " = @" + sFieldName;
sSqlInsertFields += ", " + sFieldName;
sSqlInsertValues += ", @" + sFieldName;

sVirgule = ", ";
}
}

sSqlUpdate += " WHERE " + sPrimaryKey + " = @ID";
sSqlInsertFields += " )";
sSqlInsertValues += " )";

cn = new MySqlConnection(Properties.Resources.SECUConnectionString);
cn.Open ();

if (poObject.Id> 0)
{
cmd = new MySqlCommand (sSqlUpdate, cn);
}
else
{
poObject.Id = GetNextId(sTableName);

cmd = new MySqlCommand (sSqlInsertFields + sSqlInsertValues, cn);
}

foreach (PropertyInfo pi in type.GetProperties ()) / / Creating parameters
{
AGDField [] aoField = (AGDField []) pi.GetCustomAttributes (typeof (AGDField), false);
if (aoField.Length> 0)
{
/ / Get the attribute AGDField to know the name of the field and type in base
AGDField field = aoField [0];
param = new MySqlParameter (field.FieldName, field.DBType);

/ / Get the value of this property for this instance of an object
param.Value = pi.GetValue (poObject, null);
cmd.Parameters.Add (param);
}
}

param = new MySqlParameter (sPrimaryKey, MySqlDbType.Int64);
param.Value = poObject.Id;
cmd.Parameters.Add (param);

try
{
cmd.ExecuteNonQuery ();
}
catch (Exception excp)
{
Exception myExcp = new Exception("Could not Save " + poObject.GetType().Name + ". Error: " + excp.Message, excp);
throw (myExcp);
}

}

---

Do someone know why my settings are not taken into account?

1) Creating a class called Object is dangerous because there is already a class Object in the tree of .NET classes, which inherit all classes

2) your parameters are not taken into account, I do not know what to do with concatenations of your chains, but it means nothing. Why not do something like this

Code:

---

String req = "INSERT INTO Table(field1, field2) VALUES(?field1, ?field2);
MySqlCommand cmd = new MySqlCommand (connection, req);
cmd.Parameters.Add ( "? field1", value);
cmd.Parameters.Add ( "? field2", value);
cmd.ExecuteNonQuery ();
...

---

instead of your foreach?

For the first remark, it is not called Object. Then I do not know the fields or the values when I call this function. It receives an object type AGDDataTable which can represent my application. Hence the usefulness of my foreach loop that I used to recover the fields and the values:

Code:

---

[AGDTable ( "SESSION", "ID_SESSION")]
public class Session: AGDDataTable
{
[AGDField("ID_LOGIN", AGDDBType.BigInt, typeof(Login))]
public Login Login { get; set; }

[AGDField("ID_BD", AGDDBType.BigInt, typeof(BD))]
Public BD BD { get; set; }

[AGDField("DATEHDEBUT_SESSION", AGDDBType.BigInt)]
public Int64 DateDebut { get; set; }

[AGDField("DATEHFIN_SESSION", AGDDBType.BigInt)]
public Int64 DateFin { get; set; }

[AGDField("IP_SESSION", AGDDBType.Char)]
public String Ip { get; set; }

[AGDField("DTAEHMANIF_SESSION", AGDDBType.BigInt)]
public Int64 DateManif { get; set; }

[AGDField("ID_DATETRAITEMENT", AGDDBType.BigInt)]
public Int64 DateTraitement { get; set; }

[AGDField("CONTEXT_SESSION", AGDDBType.Blob)]
public String Context { get; set; }
}

---

Do you know what I am trying to do or I'm not clear?

Do you use a ORM? Your metadata in your header methods forced me to say so.

No I do not use ORM.

In fact I have a little more researched on my bug. I described my DBType in MySqlDBType and it works.