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
| public static string getInsertsTable(string table, int idxChampNumAuto, bool faireDelete, ref int nb) { // Si idxChampNumAuto<=0, alors on prend tous les champs
string ret = "";
if (faireDelete)
ret = "DELETE FROM " + table + ";" + Environment.NewLine;
DataSet ds = GetDs("SELECT * FROM " + table, dsName); // Remplir le dataset
// Préparer le masque de la requête avec le nom des colonnes
string sep = "";
string strTmp = "INSERT INTO " + table + " (";
for (int i = 0; i<ds.Tables[0].Columns.Count; i++) {
if (i+1 != idxChampNumAuto) {
strTmp += sep + "[" + ds.Tables[0].Columns[i].ColumnName + "]";
sep = ",";
}
}
strTmp += ") VALUES ([VALEURS]);" + Environment.NewLine;
// Aller chercher les valeurs de la table
for (int i= 0; i < ds.Tables[0].Rows.Count; i++) {
string strTmp2 = "";
sep = "";
for (int j = 0; j<ds.Tables[0].Columns.Count; j++) {
if (j+1 != idxChampNumAuto) {
object leType = ds.Tables[0].Rows[i][j].GetType();
if ((leType == typeof(System.String))) { // Texte: Encadrés de guillemets
strTmp2 += sep + "N'" + ds.Tables[0].Rows[i][j].ToString().Replace("'", "''").Replace("\r", "").Replace("\n", " ") + "'";
} else if ((leType == typeof(System.DateTime))) { // Date: Cast
//DateTime d = new DateTime();
strTmp2 += sep + "CONVERT(DATETIME, '" + ((DateTime)ds.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd") + "', 102)";
} else if ((leType == typeof(System.Int32)) || (leType == typeof(System.Boolean))) { // Numérique: Pas encadrés de guillemets
strTmp2 += sep + "" + ds.Tables[0].Rows[i][j].ToString() + "";
} else if (leType == typeof(System.DBNull)) { // NULL
strTmp2 += sep + "NULL";
} else
strTmp2 += sep + "(" + leType + ")" + ds.Tables[0].Rows[i][j].ToString() + "";
sep = ",";
}
}
ret += strTmp.Replace("[VALEURS]", strTmp2);
}
nb += ds.Tables[0].Rows.Count;
return ret + Environment.NewLine;
} |
Partager