using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;
using SQLHelper;
using Model;
/// <summary>
/// AdminSQL 的摘要说明
/// </summary>
namespace DAL
{
public class AdminSQL
{
/// <summary>
/// 添加
/// </summary>
/// <param name="admin"></param>
/// <returns></returns>
public int Add_Admin(Admin admin)
{
SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
SqlParameter[] ParamList = {
sqlHelper.CreateInParam( " @UserName " ,SqlDbType.NVarChar, 50 ,admin.UserName),
sqlHelper.CreateInParam( " @Password " ,SqlDbType.NVarChar, 50 ,admin.Password),
sqlHelper.CreateInParam( " @LastLoginIP " ,SqlDbType.NVarChar, 50 ,admin.LastLoginIP),
sqlHelper.CreateInParam( " @LastLoginTime " ,SqlDbType.DateTime, 8 ,admin.LastLoginTime)
};
try
{
return (sqlHelper.RunProc( " Add_Admin " , ParamList));
}
catch (Exception ex)
{
SystemError.CreateErrorLog(ex.Message);
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 修改
/// </summary>
/// <param name="admin"></param>
public void Update_Admin(Admin admin)
{
SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
SqlParameter[] ParamList = {
sqlHelper.CreateInParam( " @ID " ,SqlDbType.Int, 4 ,admin.ID),
sqlHelper.CreateInParam( " @UserName " ,SqlDbType.NVarChar, 50 ,admin.UserName),
sqlHelper.CreateInParam( " @Password " ,SqlDbType.NVarChar, 50 ,admin.Password),
sqlHelper.CreateInParam( " @LastLoginIP " ,SqlDbType.NVarChar, 50 ,admin.LastLoginIP),
sqlHelper.CreateInParam( " @LastLoginTime " ,SqlDbType.DateTime, 8 ,admin.LastLoginTime)
};
try
{
sqlHelper.RunProc( " Update_Admin " , ParamList);
}
catch (Exception ex)
{
SystemError.CreateErrorLog(ex.Message);
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 删除
/// </summary>
/// <param name="nID"></param>
public void Delete_Admin( int nID)
{
SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
SqlParameter[] ParamList = { sqlHelper.CreateInParam( " @ID " ,SqlDbType.Int, 4 ,nID)};
try
{
sqlHelper.RunProc( " Delete_Admin " , ParamList);
}
catch (Exception ex)
{
SystemError.CreateErrorLog(ex.Message);
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 查看全部,返回包含所有记录.
/// </summary>
/// <returns></returns>
public List < Admin > Get_Admin()
{
SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
SqlDataReader rec = null ;
try
{
sqlHelper.RunProc( " Get_Admin " , out rec);
}
catch (Exception ex)
{
SystemError.CreateErrorLog(ex.Message);
throw new Exception(ex.Message, ex);
}
List < Admin > List_Admin = new List < Admin > ();
while (rec.Read())
{
Admin admin = new Admin();
admin.ID = Int32.Parse(rec[ " ID " ].ToString());
admin.UserName = rec[ " UserName " ].ToString();
admin.Password = rec[ " Password " ].ToString();
admin.LastLoginIP = rec[ " LastLoginIP " ].ToString();
admin.LastLoginTime = DateTime.Parse(rec[ " LastLoginTime " ].ToString());
List_Admin.Add(admin);
admin = null ;
}
rec.Close();
return List_Admin;
}
/// <summary>
/// 查看单个,输入一个用户名,返回一个包含全部信息的类.
/// </summary>
/// <param name="nID"></param>
/// <returns></returns>
public Admin Get_SingAdmin( string sUserName)
{
SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
// 参数列表 // SqlHelper的创建参数方法(参数名,对应字段;参数类型;参数值)
SqlParameter[] ParamList = { sqlHelper.CreateInParam( " @UserName " , SqlDbType.NVarChar, 50 ,sUserName) };
SqlDataReader rec = null ;
try
{
// 执行存储过程(存储过程名,参数列表,输出参数)
sqlHelper.RunProc( " Get_SingAdmin " , ParamList, out rec);
}
catch (Exception ex)
{
SystemError.CreateErrorLog(ex.Message);
throw new Exception(ex.Message, ex);
}
// Model,一个类对应数据库中一个表
Admin admin = new Admin();
while (rec.Read())
{
admin.ID = Int32.Parse(rec[ " ID " ].ToString()); // int类型转换
admin.UserName = rec[ " UserName " ].ToString();
admin.Password = rec[ " Password " ].ToString();
admin.LastLoginIP = rec[ " LastLoginIP " ].ToString();
admin.LastLoginTime = DateTime.Parse(rec[ " LastLoginTime " ].ToString());
}
rec.Close();
return admin;
}
/// <summary>
/// 用户是否存在
/// </summary>
/// <param name="sUserName"></param>
/// <returns></returns>
public bool Login_Admin( string sUserName)
{
SQLHelper.SQLHelper sqlHelper = new SQLHelper.SQLHelper();
SqlParameter[] ParamList = { sqlHelper.CreateInParam( " @UserName " , SqlDbType.NVarChar, 50 , sUserName) };
SqlDataReader rec = null ;
try
{
sqlHelper.RunProc( " Login_Admin " , ParamList, out rec);
}
catch (Exception ex)
{
SystemError.CreateErrorLog(ex.Message);
throw new Exception(ex.Message, ex);
}
bool Bool = false ;
while (rec.Read())
{
if (sUserName == rec[ " UserName " ].ToString())
{
Bool = true ;
}
}
rec.Close();
return Bool;
}
}
}