Expand|Select|Wrap|Line Numbers
- if (!DirectoryIsEmpty(DropZone.ToString()))
- {
- DirectoryInfo di = new DirectoryInfo(DropZone);
- FileInfo[] fi = di.GetFiles();
- // step through each found file
- foreach (FileInfo fiTemp in fi)
- {
- // Connection String to Excel Workbook
- string excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + DropZone + "\\" + fiTemp + "'; Extended Properties=\"Excel 8.0; HDR=YES; IMEX=1;\"";
- string SheetName = string.Empty;
- // Create Connection to Excel Workbook
- using (OleDbConnection connection = new OleDbConnection(excelConnStr))
- {
- try
- {
- string curaddy2 = string.Empty;
- string priaddy2 = string.Empty;
- connection.Open();
- DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
- if (dt != null)
- {
- string[] excelSheets = new String[dt.Rows.Count];
- int i = 0;
- foreach (DataRow row in dt.Rows)
- {
- excelSheets[i] = row["TABLE_NAME"].ToString();
- #region ExcelReading
- SheetName = excelSheets[i].ToString();
- OleDbCommand command = new OleDbCommand("SELECT [Name], [Address], [City], [State], [Zip] FROM '" + SheetName.ToString() + "'", connection);
- using (DbDataReader dr = command.ExecuteReader())
- {
- // SQL Server Connection String
- string sqlConnectionString = DBUtils.DBString;
- // Bulk Copy to SQL Server
- using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
- {
- // Columns get copied from sheet and into bulk copy statement
- logger.MyLogFile("" + DateTime.Now.ToString() + " Bulk Copy: ", "ID Number " + dr.GetValue(0).ToString() + "");
- // now that matrix is created, do the insert
- bulkCopy.DestinationTableName = "Table";
- bulkCopy.WriteToServer(dr);
- logger.MyLogFile("" + DateTime.Now.ToString() + " Bulk Copy: ", "All values inserted as burst");
- }
- }
- #endregion
- i++;
- }
- }
- dt.Dispose();
- }
- catch (Exception ex)
- {
- logger.MyLogFile("" + DateTime.Now.ToString() + " Importer \n", "Import failed, error code: " + ex.ToString() + "");
- }
- finally
- {
- connection.Close();
- }
- }
- }