public IList <ActivitySignUpInfo> GetList()
{
StringBuilder sb = new StringBuilder(250);
sb.Append(@"select Id,ActivityId,UserId,Remark,LastUpdatedDate
from ActivitySignUp
order by LastUpdatedDate desc " );
IList <ActivitySignUpInfo> list = new List <ActivitySignUpInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.HnztcTeamDbConnString, CommandType.Text, sb.ToString()))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
ActivitySignUpInfo model = new ActivitySignUpInfo();
model.Id = reader.GetGuid(0);
model.ActivityId = reader.GetGuid(1);
model.UserId = reader.GetGuid(2);
model.Remark = reader.GetString(3);
model.LastUpdatedDate = reader.GetDateTime(4);
list.Add(model);
}
}
}
return(list);
}
public ActivitySignUpInfo GetModel(object Id)
{
ActivitySignUpInfo model = null;
StringBuilder sb = new StringBuilder(300);
sb.Append(@"select top 1 Id,ActivityId,UserId,Remark,LastUpdatedDate
from ActivitySignUp
where Id = @Id " );
SqlParameter parm = new SqlParameter("@Id", SqlDbType.UniqueIdentifier);
parm.Value = Guid.Parse(Id.ToString());
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.HnztcTeamDbConnString, CommandType.Text, sb.ToString(), parm))
{
if (reader != null)
{
while (reader.Read())
{
model = new ActivitySignUpInfo();
model.Id = reader.GetGuid(0);
model.ActivityId = reader.GetGuid(1);
model.UserId = reader.GetGuid(2);
model.Remark = reader.GetString(3);
model.LastUpdatedDate = reader.GetDateTime(4);
}
}
}
return(model);
}
public IList <ActivitySignUpInfo> GetList(string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(250);
sb.Append(@"select Id,ActivityId,UserId,Remark,LastUpdatedDate
from ActivitySignUp ");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
IList <ActivitySignUpInfo> list = new List <ActivitySignUpInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.HnztcTeamDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
ActivitySignUpInfo model = new ActivitySignUpInfo();
model.Id = reader.GetGuid(0);
model.ActivityId = reader.GetGuid(1);
model.UserId = reader.GetGuid(2);
model.Remark = reader.GetString(3);
model.LastUpdatedDate = reader.GetDateTime(4);
list.Add(model);
}
}
}
return(list);
}
public IList <ActivitySignUpInfo> GetList(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(250);
sb.Append(@"select count(*) from ActivitySignUp ");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.HnztcTeamDbConnString, CommandType.Text, sb.ToString(), cmdParms);
if (totalRecords == 0)
{
return(new List <ActivitySignUpInfo>());
}
sb.Clear();
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
sb.Append(@"select * from(select row_number() over(order by LastUpdatedDate desc) as RowNumber,
Id,ActivityId,UserId,Remark,LastUpdatedDate
from ActivitySignUp " );
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex);
IList <ActivitySignUpInfo> list = new List <ActivitySignUpInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.HnztcTeamDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
ActivitySignUpInfo model = new ActivitySignUpInfo();
model.Id = reader.GetGuid(1);
model.ActivityId = reader.GetGuid(2);
model.UserId = reader.GetGuid(3);
model.Remark = reader.GetString(4);
model.LastUpdatedDate = reader.GetDateTime(5);
list.Add(model);
}
}
}
return(list);
}
public string SaveActivitySignUp1(ActivitySignUpInfo info)
{
string text = string.Empty;
ActivityDao activityDao = new ActivityDao();
ActivityInfo activity = activityDao.GetActivity(info.ActivityId);
if (activity == null)
{
text = "活动不存在";
}
else
{
int maxValue = activity.MaxValue;
if (maxValue > 0)
{
string query = "select count(0) from vshop_ActivitySignUp where ActivityId=@ActivityId";
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
this.database.AddInParameter(sqlStringCommand, "ActivityId", System.Data.DbType.Int32, info.ActivityId);
int num = Convert.ToInt32(this.database.ExecuteScalar(sqlStringCommand));
if (maxValue <= num)
{
text = "已经达到了报名上限";
}
}
if (string.IsNullOrEmpty(text))
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("IF NOT EXISTS (select 1 from vshop_ActivitySignUp WHERE ActivityId=@ActivityId and UserId=@UserId) ").Append("INSERT INTO vshop_ActivitySignUp(").Append("ActivityId,UserId,UserName,RealName,SignUpDate").Append(",Item1,Item2,Item3,Item4,Item5)").Append(" VALUES (").Append("@ActivityId,@UserId,@UserName,@RealName,@SignUpDate").Append(",@Item1,@Item2,@Item3,@Item4,@Item5)");
System.Data.Common.DbCommand sqlStringCommand2 = this.database.GetSqlStringCommand(stringBuilder.ToString());
this.database.AddInParameter(sqlStringCommand2, "ActivityId", System.Data.DbType.Int32, info.ActivityId);
this.database.AddInParameter(sqlStringCommand2, "UserId", System.Data.DbType.Int32, info.UserId);
this.database.AddInParameter(sqlStringCommand2, "UserName", System.Data.DbType.String, info.UserName);
this.database.AddInParameter(sqlStringCommand2, "RealName", System.Data.DbType.String, info.RealName);
this.database.AddInParameter(sqlStringCommand2, "SignUpDate", System.Data.DbType.DateTime, info.SignUpDate);
this.database.AddInParameter(sqlStringCommand2, "Item1", System.Data.DbType.String, info.Item1);
this.database.AddInParameter(sqlStringCommand2, "Item2", System.Data.DbType.String, info.Item2);
this.database.AddInParameter(sqlStringCommand2, "Item3", System.Data.DbType.String, info.Item3);
this.database.AddInParameter(sqlStringCommand2, "Item4", System.Data.DbType.String, info.Item4);
this.database.AddInParameter(sqlStringCommand2, "Item5", System.Data.DbType.String, info.Item5);
text = ((this.database.ExecuteNonQuery(sqlStringCommand2) > 0) ? "1" : "你已经报过名了,请勿重复报名");
}
}
return(text);
}
public bool SaveActivitySignUp(ActivitySignUpInfo info)
{
StringBuilder builder = new StringBuilder();
builder.Append("IF NOT EXISTS (select 1 from vshop_ActivitySignUp WHERE ActivityId=@ActivityId and UserId=@UserId) ").Append("INSERT INTO vshop_ActivitySignUp(").Append("ActivityId,UserId,UserName,RealName,SignUpDate").Append(",Item1,Item2,Item3,Item4,Item5)").Append(" VALUES (").Append("@ActivityId,@UserId,@UserName,@RealName,@SignUpDate").Append(",@Item1,@Item2,@Item3,@Item4,@Item5)");
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
this.database.AddInParameter(sqlStringCommand, "ActivityId", DbType.Int32, info.ActivityId);
this.database.AddInParameter(sqlStringCommand, "UserId", DbType.Int32, info.UserId);
this.database.AddInParameter(sqlStringCommand, "UserName", DbType.String, info.UserName);
this.database.AddInParameter(sqlStringCommand, "RealName", DbType.String, info.RealName);
this.database.AddInParameter(sqlStringCommand, "SignUpDate", DbType.DateTime, info.SignUpDate);
this.database.AddInParameter(sqlStringCommand, "Item1", DbType.String, info.Item1);
this.database.AddInParameter(sqlStringCommand, "Item2", DbType.String, info.Item2);
this.database.AddInParameter(sqlStringCommand, "Item3", DbType.String, info.Item3);
this.database.AddInParameter(sqlStringCommand, "Item4", DbType.String, info.Item4);
this.database.AddInParameter(sqlStringCommand, "Item5", DbType.String, info.Item5);
return(this.database.ExecuteNonQuery(sqlStringCommand) > 0);
}
public int Insert(ActivitySignUpInfo model)
{
StringBuilder sb = new StringBuilder(250);
sb.Append(@"insert into ActivitySignUp (ActivityId,UserId,Remark,LastUpdatedDate)
values
(@ActivityId,@UserId,@Remark,@LastUpdatedDate)
" );
SqlParameter[] parms =
{
new SqlParameter("@ActivityId", SqlDbType.UniqueIdentifier),
new SqlParameter("@UserId", SqlDbType.UniqueIdentifier),
new SqlParameter("@Remark", SqlDbType.NVarChar,300),
new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime)
};
parms[0].Value = model.ActivityId;
parms[1].Value = model.UserId;
parms[2].Value = model.Remark;
parms[3].Value = model.LastUpdatedDate;
return(SqlHelper.ExecuteNonQuery(SqlHelper.HnztcTeamDbConnString, CommandType.Text, sb.ToString(), parms));
}
public static string SaveActivitySignUp(ActivitySignUpInfo info)
{
return(new ActivitySignUpDao().SaveActivitySignUp(info));
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(ActivitySignUpInfo model)
{
return(dal.Update(model));
}
/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(ActivitySignUpInfo model)
{
return(dal.Insert(model));
}
public static bool SaveActivitySignUp(ActivitySignUpInfo info)
{
return new ActivitySignUpDao().SaveActivitySignUp(info);
}