winform的help层

 

设两个字段
private SqlConnection conn=null;
private readonly string returnValue="returnValue";

打开数据库方法
public void ConnOpen()
{
    if(conn==null)
    {
        conn=new SqlConnection(configurationManager.ConnectionString["SQLconn"].ToString());
    }
    if(conn.State==ConnectionState.Closed)
    {
        conn.Open();
    }
}

关闭数据库方法
public void ConnClose()
{
    if(conn!=null)
    {
        if(conn.State==ConnectionState.Open)
        {
            conn.Close();
        }
    }
}

释入资源
public void Disponse()
{
    if(conn!=null)
    {
        conn.Disponse();
        conn=null;
    }
}

生成存储过程参数方法
private SqlParameter CreateParameter(string ParameterName,SqlDbType DbType,Int32 Size,ParameterDirection PDirection,object Value)
{
    SqlParameter parameter;
    if(Size>0)
    {
        parameter=new SqlParameter(ParameterName,DbType,Size);
    }
    else
    {
        parameter=new SqlParameter(ParameterName,DbType);
    }

    parameter.Direction=PDirection;
    if(!(PDirection==Parameter.output&&Value==null))
    {
        parameter.Value=Value;
    }
    return parameter;
}

传入参数
public SqlParameter CreateInParameter(string ParameterName,SqlDbType DbType,int32 Size,object Value)
{
    return CreateParameter(ParameterName,DbType,Size,ParameterDirection.Input,Value);
}
public SqlParameter CreateOutParameter(string ParameterNAme,SqlDbType DbType,int32 Size,object Value)
{
    return CreateParameter(ParameterName,DbType,Size,ParameterDirection.Output,Value);
}

创建一个SqlCommand对象来执行存储过程
private SqlCommand CreateProcCommand(string ProcName,SqlParameter[] parameter)
{
    ConnOpen();
    SqlCommand cmd=new SqlCommand(ProcName,conn);
    cmd.CommandType=CommandType.StoreProcedure;
    if(parameter!=null)
    {
        foreach(SqlParamete Parames in parameter)
        {
            cmd.Parameters.Add(parames);
        }
    }
    cmd.Parameters.Add(new SqlParameter(returnValue,SqlDbType.Int,4,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null));
    return cmd;
}

执行存储过程
public int RunProc(string procName,SqlParameter[]parameter)
{
    SqlCommand cmd=CreateProcCommand(procName,parameter);
    try
    {
        return cmd.ExecuteNonQuery();
    }
    catch(Exception ex)
    {
        throw new Exception(ex.Message);
    }
    finally
    {
         ConnClose();
    }
}
public void RunProc(string procName,SqlParameter[]parameter)
{
    SqlCommand cmd=CreateProcCommand(procName,parameter);
    try
    {
        cmd.ExecuteNonQuery();
    }
    catch(Exception ex)
    {
        throw new Exception(ex.Message);
    }
    finally
    {
         ConnClose();
    }
}
public void RunProcRead(string procName,out SqlDataReader dataReader)
{
    SqlCommand cmd=CreateProcCommand(procName,null);
    try
    {
        dataReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }
    catch(Exception ex)
    {
        dataReader=null;
        throw new Exception(ex.Message);
    }
}
public void RunProcRead(string procName,SqlParameter[]parameter,out SqlDataReader dataReader)
{
    SqlCommand cmd=CreateProcCommand(ProcName,parameter);
    try
    {
        dataReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }
    catch(Exception ex)
    {
        dataReader=null;
        throw new Exception(ex.Message);
    }
}

创建一个DataSet来存储数据
public DataSet CreateDataSet(string procName)
{
    ConnOpen();
    try
    {
        SqlCommand cmd=CreateProcCommand(procName,null);
        SqlDataAdapter ds=new SqlDataAdapter(cmd);
        cmd.ExecuteNonQuery();
        DataSet ds=new DataSet();
        ds.Fill(ds);
        return ds;
    }
    catch(Exception ex)
    {
        throw new Exception(ex.Message);
    }
    finally
    {
        ConnClose();
    }
}
public DataSet CreateDataSet(string procName,SqlParameter[]parameter)
{
    ConnOpen();
    try
    {
        SqlCommand cmd=CreateProcCommand(procName,parameter);
        SqlDataAdapter ds=new SqlDataAdapter(cmd);
        cmd.ExecuteNonQuery();
        DataSet ds=new DataSet();
        ds.Fill(ds);
        return ds;
    }
    catch(Exception ex)
    {
        throw new Exception(ex.Message);
    }
    finally
    {
        ConnClose();
    }
}

创建一个DataTable来存储数据
public DataTable CreateDataTable(string procName)
{
    ConnOpen();
    try
    {
        SqlCommand cmd = CreateProcCommand(procName, null);
        SqlDataAdapter dr = new SqlDataAdapter(cmd);
        cmd.ExecuteNonQuery();
        DataTable dt = new DataTable();
        dr.Fill(dt);
        return dt;
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
    finally
    {
        ConnClose();
    }
}