首页 购物 网址 三丰软件 | 小说 美女秀 图库大全 游戏 笑话 | 下载 开发知识库 新闻 开发 图片素材
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
移动开发 架构设计 编程语言 Web前端 互联网
开发杂谈 系统运维 研发管理 数据库 云计算 Android开发资料
资讯 业界资讯 软件杂谈 编程开发 网站建设 网络观查 搜索引擎 移动应用 网站运营 网络地图
开发 移动开发 Web前端 架构设计 编程语言 互联网 数据库 系统运维 云计算 开发杂谈
[编程语言] 四个DBHelper实现
四个DBHelper实现

0.
建一个通用的处理数据的类
[csharp] view plaincopyprint?
  1. using System;  
  2. using System.Data;  
  3. using System.Data.SqlClient;  
  4. using System.Text;  
  5. namespace Document  
  6. {  
  7.     /**//// <summary>  
  8.     /// Summary description for DataHelper.  
  9.     /// </summary>  
  10.     public class DataHelper  
  11.     {  
  12.         public DataHelper()  
  13.         {  
  14.             //  
  15.             // TODO: Add constructor logic here  
  16.             //  
  17.         }  
  18.         public static string ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];  
  19.       
  20.         GetDataSet#region GetDataSet  
  21.         public static DataSet GetDataSet(string sql)  
  22.         {  
  23.             SqlDataAdapter    sda =new SqlDataAdapter(sql,ConnectionString);  
  24.             DataSet ds=new DataSet();  
  25.             sda.Fill(ds);  
  26.             return ds;  
  27.         }  
  28.         #endregion  
  29.   
  30.         ExecCommand#region ExecCommand  
  31.         public static int ExecCommand(SqlCommand sqlcom)  
  32.         {  
  33.             SqlConnection conn=new SqlConnection(ConnectionString);  
  34.             sqlcom.Connection =conn;  
  35.             conn.Open();  
  36.             try  
  37.             {  
  38.                 int rtn=sqlcom.ExecuteNonQuery();  
  39.                 return rtn;  
  40.             }  
  41.             catch(Exception ex)   
  42.             {  
  43.                 throw ex;                  
  44.             }  
  45.             finally  
  46.             {  
  47.                 conn.Close();  
  48.             }  
  49.             return 0;  
  50.   
  51.         }  
  52.         public static int ExecCommand(string sql)  
  53.         {  
  54.             if (sql.EndsWith(",")) sql=sql.Substring(0,sql.Length-1);  
  55.           
  56.             SqlCommand sqlcom=new SqlCommand(sql);  
  57.             return ExecCommand(sqlcom);                  
  58.         }  
  59.         #endregion  
  60.           
  61.         ExecuteScalar#region ExecuteScalar  
  62.         public static object ExecuteScalar(string sql)  
  63.         {  
  64.             SqlConnection conn=new SqlConnection(ConnectionString);  
  65.             SqlCommand sqlcom=new SqlCommand(sql,conn);  
  66.             conn.Open();  
  67.             try  
  68.             {  
  69.                 object rtn=sqlcom.ExecuteScalar ();  
  70.                 return rtn;  
  71.             }  
  72.             catch(Exception ex)   
  73.             {  
  74.                 throw ex;                  
  75.             }  
  76.             finally  
  77.             {  
  78.                 conn.Close();  
  79.             }  
  80.             return null;  
  81.         }  
  82.         #endregion  
  83.   
  84.         ExecSPCommand#region ExecSPCommand  
  85.         public static void ExecSPCommand(string sql,System.Data.IDataParameter[] paramers)  
  86.         {  
  87.             SqlConnection conn=new SqlConnection(ConnectionString);  
  88.             SqlCommand sqlcom=new SqlCommand(sql,conn);  
  89.             sqlcom.CommandType= CommandType.StoredProcedure ;  
  90.   
  91.             foreach(System.Data.IDataParameter paramer in paramers)  
  92.             {  
  93.                 sqlcom.Parameters.Add(paramer);  
  94.             }              
  95.             conn.Open();  
  96.             try  
  97.             {  
  98.                 sqlcom.ExecuteNonQuery();  
  99.             }  
  100.             catch(Exception ex)   
  101.             {  
  102.                 string s=ex.Message ;  
  103.             }  
  104.             finally  
  105.             {  
  106.                 conn.Close();  
  107.             }  
  108.         }  
  109.         #endregion  
  110.   
  111.         ExecSPDataSet#region ExecSPDataSet  
  112.         public static DataSet ExecSPDataSet(string sql,System.Data.IDataParameter[] paramers)  
  113.         {  
  114.             SqlConnection conn=new SqlConnection(ConnectionString);  
  115.             SqlCommand sqlcom=new SqlCommand(sql,conn);  
  116.             sqlcom.CommandType= CommandType.StoredProcedure ;  
  117.   
  118.             foreach(System.Data.IDataParameter paramer in paramers)  
  119.             {  
  120.                 sqlcom.Parameters.Add(paramer);  
  121.             }              
  122.             conn.Open();  
  123.               
  124.             SqlDataAdapter da=new SqlDataAdapter();  
  125.             da.SelectCommand=sqlcom;  
  126.             DataSet ds=new DataSet();  
  127.             da.Fill(ds);  
  128.           
  129.             conn.Close();  
  130.             return ds;  
  131.         }  
  132.  
  133.         #endregion  
  134.   
  135.         DbType#region DbType  
  136.         private static System.Data.DbType GetDbType(Type type)  
  137.         {  
  138.             DbType result = DbType.String;  
  139.             if( type.Equals(typeof(int)) ||  type.IsEnum)  
  140.                 result = DbType.Int32;  
  141.             else if( type.Equals(typeof(long)))  
  142.                 result = DbType.Int32;  
  143.             else if( type.Equals(typeof(double)) || type.Equals( typeof(Double)))  
  144.                 result = DbType.Decimal;  
  145.             else if( type.Equals(typeof(DateTime)))  
  146.                 result = DbType.DateTime;  
  147.             else if( type.Equals(typeof(bool)))  
  148.                 result = DbType.Boolean;  
  149.             else if( type.Equals(typeof(string) ) )  
  150.                 result = DbType.String;  
  151.             else if( type.Equals(typeof(decimal)))  
  152.                 result = DbType.Decimal;  
  153.             else if( type.Equals(typeof(byte[])))  
  154.                 result = DbType.Binary;  
  155.             else if( type.Equals(typeof(Guid)))  
  156.                 result = DbType.Guid;  
  157.           
  158.             return result;  
  159.               
  160.         }  
  161.  
  162.         #endregion  
  163.   
  164.         UpdateTable#region UpdateTable  
  165.         public static void UpdateTable(DataTable dt,string TableName,string KeyName)  
  166.         {  
  167.             foreach(DataRow dr in dt.Rows)  
  168.             {  
  169.                 updateRow(dr,TableName,KeyName);  
  170.             }  
  171.         }  
  172.         #endregion  
  173.   
  174.         InsertTable#region InsertTable  
  175.         //用于主键是数据库表名+ID类型的  
  176.         public static void InsertTable(DataTable dt)  
  177.         {  
  178.             string TableName="["+dt.TableName+"]";  
  179.             string KeyName=dt.TableName+"ID";  
  180.             foreach(DataRow dr in dt.Rows)  
  181.             {  
  182.                 insertRow(dr,TableName,KeyName);  
  183.             }  
  184.         }  
  185.         //用于主键是任意类型的  
  186.         public static void InsertTable(DataTable dt,string KeyName)  
  187.         {  
  188.             string TableName="["+dt.TableName+"]";  
  189.             foreach(DataRow dr in dt.Rows)  
  190.             {  
  191.                 insertRow(dr,TableName,KeyName);  
  192.             }  
  193.         }  
  194.         #endregion  
  195.   
  196.         DeleteTable#region DeleteTable  
  197.         public static void DeleteTable(DataTable dt,string KeyName)  
  198.         {  
  199.             string TableName="["+dt.TableName+"]";  
  200.             foreach(DataRow dr in dt.Rows)  
  201.             {  
  202.                 deleteRow(dr,TableName,KeyName);  
  203.             }  
  204.         }  
  205.         #endregion  
  206.   
  207.         updateRow#region updateRow  
  208.         private static void  updateRow(DataRow dr,string TableName,string KeyName)  
  209.         {  
  210.             if (dr[KeyName]==DBNull.Value )   
  211.             {  
  212.                 throw new Exception(KeyName +"的值不能为空");  
  213.             }  
  214.               
  215.             if (dr.RowState ==DataRowState.Deleted)  
  216.             {  
  217.                 deleteRow(dr,TableName,KeyName);  
  218.    
  219.             }  
  220.             else if (dr.RowState ==DataRowState.Modified )  
  221.             {  
  222.                 midifyRow(dr,TableName,KeyName);  
  223.             }  
  224.             else if (dr.RowState ==DataRowState.Added  )  
  225.             {  
  226.                 insertRow(dr,TableName,KeyName);  
  227.             }  
  228.             else if (dr.RowState ==DataRowState.Unchanged )  
  229.             {  
  230.                 midifyRow(dr,TableName,KeyName);  
  231.             }             
  232.         }  
  233.  
  234.         #endregion  
  235.   
  236.         deleteRow#region deleteRow  
  237.         private static void  deleteRow(DataRow dr,string TableName,string KeyName)  
  238.         {  
  239.             string sql="Delete {0} where {1} =@{1}";  
  240.             DataTable dtb=dr.Table ;  
  241.             sql=string.Format(sql,TableName,KeyName);  
  242.   
  243.             SqlCommand sqlcom=new SqlCommand(sql);  
  244.             System.Data.IDataParameter iparam=new  SqlParameter();  
  245.             iparam.ParameterName    = "@"+ KeyName;  
  246.             iparam.DbType            = GetDbType(dtb.Columns[KeyName].DataType);  
  247.             iparam.Value            = dr[KeyName];  
  248.             sqlcom.Parameters .Add(iparam);  
  249.               
  250.             ExecCommand(sqlcom);  
  251.         }  
  252.         #endregion  
  253.   
  254.         midifyRow#region midifyRow  
  255.         private static void  midifyRow(DataRow dr,string TableName,string KeyName)  
  256.         {  
  257.             string UpdateSql            = "Update {0} set {1} {2}";  
  258.             string setSql="{0}= @{0}";  
  259.             string wherSql=" Where {0}=@{0}";  
  260.             StringBuilder setSb    = new StringBuilder();  
  261.   
  262.             SqlCommand sqlcom=new SqlCommand();  
  263.             DataTable dtb=dr.Table;  
  264.           
  265.             for (int k=0; k<dr.Table.Columns.Count; ++k)  
  266.             {  
  267.                 System.Data.IDataParameter iparam=new  SqlParameter();  
  268.                 iparam.ParameterName    = "@"+ dtb.Columns[k].ColumnName;  
  269.                 iparam.DbType            = GetDbType(dtb.Columns[k].DataType);  
  270.                 iparam.Value            = dr[k];  
  271.                 sqlcom.Parameters .Add(iparam);  
  272.   
  273.                 if (dtb.Columns[k].ColumnName==KeyName)  
  274.                 {  
  275.                     wherSql=string.Format(wherSql,KeyName);  
  276.                 }  
  277.                 else  
  278.                 {  
  279.                     setSb.Append(string.Format(setSql,dtb.Columns[k].ColumnName));      
  280.                     setSb.Append(",");  
  281.                 }  
  282.                   
  283.             }  
  284.               
  285.             string setStr=setSb.ToString();  
  286.             setStr=setStr.Substring(0,setStr.Length -1); //trim ,  
  287.               
  288.             string sql = string.Format(UpdateSql, TableName, setStr,wherSql);  
  289.             sqlcom.CommandText =sql;      
  290.             try  
  291.             {  
  292.                 ExecCommand(sqlcom);  
  293.             }  
  294.             catch(Exception ex)  
  295.             {  
  296.                 throw ex;              
  297.             }  
  298.         }  
  299.         #endregion  
  300.   
  301.         insertRow#region insertRow  
  302.         private static void  insertRow(DataRow dr,string TableName,string KeyName)  
  303.         {  
  304.             string InsertSql = "Insert into {0}({1}) values({2})";  
  305.             SqlCommand sqlcom=new SqlCommand();  
  306.             DataTable dtb=dr.Table ;  
  307.             StringBuilder insertValues    = new StringBuilder();  
  308.             StringBuilder cloumn_list    = new StringBuilder();  
  309.             for (int k=0; k<dr.Table.Columns.Count; ++k)  
  310.             {  
  311.                 //just for genentae,  
  312.                 if (dtb.Columns[k].ColumnName==KeyName) continue;  
  313.                 System.Data.IDataParameter iparam=new  SqlParameter();  
  314.                 iparam.ParameterName    = "@"+ dtb.Columns[k].ColumnName;  
  315.                 iparam.DbType            = GetDbType(dtb.Columns[k].DataType);  
  316.                 iparam.Value            = dr[k];  
  317.                 sqlcom.Parameters .Add(iparam);  
  318.   
  319.                 cloumn_list.Append(dtb.Columns[k].ColumnName);  
  320.                 insertValues.Append("@"+dtb.Columns[k].ColumnName);  
  321.   
  322.                 cloumn_list.Append(",");  
  323.                 insertValues.Append(",");  
  324.             }  
  325.               
  326.             string cols=cloumn_list.ToString();  
  327.             cols=cols.Substring(0,cols.Length -1);  
  328.   
  329.             string values=insertValues.ToString();  
  330.             values=values.Substring(0,values.Length -1);  
  331.               
  332.             string sql = string.Format(InsertSql, TableName,cols ,values);  
  333.             sqlcom.CommandText =sql;      
  334.             try  
  335.             {  
  336.                 ExecCommand(sqlcom);  
  337.             }  
  338.             catch(Exception ex)  
  339.             {  
  340.                 throw ex;  
  341.             }  
  342.         }  
  343.         #endregion  
  344.     }  
  345. }  


