• 推荐
  • 评论
  • 收藏

步步为营 .NET三层架构解析 三、SQLHelper设计

2022-11-10    9808次浏览

数据库设计好了,我们开始设计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

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

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

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

创建一个SqlCommand对象以此来执行存储过程

private SqlCommand CreateProcCommand(string procName, SqlParameter[] prams)
       {
           ///打开数据库连接
           Open();
 
           ///设置Command
           SqlCommand cmd = new SqlCommand(procName, myConnection);
           cmd.CommandType = CommandType.StoredProcedure;
 
 
           ///添加把存储过程的参数
           if (prams != null)
           {
               foreach (SqlParameter parameter in prams)
               {
                   cmd.Parameters.Add(parameter);
               }
           }
 
           ///添加返回参数ReturnValue
           cmd.Parameters.Add(
               new SqlParameter(RETURNVALUE, SqlDbType.Int, 4, ParameterDirection.ReturnValue,
               false, 0, 0, string.Empty, DataRowVersion.Default, null));
 
           ///返回创建的SqlCommand对象
           return cmd;
       }

创建一个SqlCommand对象以此来执行存储过程

private SqlCommand CreateSQLCommand(string cmdText, SqlParameter[] prams)
       {
           ///打开数据库连接
           Open();
 
           ///设置Command
           SqlCommand cmd = new SqlCommand(cmdText, myConnection);
 
           ///添加把存储过程的参数
           if (prams != null)
           {
               foreach (SqlParameter parameter in prams)
               {
                   cmd.Parameters.Add(parameter);
               }
           }
 
           ///添加返回参数ReturnValue
           cmd.Parameters.Add(
               new SqlParameter(RETURNVALUE, SqlDbType.Int, 4, ParameterDirection.ReturnValue,
               false, 0, 0, string.Empty, DataRowVersion.Default, null));
 
           ///返回创建的SqlCommand对象
           return cmd;
       }

创建一个SqlDataAdapter对象,用此来执行存储过程

private SqlDataAdapter CreateProcDataAdapter(string procName, SqlParameter[] prams)
        {
            ///打开数据库连接
            Open();
 
            ///设置SqlDataAdapter对象
            SqlDataAdapter da = new SqlDataAdapter(procName, myConnection);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
 
            ///添加把存储过程的参数
            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    da.SelectCommand.Parameters.Add(parameter);
                }
            }
 
            ///添加返回参数ReturnValue
            da.SelectCommand.Parameters.Add(
                new SqlParameter(RETURNVALUE, SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
 
            ///返回创建的SqlDataAdapter对象
            return da;
        }

创建一个SqlDataAdapter对象,用此来执行SQL语句

private SqlDataAdapter CreateSQLDataAdapter(string cmdText, SqlParameter[] prams)
        {
            ///打开数据库连接
            Open();
 
            ///设置SqlDataAdapter对象
            SqlDataAdapter da = new SqlDataAdapter(cmdText, myConnection);
 
            ///添加把存储过程的参数
            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    da.SelectCommand.Parameters.Add(parameter);
                }
            }
 
            ///添加返回参数ReturnValue
            da.SelectCommand.Parameters.Add(
                new SqlParameter(RETURNVALUE, SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
 
            ///返回创建的SqlDataAdapter对象
            return da;
        }

生成存储过程参数

public SqlParameter CreateParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
       {
           SqlParameter param;
 
           ///当参数大小为0时,不使用该参数大小值
           if (Size > 0)
           {
               param = new SqlParameter(ParamName, DbType, Size);
           }
           else
           {
               ///当参数大小为0时,不使用该参数大小值
               param = new SqlParameter(ParamName, DbType);
           }
 
           ///创建输出类型的参数
           param.Direction = Direction;
           if (!(Direction == ParameterDirection.Output && Value == null))
           {
               param.Value = Value;
           }
 
           ///返回创建的参数
           return param;
       }

传入输入参数

public SqlParameter CreateInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
    return CreateParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}

