读书人
栏目▼

求个增删节查的通用基类

发布时间： 2012-08-30 09:55:54 作者： rapoo

求个增删改查的通用基类。
今天不要脸皮，跪求基类。
只为学习用。

[解决办法]
用微软的sqlhelper
[解决办法]
微软的

C# code
 
 public abstract class SQLHelper 
   { 
     ///  <summary> 
     /// 连接字符串 
     ///  </summary> 
  
    public static readonly string ConnectionString = "数据库连接字符串"; 
  
     
     
        
     private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); 
  
     ///  <summary> 
     /// 数据库增,删,改方法 
     ///  </summary> 
     ///  <param name="connectionString">连接字符串 </param> 
     ///  <param name="cmdType">Command类型 </param> 
     ///  <param name="cmdText">执行文本 </param> 
     ///  <param name="commandParameters">参数 </param> 
     ///  <returns> </returns> 
     public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 
     { 
  
       SqlCommand cmd = new SqlCommand(); 
        
       using (SqlConnection conn = new SqlConnection(connectionString)) 
       { 
         PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 
         int val = cmd.ExecuteNonQuery(); 
         cmd.Parameters.Clear(); 
         return val; 
       } 
     } 
  
     ///  <summary>a 
     /// 数据库增,删,改方法 
  
     ///  </summary> 
     ///  <param name="connection">连接对象 </param> 
     ///  <param name="cmdType">Command类型 </param> 
     ///  <param name="cmdText">执行文本 </param> 
     ///  <param name="commandParameters">参数 </param> 
     ///  <returns> </returns> 
     public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 
     { 
  
       SqlCommand cmd = new SqlCommand(); 
  
       PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); 
       int val = cmd.ExecuteNonQuery(); 
       cmd.Parameters.Clear(); 
       return val; 
     } 
  
     ///  <summary> 
     /// 数据库增,删,改方法 
  
     ///  </summary> 
     ///  <param name="trans">事务 </param> 
     ///  <param name="cmdType">Command类型 </param> 
     ///  <param name="cmdText">执行文本 </param> 
     ///  <param name="commandParameters">参数 </param> 
     ///  <returns> </returns> 
     public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 
     { 
       SqlCommand cmd = new SqlCommand(); 
       PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); 
       int val = cmd.ExecuteNonQuery(); 
       cmd.Parameters.Clear();  
 
       return val; 
     } 
  
     ///  <summary> 
     /// 读取数据 
     ///  </summary> 
     ///  <param name="connectionString">连接字符串 </param> 
     ///  <param name="cmdType">Command类型 </param> 
     ///  <param name="cmdText">执行文本 </param> 
     ///  <param name="commandParameters">参数 </param> 
     ///  <returns>SqlDataReader </returns> 
     public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 
     { 
       SqlCommand cmd = new SqlCommand(); 
       SqlConnection conn = new SqlConnection(connectionString); 
  
  
       try 
       { 
         PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 
         SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); 
         cmd.Parameters.Clear(); 
         return rdr; 
       } 
       catch(SqlException ex) 
       { 
         string s = ex.Message; 
         conn.Close(); 
         throw; 
       } 
     } 
  
     ///  <summary> 
     /// 读取数据 
     ///  </summary> 
     ///  <param name="safeSql">执行文本 </param> 
     ///  <param name="cmdType">command类型 </param> 
     ///  <param name="connectionString">连接字符串 </param> 
     ///  <returns>DataTable </returns> 
     public static DataTable GetDataSet(string safeSql,CommandType cmdType, string connectionString) 
     { 
       DataSet ds = new DataSet(); 
       using (SqlConnection conn = new SqlConnection(connectionString)) 
       { 
         SqlCommand cmd = new SqlCommand(safeSql, conn); 
         SqlDataAdapter da = new SqlDataAdapter(cmd); 
         da.Fill(ds); 
         return ds.Tables[0]; 
       } 
  
     } 
  
     ///  <summary> 
     /// 读取数据 
     ///  </summary> 
     ///  <param name="connectionString">连接字符串 </param> 
     ///  <param name="cmdType">command类型 </param> 
     ///  <param name="sql">执行文本 </param> 
     ///  <param name="values">参数 </param> 
     ///  <returns>DataTable </returns> 
     public static DataTable GetDataSet(string connectionString,CommandType cmdType, string sql, params SqlParameter[] values) 
     { 
       DataSet ds = new DataSet(); 
       using (SqlConnection conn = new SqlConnection(connectionString)) 
       { 
         SqlCommand cmd = new SqlCommand(sql, conn); 
         cmd.CommandType = cmdType; 
         cmd.Parameters.AddRange(values); 
         SqlDataAdapter da = new SqlDataAdapter(cmd); 
         da.Fill(ds); 
         return ds.Tables[0]; 
       } 
  
     }  
 
  
     ///  <summary> 
     /// 得到单个返回值结果 
  
     ///  </summary> 
     ///  <param name="connectionString">连接字符串 </param> 
     ///  <param name="cmdType">command类型 </param> 
     ///  <param name="cmdText">执行文本 </param> 
     ///  <param name="commandParameters">参数 </param> 
     ///  <returns>object对象 </returns> 
     public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 
     { 
       SqlCommand cmd = new SqlCommand(); 
  
       using (SqlConnection connection = new SqlConnection(connectionString)) 
       { 
         PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); 
         object val = cmd.ExecuteScalar(); 
         cmd.Parameters.Clear(); 
         return val; 
       } 
     } 
  
     ///  <summary> 
     /// 得到单个返回值结果 
  
     ///  </summary> 
     ///  <param name="connection">连接对象 </param> 
     ///  <param name="cmdType">command类型 </param> 
     ///  <param name="cmdText">执行文本 </param> 
     ///  <param name="commandParameters">参数 </param> 
     ///  <returns>object对象 </returns> 
     public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 
     { 
  
       SqlCommand cmd = new SqlCommand(); 
  
       PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); 
       object val = cmd.ExecuteScalar(); 
       cmd.Parameters.Clear(); 
       return val; 
     } 
  
     public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters) 
     { 
       parmCache[cacheKey] = commandParameters; 
     } 
  
     public static SqlParameter[] GetCachedParameters(string cacheKey) 
     { 
       SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey]; 
  
       if (cachedParms == null) 
         return null; 
  
       SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length]; 
  
       for (int i = 0, j = cachedParms.Length; i  < j; i++) 
         clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone(); 
  
       return clonedParms; 
     } 
  
     ///  <summary> 
     /// 处理ADO.NET数据操作对象 
     ///  </summary> 
     ///  <param name="cmd">执行对象 </param> 
     ///  <param name="conn">连接对象 </param> 
     ///  <param name="trans">事务对象 </param> 
     ///  <param name="cmdType">command类型 </param> 
     ///  <param name="cmdText">执行文本 </param> 
     ///  <param name="cmdParms">参数 </param> 
     private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, 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 = cmdType; 
  
       if (cmdParms != null) 
       { 
         foreach (SqlParameter parm in cmdParms) 
           cmd.Parameters.Add(parm); 
       } 
     }

