求C#高效、安全执行SQL的相关代码，高分相赠，在线待待~~~~

Question

现在在做一项目，要求用C#操作SQL 2005数据库，刚刚接触C#，还不太了解这方面的技术，请大家帮帮我~~~求C#高效、安全执行SQL的相关代码现面向全体C#战友求一个叫SQLHelper的类，或类似操作的类，文档都行，请直接将代码跟贴出来，或者发邮件：
[email protected]小弟在此万分感谢~~~~~~~~~~~~~~~~

Answer 1

sqlhelper有什么好？？？你认为什么是高效安全关键还得看你的dml,ddl这是我用的一个简单的类库:using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public static class DB
{
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    public static string DBLink;
    static DB()
    {
        DBLink = ConfigurationSettings.AppSettings[0];
    }
    /// <summary>
    /// 得到一个已经实例化的数据库连接对象
    /// </summary>
    /// <returns>得到一个已经实例化的数据库连接对象</returns>
    public static SqlConnection GetCon()
    {
        return new SqlConnection(DBLink);
    }    /// <summary>
    /// 执行存储过程返回一个DataSet对象
    /// </summary>
    /// <param name="strProcEdureName">存储过程名</param>
    /// <param name="sqlparams">参数数组</param>
    /// <returns>执行存储过程返回一个DataSet对象</returns>
    public static DataSet DSExecuteProcEdure(string strProcEdureName, params SqlParameter[] sqlparams)
    {
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = GetCmd(strProcEdureName, sqlparams);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        return ds;
    }
    /// <summary>
    /// 执行存储过程返回一个DataSet对象
    /// </summary>
    /// <param name="strProcEdureName">存储过程名</param>
    /// <returns>执行存储过程返回一个DataSet对象</returns>
    public static DataSet DSExecuteProcEdure(string strProcEdureName)
    {
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = GetCmd(strProcEdureName);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        return ds;
    }    /// <summary>
    /// 执行一个存储过程
    /// </summary>
    /// <param name="strProcEdureName">存储过程名</param>
    /// <param name="sqlparams">参数</param>
    public static void ExecuteProcEdure(string strProcEdureName,  SqlParameter[] sqlparams)
    {
        GetCmd(strProcEdureName, sqlparams);
    }    /// <summary>
    /// 执行一个存储过程
    /// </summary>
    /// <param name="strProcEdureName">存储过程名</param>
    public static void ExecuteProcEdure(string strProcEdureName)
    {
        GetCmd(strProcEdureName);
    }
    /// <summary>
    /// 获取一个已经实例化的SqlCommand对象
    /// </summary>
    /// <param name="strProcEdureName">存储过程名</param>
    /// <param name="sqlparams">参数</param>
    /// <returns>返回SqlCommand对象</returns>
    public static SqlCommand GetCmd(string strProcEdureName, params SqlParameter[] sqlparams)
    {
        SqlConnection con = GetCon();
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand(strProcEdureName, con);
            cmd.CommandType = CommandType.StoredProcedure;
            if (sqlparams != null)
            {
                foreach (SqlParameter sqlparam in sqlparams)
                {
                    cmd.Parameters.Add(sqlparam);
                }
            }
            cmd.ExecuteNonQuery();
            return cmd;
        }
        catch
        {
            return null;
        }
        finally
        {
            con.Close();
        }
    }
    /// <summary>
    /// 获取一个已经实例化的SqlCommand对象
    /// </summary>
    /// <param name="strProcEdureName">存储过程名</param>
    /// <returns>获取一个已经实例化的SqlCommand对象</returns>
    public static SqlCommand GetCmd(string strProcEdureName)
    {
        return GetCmd(strProcEdureName, null);
    }
}

