/// <summary> /// 查詢資料庫內容,並存入指定的 DataTable 中 /// </summary> public DataTable GetDataTable(string sqlcmd) { var table = new DataTable(); try { rs = db.OpenRecordset(sqlcmd, DAO.RecordsetTypeEnum.dbOpenForwardOnly, DAO.RecordsetOptionEnum.dbReadOnly); int colSize = rs.Fields.Count; string[] data = new string[colSize]; for (int i = 0; i < colSize; i++) { 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; } return(table); }
/// <summary> /// 查詢資料庫內容,並存入指定的 List 中 /// </summary> public void ToList(bool clear, List <string> list, string sqlcmd) { try { rs = db.OpenRecordset(sqlcmd, DAO.RecordsetTypeEnum.dbOpenForwardOnly, DAO.RecordsetOptionEnum.dbReadOnly); while (!rs.EOF) { list.Add(rs.Fields[0].Value); rs.MoveNext(); } } catch (Exception e) { throw e; } }
/// <summary> /// 查詢資料庫內容,並存入指定的 ListBox 中 /// </summary> public void ToListBox(bool clear, System.Windows.Forms.ListBox listbox, string sqlcmd) { try { rs = db.OpenRecordset(sqlcmd, DAO.RecordsetTypeEnum.dbOpenForwardOnly, DAO.RecordsetOptionEnum.dbReadOnly); while (!rs.EOF) { listbox.Items.Add(rs.Fields[0].Value); rs.MoveNext(); } } catch (Exception e) { throw e; } }
/// <summary> /// 查詢資料庫內容,並存入指定的 Dictionary 中 /// </summary> public void ToDictionary(bool clear, Dictionary <string, string> dic, string sqlcmd) { try { rs = db.OpenRecordset(sqlcmd, DAO.RecordsetTypeEnum.dbOpenForwardOnly, DAO.RecordsetOptionEnum.dbReadOnly); while (!rs.EOF) { dic.Add(rs.Fields[0].Value, rs.Fields[1].Value); rs.MoveNext(); } } catch (Exception e) { throw e; } }
/// <summary> /// 取回 mdb 內 table 有設 Index 的 columns /// </summary> public List <string> GetTableIndexes(string _TableName) { List <string> list = new List <string>(); try { rs = db.OpenTable(_TableName).ListIndexes(); while (!rs.EOF) { list.Add(rs.Fields[0].Value); rs.MoveNext(); } } catch (Exception e) { throw e; } return(list); }
/// <summary> /// 傳回 SQL Select 指令第一個欄位所有資料的陣列 /// </summary> public List <string> GetList(string sqlcmd) { List <string> list = new List <string>(); try { rs = db.OpenRecordset(sqlcmd, DAO.RecordsetTypeEnum.dbOpenForwardOnly, DAO.RecordsetOptionEnum.dbReadOnly); while (!rs.EOF) { list.Add(rs.Fields[0].Value.ToString()); rs.MoveNext(); } } catch (Exception e) { throw e; } return(list); }
/// <summary> /// 查詢資料庫內容,並存入指定的 Dictionary 中 /// </summary> public Dictionary <string, string> GetDictionary(string sqlcmd) { Dictionary <string, string> dic = new Dictionary <string, string>(); try { rs = db.OpenRecordset(sqlcmd, DAO.RecordsetTypeEnum.dbOpenForwardOnly, DAO.RecordsetOptionEnum.dbReadOnly); while (!rs.EOF) { dic.Add(rs.Fields[0].Value, rs.Fields[1].Value); rs.MoveNext(); } } catch (Exception e) { throw e; } return(dic); }
/// <summary> /// 查詢資料庫內容,並存入指定的 List 中 /// </summary> public void ToList(bool clear, List <string>[] lists, string sqlcmd) { try { rs = db.OpenRecordset(sqlcmd, DAO.RecordsetTypeEnum.dbOpenForwardOnly, DAO.RecordsetOptionEnum.dbReadOnly); while (!rs.EOF) { for (int j = 0; j < lists.Length; j++) { lists[j].Add(rs.Fields[j].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); }
/// <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; } }