DBHelper类及数据库连接问题？

Question

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
using System.Configuration;
using Cp.Processing;
namespace Cp.DAL
{
    /// <summary>
    /// DBhelper 类
    /// </summary>
    public class DBHelper
    {
        public static string connString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;        ///// <summary>
        ///// 工具生成的
        ///// </summary>
        ///// <returns></returns>
        //public static SqlConnection CreateConn()
        //{
        //    return new SqlConnection(connString);
        //}        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="conn"></param>
        public static void OpenConn(SqlConnection conn)
        {
            if (conn != null)
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
            }
        }        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="conn"></param>
        public static void CloseConn(SqlConnection conn)
        {
            if (conn != null)
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }        }
        private static SqlConnection connection;
        /// <summary>
        /// 获取数据库连接(Connection)
        /// </summary>
        public static SqlConnection Connection
        {
            get
            {
                string connectionString = connString;
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }
        /// <summary>
        /// 执行 T-SQL 语句，并返回所受影响的行数-----AAA
        /// </summary>
        /// <param name="safeSql">Sql语句</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecuteCommand(string safeSql)
        {            SqlConnection conn = Connection;
            try
            {
                SqlCommand cmd = new SqlCommand(safeSql, conn);
                //等待时间
                cmd.CommandTimeout = 30;
                int num = cmd.ExecuteNonQuery();
                CloseConn(conn); //关闭数据库连接
                return num;
            }
            catch (Exception e)
            {
                CloseConn(conn);
                return 0;
            }
        }
        /// <summary>
        /// 返回 SqlDataReader 的对象
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string safeSql)
        {
            SqlConnection conn = Connection;
            try
            {
                SqlCommand cmd = new SqlCommand(safeSql.Trim(), conn);
                //等待时间
                cmd.CommandTimeout = 30;
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch (Exception e)
            {
                CloseConn(conn);
                return null;
            }
        }
        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="paramers"></param>
        /// <returns></returns>
        public static Object Execute(string cmdText, params SqlParameter[] paramers)
        {
            SqlConnection conn = Connection;
            try
            {
                SqlCommand cmd = CreateCMD(cmdText, paramers, conn);
                //等待时间
                cmd.CommandTimeout = 30;
                int num = cmd.ExecuteNonQuery();
                CloseConn(conn); //关闭数据库连接
                return num;
            }
            catch (Exception e)
            {
                CloseConn(conn);
                return 0;
            }
        }
        /// <summary>
        /// 查询 的sql 或者 存储过程
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="paramers"></param>
        /// <returns></returns>
        public static Object M_Execute(string cmdText, params SqlParameter[] paramers)
        {
            SqlConnection conn = Connection;
            try
            {
                SqlCommand cmd = CreateCMD(cmdText, paramers, conn);
                //等待时间
                cmd.CommandTimeout = 30;
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception e)
            {
                CloseConn(conn);
                return new object();
            }        }
        //上面的调用
        private static SqlCommand CreateCMD(string cmdText, SqlParameter[] paramers, SqlConnection conn)
        {
            SqlCommand cmd = new SqlCommand(cmdText, conn);
            if (paramers != null)
                cmd.Parameters.AddRange(paramers);
            OpenConn(conn);
            return cmd;
        }
    }
}
读取时候调用如下：using (SqlDataReader sdr = (SqlDataReader)DBHelper.M_Execute(cmdText))
            {
                while (sdr.Read())
                {
                    UserType userType = new UserType();
                    userType.id = (Convert.ToInt32(sdr[0]));
                    userType.name = (Convert.ToString(sdr[1]));
                    userTypes.Add(userType);
                    ........
                }
                sdr.Dispose();
            }
有时候根据用户Id，用户信息 明明存在，确查询不出数据。 （数据库不在本地）

Answer 1

UserInfo userInfo = (UserInfo)Session["User"];
UserInfo userInfoTwo = UserInfoManager.M_SelectUserInfoById(userInfo.ID);
执行语句： SELECT * from userInfo wehre Id=1
但是有时候获取不到竟然数据，这条数据数据库中肯定存在的

Answer 2

在Web.config节点<connectionStrings>中添加 <configuration> 
    <connectionStrings> 
    <add name="ConnectionServer" connectionString="Data Source=192.168.133.30（服务器地址）;Initial Catalog=ShuiDian（数据库名称）;Persist Security Info=True;User ID=sa(用户名);Password=（密码）;"/> 
   </connectionStrings> 
</configuration> 
字符串要对 在查查你的参数ID正确不正确。

Answer 3

string cmdText = "SELECT UserInfo.id,UserInfo.LoginName,UserInfo.LoginPwd,UserInfo.TypeId,UserInfo.BankUserName,UserInfo.BankNum,UserInfo.QQ,UserInfo.PhoneNum";
         
            cmdText += " FROM where UserInfo.id =" + Id;

Answer 4

string cmdText = "SELECT UserInfo.id,UserInfo.LoginName,UserInfo.LoginPwd,UserInfo.TypeId,UserInfo.BankUserName,UserInfo.BankNum,UserInfo.QQ,UserInfo.PhoneNum";
    
  cmdText += " FROM userinfo where UserInfo.id =" + Id;

Answer 5

断点看一下拼接的sql语句有没有语法问题
没有的话放到sql查询器内查一下是不是你想要的数据

Answer 6

你是用Ilist来查的吧那你代码应该这样写哎
public static Ilist<UserType> getAll()
{
Ilist<UserType> userTypes =new List<UserType>();
string cmdTest="";
using (SqlDataReader sdr = (SqlDataReader)DBHelper.M_Execute(cmdText))
            {
                while (sdr.Read())
                {
                    UserType userType = new UserType();
                    userType.id = (Convert.ToInt32(sdr[0]));
                    userType.name = (Convert.ToString(sdr[1]));
                    userTypes.Add(userType);
                 
                }
             return userTypes;
                sdr.Dispose();
            }} 

Answer 7

哈哈，你没return 把，呵呵，问题出来了吧！

Answer 8

 public static IList<UserInfo> M_SelectUserInfoByCmdText(String cmdText)
        {
            IList<UserInfo> userInfos = new List<UserInfo>();
            using (SqlDataReader sdr = DBHelper.ExecuteReader(cmdText))
            {
                while (sdr.Read())
                {
                    UserInfo userInfo = new UserInfo();
                    userInfo.ID = (Convert.ToInt32(sdr[0]));
                    userInfo.LoginName = (Convert.ToString(sdr[1]));
                    userInfo.LoginPwd = (Convert.ToString(sdr[2]));
                    .....
                }
                sdr.Dispose();
            }
            return userInfos;
        }