data.cs

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

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 WebDBManage;
using System.Collections;

/// <summary>
/// Summary description for Data
/// </summary>
public class Data
{
	public void BindData(DataGrid TableDataList,string sTableName)
	{
		///清空数据列表中的所有数据
		TableDataList.DataSource = null;
		TableDataList.DataBind();

		///如果是菜单节点，则返回
		if (sTableName == Tree.MENUNODETEXT) return;

		///设置表名称和排序字段
		String RealTableName = sTableName;
		String OrderByString = "";

		///获取系统表的数据
		SqlServerDB sqlServer = new SqlServerDB();
		DataSet ds = sqlServer.GetSystemTableDS();
		if (ds == null) return;
		if (ds.Tables.Count <= 0) return;

		///获取当前表的所有数据行
		DataRow[] dataRowrList = ds.Tables[0].Select(SystemTable.TABLENAME + "='" + sTableName + "'");

		ArrayList taList = new ArrayList();
		taList.Clear();

		///创建查询数据库的Select语句
		String cmdText = "Select";
		foreach (DataRow row in dataRowrList)
		{
			int nControlTypeID = Int32.Parse(row[SystemTable.CONTROLTYPEID].ToString());
			if (GlobalConst.ONE == row[SystemTable.ISSELECT].ToString())
			{
				if (GlobalConst.ONE == row[SystemTable.ISKEY].ToString())
				{
					OrderByString = row[SystemTable.FIELDCODE].ToString();
				}

				if (nControlTypeID < Int32.Parse(GlobalConst.FOUR)
					|| nControlTypeID > Int32.Parse(GlobalConst.SEVEN))
				{
					cmdText += " [" + RealTableName + "]." + row[SystemTable.FIELDCODE].ToString();
				}
				else
				{
					String tempStr = UpdataSelectSQL(sTableName,row[SystemTable.FIELDCODE].ToString(),taList,row);
					if (tempStr == null) break;
					cmdText += " " + tempStr;
				}
				cmdText += " AS " + row[SystemTable.FIELDDESN].ToString() + ",";
			}
		}
		///如果Select语句为空，则清空DataGrid控件的数据
		if (cmdText == "Select")
		{
			TableDataList.DataSource = null;
			TableDataList.DataBind();
			return;
		}

		cmdText = cmdText.Substring(0,cmdText.Length - 1) + " FROM ["
			+ sTableName + "]";

		///创建SQL语句中的INNER JOIN 子句
		for (int i = 0;i < taList.Count;i++)
		{
			cmdText += " INNER JOIN ["
				+ ((DropdownListTable)taList[i]).SelectTable
				+ "] ON ["
				+ ((DropdownListTable)taList[i]).TableName + "]."
				+ ((DropdownListTable)taList[i]).FieldCode
				+ " = ["
				+ ((DropdownListTable)taList[i]).SelectTable + "]."
				+ ((DropdownListTable)taList[i]).Value;
		}

		///添加排序字段
		if (OrderByString.Trim().Length > 0)
		{
			cmdText += " ORDER BY " + OrderByString;
		}

		///获取数据
		DataSet dssqlServer = sqlServer.GetDataSet(cmdText);
		if (dssqlServer == null) return;
		if (dssqlServer.Tables.Count <= 0) return;

		///根据获取的数据是否包含布尔数据，否则重新构建数据源
		bool isEditDataSet = false;
		foreach (DataColumn column in dssqlServer.Tables[0].Columns)
		{
			if (column.ColumnName.IndexOf("是否") > -1)
			{
				isEditDataSet = true;
				break;
			}
		}

		///绑定控件的数据
		if (isEditDataSet == true)
		{
			DataSet dst = EditDataSet(dssqlServer);
			if (dst == null) return;
			if (dst.Tables.Count <= 0) return;
			TableDataList.DataSource = dst;
		}
		else
		{
			TableDataList.DataSource = dssqlServer;
		}
		TableDataList.DataBind();
	}

	/// <summary>
	/// 清空Panel上的所有控件的值
	/// </summary>
	public void ClearPanelControlValue(Panel panel)
	{
		foreach (System.Web.UI.Control control in panel.Controls)
		{
			if (control is TextBox)
			{
				((TextBox)control).Text = "";
			}
			if (control is DropDownList)
			{
				((DropDownList)control).SelectedIndex = ((DropDownList)control).Items.Count > 0 ? 0 : -1;
			}
		}
	}

	/// <summary>
	/// 更新表的选中行的数据之前，绑定Panel控件的值
	/// </summary>
	public void GetPanelControlData(TableCellCollection cellList,Panel panel,string sTableName)
	{
		if (cellList == null) return;
		if (cellList.Count <= 1) return;

		///在cellList中，第二项表示ID的值，第三项表示Panel的第一个控件的值。
		///因此，如果ID是显示的，那么nBaseCellIndex = 1;否则nBaseCellIndex = 2
		int nBaseCellIndex = 2;
		if (true == TableIDIsVisible(sTableName))   ///表示ID列是否显示
		{
			nBaseCellIndex = 1;
		}

		int nCellIndex = 0;
		foreach (System.Web.UI.Control control in panel.Controls)
		{
			if (control is TextBox)
			{
				((TextBox)control).Text = cellList[nBaseCellIndex + nCellIndex].Text;
				nCellIndex++;
			}
			if (control is DropDownList)
			{
				ASPNET2System.SetListBoxItemByText((DropDownList)control,
					cellList[nBaseCellIndex + nCellIndex].Text);
				nCellIndex++;
			}
		}		
	}

