database access from multiple user controls

[rebelde]

I'm trying to move from <#INCLUDE> to user controls.

With an .aspx page, I just used <#INCLUDE> to set up database connections (connection string, OdbcConnection(), OdbcCommand, OdbcDataReader) once and accessed them from multiple <#INCLUDE> parts of the page. It was easy and it worked.

Now, using user controls instead, I seem to need to have to repeat this code in each and every user control. My best idea is to use an <#INCLUDE> in all those user controls.

Is there a better way?

[Ocean10000]

The easiest way is to create a class which includes all your database logic. And use that class on all the pages. Below is an example which is part of such a class.

namespace Ocean2.Web.Dal.Reporting
{
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;

/// <summary>
/// Summary description for DataAccess
/// </summary>
public partial class DataAccess
{
System.Data.SqlClient.SqlConnection Connection;
public DataAccess()
{
Connection = new System.Data.SqlClient.SqlConnection();
Connection.ConnectionString = ConfigurationManager.ConnectionStrings["ComicsConnectionString"].ConnectionString;
}
private System.Data.SqlClient.SqlConnection CreateConnection()
{
return Connection;
}
public System.Data.DataSet Page(int PageId)
{
System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand();
Cmd.CommandText = @"Select
[pageid],[pagename],[title],[keywords],[description]
From [Page]
where (
[pageid] = @pageid)

Order By
[PageId]
";
Cmd.CommandType = System.Data.CommandType.Text;
Cmd.Connection = this.CreateConnection();
System.Data.SqlClient.SqlParameter Parameter;

//---------------------------------------------------
// PageId
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@PageId";
Parameter.SourceColumn = "PageId";
Parameter.Precision = 10;
Parameter.Size = 4;
Parameter.IsNullable = false;
Parameter.Value = PageId;
Cmd.Parameters.Add(Parameter);

System.Data.SqlClient.SqlDataAdapter Adapter;
Adapter = new System.Data.SqlClient.SqlDataAdapter();
Adapter.SelectCommand = Cmd;
System.Data.DataSet ds = new System.Data.DataSet();
Adapter.Fill(ds);
return ds;
}
public System.Data.DataSet PageItems(int pageid)
{
System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand();
Cmd.CommandText = @"Select
[pageitemid],[ordernum],[pageid],[title],[article]
From [PageItems]
where (
[pageid] = @pageid)

Order By
[ordernum],
[PageitemId]
";
Cmd.CommandType = System.Data.CommandType.Text;
Cmd.Connection = this.CreateConnection();
System.Data.SqlClient.SqlParameter Parameter;

//---------------------------------------------------
// pageid
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@pageid";
Parameter.SourceColumn = "pageid";
Parameter.Precision = 10;
Parameter.Size = 4;
Parameter.IsNullable = false;
Parameter.Value = pageid;
Cmd.Parameters.Add(Parameter);

System.Data.SqlClient.SqlDataAdapter Adapter;
Adapter = new System.Data.SqlClient.SqlDataAdapter();
Adapter.SelectCommand = Cmd;
System.Data.DataSet ds = new System.Data.DataSet();
Adapter.Fill(ds);
return ds;
}

}
}

Ocean2.Web.Dal.Reporting.DataAccess DB = new Ocean2.Web.Dal.Reporting.DataAccess();
System.Data.DataSet ds = DB.Page(PageId);

[marcel]

To follow up on what Ocean has written, it is best to separate you Data, Business Logic en Presentation. Here is a great article series from Imar Spaanjars (author of beginning ASP.Net 3.5)

Building Layered Web Applications with Microsoft ASP.NET 2.0 [imar.spaanjaars.com]

[rebelde]

Thanks,

Ocean, I think I see what you are doing that is different. I have been using the DataReader as a global object(?) that I could access form anywhere. You, instead, create a function that returns a DataSet. I hope that I'll be able to create a function that returns an OdbcDataReader that I can use While.read() on just the same.

Marcel, thanks for the link. Separating this into three layers is a bit beyond what I'm trying to accomplish here, and might be overkill. I just want to execute a SQL query and output the lines, nothing more. I'll still reread that article a few times to try to understand it all better.

[Ocean10000]

I do not normally use DataReaders directly. I use datasets since they allow me to cache the data to save future database hits when possible. Also makes managing database connections much much simpler. Since the connection is only opened long enough to read the data into the dataset, then closed.

[rebelde]

I prefer to cache the data after I transform it to HTML (using Cache.Insert). Then when the same webpage is requested again, the HTML is already ready and stored in memory. Simplifying database connectivity certainly seems like a worthwhile goal, though.

Thanks again.