SqlDbHelper备份，做项目时方便应用(目前不太全，把自己项目中的逐渐转移过来)...

******************************************

这是官网新闻左侧类别那部分用到的

****************************************

public string ConnectionString = ConfigurationManager.ConnectionStrings["GsWebDbEntities"].ConnectionString;
public myDBHelper()
{
//
//TODO: 在此处添加构造函数逻辑
//
}

public DataSet ExecuteDataset(CommandType commandType, string commandText, params DbParameter[] commandParameters)
{

if ((this.ConnectionString == null) || (this.ConnectionString.Length == 0))
{
throw new ArgumentNullException("ConnectionString");
}
using (DbConnection connection = new SqlConnection(ConnectionString))
{
connection.ConnectionString = this.ConnectionString;
connection.Open();
return this.ExecuteDataset(connection, commandType, commandText, commandParameters);
}
}

 

public DataSet ExecuteDataset(DbConnection connection, CommandType commandType, string commandText, params DbParameter[] commandParameters)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
DbCommand command = new SqlCommand();

this.PrepareCommand(command, connection, null, commandType, commandText, commandParameters);
using (DbDataAdapter adapter = new SqlDataAdapter())
{
adapter.SelectCommand = command;
DataSet dataSet = new DataSet();
DateTime now = DateTime.Now;
adapter.Fill(dataSet);
DateTime dtEnd = DateTime.Now;

command.Parameters.Clear();
if (connection.State==ConnectionState.Open)
{
connection.Close();
}


return dataSet;
}
}

 

