database.cs

来自「《精通ASP.NET2.0网络应用系统开发》书中的源码」· CS 代码 · 共 305 行

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;

/// <summary>
/// Summary description for Database
/// </summary>
namespace WebDBManage
{	
	public class SqlInformation
	{
		/// 定义查询数据库的SQL语句		
		public static Hashtable staticCache = Hashtable.Synchronized(new Hashtable());
		public static readonly string GETSYSTEMTABLES        = "SELECT DISTINCT SystemTableID,TableName FROM SystemTable";
		public static readonly string GETCOMBOBOXBASES       = "SELECT * FROM DropdownListBase";
		public static readonly string GETCOMBOBOXCHILDS      = "SELECT * FROM DropdownListChild";
		public static readonly string GETSYSTEMTABLESBYORDER = "SELECT * FROM SystemTable ORDER BY OrderBy";
		public static readonly string GETBOOLTABLE           = "SELECT * FROM BoolTable ORDER BY BoolTableID";

		/// <summary>
		/// 缓存数据到Cache中
		/// </summary>
		/// <param name="cacheKey"></param>
		/// <param name="cmdParms"></param>
		public static void CacheData(string cacheKey,DataSet ds)
		{
			staticCache[cacheKey] = ds;
		}

		public static string SQLCONNECTIONSTRING = ConfigurationSettings.AppSettings["SQLCONNECTIONSTRING"];
		//public static string SQLCONNECTIONSTRING = @"data source=zhengyd;uid=sa;pwd=dorient;database=WebDynamicDB";
		public static SqlConnection _sqlConnection;
	}

	/// <summary>
	/// 定义类封装表的属性
	/// </summary>
	public class TableProperty
	{
		public String TableName;
		public String TableCode;
		public String TableFCode;
	}

	public class DropdownListTable
	{
		public String TableName;
		public String FieldCode;
		public String SelectTable;
		public String Text;
		public String Value;
	}


	public class GlobalConst
	{
		public static string ONE = "1";
		public static string ZERO = "0";
		public static string FOUR = "4";
		public static string SEVEN = "7";
	}

	public class TableTree
	{
		public static string TABLTREE = "TableTree";
		public static string TABLETREEID = "TableTreeID";
		public static string DESN = "Desn";
		public static string FATHERID = "FatherID";
		public static string ORDERBY = "OrderBy";
		public static string TABLENAME = "TableName";
	}

	public class SystemTable
	{
		public static string SYSTEMTABLE = "SystemTable";
		public static string SYSTEMTABLEID = "SystemTableID";
		public static string TABLEDESN = "TableDesn";
		public static string TABLENAME = "TableName";
		public static string FIELDCODE = "FieldCode";
		public static string FIELDDESN = "FieldDesn";
		public static string ISKEY = "IsKey";
		public static string ISSELECT = "IsSelect";
		public static string ISVISIBLE = "IsVisible";
		public static string WIDTH = "Width";
		public static string ORDERBY = "OrderBy";
		public static string CONTROLTYPEID = "ControlTypeID";
	}

	public class ControlType
	{
		public static string CONTROLTYPE = "ControlTypes";
		public static string CONTROLTYPEID = "ControlTypeID";
		public static string DESN = "Desn";
	}

	public class BoolTable
	{
		public static string BOOLTABLE = "BoolTable";
		public static string BOOLTABLEID = "BoolTableID";
		public static string DISPLAYTEXT = "DisplayText";
	}

	public class DropdownListBase
	{
		public static string COMBOBOXBASE = "DropdownListBase";
		public static string DROPDOWNLISTBASEID = "DropdownListBaseID";
		public static string TABLENAME = "TableName";
		public static string FIELDCODE = "FieldCode";
		public static string SELECTTABLE = "SelectTable";
		public static string TEXT = "Text";
		public static string VALUE = "Value";
	}

	public class DropdownListChild
	{
		public static string COMBOBOXCHILD = "DropDownListChild";
		public static string DROPDOWNLISTCHILDID = "DropdownListChildID";
		public static string TABLENAME = "TableName";
		public static string FIELDCODE = "FieldCode";
		public static string SELECTTABLE = "SelectTable";
		public static string TEXT = "Text";
		public static string VALUE = "Value";
		public static string FATHERTABLENAME = "FatherTableName";
		public static string FATHERVALUE = "FatherValue";
	}

	/// <summary>
	/// Summary description for SqlDB.
	/// </summary>	
	public class SqlServerDB
	{
		/// <summary>
		/// 创建访问数据库的连接
		/// </summary>
		public static SqlConnection OpenConnection()
		{
			SqlConnection myConnection = new SqlConnection(SqlInformation.SQLCONNECTIONSTRING);
			return (myConnection);
		}


