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