I am not an expert but, there are many things you could do that would be a good practice.
I like to make a public query method for "my" database object. In the method I do the connecting, opening the connection, querying and closing the connection. Like so...
public [data_type] query([parameters])
{
connect()
//execute the query
disconnect();
}
Why have your other classes worry about connecting when your DB object should?
You could use threads to execute the query if you have that many running.
Rather than pass the db object along to your child form you could use a singleton pattern (I think I spelled that right) which dictates that only one db object will exist at any time. Do this by using a getInstance method like so...
class DB
{
private DB instance;
public DB getInstance()
{
return (instance == null) ? new DB() : instance;
}
}
This will insure only one exists in the code. I think there might even be a singleton class or interface or whatever (not sure).
Hope that helps! Anyone feel free to correct me or elaborate or share another idea.

didittoday has a great point here.
I think the best thing to do is create a class implemented as a singleton to handle all the database access. This should look somehing like this:
namespace Your_Namespace_Here
{
/// <summary>
/// The DAO class handles all database access.
/// Use the Instance property to access methods.
/// </summary>
class DAO
{
private static DAO instance;
private String ConnectionString;
private DAO()
{
ConnectionString = "Put ConnectionString here.";
}
/// <summary>
/// Returns an instance of this class.
/// Creates a new instance if none exists.
/// </summary>
public static DAO Instance
{
get
{
if (instance == null)
instance = new DAO();
return instance;
}
}
public string GetName(int ID)
{
string result = "";
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
String QueryString = "SELECT Name FROM Names WHERE ID = @ID";
using (SqlCommand cmd = new SqlCommand(QueryString, connection))
{
cmd.Parameters.Add(new SqlParameter("@ID"), ID);
try
{
cmd.Connection.Open();
result = (string)cmd.ExecuteScalar();
}
catch
{
}
}
}
return result;
}
}
}
Hope it helps...

Looking at foamy code, shouldnt it have a close connection in it? Also looking at your code I need to build all my querys into the DAO class... or maybe just build one function that I pass the query and it returns the data.

First question,
No. When you use a using statement such as foamy did when the code is done running (e.g. it hits the cmd.Connection.Open();
result = (string)cmd.ExecuteScalar();) it automatically closes the connection because it knows you are done "using" that object at this current time (or something like that...maybe someone can explain it better).
Second question,
Its up to you. Whatever you think will work best for you. Being a beginner you might not know (and us not being you, we don't know) but I'll take a stab at it. I would make a query method in your DAO class that takes three parameters, 1. the query itself (e.g. use command objects and set their parameters) such as "SELECT * FROM Table WHERE "SomeColumn" = ?". 2. the data to be used for the question marks (typically an object[] or string[] but obviously doesn't have to be either one of those). 3. a bool variable indicating whether or not it is a select statement.
See this is where you will get different opinions. Yeah some ways are betters than others (obviously). But what I do is make one query method that handles all select, insert, update and delete statements. But that may be a little difficult for you.

I added a function in the DAO class that looks like below. Now I just have to figure out how to pass the Parameters to the function and I think I till be good to go.
public static DataTable GetDataTable(string sqlQuery)
{
DataTable DataResults = new DataTable();
using (SqlConnection connection = new SqlConnection(inc_config.Config.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sqlQuery, connection))
{
try
{
cmd.Connection.Open();
SqlDataAdapter thisAdapter = new SqlDataAdapter(cmd);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "tmpEvents");
DataResults = thisDataSet.Tables[0];
}
catch
{
}
}
}
return DataResults;
}

Try google, every programmer best friend. I found this when I typed in "c# parameterized queries"...
http://codebetter.com/blogs/david.hayden/archive/2006/01/05/136264.aspx

Typically, and as far as I know. Best practices say to use a strongly typed class that mirrors your table. I was planning on posting a snippet of a generated class from a database table, however I can't seem to find my generator that I wrote some time ago in Framework 1.1. But there are plenty of examples and DB generators out there.
By the way. A "Using" statement is simply a try finally statement
using()
{
}
equals
try
{
}
finally
{
}
In fact, I think you can only use a "Using" statement on something that inherits the IDisposable Interface. The "finally" calls the objects Dispose, that is why it closes the DB connection for you.

I understand how to use the Parameters as I was using those in my other querys, but I just cant seem to get it pass the params to my function. Here is the code I am using, do you see anything wrong with it?
string sqlQuery = "SELECT Event_ID";
sqlQuery += ", Event_Name";
sqlQuery += ", Event_DateTime";
sqlQuery += ", Event_LastSynced";
sqlQuery += " FROM Event";
sqlQuery += " WHERE Event_DateTime >= @DateSearch";
sqlQuery += " ORDER BY Event_DateTime ASC";
SqlParameter[] sqlparams = {
new SqlParameter("@DateSearch", EventSearchDate)
};
DataTable DataResults = DAO.DAO.GetDataTable(sqlQuery, sqlparams);
public static DataTable GetDataTable(string sqlQuery, SqlParameter[] sqlParm)
{
DataTable DataResults = new DataTable();
using (SqlConnection connection = new SqlConnection(inc_config.Config.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sqlQuery, connection))
{
cmd.Parameters.Add(sqlParm);
try
{
cmd.Connection.Open();
SqlDataAdapter thisAdapter = new SqlDataAdapter(cmd);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "tmpEvents");
DataResults = thisDataSet.Tables[0];
}
catch
{
}
}
}
return DataResults;
}

I guess I should also post the error :-)
The SqlParameterCollection only accepts non-null SqlParameter type objects, not SqlParameter[] objects.

Looks like I got it... I need to use AddRange instead of Add
cmd.Parameters.AddRange(sqlParm);
Time to convert all to use this new Class... thanks guys for all your help.

Just to point it out, the DAO class I wrote for you is meant to be used as a singleton. There's nothing wrong with using it as a static class, but this is how I usually do it:
DAO dao = DAO.Instance;
dao.Method();

but from performance reasons or security..... which one is better or its just however you wish to code it?

In this case, there isn't really a difference.
A singleton is a pattern that ensures only one instance of a class is created, so in effect doing this or creating a static class will make no difference whatsoever.
At least, that's how I understand it.