.NET三层架构解析：SQLHelper设计 -建站知识-成都网站建设|郫县网页制作|微信开发|微信商城-中原动力

建站知识

.NET三层架构解析：SQLHelper设计

来源：网络作者：网络

数据库设计好了,我们开始设计SQLHelper了,是一个SQL基类.

连接数据源:

private SqlConnection myConnection = null;

private readonly string RETURNVALUE = "RETURNVALUE";

打开数据库连接.

private void Open() 
       { 
           // 打开数据库连接 
           if (myConnection == null) 
           { 
              //    myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); 
              myConnection = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString()); 
             
           } 
           if (myConnection.State == ConnectionState.Closed) 
           { 
               try
               { 
                   ///打开数据库连接 
                   myConnection.Open(); 
               } 
               catch (Exception ex) 
               { 
                     
                   SystemError.CreateErrorLog(ex.Message); 
               } 
               finally
               { 
                   ///关闭已经打开的数据库连接              
               } 
           } 
       }

关闭数据库连接

public void Close() 
       { 
           ///判断连接是否已经创建 
           if (myConnection != null) 
           { 
               ///判断连接的状态是否打开 
               if (myConnection.State == ConnectionState.Open) 
               { 
                   myConnection.Close(); 
               } 
           } 
       }

释放资源

public void Dispose() 
{ 
    // 确认连接是否已经关闭 
    if (myConnection != null) 
    { 
        myConnection.Dispose(); 
        myConnection = null; 
    } 
}

执行无参数和返回int型的存储过程

public int RunProc(string procName) 
        { 
            SqlCommand cmd = CreateProcCommand(procName, null); 
            try
            { 
                ///执行存储过程 
                cmd.ExecuteNonQuery(); 
            } 
            catch (Exception ex) 
            { 
                ///记录错误日志 
                SystemError.CreateErrorLog(ex.Message); 
            } 
            finally
            { 
                ///关闭数据库的连接 
                Close(); 
            } 
  
            ///返回存储过程的参数值 
            return (int)cmd.Parameters[RETURNVALUE].Value; 
        }

执行传入参数和返回int型的存储过程

public int RunProc(string procName, SqlParameter[] prams) 
        { 
            SqlCommand cmd = CreateProcCommand(procName, prams); 
            try
            { 
                ///执行存储过程 
                cmd.ExecuteNonQuery(); 
            } 
            catch (Exception ex) 
            { 
                ///记录错误日志 
                SystemError.CreateErrorLog(ex.Message); 
            } 
            finally
            { 
                ///关闭数据库的连接 
                Close(); 
            } 
  
            ///返回存储过程的参数值 
            return (int)cmd.Parameters[RETURNVALUE].Value; 
        }

执行存储过程和返回SqlDataReader

public void RunProc(string procName, out SqlDataReader dataReader) 
{ 
    ///创建Command 
    SqlCommand cmd = CreateProcCommand(procName, null); 
 
    try
    { 
        ///读取数据 
        dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 
    } 
    catch (Exception ex) 
    { 
        dataReader = null; 
        ///记录错误日志 
        SystemError.CreateErrorLog(ex.Message); 
    } 
}

执行传入参数和返回SqlDataReader存储过程

public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader) 
        { 
            ///创建Command 
            SqlCommand cmd = CreateProcCommand(procName, prams); 
  
            try
            { 
                ///读取数据 
                dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 
            } 
            catch (Exception ex) 
            { 
                dataReader = null; 
                ///记录错误日志 
                SystemError.CreateErrorLog(ex.Message); 
            } 
        }

执行无参数存储过程返回DataSet

public void RunProc(string procName, ref DataSet dataSet) 
{ 
    if (dataSet == null) 
    { 
        dataSet = new DataSet(); 
    } 
    ///创建SqlDataAdapter 
    SqlDataAdapter da = CreateProcDataAdapter(procName, null); 
 
    try
    { 
        ///读取数据 
        da.Fill(dataSet); 
    } 
    catch (Exception ex) 
    { 
        ///记录错误日志 
        SystemError.CreateErrorLog(ex.Message); 
    } 
    finally
    { 
        ///关闭数据库的连接 
        Close(); 
    } 
}

执行传入参数的存储过程返回DataSet

