Solved

C# - Re-using a DataAdapter

Posted on 2007-04-11
4
234 Views
Last Modified: 2010-04-16
Hi guys 'n gals,

I wanted to know if there is a way so consolidate my code, by maybe re-using my DataAdapter variable, rather than creating a new one for each query to the database? Here is how my code looks at the moment:

        void FillDataTables()
        {
            MySqlConnection conn = new MySqlConnection(connString);
            DataSet ds = new DataSet();
            conn.Open();
            MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM tblConnections", conn);
            adapter.Fill(ds);
            adapter = null;
            MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM tblInspector", conn);
            adapter.Fill(ds);
            conn.Close();

            MessageBox.Show(ds.Tables.Count.ToString());
        }


It throws the following error:

Error      1      A local variable named 'adapter' is already defined in this scope



Any ideas on getting past this?


Cheers guys!
0
Comment
Question by:Cyber-Drugs
4 Comments
 
LVL 23

Assisted Solution

by:Jens Fiederer
Jens Fiederer earned 150 total points
Comment Utility
For the second use, do
    adapter = new MySqlDataAdapter("SELECT * FROM tblInspector", conn);

You don't need (and are not allowed!) to declare it twice!
0
 
LVL 4

Assisted Solution

by:aponcealbuerne
aponcealbuerne earned 150 total points
Comment Utility
hi:
Well the error is because you have the same declaration two times
MySqlDataAdapter adapter --> this is twice in the code so you can use something like:

void FillDataTables()
        {
            MySqlConnection conn = new MySqlConnection(connString);
            DataSet ds = new DataSet();
            conn.Open();
            MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM tblConnections", conn);
            adapter.Fill(ds);
            adapter = null;
            adapter = new MySqlDataAdapter("SELECT * FROM tblInspector", conn);
            adapter.Fill(ds);
            conn.Close();
            MessageBox.Show(ds.Tables.Count.ToString());
        }
you can also just change the CommandText for the DataAdapter instead of recreate the object.


something like:

void FillDataTables()
        {
            MySqlConnection conn = new MySqlConnection(connString);
            DataSet ds = new DataSet();
            conn.Open();
            MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM tblConnections", conn);
            adapter.Fill(ds);
            adapter = null;
            adapter.SelectComand =      "SELECT * FROM tblInspector";
            adapter.Fill(ds);
            conn.Close();
            MessageBox.Show(ds.Tables.Count.ToString());
        }


Hope helps
0
 
LVL 3

Accepted Solution

by:
zajda82 earned 200 total points
Comment Utility
you cant declare two variables of same name in one scope, you set your adapter to null, but variable still exists, try something like this:

using (MySqlConnection conn = new MySqlConnection("connstring"))
using (MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM tblConnections", conn))
{
       conn.Open();
       adapter.Fill(ds);
       adapter.SelectCommand.CommandText = "SELECT * FROM tblInspector";
       adapter.Fill(ds);
       conn.Close();
}
0
 
LVL 4

Author Comment

by:Cyber-Drugs
Comment Utility
Thanks guys, all the solutions seem to work, although I think I will be going with zajda82's, as it allows me to just change the select statement for the adapter, rather than re-calling the while lot again.


Cheers!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now