ASP.NET部分代码示例

  1 using System;
  2 using System.Collections.Generic;
  3 using Model;
  4 using System.Data;
  5 using System.Data.SqlClient;
  6 
  7 
  8 namespace DAL
  9 {
 10     /// <summary>
 11     /// 发布活动信息
 12     /// </summary>
 13     public class ActivityBulletinDA
 14     {
 15         /// <summary>
 16         /// 发布活动
 17         /// </summary>
 18         /// <param name="activityBulletin"></param>
 19         /// <returns></returns>
 20         public static int Save(Model.ActivityBulletin activityBulletin)
 21         {
 22             if (activityBulletin.ID.HasValue)
 23             {
 24                 return Update(activityBulletin);
 25             }
 26             else
 27             {
 28                 return Insert(activityBulletin);
 29             }
 30          
 31         }
 32       
 33      
 34         /// <summary>
 35         /// 执行插入
 36         /// </summary>
 37         /// <param name="activityBulletin"></param>
 38         /// <returns></returns>
 39         internal static int Insert(Model.ActivityBulletin activityBulletin)
 40         {
 41             activityBulletin.ID = Guid.NewGuid();
 42             string sql = @" INSERT  INTO [dbo].[ActivityBulletin]
 43                                     ( 
 44                                       [ID],
 45                                       [AssociationsID],
 46                                       [Title] ,
 47                                       [Details] ,
 48                                       [Type] ,
 49                                       [SponsorEnterpriseName] ,
 50                                       [LinkMan],
 51                                       [Phone],
 52                                       [beginTime] ,
 53                                       [endTime] ,
 54                                       [imgName],
 55                                       [TIndex],
 56                                       [Recommend]
 57                                     )
 58                             VALUES  (
 59                                       @ID,
 60                                       @AssociationsID,
 61                                       @Title ,
 62                                       @Details ,
 63                                       @Type ,
 64                                       @SponsorEnterpriseName ,
 65                                       @LinkMan ,
 66                                       @Phone ,
 67                                       @beginTime ,
 68                                       @endTime ,
 69                                       @imgName,
 70                                       @TIndex,
 71                                       @Recommend
 72                                     )";
 73             List<SqlParameter> parms = new List<SqlParameter>();
 74             parms.Add(SqlHelper.CreateParameter("@ID", activityBulletin.ID));
 75             parms.Add(SqlHelper.CreateParameter("@AssociationsID", activityBulletin.AssociationsID));
 76             parms.Add(SqlHelper.CreateParameter("@Title", activityBulletin.Title));
 77             parms.Add(SqlHelper.CreateParameter("@Details", activityBulletin.Details));
 78             parms.Add(SqlHelper.CreateEnumParamter("@Type", activityBulletin.Type));
 79             parms.Add(SqlHelper.CreateParameter("@SponsorEnterpriseName", activityBulletin.SponsorEnterpriseName));
 80             parms.Add(SqlHelper.CreateParameter("@LinkMan", activityBulletin.LinkMan));
 81             parms.Add(SqlHelper.CreateParameter("@Phone", activityBulletin.Phone));
 82             parms.Add(SqlHelper.CreateParameter("@beginTime", activityBulletin.BeginTime));
 83             parms.Add(SqlHelper.CreateParameter("@endTime", activityBulletin.EndTime));
 84             parms.Add(SqlHelper.CreateParameter("@imgName", activityBulletin.IMGName));
 85             parms.Add(SqlHelper.CreateParameter("@TIndex", 99));
 86             parms.Add(SqlHelper.CreateParameter("@Recommend", 0));
 87             
 88             string connStr = SqlConnectionHelper.GetConnection();
 89             int resultCount = SqlHelper.ExecuteNonQuery(connStr, CommandType.Text, sql, parms.ToArray());
 90             return resultCount;
 91         }
 92 
 93         /// <summary>
 94         /// 执行更新
 95         /// </summary>
 96         /// <param name="activityBulletin"></param>
 97         /// <returns></returns>
 98         public static int Update( Model.ActivityBulletin activityBulletin)
 99         {
100             string sql = @" UPDATE [dbo].[ActivityBulletin]
101                SET 
102                   [title] = ISNULL(@title, title),
103                   [Details] =ISNULL(@Details, Details),
104                   [Type] = ISNULL(@Type, Type),
105                   [SponsorEnterpriseName] = ISNULL(@SponsorEnterpriseName, SponsorEnterpriseName),
106                   [LinkMan] = ISNULL(@LinkMan, LinkMan),
107                   [Phone] =ISNULL(@Phone, Phone), 
108                   [beginTime]=ISNULL(@beginTime, beginTime),
109                   [endTime]=ISNULL(@endTime, endTime),
110                   [IMGName]=ISNULL(@IMGName, IMGName),
111                   [TIndex]=ISNULL(@TIndex, TIndex),
112                   [Recommend]=ISNULL(@Recommend, Recommend)
113                   WHERE ID=@ID";
114 
115             List<SqlParameter> parms = new List<SqlParameter>();
116             parms.Add(SqlHelper.CreateParameter("@ID", activityBulletin.ID));
117             parms.Add(SqlHelper.CreateParameter("@title", activityBulletin.Title));
118             parms.Add(SqlHelper.CreateParameter("@Details", activityBulletin.Details));
119             parms.Add(SqlHelper.CreateEnumParamter("@Type", activityBulletin.Type));
120             parms.Add(SqlHelper.CreateParameter("@SponsorEnterpriseName", activityBulletin.SponsorEnterpriseName));
121             parms.Add(SqlHelper.CreateParameter("@LinkMan", activityBulletin.LinkMan));
122             parms.Add(SqlHelper.CreateParameter("@beginTime", activityBulletin.BeginTime));
123             parms.Add(SqlHelper.CreateParameter("@endTime", activityBulletin.EndTime));
124             parms.Add(SqlHelper.CreateParameter("@Phone", activityBulletin.Phone));
125             parms.Add(SqlHelper.CreateParameter("@IMGName", activityBulletin.IMGName));
126             parms.Add(SqlHelper.CreateParameter("@TIndex", activityBulletin.TIndex));
127             parms.Add(SqlHelper.CreateParameter("@Recommend", activityBulletin.Recommend));
128 
129             string connStr = SqlConnectionHelper.GetConnection();
130             int resultCount = SqlHelper.ExecuteNonQuery(connStr, CommandType.Text, sql, parms.ToArray());
131             return resultCount;
132         }
133 
134         public List<Model.ActivityBulletin> GetInfo(ActivityBulletinQueryEntity query)
135         {
136             return GetActivityBulletinList(query);
137         }
138 
139         public static int Delete(Guid? id)
140         {
141             int count = 0;
142             if (id.HasValue)
143             {
144                 string sql = "delete from [ActivityBulletin]  where [ID]=@ID";
145                 List<SqlParameter> parms = new List<SqlParameter>();
146                 parms.Add(SqlHelper.CreateParameter("@ID", id));
147                 string connStr = SqlConnectionHelper.GetConnection();
148                 count = SqlHelper.ExecuteNonQuery(connStr, CommandType.Text, sql, parms.ToArray());
149             }
150             return count;
151         }
152 
153         /// <summary>
154         /// 根据查询条件获取活动信息集合
155         /// </summary>
156         /// <returns></returns>
157         public static List<Model.ActivityBulletin> GetActivityBulletinList(ActivityBulletinQueryEntity query)
158         {
159             List<Model.ActivityBulletin> result = new List<Model.ActivityBulletin>();
160             string tempStr = string.Empty;
161             List<SqlParameter> parms = new List<SqlParameter>();
162             parms.Add(SqlHelper.CreateParameter("@RowCount", query.TotalRecord));
163             parms[0].Direction = ParameterDirection.Output;
164             parms.Add(SqlHelper.CreateParameter("@PageIndex", query.PageIndex));
165             parms.Add(SqlHelper.CreateParameter("@PageSize", query.PageSize));
166             parms.Add(SqlHelper.CreateEnumParamter("@Type", query.Type));
167             parms.Add(SqlHelper.CreateEnumParamter("@ID", query.ActivityID));
168             parms.Add(SqlHelper.CreateParameter("@AssociationsID", query.AssociationsID));
169             parms.Add(SqlHelper.CreateParameter("@TIndex", query.TIndex));
170             parms.Add(SqlHelper.CreateParameter("@BlurInfo", query.BlurInfo));
171             parms.Add(SqlHelper.CreateParameter("@EndTime", query.EndTime));
172             string connStr = SqlConnectionHelper.GetConnection();
173             using (IDataReader reader = SqlHelper.ExecuteReader(connStr, CommandType.StoredProcedure, "Proc_GetActivityBulletinList", parms.ToArray()))
174             {
175                 while (reader.Read())
176                 {
177                     result.Add(new Model.ActivityBulletin
178                     {
179                         Title = SqlHelper.ReadNullableString(reader, 1),
180                         IMGName = SqlHelper.ReadNullableString(reader, 2),
181                         Details = SqlHelper.ReadNullableString(reader, 3),
182                         ID = SqlHelper.ReadNullableValue<Guid>(reader, 4),
183                         BeginTime = SqlHelper.ReadNullableValue<DateTime>(reader, 5),
184                         Type = SqlHelper.ReadNullableEnum<AssociationsTypeEnum>(reader, 6),
185                         SponsorEnterpriseName= SqlHelper.ReadNullableString(reader, 7),
186                         LinkMan = SqlHelper.ReadNullableString(reader, 8),
187                         Phone = SqlHelper.ReadNullableString(reader, 9),
188                         EndTime = SqlHelper.ReadNullableValue<DateTime>(reader, 10),
189                         Recommend = SqlHelper.ReadNullableValue<int>(reader, 11),
190                         AssociationsID = SqlHelper.ReadNullableValue<Guid>(reader,12),
191                         TIndex = SqlHelper.ReadNullableValue<int>(reader,13)
192                     });
193                 }
194             }
195             query.TotalRecord = (int)parms[0].Value;
196             return result;
197         }
198         /// <summary>
199         /// 前10活动信息集合
200         /// </summary>
201         /// <returns></returns>
202         public static List<Model.ActivityBulletin> GetActivityBulletinList()
203         {
204             List<Model.ActivityBulletin> result = new List<Model.ActivityBulletin>();
205             string sql = @" SELECT top 10 id,title,imgName,Details,beginTime,Type,SponsorEnterpriseName,
206                                 LinkMan,Phone,endTime,Recommend,AssociationsID,TIndex FROM 
207                             [dbo].[ActivityBulletin] order by TIndex";
208             string connStr = SqlConnectionHelper.GetConnection();
209             using (IDataReader reader = SqlHelper.ExecuteReader(connStr, CommandType.Text, sql, null))
210             {
211                 while (reader.Read())
212                 {
213                     result.Add(new Model.ActivityBulletin
214                     {
215                         ID = SqlHelper.ReadNullableValue<Guid>(reader, 0),
216                         Title = SqlHelper.ReadNullableString(reader, 1),
217                         IMGName = SqlHelper.ReadNullableString(reader, 2),
218                         Details = SqlHelper.ReadNullableString(reader, 3),
219                         BeginTime = SqlHelper.ReadNullableValue<DateTime>(reader, 4),
220                         Type = SqlHelper.ReadNullableEnum<AssociationsTypeEnum>(reader, 5),
221                         SponsorEnterpriseName = SqlHelper.ReadNullableString(reader, 6),
222                         LinkMan = SqlHelper.ReadNullableString(reader, 7),
223                         Phone = SqlHelper.ReadNullableString(reader, 8),
224                         EndTime = SqlHelper.ReadNullableValue<DateTime>(reader, 9),
225                         Recommend = SqlHelper.ReadNullableValue<int>(reader, 10),
226                         AssociationsID = SqlHelper.ReadNullableValue<Guid>(reader, 11),
227                         TIndex = SqlHelper.ReadNullableValue<int>(reader, 12)
228                     });
229                 }
230             }
231             return result;
232         }
233         /// <summary>
234         /// 活动信息集合根据时间
235         /// </summary>
236         /// <returns></returns>
237         public static List<Model.ActivityBulletin> GetActivityBulletinList(int count)
238         {
239             List<Model.ActivityBulletin> result = new List<Model.ActivityBulletin>();
240             string sql = string.Format(@" SELECT top {0} id,title FROM 
241                             [dbo].[ActivityBulletin] order by InDate Desc",count);
242             string connStr = SqlConnectionHelper.GetConnection();
243             using (IDataReader reader = SqlHelper.ExecuteReader(connStr, CommandType.Text, sql, null))
244             {
245                 while (reader.Read())
246                 {
247                     result.Add(new Model.ActivityBulletin
248                     {
249                         ID = SqlHelper.ReadNullableValue<Guid>(reader, 0),
250                         Title = SqlHelper.ReadNullableString(reader, 1)
251                     });
252                 }
253             }
254             return result;
255         }
256         public static List<ActivityBulletin> GetMyActivityBulletinList(ActivityBulletinQueryEntity query)
257         {
258             List<Model.ActivityBulletin> result = new List<Model.ActivityBulletin>();
259             string tempStr = string.Empty;
26
294             List<SqlParameter> parms = new List<SqlParameter>();
295             parms.Add(SqlHelper.CreateParameter("@RowCount", query.TotalRecord));
296             parms[0].Direction = ParameterDirection.Output;
297             parms.Add(SqlHelper.CreateParameter("@PageIndex", query.PageIndex));
298             parms.Add(SqlHelper.CreateParameter("@PageSize", query.PageSize));
299             parms.Add(SqlHelper.CreateEnumParamter("@Type", query.Type));
300             parms.Add(SqlHelper.CreateEnumParamter("@ID", query.ActivityID));
301             parms.Add(SqlHelper.CreateParameter("@AssociationsID", query.AssociationsID));
302             parms.Add(SqlHelper.CreateParameter("@TIndex", query.TIndex));
303             parms.Add(SqlHelper.CreateParameter("@BlurInfo", query.BlurInfo));
304             parms.Add(SqlHelper.CreateParameter("@EndTime", query.EndTime));
305             parms.Add(SqlHelper.CreateParameter("@BeginTime", query.BeginTime));
306 
307             string connStr = SqlConnectionHelper.GetConnection();
308             using (IDataReader reader = SqlHelper.ExecuteReader(connStr, CommandType.StoredProcedure, "Proc_GetMyActivityBulletinList", parms.ToArray()))
309             {
310                 while (reader.Read())
311                 {
312                     result.Add(new Model.ActivityBulletin
313                     {
314                         Title = SqlHelper.ReadNullableString(reader, 1),
315                         IMGName = SqlHelper.ReadNullableString(reader, 2),
316                         Details = SqlHelper.ReadNullableString(reader, 3),
317                         ID = SqlHelper.ReadNullableValue<Guid>(reader, 4),
318                         BeginTime = SqlHelper.ReadNullableValue<DateTime>(reader, 5),
319                         Type = SqlHelper.ReadNullableEnum<AssociationsTypeEnum>(reader, 6),
320                         SponsorEnterpriseName = SqlHelper.ReadNullableString(reader, 7),
321                         LinkMan = SqlHelper.ReadNullableString(reader, 8),
322                         Phone = SqlHelper.ReadNullableString(reader, 9),
323                         EndTime = SqlHelper.ReadNullableValue<DateTime>(reader, 10),
324                         Recommend = SqlHelper.ReadNullableValue<int>(reader, 11),
325                         AssociationsID = SqlHelper.ReadNullableValue<Guid>(reader, 12),
326                         TIndex = SqlHelper.ReadNullableValue<int>(reader, 13)
327                     });
328                 }
329             }
330             query.TotalRecord = (int)parms[0].Value;
331             return result;
332         }
333     }
334 }