四个DBHelper实现

最新推荐文章于 2024-04-16 19:33:41 发布
最新推荐文章于 2024-04-16 19:33:41 发布
阅读量2.8k 收藏 1
点赞数
分类专栏： C# ASP.NET 文章标签： string dataset sql cmd user null
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/GoodShot/article/details/6581865
版权
ASP.NET 同时被 2 个专栏收录
326 篇文章 2 订阅
订阅专栏
C#
181 篇文章 2 订阅
订阅专栏

0.



建一个通用的处理数据的类

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
    }
}


2..调用范例

        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 = 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();
        }
    }
}

 

GoodShot
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
DbHelperMySQL，DbHelperSQL提供增删改查
01-11
DbHelperMySQL，DbHelperSQL提供增删改查，SQL连接只需要DbHelperSQL类，MYSQL连接需要其他剩余的三个类
DBHelper类的应用代码
qq_54392876的博客
07-13 462
DBHelper类逻辑层 public class employeeServer { //查询 public SqlDataReader GetEndData() { string sql = "select * from employee"; return DBHelper.GetReader(sql); } //删除 public bool delEnd
Qt操作Sqlite类封装，及命令行导入csv文件到Sqlite数据库
独行猫a 的沉淀、积累、总结。天天学习，好好向上...c/c++,嵌入式 linux,Android,HarmonyOS)
08-07 2821
日常工作中，有时候需要对数据进行分析。如果能把待分析的数据导入sqllite数据库，是种不错的分析手段。或者需要导入其他数据库中的数据，做数据迁移用，比如可以把其他数据库中的表数据导出为csv文件，再把csv文件导入到sqllite。...............
Qt -- 1、QT连接数据库（SQLite3）
xuechanba的博客
02-15 5699
视频教程链接：https://www.bilibili.com/video/BV1Li4y1f7ho?p=3. 类 QSqlDatabase 上图中的静态成员函数 addDatabase 的第一个参数 type 表示的是我要连接的这个数据库是什么类型的一个数据库，或者说我连接的是一个什么数据库软件。第二个参数是默认参数，不用管。 类 QSqlDatabase的常用接口 上面函数 setDatabaseName 和 removeDatabase 的参数是要打开或关闭数据库文件路径。 下面开始在 QT 软
C#可视化编程技术第三章：DBHelper类（内含思路、代码、注释）
黄瓜炒鸡蛋的博客
05-26 7672
DBHelper类：
完善的C#/ASP.NET版DBHelper，配套Model生成器
11-02
VS2012打开，.NET Framework4.0； 支持Oracle、MSSQL、MySQL、SQLite四种数据库，支持事务，支持部分ORM功能，对象关系映射；使用相当方便：DAL中添加：public void Insert(object obj){DBHelper.Insert(obj);}DAL中修改：public void Update(object obj){DBHelper.Update(obj);}DAL中分页查询： public List<BS_Template> GetList(ref PagerModel pager, string noticeType, string coreType, string name, Enums.TemplateType templateType) { StringBuilder sql = new StringBuilder(string.Format(@" select * from BS_Template t where 1=1 ")); if (!string.IsNullOrWhiteSpace(noticeType)) { sql.AppendFormat(" and nt.name like '%{0}%'", noticeType); } if (!string.IsNullOrWhiteSpace(coreType)) { sql.AppendFormat(" and ct.name like '%{0}%'", coreType); } if (!string.IsNullOrWhiteSpace(name)) { sql.AppendFormat(" and t.name like '%{0}%'", name); } sql.AppendFormat(" and t.type = '{0}'", (int)templateType); string orderby = "order by cast(id as int)"; pager = DBHelper.FindPageBySql<BS_Template>(sql.ToString(), orderby, pager.rows, pager.page); return pager.result as List<BS_Template>; }
基于QT的水果管理系统
csdn_walter的博客
10-16 4540
基于QT的水果管理系统
Asp.Net 之StringBuilder的介绍
Cloud.L 每天一点点~
07-21 1442
    StringBuilder 的容量是实例在任何给定时间可存储的最大字符数，并且大于或等于实例值的字符串表示形式的长度。容量可通过 Capacity 属性或 EnsureCapacity 方法来增加或减少，但它不能小于 Length 属性的值。 如果在初始化 StringBuilder 的实例时没有指定容量或最大容量，则使用特定于实现的默认值。String 或 StringBuild