	/// <summary>
	/// 添加数据
	/// </summary>	
	public void AddData(Panel panel,String sTableName)
	{
		ArrayList aValue = new ArrayList();

		String sTableIDCode = GetTableIDCode(sTableName);
		if (sTableIDCode == "" || sTableIDCode == null) return;

		String cmdText = "INSERT INTO [" + sTableName + "](";
		aValue.Clear();
		foreach (System.Web.UI.Control control in panel.Controls)
		{
			if (control is TextBox)
			{
				cmdText += control.ID + ",";
				aValue.Add(((TextBox)control).Text);
			}
			if (control is DropDownList)
			{
				cmdText += control.ID + ",";
				aValue.Add(((DropDownList)control).SelectedValue);
			}
		}
		cmdText = cmdText.Substring(0,cmdText.Length - 1) + ")VALUES(";

		for (int i = 0;i < aValue.Count;i++)
		{
			cmdText += "'" + aValue[i].ToString() + "',";
		}
		cmdText = cmdText.Substring(0,cmdText.Length - 1) + ")";

		SqlServerDB sqlServer = new SqlServerDB();
		sqlServer.ExecuteOperateData(cmdText);

		///初始化Panel上所有控件的数据
		//ClearPanelControlValue(panel);
	}

	/// <summary>
	/// 更新数据
	/// </summary>		
	public void UpdateData(Panel panel,String sTableName,String sTableIDValue)
	{
		String cmdText = "UPDATE [" + sTableName + "] SET ";
		foreach (System.Web.UI.Control control in panel.Controls)
		{
			if (control is TextBox)
			{
				cmdText += control.ID + "='" + ((TextBox)control).Text + "',";
			}
			if (control is DropDownList)
			{
				cmdText += control.ID + "='" + ((DropDownList)control).SelectedValue + "',";
			}
		}
		cmdText = cmdText.Substring(0,cmdText.Length - 1);

		String sTableIDCode = GetTableIDCode(sTableName);
		if (sTableIDCode == "" || sTableIDCode == null) return;
		
		cmdText += " WHERE " + sTableIDCode + "='" + sTableIDValue + "'";

		SqlServerDB sqlServer = new SqlServerDB();
		sqlServer.ExecuteOperateData(cmdText);
	}

	/// <summary>
	/// 删除数据
	/// </summary>
	public void DeleteData(String sTableName,String sTableIDValue)
	{
		String sTableIDCode = GetTableIDCode(sTableName);
		if (sTableIDCode == "" || sTableIDCode == null) return;
		
		String cmdText = "DELETE [" + sTableName + "] WHERE "
			+ sTableIDCode + "='" + sTableIDValue + "'";

		SqlServerDB sqlServer = new SqlServerDB();
		sqlServer.ExecuteOperateData(cmdText);
	}

	/// <summary>
	/// 绑定普通下拉列表控件的数据
	/// </summary>	
	public void BindDropdownListBaseData(DropDownList dlist,DataSet ds,String cmdText)
	{
		if (ds == null) return;
		if (ds.Tables.Count <= 0) return;

		String sTableName = "";
		String sText = "";
		String sValue = "";

		DataRow[] rowList = ds.Tables[0].Select(cmdText);
		if (null != rowList && rowList.Length >= 1)
		{
			sTableName = rowList[0][DropdownListBase.SELECTTABLE].ToString();
			sText = rowList[0][DropdownListBase.TEXT].ToString();
			sValue = rowList[0][DropdownListBase.VALUE].ToString();
		}
		String tempcmdText = "SELECT DISTINCT " + sText + ", " + sValue
			+ " FROM [" + sTableName + "]";

		SqlServerDB sqlServer = new SqlServerDB();
		DataSet dssqlServer = sqlServer.GetDataSet(tempcmdText);
		if (dssqlServer == null) return;
		if (dssqlServer.Tables.Count <= 0) return;

		dlist.DataSource = dssqlServer;
		dlist.DataTextField = sText;
		dlist.DataValueField = sValue;
		dlist.DataBind();		
	}

	/// <summary>
	/// 绑定布尔下拉框的数据
	/// </summary>
	public void BindDropDownListBoolData(DropDownList dlist,DataSet ds)
	{
		if (ds == null) return;
		if (ds.Tables.Count <= 0) return;

		dlist.DataSource = ds;
		dlist.DataTextField = BoolTable.DISPLAYTEXT;
		dlist.DataValueField = BoolTable.BOOLTABLEID;
		dlist.DataBind();
	}	