[解决办法]
DBAccess.cs
C# code
 
 using System; 
 using System.Data; 
 using System.Data.SqlClient; 
 using System.Collections.Generic; 
 using System.Linq; 
 using System.Text; 
  
  
 namespace DBAccess 
 { 
   public class SqlHelper:DBAccess 
   { 
     private List <SqlParameter> m_ListParameter = null; 
  
     public DataTable ExecuteDataTable(string cmdText, params SqlParameter[] commandParameters) 
     { 
       DataSet ds = ExecuteDataSet(cmdText, commandParameters); 
       if (null != ds) 
       { 
         if (0 != ds.Tables.Count) 
         { 
           return ds.Tables[0]; 
         } 
         else 
         { 
           return null; 
         } 
       } 
       else 
       { 
         return null; 
       } 
     } 
  
     public DataTable ExecuteDataTable(string cmdText) 
     { 
       return ExecuteDataTable(cmdText, GetParameters()); 
     } 
  
     public DataRow ExecuteDataRow(string cmdText, params SqlParameter[] commandParameters) 
     { 
       DataTable dt = ExecuteDataTable(cmdText, commandParameters); 
       if (null != dt) 
       { 
         if (0 != dt.Rows.Count) 
         { 
           return dt.Rows[0]; 
  
         } 
         else 
         { 
           return null; 
         } 
       } 
       else 
       { 
         return null; 
       } 
     } 
  
     public DataRow ExecuteDataRow(string cmdText) 
     { 
       return ExecuteDataRow(cmdText, GetParameters()); 
     } 
  
     public int ExecuteNonQuery(string cmdText) 
     { 
       return ExecuteNonQuery(cmdText, GetParameters()); 
     } 
     public int ExecuteNonQuery(SqlTransaction trans, string cmdText) 
     { 
       return ExecuteNonQuery(trans, cmdText, GetParameters()); 
     } 
     public DataSet ExecuteDataSet(string cmdText) 
     { 
       return ExecuteDataSet(cmdText, GetParameters()); 
     } 
     public SqlDataReader ExecuteReader(string cmdText) 
     { 
       return ExecuteReader(cmdText, GetParameters());  
 
     } 
     public object ExecuteScalar(string cmdText) 
     { 
       return ExecuteScalar(cmdText, GetParameters()); 
     } 
  
     private SqlParameter GetParameter(string paraName, SqlDbType paraType, object value) 
     { 
       SqlParameter para = new SqlParameter(paraName, paraType); 
       para.Value = value; 
  
       return para; 
     } 
  
     private SqlParameter GetParameter(string paraName, SqlDbType paraType, object value, int size) 
     { 
       SqlParameter para = new SqlParameter(paraName, paraType); 
       para.Value = value; 
       para.Size = size; 
       return para; 
     } 
  
     public void AddParameter(string paraName, SqlDbType paraType, object value) 
     { 
       if (null == m_ListParameter) 
       { 
         m_ListParameter = new List <SqlParameter>(); 
       } 
       SqlParameter para = GetParameter(paraName, paraType, value); 
       m_ListParameter.Add(para); 
     } 
  
     private SqlParameter[] GetParameters() 
     { 
       SqlParameter[] paras = null; 
       if (null != m_ListParameter) 
       { 
         if (0 != m_ListParameter.Count) 
         { 
           paras = m_ListParameter.ToArray(); 
         } 
         m_ListParameter = null; 
       } 
  
       return paras; 
     } 
   } 
 }

