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

Stella981
• 阅读 389

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

文件结构如右图所示ASP.net+SQL server2008简单的数据库增删改查 VS2012  

数据库名字:easylife  表的名字:table_user  表内容如图:ASP.net+SQL server2008简单的数据库增删改查 VS2012

界面如下图所示:ASP.net+SQL server2008简单的数据库增删改查 VS2012 ASP.net+SQL server2008简单的数据库增删改查 VS2012 ASP.net+SQL server2008简单的数据库增删改查 VS2012

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.Library
11 {
12     public class DBHelper
13     {//server=.;Trusted_Connection=SSPI;database=easylife
14         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             try
33             {
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.Library
10 {
11     public class UserManager
12     {
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 xmlns="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

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

 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.Page
11     {
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             else
47             {
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

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

 1 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="login.aspx.cs" Inherits="login" %>
 2 
 3 <!DOCTYPE html>
 4 
 5 <html xmlns="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

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

 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.Page
10 {
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

显示界面代码:

显示界面相关说明:

显示界面图片是这样:ASP.net+SQL server2008简单的数据库增删改查 VS2012

当点击修改时图片如下:

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

点击修改时通过table获得table中的行数,从而改变行数中相应的内容,相关代码在javascript中

参数传值通过URL获取

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

 1 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="list.aspx.cs" Inherits="list" %>
 2 
 3 <%@ Import Namespace="testDAO.Library" %>
 4 <!DOCTYPE html>
 5 
 6 <html xmlns="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.Page
10 {
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      else
26      {
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         else
24         {
25             Response.Write("删除失败");
26         }
27 
28     }
29 }
点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
2年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Stella981 Stella981
2年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
2年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
2年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
1个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这