/// <summary> /// /// </summary> /// <param name="sourceConnection"></param> /// <param name="destinationConnection"></param> public MSDBIntegration(String sourceConnection = null, String destinationConnection = null) { if (sourceConnection != null) { SourceConnection = new MSSQL2005_DBConnection(sourceConnection); } if (destinationConnection != null) { DestinationConnection = new MSSQL2005_DBConnection(destinationConnection); } }
public MSDBIntegration(DBConnectionDelegate sourceConnection = null, DBConnectionDelegate destinationConnection = null) { if (sourceConnection != null) { SourceConnection = sourceConnection; } if (destinationConnection != null) { DestinationConnection = destinationConnection; } }
/// <summary> /// Read the Csv File and fill the Table on the given Connection /// </summary> /// <param name="connection"></param> /// <param name="dataTableSchema"></param> /// <param name="dataTableName">if not exists, create the table with collate option</param> /// <param name="tableCollation">by default, Latin1_General_CP1</param> /// <returns></returns> public int Fill(DBConnectionDelegate connection, DatabaseTable dataTableName, string date_format = "dd/MM/yyyy", string tableCollation = "COLLATE SQL_Latin1_General_CP1_CI_AS") { System.Diagnostics.Contracts.Contract.Assert(connection != null, "Connection must be setted"); string createDataTableRequest = "create TABLE [{0}].[{1}] ({2})"; string createDataTableColumns = null; string insertRequest = "insert into [{0}].[{1}] ({2}) VALUES ({3})"; string insertRequestParameters = null; string[] fieldHeaders = reader.GetFieldHeaders(); // intersept the file hearders with the columns name of the destination table Dictionary <string, ColumnSpec> databaseColumns = connection.GetColumnsSpec(dataTableName); IEnumerable <string> databaseColumnsName = databaseColumns.Keys.AsEnumerable <string>(); IEnumerable <string> headers; if (databaseColumnsName.Count <string>() > 0) { headers = Enumerable.Intersect <string>(fieldHeaders.AsEnumerable <string>(), databaseColumnsName); } else { headers = fieldHeaders.AsEnumerable <string>(); } int fieldCount = headers.Count <string>(); int[] headersMaxWidth = new int[fieldCount]; List <SqlParameter[]> wholeParams = new List <SqlParameter[]>(); // TODO pour createDataTableColumns, prevoir une adaptation, car si il y a un objet mapping, create des champs typés, et pas tout le temps int i = 0; foreach (string h in headers) { if (createDataTableColumns == null) { createDataTableColumns = h + " NVARCHAR({" + i + "}) " + tableCollation; } else { createDataTableColumns += ", " + h + " NVARCHAR({" + i + "}) " + tableCollation; } if (insertRequestParameters == null) { insertRequestParameters = "{0}" + h; } else { insertRequestParameters += ", {0}" + h; } i++; } // Type Management: Each parameter is typed using the destination database columns type. // int nbRows = 0; while (reader.ReadNextRecord()) { bool emptyRecord = true; SqlParameter[] entries = new SqlParameter[fieldCount]; i = 0; foreach (string h in headers) { SqlParameter p; // Get the DbType ColumnSpec spec = databaseColumns[h]; string fieldContent = reader[h]; int width = fieldContent.Length; if (width > 0) { emptyRecord = false; if (headersMaxWidth[i] < width) { headersMaxWidth[i] = width; } if (spec == null) {// The parameter is in nvarchar p = new SqlParameter(h, fieldContent); } else if (spec.isSQLCharType) { p = new SqlParameter(h, spec.Type, spec.MaximumLength); p.Value = fieldContent; } else if (spec.Type.Equals(SqlDbType.Float) || spec.Type.Equals(SqlDbType.Decimal)) { p = new SqlParameter(h, spec.Type, spec.MaximumLength); double res; // If type is float, but impossible to parse => Null Value if (Double.TryParse(fieldContent, NumberStyles.Float | NumberStyles.Number, CultureInfo.InvariantCulture, out res)) { p.Value = res; } else { p.Value = DBNull.Value; } } else if (spec.Type.Equals(SqlDbType.DateTime) || spec.Type.Equals(SqlDbType.DateTime2)) { p = new SqlParameter(h, spec.Type); DateTime res; // If type is datetime, but impossible to parse => Null Value if (DateTime.TryParseExact(fieldContent, date_format, CultureInfo.InvariantCulture, DateTimeStyles.None, out res)) { p.Value = res; } else { p.Value = DBNull.Value; } } else { p = new SqlParameter(h, spec.Type, spec.MaximumLength); p.Value = fieldContent; } } else { p = new SqlParameter(h, spec.Type, spec.MaximumLength); p.Value = DBNull.Value; } entries[i] = p; i++; } if (!emptyRecord) { wholeParams.Add(entries); nbRows++; } } // Execution SQL // creation de la table destination if (!connection.isTableExist(dataTableName)) { string[] values = headersMaxWidth.Select(x => x > 0 ? x.ToString() : "1").ToArray(); createDataTableColumns = String.Format(createDataTableColumns, values); createDataTableRequest = String.Format(createDataTableRequest, dataTableName.schema, dataTableName.table, createDataTableColumns); connection.Execute(createDataTableRequest); } // effectuer les Insertions string insertParameters1 = String.Format(insertRequestParameters, ' '); string insertParameters2 = String.Format(insertRequestParameters, '@'); insertRequest = String.Format(insertRequest, dataTableName.schema, dataTableName.table, insertParameters1, insertParameters2); IDbCommand command = new SqlCommand(insertRequest); for (i = 0; i < nbRows; i++) { IDataParameter[] p = wholeParams[i]; connection.Execute(ref command, p); } return(nbRows); }