Hello --
I'm trying to rewrite a few simple ASP pages that I have to use ASP.NET 2.0.
One of the nice things I see that I can do now is separate my Business Logic
layer and Data Access Layer to use the App_Code directory.
I'm having an awful time finding any good examples of this. I've read the
examples on http://www.asp.net and various Microsoft webcasts. Most seem
very simple and I'm just wondering if I'm on the right track. I'm new to
ASP.NET 2.0.
My requirement is that I basically have a business website that has a search
form. Users basically pick a few options on the search form and the ASP
page will query a particular database and various tables and return the
results. Users can basically check the status of various jobs that are run
on the server.
I'm trying to rewrite this application the correct way and use Data Binding
along with the DataObjectSourc e to access the App_Code classes.
I've essentially duplicated the following code to query a particular table
that I have:
Business Access Layer:
using System;
public class Author
{
private String _id;
public String ID
{
get
{
return _id;
}
set
{
_id = value;
}
}
private String _name;
public String Name
{
get
{
return _name;
}
set
{ _name = value; } } private String
_lastName;
public String LastName
{
get
{
return _lastName;
}
set
{
_lastName = value;
}
}
private String _state;
public String State
{
get
{
return _state;
}
set
{
_state = value;
}
}
public Author (String id, String name, String lastName, String state)
{
this.ID = id;
this.Name = name;
this.LastName = lastName;
this.State = state;
}
public Author()
{
// default constructor
}
}
using System;
using System.Data;
using System.Collecti ons.Generic;
public class AuthorsComponen t
{
public AuthorsComponen t ()
{
// TODO: Add constructor logic here
}
public List<Author> GetAuthorsBySta te (String state, String
sortExpression)
{
List<Author> authors = new List<Author> ();
DataSet ds = AuthorsDB.GetAu thorsByState (state);
foreach (DataRow row in ds.Tables[0].Rows)
{
authors.Add (new Author ((String)row["au_id"],
(String)row["au_fname"], (String)row["au_lname"], (String)row["state"]));
}
authors.Sort(ne w AuthorComparer( sortExpression) );
return authors;
}
public int UpdateAuthor (string ID, string LastName, string Name, string
State)
{
return AuthorsDB.Updat eAuthor (ID, LastName, Name, State);
}
public int UpdateAuthor(Au thor a)
{
return AuthorsDB.Updat eAuthor(a.ID, a.LastName, a.Name, a.State);
}
public List<String> GetStates()
{
List<String> states = new List<String>();
DataSet ds = AuthorsDB.GetSt ates();
foreach (DataRow row in ds.Tables[0].Rows)
{
states.Add((Str ing)row["state"]);
}
return states;
}
}
public class AuthorComparer : IComparer<Autho r>
{
private string _sortColumn;
private bool _reverse;
public AuthorComparer( string sortExpression)
{
_reverse = sortExpression. ToLowerInvarian t().EndsWith(" desc");
if (_reverse)
{
_sortColumn = sortExpression. Substring(0,
sortExpression. Length - 5);
}
else
{
_sortColumn = sortExpression;
}
}
public int Compare(Author a, Author b)
{
int retVal = 0;
switch (_sortColumn)
{
case "ID":
retVal = String.Compare( a.ID, b.ID,
StringCompariso n.InvariantCult ureIgnoreCase);
break;
case "Name":
retVal = String.Compare( a.Name, b.Name,
StringCompariso n.InvariantCult ureIgnoreCase);
break;
case "LastName":
retVal = String.Compare( a.LastName, b.LastName,
StringCompariso n.InvariantCult ureIgnoreCase);
break;
case "State":
retVal = String.Compare( a.State, b.State,
StringCompariso n.InvariantCult ureIgnoreCase);
break;
}
return (retVal * (_reverse ? -1 : 1));
}
}
Data Access Layer:
using System;
using System.Configur ation;
public class AuthorsDB
{
public AuthorsDB() { }
public static System.Data.Dat aSet GetAuthorsBySta te(string state)
{
string connectionStrin g =
ConfigurationMa nager.Connectio nStrings["Pubs"].ConnectionStri ng;
System.Data.IDb Connection dbConnection = new
System.Data.Sql Client.SqlConne ction(connectio nString);
string queryString = "SELECT au_id, au_fname, au_lname, state FROM
[authors] WHERE ([authors].[state] = @state)";
System.Data.IDb Command dbCommand = new
System.Data.Sql Client.SqlComma nd();
dbCommand.Comma ndText = queryString;
dbCommand.Conne ction = dbConnection;
System.Data.IDa taParameter dbParam_state = new
System.Data.Sql Client.SqlParam eter();
dbParam_state.P arameterName = "@state";
dbParam_state.V alue = state;
dbParam_state.D bType = System.Data.DbT ype.StringFixed Length;
dbCommand.Param eters.Add(dbPar am_state);
System.Data.IDb DataAdapter dataAdapter = new
System.Data.Sql Client.SqlDataA dapter();
dataAdapter.Sel ectCommand = dbCommand;
System.Data.Dat aSet dataSet = new System.Data.Dat aSet();
dataAdapter.Fil l(dataSet);
return dataSet;
}
public static System.Data.Dat aSet GetStates()
{
string connectionStrin g =
ConfigurationMa nager.Connectio nStrings["Pubs"].ConnectionStri ng;
System.Data.IDb Connection dbConnection = new
System.Data.Sql Client.SqlConne ction(connectio nString);
string queryString = "SELECT DISTINCT [authors].state FROM
[authors]";
System.Data.IDb Command dbCommand = new
System.Data.Sql Client.SqlComma nd();
dbCommand.Comma ndText = queryString;
dbCommand.Conne ction = dbConnection;
System.Data.IDb DataAdapter dataAdapter = new
System.Data.Sql Client.SqlDataA dapter();
dataAdapter.Sel ectCommand = dbCommand;
System.Data.Dat aSet dataSet = new System.Data.Dat aSet();
dataAdapter.Fil l(dataSet);
return dataSet;
}
public static int UpdateAuthor (string au_id, string au_lname, string
au_fname, string state)
{
string connectionStrin g =
ConfigurationMa nager.Connectio nStrings["Pubs"].ConnectionStri ng;
System.Data.IDb Connection dbConnection = new
System.Data.Sql Client.SqlConne ction(connectio nString);
string queryString = "UPDATE [authors] SET [au_lname]=@au_lname,
[au_fname]=@au_fname, [state]=@state WHERE ([authors].[au_id] = @au_id)";
System.Data.IDb Command dbCommand = new
System.Data.Sql Client.SqlComma nd();
dbCommand.Comma ndText = queryString;
dbCommand.Conne ction = dbConnection;
System.Data.IDa taParameter dbParam_au_id = new
System.Data.Sql Client.SqlParam eter();
dbParam_au_id.P arameterName = "@au_id";
dbParam_au_id.V alue = au_id;
dbParam_au_id.D bType = System.Data.DbT ype.String;
dbCommand.Param eters.Add(dbPar am_au_id);
System.Data.IDa taParameter dbParam_au_lnam e = new
System.Data.Sql Client.SqlParam eter();
dbParam_au_lnam e.ParameterName = "@au_lname" ;
dbParam_au_lnam e.Value = au_lname;
dbParam_au_lnam e.DbType = System.Data.DbT ype.String;
dbCommand.Param eters.Add(dbPar am_au_lname);
System.Data.IDa taParameter dbParam_au_fnam e = new
System.Data.Sql Client.SqlParam eter();
dbParam_au_fnam e.ParameterName = "@au_fname" ;
dbParam_au_fnam e.Value = au_fname;
dbParam_au_fnam e.DbType = System.Data.DbT ype.String;
dbCommand.Param eters.Add(dbPar am_au_fname);
System.Data.IDa taParameter dbParam_state = new
System.Data.Sql Client.SqlParam eter();
dbParam_state.P arameterName = "@state";
dbParam_state.V alue = state;
dbParam_state.D bType = System.Data.DbT ype.StringFixed Length;
dbCommand.Param eters.Add(dbPar am_state);
int rowsAffected = 0;
dbConnection.Op en();
try
{
rowsAffected = dbCommand.Execu teNonQuery();
}
finally
{
dbConnection.Cl ose();
}
return rowsAffected;
}
}
I really like the design of this and it seems fairly straight-forward to
follow. My question is, is this a normal scenario for access my data? I
mean, if I was query many tables and columns, would I basically have to
create a class entity for each of the queries that I'm running, similar to
what they did here for "Author" so items can be added to the "Author"
generic collection?
Also, why do they use type "string" when accessing integer fields within the
database. For example, they pass auth_id to the function as a string
instead of integer. What is the reasoning for this?
I would be interested in seeing more code similar to the above for me to use
as an example of anyone knows where I can obtain or any online resources or
books. There appears to not be much available.
I've also downloaded most of the ASP.NET 2.0 web site templates to look at
what they have.
Any help would be appreciated.
..