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