?using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// Dal 數(shù)據(jù)操作類
/// </summary>
public class Dal:DataBase
{
    private SqlCommand Cmd=null;
    private DataSet Ds = null;
    private DataTable Dt=null;
	public Dal()
	{
		//
		// TODO: 在此處添加構(gòu)造函數(shù)邏輯
		//
	}
    /// <summary>
    /// 檢測 用戶登陸
    /// </summary>
    /// <param name="info"></param>
    /// <returns></returns>
    public DataTable CheckLogin(Info info)
    {
        
        string strSql = "select * from [info] where I_UserName=@username and I_UserPwd=@userpwd";
        SqlParameter[] parms ={ new SqlParameter("@username",SqlDbType.VarChar,100),
                               new SqlParameter("@userpwd",SqlDbType.VarChar,200)};
        parms[0].Value = info.Name;
        parms[1].Value = info.Pwd;
        try
        {
            Cmd = ExecuteCmd(strSql, parms);
            Dt = ExecuteDataTable(Cmd);
        }
        catch (SqlException ex)
        {
            throw new Exception(ex.Message, ex);
        }
        return Dt;


    }
    /// <summary>
    /// 添加新用戶
    /// </summary>
    /// <param name="info"></param>
    /// <returns></returns>
    public int Insert(Info info)
    {
       
        int i = -1;
        string strSql = "insert into [Info] (I_UserName,I_UserPwd,I_IsAdmin)values(@username,@userpwd,@isadmin)";
        SqlParameter[] parms ={ new SqlParameter("@username",SqlDbType.VarChar,100),
                               new SqlParameter("@userpwd",SqlDbType.VarChar,200),
                               new SqlParameter("@isadmin",SqlDbType.VarChar,50) };
        parms[0].Value = info.Name;
        parms[1].Value = info.Pwd;
        parms[2].Value = info.IsAdmin;
        try
        {
            objConn.Open();
            Cmd = ExecuteCmd(strSql, parms);
            i = Cmd.ExecuteNonQuery();

        }
        catch (SqlException ex)
        {
            throw new Exception(ex.Message, ex);
        }
        finally
        {
            objConn.Close();
        }

        return i;
    }
    /// <summary>
    /// 修改用戶
    /// </summary>
    /// <param name="info"></param>
    /// <returns></returns>
    public int Update(Info info)
    {
        string strSql = "UPDATE [Info] SET I_UserName = @username,I_UserPwd = @userpwd,I_IsAdmin = @isadmin where I_ID = @userid";
        SqlParameter[] parms ={ new SqlParameter("@userid",SqlDbType.Int),
                                new SqlParameter("@username",SqlDbType.VarChar,100),
                                new SqlParameter("@userpwd",SqlDbType.VarChar,200),
                                new SqlParameter("@isadmin",SqlDbType.VarChar,50),
                              };
        parms[0].Value = info.ID;
        parms[1].Value = info.Name;
        parms[2].Value = info.Pwd;
        parms[3].Value = info.IsAdmin;
        
        try
        {
            objConn.Open();
            Cmd = ExecuteCmd(strSql, parms);
            Cmd.ExecuteNonQuery();
            return 1;

        }
        catch 
        {
            return 0;
        }
        finally
        {
            objConn.Close();
        }
    }
    /// <summary>
    /// 刪除指定記錄
    /// </summary>
    /// <param name="info"></param>
    /// <returns></returns>
    public int Delete(Info info)
    {
        string strSql = "Delete From [Info] where I_ID=@userid";
        SqlParameter[] parms ={ new SqlParameter("@userid",SqlDbType.Int)};
        parms[0].Value = info.ID;

        try
        {
            objConn.Open();
            Cmd = ExecuteCmd(strSql, parms);
            Cmd.ExecuteNonQuery();
            return 1;
        }
        catch
        {
            return 0;
        }
        finally
        {
            objConn.Close();
        }
    }
    
    /// <summary>
    /// 返回用戶信息
    /// </summary>
    /// <param name="info">Info 用戶類</param>
    /// <returns></returns>
    public DataTable Getinfo(Info info)
    {
        string strSql = "select * from [info] where I_ID=@id";
        SqlParameter[] parms ={ new SqlParameter("@id", SqlDbType.Int) };
        parms[0].Value = info.ID;
        try
        {
            Cmd = ExecuteCmd(strSql,parms);
            Dt = ExecuteDataTable(Cmd);
        }
        catch(SqlException ex)
        {
            throw new Exception(ex.Message,ex);
        }
        return Dt;
    }
    /// <summary>
    /// 按名字 查詢
    /// </summary>
    /// <param name="info"></param>
    /// <returns></returns>
    public DataSet GetName(Info info)
    {
        
        string strSql = @"Select * From [Info] where I_UserName like'%'+@username+'%'";
        SqlParameter[] parms ={ new SqlParameter("@username",SqlDbType.VarChar,100)};
        parms[0].Value = info.Name;
        try
        {
            Cmd = ExecuteCmd(strSql,parms);
            Ds = ExecuteDataSet(Cmd);
        }
        catch(SqlException ex)
        {
            throw new Exception(ex.Message,ex);
        }
        return Ds;
    }
    /// <summary>
    /// 返回記錄
    /// </summary>
    /// <returns></returns>
    public DataSet GetList()
    {
        string strSql = "select * from [info]";
        try
        {
            Cmd = ExecuteCmd(strSql);
            Ds = ExecuteDataSet(Cmd);
        }
        catch(SqlException ex)
        {
            throw new Exception(ex.Message,ex);
        }
        return Ds;

    }

}