private string ConstructIndividualValue(string dataType, string value) { switch (dataType) { case "BIT": return(value); //Numbers case "INT": case "TINYINT": case "SMALLINT": case "MEDIUMINT": case "BIGINT": case "FLOAT": case "DOUBLE": case "DECIMAL": return(string.Format("{0}", value)); //Text case "CHAR": case "VARCHAR": case "BLOB": case "TEXT": case "TINYBLOB": case "TINYTEXT": case "MEDIUMBLOB": case "MEDIUMTEXT": case "LONGBLOB": case "LONGTEXT": case "ENUM": return(string.Format("'{0}'", MySqlHelper.EscapeString(value))); //Dates/times case "DATE": return(String.Format("'{0:yyyy-MM-dd}'", (DateTime)DateTimeDecider.Parse(value))); case "TIMESTAMP": case "DATETIME": return(String.Format("'{0:yyyy-MM-dd HH:mm:ss}'", (DateTime)DateTimeDecider.Parse(value))); case "TIME": return(String.Format("'{0:HH:mm:ss}'", (DateTime)DateTimeDecider.Parse(value))); case "YEAR2": return(String.Format("'{0:yy}'", value)); case "YEAR4": return(String.Format("'{0:yyyy}'", value)); //Unknown default: // we don't understand the format. to safegaurd the code, just enclose with '' return(string.Format("'{0}'", MySqlHelper.EscapeString(value))); } }
/// <summary> /// Replaces all string representations destined to end up in DateTime or TimeSpan fields in the target database /// into hard typed objects (using <see cref="DateTimeDecider"/>) /// </summary> /// <param name="dt"></param> protected void ConvertStringDatesToDateTime(DataTable dt) { var dict = GetMapping(dt.Columns.Cast <DataColumn>(), out _); //for each column in the destination foreach (var kvp in dict) { //if the destination column is a date based column var dataType = kvp.Value.DataType.GetCSharpDataType(); if (dataType == typeof(DateTime) || dataType == typeof(TimeSpan)) { //if it's already not a string then that's fine (hopefully its a DateTime!) if (kvp.Key.DataType != typeof(string)) { continue; } //create a new column hard typed to DateTime var newColumn = dt.Columns.Add(kvp.Key.ColumnName + "_" + Guid.NewGuid().ToString(), dataType); //guess the DateTime culture based on values in the table DateTimeDecider.GuessDateFormat(dt.Rows.Cast <DataRow>().Take(500).Select(r => r[kvp.Key] as string)); foreach (DataRow dr in dt.Rows) { //parse the value var val = DateTimeDecider.Parse(dr[kvp.Key] as string) ?? DBNull.Value; if (dataType == typeof(DateTime)) { dr[newColumn] = val; } else { dr[newColumn] = val == DBNull.Value? val:((DateTime)val).TimeOfDay; } } //if the DataColumn is part of the Primary Key of the DataTable (in memory) //then we need to update the primary key to include the new column not the old one if (dt.PrimaryKey != null && dt.PrimaryKey.Contains(kvp.Key)) { dt.PrimaryKey = dt.PrimaryKey.Except(new [] { kvp.Key }).Union(new [] { newColumn }).ToArray(); } //drop the original column dt.Columns.Remove(kvp.Key); //rename the hard typed column to match the old column name newColumn.ColumnName = kvp.Key.ColumnName; } } }
public override int UploadImpl(DataTable dt) { //don't run an insert if there are 0 rows if (dt.Rows.Count == 0) { return(0); } var syntaxHelper = _server.GetQuerySyntaxHelper(); var tt = syntaxHelper.TypeTranslater; //if the column name is a reserved keyword e.g. "Comment" we need to give it a new name Dictionary <DataColumn, string> parameterNames = syntaxHelper.GetParameterNamesFor(dt.Columns.Cast <DataColumn>().ToArray(), c => c.ColumnName); int affectedRows = 0; var mapping = GetMapping(dt.Columns.Cast <DataColumn>()); var dateColumns = new HashSet <DataColumn>(); var sql = string.Format("INSERT INTO " + TargetTable.GetFullyQualifiedName() + "({0}) VALUES ({1})", string.Join(",", mapping.Values.Select(c => '"' + c.GetRuntimeName() + '"')), string.Join(",", mapping.Keys.Select(c => parameterNames[c])) ); using (OracleCommand cmd = (OracleCommand)_server.GetCommand(sql, Connection)) { //send all the data at once cmd.ArrayBindCount = dt.Rows.Count; foreach (var kvp in mapping) { var p = _server.AddParameterWithValueToCommand(parameterNames[kvp.Key], cmd, DBNull.Value); p.DbType = tt.GetDbTypeForSQLDBType(kvp.Value.DataType.SQLType); if (p.DbType == DbType.DateTime) { dateColumns.Add(kvp.Key); } } var values = new Dictionary <DataColumn, List <object> >(); foreach (DataColumn c in mapping.Keys) { values.Add(c, new List <object>()); } foreach (DataRow dataRow in dt.Rows) { //populate parameters for current row foreach (var col in mapping.Keys) { var val = dataRow[col]; if (val is string && string.IsNullOrWhiteSpace((string)val)) { val = null; } else if (val == null || val == DBNull.Value) { val = null; } else if (dateColumns.Contains(col)) { if (val is string s) { val = (DateTime)DateTimeDecider.Parse(s); } else { val = Convert.ToDateTime(dataRow[col]); } } values[col].Add(val); } } foreach (DataColumn col in mapping.Keys) { var param = cmd.Parameters[parameterNames[col]]; param.Value = values[col].ToArray(); } //send query affectedRows += cmd.ExecuteNonQuery(); } return(affectedRows); }