1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110
| ==============================================
LECTURE DE LA STRUCTURE DE LA TABLE
==============================================
try
{
string olebcommandstr = "";
switch (database.DbType)
{
case SxSysDatabase.DatabaseType.MSACCESS_OleDB :
olebcommandstr = "SELECT TOP 1 * FROM "+tablename; break;
case SxSysDatabase.DatabaseType.MYSQL_OleDB :
olebcommandstr = "SELECT * FROM "+tablename+" LIMIT 0,1"; break;
}
OleDbCommand SelectCommand = new OleDbCommand(olebcommandstr,(OleDbConnection)database.DbCnx);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = SelectCommand;
System.Data.DataSet dataSet = new System.Data.DataSet() ;
adapter.Fill(dataSet);
==============================================
CONSTRUCTION DES COMMANDES SQL DES DATA ADAPTER
==============================================
try
{
// Build Ole types table
OleDbType[] oletypes = new OleDbType[Columns.Count];
for (int i=0;i<Columns.Count;i++) GetOleDbType(Columns[i],out oletypes[i]); // NB: types verified in Init()
// Create SelectCommand ============================================================
where = where.Trim();
string sortby = "";
string querystr = "SELECT * FROM "+TableName;
if (where.Length>0) querystr += " WHERE "+where;
if ((Object)primarykey!=null && primarykey.Count>0)
for (int i=0;i<primarykey.Count;i++)
{
sortby += primarykey[i];
if (i<primarykey.Count-1) sortby += ",";
}
if (sortby.Length>0) querystr += " ORDER BY "+sortby;
OleDbCommand SelectCommand = new OleDbCommand(querystr, (OleDbConnection)DataBase.DbCnx);
adapter = new OleDbDataAdapter();
((OleDbDataAdapter)adapter).SelectCommand = SelectCommand;
// Create InsertCommand ==============================================================
((OleDbDataAdapter)adapter).InsertCommand = new System.Data.OleDb.OleDbCommand();
((OleDbDataAdapter)adapter).InsertCommand.Connection = (OleDbConnection)DataBase.DbCnx;
string fieldsnames="",fieldvalues="";
for (int i=0;i<Columns.Count;i++)
{
fieldsnames += ""+Columns[i].ColumnName+""; fieldvalues += "?";
if (i<Columns.Count-1) { fieldsnames += ","; fieldvalues += ","; }
((OleDbDataAdapter)adapter).InsertCommand.Parameters.Add
(new OleDbParameter
(Columns[i].ColumnName,oletypes[i],0,ParameterDirection.Input,((byte)(0)),((byte)(0)),
Columns[i].ColumnName,System.Data.DataRowVersion.Current,false,null));
}
((OleDbDataAdapter)adapter).InsertCommand.CommandText =
@"INSERT INTO "+TableName+" ("+fieldsnames+") VALUES ("+fieldvalues+")";
((OleDbDataAdapter)adapter).InsertCommand.CommandType = System.Data.CommandType.Text;
// Create UpdateCommand ==============================================================
((OleDbDataAdapter)adapter).UpdateCommand = new System.Data.OleDb.OleDbCommand();
((OleDbDataAdapter)adapter).UpdateCommand.Connection = (OleDbConnection)DataBase.DbCnx;
string newfields="",oldfields="";
for (int i=0;i<Columns.Count;i++)
{
newfields += ""+Columns[i].ColumnName+"=?";
oldfields += "(("+Columns[i].ColumnName+"=?) OR (("+Columns[i].ColumnName+" IS NULL)AND(? IS NULL)))";
if (i<Columns.Count-1) { newfields += ","; oldfields += " AND "; }
((OleDbDataAdapter)adapter).UpdateCommand.Parameters.Add(new OleDbParameter
(Columns[i].ColumnName,oletypes[i],0,ParameterDirection.Input,((byte)(0)),((byte)(0)),
Columns[i].ColumnName,System.Data.DataRowVersion.Current,false,null));
}
for (int i=0;i<Columns.Count;i++) for(int j=1;j<=2;j++)
{
((OleDbDataAdapter)adapter).UpdateCommand.Parameters.Add(new OleDbParameter
("Original_"+j+"_"+Columns[i].ColumnName,oletypes[i],0,ParameterDirection.Input,((byte)(0)),((byte)(0)),
Columns[i].ColumnName,System.Data.DataRowVersion.Original,false,null));
}
((OleDbDataAdapter)adapter).UpdateCommand.CommandText =
@"UPDATE "+TableName+" SET "+newfields+" WHERE ("+oldfields+")";
((OleDbDataAdapter)adapter).UpdateCommand.CommandType = System.Data.CommandType.Text;
// Create DeleteCommand ==============================================================
((OleDbDataAdapter)adapter).DeleteCommand = new System.Data.OleDb.OleDbCommand();
((OleDbDataAdapter)adapter).DeleteCommand.Connection = (OleDbConnection)DataBase.DbCnx;
fieldsnames="";
for (int i=0;i<Columns.Count;i++)
{
fieldsnames += "(("+Columns[i].ColumnName+"=?) OR (("+Columns[i].ColumnName+" IS NULL)AND(? IS NULL)))";
if (i<Columns.Count-1) fieldsnames += "AND";
for(int j=1;j<=2;j++)
((OleDbDataAdapter)adapter).DeleteCommand.Parameters.Add
(new OleDbParameter
("Original"+j+"_"+Columns[i].ColumnName,oletypes[i],0,ParameterDirection.Input,((byte)(0)),((byte)(0)),
Columns[i].ColumnName,System.Data.DataRowVersion.Original,false,null));
}
((OleDbDataAdapter)adapter).DeleteCommand.CommandText =
@"DELETE FROM "+TableName+" WHERE ("+fieldsnames+")";
((OleDbDataAdapter)adapter).DeleteCommand.CommandType = System.Data.CommandType.Text;
}
catch (Exception e)
{
SxStrings.AddStringToTsl("Can not read from table "+TableName+"(Where="+where+") : '"+e.Message+"'",errors);
result = false;
}
break;
default :
result = false;
SxStrings.AddStringToTsl("Not processed table type!",errors);
break; |
Partager