用QT封装的操作各类数据库的接口类
10-19
用QT 封装的操作Sql server，MySql，SQLite，ODBC数据库的接口类，我在QT5.8下使用没问题，非常好用！
DBHelper方便连接数据库 DBHelper
12-28
using System; using System.Collections; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace MyOfficeDAL { public static class DBHelper { private static SqlConnection connection; public static SqlConnection Connection { get { string connectionString = ConfigurationManager.ConnectionStrings["MyOfficeConnectionString"].ConnectionString; connection = new SqlConnection(connectionString); if (connection == null) { 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 string ReturnStringScalar(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); try { string result = cmd.ExecuteScalar().ToString(); return result; } catch (Exception ex) { return "0"; } connection.Close(); } public static int GetScalar(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); try { int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; } catch (Exception e) { return 0; } connection.Close(); } 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; connection.Close(); } public static SqlDataReader GetReader(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); SqlDataReader reader = cmd.ExecuteReader(); return reader; reader.Close(); reader.Dispose(); } 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; reader.Close(); reader.Dispose(); } public static DataTable GetDataSet(string safeSql) { DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(safeSql, Connection); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); connection.Close(); connection.Dispose(); 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); connection.Close(); connection.Dispose(); return ds.Tables[0]; } } }
QT QSQLITE 连接池 增删改查
11-26
ConnectionPool类为连接池 MySqlDbHelper类为增删改查的函数，因为写的类前期用作mysql的连接类，勿喷，代码已改，想用MySQL的也可以，
Java实现Dbhelper支持大数据增删改
09-02
主要介绍了Java实现Dbhelper支持大数据增删改功能的实现过程，感兴趣的小伙伴们可以参考一下
c#多数据库自适应DBHelper源码
03-18
1、采用键值对、匿名对象进行CRUD、分页查询操作...MysqlDBHelper需安装mysql-connector-net（本项目使用6.9.9版） -AccessDBHelper使用的是Office2010驱动，可更改_provider做版本调整；创建Access文件使用了ADOX库。
DBhelper.rar
07-27
C#的一个DBhelper和配置成ini文件加一个SQL数据库配置，方便配置调用，使用，个人觉得简单易懂，大家可以参考下。
DBHelper类库
03-13
DBHelper是一个国外的开源软件开发组织，官方网站在参考资料记载。主要提供数据库协助方面的软件开发，最出名的是SqlHelper组件，使用.NET Framework为运行架构。DBHelper类库，适用于ASP开发和Winfrom开发，支持...
通用DBHelper
01-03
C#连接和操作数据库助手，支持各类数据库Access，sql server，oracle，sqlite等
关于使用druid数据源连接Oracle导致的SQLRecoverableException（socket read timeout,关闭的连接等）
男神的专栏
04-13 194
查看出错日志检查问题的时候，不能只看抛出错误最近的那几行代码，一定要看过整体的方法栈，知道运行的流程，再仔细看抛出异常的代码，看看该行代码执行的前后逻辑。
Flink SQL 自定义函数 - 字符串拆分
最新发布
qq_36382892的博客
04-16 247
/ 实现eval方法，用于拆分输入字符串并输出每个子串if (str!= null) {// 使用指定正则表达式对输入字符串进行拆分// 使用collect(...)方法发射一行数据。
采用Flink CDC操作SQL Server数据库获取增量变更数据
qq_22494169的博客
04-09 615
Slf4j@Overridetry {// 获取操作类型 CREATE UPDATE DELETE 1新增 2修改 3删除2 : 3;//7.输出数据log.error("SQLServer消息读取自定义序列化报错：{}", e.getMessage());/*** 从源数据获取出变更之前或之后的数据*/= null) {@Override。
Android使用sqlite写笔记本实现增删改查 ，布局为两个输入框四个按钮
05-30
3. 在布局文件中添加两个 EditText 和四个 Button，用于输入和操作笔记。 4. 创建一个 Note 类，用于存储笔记的信息，包括标题和内容。 5. 创建一个 NotesAdapter 类，用于将笔记列表显示在 RecyclerView 中。 6. 在...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值