Programming problem ASP.NET c#

  • Thread starter Thread starter Reinier Beeckman
  • Start date Start date
R

Reinier Beeckman

Hi got the following problem, it's more a programming problem then really a
database problem. I have a Database class which connects to a mySQL database on
the network. In WebForm1.aspx i connect, using the Database class, to this
database. Works fine. In
Webform1.aspx i have the main view (SELECT * FROM) of the database. In
Webform2.aspx i want to see certain details. So when i click on a button in
Webform1.aspx i go to Webform2.aspx.

In webform2.aspx i want to use the same (open) connection. What happens now is
that the connection is closed and made over again, this is needed because else
the Class Database is not known in WebForm2.aspx. (The Database class is the
class where i initiate the database)

My question is how can i use the Database class without initiating it again in
WebForm2.aspx ??

This is my Database Class

using System;
using System.Data;
using ByteFX.Data.MySQLClient;
using System.Collections;
namespace webAPP
{

public class Database
{
private MySQLConnection con;
private string server, database, user, password;

public Database(string server, string database, string user, string
password)
{
this.server = server;
this.database = database;
this.user = user;
this.password = password;
}

public void Connect()
{
string connectionString = "Server="+server+
";Database="+database+";User ID="+user+";Password="+password+";";
try
{
con = new MySQLConnection(connectionString);
con.Open();
if(con!=null)
Console.WriteLine("succes");
}
catch(Exception e)
{
throw new Exception(e.Message);
}
}

public void Close()
{
if(con!=null) con.Close();
}

public string[] getValue(string value)
{
String[] arrValues = null;
MySQLDataAdapter adapter = new MySQLDataAdapter("SELECT * from pc where
testnr =" + value, con);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable table = ds.Tables[0];
ArrayList kolomWaarden = new ArrayList();
foreach (DataRow row in table.Rows)
{
arrValues = new String[table.Columns.Count];
for (int i=0; i<table.Columns.Count; i++)
arrValues = Convert.ToString(row);
}
return arrValues;
}

}
}


In WebForm1.aspx i have this code to connect to the database:

private void openGb()
{
try
{
if(gb != null)
gb.Close();
gb = new Database("servername", "databasenaam", "username", "password");
//initiate database class
gb.Connect();
}
catch(Exception e)
{
Console.WriteLine(e);
}
}

In WebForm2.aspx i declare the Database Class by saying

Database gb;

In the page_load i then want to run the SQL-Query in gb.getValue( ) But i then
get a Object reference not found error because the Database Class is not
instantiated in WebForm2.aspx. That is like: gb = new Database("servername",
"databasenaam", "username", "password");

Is there a way to prevent this ??
 
It should also be said that performing database operations in this way
is a very bad idea. Generally speaking, when you want to connect to a
database, you should open the connection, use it, and then close it. If you
are changing contexts like this (i.e. moving from page to page), you
definitely should close the connection. If something goes wrong during the
transition, then the open connection is left hanging, which is definitely
another downside.

However, this doesn't mean that you can't store the result of the query
(a DataSet) somewhere (like the session).

Hope this helps.
 
Hi Reinier,

You can do several things, one of then is keep the instance of DataBase
class in a session variable , this is not a very good solution as you will
have several open connection opened at the same time ( one per session )
this greatly affect the performance and scalability of your solution.
another solution would be make the DataBase class a singleton, doing this
will assure you that only one instance of the class exist in your
application so all the request will use the same connection to the DB.

Any of the above forms solve your problem of reuse the same instance in
webform2.aspx page .

Now to finish, I would suggest you to take a look at the connection
pooling capabilities of ADo.NET and the SQLServer provider:
http://msdn.microsoft.com/library/d...nectionpoolingforsqlservernetdataprovider.asp

there you will see that you don;t have to worry for this, as the provider
provide a pooling feature that handle this situation for you.
Remember you should ALWAYS close the connection AS EARLY AS POSSIBLE.

Hope this help,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

Reinier Beeckman said:
Hi got the following problem, it's more a programming problem then really a
database problem. I have a Database class which connects to a mySQL database on
the network. In WebForm1.aspx i connect, using the Database class, to this
database. Works fine. In
Webform1.aspx i have the main view (SELECT * FROM) of the database. In
Webform2.aspx i want to see certain details. So when i click on a button in
Webform1.aspx i go to Webform2.aspx.

In webform2.aspx i want to use the same (open) connection. What happens now is
that the connection is closed and made over again, this is needed because else
the Class Database is not known in WebForm2.aspx. (The Database class is the
class where i initiate the database)

My question is how can i use the Database class without initiating it again in
WebForm2.aspx ??

This is my Database Class

using System;
using System.Data;
using ByteFX.Data.MySQLClient;
using System.Collections;
namespace webAPP
{

public class Database
{
private MySQLConnection con;
private string server, database, user, password;

public Database(string server, string database, string user, string
password)
{
this.server = server;
this.database = database;
this.user = user;
this.password = password;
}

public void Connect()
{
string connectionString = "Server="+server+
";Database="+database+";User ID="+user+";Password="+password+";";
try
{
con = new MySQLConnection(connectionString);
con.Open();
if(con!=null)
Console.WriteLine("succes");
}
catch(Exception e)
{
throw new Exception(e.Message);
}
}

public void Close()
{
if(con!=null) con.Close();
}

public string[] getValue(string value)
{
String[] arrValues = null;
MySQLDataAdapter adapter = new MySQLDataAdapter("SELECT * from pc where
testnr =" + value, con);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable table = ds.Tables[0];
ArrayList kolomWaarden = new ArrayList();
foreach (DataRow row in table.Rows)
{
arrValues = new String[table.Columns.Count];
for (int i=0; i<table.Columns.Count; i++)
arrValues = Convert.ToString(row);
}
return arrValues;
}

}
}


In WebForm1.aspx i have this code to connect to the database:

private void openGb()
{
try
{
if(gb != null)
gb.Close();
gb = new Database("servername", "databasenaam", "username", "password");
//initiate database class
gb.Connect();
}
catch(Exception e)
{
Console.WriteLine(e);
}
}

In WebForm2.aspx i declare the Database Class by saying

Database gb;

In the page_load i then want to run the SQL-Query in gb.getValue( ) But i then
get a Object reference not found error because the Database Class is not
instantiated in WebForm2.aspx. That is like: gb = new Database("servername",
"databasenaam", "username", "password");

Is there a way to prevent this ??
Click to expand...
 
Back
Top