2..调用范例
[csharp] view plaincopyprint?
  1. Insert#region Insert  
  2. private void InsertUserInfo()  
  3. {  
  4.     DataTable dt=ds.Tables[0];  
  5.     dt.TableName="UserInfo";  
  6.         string keyname="UserInfoID";  
  7.     DataRow dr=dt.NewRow();  
  8.     dr["LoginName"]=this.txtUserName.Value;  
  9.     dr["Pass"]=this.txtPassword.Value;  
  10.     dr["NickName"]=this.txtNickName.Value;  
  11.     dr["UserType"]=1;  
  12.     dr["IsActive"]=false;  
  13.     dr["RegisterDate"]=System.DateTime.Now;  
  14.     dt.Rows.Add(dr);  
  15.     dt.AcceptChanges();  
  16.     DataHelper.InsertTable(dt,keyname);  
  17. }  
  18. #endregion  
  19.   
  20. Update#region Update  
  21. private void UpdateUserInfo(string UserID)  
  22. {              
  23.     DataSet ds=GetUserOther(UserID);  
  24.     DataTable dt=ds.Tables[0];  
  25.     dt.TableName="UserInfo";  
  26.         string keyname="UserID";  
  27.     DataRow dr=dt.Rows[0];  
  28.     dr["LoginName"]=this.txtUserName.Value;  
  29.     dr["Pass"]=this.txtPassword.Value;  
  30.     dr["NickName"]=this.txtNickName.Value;  
  31.     dr["UserType"]=1;  
  32.     dr["IsActive"]=false;  
  33.     dr["RegisterDate"]=System.DateTime.Now;  
  34.     dt.Rows.Add(dr);  
  35.     dt.AcceptChanges();  
  36.     DataHelper.UpdateTable(dt,dt.TableName,keynanme);  
  37. }  
  38.  
  39. #endregion  
  40.   
  41. Delete  


 
 
 
 
 
