Mercurial > pub > bltoolkit

comparison Demo/Asp.Net/BusinessLogic/DataAccess/ProfileAccessor.cs @ 0:f990fcb411a9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Копия текущей версии из github
author cin
date Thu, 27 Mar 2014 21:46:09 +0400
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:f990fcb411a9
1 using System;
2 using System.Collections.Generic;
3
4 using BLToolkit.Data;
5 using BLToolkit.DataAccess;
6
7 namespace PetShop.BusinessLogic.DataAccess
8 {
9 using ObjectModel;
10
11 public abstract class ProfileAccessor : AccessorBase<ProfileAccessor.DB, ProfileAccessor>
12 {
13 public class DB : DbManager { public DB() : base("ProfileDB") {} }
14
15 [SqlQuery(@"
16 SELECT
17 UniqueID
18 FROM
19 Profiles
20 WHERE
21 Username = @userName AND ApplicationName = @appName")]
22 public abstract int? GetUniqueID(string @userName, string @appName);
23
24 [SqlQuery(@"
25 SELECT
26 UniqueID
27 FROM
28 Profiles
29 WHERE
30 Username = @userName AND ApplicationName = @appName AND IsAnonymous != @isAuthenticated")]
31 public abstract int? GetUniqueIDAuth(string @userName, string @appName, bool @isAuthenticated);
32
33 [SqlQuery(@"
34 INSERT INTO Profiles (
35 Username, ApplicationName, LastActivityDate, LastUpdatedDate, IsAnonymous
36 ) Values (
37 @userName, @appName, getdate(), getdate(), CASE WHEN @isAuthenticated = 1 THEN 0 ELSE 1 END
38 )
39
40 SELECT SCOPE_IDENTITY()")]
41 public abstract int CreateProfile(string @userName, string @appName, bool @isAuthenticated);
42
43 [SqlQuery(@"
44 SELECT
45 a.FirstName as ToFirstName,
46 a.LastName as ToLastName,
47 a.Address1 as Addr1,
48 a.Address2 as Addr2,
49 a.City,
50 a.State,
51 a.Zip,
52 a.Country,
53 a.Email,
54 a.Phone
55 FROM
56 Account a
57 JOIN Profiles p ON p.UniqueID = a.UniqueID
58 WHERE
59 p.Username = @userName AND p.ApplicationName = @appName;")]
60 public abstract Address GetAccountInfo(string @userName, string @appName);
61
62 [SqlQuery(@"
63 SELECT
64 c.ItemId,
65 c.Name,
66 c.Type,
67 c.Price,
68 c.CategoryId,
69 c.ProductId,
70 c.Quantity
71 FROM
72 Profiles p
73 JOIN Cart c ON c.UniqueID = p.UniqueID
74 WHERE
75 p.Username = @userName AND
76 p.ApplicationName = @appName AND
77 c.IsShoppingCart = @isShoppingCart")]
78 public abstract IList<CartItem> GetCartItems(string @userName, string @appName, bool @isShoppingCart);
79
80 [SqlQuery(@"
81 DELETE FROM Account WHERE UniqueID = @uniqueID
82
83 INSERT INTO Account (
84 UniqueID, Email, FirstName, LastName, Address1, Address2, City, State, Zip, Country, Phone
85 ) VALUES (
86 @uniqueID, @Email, @ToFirstName, @ToLastName, @Addr1, @Addr2, @City, @State, @Zip, @Country, @Phone
87 )")]
88 public abstract void SetAccountInfo(int @uniqueID, Address address);
89
90 // This method is not abstract as BLToolkit does not generate methods for the ExecuteForEach method.
91 // It's virtual as we want to get statistic info for this method.
92 // Counter and Log aspects wrap all abstract, virtual, and override members.
93 //
94 public virtual void SetCartItems(int uniqueID, ICollection<CartItem> cartItems, bool isShoppingCart)
95 {
96 using (DbManager db = GetDbManager())
97 {
98 db.BeginTransaction();
99
100 db
101 .SetCommand(@"
102 DELETE FROM
103 Cart
104 WHERE
105 UniqueID = @uniqueID AND IsShoppingCart = @isShoppingCart",
106 db.Parameter("@uniqueID", uniqueID),
107 db.Parameter("@isShoppingCart", isShoppingCart))
108 .ExecuteNonQuery();
109
110 if (cartItems.Count > 0)
111 {
112 db
113 .SetCommand(@"
114 INSERT INTO Cart (
115 UniqueID, ItemId, Name, Type, Price, CategoryId, ProductId, IsShoppingCart, Quantity
116 ) VALUES (
117 @uniqueID, @ItemId, @Name, @Type, @Price, @CategoryId, @ProductId, @isShoppingCart, @Quantity
118 )",
119 db.CreateParameters(typeof(CartItem),
120 db.Parameter("@uniqueID", uniqueID),
121 db.Parameter("@isShoppingCart", isShoppingCart)))
122 .ExecuteForEach(cartItems);
123 }
124
125 db.CommitTransaction();
126 }
127 }
128
129 [SqlQuery(@"
130 UPDATE
131 Profiles
132 SET
133 LastActivityDate = getdate()
134 WHERE
135 Username = @userName AND ApplicationName = @appName")]
136 public abstract void UpdateActivityDate(string @userName, string @appName);
137
138 [SqlQuery(@"
139 UPDATE
140 Profiles
141 SET
142 LastActivityDate = getdate(),
143 LastUpdatedDate = getdate()
144 WHERE
145 Username = @userName AND ApplicationName = @appName")]
146 public abstract void UpdateActivityAndUdpateDates(string @userName, string @appName);
147
148 [SqlQuery(@"DELETE FROM Profiles WHERE UniqueID = @uniqueID")]
149 [ScalarSource(ScalarSourceType.AffectedRows)]
150 public abstract int DeleteProfile(int @uniqueID);
151
152 [SqlQuery(@"
153 SELECT
154 Username
155 FROM
156 Profiles
157 WHERE ApplicationName = @appName AND LastActivityDate <= @userInactiveSinceDate")]
158 public abstract IList<string> GetInactiveProfiles(DateTime userInactiveSinceDate, string appName);
159
160 [SqlQuery(@"
161 SELECT
162 Username
163 FROM
164 Profiles
165 WHERE ApplicationName = @appName AND LastActivityDate <= @userInactiveSinceDate AND IsAnonymous = @isAnonymous")]
166 public abstract IList<string> GetInactiveProfiles(DateTime @userInactiveSinceDate, string @appName, bool @isAnonymous);
167
168 const string _profileQuery = @"
169 FROM
170 Profiles
171 WHERE
172 ApplicationName = @appName AND
173 (@isAnonymous IS NULL OR IsAnonymous = @isAnonymous) AND
174 (@userName IS NULL OR Username LIKE @userName) AND
175 (@userInactiveSinceDate IS NULL OR LastActivityDate >= @userInactiveSinceDate)";
176
177 [SqlQuery(@"
178 SELECT @totalRecords = Count(*)" + _profileQuery + @"
179 SELECT *" + _profileQuery)]
180 public abstract IList<CustomProfile> GetProfile(
181 bool? @isAnonymous, string @userName, DateTime? @userInactiveSinceDate, string @appName, out int @totalRecords);
182 }
183 }