public (DataTable Table, bool Success) ReaDataTable(string pFileName, string pValue) { mHasException = false; var dt = new DataTable(); var selectStatement = "SELECT * FROM [Customers$] WHERE ContactTitle = @ContactTitle"; var con = new SmartConnection(); try { using (var cn = new OleDbConnection { ConnectionString = con.ConnectionString(pFileName, 1, ExcelHeader.Yes) }) { using (var cmd = new OleDbCommand { Connection = cn, CommandText = selectStatement }) { cmd.Parameters.AddWithValue("ContactName", pValue); cn.Open(); dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection) ?? throw new InvalidOperationException()); } } } catch (Exception e) { mHasException = true; } return(dt, IsSuccessFul); }
public DataTable ReaDataTable(string pFileName) { var dt = new DataTable(); var selectStatement = "SELECT * FROM [Customers$]"; var con = new SmartConnection(); try { using (var cn = new OleDbConnection { ConnectionString = con.ConnectionString(pFileName, 1, ExcelHeader.Yes) }) { using (var cmd = new OleDbCommand { Connection = cn, CommandText = selectStatement }) { cn.Open(); dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection) ?? throw new InvalidOperationException()); } } } catch (OleDbException oe) { // isolate specific exception and record to log } catch (Exception e) { // record exception to log of some sort } return(dt); }
/// <summary> /// Get WorkSheet names for a specific Excel file /// </summary> /// <param name="pFileName"></param> /// <returns></returns> public List <string> SheetNames(string pFileName) { mHasException = false; var names = new List <string>(); var con = new SmartConnection(); try { using (var cn = new OleDbConnection(con.ConnectionString(pFileName))) { cn.Open(); var dt = cn.GetSchema("Tables", new string[] { null, null, null, "Table" }); foreach (DataRow row in dt.Rows) { names.Add(row.Field <string>("Table_Name").Replace("$", "")); } } } catch (Exception ex) { mHasException = true; mLastException = ex; } return(names.Distinct().ToList()); }
/// <summary> /// Uses a DataReader to create a List of Customer /// </summary> /// <param name="pFileName"></param> /// <returns></returns> public List <Customer> ReadCustomers(string pFileName) { mHasException = false; var selectStatement = "SELECT CompanyName, ContactName,ContactTitle FROM [Customers$]"; List <Customer> customers = new List <Customer>(); var con = new SmartConnection(); try { using (var cn = new OleDbConnection { ConnectionString = con.ConnectionString(pFileName, 1, ExcelHeader.Yes) }) { using (var cmd = new OleDbCommand { Connection = cn, CommandText = selectStatement }) { cn.Open(); var reader = cmd.ExecuteReader(); while (reader != null && reader.Read()) { customers.Add(new Customer() { CompanyName = reader.GetString(0), ContactName = reader.GetString(1), ContactTitle = reader.GetString(2) }); } } } } catch (Exception ex) { mHasException = true; mLastException = ex; } return(customers); }
/// <summary> /// Simple example for importing a comma delimited text file /// </summary> /// <param name="pExcelFileName">Existing Excel file to import into</param> /// <param name="pTextFile">Source comma delimited text file</param> /// <param name="pSheetName">Worksheet to create and import text into</param> /// <returns></returns> /// <remarks> /// The database in this case is the directory /// </remarks> public bool ImportFromTextFile(string pExcelFileName, string pTextFile, string pSheetName) { mHasException = false; var result = true; var con = new SmartConnection(); using (var cn = new OleDbConnection() { ConnectionString = con.ConnectionStringExporter(pExcelFileName, 0) }) { var fileName = Path.GetFileName(pTextFile); var sql = $"SELECT * INTO [{pSheetName}] FROM [Text;" + $"DATABASE={Path.Combine(AppDomain.CurrentDomain.BaseDirectory)}].[{fileName}]"; using (var cmd = new OleDbCommand(sql, cn)) { try { cn.Open(); cmd.ExecuteNonQuery(); } catch (Exception ex) { mHasException = true; mLastException = ex; result = false; } } } return(result); }
/// <summary> /// This is an exercise in reading data via a data reader where we have /// the following columns, id known to be int, next two columns strings, /// next column int and finally a date. We attempt to read the cells /// using those data types but fail with runtime exceptions on invalid /// data types on conversion but are not told which ones failed yet easy /// to figure it’s the numeric cells. Rather than attempt to try different /// cast we load data into a DataTable and query the column data types via /// console write line. Once we know that we can properly cast the cells. /// /// From here we remove the code for the DataTable and are good to go. /// /// CAVEAT /// The sheet read has data for the first row, not column names. When /// this is done each column begins with "F" followed by a numeric. So /// Column 1 is F1, Column 2 is F3 etc. If we wanted to use a DataTable /// to display the data that is not appealing so as shown below we can /// alias the Fn names. /// /// /// /// var ops = new OledbCode.Operations(); /// var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "PeopleNoHeader.xlsx"); /// var sheetName = "People"; /// ops.ReadPeopleNoHeaderRow(fileName,sheetName); /// /// </summary> /// <param name="pFileName"></param> /// <param name="pSheetName"></param> public void ReadPeopleNoHeaderRow(string pFileName, string pSheetName) { mHasException = false; List <Person> peopleList = new List <Person>(); var con = new SmartConnection(); var dt = new DataTable(); try { using (var cn = new OleDbConnection { ConnectionString = con.ConnectionString(pFileName, 1, ExcelHeader.No) }) { var selectStatement = "SELECT F1 AS Identifer, F2 AS FirstName, F3 As LastName, " + "F4 AS Gender, F5 As BirthDate " + $"FROM [{pSheetName}$]"; using (var cmd = new OleDbCommand { Connection = cn, CommandText = selectStatement }) { cn.Open(); var reader = cmd.ExecuteReader(); dt.Load(reader); reader.Close(); reader = cmd.ExecuteReader(); while (reader != null && reader.Read()) { peopleList.Add(new Person() { Id = Convert.ToInt32(reader.GetDouble(0)), FirstName = reader.GetString(1), LastName = reader.GetString(2), Gender = Convert.ToInt32(reader.GetDouble(3)), BirthDay = Convert.ToDateTime(reader.GetString(4)) }); } if (reader != null) { reader.Close(); } } } } catch (Exception ex) { mHasException = true; mLastException = ex; } foreach (DataColumn col in dt.Columns) { Console.WriteLine(col.DataType.ToString()); } }
/// <summary> /// Creates a new WorkSheet, populates the WorkSheet /// with data from a DataTable. What is not covered here /// is when a column name has spaces in a name. If that is /// the case then those columns need to be wrapped in [] /// where the simple method would be to wrap each and every /// column with brackets rather than trying to single out /// column names with spaces. /// </summary> /// <param name="pFileName">Existing Excel file</param> /// <param name="pDataTable">DataTable used to populate a new WorkSheet</param> /// <remarks> /// The intent here was to make everything very dynamic so that this method can /// be used for any file. As stated above this does not handle column names /// with spaces or column names that are reserve words for OleDb or Excel /// </remarks> public void ExportDataTableToExcel(string pFileName, DataTable pDataTable) { mHasException = false; /* * Create field names for the create table (worksheet) */ var columnsPartsForCreateSheet = pDataTable.Columns.OfType <DataColumn>() .Select(col => $"{col.ColumnName.Replace("Column", "")} CHAR(255)") .ToArray(); /* * Turn column name and type into a delimited string for the actual create statement * below. */ var columnsForCreateSheet = string.Join(",", columnsPartsForCreateSheet); /* * Full SQL INSERT statement */ var createStatement = $"CREATE TABLE {pDataTable.TableName} ({columnsForCreateSheet})"; /* * Column names for the INSERT SQL staetment. */ var columnsPartsForInsert = pDataTable.Columns.OfType <DataColumn>() .Select(col => col.ColumnName.Replace("Column", "")) .ToArray(); /* * Create named parameters for the insert statement. Note that OleDb * does not 'use' named parameters so we could had used a question mark * for each parameter name as OleDb parameters are done in ordinal position * which the parameters are anyways. The idea is for developers to get * use to named parameters as when moving to SQL-Server named parameters * allow parameters to be set out of order */ var paramsForInsert = pDataTable.Columns.OfType <DataColumn>() .Select(col => "@" + col.ColumnName.Replace("Column", "")) .ToArray(); /* * Insert statement for populating rows in the WorkSheet. */ var insertStatement = $"INSERT INTO {pDataTable.TableName} ({string.Join(",", columnsPartsForInsert)}) VALUES ({string.Join(",", paramsForInsert)})"; try { var con = new SmartConnection(); /* * IMPORTANT: In the connection string, second parameter must be IMEX = 0 for this to work. */ using (var cn = new OleDbConnection(con.ConnectionString(pFileName, 0, ExcelHeader.Yes))) { using (var cmd = new OleDbCommand { Connection = cn }) { cmd.CommandText = createStatement; cn.Open(); /* * Create the WorkSheet */ cmd.ExecuteNonQuery(); /* * Change our commandText for the INSERT */ cmd.CommandText = insertStatement; /* * Create parameters once rather than creating them for each * iteration of an insert, clearing or re-creating the parameters. */ foreach (var pName in paramsForInsert) { cmd.Parameters.Add(pName, OleDbType.VarWChar); } /* * Insert row into the WorkSheet. */ for (int rowIndex = 0; rowIndex < pDataTable.Rows.Count; rowIndex++) { for (int colIndex = 0; colIndex < pDataTable.Columns.Count; colIndex++) { /* * Set each parameter's value */ cmd.Parameters[colIndex].Value = pDataTable.Rows[rowIndex].Field <string>(pDataTable.Columns.IndexOf(pDataTable.Columns[colIndex])); } cmd.ExecuteNonQuery(); } } } } catch (Exception e) { mHasException = true; mLastException = e; } }