1.
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 
 1 using System;
  2  using System.Collections.Generic;
  3 using System.Data.SqlClient;
  4 using System.Configuration;
  5 using System.Data;
  6
  7 namespace Common
  8 {
  9     public abstractclass DbHelperSQL
10     {
11         //格式化字符串
12         public staticstring inSQL(string formatStr)
13         {
14            string Str= formatStr;
15            if (formatStr!=null&& formatStr!=string.Empty)
16             {
17                 Str= Str.Replace("'","''");
18             }
19            return Str;
20         }
21
22         //获取连接字符串
23         public staticstring ConnectionString
24         {
25            get
26             {
27                string _connectionstring= ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
28                string ConStringEncrypt= ConfigurationManager.AppSettings["ApplicationServices"];
29                if (ConStringEncrypt=="true")
30                 {
31                     _connectionstring= DESEncrypt.Encrypt(_connectionstring);
32                 }
33                return _connectionstring;
34             }
35         }
36
37         #region 执行带参数的SQL语句
38
39         // 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )
40         public static  SqlDataReader ExecuteReader(string SQLString,params SqlParameter[] cmdParms)
41         {
42             SqlConnection connection=new SqlConnection(ConnectionString);
43             SqlCommand cmd=new SqlCommand();
44            try
45             {
46                 PrepareCommand(cmd,connection,null,SQLString,cmdParms);
47                 SqlDataReader myReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
48                 cmd.Parameters.Clear();
49                return myReader;
50             }
51            catch(System.Data.SqlClient.SqlException e)
52             {
53                throw e;
54             }
55         }
56
57         // 执行SQL语句,返回影响的记录数
58         public staticint ExecuteSql(string SQLString,params SqlParameter[] cmdParms)
59         {
60            using (SqlConnection connection=new SqlConnection(ConnectionString))
61             {
62                using (SqlCommand cmd=new SqlCommand())
63                 {
64                    try
65                     {
66                         PrepareCommand(cmd,connection,null,SQLString,cmdParms);
67                        int rows=cmd.ExecuteNonQuery();
68                         cmd.Parameters.Clear();
69                        return rows;
70                     }
71                    catch(System.Data.SqlClient.SqlException e)
72                     {
73                        throw e;
74                     }
75                 }
76             }
77         }
78        
79         // 执行查询语句,返回DataSet
80         private staticvoid PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans,string cmdText, SqlParameter[] cmdParms)
81         {
82            if (conn.State!= ConnectionState.Open)
83                 conn.Open();
84             cmd.Connection= conn;
85             cmd.CommandText= cmdText;
86            if (trans!= null)
87                 cmd.Transaction= trans;
88             cmd.CommandType= CommandType.Text;
89            if (cmdParms!=null)
90             {
91                foreach (SqlParameter parameterin cmdParms)
92                 {
93                    if ((parameter.Direction== ParameterDirection.InputOutput|| parameter.Direction== ParameterDirection.Input)&&
94                         (parameter.Value==null))
95                     {
96                         parameter.Value= DBNull.Value;
97                     }
98                     cmd.Parameters.Add(parameter);
99                 }
100             }
101         }
102
103        #endregion
104     }
105 }
106
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
2.
适用于Asp.net的完整的DbHelperSQL类
本文来源于:http://www.lmwlove.com/ac/ID692
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace DBUtility
{
    public abstract class DbHelperSQL
    {
        public DbHelperSQL()
        {
        }
        //获取解密后的字符串
        protected static string connectionString = new SymmetricMethod().Decrypto(ConfigurationSettings.AppSettings["connectionString"].ToString());
        public static int GetMaxID(string FieldName, string TableName)
        {
            string strsql = "select isnull(max(" + FieldName + "),0)+1 from " + TableName;
            object obj = DbHelperSQL.GetSingle(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }
        public static bool Exists(string strSql)
        {
            object obj = DbHelperSQL.GetSingle(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public static bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            object obj = DbHelperSQL.GetSingle(strSql, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        /// <summary >
        /// 返回连接
        /// </summary >
        /// <returns ></returns >
        public static SqlConnection GetConnection()
        {
            string currentConnectionString = connectionString;
            if (!string.IsNullOrEmpty(System.Web.HttpContext.Current.User.Identity.Name))
            {
                currentConnectionString = currentConnectionString + ";Application Name=ForegroundUserID=" + System.Web.HttpContext.Current.User.Identity.Name;
            }
            return new SqlConnection(currentConnectionString);
        }
        /// <summary >
        /// 执行SQL语句,返回影响的记录数
        /// </summary >
        /// <param name="SQLString" >SQL语句</param >
        /// <returns >影响的记录数</returns >
        public static int ExecuteSql(string SQLString)
        {
            using (SqlConnection connection = GetConnection())
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        throw new Exception(E.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }
        /// <summary >
        /// 执行一条计算查询结果语句,返回查询结果(object)。
        /// </summary >
        /// <param name="SQLString" >计算查询结果语句</param >
        /// <returns >查询结果(object)</returns >
        public static object GetSingle(string SQLString)
        {
            using (SqlConnection connection = GetConnection())
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw new Exception(e.Message);
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                }
            }
        }
        public static object ExecuteScalar(string strSQL)
        {
            using (SqlConnection conn = GetConnection())
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSQL;
                object result = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return result;
            }
        }
        public static object ExecuteScalar(string strSQL, params SqlParameter[] paramter)
        {
            using (SqlConnection conn = GetConnection())
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSQL;
                if (paramter != null)
                {
                    foreach (SqlParameter par in paramter)
                    {
                        cmd.Parameters.Add(par);
                    }
                }
                object result = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return result;
            }
        }
        /// <summary >
        /// 执行查询语句,返回DataSet
        /// </summary >
        /// <param name="SQLString" >查询语句</param >
        /// <returns >DataSet</returns >
        public static DataSet Query(string SQLString)
        {
            using (SqlConnection connection = GetConnection())
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
#if DEBUG
                    throw new Exception(ex.Message + SQLString);
#endif
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
                return ds;
            }
        }
        /// <summary >
        /// 获取数据集
        /// </summary >
        /// <param name="SQLString" ></param >
        /// <param name="dtname" ></param >
        /// <returns ></returns >
        public static DataSet Query(string SQLString, string dtname)
        {
            using (SqlConnection connection = GetConnection())
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, dtname);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
                return ds;
            }
        }
        /// <summary >
        ///
        /// </summary >
        /// <param name="SQLString" ></param >
        /// <param name="dtname" ></param >
        /// <param name="ds" ></param >
        /// <returns ></returns >
        public static DataSet Query(string SQLString, string dtname, ref DataSet ds)
        {
            using (SqlConnection connection = GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, dtname);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
                return ds;
            }
        }
        /// <summary >
        /// 执行SQL语句,返回影响的记录数
        /// </summary >
        /// <param name="SQLString" >SQL语句</param >
        /// <returns >影响的记录数</returns >
        public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = GetConnection())
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        throw new Exception(E.Message);
                    }
                    finally
                    {
                    }
                }
            }
        }
        /// <summary >
        /// 执行多条SQL语句,实现数据库事务。
        /// </summary >
        /// <param name="SQLStringList" >SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param >
        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (SqlConnection conn = GetConnection())
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    object myDeValue = null;
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            myDeValue = myDE.Value;
                            PrepareCommand(cmd, conn, trans, myDE.Key.ToString(), cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    trans.Commit();
                }
            }
        }
        public static List<int > ExecuteSqlTranRunID(IList<DictionaryEntry > SQLStringList, SqlConnection conn, SqlTransaction trans)
        {
            List<int > list = new List<int >();
            if (conn.State == ConnectionState.Closed) conn.Open();
            SqlCommand cmd = new SqlCommand();
            try
            {
                foreach (DictionaryEntry myDE in SQLStringList)
                {
                    SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                    PrepareCommand(cmd, conn, trans, myDE.Key.ToString(), cmdParms);
                    list.Add(int.Parse(cmd.ExecuteScalar().ToString()));
                    cmd.Parameters.Clear();
                }
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
            }
            return list;
        }
        /// <summary >
        /// 执行事务,且事务中第一条Sql的返回值作为后面所有Sql最后一个参数的值
        /// </summary >
        /// <param name="SqlStringList" ></param >
        public static void ExecuteSqlTrans(List<DictionaryEntry > SqlStringList)
        {
            using (SqlConnection conn = GetConnection())
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    int identity = 0;
                    string cmdText;
                    SqlParameter[] parameter;
                    try
                    {
                        for (int i = 0; i < SqlStringList.Count; i++)
                        {
                            cmdText = SqlStringList[i].Key.ToString();
                            parameter = (SqlParameter[])SqlStringList[i].Value;
                            if (i == 0)
                            {
                                PrepareCommand(cmd, conn, trans, cmdText, parameter);
                                identity = int.Parse(cmd.ExecuteScalar().ToString());
                                cmd.Parameters.Clear();
                            }
                            else
                            {
                                if (parameter[parameter.Length - 1].Value == DBNull.Value || decimal.Parse(parameter[parameter.Length - 1].Value.ToString()) == 0)
                                {
                                    parameter[parameter.Length - 1].Value = identity;
                                }
                                PrepareCommand(cmd, conn, trans, cmdText, parameter);
                                cmd.ExecuteNonQuery();
                                cmd.Parameters.Clear();
                            }
                        }
                        trans.Commit();
                    }
                    catch (Exception e)
                    {
                        trans.Rollback();
                        throw e;
                    }
                }
            }
        }
        /// <summary >
        /// 执行多条SQL语句,实现数据库事务。
        /// </summary >
        /// <param name="SQLStringList" >SQL语句的有序表(key为sql语句,value是该语句的SqlParameter[])</param >
        public static void ExecuteSqlTran(IList<DictionaryEntry > SQLStringList)
        {
            using (SqlConnection conn = GetConnection())
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    object myDeValue = null;
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            if (myDE.Key == null || string.IsNullOrEmpty(myDE.Key.ToString()))
                            {
                                continue;
                            }
                            else
                            {
                                string cmdText = myDE.Key.ToString();
                                SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                                myDeValue = myDE.Value;
                                PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                                int val = cmd.ExecuteNonQuery();
                                cmd.Parameters.Clear();
                            }
                        }
                        trans.Commit();
                    }
                    catch (SqlException ex)
                    {
                        trans.Rollback();
                        throw ex;
                    }
                }
            }
        }
        /// <summary >
        /// 执行一条计算查询结果语句,返回查询结果(object)。
        /// </summary >
        /// <param name="SQLString" >计算查询结果语句</param >
        /// <returns >查询结果(object)</returns >
        public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = GetConnection())
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw new Exception(e.Message);
                    }
                    finally
                    {
                    }
                }
            }
        }
        /// <summary >
        /// 执行查询语句,返回DataSet
        /// </summary >
        /// <param name="SQLString" >查询语句</param >
        /// <returns >DataSet</returns >
        public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
