[雖然說，開發的時候，我們可以使用各種框架，ado.net作為底層的東西，作為一個合格的程式員，在出問題的時候我們還是要知道如何調試] 一、增刪改查 cmd.ExecuteReader();執行查詢,所有sql語句的查詢都用這個方法; cmd.ExecuteNonQuery();執行所有sql語句的 ...
[雖然說，開發的時候，我們可以使用各種框架，ado.net作為底層的東西，作為一個合格的程式員，在出問題的時候我們還是要知道如何調試]
一、增刪改查
cmd.ExecuteReader();執行查詢,所有sql語句的查詢都用這個方法;
cmd.ExecuteNonQuery();執行所有sql語句的增刪改都用這個方法;
1 <div> 2 <table> 3 <tr> 4 <td> 用戶名:</td> 5 <td> 6 <asp:TextBox ID="txtSUserName" runat="server"></asp:TextBox> 7 </td> 8 <td>班級：</td> 9 <td> 10 <asp:DropDownList ID="ddlselPhase" runat="server"> 11 <asp:ListItem>---請選擇---</asp:ListItem> 12 <asp:ListItem>.NET高級班01期</asp:ListItem> 13 <asp:ListItem>.NET高級班02期</asp:ListItem> 14 <asp:ListItem>.NET講師</asp:ListItem> 15 <asp:ListItem>.NET網站開發01期</asp:ListItem> 16 <asp:ListItem>.NET網站開發02期</asp:ListItem> 17 <asp:ListItem>.NET網站開發03期</asp:ListItem> 18 <asp:ListItem>.NET網站開發04期</asp:ListItem> 19 <asp:ListItem>.NET網站開發05期</asp:ListItem> 20 <asp:ListItem>.NET網站開發06期</asp:ListItem> 21 <asp:ListItem>.NET網站開發07期</asp:ListItem> 22 <asp:ListItem>.NET網站開發08期</asp:ListItem> 23 <asp:ListItem>.NET網站開發09期</asp:ListItem> 24 <asp:ListItem>.NET網站開發10期</asp:ListItem> 25 <asp:ListItem>.NET網站開發11期</asp:ListItem> 26 <asp:ListItem>.NET網站開發12期</asp:ListItem> 27 <asp:ListItem>.NET網站開發13期</asp:ListItem> 28 <asp:ListItem>.NET網站開發14期</asp:ListItem> 29 <asp:ListItem>.NET網站開發15期</asp:ListItem> 30 <asp:ListItem>.NET網站開發16期</asp:ListItem> 31 <asp:ListItem>java第一期</asp:ListItem> 32 <asp:ListItem>JAVA講師</asp:ListItem> 33 <asp:ListItem>ps設計01期</asp:ListItem> 34 <asp:ListItem>ps設計02期</asp:ListItem> 35 <asp:ListItem>ps設計03期</asp:ListItem> 36 <asp:ListItem>網頁前端01期</asp:ListItem> 37 </asp:DropDownList> 38 </td> 39 <td> 40 <asp:Button ID="btnSel" runat="server" Text="查詢" OnClick="btnSel_Click" /> 41 </td> 42 </tr> 43 </table> 44 </div> 45 <div> 46 <table> 47 <tr> 48 <td> 用戶名:</td> 49 <td> 50 <asp:TextBox ID="txtAddUserName" runat="server"></asp:TextBox> 51 </td> 52 <td> 密碼:</td> 53 <td> 54 <asp:TextBox ID="txtAddPwd" runat="server" TextMode="Password"></asp:TextBox> 55 </td> 56 <td> QQ:</td> 57 <td> 58 <asp:TextBox ID="txtAddQq" runat="server"></asp:TextBox> 59 </td> 60 <td>班級：</td> 61 <td> 62 <asp:DropDownList ID="ddlAddPhase" runat="server"> 63 <asp:ListItem>---請選擇---</asp:ListItem> 64 <asp:ListItem>.NET高級班01期</asp:ListItem> 65 <asp:ListItem>.NET高級班02期</asp:ListItem> 66 <asp:ListItem>.NET講師</asp:ListItem> 67 <asp:ListItem>.NET網站開發01期</asp:ListItem> 68 <asp:ListItem>.NET網站開發02期</asp:ListItem> 69 <asp:ListItem>.NET網站開發03期</asp:ListItem> 70 <asp:ListItem>.NET網站開發04期</asp:ListItem> 71 <asp:ListItem>.NET網站開發05期</asp:ListItem> 72 <asp:ListItem>.NET網站開發06期</asp:ListItem> 73 <asp:ListItem>.NET網站開發07期</asp:ListItem> 74 <asp:ListItem>.NET網站開發08期</asp:ListItem> 75 <asp:ListItem>.NET網站開發09期</asp:ListItem> 76 <asp:ListItem>.NET網站開發10期</asp:ListItem> 77 <asp:ListItem>.NET網站開發11期</asp:ListItem> 78 <asp:ListItem>.NET網站開發12期</asp:ListItem> 79 <asp:ListItem>.NET網站開發13期</asp:ListItem> 80 <asp:ListItem>.NET網站開發14期</asp:ListItem> 81 <asp:ListItem>.NET網站開發15期</asp:ListItem> 82 <asp:ListItem>.NET網站開發16期</asp:ListItem> 83 <asp:ListItem>java第一期</asp:ListItem> 84 <asp:ListItem>JAVA講師</asp:ListItem> 85 <asp:ListItem>ps設計01期</asp:ListItem> 86 <asp:ListItem>ps設計02期</asp:ListItem> 87 <asp:ListItem>ps設計03期</asp:ListItem> 88 <asp:ListItem>網頁前端01期</asp:ListItem> 89 </asp:DropDownList> 90 </td> 91 <td> 92 <asp:Button ID="btnAdd" runat="server" Text="添加" OnClick="btnAdd_Click" style="height: 21px" /> 93 </td> 94 </tr> 95 </table> 96 </div> 97 <div> 98 <table> 99 <tr> 100 <td>用戶ID:</td> 101 <td> 102 <asp:TextBox ID="txtDUId" runat="server"></asp:TextBox> 103 </td> 104 <td> 105 <asp:Button ID="btnD" runat="server" Text="刪除" OnClick="btnD_Click" /> 106 </td> 107 </tr> 108 </table> 109 </div>
110 <div> 111 <table> 112 <tr> 113 <td>ID:</td> 114 <td> 115 <asp:TextBox ID="txtUId" runat="server"></asp:TextBox> 116 </td> 117 <td> 118 <asp:TextBox ID="txtUUserName" runat="server"></asp:TextBox> 119 </td> 120 <td> 121 <asp:Button ID="btnU" runat="server" Text="更新" OnClick="btnU_Click" /> 122 </td> 123 </tr> 124 </table> 125 </div> 126 <div> 127 <asp:GridView ID="GriVShow" runat="server" AutoGenerateColumns="False"> 128 <Columns> 129 <asp:BoundField DataField="Userid" HeaderText="用戶ID" /> 130 <asp:BoundField DataField="UserName" HeaderText="用戶名：" /> 131 <asp:BoundField DataField="phonenum" HeaderText="電話號碼" /> 132 <asp:BoundField DataField="sex" HeaderText="性別" /> 133 <asp:BoundField DataField="phase" HeaderText="班級" /> 134 <asp:BoundField DataField="qq" HeaderText="QQ" /> 135 <asp:BoundField DataField="Message" HeaderText="信息" /> 136 <asp:BoundField DataField="HeadPic" HeaderText="頭像" /> 137 <asp:BoundField DataField="CreatedTime" HeaderText="創建時間" /> 138 <asp:TemplateField HeaderText="詳情"> 139 <ItemTemplate> 140 <a href="UserInforManagerContext.aspx?Id=<%#Eval("UserId")%>">詳情</a> 141 </ItemTemplate> 142 </asp:TemplateField> 143 </Columns> 144 </asp:GridView> 145 </div>
1 public partial class UserInforManager : System.Web.UI.Page 2 { 3 string constr = ConfigurationManager.ConnectionStrings["sq_r"].ToString(); 4 SqlConnection con = null; 5 SqlCommand cmd = null; 6 SqlDataReader read = null; 7 protected void Page_Load(object sender, EventArgs e) 8 { 9 if (!(IsPostBack)) 10 { 11 BindUserInfor(); 12 } 13 } 14 /// <summary> 15 /// 數據綁定 16 /// </summary> 17 public void BindUserInfor() 18 { 19 try 20 { 21 //string strstring = "select Userid,UserName,phonenum,sex,phase,qq,Message,HeadPic,CreatedTime from UserInfor where 1=1"; 22 using (con = new SqlConnection(constr)) 23 { 24 con.Open(); 25 cmd = new SqlCommand(GetSql(), con); 26 using (read = cmd.ExecuteReader()) 27 { 28 //開始讀以上sql數據,這句話一定要有，如果把它取出來的話是需要讀的，但是這地方我只想它把內容賦值給gridview 29 //if (read.Read()) 30 //{ 31 //} 32 //但是這地方我只想它把內容賦值給gridview 33 //內容取出來之後，我希望有東西可以接收它的內容 34 GriVShow.DataSource = read; 35 GriVShow.DataBind(); 36 } 37 } 38 } 39 catch (Exception ex) 40 { 41 Response.Write("<script>alret('系統正在維護，請聯繫管理員!');</script>"); 42 } 43 } 44 45 protected void btnSel_Click(object sender, EventArgs e) 46 { 47 BindUserInfor(); 48 } 49 /// <summary> 50 /// 獲取SQL 51 /// </summary> 52 /// <returns></returns> 53 public string GetSql() 54 { 55 //string username = txtSUserName.Text.Trim(); 56 //string phase = ddlselPhase.SelectedValue; 57 StringBuilder sb = new StringBuilder(); 58 sb.Append("select Userid,UserName,phonenum,sex,phase,qq,Message,HeadPic,CreatedTime from UserInfor where 1=1"); 59 if (!string.IsNullOrEmpty(txtSUserName.Text.Trim())) 60 { 61 sb.Append(string.Format("and UserName='{0}'", txtSUserName.Text.Trim())); 62 } 63 if (ddlselPhase.SelectedIndex > 0) 64 { 65 sb.Append(string.Format("and phase='{0}'", ddlselPhase.SelectedValue)); 66 } 67 return sb.ToString(); 68 } 69 70 protected void btnAdd_Click(object sender, EventArgs e) 71 { 72 try 73 { 74 string addUserName = txtAddUserName.Text.Trim(); 75 string addPwd = txtAddPwd.Text.Trim(); 76 string addqq = txtAddQq.Text.Trim(); 77 string addPhase = ddlAddPhase.SelectedIndex > 0 ? ddlAddPhase.SelectedValue : ""; 78 if (!string.IsNullOrEmpty(addUserName)) 79 { 80 using (con = new SqlConnection(constr)) 81 { 82 con.Open(); 83 string sstring1 = string.Format("insert into UserInfor(UserName,Pwd,QQ,Phase)values('{0}','{1}','{2}','{3}')", addUserName, addPwd, addqq, addPhase); 84 cmd = new SqlCommand(sstring1, con); 85 if (cmd.ExecuteNonQuery() > 0) 86 { 87 Response.Write("<script>alert('插入成功！');</script>"); 88 } 89 BindUserInfor(); 90 } 91 } 92 else 93 { 94 Response.Write("<script>alert('請輸入內容');</script>"); 95 } 96 } 97 catch (Exception) 98 { 99 Response.Write("網頁正在維護！"); 100 } 101 } 102 103 protected void btnD_Click(object sender, EventArgs e) 104 { 105 int UserId = txtDUId.Text.Trim() == "" ? 0 : Convert.ToInt32(txtDUId.Text.Trim()); 106 try 107 { 108 using (con = new SqlConnection(constr)) 109 { 110 con.Open(); 111 string ssql = string.Format("select UserId from UserInfor where UserId='{0}'", UserId); 112 cmd = new SqlCommand(ssql, con); 113 read = cmd.ExecuteReader(); 114 if (read.HasRows) 115 { 116 read.Dispose(); 117 read.Close(); 118 string sstring1 = string.Format("delete UserInfor where UserId='{0}'", UserId); 119 cmd = new SqlCommand(sstring1, con); 120 if (cmd.ExecuteNonQuery() > 0) 121 { 122 Response.Write("<script>alert('刪除成功！');</script>"); 123 } 124 BindUserInfor(); 125 } 126 else 127 { 128 Response.Write("該用戶不存在！"); 129 } 130 } 131 } 132 catch (Exception ex) 133 { 134 Response.Write("<script>alret('系統正在維護，請聯繫管理員!');</script>"); 135 } 136 } 137 138 protected void btnU_Click(object sender, EventArgs e) 139 { 140 int uId = txtUId.Text.Trim()==""?0:Convert.ToInt32(txtUId.Text.Trim()); 141 string uUserName = txtUUserName.Text.Trim(); 142 try 143 { 144 using (con = new SqlConnection(constr)) 145 { 146 con.Open(); 147 string ssql = string.Format("select UserId from UserInfor where UserId='{0}'", uId); 148 cmd = new SqlCommand(ssql, con); 149 read = cmd.ExecuteReader(); 150 if (read.HasRows) 151 { 152 read.Dispose(); 153 read.Close(); 154 string ssql1 = string.Format("update UserInfor set UserName='{0}' where UserId='{1}'", uUserName, uId); 155 cmd = new SqlCommand(ssql1, con); 156 if (cmd.ExecuteNonQuery() > 0) 157 { 158 Response.Write("更新成功！"); 159 } 160 BindUserInfor(); 161 } 162 else 163 { 164 Response.Write("該用戶不存在！"); 165 } 166 } 167 } 168 catch (Exception) 169 { 170 Response.Write("<script>alret('系統正在維護，請聯繫管理員!');</script>"); 171 } 172 } 173 }
另一種更新的方法，調用cmd的ExecuteScalar()執行查詢，記錄數，返回0或1，返回是object類型
1 <div> 2 <table> 3 <tr> 4 <td>ID:</td> 5 <td> 6 <asp:TextBox ID="txtUuid" runat="server"></asp:TextBox> 7 </td> 8 <td> 9 <asp:TextBox ID="txtUuserName2" runat="server"></asp:TextBox> 10 </td> 11 <td> 12 <asp:Button ID="btnU2" runat="server" Text="更新" OnClick="btnU2_Click"/> 13 </td> 14 </tr> 15 </table> 16 </div>
1 protected void btnU2_Click(object sender, EventArgs e) 2 { 3 int Uid2 = txtUuid.Text.Trim() == "" ? 0 : Convert.ToInt32(txtUuid.Text.Trim()); 4 string uusername2 = txtUuserName2.Text.Trim(); 5 try 6 { 7 using (con = new SqlConnection(constr)) 8 { 9 con.Open(); 10 string ssql = string.Format("select count(*) from UserInfor where userid='{0}'", Uid2); 11 cmd = new SqlCommand(ssql, con); 12 int icount = Convert.ToInt32(cmd.ExecuteScalar().ToString()); 13 if (icount> 0) 14 { 15 string ssql1 = string.Format("update UserInfor set UserName='{0}' where UserId='{1}'", uusername2, Uid2); 16 cmd = new SqlCommand(ssql1,con); 17 if (cmd.ExecuteNonQuery() > 0) 18 { 19 Response.Write("<script>alert('數據更新成功！');</script>"); 20 } 21 BindUserInfor(); 22 } 23 else 24 { 25 Response.Write("該用戶不存在！"); 26 } 27 } 28 } 29 catch (Exception) 30 { 31 Response.Write("<script>alret('系統正在維護，請聯繫管理員!');</script>"); 32 } 33 }
二、Get傳值：傳一些安全繫數低，Id,傳類型，比較小的數據
1 <div> 2 <%=GetUserInfor()%> 3 </div>
1 public partial class UserInforManagerContext : System.Web.UI.Page 2 { 3 /// <summary> 4 ///通過這種方式把ID傳了過去 ?<%#Eval("UserId") %>,接收get傳值後的id 5 /// </summary> 6 /// <param name="sender"></param> 7 /// <param name="e"></param> 8 9 private int _userId;//定義一個欄位,只可以訪問 10 11 public int UserId 12 { 13 //get,set是屬性，既可以訪問，又可以寫 14 get 15 { 16 try 17 { 18 _userId = Request.QueryString["Id"] == "" ? 0 : Convert.ToInt32(Request.QueryString["Id"].ToString()); 19 } 20 catch (Exception) 21 { 22 _userId = 0; 23 } 24 return _userId; 25 } 26 set { _userId = value; } 27 } 28 protected void Page_Load(object sender, EventArgs e) 29 { 30 31 } 32 33 public string GetUserInfor() 34 { 35 StringBuilder sb = new StringBuilder(); 36 sb.Append("<table>"); 37 try 38 { 39 if (UserId > 0) 40 { 41 string constr = ConfigurationManager.ConnectionStrings["sq_r"].ToString(); 42 using (SqlConnection con = new SqlConnection(constr)) 43 { 44 con.Open(); 45 string sString = string.Format("select * from UserInfor where UserId='{0}'", UserId); 46 SqlCommand cmd = new SqlCommand(sString, con); 47 using (SqlDataReader read = cmd.ExecuteReader()) 48 { 49 if (read.HasRows) 50 { 51 if (read.Read()) 52 { 53 sb.Append(string.Format("<tr><td>ID:</td><td>{0}</td></tr>", UserId)); 54 sb.Append(string.Format("<tr><td>用戶名:</td><td>{0}</td></tr>", read["userName
