There must be a better way than this (oledb connections and connection strings)

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

Hi all,

I'm currently writing a windows-forms based application in C# using
visual studio express. It interfaces with an Access Database backend
and at the moment, I have a seperate connection for every function
(such as a button click) that I use. This means that at the moment, my
program is doing the following in a number of places:

1) On Form_Load, read the list of users from the database then close
the data connection
2) When the button is pushed to get the details of a specific user,
open the data connection (respecifying the variables including the
connection string), select the data and then close the connection

I'm from a PHP development background, so what I would like to do is
this:

1) Declare an application wide database connection object.
2) when any form is loaded, open the database object, do what you have
to do and close the connection.
3) if a button is clicked, open the same database object, do what you
need to do and close the object.

In the past (using PHP), I've declared the variable/object at the start
of the session and then just opened and closed it as needed. How do I
do this in C#?

Cheers,

Matt
 
Matt,

I'm not an extremely experienced developer, but if your application is
large enough, consider using Microsoft's Enterprise Library 2.0 Data
Access application block.

You specify the connection strings in an external configuration file,
create a wrapper for the functionality you need (queries, non-queries,
datasets, datareader, etc), and the dll handles the connection objects.

http://msdn.microsoft.com/practices/guidetype/AppBlocks/

The other, slightly more lightweigt option is to create your own Data
Access class. For instance (now this has no real exception handling and
may be less than optimal, but it's a good start)...


public class DBC
{
private const string dbPath = @"[path to db]";

private static DataSet ExecuteQuery(string query)
{
// create and open connection
OleDbConnection myAccessConn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
dbPath);
myAccessConn.Open();

// create and reference command and connection
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(query,
myAccessConn);

// create empty dataset
DataSet myDataSet = new DataSet();

// fill dataset
try
{
myDataAdapter.Fill(myDataSet);
}
catch(Exception e)
{
return null;
}

// close connection
myAccessConn.Close();

return myDataSet;
}

private static void NonQuery(string sql)
{
// create and open connection
OleDbConnection myAccessConn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
dbPath);
myAccessConn.Open();

// create and reference command and connection
OleDbCommand myAccessCommand = new OleDbCommand(sql, myAccessConn);

// execute the command
myAccessCommand.ExecuteNonQuery();

myAccessConn.Close();
}
}
 
Matt,

Try to find some information about DataSet/DataTable development. In my idea
are you using a king of datareading approach. (I can be wrong I wrote in my
idea).

Although I seldom read books I would in your place do that. Bill Vaughn is
the most active AdoNet writter on this board, however AFAIK also very good
recommended elsewhere. He has written a new book, which is in my idea not as
most AdoNet book writters an update of the previous ones. There are in my
idea to much new things in SQL server and ADOnet to buy a kind of updated
book

http://www.hitchhikerguides.net:80/

I hope this helps,

Cor
 
All,

Thanks for the input. I'll look at writing my own class and adding it
to the project as this looks like being the most suitable route.

Regards,

Matt
 
Microsoft Access and .NET is slooooow.

That said, you could use this code generator designed for
access. At the very least, you code skip the layer
that populates classes. It will save you writing
a ton of code.

http://www.eggheadcafe.com/articles/microsoftaccess_source_code_generator.asp

--
Robbe Morris - 2004-2006 Microsoft MVP C#
I've mapped the database to .NET class properties and methods to
implement an multi-layered object oriented environment for your
data access layer. Thus, you should rarely ever have to type the words
SqlCommand, SqlDataAdapter, or SqlConnection again.
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp
 
You say it must be an Access (JET) backend. If I might be so bold as to ask
why?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
William said:
You say it must be an Access (JET) backend. If I might be so bold as to ask
why?
Click to expand...

If you don't mind, I'm not able to answer that, however all I can say
is that using something like SQLServer is out of the question (If I
could, I'd write the whole thing as a web-based app using LAMP (Linux,
Apache, MySQL and PHP) because that's what I know and what I love!), so
at the moment I'm using C# as the forms within Access won't do
everything that I want them to and this app also has to tie into Active
Directory.

Cheers,

Matt
 
I understand. Consider that there are other lightweight engines that might
be a better choice. Consider the SQL Server Compact Edition that's just been
reported to work on Windows desktop platforms. It's more secure than JET,
faster and while more limited in some respects, it's often a better choice
for smaller single-user dedicated databases.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
Back
Top