/// <summary> /// 關閉連線. /// </summary> public void ConnClose() { try { if (rs != null) { rs.Close(); rs = null; } if (db != null) { db.Close(); db = null; } } catch (Exception e) { throw e; } }
/// <summary> /// 查詢資料庫內容,並存入指定的 Dictionary 中 /// </summary> public void ToDictionary(bool clear, Dictionary <string, Tuple <string, string> > dic, string sqlcmd) { try { if (clear) { dic.Clear(); } rs = db.OpenRecordset(sqlcmd, DAO.RecordsetTypeEnum.dbOpenForwardOnly, DAO.RecordsetOptionEnum.dbReadOnly); while (!rs.EOF) { dic.Add(rs.Fields[0].Value, new Tuple <string, string>(rs.Fields[1].Value, rs.Fields[2].Value)); rs.MoveNext(); } } catch (Exception e) { throw e; } }
/// <summary> /// 取回 mdb 內 table 有設 Index 的 columns /// </summary> public List <string> GetTableIndexes(string _TableName, string[] _Exclude) { List <string> list = new List <string>(); try { List <string> _ExcludeList = new List <string>(_Exclude); rs = db.OpenTable(_TableName).ListIndexes(); while (!rs.EOF) { string idxName = rs.Fields[0].Value.ToString(); if (!_ExcludeList.Contains(idxName)) { list.Add(idxName); } rs.MoveNext(); } } catch (Exception e) { throw e; } return(list); }
public static int BulkExportToAccess(DataTable dtOutData, String DBPath, String TableNm) { DAO.DBEngine dbEngine = new DAO.DBEngine(); Boolean CheckFl = false; int rowInserted = 0; try { DAO.Database db = dbEngine.OpenDatabase(DBPath); DAO.Recordset AccesssRecordset = db.OpenRecordset(TableNm); DAO.Field[] AccesssFields = new DAO.Field[dtOutData.Columns.Count]; //Loop on each row of dtOutData for (Int32 rowCounter = 0; rowCounter < dtOutData.Rows.Count; rowCounter++) { AccesssRecordset.AddNew(); //Loop on column for (Int32 colCounter = 0; colCounter < dtOutData.Columns.Count; colCounter++) { // for the first time... setup the field name. if (!CheckFl) { AccesssFields[colCounter] = AccesssRecordset.Fields[dtOutData.Columns[colCounter].ColumnName]; } AccesssFields[colCounter].Value = dtOutData.Rows[rowCounter][colCounter]; } AccesssRecordset.Update(); CheckFl = true; rowInserted = rowCounter; } AccesssRecordset.Close(); db.Close(); } finally { System.Runtime.InteropServices.Marshal.ReleaseComObject(dbEngine); dbEngine = null; } return(rowInserted); }
/// <summary> /// 查詢資料庫內容,並存入指定的 DataTable 中 /// </summary> public DataTable GetDataTable(string sqlcmd, string[] columnsExclude, int pageNumber, int rowCount, ref int allCount) { var table = new DataTable(); try { rs = db.OpenRecordset(sqlcmd, DAO.RecordsetTypeEnum.dbOpenForwardOnly, DAO.RecordsetOptionEnum.dbReadOnly); int colSize = rs.Fields.Count; for (int i = 0; i < colSize; i++) { if (!columnsExclude.Contains(rs.Fields[i].Name)) { table.Columns.Add(new DataColumn(rs.Fields[i].Name, typeof(string))); } } string[] data = new string[table.Columns.Count]; int idx = 0; int idxStart = (pageNumber * rowCount) - rowCount; int idxEnd = pageNumber * rowCount - 1; while (!rs.EOF) { if (idx >= idxStart && idx <= idxEnd) { for (int i = 0; i < table.Columns.Count; i++) { data[i] = string.Empty; data[i] = rs.Fields[table.Columns[i].ColumnName].Value.ToString(); } table.Rows.Add(data); } idx++; //if (idx++ >= idxEnd) break; rs.MoveNext(); } allCount = idx; } catch (Exception e) { throw e; } return(table); }
/// <summary> /// 查詢資料庫內容,並存入指定的 DataTable 中 /// </summary> public void ToDataTable(bool clear, DataTable table, string sqlcmd) { try { if (clear == true) { table.Clear(); } rs = db.OpenRecordset(sqlcmd, DAO.RecordsetTypeEnum.dbOpenForwardOnly, DAO.RecordsetOptionEnum.dbReadOnly); if (!rs.EOF) { int colSize = rs.Fields.Count; string[] data = new string[colSize]; for (int i = 0; i < colSize; i++) { if (!table.Columns.Contains(rs.Fields[i].Name)) { table.Columns.Add(new DataColumn(rs.Fields[i].Name, typeof(string))); } } while (!rs.EOF) { for (int i = 0; i < colSize; i++) { data[i] = string.Empty; data[i] = rs.Fields[i].Value.ToString(); } table.Rows.Add(data); rs.MoveNext(); } } } catch (Exception e) { throw e; } }