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

工具：VS2012

数据库：SQLserver

简单说明：根据老师上课给的代码，进行了简单的改正适用于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

Stringx= 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 resu