Answer 2

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Data.OracleClient;
using System.IO;namespace GroupAWebApplication.CommonUtility.Helper{
    /// <summary>
    /// DatabaseHelper是一个对数据库的封装库,主要针对小型数据库开发
    /// 有着很好的跨数据库功能，但针对专有数据库优化不足
    /// </summary>
    public class DatabaseHelper : IDisposable
    {
        #region 私有字段
        private string strConnectionString;
        private DbConnection objConnection;
        private DbCommand objCommand;
        private DbProviderFactory objFactory = null;
        private bool boolHandleErrors;
        private string strLastError;
        private bool boolLogError;
        private string strLogFile;
        #endregion        #region 构造方法
        /// <summary>
        /// Initializes a new instance of the <see cref="DatabaseHelper"/> class.
        /// </summary>
        /// <param name="connectionstring">The connectionstring.</param>
        /// <param name="provider">The provider.</param>
        public DatabaseHelper(string connectionstring, Providers provider)
        {
            strConnectionString = connectionstring;
            switch (provider)
            {
                case Providers.SqlServer:
                    objFactory = SqlClientFactory.Instance;
                    break;
                case Providers.OleDb:
                    objFactory = OleDbFactory.Instance;
                    break;
                case Providers.Oracle:
                    objFactory = OracleClientFactory.Instance;
                    break;
                case Providers.ODBC:
                    objFactory = OdbcFactory.Instance;
                    break;
                case Providers.ConfigDefined:
                    string providername = ConfigurationManager.ConnectionStrings["connectionstring"].ProviderName;
                    switch (providername)
                    {
                        case "System.Data.SqlClient":
                            objFactory = SqlClientFactory.Instance;
                            break;
                        case "System.Data.OleDb":
                            objFactory = OleDbFactory.Instance;
                            break;
                        case "System.Data.OracleClient":
                            objFactory = OracleClientFactory.Instance;
                            break;
                        case "System.Data.Odbc":
                            objFactory = OdbcFactory.Instance;
                            break;
                    }
                    break;            }
            objConnection = objFactory.CreateConnection();
            objCommand = objFactory.CreateCommand();            objConnection.ConnectionString = strConnectionString;
            objCommand.Connection = objConnection;
        }        /// <summary>
        /// Initializes a new instance of the <see cref="DatabaseHelper"/> class.
        /// </summary>
        /// <param name="provider">The provider.</param>
        public DatabaseHelper(Providers provider)
            : this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString, provider)
        {
        }        /// <summary>
        /// Initializes a new instance of the <see cref="DatabaseHelper"/> class.
        /// </summary>
        /// <param name="connectionstring">The connectionstring.</param>
        public DatabaseHelper(string connectionstring)
            : this(connectionstring, Providers.SqlServer)
        {
        }        /// <summary>
        /// Initializes a new instance of the <see cref="DatabaseHelper"/> class.
        /// </summary>
        public DatabaseHelper()
            : this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString, Providers.ConfigDefined)
        {
        }
        #endregion        #region 公开属性
        /// <summary>
        /// Gets or sets a value indicating whether [handle errors].
        /// </summary>
        /// <value><c>true</c> if [handle errors]; otherwise, <c>false</c>.</value>
        public bool HandleErrors
        {
            get
            {
                return boolHandleErrors;
            }
            set
            {
                boolHandleErrors = value;
            }
        }        /// <summary>
        /// Gets the last error.
        /// </summary>
        /// <value>The last error.</value>
        public string LastError
        {
            get
            {
                return strLastError;
            }
        }        /// <summary>
        /// Gets or sets a value indicating whether [log errors].
        /// </summary>
        /// <value><c>true</c> if [log errors]; otherwise, <c>false</c>.</value>
        public bool LogErrors
        {
            get
            {
                return boolLogError;
            }
            set
            {
                boolLogError = value;
            }
        }        /// <summary>
        /// Gets or sets the log file.
        /// </summary>
        /// <value>The log file.</value>
        public string LogFile
        {
            get
            {
                return strLogFile;
            }
            set
            {
                strLogFile = value;
            }
        }        /// <summary>
        /// Adds the parameter.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="value">The value.</param>
        /// <returns></returns>
        public int AddParameter(string name, object value)
        {
            DbParameter p = objFactory.CreateParameter();
            p.ParameterName = name;
            p.Value = value;
            return objCommand.Parameters.Add(p);
        }        /// <summary>
        /// Adds the parameter.
        /// </summary>
        /// <param name="parameter">The parameter.</param>
        /// <returns></returns>
        public int AddParameter(DbParameter parameter)
        {
            return objCommand.Parameters.Add(parameter);
        }        /// <summary>
        /// Gets the command.
        /// </summary>
        /// <value>The command.</value>
        public DbCommand Command
        {
            get
            {
                return objCommand;
            }
        }        /// <summary>
        /// Begins the transaction.
        /// </summary>
        public void BeginTransaction()
        {
            if (objConnection.State == System.Data.ConnectionState.Closed)
            {
                objConnection.Open();
            }
            objCommand.Transaction = objConnection.BeginTransaction();
        }        /// <summary>
        /// Commits the transaction.
        /// </summary>
        public void CommitTransaction()
        {
            objCommand.Transaction.Commit();
            objConnection.Close();
        }        /// <summary>
        /// Get the Adapter
        /// </summary>
        public DbDataAdapter Adapter
        {
            get
            {
                return objFactory.CreateDataAdapter();
            }
        }
        #endregion

