ADO.net通用数据访问类


 
private static string connString = "Server = localhost\\SQLEXPRESS2014;DataBase = StudentManageDB;Uid = sa;Pwd = sa";
        /// <summary>
        /// 单一结果查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object GetSingleResult(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand command = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return command.ExecuteScalar();
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 增删改操作
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int AlterData(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand command = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return command.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();//Close会在返回值之前执行
            }
        }
        /// <summary>
        /// 返回一个结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand command = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return command.ExecuteReader(CommandBehavior.CloseConnection);
                //CommandBehavior.CloseConnection 该参数能够保证从外部关闭DataReader时,与之关联的Connection对象将随之关闭
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            //conn.Close();不能用此种方法关闭,关闭之后Read方法将不能使用
        }

//sqldatareader的使用
        static void Main(string[] args)
        {
            string sql = "select * from Students";
            SqlDataReader reader = SQLHelper.GetReader(sql);
            while (reader.Read())
            {
                Console.WriteLine(reader["StudentId"] + "\t" + reader["StudentName"]);
            }
            reader.Close();//会同时关闭conn
        }
 /// <summary>
        /// 事务的应用,主要用于多条sql语句
        /// </summary>
        /// <param name="sqlList"></param>
        /// <returns></returns>
        public static int UpdateByTrans(List<string> sqlList)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            try
            {
                conn.Open();
                cmd.Transaction = conn.BeginTransaction();
                int result = 0;
                foreach (string item in sqlList)
                {
                    cmd.CommandText = item;
                    result += cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();//提交事务
                return result;
            }
            catch(Exception ex)
            {
                if(cmd.Transaction != null)
                    cmd.Transaction.Rollback();//回滚事务
                throw new Exception("调用事务更新方法时出现异常:" + ex.Message);
            }
            finally
            {
                if (cmd.Transaction != null)
                    cmd.Transaction = null;//清除事务
                conn.Close();
            }
        }
 /// <summary>
        /// 调用存储过程
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="param">存储过程的参数</param>
        /// <returns></returns>
        public static int UpdateByProcedure(string procedureName, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procedureName;
                cmd.Parameters.AddRange(param);
                int result = cmd.ExecuteNonQuery();
                return result;
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

 

阅读更多

没有更多推荐了,返回首页