PetShop的数据库访问(例子源码 I )

[AuthorsInfo.cs]

using System;

using System.Collections.Generic;

using System.Text;

namespace PetShop.Model {

public class AuthorsInfo {

private string au_id;

private string au_lname;

private string au_fname;

private string phone;

private string address;

private string city;

private string state;

private string zip;

private bool contract; //注: SQL Server中的bit类型(0/1),对应c#的bool类型(false/true)

public AuthorsInfo() { }

public AuthorsInfo(

string au_id,

string au_lname,

string au_fname,

string phone,

string address,

string city,

string state,

string zip,

bool contract){

this.au_id = au_id;

this.au_lname = au_lname;

this.au_fname = au_fname;

this.phone = phone;

this.address = address;

this.city = city;

this.state = state;

this.zip = zip;

this.contract = contract;

}

public string Au_id {

get{ return au_id; }

set{ au_id = value; }

}

public string Au_lname {

get{ return au_lname; }

set{ au_lname = value; }

}

public string Au_fname {

get{ return au_fname; }

set{ au_fname = value; }

}

public string Phone {

get{ return phone; }

set{ phone = value; }

}

public string Address {

get{ return address; }

set{ address = value; }

}

public string City {

get{ return city; }

set{ city = value; }

}

public string State {

get{ return state; }

set{ state = value; }

}

public string Zip {

get{ return zip; }

set{ zip = value; }

}

public bool Contract {

get{ return contract; }

set{ contract = value; }

}

}

}

[IAuthors.cs]

using System;

using System.Collections.Generic;

using System.Text;

using PetShop.Model;

namespace PetShop.IDAL {

public interface IAuthors {

/// <summary>

/// 读取全部Author信息

/// </summary>

/// <returns>查询结果的实体集的泛型接口</returns>

IList<AuthorsInfo> GetAllAuthors();

/// <summary>

/// 根据au_id读取author信息

/// </summary>

/// <param name="au_id">author的唯一标示</param>

/// <returns>表示Author的业务实体</returns>

AuthorsInfo GetAuthor(string au_id);

}

}

[Authors.cs]

using System;

using System.Collections.Generic;

using System.Text;

using PetShop.IDAL;

using PetShop.Model;

using System.Data.SqlClient;

using PetShop.DBUtility;

using System.Data;

using System.Configuration;

namespace PetShop.SQLServerDAL {

public class Authors : IAuthors {

// 静态常量

private const string SQL_SELECT_AUTHORS = "SELECT

authors.au_id,

authors.au_lname,

authors.au_fname,

authors.phone,

authors.address,

authors.city,

authors.state,

authors.zip,

authors.contract

FROM authors";

private const string PARM_AU_ID = "@au_id";

/// <summary>

/// 读取全部Author信息

/// </summary>

/// <returns>查询结果的实体集的泛型接口</returns>

public IList<AuthorsInfo> GetAllAuthors() {

//设置返回值

IList<AuthorsInfo> authorsInfos = new List<AuthorsInfo>();

//执行查询

using (

SqlDataReader rdr = SqlHelper.ExecuteReader(

SqlHelper.PubConnectionString,

CommandType.Text,

SQL_SELECT_AUTHORS,

null)

) {

AuthorsInfo item = null;

while (rdr.Read()) {

item = new AuthorsInfo(

rdr.GetString(0),

rdr.GetString(1),

rdr.GetString(2),

rdr.GetString(3),

rdr.GetString(4),

rdr.GetString(5),

rdr.GetString(6),

rdr.GetString(7),

rdr.GetBoolean(8));

authorsInfos.Add(item);

}

}

return authorsInfos;

}

/// <summary>

/// 根据au_id读取author信息

/// </summary>

/// <param name="au_id">author的唯一标示</param>

/// <returns>表示Author的业务实体</returns>

public AuthorsInfo GetAuthor(string au_id) {

//设置返回值

AuthorsInfo author = null;

//创建参数

SqlParameter parm = new SqlParameter(PARM_AU_ID, SqlDbType.VarChar, 10);

//绑定参数

parm.Value = au_id;

//执行查询

using (

SqlDataReader rdr = SqlHelper.ExecuteReader(

SqlHelper.PubConnectionString,

CommandType.Text,

SQL_SELECT_AUTHORS,

parm)

) {

if (rdr.Read())

author = new AuthorsInfo(

rdr.GetString(0),

rdr.GetString(1),

rdr.GetString(2),

rdr.GetString(3),

rdr.GetString(4),

rdr.GetString(5),

rdr.GetString(6),

rdr.GetString(7),

rdr.GetBoolean(8));

else

author = new AuthorsInfo();

}

return author;

}

}

}