ttyupkdata.cs

利用AJAX实现目录树

using System;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.InteropServices;
using System.Text;
using System.Web.UI.WebControls;
namespace Ttyu
{
	//定义一个接口，并给接口添加一个GUID
	
	public class ttyuPKData
	{
		public ttyu.DB7.DataAccess DB=new ttyu.DB7.DataAccess ();

		#region 数据访问基本操作
		public ttyuPKData()
		{
			GetConnectionString();
		}
		
		private string  GetConnectionString()
		{
			//DB.ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["DBConnString"];
			DB.ConnectionString="server=(local);uid=sa;pwd=sa;database=ttyuPK;" ;

			DB.DatabaseType=0;//System.Convert.ToInt16( System.Configuration.ConfigurationSettings.AppSettings["DatabaseType"]);

			DB.ProductRunType ="web";
			return DB.ConnectionString;
		}
		// 由表名取得数据集
		private DataSet GetDataSetByTableName(string TableName)
		{
			GetConnectionString();
			return DB.GetDataSet(TableName);
		}

		// 由查询语句取得数据集
		private DataSet GetDataSetBySql(string Sql)
		{
			GetConnectionString();
			return DB.GetDataSetBySql(Sql);
		}
		// 由查询语句取得数据集
		private bool ExecuteSQL(string Sql)
		{
			GetConnectionString();
			return DB.ExecuteSQL(Sql);
		}

		private bool Delete(string TableName,string Filter)
		{
			GetConnectionString();
			return DB.Delete (TableName,Filter);
		}
		#endregion

		#region 年级

				//得到年级
				public DataSet GetInfoGrade(string GradeName)
				{
					GetConnectionString();
					string Sql="select * from tInfoGrade ";
					if(GradeName!="")
					Sql+=" where 年级名称 like '%"+GradeName+"%'";
					return DB.GetDataSetBySql(Sql);
				}

				//得到年级
				public DataSet GetInfoGradeByCode(string GradeCode)
				{
					GetConnectionString();
					string Sql="select * from tInfoGrade ";
					if(GradeCode!="")
						Sql+=" where 年级编号 like '%"+GradeCode+"%'";
					return DB.GetDataSetBySql(Sql);
				}
				
		#endregion

	
	

    //得到某年级的班级数据。GradeCode为空时表示所有的
 		public DataSet GetAllClassByGradeCode(string GradeCode) 
		{//DB.
			string Sql;
			if(GradeCode=="")
			     Sql="SELECT B.年级名称,A.*  FROM tInfoClass A ,tInfoGrade B  WHERE A.年级ID = B.ID ORDER BY A.年级ID, A.班级编号";
			else
				 Sql="SELECT B.年级名称,A.*  FROM tInfoClass A ,tInfoGrade B  WHERE A.年级ID = B.ID and B.ID = "+GradeCode+" ORDER BY A.年级ID, A.班级编号";

			return DB.GetDataSetBySql(Sql);
		}
       //生成年级下的班级树XML格式字符串
		public  string getClassTree() 
		{
			System.Text.StringBuilder ret = new System.Text.StringBuilder();
			ret.Append("<?xml version='1.0' encoding='gb2312'?><tree id='0'>");

			ret.Append(" <item  child='1' text='年级' id='R_1' >");

			DataSet rs=GetInfoGrade("");
			DataTable dt=rs.Tables [0];
			for(int i=0;i<dt.Rows.Count;i++ )
			{
				string GradeCode = dt.Rows [i]["ID"].ToString ();
				string GradeName = dt.Rows [i]["年级名称"].ToString ();
				
				if (GradeCode == null) 
				{
					GradeCode = " ";
				}

				if (GradeName == null) 
				{
					GradeName = "新年级";
				}
				DataSet rs1=GetAllClassByGradeCode(GradeCode);
				DataTable dt1=rs1.Tables [0];
				int childCount = dt1.Rows.Count;
				if (childCount > 0) 
				{
					childCount = 1;
				}
				GradeCode = "G_"+GradeCode;
				ret.Append("<item child='" + childCount + "' text='" + GradeName +
					"' id='" +GradeCode+"'>");
				for(int j=0;j<dt1.Rows.Count;j++ )
				{
					string childClassCode = "C_"+dt1.Rows [j]["ID"].ToString ();
					string childClassName = dt1.Rows [j]["班级名称"].ToString ();
					ret.Append("<item child='0' text='" + childClassName +
						"' id='" +childClassCode + "'>");
					ret.Append("</item>");
				}
				ret.Append("</item>");
			}
			ret.Append("</item>");
			ret.Append("</tree>");
			return ret.ToString();
		}

