/// <summary>
/// 新增
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
private static bool InsertUser(User user)
{
var sql = @"INSERT INTO User
(Account,RealName,Password,CreationTime)
VALUES(?,?,?,?)";
var parameters = new OleDbParameter[4];
parameters[0] = new OleDbParameter("@Account", OleDbType.VarChar, 50)
{
Value = user.RealName
};
parameters[1] = new OleDbParameter("@RealName", OleDbType.Integer)
{
Value = user.RealName
};
parameters[2] = new OleDbParameter("@Password", OleDbType.Integer)
{
Value = Encrypt.Md5By32(user.Password)
};
parameters[3] = new OleDbParameter("@CreationTime", OleDbType.Date)
{
Value = DateTime.Now
};
return(AccessDbUtil.ExecuteInsert(sql, parameters) == 1);
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="curPageIndex"></param>
/// <param name="where"></param>
/// <returns></returns>
public static Page GetStudents(int curPageIndex, string where)
{
var sqlQuery = "select * from Student";
var sqlCount = "select count(*) from Student";
var sqlOrder = " order by name";
if (where != null)
{
string sqlCondition = where;
sqlQuery += sqlCondition + sqlOrder;
sqlCount += sqlCondition;
}
int totalRecord = AccessDbUtil.ExecuteScalar(sqlCount);
var page = new Page(totalRecord, AccessPageUtil.PageSize);
if (curPageIndex >= page.TotalPage)
{
curPageIndex = page.TotalPage - 1;
}
if (curPageIndex < 0)
{
curPageIndex = 0;
}
page.CurPageIndex = curPageIndex;
var data = AccessPageUtil.Query(sqlQuery, curPageIndex, totalRecord);
var ls = new List <Student>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(RowToEntity(row));
}
page.ValueList = ls;
return(page);
}
public static User Get(string where)
{
var sql = "select * from User where 1=1";
if (!string.IsNullOrEmpty(where))
{
sql += where;
}
var ds = AccessDbUtil.ExecuteQuery(sql);
return(RowToEntity(ds.Tables["ds"].Rows[0]));
}
/// <summary>
/// 更新学生信息
/// </summary>
/// <param name="student"></param>
/// <returns></returns>
private static bool UpdateStudent(Student student)
{
var sql = @"UPDATE Student
SET RealName = ?
,Age = ?
,Sex =?
,Class = ?
,Grade = ?
,ParentsName = ?
,ContantNumber = ?
,DanceGradeId = ?
WHERE Id=?" ;
var parameters = new OleDbParameter[9];
parameters[0] = new OleDbParameter("@RealName", OleDbType.VarChar, 50)
{
Value = student.RealName
};
parameters[1] = new OleDbParameter("@Age", OleDbType.Integer)
{
Value = student.Age
};
parameters[2] = new OleDbParameter("@Sex", OleDbType.Integer)
{
Value = (int)student.Sex
};
parameters[3] = new OleDbParameter("@Class", OleDbType.VarChar, 25)
{
Value = student.Class
};
parameters[4] = new OleDbParameter("@Grade", OleDbType.VarChar, 50)
{
Value = student.Grade
};
parameters[5] = new OleDbParameter("@ParentsName", OleDbType.VarChar, 50)
{
Value = student.ParentsName
};
parameters[6] = new OleDbParameter("@ContantNumber", OleDbType.VarChar, 150)
{
Value = student.ContantNumber
};
parameters[7] = new OleDbParameter("@DanceGradeId", OleDbType.Integer)
{
Value = student.DanceGradeId
};
parameters[8] = new OleDbParameter("@id", OleDbType.Integer)
{
Value = student.Id
};
return(AccessDbUtil.ExecuteNonQuery(sql, parameters) == 1);
}
/// <summary>
/// 新增学生信息
/// </summary>
/// <param name="student"></param>
/// <returns></returns>
private static bool InsertStudent(Student student)
{
var sql =
@"INSERT INTO Student (RealName,Age,Sex,Class,Grade,ParentsName,ContantNumber,DanceGradeId) VALUES(?,?,?,?,?,?,?,?)";
var parameters = new OleDbParameter[8];
parameters[0] = new OleDbParameter("@RealName", OleDbType.VarChar, 50)
{
Value = student.RealName.Trim()
};
parameters[1] = new OleDbParameter("@Age", OleDbType.Integer)
{
Value = student.Age
};
parameters[2] = new OleDbParameter("@Sex", OleDbType.Integer)
{
Value = student.Sex.Value()
};
parameters[3] = new OleDbParameter("@Class", OleDbType.VarChar, 25)
{
Value = student.Class.Trim()
};
parameters[4] = new OleDbParameter("@Grade", OleDbType.VarChar, 50)
{
Value = student.Grade.Trim()
};
parameters[5] = new OleDbParameter("@ParentsName", OleDbType.VarChar, 50)
{
Value = student.ParentsName.Trim()
};
parameters[6] = new OleDbParameter("@ContantNumber", OleDbType.VarChar, 150)
{
Value = student.ContantNumber.Trim()
};
parameters[7] = new OleDbParameter("@DanceGradeId", OleDbType.Integer)
{
Value = student.DanceGradeId
};
return(AccessDbUtil.ExecuteInsert(sql, parameters) == 1);
}
public static User Login(string account, string password, ref string strResult)
{
strResult = "1";
if (string.IsNullOrEmpty(account))
{
strResult = "账号不能为空";
return(null);
}
if (string.IsNullOrEmpty(password))
{
strResult = "密码不能为空";
return(null);
}
var sql = "select * from [user] where account=?";
var parameters = new[]
{
new OleDbParameter("@account", OleDbType.VarChar, 50)
{
Value = account
}
};
var ds = AccessDbUtil.ExecuteQuery(sql, parameters);
if (ds.Tables[0].Rows.Count == 0)
{
strResult = "账号不存在！";
return(null);
}
var user = RowToEntity(ds.Tables[0].Rows[0]);
password = Encrypt.Md5By32(password);
if (user.Password != password)
{
strResult = "密码不正确！";
return(null);
}
return(user);
}
/// <summary>
/// 验证是否存在
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public static bool Exists(User user)
{
var strSql = "select 1 from user where account='" + user.Account + "'";
return(AccessDbUtil.Exists(strSql));
}
