asp.net 操作带输入输出参数的分页存储过程（二）

最新推荐文章于 2024-08-07 15:27:33 发布

Mellee

最新推荐文章于 2024-08-07 15:27:33 发布

阅读量340

点赞数

分类专栏： WEB编程文章标签： asp.net 数据库存储分页对象

本文链接：https://blog.csdn.net/Mellee/article/details/74019188

版权

WEB编程专栏收录该内容

6 篇文章 0 订阅

订阅专栏

数据层用到的几个方法。


/// <summary>
/// SQL预处理方式
/// </summary>
/// <param name="conn">数据库连接对象</param>
/// <param name="cmd">数据命令对象</param>
/// <param name="SqlCommandText">SQL语句或存储过程名称</param>
/// <param name="trans">事务对象</param>
 /// <param name="SqlParameters">参数对象</param>
private static void PrepareCommand(IDbConnection conn, IDbCommand cmd, string SqlCommandText, IDbTransaction trans, IDbDataParameter[] SqlParameters)
{
    try
    {
         if (conn.State != ConnectionState.Open)
         {
             conn.Open();
         }
            cmd.Connection = conn;
            cmd.CommandText = SqlCommandText;
            cmd.CommandType = CommandType.Text;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            if (SqlParameters != null)
            {
                if (IsStoredProcedure(SqlCommandText))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                foreach (IDbDataParameter parameter in SqlParameters)
                {
                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
                     {
                         parameter.Value = DBNull.Value;  
                     }
                     cmd.Parameters.Add(parameter);
                }
            }
       }
       catch (Exception ex)
       {
           CustomErrors.ErrFlag = "database";
            CustomErrors.ErrDescription = ex.Message;
       }
 }
 /// <summary>
/// 判断SQL命令文体是否为存储过程,MYSQL,ORCALE有待改进
/// </summary>
/// <param name="SqlCommandText">SQL命令文本</param>
/// <returns></returns>
 private static bool IsStoredProcedure(string SqlCommandText)
 {
     bool result = true;
     string[] operate = { "select ", "insert ", "delete ", "update " };
     string lowerText = SqlCommandText.ToLower();
     foreach (string str in operate)
     {
         f (lowerText.Contains(str))
         {
             result = false;
             break;
         }
     }
      return result;
}
/// <summary>
/// 将DataReader转换成DataTable
/// </summary>
/// <param name="reader">DataReader对象</param>
/// <returns></returns>
private static DataTable DataReaderToDataTable(IDataReader reader)
{
     DataTable tab = null;
     try
     {
         tab = new DataTable();
         int len = reader.FieldCount;
         for (int i = 0; i < len; i++)
         {
             tab.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
         }
         tab.BeginLoadData();
          object[] objArr = new object[len];
          while (reader.Read())
          {
              reader.GetValues(objArr);
              tab.LoadDataRow(objArr, true);
          }
           reader.Close();
           tab.EndLoadData();
     }
     catch (Exception ex)
      {
          CustomErrors.ErrFlag = "database";
          CustomErrors.ErrDescription = ex.Message;
      }
      return tab;
 }