homepage Welcome to WebmasterWorld Guest from 54.197.94.241
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
Forum Library, Charter, Moderators: ocean10000

Microsoft IIS Web Server and ASP.NET Forum

    
database access from multiple user controls
rebelde




msg:4023421
 10:29 pm on Nov 11, 2009 (gmt 0)

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




msg:4023554
 3:14 am on Nov 12, 2009 (gmt 0)

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




msg:4023583
 6:13 am on Nov 12, 2009 (gmt 0)

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




msg:4023816
 3:44 pm on Nov 12, 2009 (gmt 0)

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




msg:4024050
 8:42 pm on Nov 12, 2009 (gmt 0)

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




msg:4024060
 8:56 pm on Nov 12, 2009 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved