sqlhelper.cs

我的一个Ado.Net一个框架设计

                return val;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                trans.Connection.Close();
            }
        }

        /// <summary>
        /// 根据一个指定的链接字符串和一组参数来执行一组sql命令
        /// </summary>
        /// <remarks>
        /// 例如：  
        ///  SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">有效的链接字符串</param>
        /// <param name="commandType">命令类型（存储过程，sql语句等等）</param>
        /// <param name="commandText">存储过程名字或者T－SQL命令</param>
        /// <param name="commandParameters">用于执行命令的一组参数</param>
        /// <returns>A SqlDataReader containing the results</returns>
        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            // 我们用一个try/catch，因为假如这方法抛出一个异常我们就要关闭数据库链接然后抛出异常代码, 因为此时没有datareader存在, 所以commandBehaviour.CloseConnection将不起作用
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// <summary>
        ///结果集中第一行的第一列或空引用（如果结果集为空）。 
        /// </summary>
        /// <remarks>
        ///例如： 
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">有效的链接字符串</param>
        /// <param name="commandType">命令类型（存储过程，sql语句等等）</param>
        /// <param name="commandText">存储过程名字或者T－SQL命令</param>
        /// <param name="commandParameters">用于执行命令的一组参数</param>
        /// <returns>一个可以转换成预期变量类型的对象</returns>
        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                    object val = cmd.ExecuteScalar();//返回第一行中的第一列
                    cmd.Parameters.Clear();
                    return val;
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 利用一个存在的数据库链接对象来执行sql命令，返回查询结果中的第一行的第一列
        /// </summary>
        /// <remarks>
        /// 例如  
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">命令类型（存储过程，sql语句等等）</param>
        /// <param name="commandText">存储过程名字或者T－SQL命令</param>
        /// <param name="commandParameters">用于执行命令的一组参数</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {

            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();//返回查询结果中的第一行的第一列
                cmd.Parameters.Clear();
                return val;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 异步获取SqlDataReader
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static SqlDataReader AsyncExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            connectionString = connectionString + ";Asynchronous Processing=true";
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {

                try
                {
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                    IAsyncResult result = cmd.BeginExecuteReader();
                    SqlDataReader reader = cmd.EndExecuteReader(result);
                    return reader;
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                catch (InvalidOperationException ex)
                {
                    throw ex;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

        }
        public static int AsyncExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            connectionString = connectionString + ";Asynchronous Processing=true";

            SqlCommand cmd = new SqlCommand();
            
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                
                try
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                    IAsyncResult result = cmd.BeginExecuteNonQuery();
                    int affectedRow = cmd.EndExecuteNonQuery(result);
                    return affectedRow;
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                catch (InvalidOperationException ex)
                {
                    throw ex;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 为一个执行命令坐准备
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <param name="conn">SqlConnection对象</param>
        /// <param name="trans">SqlTransaction 对象</param>
        /// <param name="cmdType">Cmd type如：存储过程或sql文本</param>
        /// <param name="cmdText">Command text, 例如 Select * from Products</param>
        /// <param name="cmdParms">用在命令中的参数</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
    }
}