SQLHelper.cs类源码-数据库命名空间-代码大全-开发者工具网

1-HttpHelper
- HttpCookieHelper.cs
- HttpHelper.cs
2-数据库
- DbHelperMySQL.cs
- DbHelperOleDb.cs
- DbHelperOra.cs
- DbHelperSQL.cs
- DbHelperSQLite.cs
- DbHelperSQLP.cs
- OracleHelper.cs
- SQLHelper.cs
3-百度相关组件
- BaiduMapHelper.cs
BarCode
- BarCodeToHTML.cs
Chart
- Assistant.cs
- OWCChart11.cs
Config
- ConfigHelper-sufei.cs
- ConfigHelper.cs
Control
- BindDataControl.cs
Convert
- ConvertHelper.cs
- StringPlus.cs
Cookie_Session_Cache
- CacheHelper.cs
- CookieHelper.cs
- DataCache.cs
- SessionHelper.cs
- SessionHelper2.cs
CSV
- CsvHelper.cs
Date
- CNDate.cs
DEncrypt
- DEncrypt.cs
- DESEncrypt.cs
- Encrypt.cs
- HashEncode.cs
- MySecurity.cs
- RSACryption.cs
Download_Up
- DownLoadHelper.cs
- FileDown.cs
- FileUp.cs
- UpLoadFiles.cs
Encoding
- FileEncoding.cs
Excel
- DataToExcel.cs
- ExcelHelper.cs
- ExportExcel.cs
- GridViewExport.cs
File
- DirFileHelper.cs
- FileOperateHelper.cs
- INIFileHelper.cs
FormatDate
- TimeHelper.cs
FTP
- FTPClient.cs
- FTPHelper.cs
- FTPOperater.cs
GridView
- GridViewHelper.cs
Image
- ImageClass.cs
- ImageDown.cs
- ImageUpload.cs
- PicDeal.cs
Input
- InputHelper.cs
Internet_Url
- AdslHelper.cs
- IpHelper.cs
- NetHelper.cs
- UrlOper.cs
IP
- IPHelper.cs
JS
- ExeJsHelper.cs
- JsHelper.cs
JsAlert
- MessageBox.cs
- ShowMessage.cs
Json
- ConvertJson.cs
- JsonHelper.cs
- SerializerJsonHelper.cs
Mail
- ConnectCommand.cs
- ConnectResponse.cs
- DeleCommand.cs
- ListCommand.cs
- ListResponse.cs
- MailHeaders.cs
- MailHelper.cs
- MailMessageEx.cs
- MailPoper.cs
- MailSender.cs
- NoopCommand.cs
- PassCommand.cs
- Pop3Client.cs
- Pop3Command.cs
- Pop3Commands.cs
- Pop3Exception.cs
- Pop3ListItem.cs
- Pop3Response.cs
- Pop3Responses.cs
- Pop3State.cs
- QuitCommand.cs
- RetrCommand.cs
- RetrResponse.cs
- RsetCommand.cs
- SmtpServerHelper.cs
- Stat.cs
- StatCommand.cs
- StatResponse.cs
- TopCommand.cs
- UserCommand.cs
Mime
- MediaTypes.cs
- MimeEntity.cs
- MimeHeaders.cs
- MimeReader.cs
- QuotedPrintableEncoding.cs
Page
- BasePage.cs
- HTMLHelper.cs
- HttpProc.cs
- JavaScriptPlus.cs
- PageHelper.cs
- Utility.cs
- WebSitePathHelper.cs
PinYin
- EcanConvertToCh.cs
- PinYin.cs
Properties
- AssemblyInfo.cs
Random
- BaseRandom.cs
- RandomHelper.cs
Regex
- RegexHelper.cs
ResourceManager
- AppMessage.cs
- ResourceManager.cs
- ResourceManagerWrapper.cs
- Resources.cs
Rmb
- RMB.cs
SegList
- SegList.cs
seo
- SeoHelper.cs
Serialize
- Serialize.cs
- SerializeHelper.cs
Sound
- MediaHandler.cs
Sqlhelper
- SqlHelper.cs
String
- QueryString.cs
- StringHelper.cs
System
- SysHelper.cs
Validate_Tool
- FormulaExpress.cs
- PageValidate.cs
- Tools.cs
- ValidateImg.cs
Video
- VideoConvert.cs
Web
- FilterHelper.cs
- WebHelper.cs
XML
- XmlHelper.cs
- XMLProcess.cs
Yzm
- YZMHelper.cs
Zip
- SharpZip.cs
百度相关组件
- BaiduMapHelper.cs
系统Api
- API.cs
- Mac.cs
- ResourcesApi.cs
- Win32Private.cs
- Win32Public.cs

你现在查看的是SQLHelper.cs类的源码

/// <summary>
/// 编 码 人：苏飞
/// 联系方式：361983679  
/// 更新网站：http://www.sufeinet.com/thread-655-1-1.html
/// </summary>
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace Maticsoft.DBUtility
{

/// <summary>
    /// The SqlHelper class is intended to encapsulate high performance, 
    /// scalable best practices for common uses of SqlClient.
    /// </summary>
    public abstract class SqlHelper
    {

//Database connection strings
        public static readonly string ConnectionStringLocalTransaction =  ConfigurationManager.AppSettings["SQLConnString1"];
        public static readonly string ConnectionStringInventoryDistributedTransaction =  ConfigurationManager.AppSettings["SQLConnString2"];
        public static readonly string ConnectionStringOrderDistributedTransaction =  ConfigurationManager.AppSettings["SQLConnString3"];
        public static readonly string ConnectionStringProfile =  ConfigurationManager.AppSettings["SQLProfileConnString"];

// Hashtable to store cached parameters
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

/// <summary>
        /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</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>
        /// Execute a SqlCommand (that returns no resultset) against an existing database connection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</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>
        /// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans">an existing sql transaction</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</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>
        /// Execute a SqlCommand that returns a resultset against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>A SqlDataReader containing the results</returns>
        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);

// we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

/// <summary>
        /// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</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>
        /// Execute a SqlCommand that returns the first column of the first record against an existing database connection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</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;
        }

/// <summary>
        /// add parameter array to the cache
        /// </summary>
        /// <param name="cacheKey">Key to the parameter cache</param>
        /// <param name="cmdParms">an array of SqlParamters to be cached</param>
        public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

/// <summary>
        /// Retrieve cached parameters
        /// </summary>
        /// <param name="cacheKey">key used to lookup parameters</param>
        /// <returns>Cached SqlParamters array</returns>
        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>
        /// Prepare a command for execution
        /// </summary>
        /// <param name="cmd">SqlCommand object</param>
        /// <param name="conn">SqlConnection object</param>
        /// <param name="trans">SqlTransaction object</param>
        /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
        /// <param name="cmdText">Command text, e.g. Select * from Products</param>
        /// <param name="cmdParms">SqlParameters to use in the command</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);
            }
        }
    }
}