public void RunProc(string procName, SqlParameter[] prams, ref DataSet dataSet) 
       { 
           if (dataSet == null) 
           { 
               dataSet = new DataSet(); 
           } 
           ///创建SqlDataAdapter 
           SqlDataAdapter da = CreateProcDataAdapter(procName, prams); 
 
           try
           { 
               ///读取数据 
               da.Fill(dataSet); 
           } 
           catch (Exception ex) 
           { 
               ///记录错误日志 
               SystemError.CreateErrorLog(ex.Message); 
           } 
           finally
           { 
               ///关闭数据库的连接 
               Close(); 
           } 
       }

执行传入参数和表名的存储过程返回DataSet

public void RunProc(string procName, SqlParameter[] prams, string TableName, ref DataSet dataSet) 
        { 
            if (dataSet == null) 
            { 
                dataSet = new DataSet(); 
            } 
            ///创建SqlDataAdapter 
            SqlDataAdapter da = CreateProcDataAdapter(procName, prams); 
  
            try
            { 
                ///读取数据 
                da.Fill(dataSet, TableName); 
            } 
            catch (Exception ex) 
            { 
                ///记录错误日志 
                SystemError.CreateErrorLog(ex.Message); 
            } 
            finally
            { 
                ///关闭数据库的连接 
                Close(); 
            } 
        }

执行无参数SQL语句

public int RunSQL(string cmdText) 
{ 
    SqlCommand cmd = CreateSQLCommand(cmdText, null); 
    try
    { 
        ///执行存储过程 
        cmd.ExecuteNonQuery(); 
    } 
    catch (Exception ex) 
    { 
        ///记录错误日志 
        SystemError.CreateErrorLog(ex.Message); 
    } 
    finally
    { 
        ///关闭数据库的连接 
        Close(); 
    } 
 
    ///返回存储过程的参数值 
    return (int)cmd.Parameters[RETURNVALUE].Value; 
}

执行传入参数SQL语句

public int RunSQL(string cmdText, SqlParameter[] prams) 
       { 
           SqlCommand cmd = CreateSQLCommand(cmdText, prams); 
           try
           { 
               ///执行存储过程 
               cmd.ExecuteNonQuery(); 
           } 
           catch (Exception ex) 
           { 
               ///记录错误日志 
               SystemError.CreateErrorLog(ex.Message); 
           } 
           finally
           { 
               ///关闭数据库的连接 
               Close(); 
           } 
 
           ///返回存储过程的参数值 
           return (int)cmd.Parameters[RETURNVALUE].Value; 
       }

执行无参数SQL语句返回SqlDataReader

public void RunSQL(string cmdText, out SqlDataReader dataReader) 
       { 
           ///创建Command 
           SqlCommand cmd = CreateSQLCommand(cmdText, null); 
 
           try
           { 
               ///读取数据 
               dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 
           } 
           catch (Exception ex) 
           { 
               dataReader = null; 
               ///记录错误日志 
               SystemError.CreateErrorLog(ex.Message); 
           } 
       }

执行传入参数SQL语句返回SqlDataReader

public void RunSQL(string cmdText, SqlParameter[] prams, out SqlDataReader dataReader) 
{ 
    ///创建Command 
    SqlCommand cmd = CreateSQLCommand(cmdText, prams); 
 
    try
    { 
        ///读取数据 
        dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 
    } 
    catch (Exception ex) 
    { 
        dataReader = null; 
        ///记录错误日志 
        SystemError.CreateErrorLog(ex.Message); 
    } 
}

执行无参数SQL语句返回DataSet

public void RunSQL(string cmdText, ref DataSet dataSet) 
       { 
           if (dataSet == null) 
           { 
               dataSet = new DataSet(); 
           } 
           ///创建SqlDataAdapter 
           SqlDataAdapter da = CreateSQLDataAdapter(cmdText, null); 
 
           try
           { 
               ///读取数据 
               da.Fill(dataSet); 
           } 
           catch (Exception ex) 
           { 
               ///记录错误日志 
               SystemError.CreateErrorLog(ex.Message); 
           } 
           finally
           { 
               ///关闭数据库的连接 
               Close(); 
           } 
       }

执行传入参数SQL语句返回DataSet