	/// <summary>
	/// 获取SELECT语句中的JOIN INNER子句的表名称已经关联的字段名称
	/// </summary>		
	private String UpdataSelectSQL(String sTableName,String sColumnName,ArrayList taList,DataRow row)
	{
		DropdownListTable dt = new DropdownListTable();
		dt = GetDropDownListBaseTableName(sTableName,sColumnName);
		if (dt == null) return (null);

		taList.Add(dt);
		return ("[" + dt.SelectTable + "]." + dt.Text);
	}

	/// <summary>
	/// 获取基础DropdownList控件的表的名称
	/// </summary>		
	private DropdownListTable GetDropDownListBaseTableName(String sTableName,String sColumnName)
	{
		SqlServerDB sqlServer = new SqlServerDB();
		DataSet ds = sqlServer.GetDropDownListDSByBase();
		if (ds == null) return (null);
		if (ds.Tables.Count <= 0) return (null);

		string cmdText = DropdownListBase.TABLENAME + "='" + sTableName
			+ "' AND " + DropdownListBase.FIELDCODE + "='" + sColumnName + "'";
		DataRow[] rowList = ds.Tables[0].Select(cmdText);

		DropdownListTable downList = new DropdownListTable();
		if (rowList.Length > 0)
		{
			downList.TableName = rowList[0][DropdownListBase.TABLENAME].ToString();
			downList.FieldCode = rowList[0][DropdownListBase.FIELDCODE].ToString();
			downList.SelectTable = rowList[0][DropdownListBase.SELECTTABLE].ToString();
			downList.Text = rowList[0][DropdownListBase.TEXT].ToString();
			downList.Value = rowList[0][DropdownListBase.VALUE].ToString();
			return (downList);
		}

		return ((DropdownListTable)null);
	}

	/// <summary>
	/// 获取子DropdownList控件的表的名称
	/// </summary>	
	private String GetDropDownListChildTableName(String sTableName,String sColumnName)
	{
		SqlServerDB sqlServer = new SqlServerDB();
		DataSet ds = sqlServer.GetDropDownListDSByBase();
		if (ds == null) return (null);
		if (ds.Tables.Count <= 0) return (null);

		DataRow[] rowList = ds.Tables[0].Select(DropdownListChild.TABLENAME + "='" + sTableName
			+ "' AND " + DropdownListChild.FIELDCODE + "='" + sColumnName + "'");

		if (rowList.Length > 0)
		{
			return (rowList[0][DropdownListChild.SELECTTABLE].ToString() + "." + rowList[0][DropdownListChild.TEXT].ToString());
		}
		return (null);
	}

	/// <summary>
	/// 重新构建DataSet，主要是改变表中的布尔值
	/// </summary>
	private DataSet EditDataSet(DataSet ds)
	{
		if (ds == null) return ((DataSet)null);
		if (ds.Tables.Count <= 0) return ((DataSet)null);

		DataTable dt = new DataTable(ds.Tables[0].TableName);
		foreach (DataColumn colunm in ds.Tables[0].Columns)
		{
			DataColumn newcolunm = dt.Columns.Add(colunm.ColumnName);
		}

		foreach (DataRow row in ds.Tables[0].Rows)
		{
			DataRow newrow = dt.NewRow();
			foreach (DataColumn colunm in dt.Columns)
			{
				if (colunm.ColumnName.IndexOf("是否") == -1)
				{
					newrow[colunm.ColumnName] = row[colunm.ColumnName].ToString();
				}
				else
				{
					newrow[colunm.ColumnName] = row[colunm.ColumnName].ToString().Replace("1","是").Replace("0","否");
				}
			}
			dt.Rows.Add(newrow);
		}

		DataSet newds = new DataSet();
		newds.Tables.Add(dt);
		return (newds);
	}

	/// <summary>
	/// 获取表的主键字段的代码
	/// </summary>		
	private String GetTableIDCode(String sTableName)
	{
		SqlServerDB sqlServer = new SqlServerDB();

		String sFieldCodeID = "";
		DataSet ds = sqlServer.GetSystemTableDS();
		if (ds == null) return (null);
		if (ds.Tables.Count <= 0) return (null);

		foreach (DataRow row in ds.Tables[0].Rows)
		{
			if (sTableName == row[SystemTable.TABLENAME].ToString() && GlobalConst.ONE == row[SystemTable.ORDERBY].ToString())
			{
				sFieldCodeID = row[SystemTable.FIELDCODE].ToString();
				break;
			}
		}
		return (sFieldCodeID);
	}

	/// <summary>
	/// 获取表的主键字段是否显示
	/// </summary>		
	private bool TableIDIsVisible(String sTableName)
	{
		SqlServerDB sqlServer = new SqlServerDB();

		bool isVisible = false;
		DataSet ds = sqlServer.GetSystemTableDS();
		if (ds == null) return ((false));
		if (ds.Tables.Count <= 0) return (false);

		foreach (DataRow row in ds.Tables[0].Rows)
		{
			if (sTableName == row[SystemTable.TABLENAME].ToString() && GlobalConst.ONE == row[SystemTable.ISKEY].ToString())
			{
				isVisible = row[SystemTable.ISVISIBLE].ToString() == GlobalConst.ONE ? true : false;
				break;
			}
		}
		return (isVisible);
	}
}