public List <LogEntry> LoadLog(string lastForeignID) { return(_readDB.WithReadDBConnection(conn => { var trans = conn.BeginTransaction(); try { using (System.Data.OleDb.OleDbCommand cmd = (System.Data.OleDb.OleDbCommand)conn.CreateCommand()) { ((System.Data.IDbCommand)cmd).Transaction = trans; long epoch = 1; long lastID = 0; DateTime lastDate = DateTime.MinValue; bool useDate = false; string[] s = lastForeignID.Split('-'); if (s.Length == 1) { epoch = 1; if (!long.TryParse(s[0], out lastID)) { useDate = true; } } else if (s.Length == 2) { if (!long.TryParse(s[0], out epoch)) { useDate = true; } if (!long.TryParse(s[1], out lastID)) { useDate = true; } } else if (s.Length == 3) { if (!long.TryParse(s[0], out epoch)) { useDate = true; } if (!long.TryParse(s[1], out lastID)) { useDate = true; } lastDate = DateTime.ParseExact(s[2], DateTimeFormat, null); } else { useDate = true; } if (useDate) { cmd.CommandText = "SELECT ID, Date, LogMessageCode, ResourceNumber, PartName, ProcessNumber," + "FixedQuantity, PalletNumber, ProgramNumber, FromPosition, ToPosition " + "FROM Log WHERE Date > ? ORDER BY ID ASC"; var param = cmd.CreateParameter(); param.OleDbType = System.Data.OleDb.OleDbType.Date; param.Value = DateTime.Now.AddDays(-7); cmd.Parameters.Add(param); } else { CheckIDRollover(trans, conn, ref epoch, ref lastID, lastDate); cmd.CommandText = "SELECT ID, Date, LogMessageCode, ResourceNumber, PartName, ProcessNumber," + "FixedQuantity, PalletNumber, ProgramNumber, FromPosition, ToPosition " + "FROM Log WHERE ID > ? ORDER BY ID ASC"; var param = cmd.CreateParameter(); param.OleDbType = System.Data.OleDb.OleDbType.Numeric; param.Value = lastID; cmd.Parameters.Add(param); } var ret = new List <LogEntry>(); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { if (reader.IsDBNull(0)) { continue; } if (reader.IsDBNull(1)) { continue; } if (reader.IsDBNull(2)) { continue; } if (!Enum.IsDefined(typeof(LogCode), reader.GetInt32(2))) { continue; } var e = new LogEntry(); e.ForeignID = epoch.ToString() + "-" + reader.GetInt32(0).ToString() + "-" + reader.GetDateTime(1).ToString(DateTimeFormat); e.TimeUTC = new DateTime(reader.GetDateTime(1).Ticks, DateTimeKind.Local); e.TimeUTC = e.TimeUTC.ToUniversalTime(); e.Code = (LogCode)reader.GetInt32(2); e.StationNumber = reader.IsDBNull(3) ? -1 : reader.GetInt32(3); e.FullPartName = reader.IsDBNull(4) ? "" : reader.GetString(4); e.Process = reader.IsDBNull(5) ? 1 : reader.GetInt32(5); e.FixedQuantity = reader.IsDBNull(6) ? 1 : reader.GetInt32(6); e.Pallet = reader.IsDBNull(7) ? -1 : reader.GetInt32(7); e.Program = reader.IsDBNull(8) ? "" : reader.GetInt32(8).ToString(); e.FromPosition = reader.IsDBNull(9) ? "" : reader.GetString(9); e.TargetPosition = reader.IsDBNull(10) ? "" : reader.GetString(10); int idx = e.FullPartName.IndexOf(':'); if (idx > 0) { e.JobPartName = e.FullPartName.Substring(0, idx); } else { e.JobPartName = e.FullPartName; } ret.Add(e); } } trans.Commit(); return ret; } } catch { trans.Rollback(); throw; } })); }
//driver download from:https://www.microsoft.com/en-us/download/details.aspx?id=13255 public static void Save(this System.Data.DataSet dataSet, string fileName = null, string tableName = null, IWin32Window owner = null) { if (string.IsNullOrEmpty(fileName)) { fileName = UI.FileIO.GetSaveFileName(owner, fileName ?? ".xls", "Comma Separated Values Files (*.csv)|*.csv|Microsoft Excel Files (*.xls)|*.xls|Microsoft Access Files (*.mdb)|*.mdb|All files (*.*)|*.*"); } if (fileName == null) { return; } string providerName = IntPtr.Size == 8 ? "Microsoft.ACE.OLEDB.12.0" : "Microsoft.Jet.OLEDB.4.0"; string providerProds = null; string tableNameFormat = "{0}"; string fileExtention = System.IO.Path.GetExtension(fileName).ToLower(); var dataSource = fileName; switch (fileExtention) { case ".csv": tableNameFormat = System.IO.Path.GetFileNameWithoutExtension(fileName) + "_{0}.csv"; dataSource = System.IO.Path.GetDirectoryName(fileName); providerProds = "text;HDR=YES;FMT=Delimited"; break; case ".xls": providerProds = "Excel 8.0;HDR=YES"; break; case ".mdb": providerProds = "Access 8.0;HDR=YES"; break; default: throw new Exception("unknown file extention"); } using (var con = new System.Data.OleDb.OleDbConnection()) { con.ConnectionString = "Provider=" + providerName + ";Data Source=" + dataSource + ";Extended Properties='" + providerProds + "'"; con.Open(); foreach (System.Data.DataTable t in dataSet.Tables) { if (tableName != null && tableName != t.TableName) { continue; } var exportName = string.Format(tableNameFormat, t.TableName); using (var command = new System.Data.OleDb.OleDbCommand(null, con)) { string sqlCreate = string.Format("Create Table {0} (", exportName); string sqlInsert = string.Format("Insert Into {0} (", exportName); string sqlInsertValues = "Values("; foreach (System.Data.DataColumn c in t.Columns) { var p = command.CreateParameter(); p.SourceColumn = c.ColumnName; command.Parameters.Add(p); string oleDbType = "Note"; if (c.DataType == typeof(int) | c.DataType == typeof(short) | c.DataType == typeof(byte)) { oleDbType = "LONG"; } sqlCreate += string.Format("[{0}] {1}, ", c.ColumnName, oleDbType); sqlInsert += string.Format("[{0}], ", c.ColumnName); sqlInsertValues += "?, "; } command.CommandText = sqlCreate.TrimEnd(' ', ',') + ")"; command.ExecuteNonQuery(); command.CommandText = sqlInsert.TrimEnd(' ', ',') + ")" + sqlInsertValues.TrimEnd(' ', ',') + ")"; using (var adp = new System.Data.OleDb.OleDbDataAdapter(null, con)) { if (t.TableName != exportName) { adp.TableMappings.Add(t.TableName, exportName); } adp.InsertCommand = command; adp.Update(t); adp.InsertCommand = null; } } } con.Close(); } }