Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 12-12-2008
Member
 
Join Date: Nov 2008
Posts: 996
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?
Reply With Quote
  #2  
Old 12-12-2008
Member
 
Join Date: May 2008
Posts: 3,971
Re: Problem of MySqlParameter in c#

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?
Reply With Quote
  #3  
Old 12-12-2008
Member
 
Join Date: Nov 2008
Posts: 996
Re: Problem of MySqlParameter in c#

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?
Reply With Quote
  #4  
Old 12-12-2008
Member
 
Join Date: Feb 2008
Posts: 194
Re: Problem of MySqlParameter in c#

Do you use a ORM? Your metadata in your header methods forced me to say so.
Reply With Quote
  #5  
Old 12-12-2008
Member
 
Join Date: Nov 2008
Posts: 996
Re: Problem of MySqlParameter in c#

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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Problem of MySqlParameter in c#"
Thread Thread Starter Forum Replies Last Post
Sony VGNCS3 laptop 15 inch screen problem...LCD or CHIP problem? Mick$Tyler Hardware Peripherals 5 31-10-2010 06:49 AM
Blackberry Tour Youtube problem/facebook and phone freezing problem Lohitaksha Portable Devices 6 27-07-2010 12:10 AM
Toshiba satellite L305D-S5893 shutdown problem or bios update problem Colter Portable Devices 4 10-04-2010 04:19 PM
AD problem in Server 2003, Userenv Event ID 1053 problem Victor Kam Active Directory 3 24-09-2007 04:44 PM
Hyperlink Problem, Unable to Open, A security problem has occurred LaKisha MS Office Support 1 30-11-2004 11:57 AM


All times are GMT +5.5. The time now is 09:52 PM.