Browser Database History ( Viewing Browser History and System History using SQLite in C# Windows Application )

Browser Database History ( Viewing Browser History and System History using SQLite in C# Windows Application )

   5:26 PM       Marianz

---

public void GetDBTables(string databasePath, string filename)
{
    SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + databasePath);
    try
    {
        conn.Open();
        SQLiteCommand cmd = new SQLiteCommand();
        cmd.Connection = conn;
        string[] restrictions = new string[4];
        restrictions[3] = "Table";
        DataTable tables = conn.GetSchema("Tables", restrictions);
        conn.Close();
        treeViewTables.Node tableNodes = new treeViewTables.Node(filename.Replace(".sqlite", " "));
        tableNodes.Name = "mdbNodes";
        tableNodes.ImageIndex = treeIndex;
        for (int i = 0; i < tables.Rows.Count; i++)
        {
            treeViewTables.Node dbTables = new treeViewTables.Node(tables.Rows[i][2].ToString().ToUpper());
            dbTables.ImageIndex = treeIndex;ex
            tableNodes.Nodes.Add(dbTables);
        }
        advTreeMDB.Nodes.Add(tableNodes);
        lblTableCount.Text = tables.Rows.Count + " Tables";
        mdbFile = databasePath;
    }
    catch (Exception ex)
    {
        if (ex.Message.Contains("Not a valid password."))
        {
            Password pwd = new Password();
            if (pwd.ShowDialog() == DialogResult.OK)
            {
                dbPwd = Password.dataPassword;
                GetDBTables(databasePath, filename);
            }            
        }
        return;
    }
}
private void GetColumns(string databasePath, string tables, string dbPwd)
{
    lstViewHistory.Clear();
    List<String> colList = new List<String>();
    string priKey = "";
    try
    {
        SQLiteConnection sqliteConn = new SQLiteConnection(@"Data Source=" + databasePath);
        sqliteConn.Open();
        SQLiteDataReader reader;
        SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM " + tables, sqliteConn);
        reader = cmd.ExecuteReader();
        
        //Create schemaTable
        DataTable schemaTable = reader.GetSchemaTable();
        for (int i = 0; i < schemaTable.Rows.Count; i++)
        {
            lstViewHistory.Columns.Add(schemaTable.Rows[i][0].ToString().ToUpper(), 100);
            colList.Add(schemaTable.Rows[i][0].ToString());
        }
        lblColumnCount.Text = schemaTable.Rows.Count + " Columns";
    }
    catch
    {
        <???????>
        return;
    }
    GetColumnsItems(databasePath, priKey, tables, colList, dbPwd);
}
private void GetColumnsItems(string databasePath, string PrimaryKey, string table, List<string> columns, string dbPwd)
{
    SQLiteConnection sqliteConn = new SQLiteConnection(@"Data Source=" + databasePath);
    sqliteConn.Open();
    SQLiteCommand com = new SQLiteCommand("Select * From " + table, sqliteConn);
    SQLiteDataReader dr = com.ExecuteReader();
    int i = 1;
    while (dr.Read())
    {
        ListViewItem lv = new ListViewItem(dr[0].ToString());
        lv.ImageIndex = imgIndex;
        for (int col = 1; col < columns.Count; col++)
        {
            lv.SubItems.Add(dr[col].ToString());
        }
        if (lstViewHistory.Items.Count % 2 != 0)
            lv.BackColor = Color.White;
        else
            lv.BackColor = Color.WhiteSmoke;
        lstViewHistory.Items.AddRange(new ListViewItem[] { lv });
        lblRowCount.Text = i + " Rows";
        i++;
    }
}

Read More ...

CATE ( Converting Access To Excel using C# Windows Application)

CATE ( Converting Access To Excel using C# Windows Application)

   5:09 PM       Marianz

---

This code snippet is used in converting access(.mdb,.accdb) to excel(.xls,xlsx);

int row = 1;
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
 
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
 
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Columns.EntireColumn.NumberFormat = "@";
xlWorkSheet.Columns.NumberFormat = "@";
 
xlWorkSheet.Columns.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Columns.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter
 
OleDbConnection oledbConn = new OleDbConnection(mdbConn);
oledbConn.Open();
OleDbCommand thisCommand = oledbConn.CreateCommand();
thisCommand.CommandText = "SELECT " + mdbColumns.Remove(mdbColumns.Length - 2) + " FROM " + mdbTable + "";
OleDbDataReader dr = thisCommand.ExecuteReader();
while (dr.Read())
{
    row++;
    int col = 1;
    foreach (string listColumn in columnList)
    {
    ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[row, col]).Value2 = dr[listColumn].ToString();
    col++;
    }
bgWorker.ReportProgress(row);
}
 
dr.Close();
oledbConn.Close();
xlWorkBook.SaveAs(xlsFile + "\\" + xlsfilename + ".xls", 
    XlFileFormat.xlWorkbookNormal, misValue,
    misValue, misValue, misValue, 
    XlSaveAsAccessMode.xlExclusive,
    misValue, misValue, misValue, 
    misValue, misValue);
    
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
 
ImPixObject.ReleaseObject(xlWorkSheet);
ImPixObject.ReleaseObject(xlWorkBook);
ImPixObject.ReleaseObject(xlApp);

Read More ...