#if DEBUG
                        throw new Exception(ex.Message + SQLString);
#endif
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                    }
                    return ds;
                }
            }
        }
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    if (parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input)
                    {
                        if (parameter.Value.ToString() == DateTime.MinValue.ToString() || parameter.Value.ToString() == int.MinValue.ToString())
                        {
                            parameter.Value = DBNull.Value;
                        }
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
        /// <summary >
        /// 执行存储过程
        /// </summary >
        /// <param name="storedProcName" >存储过程名</param >
        /// <returns >SqlDataReader</returns >
        public static DataSet RunProcedure(string storedProcName)
        {
            using (SqlConnection connection = GetConnection())
            {
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter();
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = storedProcName;
                command.Connection = connection;
                da.SelectCommand = command;
                da.Fill(ds);
                connection.Close();
                return ds;
            }
        }
        /// <summary >
        /// 执行存储过程
        /// </summary >
        /// <param name="storedProcName" >存储过程名</param >
        /// <param name="parameters" >存储过程参数</param >
        /// <param name="tableName" >DataSet结果中的表名</param >
        /// <returns >DataSet</returns >
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            try
            {
                using (SqlConnection connection = GetConnection())
                {
                    DataSet dataSet = new DataSet();
                    connection.Open();
                    SqlDataAdapter sqlDA = new SqlDataAdapter();
                    sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                    sqlDA.Fill(dataSet);
                    connection.Close();
                    return dataSet;
                }
            }
            catch (System.Data.SqlClient.SqlException E)
            {
                throw new Exception(E.Message);
            }
        }
        /// <summary >
        /// 执行存储过程
        /// </summary >
        /// <param name="storedProcName" >存储过程名</param >
        /// <param name="parameters" >存储过程参数</param >
        /// <param name="tableName" >DataSet结果中的表名</param >
        /// <returns ></returns >
        public static DataSet RunProcedureDT(string storedProcName, IDataParameter[] parameters, string tableName, ref DataSet dataSet)
        {
            using (SqlConnection connection = GetConnection())
            {
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet);
                connection.Close();
                return dataSet;
            }
        }
        /// <summary >
        /// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)
        /// </summary >
        /// <param name="connection" >数据库连接</param >
        /// <param name="storedProcName" >存储过程名</param >
        /// <param name="parameters" >存储过程参数</param >
        /// <returns >SqlCommand</returns >
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }
    }
}
 
 
 
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
3.
DBHelper.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace PracticeMyBookShopDAL
{
    public static class DBHelper
    {
        private static SqlConnection connection;
        public static SqlConnection Connection
        {
            get
            {
                //string connectionString = "Data Source=localhost;Initial Catalog=MyBookShop;User ID=sa;password=123456";
                string connectionString = "Data Source=localhost;Initial Catalog=MyBookShop;User ID=sa;password=123456";
              
               
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }
        public static int ExecuteCommand(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = cmd.ExecuteNonQuery();
            return result;
        }
        public static int ExecuteCommand(string sql, SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteNonQuery();
        }
        public static int ExecuteCommand(string sql, SqlParameter value)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.Add(value);
            int result = cmd.ExecuteNonQuery();
            return result;
        }
        public static int ExecuteScalar(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = (int)cmd.ExecuteScalar();
            return result;
        }
        public static int ExecuteScalar(string sql, SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int result = (int)cmd.ExecuteScalar();
            return result;
        }
        public static int ExecuteScalar(string sql, SqlParameter value)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.Add(value);
            int result = (int)cmd.ExecuteScalar();
            return result;
        }
        public static SqlDataReader ExecuteReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        public static SqlDataReader ExecuteReader(string sql, SqlParameter value)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.Add(value);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        public static SqlDataReader ExecuteReader(string sql, SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        public static DataTable GetDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }
        public static DataTable GetDataSet(string sql, params SqlParameter[] values)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }
 
 
        public static SqlDataReader GetReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
 
    }
}
 
 
 
