CODEWITHSUNDEEP
c#.netdatatable
Mert Metin
Mert Metin

Reputation: 411

C# Database connection

I have a homework about database connection via ms access.

I prepared my database and saved it as dbMert and put it to debug / bin

This is my CustomerDatabase class for connecting to database:

static class CustomerDatabase
{

    static string connectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbMert.mdb";
    static OleDbConnection connection = null;
    static OleDbCommand command = null;


    public static void ConnectToDatabase()
    {
        if (connection == null)
        {
            connection = new OleDbConnection(connectionstring);
            command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = "select * from Customer";
        }
    }     

    public static DataTable executeSelect(string sql)
    {
        ConnectToDatabase();
        DataTable dt = null;
        dt = new DataTable();
        command.CommandText = sql;
        OpenConnection();
        OleDbDataReader datareader = command.ExecuteReader();
        dt.Load(datareader);
        datareader.Close();
        CloseConnection();
        return dt;
    }

    public static void OpenConnection()
    {
        try
        {
            if (connection != null)
            {
                connection.Open();
            }
        }
        catch (Exception ex)
        {
        }
    }

    public static void CloseConnection()
    {
        if (connection != null)
        {
            connection.Close();
        }
    }


}

}

Form: In constructor i try to connect to database

public Form1()
{
    InitializeComponent();
    CustomerDatabase.ConnectToDatabase();
}

and in form's load i try to take tuples to datagridview but nothing happens :S

private void Form1_Load(object sender, EventArgs e)
{
    string sql1 = "select * from Customer";
    DataTable dt = CustomerDatabase.executeSelect(sql1);
}

Upvotes: 0

Views: 2994

Answers (3)

Mert Metin
Mert Metin

Reputation: 411

When I move the DB-file to bin/debug, an error message states "4.0 is not installed".

However, when I move it to bin, the problem is solved.

My code:

      con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= ..\\dbMert.mdb";
      con.Open();


    recordları
      DataSet ds = new DataSet();
      DataTable dt = new DataTable();
      ds.Tables.Add(dt);
      OleDbDataAdapter da = new OleDbDataAdapter();



      da = new  OleDbDataAdapter("Select * from Customer", con);
      da.Fill(dt);

      dataGridView1.DataSource = dt;






      foreach (DataRow row in dt.Rows)
      {
          foreach (DataColumn col in dt.Columns)
              if(col.ToString() == "emailAdress")
              comboBox1.Items.Add(row[col]);
      }




      con.Close();

Upvotes: 0

KV Prajapati
KV Prajapati

Reputation: 94645

Try to write simple code. I suggest you to use OleDbDataAdaper, its Fill() method populate the DataTable easily.

You may use |DataDirectory| if you are using database (.mdb) located under Bin\Debug folder.

static string connectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;
                             Data Source=|DataDirectory|\dbMert.mdb";

Or

static string connectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;
                              Data Source=x:\full_path\dbMert.mdb";

Or

static string connectionstring = @"Provider=Microsoft.Jet.OLEDB.4.0;
                              Data Source=x:\full_path\dbMert.mdb";

static class Test
{
    static string connectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbMert.mdb";
    public static DataTable executeSelect(string sql)
    {
        DataTable dt = new DataTable();
        OleDbDataAdapter adapter = new OleDbDataAdapter(sql,connectionString);
        adapter.Fill(dt);
        return dt;
    }
}

Add following code in form_load handler,

 string sql1 = "select * from Customer";
 DataTable dt = Test.executeSelect(sql1);
 DataGridView1.DataSource=dt;

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1501626

Regardless of other things (such as not keeping a single connection open, using using statements etc) you're not connecting your newly-loaded DataTable to your DataGridView at all. Your Form1_Load method just loads the data into a DataTable, then effectively throws it away.

I suspect you want something like:

 dataGridView.DataSource = dt;

at the end of the method.

EDIT: Note that this is also a really bad idea in your OpenConnection code:

catch (Exception ex)
{
}

That basically says, "If something goes wrong, don't bother recording that fact or changing how the rest of the code works - just keep going as if nothing had happened."

Why are you catching the exception at all?

Upvotes: 2

Related Questions