SqlHelper.cs
C# code
 
 using System; 
 using System.Data; 
 using System.Data.SqlClient; 
 using System.Collections.Generic; 
 using System.Linq; 
 using System.Text; 
  
  
 namespace DBAccess 
 { 
   public class SqlHelper:DBAccess 
   { 
     private List <SqlParameter> m_ListParameter = null; 
  
     public DataTable ExecuteDataTable(string cmdText, params SqlParameter[] commandParameters) 
     { 
       DataSet ds = ExecuteDataSet(cmdText, commandParameters); 
       if (null != ds) 
       { 
         if (0 != ds.Tables.Count) 
         { 
           return ds.Tables[0]; 
         } 
         else 
         { 
           return null; 
         } 
       } 
       else 
       { 
         return null; 
       } 
     } 
  
     public DataTable ExecuteDataTable(string cmdText) 
     { 
       return ExecuteDataTable(cmdText, GetParameters()); 
     } 
  
     public DataRow ExecuteDataRow(string cmdText, params SqlParameter[] commandParameters) 
     { 
       DataTable dt = ExecuteDataTable(cmdText, commandParameters); 
       if (null != dt) 
       { 
         if (0 != dt.Rows.Count) 
         { 
           return dt.Rows[0]; 
  
         }  
 
         else 
         { 
           return null; 
         } 
       } 
       else 
       { 
         return null; 
       } 
     } 
  
     public DataRow ExecuteDataRow(string cmdText) 
     { 
       return ExecuteDataRow(cmdText, GetParameters()); 
     } 
  
     public int ExecuteNonQuery(string cmdText) 
     { 
       return ExecuteNonQuery(cmdText, GetParameters()); 
     } 
     public int ExecuteNonQuery(SqlTransaction trans, string cmdText) 
     { 
       return ExecuteNonQuery(trans, cmdText, GetParameters()); 
     } 
     public DataSet ExecuteDataSet(string cmdText) 
     { 
       return ExecuteDataSet(cmdText, GetParameters()); 
     } 
     public SqlDataReader ExecuteReader(string cmdText) 
     { 
       return ExecuteReader(cmdText, GetParameters()); 
     } 
     public object ExecuteScalar(string cmdText) 
     { 
       return ExecuteScalar(cmdText, GetParameters()); 
     } 
  
     private SqlParameter GetParameter(string paraName, SqlDbType paraType, object value) 
     { 
       SqlParameter para = new SqlParameter(paraName, paraType); 
       para.Value = value; 
  
       return para; 
     } 
  
     private SqlParameter GetParameter(string paraName, SqlDbType paraType, object value, int size) 
     { 
       SqlParameter para = new SqlParameter(paraName, paraType); 
       para.Value = value; 
       para.Size = size; 
       return para; 
     } 
  
     public void AddParameter(string paraName, SqlDbType paraType, object value) 
     { 
       if (null == m_ListParameter) 
       { 
         m_ListParameter = new List <SqlParameter>(); 
       } 
       SqlParameter para = GetParameter(paraName, paraType, value); 
       m_ListParameter.Add(para); 
     } 
  
     private SqlParameter[] GetParameters() 
     { 
       SqlParameter[] paras = null; 
       if (null != m_ListParameter) 
       { 
         if (0 != m_ListParameter.Count) 
         { 
           paras = m_ListParameter.ToArray(); 
         } 
         m_ListParameter = null; 
       } 
  
       return paras; 
     } 
   } 
 }

[解决办法]
C# code
using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Data;/// <summary>///BasePge 的摘要说明/// </summary>public class BasePage:System.Web.UI.Page{    public BasePage()    {        //        //TODO: 在此处添加构造函数逻辑        //    }    //    //执行数据库操作的方法    //    protected virtual DataSet GetDataSet(String query)    {        //......        return new DataSet();    }}
------解决方案-------------------- 
 
mark！
学习。。。
读书人网 >asp.net

热点推荐

触屏版 | 电脑版

读书人网 m.reader8.net