1.创建数据层
创建对客户进行操作的存储过程.存储过程是数据层的一部分,可以在业务层中访问它,以实现表示层的某个地方要执行的功能.这里不再说明把数据访问逻辑放在单独一层的优点.
在Store数据库中创建存储过程GetCustomerByEmail,该存储过程把登录页面中输入的EMAIL作为参数,查找EMAIL地址为该参数值的客户,然后以输出参数的形式返回该客户的ID,名字,密码 ,地址,国家,电话和传真.代码如下:
- create procedure GetCustomerByEmail
-
- (
-
- @Email nvarchar(50) = null,
-
- @CustomerID int OUTPUT,
-
- @Name nvarchar(50) OUTPUT,
-
- @password nvarchar(50) OUTPUT,
-
- @Address nvarchar(250) OUTPUT,
-
- @Country nvarchar(250) OUTPUT,
-
- @PhoneNumber nvarchar(250) OUTPUT,
-
- @Fax nvarchar(250) OUTPUT
-
- )
-
- AS
-
- SET NOCOUNT ON
-
- SELECT
-
- @CustomerID = c.CutomerID,
-
- @Name = c.FullName,
-
- @Password = c.Password,
-
- @Address = a.Address,
-
- @Country = a.Country,
-
- @PhoneNumber = a.PhoneNumber,
-
- @Fax = a.Fax
-
- FROM Customers c,Addresses a
-
- WHERE c.EmailAddree = @Email
-
- AND a.CustomerId = c.CustomerId
-
- IF @@Rowcount < 1
-
- SELECT
-
- @CustomerID = 0
-
- GO
-
- (
-
- @Email nvarchar(50) = null,
-
- @CustomerID int OUTPUT,
-
- @Name nvarchar(50) OUTPUT,
-
- @password nvarchar(50) OUTPUT,
-
- @Address nvarchar(250) OUTPUT,
-
- @Country nvarchar(250) OUTPUT,
-
- @PhoneNumber nvarchar(250) OUTPUT,
-
- @Fax nvarchar(250) OUTPUT
-
- )
-
- AS
-
- SET NOCOUNT ON
-
- SELECT
-
- @CustomerID = c.CutomerID,
-
- @Name = c.FullName,
-
- @Password = c.Password,
-
- @Address = a.Address,
-
- @Country = a.Country,
-
- @PhoneNumber = a.PhoneNumber,
-
- @Fax = a.Fax
-
- FROM Customers c,Addresses a
-
- WHERE c.EmailAddree = @Email
-
- AND a.CustomerId = c.CustomerId
-
- IF @@Rowcount < 1
-
- SELECT
-
- @CustomerID = 0
-
- GO
2.创建业务层
业务层是存取数据的类,该类与数据层和表示层(用户界面)交户作用,在需要时从表示层获得数据,并将数据存入数据库,也可以从数据层获得数据,并将数据发送给表示层(用户界面).
(1)在"解决方案资源管理器"中,选择解决方案下的GoShop项目,右击,再将鼠标指向"添加",然后选择"添加类"命令.
(2)在"类别"窗口中选择"本地项目",然后在"模板"窗口中选择"代码文件".在"名称"文本框中,输入CustomerDB.
(3)单击"打开",代码如下:
- using System;
-
- using System.Data;
-
- using System.Configuration;
-
- using System.Web;
-
- using System.Web.Security;
-
- using System.Web.UI;
-
- using System.Web.UI.WebControls;
-
- using System.Web.UI.WebControls.WebParts;
-
- using System.Web.UI.HtmlControls;
-
- using System.Data.SqlClient;
-
-
-
- namespace Goshop{
-
-
-
-
-
-
-
-
-
- public class Customer
-
- {
-
-
-
- public string Name;
-
- public string Email;
-
- public string Password;
-
- public string Country;
-
- public string Addresss;
-
- public string PhoneNumber;
-
- public string Fax;
-
- public int CustomerID;
-
-
-
- }
-
- public class CustomerDB
-
- {
-
- private string ConnectionString
-
- {
-
- get
-
- {
-
- string strConn;
-
- strConn = "Data Source =(local);";
-
- strConn +="Initial Catalog = Store;";
-
- return strConn ;
-
- }
-
- }
-
- public Customer GetCustomerByEmail(string Email)
-
- {
-
- SqlConnection myconnection = new SqlConnection(ConnectionString);
-
- SqlCommand mycommand = new SqlCommand("GetCustomerByEmail",myconnection);
-
- mycommand.CommandType = CommandType.StoredProcedure;
-
- SqlParameter parameterCustomerID = new SqlParameter("@CustomerID",SqlDbType.Int,4);
-
- parameterCustomerID.Direction = ParameterDirection.Output;
-
- mycommand.Parameters.Add(parameterCustomerID);
-
- SqlParameter parameterFullName = new SqlParameter("@Name",SqlDbType.NVarChar,50);
-
- parameterFullName.Direction = ParameterDirection.Output;
-
- mycommand.Parameters.Add(parameterFullName);
-
- SqlParameter parameterEmail = new SqlParameter("@Email",SqlDbType.NVarChar,50);
-
- parameterEmail.Value = Email;
-
- mycommand.Parameters.Add(parameterEmail);
-
- SqlParameter parameterPassword = new SqlParameter("@Password",SqlDbType.NVarChar,50);
-
- parameterPassword.Direction = ParameterDirection.Output;
-
- mycommand.Parameters.Add(parameterPassword);
-
- SqlParameter parameterAddress = new SqlParameter("@Address",SqlDbType.NVarChar,255);
-
- parameterAddress.Direction = ParameterDirection.Output;
-
- mycommand.Parameters.Add(parameterAddress);
-
- SqlParameter parameterCountry = new SqlParameter("@Country",SqlDbType.NVarChar,40);
-
- parameterCountry.Direction = ParameterDirection.Output;
-
- mycommand.Parameters.Add(parameterCountry);
-
- SqlParameter parameterPhoneNumber = new SqlParameter("@PhoneNuber",SqlDbType.NVarChar,30);
-
- parameterPhoneNumber.Direction = ParameterDirection.Output;
-
- mycommand.Parameters.Add(parameterPhoneNumber);
-
- SqlParameter parameterFax = new SqlParameter("@Fax",SqlDbType.NVarChar,30);
-
- parameterFax.Direction = ParameterDirection.Output;
-
- mycommand.Parameters.Add(parameterFax);
-
- myconnection.Open();
-
- mycommand.ExecuteNonQuery();
-
- myconnection.Close();
-
- int customerId =(int)(parameterCustomerID.Value);
-
- if(customerId == 0)
-
- {
-
- return null;
-
- }
-
- else
-
- {
-
- Customer myCustomer = new Customer();
-
- myCustomer.Name = (string)parameterFullName.Value;
-
- myCustomer.Password = (string)parameterPassword.Value;
-
- myCustomer.Email = (string)parameterEmail.Value;
-
- myCustomer.Addresss = (string)parameterAddress.Value;
-
- myCustomer.Country = (string)parameterCountry.Value;
-
- myCustomer.PhoneNumber = (string)parameterPhoneNumber.Value;
-
- myCustomer.Fax = (string)parameterFax.Value;
-
- myCustomer.CustomerID = customerId;
-
- return myCustomer;
-
- }
-
-
-
- }
-
- }
-
- }
|