		//生成年级下的班级树XML格式字符串
		public  void FillDataSet(DataSet ds) 
		{
			DataSet ds1=new DataSet ();
			System.Data.SqlClient.SqlDataAdapter da=new  System.Data.SqlClient.SqlDataAdapter();
			da=DB.GetSqlDataAdapter("select * from tInfoClass");
			da.Fill (ds,"tInfoClass");
		}
		//把数据行转化为HtmlTable的行的innerHTML
		public string GetRowOuterHTML(DataRow dr)
		{	
			StringBuilder ret = new StringBuilder();
			ret.Append("<tr  class='DataGridItemStyle'>" );
			ret.Append("<td width='5'> <input type='checkbox' onclick='CheckMe(this)'></td> ");
			foreach(DataColumn dc in dr.Table.Columns )
			{ 
				if(dc.Ordinal==2)
				{
					//string ID=dr[0].ToString ();
					ret.Append("<td class='OpenAddWebCell' onclick='OpenEditWeb(this)' title ='单击打开更新窗口'>"+dr[dc].ToString ()+"</td>");	
				}
				else
					if(dc.Ordinal==0)
					ret.Append("<td class='IDColumn'>"+dr[dc].ToString ()+"</td>");
				else
					ret.Append("<td >"+dr[dc].ToString ()+"</td>");	
					
			}
			ret.Append("</tr>");
			return ret.ToString ();
		}
//由一个数据库表得到该表的所有数据，以XML格式字符串返回
		public string GetXmlDataRows(string TableName)
		{
			GetConnectionString();
			SqlDataReader myReader= DB.GetSqlDataReader(TableName);

			StringBuilder ret = new StringBuilder();
			ret.Append("<?xml version='1.0' encoding='gb2312'?><Rows>");
			while (myReader.Read ())
			{
				string Row="";
				for(int i=0;i< myReader.FieldCount;i++)
				{
					string FieldName=myReader.GetName (i).ToString();
					string FieldData="";
					string FieldType=myReader.GetFieldType(i).Name;
					if(!myReader.IsDBNull(i))
					{
						switch(FieldType)
						{
							case "Int16":
								FieldData = myReader.GetInt16(i).ToString ();
								break;
							case "Int32":
								FieldData = myReader.GetInt32(i).ToString ();
								break;
							case "String":
								FieldData = myReader.GetString(i);
								break;
							case "DateTime":
								FieldData = myReader.GetDateTime(i).ToString();
								break;
						}
					}
					if (FieldData == null) 
					{
						FieldData = "";
					}
					string Column="<"+FieldName+"><![CDATA[" + FieldData + "]]></"+FieldName+">";
					Row+=Column;
				}

				string strRet =
					"<Row>" +
					Row+
					"</Row>";
				ret.Append(strRet);
			}	
			ret.Append("</Rows>");

			myReader.Close();
			return ret.ToString ();
		}
	

		//由一个数据视图得到该表的表头及所有数据，以XML格式的表字符串返回
		public string GetTableOuterHTML(DataView dv)
		{
			StringBuilder ret = new StringBuilder();
			ret.Append("<table class='DataGrid' id='DataGrid1' cellspacing=1 cellpadding=4>"  );

			ret.Append("<tr class='DataGridHeaderStyle'>");
			
			ret.Append("<td width='5'> <input type='checkbox' onclick='SelectAll(this)'></td> ");
			//标题
			foreach(System.Data.DataColumn dc in dv.Table.Columns )
			{ 
				if(dc.Ordinal==0) //该列隐藏
				    ret.Append("<td class='IDColumn'>"+dc.ColumnName+"</td>");
				else
					ret.Append("<td>"+dc.ColumnName+"</td>");
			 }
			ret.Append("</tr>");
			//这时是视图中筛选后的数据
			//foreach(DataRow dr in dv.FindRows().Table.Rows)
			foreach(DataRowView drv in dv)
//			for(int i=0;i<dv.Count ;i++)			
			{ 
				DataRow dr=drv.Row;//dv.Table.Rows[i];
				//ret.Append("<tr  class='DataGridItemStyle'>" );
				ret.Append(GetRowOuterHTML(dr));
				//ret.Append("</tr>");
			}
			ret.Append("</table>");
			return ret.ToString ();
		}