Answer 3

 #region 私有方法
         /// <summary>
        /// 将Command与Connection绑定
        /// </summary>
        /// <param name="command"></param>
        private void BingdingCommandToConnection(DbCommand command)
        {
            command.Connection = objConnection;
        }        private DbCommand BuildCommand(string commandText, CommandType commandType, DbParameter[] parameters)
        {
            //这是由本类Command属性提供的DbCommand
            //DbCommand result = Command;            DbCommand result = objFactory.CreateCommand();
            result.Connection = objConnection;
            result.CommandType = commandType;
            result.CommandText = commandText;            //如果SQL语句有参数,将参数加入参数集合
            if (parameters != null && parameters.Length != 0)
            {
                foreach (DbParameter param in parameters)
                {
                    result.Parameters.Add(param);
                }
            }            return result;
        }
        #endregion
        /// <summary>
        /// Rollbacks the transaction.
        /// </summary>
        public void RollbackTransaction()
        {
            objCommand.Transaction.Rollback();
            objConnection.Close();
        }        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string query)
        {
            return ExecuteNonQuery(query, CommandType.Text, ConnectionState.CloseOnExit);
        }        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="commandtype">The commandtype.</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string query, CommandType commandtype)
        {
            return ExecuteNonQuery(query, commandtype, ConnectionState.CloseOnExit);
        }        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="connectionstate">The connectionstate.</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string query, ConnectionState connectionstate)
        {
            return ExecuteNonQuery(query, CommandType.Text, connectionstate);
        }        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="commandtype">The commandtype.</param>
        /// <param name="connectionstate">The connectionstate.</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            int i = -1;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                i = objCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    objConnection.Close();
                }
            }            return i;
        }        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <returns></returns>
        public object ExecuteScalar(string query)
        {
            return ExecuteScalar(query, CommandType.Text, ConnectionState.CloseOnExit);
        }        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="commandtype">The commandtype.</param>
        /// <returns></returns>
        public object ExecuteScalar(string query, CommandType commandtype)
        {
            return ExecuteScalar(query, commandtype, ConnectionState.CloseOnExit);
        }        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="connectionstate">The connectionstate.</param>
        /// <returns></returns>
        public object ExecuteScalar(string query, ConnectionState connectionstate)
        {
            return ExecuteScalar(query, CommandType.Text, connectionstate);
        }        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="commandtype">The commandtype.</param>
        /// <param name="connectionstate">The connectionstate.</param>
        /// <returns></returns>
        public object ExecuteScalar(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            object o = null;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                o = objCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    objConnection.Close();
                }
            }            return o;
        }

