四个DBHelper实现

首页 > 代码库 > 四个DBHelper实现

2024-10-24 03:09:02 212人阅读
建一个通用的处理数据的类
[csharp] view plaincopyprint?
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace Document
{
/**//// <summary>
/// Summary description for DataHelper.
/// </summary>
public class DataHelper
{
public DataHelper()
{
//
// TODO: Add constructor logic here
//
}
public static string ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
GetDataSet#region GetDataSet
public static DataSet GetDataSet(string sql)
{
SqlDataAdapter sda =new SqlDataAdapter(sql,ConnectionString);
DataSet ds=new DataSet();
sda.Fill(ds);
return ds;
}
#endregion
ExecCommand#region ExecCommand
public static int ExecCommand(SqlCommand sqlcom)
{
SqlConnection conn=new SqlConnection(ConnectionString);
sqlcom.Connection =conn;
conn.Open();
try
{
int rtn=sqlcom.ExecuteNonQuery();
return rtn;
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return 0;
}
public static int ExecCommand(string sql)
{
if (sql.EndsWith(",")) sql=sql.Substring(0,sql.Length-1);
SqlCommand sqlcom=new SqlCommand(sql);
return ExecCommand(sqlcom);
}
#endregion
ExecuteScalar#region ExecuteScalar
public static object ExecuteScalar(string sql)
{
SqlConnection conn=new SqlConnection(ConnectionString);
SqlCommand sqlcom=new SqlCommand(sql,conn);
conn.Open();
try
{
object rtn=sqlcom.ExecuteScalar ();
return rtn;
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return null;
}
#endregion
ExecSPCommand#region ExecSPCommand
public static void ExecSPCommand(string sql,System.Data.IDataParameter[] paramers)
{
SqlConnection conn=new SqlConnection(ConnectionString);
SqlCommand sqlcom=new SqlCommand(sql,conn);
sqlcom.CommandType= CommandType.StoredProcedure ;
foreach(System.Data.IDataParameter paramer in paramers)
{
sqlcom.Parameters.Add(paramer);
}
conn.Open();
try
{
sqlcom.ExecuteNonQuery();
}
catch(Exception ex)
{
string s=ex.Message ;
}
finally
{
conn.Close();
}
}
#endregion
ExecSPDataSet#region ExecSPDataSet
public static DataSet ExecSPDataSet(string sql,System.Data.IDataParameter[] paramers)
{
SqlConnection conn=new SqlConnection(ConnectionString);
SqlCommand sqlcom=new SqlCommand(sql,conn);
sqlcom.CommandType= CommandType.StoredProcedure ;
foreach(System.Data.IDataParameter paramer in paramers)
{
sqlcom.Parameters.Add(paramer);
}
conn.Open();
SqlDataAdapter da=new SqlDataAdapter();
da.SelectCommand=sqlcom;
DataSet ds=new DataSet();
da.Fill(ds);
conn.Close();
return ds;
}
#endregion
DbType#region DbType
private static System.Data.DbType GetDbType(Type type)
{
DbType result = DbType.String;
if( type.Equals(typeof(int)) || type.IsEnum)
result = DbType.Int32;
else if( type.Equals(typeof(long)))
result = DbType.Int32;
else if( type.Equals(typeof(double)) || type.Equals( typeof(Double)))
result = DbType.Decimal;
else if( type.Equals(typeof(DateTime)))
result = DbType.DateTime;
else if( type.Equals(typeof(bool)))
result = DbType.Boolean;
else if( type.Equals(typeof(string) ) )
result = DbType.String;
else if( type.Equals(typeof(decimal)))
result = DbType.Decimal;
else if( type.Equals(typeof(byte[])))
result = DbType.Binary;
else if( type.Equals(typeof(Guid)))
result = DbType.Guid;
return result;
}
#endregion
UpdateTable#region UpdateTable
public static void UpdateTable(DataTable dt,string TableName,string KeyName)
{
foreach(DataRow dr in dt.Rows)
{
updateRow(dr,TableName,KeyName);
}
}
#endregion
InsertTable#region InsertTable
//用于主键是数据库表名+ID类型的
public static void InsertTable(DataTable dt)
{
string TableName="["+dt.TableName+"]";
string KeyName=dt.TableName+"ID";
foreach(DataRow dr in dt.Rows)
{
insertRow(dr,TableName,KeyName);
}
}
//用于主键是任意类型的
public static void InsertTable(DataTable dt,string KeyName)
{
string TableName="["+dt.TableName+"]";
foreach(DataRow dr in dt.Rows)
{
insertRow(dr,TableName,KeyName);
}
}
#endregion
DeleteTable#region DeleteTable
public static void DeleteTable(DataTable dt,string KeyName)
{
string TableName="["+dt.TableName+"]";
foreach(DataRow dr in dt.Rows)
{
deleteRow(dr,TableName,KeyName);
}
}
#endregion
updateRow#region updateRow
private static void updateRow(DataRow dr,string TableName,string KeyName)
{
if (dr[KeyName]==DBNull.Value )
{
throw new Exception(KeyName +"的值不能为空");
}
if (dr.RowState ==DataRowState.Deleted)
{
deleteRow(dr,TableName,KeyName);
}
else if (dr.RowState ==DataRowState.Modified )
{
midifyRow(dr,TableName,KeyName);
}
else if (dr.RowState ==DataRowState.Added )
{
insertRow(dr,TableName,KeyName);
}
else if (dr.RowState ==DataRowState.Unchanged )
{
midifyRow(dr,TableName,KeyName);
}
}
#endregion
deleteRow#region deleteRow
private static void deleteRow(DataRow dr,string TableName,string KeyName)
{
string sql="Delete {0} where {1} =@{1}";
DataTable dtb=dr.Table ;
sql=string.Format(sql,TableName,KeyName);
SqlCommand sqlcom=new SqlCommand(sql);
System.Data.IDataParameter iparam=new SqlParameter();
iparam.ParameterName = "@"+ KeyName;
iparam.DbType = GetDbType(dtb.Columns[KeyName].DataType);
iparam.Value = dr[KeyName];
sqlcom.Parameters .Add(iparam);
ExecCommand(sqlcom);
}
#endregion
midifyRow#region midifyRow
private static void midifyRow(DataRow dr,string TableName,string KeyName)
{
string UpdateSql = "Update {0} set {1} {2}";
string setSql="{0}= @{0}";
string wherSql=" Where {0}=@{0}";
StringBuilder setSb = new StringBuilder();
SqlCommand sqlcom=new SqlCommand();
DataTable dtb=dr.Table;
for (int k=0; k<dr.Table.Columns.Count; ++k)
{
System.Data.IDataParameter iparam=new SqlParameter();
iparam.ParameterName = "@"+ dtb.Columns[k].ColumnName;
iparam.DbType = GetDbType(dtb.Columns[k].DataType);
iparam.Value = dr[k];
sqlcom.Parameters .Add(iparam);
if (dtb.Columns[k].ColumnName==KeyName)
{
wherSql=string.Format(wherSql,KeyName);
}
else
{
setSb.Append(string.Format(setSql,dtb.Columns[k].ColumnName));
setSb.Append(",");
}
}
string setStr=setSb.ToString();
setStr=setStr.Substring(0,setStr.Length -1); //trim ,
string sql = string.Format(UpdateSql, TableName, setStr,wherSql);
sqlcom.CommandText =sql;
try
{
ExecCommand(sqlcom);
}
catch(Exception ex)
{
throw ex;
}
}
#endregion
insertRow#region insertRow
private static void insertRow(DataRow dr,string TableName,string KeyName)
{
string InsertSql = "Insert into {0}({1}) values({2})";
SqlCommand sqlcom=new SqlCommand();
DataTable dtb=dr.Table ;
StringBuilder insertValues = new StringBuilder();
StringBuilder cloumn_list = new StringBuilder();
for (int k=0; k<dr.Table.Columns.Count; ++k)
{
//just for genentae，
if (dtb.Columns[k].ColumnName==KeyName) continue;
System.Data.IDataParameter iparam=new SqlParameter();
iparam.ParameterName = "@"+ dtb.Columns[k].ColumnName;
iparam.DbType = GetDbType(dtb.Columns[k].DataType);
iparam.Value = dr[k];
sqlcom.Parameters .Add(iparam);
cloumn_list.Append(dtb.Columns[k].ColumnName);
insertValues.Append("@"+dtb.Columns[k].ColumnName);
cloumn_list.Append(",");
insertValues.Append(",");
}
string cols=cloumn_list.ToString();
cols=cols.Substring(0,cols.Length -1);
string values=insertValues.ToString();
values=values.Substring(0,values.Length -1);
string sql = string.Format(InsertSql, TableName,cols ,values);
sqlcom.CommandText =sql;
try
{
ExecCommand(sqlcom);
}
catch(Exception ex)
{
throw ex;
}
}
#endregion
}
}
using System;using System.Data;using System.Data.SqlClient;using System.Text;namespace Document{    /**//// <summary>    /// Summary description for DataHelper.    /// </summary>    public class DataHelper    {        public DataHelper()        {            //            // TODO: Add constructor logic here            //        }        public static string ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];            GetDataSet#region GetDataSet        public static DataSet GetDataSet(string sql)        {            SqlDataAdapter    sda =new SqlDataAdapter(sql,ConnectionString);            DataSet ds=new DataSet();            sda.Fill(ds);            return ds;        }        #endregion        ExecCommand#region ExecCommand        public static int ExecCommand(SqlCommand sqlcom)        {            SqlConnection conn=new SqlConnection(ConnectionString);            sqlcom.Connection =conn;            conn.Open();            try            {                int rtn=sqlcom.ExecuteNonQuery();                return rtn;            }            catch(Exception ex)             {                throw ex;                            }            finally            {                conn.Close();            }            return 0;        }        public static int ExecCommand(string sql)        {            if (sql.EndsWith(",")) sql=sql.Substring(0,sql.Length-1);                    SqlCommand sqlcom=new SqlCommand(sql);            return ExecCommand(sqlcom);                        }        #endregion                ExecuteScalar#region ExecuteScalar        public static object ExecuteScalar(string sql)        {            SqlConnection conn=new SqlConnection(ConnectionString);            SqlCommand sqlcom=new SqlCommand(sql,conn);            conn.Open();            try            {                object rtn=sqlcom.ExecuteScalar ();                return rtn;            }            catch(Exception ex)             {                throw ex;                            }            finally            {                conn.Close();            }            return null;        }        #endregion        ExecSPCommand#region ExecSPCommand        public static void ExecSPCommand(string sql,System.Data.IDataParameter[] paramers)        {            SqlConnection conn=new SqlConnection(ConnectionString);            SqlCommand sqlcom=new SqlCommand(sql,conn);            sqlcom.CommandType= CommandType.StoredProcedure ;            foreach(System.Data.IDataParameter paramer in paramers)            {                sqlcom.Parameters.Add(paramer);            }                        conn.Open();            try            {                sqlcom.ExecuteNonQuery();            }            catch(Exception ex)             {                string s=ex.Message ;            }            finally            {                conn.Close();            }        }        #endregion        ExecSPDataSet#region ExecSPDataSet        public static DataSet ExecSPDataSet(string sql,System.Data.IDataParameter[] paramers)        {            SqlConnection conn=new SqlConnection(ConnectionString);            SqlCommand sqlcom=new SqlCommand(sql,conn);            sqlcom.CommandType= CommandType.StoredProcedure ;            foreach(System.Data.IDataParameter paramer in paramers)            {                sqlcom.Parameters.Add(paramer);            }                        conn.Open();                        SqlDataAdapter da=new SqlDataAdapter();            da.SelectCommand=sqlcom;            DataSet ds=new DataSet();            da.Fill(ds);                    conn.Close();            return ds;        }        #endregion        DbType#region DbType        private static System.Data.DbType GetDbType(Type type)        {            DbType result = DbType.String;            if( type.Equals(typeof(int)) ||  type.IsEnum)                result = DbType.Int32;            else if( type.Equals(typeof(long)))                result = DbType.Int32;            else if( type.Equals(typeof(double)) || type.Equals( typeof(Double)))                result = DbType.Decimal;            else if( type.Equals(typeof(DateTime)))                result = DbType.DateTime;            else if( type.Equals(typeof(bool)))                result = DbType.Boolean;            else if( type.Equals(typeof(string) ) )                result = DbType.String;            else if( type.Equals(typeof(decimal)))                result = DbType.Decimal;            else if( type.Equals(typeof(byte[])))                result = DbType.Binary;            else if( type.Equals(typeof(Guid)))                result = DbType.Guid;                    return result;                    }        #endregion        UpdateTable#region UpdateTable        public static void UpdateTable(DataTable dt,string TableName,string KeyName)        {            foreach(DataRow dr in dt.Rows)            {                updateRow(dr,TableName,KeyName);            }        }        #endregion        InsertTable#region InsertTable        //用于主键是数据库表名+ID类型的        public static void InsertTable(DataTable dt)        {            string TableName="["+dt.TableName+"]";            string KeyName=dt.TableName+"ID";            foreach(DataRow dr in dt.Rows)            {                insertRow(dr,TableName,KeyName);            }        }        //用于主键是任意类型的        public static void InsertTable(DataTable dt,string KeyName)        {            string TableName="["+dt.TableName+"]";            foreach(DataRow dr in dt.Rows)            {                insertRow(dr,TableName,KeyName);            }        }        #endregion        DeleteTable#region DeleteTable        public static void DeleteTable(DataTable dt,string KeyName)        {            string TableName="["+dt.TableName+"]";            foreach(DataRow dr in dt.Rows)            {                deleteRow(dr,TableName,KeyName);            }        }        #endregion        updateRow#region updateRow        private static void  updateRow(DataRow dr,string TableName,string KeyName)        {            if (dr[KeyName]==DBNull.Value )             {                throw new Exception(KeyName +"的值不能为空");            }                        if (dr.RowState ==DataRowState.Deleted)            {                deleteRow(dr,TableName,KeyName);             }            else if (dr.RowState ==DataRowState.Modified )            {                midifyRow(dr,TableName,KeyName);            }            else if (dr.RowState ==DataRowState.Added  )            {                insertRow(dr,TableName,KeyName);            }            else if (dr.RowState ==DataRowState.Unchanged )            {                midifyRow(dr,TableName,KeyName);            }                   }        #endregion        deleteRow#region deleteRow        private static void  deleteRow(DataRow dr,string TableName,string KeyName)        {            string sql="Delete {0} where {1} =@{1}";            DataTable dtb=dr.Table ;            sql=string.Format(sql,TableName,KeyName);            SqlCommand sqlcom=new SqlCommand(sql);            System.Data.IDataParameter iparam=new  SqlParameter();            iparam.ParameterName    = "@"+ KeyName;            iparam.DbType            = GetDbType(dtb.Columns[KeyName].DataType);            iparam.Value            = http://www.mamicode.com/dr[KeyName];>
2..调用范例
[csharp] view plaincopyprint?
Insert#region Insert  
private void InsertUserInfo()  
{  
    DataTable dt=ds.Tables[0];  
    dt.TableName="UserInfo";  
        string keyname="UserInfoID";  
    DataRow dr=dt.NewRow();  
    dr["LoginName"]=this.txtUserName.Value;  
    dr["Pass"]=this.txtPassword.Value;  
    dr["NickName"]=this.txtNickName.Value;  
    dr["UserType"]=1;  
    dr["IsActive"]=false;  
    dr["RegisterDate"]=System.DateTime.Now;  
    dt.Rows.Add(dr);  
    dt.AcceptChanges();  
    DataHelper.InsertTable(dt,keyname);  
}  
#endregion  
  
Update#region Update  
private void UpdateUserInfo(string UserID)  
{              
    DataSet ds=GetUserOther(UserID);  
    DataTable dt=ds.Tables[0];  
    dt.TableName="UserInfo";  
        string keyname="UserID";  
    DataRow dr=dt.Rows[0];  
    dr["LoginName"]=this.txtUserName.Value;  
    dr["Pass"]=this.txtPassword.Value;  
    dr["NickName"]=this.txtNickName.Value;  
    dr["UserType"]=1;  
    dr["IsActive"]=false;  
    dr["RegisterDate"]=System.DateTime.Now;  
    dt.Rows.Add(dr);  
    dt.AcceptChanges();  
    DataHelper.UpdateTable(dt,dt.TableName,keynanme);  
}  
 
#endregion  
  
Delete  
        Insert#region Insert        private void InsertUserInfo()        {            DataTable dt=ds.Tables[0];            dt.TableName="UserInfo";                string keyname="UserInfoID";            DataRow dr=dt.NewRow();            dr["LoginName"]=this.txtUserName.Value;            dr["Pass"]=this.txtPassword.Value;            dr["NickName"]=this.txtNickName.Value;            dr["UserType"]=1;            dr["IsActive"]=false;            dr["RegisterDate"]=System.DateTime.Now;            dt.Rows.Add(dr);            dt.AcceptChanges();            DataHelper.InsertTable(dt,keyname);        }        #endregion        Update#region Update        private void UpdateUserInfo(string UserID)        {                        DataSet ds=GetUserOther(UserID);            DataTable dt=ds.Tables[0];            dt.TableName="UserInfo";                string keyname="UserID";            DataRow dr=dt.Rows[0];            dr["LoginName"]=this.txtUserName.Value;            dr["Pass"]=this.txtPassword.Value;            dr["NickName"]=this.txtNickName.Value;            dr["UserType"]=1;            dr["IsActive"]=false;            dr["RegisterDate"]=System.DateTime.Now;            dt.Rows.Add(dr);            dt.AcceptChanges();            DataHelper.UpdateTable(dt,dt.TableName,keynanme);        }        #endregion        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 = http://www.mamicode.com/null;
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            myDeValue = http://www.mamicode.com/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 =http://www.mamicode.com/= DBNull.Value || decimal.Parse(parameter[parameter.Length - 1].Value.ToString()) == 0)
                                {
                                    parameter[parameter.Length - 1].Value = http://www.mamicode.com/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 = http://www.mamicode.com/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 = http://www.mamicode.com/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 =http://www.mamicode.com/= null))
                    {
                        parameter.Value = http://www.mamicode.com/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 = http://www.mamicode.com/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 =http://www.mamicode.com/= null))
                    {
                        parameter.Value = http://www.mamicode.com/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();
        }
    }
}
四个DBHelper实现
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。
联系
我们
首页 > 代码库 > 四个DBHelper实现

四个DBHelper实现

看完仍有疑问？有类似问题直接问程序猿
