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; //返回資料集        }     }