简单的DBHelper类

来源：互联网发布：spss卡方数据分析教程编辑：程序博客网时间：2024/05/03 01:20

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Configuration;using System.Data.SqlClient;using System.Data;namespace GoldenUnicornDAL{   public class DBHelper    {       private static string ConnString = ConfigurationManager.ConnectionStrings["GoldenUnicornConnectionString"].ToString();       public static SqlConnection Conn = new SqlConnection(ConnString);       /// <summary>       /// 打开连接方法       /// </summary>       public static void OpenConn()        {           if (Conn.State == ConnectionState.Open || Conn.State == ConnectionState.Broken)           {               Conn.Close();               Conn.Open();           }           else            {               Conn.Open();           }       }       /// <summary>       /// 关闭连接方法       /// </summary>       public static void CloseConn()       {           if (Conn.State == ConnectionState.Open || Conn.State == ConnectionState.Broken)           {               Conn.Close();           }       }       public static bool ExecuteNonQuery(string sql, SqlParameter[] pars)        {           bool IsFind = false;           OpenConn();           SqlCommand cmd = new SqlCommand(sql, Conn);           cmd.Parameters.AddRange(pars);           int result = cmd.ExecuteNonQuery();           if (result > 0)            {               IsFind = true;           }           CloseConn();           return IsFind;       }       public static bool ExecuteNonQueryProc(string sql, SqlParameter[] pars)       {           bool IsFind = false;           OpenConn();           SqlCommand cmd = new SqlCommand(sql, Conn);           cmd.CommandType = CommandType.StoredProcedure;           cmd.Parameters.AddRange(pars);           int result = cmd.ExecuteNonQuery();           if (result > 0)           {               IsFind = true;           }           CloseConn();           return IsFind;       }       public static bool ExecuteScalar(string sql, SqlParameter[] pars)        {           bool IsFind = false;           OpenConn();           SqlCommand cmd = new SqlCommand(sql, Conn);           cmd.Parameters.AddRange(pars);           int result = (int)cmd.ExecuteScalar();           if (result > 0)            {               IsFind = true;           }           CloseConn();           return IsFind;       }       public static int ExecuteScalars(string sql, SqlParameter[] pars)       {           OpenConn();           SqlCommand cmd = new SqlCommand(sql, Conn);           cmd.Parameters.AddRange(pars);           int result = Convert.ToInt32(cmd.ExecuteScalar());           CloseConn();           return result;       }       public static bool ExecuteScalarProc(string sql, SqlParameter[] pars)       {           bool IsFind = false;           OpenConn();           SqlCommand cmd = new SqlCommand(sql, Conn);           cmd.CommandType = CommandType.StoredProcedure;           cmd.Parameters.AddRange(pars);           int result = (int)cmd.ExecuteScalar();           if (result > 0)           {               IsFind = true;           }           CloseConn();           return IsFind;       }       public static SqlDataReader ExecuteReader(string sql, SqlParameter[] pars)        {           OpenConn();           SqlCommand cmd = new SqlCommand(sql, Conn);           if (pars != null)            {               cmd.Parameters.AddRange(pars);           }           SqlDataReader reader = cmd.ExecuteReader();           return reader;       }       public static SqlDataReader ExecuteReaderProc(string sql, SqlParameter[] pars)       {           OpenConn();           SqlCommand cmd = new SqlCommand(sql, Conn);           cmd.CommandType = CommandType.StoredProcedure;           if (pars != null)           {               cmd.Parameters.AddRange(pars);           }           SqlDataReader reader = cmd.ExecuteReader();           return reader;       }    }}

web.config配置文件代码：

  <connectionStrings>    <add name="GoldenUnicornConnectionString" connectionString="Data Source=.;Initial Catalog=GoldenUnicorn;Persist Security Info=True;User ID=sa;Password=sa"      providerName="System.Data.SqlClient" />    <!--<add name="BlogConn" connectionString="Data Source=.;Initial Catalog=MyBlogPlus;User ID=sa;Password=sa"/>-->  </connectionStrings>

调用：

public static bool AddUsers(Users user)        {            string sql = @"INSERT INTO [MyBlogPlus].[dbo].[Users] VALUES(@LoginId,@LoginPwd,@Name,@Address,@Phone ,@Mail ,@Gender ,@UserRole)";            SqlParameter[] pmr = new SqlParameter[] {                 new SqlParameter("@LoginId",user.LoginId),                new SqlParameter("@LoginPwd",user.LoginPwd),                new SqlParameter("@Name",user.Name),                new SqlParameter("@Address",user.Address),                new SqlParameter("@Phone",user.Phone),                new SqlParameter("@Mail",user.Mail),                new SqlParameter("@Gender",SqlHelper.ToDbValue(user.Gender)),                new SqlParameter("@UserRole",user.UserRole)            };            int num = SqlHelper.ExecuteNonQuery(sql, pmr);            if (num == 1)            {                return true;            }            else            {                return false;            }        } public static IList<PostCommentsBusiness> GetPostCommentsBusinessByPostId(int postId)        {            IList<PostCommentsBusiness> postCommentList = new List<PostCommentsBusiness>();            string sql = @"SELECT     dbo.PostComments.CommentId, dbo.PostComments.PostId, dbo.PostComments.UserId, dbo.PostComments.CommentDate,                       dbo.PostComments.Comment, dbo.PostComments.Ip, dbo.Users.Name                        FROM         dbo.PostComments INNER JOIN                        dbo.Users ON dbo.PostComments.UserId = dbo.Users.UserId                        WHERE     (dbo.PostComments.PostId =@postId)";            DataTable dt = SqlHelper.ExecuteReader(sql, new SqlParameter("@postId", postId));            foreach (DataRow row in dt.Rows)            {                PostCommentsBusiness postComm = new PostCommentsBusiness();                postComm.CommentId = Convert.ToInt32(row[0]);                postComm.PostId = Convert.ToInt32(row[1]);                postComm.UserId = Convert.ToInt32(SqlHelper.FromDbValue(row[2]));                postComm.CommentDate = Convert.ToDateTime(row[3]);                postComm.Comment = row[4].ToString();                postComm.Ip = row[5].ToString();                postComm.User = new Users();                postComm.User.Name = row[6].ToString();                postCommentList.Add(postComm);            }            return postCommentList;        }

0 0