自己封装的MSSQL的数据库操作类

代码

/*
* 作者: 牛腩
* 创建时间: 2010-1-20 10:43:14
* Email: 164423073@qq.com
* 说明: 对MSSQL数据库的增删查改操作的封装类
*/

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

namespace Niunan.Tg029.Hr.Utility
{
     public class MSSQLHelper
    {
         private SqlConnection conn = null ;
         private SqlCommand cmd = null ;
         private SqlDataReader sdr = null ;

         public MSSQLHelper()
        {
             string connStr = WebConfigurationManager.ConnectionStrings[ " connStr " ].ToString();
            conn = new SqlConnection(connStr);
        }

         /// <summary> 创建Command对象
         /// 默认是SQL语句
         /// </summary>
         /// <param name="sql"> SQL语句 </param>
         public void CreateCommand( string sql)
        {
            conn.Open();
            cmd = new SqlCommand(sql, conn);
        }

         /// <summary> 创建存储过程的Command对象
         ///
         /// </summary>
         /// <param name="procName"> 存储过程名称 </param>
         public void CreateStoredCommand( string procName)
        {
            conn.Open();
            cmd = new SqlCommand(procName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
        }

         /// <summary> 添加参数
         /// 默认是输入参数
         /// </summary>
         /// <param name="paramName"> 参数名称 </param>
         /// <param name="value"> 值 </param>
         public void AddParameter( string paramName, object value)
        {
            SqlParameter p = new SqlParameter(paramName, value);
            cmd.Parameters.Add(p);
        }

         /// <summary> 添加输出参数
         /// 用于存储过程
         /// </summary>
         /// <param name="paramName"> 参数名称 </param>
         /// <param name="value"> 值 </param>
         public void AddOutputParameter( string paramName) {
            SqlParameter p = new SqlParameter();
            p.ParameterName = paramName;
            p.Direction = ParameterDirection.Output;
            p.Size = 20 ;
            cmd.Parameters.Add(p);
        }

         /// <summary> 获取输出参数的值
         ///
         /// </summary>
         /// <param name="paramName"> 输出参数名称 </param>
         /// <returns></returns>
         public string GetOutputParameter( string paramName) {
             return cmd.Parameters[paramName].Value.ToString();
        }

         /// <summary> 执行增删改SQL语句或存储过程
         ///
         /// </summary>
         /// <returns></returns>
         public bool ExecuteNonQuery()
        {
             int res;
             try
            {
                res = cmd.ExecuteNonQuery();
                 if (res > 0 )
                {
                     return true ;
                }
            }
             catch (Exception ex)
            {
                 throw ex;
            }
             finally
            {
                 if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
             return false ;
        }

         /// <summary> 执行查询SQL语句或存储过程
         ///
         /// </summary>
         /// <returns></returns>
         public DataTable ExecuteQuery()
        {
            DataTable dt = new DataTable();
             using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
             return dt;
        }

         /// <summary> 返回查询SQL语句或存储过程查询出的结果的第一行第一列的值
         ///
         /// </summary>
         /// <returns></returns>
         public string ExecuteScalar()
        {
             string res = "" ;
             try
            {
                 object obj = cmd.ExecuteScalar();
                 if (obj != null )
                {
                    res = obj.ToString();
                }
            }
             catch (Exception ex)
            {
                 throw ex;
            }
             finally
            {
                 if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
             return res;
        }

    }
}

秒客网

自己封装的MSSQL的数据库操作类

相关文章