		/// /// <summary>
		/// 根据SQL语句从数据库中获取DataSet
		/// </summary>
		public DataSet GetDataSet(String cmdText)
		{
			SqlConnection sqlcon = OpenConnection();
			SqlDataAdapter oda = new SqlDataAdapter(cmdText,sqlcon);
			DataSet ds = new DataSet();

			///判定数据库的链接状态
			if(sqlcon.State == ConnectionState.Closed)
			{
				sqlcon.Open();
			}
			///填充数据集
			oda.Fill(ds);

			///返回数据集
			return (ds);
		}
	

		/// <summary>
		/// 根据SQL语句从数据库中获取DropDownListBase的数据
		/// </summary>
		public DataSet GetDropDownListDSByBase()
		{
			DataSet ds = new DataSet();
			ds = (DataSet)SqlInformation.staticCache["StaticDropDownListBase"];
			if (null == ds)
			{
				ds = new DataSet();
				SqlConnection sqlcon = OpenConnection();
				String cmdText = SqlInformation.GETCOMBOBOXBASES;
				SqlDataAdapter oda = new SqlDataAdapter(cmdText,sqlcon);

				///判定数据库的链接状态
				if (sqlcon.State == ConnectionState.Closed)
				{
					sqlcon.Open();
				}
				///填充数据集
				oda.Fill(ds);

				///缓存数据集中的数据
				SqlInformation.CacheData("StaticDropDownListBase",ds);
			}

			///返回数据集
			return (ds);
		}

		/// <summary>
		/// 根据SQL语句从数据库中获取DropdownlistChild表的数据
		/// </summary>
		public DataSet GetDropDownListDSByChild()
		{
			DataSet ds = new DataSet();
			ds = (DataSet)SqlInformation.staticCache["StaticDropDownListChild"];
			if (null == ds)
			{
				ds = new DataSet();
				SqlConnection sqlcon = OpenConnection();
				String cmdText = SqlInformation.GETCOMBOBOXCHILDS;
				SqlDataAdapter oda = new SqlDataAdapter(cmdText,sqlcon);

				///判定数据库的链接状态
				if (sqlcon.State == ConnectionState.Closed)
				{
					sqlcon.Open();
				}
				///填充数据集
				oda.Fill(ds);

				///缓存数据集中的数据
				SqlInformation.CacheData("StaticComboBoxChild",ds);
			}

			return (ds);
		}

		/// <summary>
		/// 根据SQL语句从数据库中获取SYSTEMTABLE表的数据
		/// </summary>
		public DataSet GetSystemTableDS()
		{
			DataSet ds = new DataSet();
			ds = (DataSet)SqlInformation.staticCache["StaticSystemTable"];
			if (null == ds)
			{
				ds = new DataSet();
				SqlConnection sqlcon = OpenConnection();
				String cmdText = SqlInformation.GETSYSTEMTABLESBYORDER;
				SqlDataAdapter oda = new SqlDataAdapter(cmdText,sqlcon);

				///判定数据库的链接状态
				if (sqlcon.State == ConnectionState.Closed)
				{
					sqlcon.Open();
				}
				///填充数据集
				oda.Fill(ds);

				///缓存数据集中的数据
				SqlInformation.CacheData("StaticSystemTable",ds);
			}

			return (ds);
		}

		/// <summary>
		/// 根据SQL语句从数据库中获取BOOLTABLE表的数据
		/// </summary>
		public DataSet GetBoolTableDS()
		{
			DataSet ds = new DataSet();
			SqlConnection sqlcon = OpenConnection();
			String cmdText = SqlInformation.GETBOOLTABLE;
			SqlDataAdapter oda = new SqlDataAdapter(cmdText,sqlcon);

			///判定数据库的链接状态
			if (sqlcon.State == ConnectionState.Closed)
			{
				sqlcon.Open();
			}
			///填充数据集
			oda.Fill(ds);

			///返回数据集
			return (ds);
		}

		/// <summary>
		/// 根据给定的SQL语句执行数据库操作
		/// </summary>
		public void ExecuteOperateData(String cmdText)
		{
			SqlConnection myConnection = OpenConnection();
			SqlCommand myCommand = new SqlCommand(cmdText,myConnection);

			///判定数据库的链接状态
			if (myConnection.State == ConnectionState.Closed)
			{
				myConnection.Open();
			}
			///执行数据库的操作
			myCommand.ExecuteNonQuery();

			if (myConnection.State == ConnectionState.Open)
			{
				myConnection.Close();
			}
		}
	}
}