OleDbConnection being used in different forms, possible?

[ashrobo]

hi all,
i am developing an application that has a couple of forms. say, if i have a connection to a MS Access database at form2, can i make use of that connection at form3 or do i have to create a new connection? many apologies if this question has been asked many times, as i've got no idea how to go about searching for solutions for this problem.

thanks,
ashrobo

[Pirate]

Yes it's possible . In my case , I declared shared variable as oleconnection and call it anywhere BUT you have to close the connection after every use .

[ashrobo]

ah, okay.. i'm currently using untyped datasets, so where do i "close the connection after every use"? do i do it after

OleDbDataAdapter1.Fill(DataSet1)

?

[ashrobo]

i am having trouble setting the oledbconnection as a shared variable. is it advisable to create a oledbconnection for every form?

[Pirate]

Originally posted by ashrobo
ah, okay.. i'm currently using untyped datasets, so where do i "close the connection after every use"? do i do it after

OleDbDataAdapter1.Fill(DataSet1)

?

Well , it depends on the transaction you're doing, for example after you fill your dataset then you can close your connection since the same data was filled from source as you can work offline.

[Pirate]

Originally posted by ashrobo
i am having trouble setting the oledbconnection as a shared variable. is it advisable to create a oledbconnection for every form?

No . Did you read my earlier posts above ? Declare one shared variable as oleconnection in a class file then use it while working in other forms

[Lunatic3]

I think a 'Fill' statement of dataadapter goes through an open and close of the corresponding connection. So you may not have to explicitly close your connection if you have not opened it explicitly.

[Pirate]

MS always recommends of closing connections after every use !

[Lunatic3]

Yes, Close but not OVERCLOSE

If you check the connection.State after the Fill statement it should be 'Closed'.

[DevGrp]

Since DataSets are disconnected, the connection is closed after its filled. But you should use try catch blocks.

try
{
    myDataAdapter.Fill(myDataSet);
}
Catch(Exeception ex)
{
    MessageBox.Show(ex.Message);
{
Finally
{
    myConn.Close;
}

[hellswraith]

Also, it helps to create a data access class. Then, you can inherit it to make other data classes more specific to what you need. It would also help you later when you want to add data access capabilities to another project, you don't have to keep rewriting the methods.

Example (in C#. I got most of it out of a good book I have called ASP.NET Website Programming: wrox):

public class DBObject
{
protected SqlConnection Connection;
private string connectionString;

public DBObject(string newConnectionString)
{
   connectionString = newConnectionString;
   Connection = new SqlConnection(connectionString);
}

protected string ConnectionString
{
   get
   {
      return connectionString;
    }
}

private SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)
{
   SqlCommand command = BuildQueryCommand(storedProcName, parameters);

   command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, /* Size */
   ParameterDirection.ReturnValue, false, /* is nullable */ 
   0, /* byte precision */
   0, /* byte scale */
   string.Empty, 
   DataRowVersion.Default, null));

   return command;
}

private SqlCommand BuildStringCommand(string storedProcName, IDataParameter[] parameters)
{
   SqlCommand command = BuildQueryCommand(storedProcName, parameters);

   command.Parameters.Add(new SqlParameter("ReturnValue",
      SqlDbType.VarChar, 75, 
      ParameterDirection.ReturnValue, false,
      0,
      0,
      string.Empty, 
      DataRowVersion.Default, null));

   return command;
}

private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
{
   SqlCommand command = new SqlCommand(storedProcName, Connection);
   command.CommandType = CommandType.StoredProcedure;

   foreach(SqlParameter parameter in parameters)
   {
       command.Parameters.Add(parameter);
    }
            
    return command;
}

private SqlCommand BuildQueryCommand(string storedProcName)
{
   SqlCommand command = new SqlCommand(storedProcName, Connection);
   command.CommandType = CommandType.StoredProcedure;
   return command;
}

protected int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
{
   int result;
   Connection.Open();
   SqlCommand command = BuildIntCommand(storedProcName, parameters);
   rowsAffected = command.ExecuteNonQuery();
   result = (int)command.Parameters["ReturnValue"].Value;
   Connection.Close();
   return result;
}

protected SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{
   SqlDataReader returnReader;
   Connection.Open();
   SqlCommand command = BuildQueryCommand(storedProcName, parameters);
   command.CommandType = CommandType.StoredProcedure;
   returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
   return returnReader;
}

protected DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
   DataSet dataSet = new DataSet();
   Connection.Open();
   SqlDataAdapter sqlDA = new SqlDataAdapter();
   sqlDA.SelectCommand = BuildQueryCommand(storedProcName, parameters);
   sqlDA.Fill(dataSet, tableName);
   Connection.Close();
   return dataSet;
}
} 

[ashrobo]

thanks for the input guys and gals!

hellswraith: do you happen to have a similar code for VB .NET?

[hellswraith]

Here is the code from the VB version of the book. You should find the class in there:
http://web.wrox.com/download/code/pr...63_code_v7.zip

It is in the core folder named dbobject.vb

[ashrobo]

thank you!