你的位置:首页 > ASP.net教程

[ASP.net教程]ASP.net+SQL server2008简单的数据库增删改查 VS2012


 

工具: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 }