dboperate.cs

勇敢者论坛全部源代码。 支持SQL2000/Access数据库

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Data.OleDb;
using System.Configuration;

namespace doughty_cn
{
	/// <summary>
	/// DBOperate 的摘要说明。
	/// </summary>
	public class DBOperate
	{
        public DBOperate()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}

		//输入SELECT语句，获得返回数据集
		public static DataTable SelectFormForum(string sql,int start,int size,string tablename)
		{
			try
			{		
                //读数据库类型
                string DBtype = ConfigurationManager.AppSettings["DBtype"];
                if (DBtype == "Accsee")
                {
                    //Accsee 数据库查询操作
                    DBsql exsql = new DBsql();
                    exsql.Open();
                    OleDbDataAdapter da = new OleDbDataAdapter(sql, exsql.con);
                    DataSet ds = new DataSet();
                    da.Fill(ds, start, size, tablename);
                    exsql.Close();
                    return ds.Tables[0];
                }
                else
                {
                    //sql server 数据库查询操作
                    SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["DBConnection"]);
                    SqlDataAdapter sqlAdapter1 = new SqlDataAdapter(sql, sqlConnection);
                    DataSet returns = new DataSet();
                    sqlAdapter1.Fill(returns, start, size, tablename);
                    return returns.Tables[0];
                }
			}
			catch (Exception ex)
			{
				throw (ex);
			}
		}

		public static DataTable SelectFormForum(string sql)
		{
            try
            {
                string DBtype = ConfigurationManager.AppSettings["DBtype"];
                if (DBtype == "Accsee")
                {
                    DBsql exsql = new DBsql();
                    exsql.Open();
                    OleDbDataAdapter da = new OleDbDataAdapter(sql, exsql.con);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    exsql.Close();
                    return ds.Tables[0];
                }
                else
                {
                    SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["DBConnection"]);
                    SqlDataAdapter sqlAdapter1 = new SqlDataAdapter(sql, sqlConnection);
                    DataSet returns = new DataSet();
                    sqlAdapter1.Fill(returns);
                    return returns.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
		}

        //返回一条记录
		public static DataRow GetDataRow(string sql,string TableName,string dbname) 
		{
			try
			{
                string DBtype = ConfigurationManager.AppSettings["DBtype"];
                if (DBtype == "Accsee")
                {
                    DBsql exsql = new DBsql();
                    exsql.Open();
                    OleDbDataAdapter da = new OleDbDataAdapter(sql, exsql.con);
                    DataSet ds = new DataSet();
                    da.Fill(ds, TableName);
                    exsql.Close();
                    return ds.Tables[0].Rows[0];
                }
                else
                {
                    SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings[dbname]);
                    SqlDataAdapter sqlAdapter1 = new SqlDataAdapter(sql, sqlConnection);
                    DataSet product = new DataSet();
                    sqlAdapter1.Fill(product, TableName);
                    return product.Tables[0].Rows[0];
                }
			}
			catch (Exception ex)
			{
				throw (ex);
			}	
		}

		//执行SQL语句，如UPDATE，INSERT等
        public static void ExcSQL(string sql)
        {
            string DBtype = ConfigurationManager.AppSettings["DBtype"];
            if (DBtype == "Accsee")
            {
                DBsql exsql = new DBsql();
                try
                {
                    exsql.Open();
                    OleDbCommand comcol = new OleDbCommand(sql, exsql.con);
                    comcol.ExecuteNonQuery();
                    exsql.Close();
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    exsql.Close();
                }
            }
            else
            {
                SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["DBConnection"]);
                try
                {
                    SqlCommand sqlCmd = new SqlCommand(sql, sqlConnection);
                    sqlConnection.Open();
                    sqlCmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

		//获取用户短消息条数
		public static int ShortMsgCount(string UserName)
		{
			try
			{
				string s="SELECT COUNT(*) AS cun FROM ShortMessage WHERE (systemmsg = 0) AND (Readed = 0) AND (UserName = '"+UserName+"')";
				DataRow dr=GetDataRow(s,"ShortMessage","DBConnection");
				return System.Convert.ToInt32(dr["cun"].ToString());
			}
			catch
			{
				return 0;
			}
		}

        //计算总共有多少条问题
        public static int CalculateQRecord(int BID, int SID, int solved)
        {
            try
            {
                string s = "SELECT COUNT(*) AS co FROM Questions WHERE (BigClass_ID = " + BID + ") AND (SmallClass_ID = " + SID + ") AND (kind > 0) AND (kind < 5) AND (solved = " + solved + ")";
                DataRow dr = GetDataRow(s, "Questions", "DBConnection");
                return System.Convert.ToInt32(dr["co"].ToString());
            }
            catch
            {
                return 0;
            }
        }

        //查询帖子级别
        public static int GetQKind(int QID)
        {
            try
            {
                string s = "SELECT kind FROM Questions WHERE Q_ID = " + QID;
                DataRow dr = GetDataRow(s, "Questions", "DBConnection");
                return System.Convert.ToInt32(dr["kind"].ToString());
            }
            catch
            {
                return 0;
            }
        }

		//计算总共有多少条回复
		public static int CalculateARecord(int QID)
		{
			try
			{
				string s="SELECT COUNT(*) AS co FROM Answers WHERE (Question_ID = "+QID+")";
				DataRow dr=GetDataRow(s,"Answers","DBConnection");
				return System.Convert.ToInt32(dr["co"].ToString());
			}
			catch
			{
				return 0;
			}
		}

        //检查用户是否存在
        public static int CalculateURecord(string User_Name, string Password)
        {
            try
            {
                string s = "SELECT COUNT(*) AS co FROM Users WHERE (User_Name ='" + User_Name + "') AND ([Password] = '" + Password + "')";
                DataRow dr = GetDataRow(s, "Users", "DBConnection");
                return System.Convert.ToInt32(dr["co"].ToString());
            }
            catch
            {
                return 0;
            }
        }

        //获取用户级别
        public static string GetUserGrade(string UserName)
        {
            try
            {
                if (UserName == "")
                    return "过客";
                else
                {
                    string s = "SELECT (SELECT UserGrade FROM UserGrade WHERE pkid = user_grade) AS UG, User_Grade FROM Users WHERE (User_Name ='"
                        + UserName + "')";
                    DataRow dr = GetDataRow(s, "ShortMessage", "DBConnection");
                    return dr["UG"].ToString();
                }
            }
            catch
            {
                return null;
            }
        }
        //获取用户管理级别
        public static string GetUserPopedom(string UserName)
        {
            try
            {
                if (UserName == "")
                    return "过客";
                else
                {
                    string s = "SELECT (SELECT UserGrade FROM UserGrade WHERE pkid = user_grade) AS UG, User_Grade FROM Users WHERE (User_Name ='"
                        + UserName + "')";
                    DataRow dr = GetDataRow(s, "ShortMessage", "DBConnection");
                    return dr["UG"].ToString();
                }
            }
            catch
            {
                return null;
            }
        }
	}
}