/// <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; }
}