其中一个DAL:
 
using System;
using System.Collections.Generic;
using System.Text;
using PracticeMyBookShopModels;
using System.Data;
using System.Data.SqlClient;
namespace PracticeMyBookShopDAL
{
    public static class UserService
    {
        public static IList<User> GetAllUsers()
        {
            string sqlAll = "SELECT * FROM Users";
            return GetUsersBySql(sqlAll);
        }
        public static void DeleteUserById(int id)
        {
            string sql = "DELETE Users WHERE Id = @Id";
                SqlParameter[] para = new SqlParameter[]
    {
     new SqlParameter("@Id", id)
    };
                DBHelper.ExecuteCommand(sql, para);
          
          
        }
        public static IList<User> GetUserByRole(int userRoleId)
        {
            string sql = "SELECT * FROM Users WHERE UserRoleId = @UserRoleId";
            return GetUsersBySql(sql, new SqlParameter("@UserRoleId", userRoleId));
          
        }
  private static IList<User> GetUsersBySql( string sql, params SqlParameter[] values )
        {
            List<User> list = new List<User>();
   
    DataTable table = DBHelper.GetDataSet( sql, values );
    
    foreach (DataRow row in table.Rows)
    {
     User user = new User();
     
     user.Id = (int)row["Id"];
     user.LoginId = (string)row["LoginId"];
     user.LoginPwd = (string)row["LoginPwd"];
     user.Name = (string)row["Name"];
     user.Address = (string)row["Address"];
     user.Phone = (string)row["Phone"];
     user.Mail = (string)row["Mail"];
     user.UserState = UserStateService.GetUserStateById((int)row["UserStateId"]); //FK
     user.UserRole = UserRoleService.GetUserRoleById((int)row["UserRoleId"]); //FK
 
     list.Add(user);
    }
 
    return list;
   
    
      
  
 }
        private static IList<User> GetUsersBySql(string safeSql)
        {
            List<User> list = new List<User>();
          
                DataTable table = DBHelper.GetDataSet(safeSql);
                foreach (DataRow row in table.Rows)
                {
                    User user = new User();
                    user.Id = (int)row["Id"];
                    user.LoginId = (string)row["LoginId"];
                    user.LoginPwd = (string)row["LoginPwd"];
                    user.Name = (string)row["Name"];
                    user.Address = (string)row["Address"];
                    user.Phone = (string)row["Phone"];
                    user.Mail = (string)row["Mail"];
                    user.UserState = UserStateService.GetUserStateById((int)row["UserStateId"]); //FK
                    user.UserRole = UserRoleService.GetUserRoleById((int)row["UserRoleId"]); //FK
                    list.Add(user);
                }
                return list;
            }
            /// 更改会员状态
         
