工具：VS2012
数据库：SQL server
简单说明：根据老师上课给的代码，进行了简单的改正适用于VS2012环境，包括注册、登录、查询、修改、删除功能，多数参考了网上的代码
百度云源代码连接testDAO：http://pan.baidu.com/s/1c0CTRgs
遇见的问题：
1、字符文本中字符太多: 在html中用的<a href=" ">,在.NET中需要把双引号变成单引号，javascript中的部分双引号也需变成单引号，此处代码详见register.aspx
2、如何javascript获取表格中的行数：通过varx=document.getElementById("表格id");找到table，x.rows[].cells[]即可找到第几行第几列 此处代码详见register.aspx的javascript代码
3、如何通过asp获取url中参数的值：http://localhost:30965/testDAO/list.aspx？username=16&psaaword=21
String x= Request.QueryString["username"];即可获得username的值16
文件结构如右图所示
数据库名字：easylife 表的名字：table_user 表内容如图：
界面如下图所示：
DBHelper.cs代码：在每一个对象的数据库访问类中：1、数据库连接反复出现 2、数据库连接打开和关闭反复出现 3、执行Sql语句的方法相似
因此，定义DBHelper类，封装常用的方法
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Data.SqlClient; 6 7 /// <summary> 8 /// DBHelper 的摘要说明 9 /// </summary>10 namespace testDAO.Library11 {12 public class DBHelper13 {//server=.;Trusted_Connection=SSPI;database=easylife14 private String connectionString = "server=.;database=easylife;uid=sa;pwd=root";15 16 public SqlDataReader ExecuteReader(String sql)17 {18 SqlConnection connection = new SqlConnection(connectionString);19 connection.Open();20 21 SqlCommand command = new SqlCommand(sql,connection);22 23 SqlDataReader result = command.ExecuteReader();24 25 return result;26 }27 28 public bool ExecuteCommand(String sql)29 {30 bool result = false;31 32 try33 {34 SqlConnection connection = new SqlConnection(connectionString);35 connection.Open();36 37 SqlCommand command = new SqlCommand(sql,connection);38 //command.Connection = connection;39 //command.CommandText = sql;40 command.ExecuteNonQuery();41 42 43 connection.Close();44 45 result = true;46 }47 catch (Exception e)48 {49 throw e;50 }51 52 return result;53 }54 55 }56 }定义User类封装用户信息 User.cs
using System;using System.Collections.Generic;using System.Linq;using System.Web;/// <summary>/// User 的摘要说明/// </summary>namespace testDAO.Library{ public class User { private String userName = ""; private String userLogin = ""; private String userPwd = ""; public String UserName { get { return userName; } set { userName = value; } } public String UserLogin { get { return userLogin; } set { userLogin = value; } } public String UserPwd { get { return userPwd; } set { userPwd = value; } } }}
采用UserService实现将用户信息的数据库操作 UserService.cs
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 6 using System.Data.SqlClient; 7 /// <summary> 8 /// UserService 的摘要说明 9 /// </summary> 10 namespace testDAO.Library 11 { 12 public class UserService 13 { 14 public bool AddUser(User user) 15 { 16 bool result = false; 17 String sql = ""; 18 19 sql = "insert into table_user (userName,userLogin,userPwd)values("; 20 sql += "'" + user.UserName + "',"; 21 sql += "'" + user.UserLogin + "',"; 22 sql += "'" + user.UserPwd + "'"; 23 sql += ")"; 24 25 DBHelper helper = new DBHelper(); 26 result = helper.ExecuteCommand(sql); 27 return result; 28 29 } 30 31 public User GetUserByLogin(User user) 32 { 33 String sql = ""; 34 35 sql = "select * from table_user where userLogin='" + user.UserLogin + "'"; 36 37 DBHelper helper = new DBHelper(); 38 SqlDataReader reader = helper.ExecuteReader(sql); 39 User result = new User(); 40 if (reader.Read()) 41 { 42 43 result.UserName = reader.GetString(0); 44 result.UserLogin = reader.GetString(1); 45 result.UserPwd = reader.GetString(2); 46 47 } 48 else 49 { 50 return null; 51 } 52 53 return result; 54 } 55 56 public List<User> GetAllUsers() 57 { 58 String sql = ""; 59 60 sql = "select * from table_user"; 61 62 DBHelper helper = new DBHelper(); 63 SqlDataReader reader = helper.ExecuteReader(sql); 64 65 if (!reader.HasRows) 66 { 67 return null; 68 } 69 70 List<User> list = new List<User>(); 71 while (reader.Read()) 72 { 73 User item = new User(); 74 75 item.UserName = reader.GetString(0); 76 item.UserLogin = reader.GetString(1); 77 item.UserPwd = reader.GetString(2); 78 79 list.Add(item); 80 } 81 82 return list; 83 } 84 85 public bool DeleteUsers(String i) 86 { 87 bool result = false; 88 String sql = ""; 89 sql = "delete from table_user where userLogin ='"+ i+" '" ; 90 DBHelper helper = new DBHelper(); 91 result = helper.ExecuteCommand(sql); 92 return result; 93 } 94 95 public bool UpdateUsers(User user) 96 { 97 bool result = false; 98 String sql = ""; 99 sql = "update table_user set userName= '" + user.UserName + "',userPwd='" + user.UserPwd + " ' where userlogin='" + user.UserLogin + " '";100 // update table_user set userName='1',userPwd='1' where userLogin='5'101 DBHelper helper = new DBHelper();102 result = helper.ExecuteCommand(sql);103 return result;104 }105 106 }107 }
业务逻辑层UserManager.cs，是表示层与数据访问层的桥梁 ，用于完成逻辑判断、业务处理、数据传递等操作。
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 6 /// <summary> 7 /// UserManager 的摘要说明 8 /// </summary> 9 namespace testDAO.Library10 {11 public class UserManager12 {13 public bool AddUser(User user)14 {15 UserService service = new UserService();16 User temp = service.GetUserByLogin(user);17 18 if (temp != null)19 {20 return false;21 }22 23 bool result = service.AddUser(user);24 return result;25 }26 27 public bool Login(User user)28 {29 bool result = false;30 31 UserService service = new UserService();32 33 User temp = service.GetUserByLogin(user);34 if (temp == null)35 {36 result = false;37 }38 else if (user.UserPwd.Equals(temp.UserPwd))39 {40 result = true;41 }42 43 return result;44 }45 46 public List<User> GetAllUsers()47 {48 UserService service = new UserService();49 return service.GetAllUsers();50 }51 public bool DeleteUser(User user)52 {53 UserService service = new UserService();54 55 bool result = service.DeleteUsers(user.UserLogin);56 return result;57 58 }59 60 public bool UpdateUser(User user)61 {62 UserService service = new UserService();63 bool result = service.UpdateUsers(user);64 return result;65 }66 }67 }
注册界面代码regeister.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="register.aspx.cs" Inherits="register" %><!DOCTYPE html><html "http://www.w3.org/1999/xhtml"><head runat="server"><meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <title></title></head><body> <form id="form1" runat="server"> <div> <br /> <asp:Label ID="Label1" runat="server" Text="姓名："></asp:Label> <asp:TextBox ID="nameText" runat="server"></asp:TextBox> <br /> <br /> <asp:Label ID="Label2" runat="server" Text="帐号："></asp:Label> <asp:TextBox ID="loginText" runat="server"></asp:TextBox> <br /> <br /> <asp:Label ID="Label3" runat="server" Text="密码："></asp:Label> <asp:TextBox ID="pwdText" runat="server"></asp:TextBox> <br /> <br /> <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="注册" /> <asp:Button ID="Button2" runat="server" onclick="Button2_Click" Text="转向登录" /> </div> </form></body></html>
注册界面逻辑代码 regeister.aspx.cs
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Web.UI; 6 using System.Web.UI.WebControls; 7 using System.Data.SqlClient; 8 using testDAO.Library; 9 10 public partial class register : System.Web.UI.Page11 {12 protected void Page_Load(object sender, EventArgs e)13 {14 15 }16 17 public void CreateTable()18 { 19 String connectionString = "server=.;Trusted_Connection=SSPI;database=easylife";20 SqlConnection connection = new SqlConnection(connectionString);21 connection.Open();22 SqlCommand command = new SqlCommand();23 command.Connection = connection;24 command.ExecuteNonQuery();25 connection.Close();26 }27 protected void Button1_Click(object sender, EventArgs e)28 {29 String userName = nameText.Text;30 String userLogin = loginText.Text;31 String userPwd = pwdText.Text;32 33 User user = new User();34 user.UserName = userName;35 user.UserLogin = userLogin;36 user.UserPwd = userPwd;37 38 bool result = false;39 UserManager manager = new UserManager();40 result = manager.AddUser(user);41 Response.Write(result);42 if (result)43 {44 Response.Write("注册成功");45 }46 else47 {48 Response.Write("注册失败");49 }50 }51 protected void Button2_Click(object sender, EventArgs e)52 {53 Response.Redirect("login.aspx");54 }55 }regeister.aspx.cs
登录界面代码 login.aspx
1 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="login.aspx.cs" Inherits="login" %> 2 3 <!DOCTYPE html> 4 5 <html "http://www.w3.org/1999/xhtml"> 6 <head runat="server"> 7 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> 8 <title></title> 9 </head>10 <body>11 <form id="form1" runat="server">12 <div>13 <br />14 <asp:Label ID="Label1" runat="server" Text="帐号："></asp:Label>15 <asp:TextBox ID="loginText" runat="server"></asp:TextBox>16 <br />17 <br />18 <asp:Label ID="Label2" runat="server" Text="密码："></asp:Label>19 <asp:TextBox ID="pwdText" runat="server"></asp:TextBox>20 <br />21 <br />22 <asp:Button ID="loginButton" runat="server" onclick="loginButton_Click" 23 Text="登录" />24 <asp:Button ID="Button1" runat="server" Text="转向注册" OnClick="Button1_Click" />25 </div>26 </form>27 </body>28 </html>
login.aspx
登录界面逻辑代码 login.aspx.cs
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Web.UI; 6 using System.Web.UI.WebControls; 7 using testDAO.Library; 8 9 public partial class login : System.Web.UI.Page10 {11 protected void Page_Load(object sender, EventArgs e)12 {13 14 }15 protected void Button1_Click(object sender, EventArgs e) 16 {17 Response.Redirect("register.aspx");18 }19 20 protected void loginButton_Click(object sender, EventArgs e)21 {22 User user = new User(); 23 24 user.UserLogin = loginText.Text;25 user.UserPwd = pwdText.Text;26 27 UserManager manager = new UserManager();28 bool result = manager.Login(user);29 if (result)30 {31 Response.Redirect("list.aspx");32 }33 else 34 {35 Response.Write("登录失败,请输入正确的用户名和密码");36 }37 38 39 }40 }login.aspx.cs
显示界面代码：
显示界面相关说明：
显示界面图片是这样：
当点击修改时图片如下：
点击修改时通过table获得table中的行数，从而改变行数中相应的内容，相关代码在javascript中
参数传值通过URL获取
1 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="list.aspx.cs" Inherits="list" %> 2 3 <%@ Import Namespace="testDAO.Library" %> 4 <!DOCTYPE html> 5 6 <html "http://www.w3.org/1999/xhtml"> 7 <head runat="server"> 8 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> 9 <title></title>10 </head>11 <body>12 <form id="form1" runat="server">13 <div>14 <table id="test" width="1000" align="center" border = "1" cellpadding="1" cellspacing="1" bordercolordark="#808080" bordercolorlight="#ffffff" >15 <tr>16 <td align="center">序号</td>17 <td align="center">姓名</td>18 <td align="center">帐号</td>19 <td align="center">密码</td>20 <td align="center">修改</td>21 <td align="center">删除</td>22 </tr>23 <%24 UserManager manager = new UserManager();25 List<User> list = manager.GetAllUsers();26 27 for (int i = 0; i < list.Count; i++)28 {29 Response.Write("<tr >");30 Response.Write("<td align='center'>" + i + "</td>");31 Response.Write("<td align='center'>" + list[i].UserName + "</td>");32 Response.Write("<td align='center' id='loginText'>" + list[i].UserLogin + "</td>");33 Response.Write("<td align='center'>" + list[i].UserPwd + "</td>");34 35 Response.Write("<td align='center'><input type='Button' value='修改' onclick='test1("+i+")' >修改</td>");36 Response.Write("<td align='center'><a href='userDelete.aspx?userLogin=" + list[i].UserLogin + "'>删除</a></td>");37 38 Response.Write("</tr>");39 }40 %>41 </table>42 <asp:Button ID="button" runat="server" Text="转向注册" OnClick="Button1_Click" />43 44 </div>45 </form>46 <script type="text/javascript">47 48 function test1(j)49 {50 var table = document.getElementById("test");51 table.rows[j + 1].cells[1].innerHTML = "<input type='text' id='nameText' >"; 52 table.rows[j + 1].cells[3].innerHTML = "<input type='text' id='pwdText' >";53 table.rows[j + 1].cells[4].innerHTML="<input type='button' value='确定' onclick='tiaozhuan("+j+")' >"54 55 }56 function tiaozhuan(i) {57 var table = document.getElementById("test");58 var userName=document.getElementById("nameText").value;59 var userPwd = document.getElementById("pwdText").value;60 var userLogin=table.rows[i + 1].cells[2].innerHTML;61 62 location.href="userUpdate.aspx?userName="+ userName+"&userPwd="+userPwd+"&userLogin="+userLogin+" ";63 }64 </script>65 </body>66 </html>list.aspx
list.aspx.cs代码只有通过点击button按钮转向到注册页面，对其它功能并无影响
通过点击修改进行更新逻辑代码 UserUpdate.aspx.cs代码
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Web.UI; 6 using System.Web.UI.WebControls; 7 using testDAO.Library; 8 9 public partial class userUpdate : System.Web.UI.Page10 {11 protected void Page_Load(object sender, EventArgs e)12 {13 14 User user = new User();15 16 user.UserName= Request.QueryString["userName"];17 user.UserLogin = Request.QueryString["userLogin"];18 user.UserPwd = Request.QueryString["userPwd"];19 UserManager manager = new UserManager();20 bool result = manager.UpdateUser(user);21 if (result)22 {23 Response.Redirect("list.aspx");24 }25 else26 {27 Response.Write("修改失败");28 }29 30 31 }32 }
通过点击删除进行删除逻辑代码 userDelete.aspx.cs代码
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Web.UI; 6 using System.Web.UI.WebControls; 7 using testDAO.Library; 8 public partial class userDelete : System.Web.UI.Page 9 {10 protected void Page_Load(object sender, EventArgs e)11 {12 User user = new User();13 14 user.UserLogin = Request.QueryString["userLogin"];15 16 17 UserManager manager = new UserManager();18 bool result = manager.DeleteUser(user);19 if (result)20 {21 Response.Redirect("list.aspx");22 }23 else24 {25 Response.Write("删除失败");26 }27 28 }29 }
原标题：ASP.net+SQL server2008简单的数据库增删改查 VS2012
关键词：ASP.NET
我知道了 