private void PrepareCommand(DbCommand command, DbConnection connection, DbTransaction transaction, CommandType commandType, string commandText, DbParameter[] commandParameters)
{
if (command == null)
{
throw new ArgumentNullException("command");
}
if ((commandText == null) || (commandText.Length == 0))
{
throw new ArgumentNullException("commandText");
}
if (connection.State != ConnectionState.Open)
{

connection.Open();
}

command.Connection = connection;
command.CommandText = commandText;
if (transaction != null)
{
if (transaction.Connection == null)
{
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
}
command.Transaction = transaction;
}
command.CommandType = commandType;
if (commandParameters != null)
{

foreach (DbParameter parameter in commandParameters)
{
if (parameter != null)
{
if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}

}
}

 

 

 

*****************************************************************************以下来自网络，以上来自自己项目中的整理

using System;

using System.Data;

using System.Data.SqlClient;

 

namespace DbHelper

{

     public class SqlDbHelper

     {

         private SqlConnection conn;

         private SqlCommand cmd;

         private SqlDataReader reader;

         private SqlDataAdapter adapter;

         private string connectionString = @"server=.;database=student;uid=sa;pwd=scce" ;

 

         public string ConnectionString

         {

             get { return this .connectionString; }

             set { this .connectionString = value; }

         }

 

         /// <summary>

         /// 获取一个未打开连接的SqlConnection对象

         /// </summary>

         /// <returns>SqlConnection对象</returns>

         public SqlConnection GetConnection()

         {

             if (conn != null )

                 return this .conn;

             return this .conn = new SqlConnection(connectionString);

         }

 

         /// <summary>

         /// 使用连接字符串获取未打开连接SqlConnection对象

         /// </summary>

         /// <param name="_connStr">连接字符串</param>

         /// <returns>SqlConnection对象</returns>

         public SqlConnection GetConnection( string _connStr)

         {

             if ( this .conn != null )

                 this .conn.ConnectionString = _connStr;

             else

                 this .conn = new SqlConnection(_connStr);

             return this .conn;

         }

 

         /// <summary>

         /// 使用指定的Sql语句创建SqlCommand对象

         /// </summary>

         /// <param name="sqlStr">Sql语句</param>

         /// <returns>SqlCommand对象</returns>

         private SqlCommand GetCommand( string sqlStr)

         {

             if ( this .conn == null )

                 this .conn = GetConnection();

             if ( this .cmd == null )

                 this .cmd = this .GetCommand(sqlStr, CommandType.Text, null );

             else

             {

                 this .cmd.CommandType = CommandType.Text;

                 this .cmd.Parameters.Clear();

             }

             this .cmd.CommandText = sqlStr;

             return this .cmd;

         }

 

         /// <summary>

         /// 使用指定的Sql语句,CommandType,SqlParameter数组创建SqlCommand对象

         /// </summary>

         /// <param name="sqlStr">Sql语句</param>

         /// <param name="type">命令类型</param>

         /// <param name="paras">SqlParameter数组</param>

         /// <returns>SqlCommand对象</returns>

         public SqlCommand GetCommand( string sqlStr, CommandType type, SqlParameter[] paras)

         {

             if (conn == null )

                 this .conn = this .GetConnection();

             if (cmd == null )

                 this .cmd = conn.CreateCommand();

             this .cmd.CommandType = type;

             this .cmd.CommandText = sqlStr;

             this .cmd.Parameters.Clear();

             if (paras != null )

                 this .cmd.Parameters.AddRange(paras);

             return this .cmd;

         }

 

         /// <summary>

         /// 执行Sql语句返回受影响的行数

         /// </summary>

         /// <param name="sqlStr">Sql语句</param>

         /// <returns>受影响的行数,失败则返回-1</returns>

         public int ExecuteNoQuery( string sqlStr)

         {

             int line = -1;

             CheckArgs(sqlStr);

             try { OpenConn(); line = this .ExecuteNonQuery(sqlStr,CommandType.Text, null ); }

             catch (SqlException e) { throw e; }

             return line;

         }

 

         /// <summary>

         /// 使用指定的Sql语句,CommandType,SqlParameter数组执行Sql语句,返回受影响的行数

         /// </summary>

         /// <param name="sqlStr">Sql语句</param>

         /// <param name="type">命令类型</param>

         /// <param name="paras">SqlParameter数组</param>

         /// <returns>受影响的行数</returns>

         public int ExecuteNonQuery( string sqlStr, CommandType type, SqlParameter[] paras)

         {

             int line = -1;

             CheckArgs(sqlStr);

             if ( this .cmd == null )

                 GetCommand(sqlStr, type, paras);

             this .cmd.Parameters.Clear();

             this .cmd.CommandText = sqlStr;

             this .cmd.CommandType = type;

             if (paras != null )

                 this .cmd.Parameters.AddRange(paras);

             try { OpenConn(); line = this .cmd.ExecuteNonQuery(); }

             catch (SqlException e) { throw e; }

             return line;

         }

 

         /// <summary>

         /// 使用指定Sql语句获取dataTable

         /// </summary>

         /// <param name="sqlStr">Sql语句</param>

         /// <returns>DataTable对象</returns>

         public DataTable GetDataTable( string sqlStr)

         {

             CheckArgs(sqlStr);

             if ( this .conn == null )

                 this .conn = GetConnection();

             this .adapter = new SqlDataAdapter(sqlStr, this .conn);

             DataTable table = new DataTable();

             try { adapter.Fill(table); }

             catch (SqlException e) { throw e; }

             return table;

         }

 

         /// <summary>

         /// 使用指定的Sql语句获取SqlDataReader

         /// </summary>

         /// <param name="sqlStr">sql语句</param>

         /// <returns>SqlDataReader对象</returns>

         public SqlDataReader GetSqlDataReader( string sqlStr)

         {

             CheckArgs(sqlStr);

             if (cmd == null )

                 GetCommand(sqlStr);

             if (reader != null )

                 reader.Dispose();

             try { OpenConn(); this .reader = this .cmd.ExecuteReader(); }

             catch (SqlException e) { throw e; }

             return this .reader;

         }

 

         /// <summary>

         /// 使用事务执行多条Sql语句

         /// </summary>

         /// <param name="sqlCommands">sql语句数组</param>

         /// <returns>全部成功则返回true否则返回false</returns>

         public bool ExecuteSqls( string [] sqlCommands)

         {

             if (sqlCommands == null )

                 throw new ArgumentNullException();

             if ( this .cmd == null )

                 GetCommand( null );

             SqlTransaction tran = null ;

             try

             {

                 OpenConn();

                 tran = this .conn.BeginTransaction();

                 this .cmd.Transaction = tran;

                 foreach ( string sql in sqlCommands)

                 {

                     if (ExecuteNoQuery(sql) == 0)

                     { tran.Rollback(); return false ; }

                 }

             }

             catch (SqlException e)

             {

                 if (tran != null )

                     tran.Rollback();

                 throw e;

             }

             tran.Commit();

             return true ;

         }

 

         private void OpenConn()

         {

             try

             {

                 if ( this .conn.State == ConnectionState.Closed)

                     conn.Open();

             }

             catch (SqlException e) { throw e; }

         }

 

         private void CheckArgs( string sqlStr)

         {

             if (sqlStr == null )

                 throw new ArgumentNullException();

             if (sqlStr.Length == 0)

                 throw new ArgumentOutOfRangeException();

         }

 

     }

}

转载于:https://www.cnblogs.com/wanshutao/p/4011197.html