ASP.NET从 SQL Server读取数据的两种方法介绍

用数据适配器SqlDataAdapter         

   SqlDataAdapter ad = new SqlDataAdapter();
            //创建连接对象
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "data source=.\\SQLEXPRESS;Initial Catalog=数据库;;User ID=用户名;Password=密码";
            //创建命令对象
            SqlCommand selecteCmd = new SqlCommand();
            selecteCmd.CommandText = "select * from table;";
            selecteCmd.Connection = conn;

            //设置数据适配器的selectcommand属性
            ad.SelectCommand = selecteCmd;
            //创建数据集对象
            DataSet datas = new DataSet();
            //使用数据适配器填充数据集
            ad.Fill(datas,"tables");
            GridView1.DataSource = datas.Tables["tables"];
            GridView1.DataBind();

用SqlDataReader

               SqlConnection con = new SqlConnection(“data source=.\\SQLEXPRESS;Initial Catalog=数据库;User ID=用户;Password=密码”); //
                SqlCommand cmd = new SqlCommand(); //创建Command对象
                cmd.Connection = con; //使用连接
                cmd.CommandText = sqlcmd; //配置SQL语句
                cmd.Parameters.Add(new SqlParameter(参数名,参数值));
                con.Open(); //打开连接
                SqlDataReader data= cmd.ExecuteReader(CommandBehavior.CloseConnection); //会自动关闭连接

再贴一个SQLHelper.cs

 public static class SQLHelper
    {
        
        private static readonly string condb = 
            ConfigurationManager.ConnectionStrings["tuanweiAppConn"].ConnectionString; //设置连接字串
        public enum SDACmd { select, delete, update, insert } //定义枚举类型 

        public static SqlDataReader ExecReader(string sqlcmd, params SqlParameter[] paraList)
        {
            try
            {
                SqlConnection con = new SqlConnection(condb); //
                SqlCommand cmd = new SqlCommand(); //创建Command对象
                cmd.Connection = con; //使用连接
                cmd.CommandText = sqlcmd; //配置SQL语句

                if (paraList != null)
                {
                    cmd.CommandType = CommandType.Text; //配置Command类型

                    foreach (SqlParameter para in paraList)
                    {
                        cmd.Parameters.Add(para);
                    } //添加参数
                }

                con.Open(); //打开连接
                SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return sdr;
            }
            catch (Exception exception)
            {
                throw exception;
            }
            
        }

        /// <summary>
        /// DataAdapter方法 返回DataSet数据集
        /// </summary>
        /// <param name="sqlCmd">SQL语句</param>
        /// <param name="command">操作参数 枚举类型</param>
        /// <returns></returns>

        public static DataSet DataAdapter(string sqlCmd, SDACmd command,  //实现适配器
                                            string tabName, params SqlParameter[] paraList)
        {
            SqlConnection con = new SqlConnection(condb); //创建连接对象
            SqlCommand cmd = new SqlCommand(); //创建Command对象
            cmd.Connection = con; //使用连接对象
            cmd.CommandText = sqlCmd; //配置连接字串

            if (paraList != null)
            {
                cmd.CommandType = CommandType.Text; //配置Command类型
                foreach (SqlParameter para in paraList) //遍历参数
                { 
                    cmd.Parameters.Add(para); 
                } //添加参数
            }

            SqlDataAdapter sda = new SqlDataAdapter(); //创建适配器
            switch (command) //查找条件
            {
                case SDACmd.select: //如果为select执行
                    sda.SelectCommand = cmd;
                    break;

                case SDACmd.insert: //如果为insert执行
                    sda.InsertCommand = cmd;
                    break;

                case SDACmd.update: //如果为update执行
                    sda.UpdateCommand = cmd;
                    break;

                case SDACmd.delete: //如果为delete执行
                    sda.DeleteCommand = cmd;
                    break;
            }

            DataSet ds = new DataSet(); //创建数据集
            sda.Fill(ds, tabName); //填充数据集
            return ds; //返回数据集
        } 
    }