[ASP.NET 设计模式] 用Visual Studio2010搭建一个简单的WCF分层结构示例Step by Step —— 03 数据访问层

注：本例来自图书Professional ASP.NET Design Pattern，该书的亚马逊链接为：http://www.amazon.com/Professional-ASP-NET-Design-Patterns-Millett/dp/0470292784/ref=sr_1_1?ie=UTF8&qid=1296479229&sr=8-1

转载请注明本文来自博客园 http://www.cnblogs.com/charrli

本文源自于该书第六章的一个SOA示例。

 

这一层是实现在Model里面定义的IEventRepository接口，实现数据的persist而编写的。

首先在Solution的Service文件夹下面新建一个ASPPatterns.Chap6.EventTickets.Repository的project。这个Project里面只有一个类。其内容如下：

 

public class EventRepository : IEventRepository
{
    private string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\EventTickets.mdf;Integrated Security=True;User Instance=True";

    public Event FindBy(Guid id)
    {
        Event Event = default(Event);

        string queryString = "SELECT * FROM dbo.Events WHERE Id = @EventId " +
                             "SELECT * FROM dbo.PurchasedTickets WHERE EventId = @EventId " +                               
                             "SELECT * FROM dbo.ReservedTickets WHERE EventId = @EventId;";                

        using (SqlConnection connection =
               new SqlConnection(connectionString))
        {
            SqlCommand command = connection.CreateCommand();
            command.CommandText = queryString;

            SqlParameter Idparam = new SqlParameter("@EventId", id.ToString());
            command.Parameters.Add(Idparam);

            connection.Open();

            using (SqlDataReader reader = command.ExecuteReader())
            {                  
                if (reader.HasRows)
                {
                    reader.Read();
                    Event = new Event();
                    Event.PurchasedTickets = new List<TicketPurchase>();
                    Event.ReservedTickets = new List<TicketReservation>();
                    Event.Allocation = int.Parse(reader["Allocation"].ToString());
                    Event.Id = new Guid(reader["Id"].ToString());
                    Event.Name = reader["Name"].ToString();

                    if (reader.NextResult())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                TicketPurchase ticketPurchase = new TicketPurchase();
                                ticketPurchase.Id = new Guid(reader["Id"].ToString());
                                ticketPurchase.Event = Event;                                   
                                ticketPurchase.TicketQuantity = int.Parse(reader["TicketQuantity"].ToString());
                                Event.PurchasedTickets.Add(ticketPurchase);
                            }
                        }
                    }

                    if (reader.NextResult())                                               
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                TicketReservation ticketReservation = new TicketReservation();
                                ticketReservation.Id = new Guid(reader["Id"].ToString());
                                ticketReservation.Event = Event;
                                ticketReservation.ExpiryTime = DateTime.Parse(reader["ExpiryTime"].ToString());
                                ticketReservation.TicketQuantity = int.Parse(reader["TicketQuantity"].ToString());
                                ticketReservation.HasBeenRedeemed = bool.Parse(reader["HasBeenRedeemed"].ToString());
                                Event.ReservedTickets.Add(ticketReservation);
                            }
                        }                           
                    }                       
                }                   
            }
        }

        return Event;
    }

    public void Save(Event Event)
    {
        // Code to save the Event entity
        // is not required in this senario

        RemovePurchasedAndReservedTicketsFrom(Event);           

        InsertPurchasedTicketsFrom(Event);
        InsertReservedTicketsFrom(Event);
      
    }

    public void InsertReservedTicketsFrom(Event Event)
    {
        string insertSQL = "INSERT INTO ReservedTickets " +
                           "(Id, EventId, TicketQuantity, ExpiryTime, HasBeenRedeemed) " +
                           "VALUES " +
                           "(@Id, @EventId, @TicketQuantity, @ExpiryTime, @HasBeenRedeemed);";                              

        foreach (TicketReservation ticket in Event.ReservedTickets)
        {
            using (SqlConnection connection =
                  new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = insertSQL;

                SqlParameter Idparam = new SqlParameter("@Id", ticket.Id.ToString());
                command.Parameters.Add(Idparam);

                SqlParameter EventIdparam = new SqlParameter("@EventId", ticket.Event.Id.ToString());
                command.Parameters.Add(EventIdparam);                   

                SqlParameter TktQtyparam = new SqlParameter("@TicketQuantity", ticket.TicketQuantity);
                command.Parameters.Add(TktQtyparam);

                SqlParameter Expiryparam = new SqlParameter("@ExpiryTime", ticket.ExpiryTime);
                command.Parameters.Add(Expiryparam);

                SqlParameter HasBeenRedeemedparam = new SqlParameter("@HasBeenRedeemed", ticket.HasBeenRedeemed);
                command.Parameters.Add(HasBeenRedeemedparam);
               
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

    }

    public void InsertPurchasedTicketsFrom(Event Event)
    {
        string insertSQL = "INSERT INTO PurchasedTickets " +
                           "(Id, EventId, TicketQuantity) " +
                           "VALUES " +
                           "(@Id, @EventId, @TicketQuantity);";                           

        foreach (TicketPurchase ticket in Event.PurchasedTickets)
        {
             using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = insertSQL;

                SqlParameter Idparam = new SqlParameter("@Id", ticket.Id.ToString());
                command.Parameters.Add(Idparam);

                SqlParameter EventIdparam = new SqlParameter("@EventId", ticket.Event.Id.ToString());
                command.Parameters.Add(EventIdparam);
               
                SqlParameter TktQtyparam = new SqlParameter("@TicketQuantity", ticket.TicketQuantity);
                command.Parameters.Add(TktQtyparam);

                connection.Open();
                command.ExecuteNonQuery();
            }
        }          
    }

    public void RemovePurchasedAndReservedTicketsFrom(Event Event)
    {
        string deleteSQL = "DELETE PurchasedTickets WHERE EventId = @EventId; " +
                           "DELETE ReservedTickets WHERE EventId = @EventId;";                               

        using (SqlConnection connection =
               new SqlConnection(connectionString))
        {
            SqlCommand command = connection.CreateCommand();
            command.CommandText = deleteSQL;

            SqlParameter Idparam = new SqlParameter("@EventId", Event.Id.ToString());
            command.Parameters.Add(Idparam);

            connection.Open();
            command.ExecuteNonQuery();
        }       
    }       
}

 

可见，这个类总共提供了五种方法。

对于FindBy方法，其实现原理是：

1. 首先注意其Select语句包含三个Select，这个查询的结果将分别包含三张表，分别是Event，PurchasedTickets和ReservedTickets。其中Event表最多只有一条记录，因为Event表中每条记录都有一个独特的GUID表明自己代表的此次售票这个事件Event是什么。

2. FindBy方法的目的，就是要根据传入的事件的GUID，返回一个Event对象。对于Event类，我们都知道它的ID、Name和Allocation都可以直接查Event表得到，但是其指向Behavior的两个List的内容，却不能得到，因此需要查TicketPurchase的表和TicketReservation的表。

 

对于Save方法，可见其内部实际上能够使分别调用了其他三个方法来实现的。这三个方法分别是

1. 删除Purchase/Reservation两张表中所有该EventID的记录；

2. 把Event对象的List<TicketPurchase>记录的所有事件插入；

3. 把Event对象的List<TicketReservation>记录的所有事件插入。