传入返回值参数

public SqlParameter CreateOutParam(string ParamName, SqlDbType DbType, int Size)
{
    return CreateParam(ParamName, DbType, Size, ParameterDirection.Output, null);
}

传入返回值参数

public SqlParameter CreateReturnParam(string ParamName, SqlDbType DbType, int Size)
{
    return CreateParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);
}

把所有这些方法放在SQLHelper.cs类里.再建一个SQLTools.cs,里面的方法如下:

public class SystemException : Exception
   {
       /// <summary>
       /// 包含系统Excepton
       /// </summary>
       public SystemException(string source, string message, Exception inner)
           base(message, inner)
       {
           base.Source = source;
       }
 
       /// <summary>
       /// 不包含系统Excepton
       /// </summary>           
       public SystemException(string source, string message)
           base(message)
       {
           base.Source = source;
       }
   }
 
   /// <summary>
   /// 处理网页中的HTML代码,并消除危险字符
   /// </summary>
   public class SystemHTML
   {
       private static string HTMLEncode(string fString)
       {
           if (fString != string.Empty)
           {
               ///替换尖括号
               fString.Replace("<""<");
               fString.Replace(">""&rt;");
               ///替换引号
               fString.Replace(((char)34).ToString(), """);
               fString.Replace(((char)39).ToString(), "'");
               ///替换空格
               fString.Replace(((char)13).ToString(), "");
               ///替换换行符
               fString.Replace(((char)10).ToString(), "<BR> ");
           }
           return (fString);
       }
   }
 
 
   /// <summary>
   /// SystemTools 的摘要说明。
   /// </summary>
   public class SystemTools
   {
       /// <summary>
       /// 将DataReader转为DataTable
       /// </summary>
       /// <param name="DataReader">DataReader</param>
       public static DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
       {
           ///定义DataTable
           DataTable datatable = new DataTable();
 
           try
           {   ///动态添加表的数据列
               for (int i = 0; i < dataReader.FieldCount; i++)
               {
                   DataColumn myDataColumn = new DataColumn();
                   myDataColumn.DataType = dataReader.GetFieldType(i);
                   myDataColumn.ColumnName = dataReader.GetName(i);
                   datatable.Columns.Add(myDataColumn);
               }
 
               ///添加表的数据
               while (dataReader.Read())
               {
                   DataRow myDataRow = datatable.NewRow();
                   for (int i = 0; i < dataReader.FieldCount; i++)
                   {
                       myDataRow[i] = dataReader[i].ToString();
                   }
                   datatable.Rows.Add(myDataRow);
                   myDataRow = null;
               }
               ///关闭数据读取器
               dataReader.Close();
               return datatable;
           }
           catch (Exception ex)
           {
               ///抛出类型转换错误
               SystemError.CreateErrorLog(ex.Message);
               throw new Exception(ex.Message, ex);
           }
       }
   }

主要是处理异常和一些特殊字符.

再建一个SystemError.cs,里面的方法如下:

public class SystemError
{
    private static string m_fileName = "c:\\Systemlog.txt";
 
    public static String FileName
    {
        get
        {
            return (m_fileName);
        }
        set
        {
            if (value != null || value != "")
            {
                m_fileName = value;
            }
        }
    }
    public static void CreateErrorLog(string message)
    {
        if (File.Exists(m_fileName))
        {
            ///如果日志文件已经存在,则直接写入日志文件
            StreamWriter sr = File.AppendText(FileName);
            sr.WriteLine("\n");
            sr.WriteLine(DateTime.Now.ToString() + message);
            sr.Close();
        }
        else
        {
            ///创建日志文件
            StreamWriter sr = File.CreateText(FileName);
            sr.Close();
        }  
    }
}

主要记录日志.

http://www.cnblogs.com/springyangwc/archive/2011/03/23/1993061.html

原文地址:https://www.cnblogs.com/Leo_wl/p/1996164.html