Sql Server数据库帮助类

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

 

/// <summary>

///SqlHelper 的摘要说明

/// </summary>

public class SqlHelper

{

    /// <summary>

    /// sql Server数据库操作帮助类

    /// </summary>

       public SqlHelper()

       {

              //

              //TODO: 在此处添加¨构造函数逻辑

              //

       }

    /// <summary>

    /// 获取数据库连接字符串

    /// </summary>

    public static readonly string cnnstring = ConfigurationManager.ConnectionStrings["cnnstring"].ConnectionString;

    /// <summary>

    /// 获取结果集的第一行第一列的结果

    /// </summary>

    /// <param name="sqlText">要执行的检索语句</param>

    /// <param name="paramss">检索语句中的参数列表</param>

    /// <returns></returns>

    public static object ExecuteScalar(string sqlText,params SqlParameter[] paramss)

    {

        using (SqlConnection sqlCnn=new SqlConnection (cnnstring))

        {

            using (SqlCommand sqlCmm=sqlCnn.CreateCommand())

            {

                sqlCmm.CommandText = sqlText;

                fillParameter(paramss, sqlCmm);

                sqlCnn.Open();

                return sqlCmm.ExecuteScalar();

            }

        }

    }

    /// <summary>

    /// 填充Command对象的参数列表

    /// </summary>

    /// <param name="paramss">参数列表</param>

    /// <param name="sqlCmm">Command对象</param>

    private static void fillParameter(SqlParameter[] paramss, SqlCommand sqlCmm)

    {

        foreach (SqlParameter  param in paramss)

        {

            sqlCmm.Parameters.Add(param);

        }

    }

    /// <summary>

    /// 获取检索结果集，返回DataTable

    /// </summary>

    /// <param name="sqlText">要检索的语句</param>

    /// <param name="paramss">检索语句中的参数列表</param>

    /// <returns></returns>

    public static DataTable ExecuteTable(string sqlText,params SqlParameter[] paramss)

    {

        using (SqlConnection sqlCnn=new SqlConnection (cnnstring))

        {

            using (SqlCommand sqlCmm=sqlCnn.CreateCommand())

            {

                sqlCmm.CommandText = sqlText;

                fillParameter(paramss,sqlCmm);

                sqlCnn.Open();

                using (SqlDataReader reader=sqlCmm.ExecuteReader())

                {

                    DataTable dt = new DataTable();

                    dt.Load(reader);

                    return dt;

                }

            }

        }

    }

    /// <summary>

    /// 执行对数据库的增、删、改操作，并返回数据库中受影响的行数

    /// </summary>

    /// <param name="sqlText">要执行的insert、updata、delete语句</param>

    /// <param name="paramss">要执行的语句中的参数列表</param>

    /// <returns></returns>

    public static int ExecuteNonQuery(string sqlText,params SqlParameter[] paramss)

    {

        using (SqlConnection sqlCnn=new SqlConnection (cnnstring))

        {

            using (SqlCommand sqlCmm=sqlCnn.CreateCommand())

            {

                sqlCmm.CommandText = sqlText;

                fillParameter(paramss,sqlCmm);

                sqlCnn.Open();

                return sqlCmm.ExecuteNonQuery();

            }

        }

    }

 

}