		//插入数据行
		public bool InsertDataRow(DataRow dr,int  BeginColumnIndex)
		{
			try
			{
				string strInsertSQL, TypeName;
				string TableName=dr.Table.TableName;
				string ColumnNames="";
				string Values="";
				int len;
				len = dr.ItemArray.Length ;
				for(int i=BeginColumnIndex;i<len;i++)
				{
					string ColumnName=dr.Table.Columns[i].ColumnName;
					object DataValue=dr[i];
					TypeName = dr[i].GetType().Name;
					if(DataValue==null) 
						Values = Values + "null,";
					ColumnNames = ColumnNames + ColumnName+"," ;
					switch(TypeName)
					{
						case "String":
							 Values = Values + "'" +DataValue.ToString () + "',";
							break;
						case "DateTime":
							//Values = Values + "'" + System.Convert.ToDateTime (Value[i]).ToShortDateString() + "',";
							Values = Values + "'" +System.Convert.ToDateTime (DataValue).ToShortDateString() + "',";
							break;
						case "Int16":
						case "Int32":
						case "Int64":
						case "Decimal":
						case "Double":
							Values = Values +  DataValue.ToString () + ",";
							break;
						case "DBNull":
							Values = Values + "null,";
							break;
					}
				}
				ColumnNames = ColumnNames.Substring(0, ColumnNames.Length - 1);
				Values = Values.Substring(0, Values.Length - 1);
				strInsertSQL = "insert into "+TableName +"(" + ColumnNames+") VALUES( " + Values + ")";
				return DB.ExecuteSQL(strInsertSQL);
			}
			catch(Exception ex)
			{
				//MessageBox.Show (ex.ToString ());
				return false;
			}
		}
		////从查询语句中取出表名（单表）
		public bool UpdateDataRow(DataRow dr,int BeginColumnIndex,string Filter)
		{
			try
			{
				string strSql, TypeName,ColumnName,Value,Values;
				string TableName=dr.Table.TableName;
				int  len;
				Values = "";
				len = dr.ItemArray.Length ;
				for(int i=BeginColumnIndex;i<len;i++)
				{
					ColumnName=dr.Table.Columns[i].ColumnName;
					Value=dr[i].ToString ();
					TypeName = dr[i].GetType().Name;
					if(Value==null) 
						Values = Values + "null,";
					if( TypeName == "String")
						Values = Values + ColumnName+"='" + Value + "',";
					if( TypeName== "Decimal")
						Values = Values + ColumnName+"='" + Value + "',";
					if( TypeName== "DateTime")
						//Values = Values + ColumnName+"=to_date('" + System.Convert.ToDateTime (Value).ToShortDateString() + "','yyyy-mm-dd'),";
					    Values = Values + ColumnName+"='" + System.Convert.ToDateTime (Value).ToShortDateString() + "',";
					if( TypeName== "Double")
						Values = Values + ColumnName+"='" + Value + "',";

				}
				Values = Values.Substring(0, Values.Length - 1);
				strSql = "UPDATE " + TableName +" SET "+ Values+ " where " + Filter;
				return DB.ExecuteSQL(strSql);
			}
			catch(Exception ex)
			{
				//MessageBox.Show (ex.ToString ());
				return false;
			}
		}
		public string GetToolBarTable(string TableName)
		{
			StringBuilder sb = new StringBuilder("");
			sb.Append(@"
			    <table class='Toolbar1' id='Table6' cellSpacing='4' cellPadding='2' border='0'>
					<TR>
						<TD style='WIDTH: 14px'><IMG src='images/toolbar.horizontal.start.gif' border='0' unselectable='on'></TD>
						<TD onmouseup='ButtonUp(this);' class='ButtonNormal' onmousemove='ButtonOver(this)'
							onmousedown='ButtonDown(this);' id='btnAdd' title='增加' onclick='OpenAddWeb()' onmouseout='ButtonOut(this,0,0)'><IMG height='20' src='images/icon_new.gif'></TD>
						<TD style='WIDTH: 1px'>
							<div class='TBSep'></div>
						</TD>
						<TD onmouseup='ButtonUp(this);' class='ButtonNormal' onmousemove='ButtonOver(this)'
							onmousedown='ButtonDown(this);' id='btnDel' title='删除' onclick=DeleteSeleteRows('"+TableName+@"')
							onmouseout='ButtonOut(this,0,0)'><IMG height='20' src='images/icon_delete.gif' width='21'></TD>
						<TD style='WIDTH: 1px'>
							<div class='TBSep'></div>
						</TD>
						<TD onmouseup='ButtonUp(this);' class='ButtonNormal' onmousemove='ButtonOver(this)'
							onmousedown='ButtonDown(this);' id='btnEdit' title='更新' onmouseout='ButtonOut(this,0,0)'><IMG height='20' src='images/icon_edit.gif' width='21'></TD>
						<TD width='*'><FONT face='宋体'></FONT></TD>
						<TD style='WIDTH: 14px'><IMG src='images/toolbar.horizontal.end.gif' border='0' unselectable='on'></TD>
					</TR>
				</table>
			");
			return sb.ToString(); 
		}
		public int GetMaxID(string TableName)
		{
			string Sql="select max(ID) from "+TableName;
			DataSet ds=DB.GetDataSetBySql(Sql);
			int MaxID=System.Convert .ToInt16 (ds.Tables [0].Rows [0][0]);
			return MaxID;
		}
	}
}