Beispiel #1
0
 /// <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; }
 }
Beispiel #2
0
 /// <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; }
 }
Beispiel #3
0
        /// <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);
        }
Beispiel #4
0
        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);
        }
Beispiel #5
0
        /// <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);
        }
Beispiel #6
0
        /// <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; }
        }