.net mysql增删改查_ASP.net+SQL server2008简单的数据库增删改查 VS2012

工具：VS2012

数据库：SQL server

简单说明：根据老师上课给的代码，进行了简单的改正适用于VS2012环境，包括注册、登录、查询、修改、删除功能，多数参考了网上的代码

百度云源代码连接testDAO：http://pan.baidu.com/s/1c0CTRgs

遇见的问题：

1、字符文本中字符太多: 在html中用的,在.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 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Web;5 usingSystem.Data.SqlClient;6

7 ///

8 ///DBHelper 的摘要说明9 ///

10 namespacetestDAO.Library11 {12 public classDBHelper13 {//server=.;Trusted_Connection=SSPI;database=easylife

14 private String connectionString = "server=.;database=easylife;uid=sa;pwd=root";15

16 publicSqlDataReader ExecuteReader(String sql)17 {18 SqlConnection connection = newSqlConnection(connectionString);19 connection.Open();20

21 SqlCommand command = newSqlCommand(sql,connection);22

23 SqlDataReader result =command.ExecuteReader();24

25 returnresult;26 }27

28 public boolExecuteCommand(String sql)29 {30 bool result = false;31

32 try

33 {34 SqlConnection connection = newSqlConnection(connectionString);35 connection.Open();36

37 SqlCommand command = newSqlCommand(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 throwe;50 }51

52 returnresult;53 }54

55 }56 }

定义User类封装用户信息  User.cs

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Web;///

///User 的摘要说明///

namespacetestDAO.Library

{public classUser

{private String userName = "";private String userLogin = "";private String userPwd = "";publicString UserName

{get{returnuserName;

}set{

userName=value;

}

}publicString UserLogin

{get{returnuserLogin;

}set{

userLogin=value;

}

}publicString UserPwd

{get{returnuserPwd;

}set{

userPwd=value;

}

}

}

}

采用UserService实现将用户信息的数据库操作 UserService.cs

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Web;5

6 usingSystem.Data.SqlClient;7 ///

8 ///UserService 的摘要说明9 ///

10 namespacetestDAO.Library11 {12 public classUserService13 {14 public boolAddUser(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 = newDBHelper();26 result =helper.ExecuteCommand(sql);27 returnresult;28

29 }30

31 publicUser GetUserByLogin(User user)32 {33 String sql = "";34

35 sql = "select * from table_user where userLogin='" + user.UserLogin + "'";36

37 DBHelper helper = newDBHelper();38 SqlDataReader reader =helper.ExecuteReader(sql);39 User result = newUser();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 returnresult;54 }55

56 public ListGetAllUsers()57 {58 String sql = "";59

60 sql = "select * from table_user";61

62 DBHelper helper = newDBHelper();63 SqlDataReader reader =helper.ExecuteReader(sql);64

65 if (!reader.HasRows)66 {67 return null;68 }69

70 List list = new List();71 while(reader.Read())72 {73 User item = newUser();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 returnlist;83 }84

85 public boolDeleteUsers(String i)86 {87 bool result = false;88 String sql = "";89 sql = "delete from table_user where userLogin ='"+ i+"'";90 DBHelper helper = newDBHelper();91 result =helper.ExecuteCommand(sql);92 returnresult;93 }94

95 public boolUpdateUsers(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 = newDBHelper();102 result =helper.ExecuteCommand(sql);103 returnresult;104 }105

106 }107 }

业务逻辑层UserManager.cs，是表示层与数据访问层的桥梁 ，用于完成逻辑判断、业务处理、数据传递等操作。

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Web;5

6 ///

7 ///UserManager 的摘要说明8 ///

9 namespacetestDAO.Library10 {11 public classUserManager12 {13 public boolAddUser(User user)14 {15 UserService service = newUserService();16 User temp =service.GetUserByLogin(user);17

18 if (temp != null)19 {20 return false;21 }22

23 bool result =service.AddUser(user);24 returnresult;25 }26

27 public boolLogin(User user)28 {29 bool result = false;30

31 UserService service = newUserService();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 returnresult;44 }45

46 public ListGetAllUsers()47 {48 UserService service = newUserService();49 returnservice.GetAllUsers();50 }51 public boolDeleteUser(User user)52 {53 UserService service = newUserService();54

55 bool result =service.DeleteUsers(user.UserLogin);56 returnresult;57

58 }59

60 public boolUpdateUser(User user)61 {62 UserService service = newUserService();63 bool result =service.UpdateUsers(user);64 returnresult;65 }66 }67 }

注册界面代码regeister.aspx

注册界面逻辑代码 regeister.aspx.cs

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Web;5 usingSystem.Web.UI;6 usingSystem.Web.UI.WebControls;7 usingSystem.Data.SqlClient;8 usingtestDAO.Library;9

10 public partial classregister : System.Web.UI.Page11 {12 protected void Page_Load(objectsender, EventArgs e)13 {14

15 }16

17 public voidCreateTable()18 {19 String connectionString = "server=.;Trusted_Connection=SSPI;database=easylife";20 SqlConnection connection = newSqlConnection(connectionString);21 connection.Open();22 SqlCommand command = newSqlCommand();23 command.Connection =connection;24 command.ExecuteNonQuery();25 connection.Close();26 }27 protected void Button1_Click(objectsender, EventArgs e)28 {29 String userName =nameText.Text;30 String userLogin =loginText.Text;31 String userPwd =pwdText.Text;32

33 User user = newUser();34 user.UserName =userName;35 user.UserLogin =userLogin;36 user.UserPwd =userPwd;37

38 bool result = false;39 UserManager manager = newUserManager();40 result =manager.AddUser(user);41 Response.Write(result);42 if(result)43 {44 Response.Write("注册成功");45 }46 else

47 {48 Response.Write("注册失败");49 }50 }51 protected void Button2_Click(objectsender, EventArgs e)52 {53 Response.Redirect("login.aspx");54 }55 }

regeister.aspx.cs

登录界面代码 login.aspx

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23 Text="登录" />

24

25

26

27

28

login.aspx

登录界面逻辑代码 login.aspx.cs

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Web;5 usingSystem.Web.UI;6 usingSystem.Web.UI.WebControls;7 usingtestDAO.Library;8

9 public partial classlogin : System.Web.UI.Page10 {11 protected void Page_Load(objectsender, EventArgs e)12 {13

14 }15 protected void Button1_Click(objectsender, EventArgs e)16 {17 Response.Redirect("register.aspx");18 }19

20 protected void loginButton_Click(objectsender, EventArgs e)21 {22 User user = newUser();23

24 user.UserLogin =loginText.Text;25 user.UserPwd =pwdText.Text;26

27 UserManager manager = newUserManager();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

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

序号

17

姓名

18

帐号

19

密码

20

修改

21

删除

22

23

24 UserManager manager = newUserManager();25 List list =manager.GetAllUsers();26

27 for (int i = 0; i < list.Count; i++)28 {29 Response.Write("

");30 Response.Write("" + i + "");31 Response.Write("" + list[i].UserName + "");32 Response.Write("" + list[i].UserLogin + "");33 Response.Write("" + list[i].UserPwd + "");34

35 Response.Write("

修改");36 Response.Write(" 删除");37

38 Response.Write("

");39 }40 %>

41

42

43

44

45

46

47

48 function test1(j)49 {50 var table = document.getElementById("test");51 table.rows[j + 1].cells[1].innerHTML = "";52 table.rows[j + 1].cells[3].innerHTML = "";53 table.rows[j + 1].cells[4].innerHTML=""

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

65

66

list.aspx

list.aspx.cs代码只有通过点击button按钮转向到注册页面，对其它功能并无影响

通过点击修改进行更新逻辑代码 UserUpdate.aspx.cs代码

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Web;5 usingSystem.Web.UI;6 usingSystem.Web.UI.WebControls;7 usingtestDAO.Library;8

9 public partial classuserUpdate : System.Web.UI.Page10 {11 protected void Page_Load(objectsender, EventArgs e)12 {13

14 User user = newUser();15

16 user.UserName= Request.QueryString["userName"];17 user.UserLogin = Request.QueryString["userLogin"];18 user.UserPwd = Request.QueryString["userPwd"];19 UserManager manager = newUserManager();20 bool result =manager.UpdateUser(user);21 if(result)22 {23 Response.Redirect("list.aspx");24 }25 else

26 {27 Response.Write("修改失败");28 }29

30

31 }32 }

通过点击删除进行删除逻辑代码 userDelete.aspx.cs代码

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Web;5 usingSystem.Web.UI;6 usingSystem.Web.UI.WebControls;7 usingtestDAO.Library;8 public partial classuserDelete : System.Web.UI.Page9 {10 protected void Page_Load(objectsender, EventArgs e)11 {12 User user = newUser();13

14 user.UserLogin = Request.QueryString["userLogin"];15

16

17 UserManager manager = newUserManager();18 bool result =manager.DeleteUser(user);19 if(result)20 {21 Response.Redirect("list.aspx");22 }23 else

24 {25 Response.Write("删除失败");26 }27

28 }29 }