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 DataObjectSource 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.Collections.Generic;
public class AuthorsComponent
{
public AuthorsComponent ()
{
// TODO: Add constructor logic here
}
public List<Author> GetAuthorsByState (String state, String
sortExpression)
{
List<Author> authors = new List<Author> ();
DataSet ds = AuthorsDB.GetAuthorsByState (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(new AuthorComparer(sortExpression));
return authors;
}
public int UpdateAuthor (string ID, string LastName, string Name, string
State)
{
return AuthorsDB.UpdateAuthor (ID, LastName, Name, State);
}
public int UpdateAuthor(Author a)
{
return AuthorsDB.UpdateAuthor(a.ID, a.LastName, a.Name, a.State);
}
public List<String> GetStates()
{
List<String> states = new List<String>();
DataSet ds = AuthorsDB.GetStates();
foreach (DataRow row in ds.Tables[0].Rows)
{
states.Add((String)row["state"]);
}
return states;
}
}
public class AuthorComparer : IComparer<Author>
{
private string _sortColumn;
private bool _reverse;
public AuthorComparer(string sortExpression)
{
_reverse = sortExpression.ToLowerInvariant().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,
StringComparison.InvariantCultureIgnoreCase);
break;
case "Name":
retVal = String.Compare(a.Name, b.Name,
StringComparison.InvariantCultureIgnoreCase);
break;
case "LastName":
retVal = String.Compare(a.LastName, b.LastName,
StringComparison.InvariantCultureIgnoreCase);
break;
case "State":
retVal = String.Compare(a.State, b.State,
StringComparison.InvariantCultureIgnoreCase);
break;
}
return (retVal * (_reverse ? -1 : 1));
}
}
Data Access Layer:
using System;
using System.Configuration;
public class AuthorsDB
{
public AuthorsDB() { }
public static System.Data.DataSet GetAuthorsByState(string state)
{
string connectionString =
ConfigurationManager.ConnectionStrings["Pubs"].ConnectionString;
System.Data.IDbConnection dbConnection = new
System.Data.SqlClient.SqlConnection(connectionStri ng);
string queryString = "SELECT au_id, au_fname, au_lname, state FROM
[authors] WHERE ([authors].[state] = @state)";
System.Data.IDbCommand dbCommand = new
System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;
System.Data.IDataParameter dbParam_state = new
System.Data.SqlClient.SqlParameter();
dbParam_state.ParameterName = "@state";
dbParam_state.Value = state;
dbParam_state.DbType = System.Data.DbType.StringFixedLength;
dbCommand.Parameters.Add(dbParam_state);
System.Data.IDbDataAdapter dataAdapter = new
System.Data.SqlClient.SqlDataAdapter();
dataAdapter.SelectCommand = dbCommand;
System.Data.DataSet dataSet = new System.Data.DataSet();
dataAdapter.Fill(dataSet);
return dataSet;
}
public static System.Data.DataSet GetStates()
{
string connectionString =
ConfigurationManager.ConnectionStrings["Pubs"].ConnectionString;
System.Data.IDbConnection dbConnection = new
System.Data.SqlClient.SqlConnection(connectionStri ng);
string queryString = "SELECT DISTINCT [authors].state FROM
[authors]";
System.Data.IDbCommand dbCommand = new
System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;
System.Data.IDbDataAdapter dataAdapter = new
System.Data.SqlClient.SqlDataAdapter();
dataAdapter.SelectCommand = dbCommand;
System.Data.DataSet dataSet = new System.Data.DataSet();
dataAdapter.Fill(dataSet);
return dataSet;
}
public static int UpdateAuthor (string au_id, string au_lname, string
au_fname, string state)
{
string connectionString =
ConfigurationManager.ConnectionStrings["Pubs"].ConnectionString;
System.Data.IDbConnection dbConnection = new
System.Data.SqlClient.SqlConnection(connectionStri ng);
string queryString = "UPDATE [authors] SET [au_lname]=@au_lname,
[au_fname]=@au_fname, [state]=@state WHERE ([authors].[au_id] = @au_id)";
System.Data.IDbCommand dbCommand = new
System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;
System.Data.IDataParameter dbParam_au_id = new
System.Data.SqlClient.SqlParameter();
dbParam_au_id.ParameterName = "@au_id";
dbParam_au_id.Value = au_id;
dbParam_au_id.DbType = System.Data.DbType.String;
dbCommand.Parameters.Add(dbParam_au_id);
System.Data.IDataParameter dbParam_au_lname = new
System.Data.SqlClient.SqlParameter();
dbParam_au_lname.ParameterName = "@au_lname";
dbParam_au_lname.Value = au_lname;
dbParam_au_lname.DbType = System.Data.DbType.String;
dbCommand.Parameters.Add(dbParam_au_lname);
System.Data.IDataParameter dbParam_au_fname = new
System.Data.SqlClient.SqlParameter();
dbParam_au_fname.ParameterName = "@au_fname";
dbParam_au_fname.Value = au_fname;
dbParam_au_fname.DbType = System.Data.DbType.String;
dbCommand.Parameters.Add(dbParam_au_fname);
System.Data.IDataParameter dbParam_state = new
System.Data.SqlClient.SqlParameter();
dbParam_state.ParameterName = "@state";
dbParam_state.Value = state;
dbParam_state.DbType = System.Data.DbType.StringFixedLength;
dbCommand.Parameters.Add(dbParam_state);
int rowsAffected = 0;
dbConnection.Open();
try
{
rowsAffected = dbCommand.ExecuteNonQuery();
}
finally
{
dbConnection.Close();
}
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.
..
