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.ActivityBulletin178                     {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.ActivityBulletin214                     {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.ActivityBulletin248                     {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;26294             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.ActivityBulletin313                     {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 }