CODEWITHSUNDEEP
c#
user150224
user150224

Reputation:

how to get tables of an access db into a list box using c#?

i needed to create a form in which i hav to browse and open mdb files ---> i did this part usin oprnfile dialogue!

private void button1_Click(object sender, EventArgs e)

{

OpenFileDialog oDlg = new OpenFileDialog();

oDlg.Title = "Select MDB";

oDlg.Filter = "MDB (*.Mdb)|*.mdb";

oDlg.RestoreDirectory = true;

string dir = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);

oDlg.InitialDirectory = dir;

DialogResult result = oDlg.ShowDialog();

if (result == DialogResult.OK)

{

textBox1.Text = oDlg.FileName.ToString();

}

}

**this is my code so far!!!

now i need to make 3 list boxes!! 1st one to display the table names of the db! 2nd to to display field names when clicked on table name!!! 3rd to display attributes on fiels on clickin on it! v can edit the attribute values and on clickin of save button it should update the database!!!

Upvotes: 0

Views: 4150

Answers (3)

Tester101
Tester101

Reputation: 8172

This class should get you the information you need. 

public static class DatabaseInfoCollector
    {       
        public static System.Collections.Generic.List<string> GetTables(string file)
        {
            System.Data.DataTable tables;
            using(System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file))
            {
                connection.Open();
                tables = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"});
            }
            System.Collections.Generic.List<string> Tables = new System.Collections.Generic.List<string>();
            for (int i = 0; i < tables.Rows.Count; i++)
            {
                Tables.Add(tables.Rows[i][2].ToString());
            }
            return Tables;
        }       

        public static System.Collections.Generic.List<string> GetColumnNames(string file, string table)
        {
            System.Data.DataTable dataSet = new System.Data.DataTable();            
            using(System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file))
            {
                connection.Open();
                System.Data.OleDb.OleDbCommand Command = new System.Data.OleDb.OleDbCommand("SELECT * FROM " + table,connection);
                using(System.Data.OleDb.OleDbDataAdapter dataAdapter = new System.Data.OleDb.OleDbDataAdapter(Command))
                {
                    dataAdapter.Fill(dataSet);
                }
            }
            System.Collections.Generic.List<string> columns = new System.Collections.Generic.List<string>();
            for(int i = 0; i < dataSet.Columns.Count; i ++)
            {
                columns.Add(dataSet.Columns[i].ColumnName);
            }
            return columns;
        }
    }

Fill the tables list like this.

System.Collections.Generic.List<string> Tables = DatabaseInfoCollector.GetTables(textBox1.Text);
foreach(string table in Tables)
{
     cboTable.Items.Add(table);
}

Fill the columns like this.

System.Collections.Generic.List<string> Columns = DatabaseInfoCollector.GetColumnNames(textBox1.Text,cboTable.SelectedItem.ToString());
foreach(string column in Columns)
{
     cboColumns.Items.Add(column);
}

You can also use this method to return a DataTable containing all kinds of information about each column.

public static System.Data.DataTable GetSchemaData(string file)
{
    System.Data.DataTable columns;
    using(System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file))
    {
        connection.Open();
        columns = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns,new object[]{null,null,null,null});
    }
    return columns;
}

Upvotes: 5

jackkav
jackkav

Reputation: 582

Use System.Data.OleDb to open a connection with new OleDbConnection(connectionString). The connection string should be "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[PathToMDBFile]"

var conn = new OleDbConnection(connectionString);
 var ds = new DataSet();
                var adapter = new OleDbDataAdapter("SELECT Column1 FROM Table1", conn);
                conn.Open();
                adapter.Fill(ds);
                conn.Close();
var value = ds.Tables[0].Rows[0]["Column1"].ToString();

That will get you the first value in the column named Column1

Upvotes: 0

Stefan Haubold
Stefan Haubold

Reputation: 666

Use the System.Data.OleDb.* classes to get the data from the access file.

Example:

//Create the OleDbConnection object 
//and associate it with our database
using(OleDbConnection conn = new OleDbConnection(
    "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source="+textBox1.Text)){

//Open the database connection
conn.Open();

//Create an OleDbCommand object and
//pass it the SQL read query and the connection to use
OleDbCommand cmd = new OleDbCommand(sqlstr,conn);

//Procure the OleDbDataReader object to browse the recordset 
OleDbDataReader rdr = cmd.ExecuteReader();

//Keep reading records in the forward direction
while (rdr.Read())
{
   //Use one of the various methods available to read the data
   //Eg:- GetValue, GetValues, Item etc.
. . .
. . .    
}

}

Upvotes: 0

Related Questions