            public static void ModifyUserStatus(int id, int status)
            {
                string sql = "Update users SET userstateid =" + status + " WHERE Id = @UserId";
                DBHelper.ExecuteCommand(sql, new SqlParameter("@UserId", id));
            }
    }
}
 
 
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
4.

 
DBHelper.cs:
namespace BankDAL
{
    public static class DBHelper
    {
        private static SqlConnection connection;
        public static SqlConnection Connection
        {
            get
            {
                string connectionString = ConfigurationManager.ConnectionStrings["BankConnectionString"].ConnectionString;
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }
        public static int ExecuteCommand(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = cmd.ExecuteNonQuery();
            return result;
        }
        public static int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteNonQuery();
        }
        public static int GetScalar(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }
        public static int GetScalar(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }
        public static SqlDataReader GetReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        public static DataTable GetDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }
        public static DataTable GetDataSet(string sql, params SqlParameter[] values)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }
    }
}
 
DAL:
namespace Bank.DAL
{
    public static class UserService
    {
        /// <summary>
        /// 添加新用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static Users AddUser(Users user)
        {
            string sql =
                "INSERT Users (Users_Account,Users_Pwd,Total_Money)" +
                "VALUES (@Users_Account, @Users_Pwd, @Total_Money)";
            sql += " ; SELECT @@IDENTITY";
            SqlParameter[] para = new SqlParameter[]
   {
    new SqlParameter("@Users_Account", user.Users_Account),
    new SqlParameter("@Users_Pwd", user.Users_Pwd),
    new SqlParameter("@Total_Money", user.Total_Money),
   };
            int newId = DBHelper.GetScalar(sql, para);
            return GetUserById(newId);
        }
        public static Users GetUserById(int id)
        {
            string sql = "SELECT * FROM Users WHERE Users_Id = @Id";
       
            using (SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@Id", id)))
            {
                if (reader.Read())
                {
                    Users user = new Users();
                    user.Users_Id = (int)reader["Users_Id"];
                    user.Users_Account = (string)reader["Users_Account"];
                    user.Users_Pwd = (string)reader["Users_Pwd"];
                    user.Total_Money= (double)reader["Total_Money"];
               
                    reader.Close();
                 
                    return user;
                }
                else
                {
                    reader.Close();
                    return null;
                }
            }
        }
        public static Users GetUserByUsersAccount(string users_Account)
        {
            string sql = "SELECT * FROM Users WHERE Users_Account = @users_Account";
            using (SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@Id", users_Account)))
            {
                if (reader.Read())
                {
                    Users user = new Users();
                    user.Users_Id = (int)reader["Users_Id"];
                    user.Users_Account = (string)reader["Users_Account"];
                    user.Users_Pwd = (string)reader["Users_Pwd"];
                    user.Total_Money = (double)reader["Total_Money"];
                    reader.Close();
                    return user;
                }
                else
                {
                    reader.Close();
                    return null;
                }
            }
        }
    
    }
}
 
 
 
BLL:   
namespace BankBLL
{
    public static class UserManager
    {
        public static bool Login(string users_Account, string users_Pwd, out Users validUser)
        {
            Users user = UserService.GetUserByUsersAccount(users_Account);
            if (user == null)
            {
                //用户名不存在
                validUser = null;
                return false;
            }
            if (user.Users_Pwd == users_Pwd)
            {
                validUser = user;
                return true;
            }
            else
            {
                //密码错误
                validUser = null;
                return false;
            }
        }
 
        public static string LoginValidate(string users_Account, string users_Pwd)
        {
            Users user = UserService.GetUserByUsersAccount(users_Account);
            if (user == null)
            {
                return "failure";
            }
            if (user.Users_Pwd == users_Pwd)
            {
                return "success";
            }
            else
            {
                return "failure";
            }
        }
        public static string FindToAccount(string users_Account)
        {
            Users user = UserService.GetUserByUsersAccount(users_Account);
            if (user != null)
            {
                return "success";
            }
            else
            {
                return "failure";
            }
        }
        public static string FindTotalMoney(double totalMoney)
        {
            throw new NotImplementedException();
        }
    }
}


 此文从网络中自动搜索生成,不代表本网站赞成被搜索网站的内容或立场    查看原文
360图书馆 软件开发资料 文字转语音 购物精选 软件下载 美食菜谱 新闻资讯 电影视频 小游戏 Chinese Culture 股票 租车
生肖星座 三丰软件 视频 开发 短信 中国文化 网文精选 搜图网 美图 阅读网 多播 租车 短信 看图 日历 万年历 2018年7日历
2018-7-22 15:05:03
 
  网站联系 软件世界网-www.sjsjw.com ©2014 蜀ICP备06016416号 三峰网旗下网站