.NET操作数据库类

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;

public class SqlClass
{
    protected SqlConnection Connection;
    protected string connectionstring;

    public SqlClass()
    {
        connectionstring = "Server=(local);Database=LightA;uid=sa;pwd=;";
        Connection = new SqlConnection(connectionstring);
    }

    public SqlClass(string connstring)
    {
        connectionstring = connstring;
        Connection = new SqlConnection(connectionstring);
    }

    /// <summary>
    /// 得到SQL连接
    /// </summary>
    public SqlConnection GetConnection()
    {
        if (Connection.State != ConnectionState.Open)
            Connection.Open();
        return Connection;
    }

    public void CloseConnection()
    {
        if (Connection.State != ConnectionState.Closed)
            Connection.Close();
    }


    /*执行返回结果和不返回结果的存储过程方法*/
    /// <summary>
    /// 根据存储过程名称和参数生成对应的SQL命令对象
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">存储过程参数</param>
    private SqlCommand BuilderQueryCommand(string storedProcName, SqlParameter[] parameters)
    {
        SqlCommand command = new SqlCommand();
        command.Connection = Connection;
        command.CommandText = storedProcName.Trim();
        command.CommandType = CommandType.StoredProcedure;
        if (parameters != null)
        {
            foreach (SqlParameter p in parameters)
            {
                command.Parameters.Add(p);
            }
        }
        return command;
    }
    /// <summary>
    /// 返回结果的存储过程
    /// </summary>
    public SqlDataReader GetDataReaderByProc(string storedProcName, SqlParameter[] parameters)
    {
        SqlDataReader reader;
        SqlCommand cmd = BuilderQueryCommand(storedProcName, parameters);
        if (Connection.State == ConnectionState.Closed)
        {
            Connection.Open();
        }
        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return reader;
    }
    /// <summary>
    /// 不返回结果的存储过程
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">参数值</param>
    /// <returns></returns>
    public int GetEffect(string storedProcName, SqlParameter[] parameters)
    {
        int result = 0;
        try
        {
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
            }
            SqlTransaction trans = Connection.BeginTransaction();
            try
            {
                SqlCommand cmd = BuilderQueryCommand(storedProcName, parameters);
                cmd.Transaction = trans;
                result = cmd.ExecuteNonQuery();
                trans.Commit();
                Connection.Close();
                return result;
            }
            catch (Exception ex)
            {
                if (trans != null)
                {
                    trans.Rollback();
                    return result;
                }
                throw ex;

            }
            finally
            {
                if (trans != null)
                {
                    trans.Dispose();
                }
                Connection.Close();
            }
        }
        catch (Exception ex1)
        {
            Debug.WriteLine(ex1.ToString());
            return 0;
            //throw new Exception(ex1.Message);
        }
    }
    /// <summary>
    /// 返回dateSet
    /// </summary>
    /// <param name="ProcName"></param>
    /// <param name="parameters"></param>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public DataSet GetDataSetByProc(string ProcName, SqlParameter[] parameters, string tableName)
    {
        try
        {

            DataSet ds = new DataSet();
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
            }

            SqlDataAdapter myDa = new SqlDataAdapter();
            myDa.SelectCommand = BuilderQueryCommand(ProcName, parameters);
            myDa.Fill(ds, tableName);
            return ds;
        }
        catch
        {
            return null;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 自定义分页
    /// </summary>
    /// <param name="ProcName">存储过程名</param>
    /// <param name="parameters">参数名</param>
    /// <param name="start">起始页</param>
    /// <param name="maxRecord">记录数</param>
    /// <param name="tableName">表名</param>
    /// <returns></returns>
    public DataSet GetDataSetByProc(string ProcName, SqlParameter[] parameters, int start, int maxRecord, string tableName)
    {
        try
        {
            DataSet ds = new DataSet();
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
            }
            Connection.Open();
            SqlDataAdapter myDa = new SqlDataAdapter();
            myDa.SelectCommand = BuilderQueryCommand(ProcName, parameters);
            myDa.Fill(ds, start, maxRecord, tableName);
            return ds;
        }
        catch
        {
            Connection.Close();
            return null;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 返回object类型　比如首行首列
    /// </summary>
    /// <param name="storedProcName">存储过程名称</param>
    /// <param name="parameters">参数列表</param>
    /// <returns></returns>
    public object GetObjectByProc(string storedProcName, SqlParameter[] parameters)
    {
        object result = null;

        if (Connection.State == ConnectionState.Closed)
        {
            Connection.Open();
        }
        //   SqlTransaction trans = Connection.BeginTransaction();
        try
        {
            SqlCommand cmd = BuilderQueryCommand(storedProcName, parameters);
            // cmd.Transaction = trans;
            result = cmd.ExecuteScalar();
            // trans.Commit();
            Connection.Close();
            return result;
        }
        catch
        {
            // trans.Rollback();
            return result;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 通过ＩＤ号操作某条记录,比如删除ＩＤ号的记录
    /// </summary>
    /// <param name="ProcName">存储过程名</param>
    /// <param name="id">值</param>
    /// <param name="paraname">参数名如：@ID</param>
    /// <returns></returns>
    public int RunProcByID(string ProcName, int id, string paraname)
    {
        try
        {
            SqlParameter[] p ={ new SqlParameter(paraname, SqlDbType.Int) };
            p[0].Value = id;
            return GetEffect(ProcName, p);
        }
        catch
        {
            Connection.Close();
            return 0;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 修改ＩＤ号为keyValue的记录的ParaDieldName字段的值为FiledValue
    /// </summary>
    /// <param name="ProcName">相信过程名</param>
    /// <param name="keyValue">ID号的值</param>
    /// <param name="paraKeyName">ID号对应的字段名</param>
    /// <param name="paraFieldName">要修改的字段名</param>
    /// <param name="FieldValue">要修改字段的值</param>
    /// <param name="length">字段的长度</param>
    /// <returns></returns>
    public int UpdateByID(string ProcName, int keyValue, string paraKeyName, string paraFieldName, string FieldValue, int length)
    {
        try
        {
            SqlParameter[] p ={ new SqlParameter(paraFieldName,SqlDbType.VarChar,length),
                                    new SqlParameter(paraFieldName, SqlDbType.Int) 
                                   };
            p[0].Value = FieldValue;
            p[1].Value = keyValue;
            return GetEffect(ProcName, p);
        }
        catch
        {
            Connection.Close();
            return 0;
        }
        finally
        {
            Connection.Close();
        }
    }

   #region 执行SQL，返回DataSet
    /// <summary>
    /// 执行ＳＱＬ语句返回DataSet
    /// </summary>
    public DataSet GetDataSet(string strSql)
    {
        try
        {

            DataSet ds = new DataSet();
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
            }
            SqlDataAdapter myDa = new SqlDataAdapter();
            myDa.SelectCommand = new SqlCommand(strSql, Connection);
            myDa.Fill(ds);
            return ds;
        }
        catch
        {
            return null;
        }
        finally
        {
            Connection.Close();
        }
    }
   #endregion

    #region  执行SQL，返回影响行数
    /// <summary>
    /// 执行ＳＱＬ语句返回影响行数
    /// </summary>
    public int RunSql(string strSql)
    {
        int effect = 0;
        if (Connection.State == ConnectionState.Closed)
        {
            Connection.Open();
        }
        SqlTransaction trans = Connection.BeginTransaction();
        try
        {
            SqlCommand cmd = new SqlCommand(strSql, Connection);
            cmd.Transaction = trans;
            effect = cmd.ExecuteNonQuery();
            trans.Commit();
            return effect;
        }
        catch
        {
            trans.Rollback();
            Connection.Close();
            return effect;
        }
        finally
        {
            Connection.Close();
        }
    }
    #endregion

    #region 执行ＳＱＬ语句返回SqlDataReader
    /// <summary>
    /// 执行ＳＱＬ语句返回SqlDataReader
    /// </summary>
    public SqlDataReader GetDataReader(string strsql)
    {
        if (Connection.State == ConnectionState.Closed)
        {
            Connection.Open();
        }
        try
        {
            SqlCommand cmd = new SqlCommand(strsql, Connection);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch
        {
            Connection.Close();
            return null;
        }

    }
    #endregion

    #region 执行SQL，返回首行首列
    /// <summary>
    /// 执行ＳＱＬ语句返回object
    /// </summary>
    public object GetObject(string strsql)
    {
        if (Connection.State == ConnectionState.Closed)
        {
            Connection.Open();
        }
        object obj = null;
        try
        {
            SqlCommand cmd = new SqlCommand(strsql, Connection);
            obj = cmd.ExecuteScalar();
            Connection.Close();
            return obj;
        }
        catch
        {
            Connection.Close();
            return null;
        }
       finally
        {
            Connection.Close();
       }
    }
    #endregion

#region 执行SQL，得到DataTable
    public DataTable GetDataTable(string strSql)
    {
        DataTable dt = new DataTable();
        SqlDataAdapter sda = new SqlDataAdapter();
        try
        {
            SqlCommand cmd = new SqlCommand(); 
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strSql;
            cmd.Connection = GetConnection();
            sda.SelectCommand = cmd;
            sda.Fill(dt);
        }
        catch (Exception e)
        {
            Debug.WriteLine(e.ToString());
            CloseConnection();
        }
        finally
        {
            CloseConnection();
        }
        return dt;
    }
#endregion
}
 

 

   使用举例：

 

   SqlClass sql = new SqlClass();

            SqlDataReader dr = sql.GetDataReader("select * from");
            if (dr != null)
            {
                while (dr.Read())
                {
                    if (dr["RecvTime"] != DBNull.Value)
                    {
                        //......
                    }
                }
                dr.Close();
                dr.Dispose();
            }