Answer 4

/// <summary>
        /// Executes the reader.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(string query)
        {
            return ExecuteReader(query, CommandType.Text, ConnectionState.CloseOnExit);
        }        /// <summary>
        /// Executes the reader.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="commandtype">The commandtype.</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(string query, CommandType commandtype)
        {
            return ExecuteReader(query, commandtype, ConnectionState.CloseOnExit);
        }        /// <summary>
        /// Executes the reader.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="connectionstate">The connectionstate.</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(string query, ConnectionState connectionstate)
        {
            return ExecuteReader(query, CommandType.Text, connectionstate);
        }        /// <summary>
        /// Executes the reader.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="commandtype">The commandtype.</param>
        /// <param name="connectionstate">The connectionstate.</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            DbDataReader reader = null;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    reader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    reader = objCommand.ExecuteReader();
                }            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
            }            return reader;
        }        /// <summary>
        /// Executes the data set.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string query)
        {
            return ExecuteDataSet(query, CommandType.Text, ConnectionState.CloseOnExit);
        }        /// <summary>
        /// Executes the data set.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="commandtype">The commandtype.</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string query, CommandType commandtype)
        {
            return ExecuteDataSet(query, commandtype, ConnectionState.CloseOnExit);
        }

Answer 5

        /// <summary>
        /// Executes the data set.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="connectionstate">The connectionstate.</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string query, ConnectionState connectionstate)
        {
            return ExecuteDataSet(query, CommandType.Text, connectionstate);
        }        /// <summary>
        /// Executes the data set.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="commandtype">The commandtype.</param>
        /// <param name="connectionstate">The connectionstate.</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            DbDataAdapter adapter = objFactory.CreateDataAdapter();
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            adapter.SelectCommand = objCommand;
            DataSet ds = new DataSet();
            try
            {
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    if (objConnection.State == System.Data.ConnectionState.Open)
                    {
                        objConnection.Close();
                    }
                }
            }
            return ds;
        }         #region 执行Fill（）方法
        private DbDataAdapter BuildDbDataAdapter(
           DbCommand selectCommand,
           DbCommand updateCommand,
           DbCommand insertCommand,
           DbCommand deleteCommand,
           DataTableMapping mapping)
        {
            DbDataAdapter result = Adapter;
            if (selectCommand != null)
            {
                BingdingCommandToConnection(selectCommand);
                result.SelectCommand = selectCommand;
            }            if (insertCommand != null)
            {
                BingdingCommandToConnection(insertCommand);
                result.InsertCommand = insertCommand;
            }            if (updateCommand != null)
            {
                BingdingCommandToConnection(updateCommand);
                result.UpdateCommand = updateCommand;
            }            if (deleteCommand != null)
            {
                BingdingCommandToConnection(deleteCommand);
                result.DeleteCommand = deleteCommand;
            }            if (mapping != null)
            {
                result.TableMappings.Add(mapping);
            }            return result;
        }        private void BindingDataAdapterCommandsToConnection(DbDataAdapter adapter)
        {
            if (adapter.SelectCommand != null)
            {
                BingdingCommandToConnection(adapter.SelectCommand);
            }
            if (adapter.InsertCommand != null)
            {
                BingdingCommandToConnection(adapter.InsertCommand);
            }
            if (adapter.UpdateCommand != null)
            {
                BingdingCommandToConnection(adapter.UpdateCommand);
            }
            if (adapter.DeleteCommand != null)
            {
                BingdingCommandToConnection(adapter.DeleteCommand);
            }
        }        public DataSet FillDataTable(DbDataAdapter adapter)
        {
            DataSet result = new DataSet();
            BingdingCommandToConnection(adapter.SelectCommand);//要将具体的SelectCommand与connection 绑定
            adapter.Fill(result);
            return result;
        }        public DataSet FillDataTable(DbCommand selectCommand)
        {
            DataSet result = new DataSet();
            DbDataAdapter adapter = BuildDbDataAdapter(selectCommand, null, null, null, null);
            adapter.Fill(result);
            return result;
        }
        public DataSet FillDataTable(string commandText, CommandType commandType, DbParameter[] parameters)
        {
            DataSet result = new DataSet();
            DbCommand selectCommand = BuildCommand(commandText, commandType, parameters);
            FillDataTable( selectCommand);
            return result;
        }
        #endregion
                /// <summary>
        /// 将DataReader对象转换为DataTable
        /// </summary>
        /// <param name="reader">
        /// DataReader对象
        /// </param>
        /// <returns></returns>
        public DataTable ReaderToTable(DbDataReader reader)
        {
            DataTable newTable = new DataTable();
            DataColumn col;
            DataRow row;
            for (int i = 0; i < reader.FieldCount - 1; i++)
            {
                col = new DataColumn();
                col.ColumnName = reader.GetName(i);
                col.DataType = reader.GetFieldType(i);
                newTable.Columns.Add(col);
            }            while (reader.Read())
            {
                row = newTable.NewRow();
                for (int j = 0; j < reader.FieldCount - 1; j++)
                {
                    row[j] = reader[j];
                }
                newTable.Rows.Add(row);
            }
            return newTable;
        }        /// <summary>
        /// Handles the exceptions.
        /// </summary>
        /// <param name="ex">The ex.</param>
        private void HandleExceptions(Exception ex)
        {
            if (LogErrors)
            {
                WriteToLog(ex.Message);
            }
            if (HandleErrors)
            {
                strLastError = ex.Message;
            }
            else
            {
                throw ex;
            }
        }        /// <summary>
        /// Writes to log.
        /// </summary>
        /// <param name="msg">The MSG.</param>
        private void WriteToLog(string msg)
        {
            StreamWriter writer = File.AppendText(LogFile);
            writer.WriteLine(DateTime.Now.ToString() + " - " + msg);
            writer.Close();
        }        /// <summary>
        /// 执行与释放或重置非托管资源相关的应用程序定义的任务。
        /// </summary>
        public void Dispose()
        {
            objConnection.Close();
            objConnection.Dispose();
            objCommand.Dispose();
        }    }
    /// <summary>
    /// 指定数据提供者的类型
    /// </summary>
    public enum Providers
    {
        SqlServer, OleDb, Oracle, ODBC, ConfigDefined
    }
    /// <summary>
    /// 指定连接状态
    /// </summary>
    public enum ConnectionState
    {
        KeepOpen, CloseOnExit
    }
}

Answer 6

完了
把这四段合一起就行了。
编译成.dll用吧
我用了很长时间这个了

Answer 7

我给你发邮件 发一个Sqlhelper吧. 都是初学者,共同进步吧.

Answer 8

发你邮箱里了,你查收一下吧[email protected] 这个地址发的。

Answer 9

建议使用微软的企业类库下面的数据库访问组件
现在出到4.0，稳定，提供强大的接口、公开源代码、完整的单元测试。
http://www.microsoft.com/downloads/details.aspx?FamilyID=90de37e0-7b42-4044-99be-f8ecfbbc5b65&DisplayLang=en
详细介绍：http://msdn.microsoft.com/en-us/library/cc512464.aspx还有 Unity Application Block
http://www.microsoft.com/downloads/details.aspx?FamilyId=6A9E363C-8E0A-48D3-BBE4-C2F36423E2DF&displaylang=en

Answer 10

微软的Enterprise Library 
http://www.codeplex.com/entlib
不是仅仅包含数据操作

Answer 11

搂主，这个你去网上搜SQLHelper，就能找到，毕业设计的时候我就用过了

Answer 12

  SqlHelper 可以自己写一个。 也可以直接从 PetShop 中得到。  学会使用后就要学会扩展