databaseobjectinfo_newobj.cs

树形逻辑报表程序源码,能根居元数据定义拖拉开发新的报表.

			// ConstrainExp
			// 
			this.ConstrainExp.Format = "";
			this.ConstrainExp.FormatInfo = null;
			this.ConstrainExp.HeaderText = "约束";
			this.ConstrainExp.MappingName = "ConstrainExp";
			this.ConstrainExp.Width = 75;
			// 
			// CheckExp
			// 
			this.CheckExp.Format = "";
			this.CheckExp.FormatInfo = null;
			this.CheckExp.HeaderText = "数据检查";
			this.CheckExp.MappingName = "CheckExp";
			this.CheckExp.Width = 75;
			// 
			// ForeignExp
			// 
			this.ForeignExp.Format = "";
			this.ForeignExp.FormatInfo = null;
			this.ForeignExp.HeaderText = "外键引用";
			this.ForeignExp.MappingName = "ForeignExp";
			this.ForeignExp.Width = 75;
			// 
			// DatabaseObjectInfo_newobj
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.ClientSize = new System.Drawing.Size(592, 429);
			this.Controls.Add(this.dataGrid_fields);
			this.Controls.Add(this.groupBox2);
			this.MaximizeBox = false;
			this.MinimizeBox = false;
			this.Name = "DatabaseObjectInfo_newobj";
			this.Text = "录入新数据对象信息(表或视图)";
			this.Load += new System.EventHandler(this.DatabaseObjectInfo_newobj_Load);
			this.groupBox2.ResumeLayout(false);
			((System.ComponentModel.ISupportInitialize)(this.dataGrid_fields)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion


		/// <summary>
		/// DatabaseObjectInfo_newobj_Load - 载入窗体时，初始化字典列表框及分类列表框
		/// </summary>
		private void DatabaseObjectInfo_newobj_Load(object sender, System.EventArgs e)
		{
			if (conn.State == System.Data.ConnectionState.Closed)
				conn.Open();
			//conn.ChangeDatabase("TreeDb");

			System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
			System.Data.OleDb.OleDbDataReader rd;
			cmd.Connection = conn;
			
			cmd.CommandText = "select ObjectName from TreeDbObjectInfos";
			rd = cmd.ExecuteReader();

			while(rd.Read())
			{
				comboBox_ObjectName.Items.Add(rd.GetString(rd.GetOrdinal("ObjectName")));
			}

			rd.Close();

			cmd.CommandText = "select distinct CatalogName from TreeDbObjectInfos";
			rd = cmd.ExecuteReader();
			while(rd.Read())
			{
				comboBox_CatalogName.Items.Add(rd.GetString(rd.GetOrdinal("CatalogName")));
			}
			
			rd.Close();

			conn.Close();

		}

		/// <summary>
		/// comboBox_ObjectName_TextChanged - 用户在字典列表框中选择了新的字典对象
		/// 　　　　　　　　　　　　　　　　　从字典管理表中读出它的信息，更新界面
		/// </summary>
		private void comboBox_ObjectName_TextChanged(object sender, System.EventArgs e)
		{
			//comboBox_ObjectName.Text = comboBox_ObjectName.Text.Trim();
			//if (comboBox_ObjectName.Text.Length<=0)
			//	return;

			if (conn.State == System.Data.ConnectionState.Closed)
				conn.Open();
			//conn.ChangeDatabase("TreeDb");

			System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
			System.Data.OleDb.OleDbDataReader rd;
			cmd.Connection = conn;
			
			cmd.CommandText = "select * from TreeDbObjectInfos where ObjectName='" + comboBox_ObjectName.Text + "'";
			rd = cmd.ExecuteReader();
			if(rd.Read())
			{
				//comboBox_ObjectName.Text = rd.GetString(rd.GetOrdinal("ObjectName"));
				comboBox_CatalogName.Text = rd.GetString(rd.GetOrdinal("CatalogName"));
				if("table".Equals(rd.GetString(rd.GetOrdinal("ObjectType"))))
				{
					radioButton_tabletype.Checked = true;
					radioButton_viewtype.Checked = false;
				}
				else
				{
					radioButton_tabletype.Checked = false;
					radioButton_viewtype.Checked = true;
				}

				int n = rd.GetOrdinal("EDescription");
				if(rd.IsDBNull(n))
					textBox_Edescription.Text = "";
				else
					textBox_Edescription.Text = rd.GetString(n);
				n = rd.GetOrdinal("CDescription");
				if(rd.IsDBNull(n))
					textBox_Cdescription.Text = "";
				else
					textBox_Cdescription.Text = rd.GetString(n);
				n = rd.GetOrdinal("HDescription");
				if(rd.IsDBNull(n))
					textBox_Hdescription.Text = "";
				else
					textBox_Hdescription.Text = rd.GetString(n);

			}
			else
			{
				//不存在此表的信息
				//comboBox_ObjectName.Text = rd.GetString(rd.GetOrdinal("ObjectName"));
				comboBox_CatalogName.Text = "";
				radioButton_tabletype.Checked = true;
				radioButton_viewtype.Checked = false;
				textBox_Edescription.Text = "";
				textBox_Cdescription.Text = "";
				textBox_Hdescription.Text = "";
			}
			
			rd.Close();
			
			//System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("select * from TreeDbObjectInfos_items where ObjectName='" + comboBox_ObjectName.Text + "'", conn);
			//System.Data.DataSet ds = new System.Data.DataSet();
			
			da = new System.Data.OleDb.OleDbDataAdapter("select * from TreeDbObjectInfos_items where ObjectName='" + comboBox_ObjectName.Text + "'", conn);
			ds = new System.Data.DataSet();
			da.Fill(ds);
			dataGrid_fields.SetDataBinding(ds,"table");


			conn.Close();
		
		}

		/// <summary>
		/// button_browsDBobject_Click - 浏览数据库中的用户对象（弹出浏览窗体）
		/// </summary>
		private void button_browsDBobject_Click(object sender, System.EventArgs e)
		{
			//DatabaseObject_brows dbbrowswin = new DatabaseObject_brows();
			DatabaseObjectExplore dbbrowswin = new DatabaseObjectExplore();
			
			//弹出浏览窗体
			if (dbbrowswin.ShowDialog(this) == DialogResult.OK)
			{
				comboBox_ObjectName.Text = dbbrowswin.objectname;
				if (dbbrowswin.objecttype.Equals("table"))
				{
					radioButton_tabletype.Checked = true;
					radioButton_viewtype.Checked = false;
				}
				else
				{
					radioButton_tabletype.Checked = false;
					radioButton_viewtype.Checked = true;
				}

				//conn.ConnectionString = "Provider=SQLOLEDB.1;Password=20020830;Persist Security Info=True;User ID=liao;Data Source=LIAOHOME\\LIAOSQLSVR";
				if (conn.State == System.Data.ConnectionState.Closed)
					conn.Open();
				//conn.ChangeDatabase("TreeDb");

				//System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("select * from TreeDbObjectInfos_items where ObjectName='" + comboBox_ObjectName.Text + "'", conn);
				//System.Data.DataSet ds = new System.Data.DataSet();
				da = new System.Data.OleDb.OleDbDataAdapter();
				System.Data.OleDb.OleDbCommand cmd1 = new System.Data.OleDb.OleDbCommand("select * from TreeDbObjectInfos_items where ObjectName='" + comboBox_ObjectName.Text + "'", conn);
				da.SelectCommand = cmd1;

				//System.Data.OleDb.OleDbCommandBuilder cmdbuilder = new System.Data.OleDb.OleDbCommandBuilder(da);
				

				ds = new System.Data.DataSet();
				da.Fill(ds);

				ds.Tables[0].Clear();
				System.Data.DataRow rw;

				string SqlStr = "";
				string fulltblname = comboBox_ObjectName.Text;
				string tablename="",dbname="",ownername="";
				int n = fulltblname.LastIndexOf(".");
				
				if (n<0)
				{
					tablename = fulltblname;
					//SqlStr = "sp_columns @table_name = '" + tablename +"'";

				}
				else
				{
					tablename = fulltblname.Substring(n+1);
					fulltblname = fulltblname.Substring(0,n);
 				   
					n = fulltblname.LastIndexOf(".");
					if (n<0)
					{
						ownername = fulltblname;
						//SqlStr = "sp_columns @table_name = '" + tablename +"',@table_owner = '" + ownername + "'";
					}
					else
					{
						ownername = fulltblname.Substring(n+1);
						dbname = fulltblname.Substring(0,n);
						//SqlStr = "sp_columns @table_name = '" + tablename +"',@table_owner = '" + ownername + "',@table_qualifier = '" + dbname +"'";
					}
					
					SqlStr = "SELECT COLUMN_NAME,DATA_TYPE ,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION  FROM "+dbname+".INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG='"+dbname+"' and TABLE_SCHEMA='"+ownername+"' and TABLE_NAME='"+tablename +"'";
				}


				//System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("sp_columns @table_name = '" + comboBox_ObjectName.Text +"'",conn);
				System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(SqlStr,conn);
				System.Data.OleDb.OleDbDataReader rd = cmd.ExecuteReader();
				 
				while(rd.Read())
				{
					rw = ds.Tables[0].NewRow();
					//rw["ItemName"]= rd.GetString(rd.GetOrdinal("COLUMN_NAME"));
					//rw["TypeOfData"]= rd.GetString(rd.GetOrdinal("TYPE_NAME"));
					//rw["LengthOfData"]= rd.GetInt32(rd.GetOrdinal("LENGTH"));
					//rw["PrecisionOfData"]= rd.GetInt32(rd.GetOrdinal("PRECISION"));
					rw["ItemName"]= ""+rd["COLUMN_NAME"];
					rw["TypeOfData"]= ""+rd["DATA_TYPE"];

					string val = ""+rd["CHARACTER_MAXIMUM_LENGTH"]+rd["NUMERIC_PRECISION"];
					rw["PrecisionOfData"]=(val.Length==0)?0:int.Parse(val);
					rw["LengthOfData"] =(val.Length==0)?0:int.Parse(val);

					ds.Tables[0].Rows.Add(rw);
				}
				rd.Close();

				dataGrid_fields.SetDataBinding(ds,"table");
				conn.Close();

			}
		}

		private void button_exit_Click(object sender, System.EventArgs e)
		{
			this.Dispose();
		}

		private void button_save_Click(object sender, System.EventArgs e)
		{
			if (comboBox_CatalogName.Text.Trim().Length<=0 && comboBox_ObjectName.Text.Trim().Length<=0)
			{
				MessageBox.Show("名称或所属不能留空!");
				return;
			}

			if (conn.State == System.Data.ConnectionState.Closed)
				conn.Open();
			//conn.ChangeDatabase("TreeDb");

			try
			{
				System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
				cmd.Connection = conn;

				cmd.CommandText = "select count(*) from TreeDbObjectInfos where ObjectName='" + comboBox_ObjectName.Text + "'";
				if(cmd.ExecuteScalar().ToString().Equals("1"))
				{
					cmd.CommandText = "update TreeDbObjectInfos set ";
					cmd.CommandText+= "EDescription='"+textBox_Edescription.Text+"',";
					cmd.CommandText+= "CDescription='"+textBox_Cdescription.Text+"',HDescription='" + textBox_Hdescription.Text + "',";
					if ( radioButton_tabletype.Checked)
						cmd.CommandText+= "ObjectType='table',";
					else
						cmd.CommandText+= "ObjectType='view',";

					cmd.CommandText+= "CatalogName='"+ comboBox_CatalogName.Text + "' where ObjectName='" + comboBox_ObjectName.Text + "'";
					cmd.ExecuteNonQuery();
				}
				else
				{
					cmd.CommandText = "insert into TreeDbObjectInfos (ObjectName,EDescription,CDescription,HDescription,ObjectType,CatalogName) values(";
					cmd.CommandText+= "'"+comboBox_ObjectName.Text+"'";
					cmd.CommandText+= ",'"+textBox_Edescription.Text+"','" + textBox_Cdescription.Text + "','" + textBox_Hdescription.Text + "',";
					if ( radioButton_tabletype.Checked)
						cmd.CommandText+= "'table','" + comboBox_CatalogName.Text + "')";
					else
						cmd.CommandText+= "'view','" + comboBox_CatalogName.Text + "')";;
				
					cmd.ExecuteNonQuery();
				}


				//da.Update(ds.Tables[0]);

				
				System.Data.DataTable tbl = ds.Tables[0];
				foreach (System.Data.DataRow rw in tbl.Rows)
				{
					try
					{
						cmd.CommandText = "select count(*) from TreeDbObjectInfos_items where ObjectName='" + comboBox_ObjectName.Text + "' and itemname='" + rw["itemname"] + "'";
						if(cmd.ExecuteScalar().ToString().Equals("1"))
						{
							cmd.CommandText = "update TreeDbObjectInfos_items set ";
							cmd.CommandText+= "EItemDescription='"+rw["EItemDescription"]+"',";
							cmd.CommandText+= "CItemDescription='"+rw["CItemDescription"]+"',HItemDescription='" + rw["HItemDescription"] + "',TypeOfData='"  +  rw["TypeOfData"] + "'";
							cmd.CommandText+= ",LengthOfData="+ (rw.IsNull("LengthOfData")?"0":rw["LengthOfData"])+",PrecisionOfData=" + (rw.IsNull("PrecisionOfData")?"0":rw["PrecisionOfData"]) + ",ConstrainExp='" + rw["ConstrainExp"] + "',CheckExp='" + rw["CheckExp"] + "',ForeignExp='" +  rw["ForeignExp"] + "'";
							cmd.CommandText+= " where ObjectName='" + comboBox_ObjectName.Text + "' and itemname='" + rw["itemname"] + "'";
							cmd.ExecuteNonQuery();
						}
						else
						{
							cmd.CommandText = "insert into TreeDbObjectInfos_items (ObjectName,itemname,EItemDescription,CItemDescription,HItemDescription,TypeOfData,LengthOfData,PrecisionOfData,ConstrainExp,CheckExp,ForeignExp) values(";
							cmd.CommandText+= "'"+comboBox_ObjectName.Text+"'";
							cmd.CommandText+= ",'"+rw["itemname"]+"','" + rw["EItemDescription"] + "','" + rw["CItemDescription"] + "','" + rw["HItemDescription"] + "','" +  rw["TypeOfData"] + "'";
							cmd.CommandText+= ","+(rw.IsNull("LengthOfData")?"0":rw["LengthOfData"])+"," + (rw.IsNull("PrecisionOfData")?"0":rw["PrecisionOfData"]) + ",'" + rw["ConstrainExp"] + "','" + rw["CheckExp"] + "','" +  rw["ForeignExp"] + "')";
							cmd.ExecuteNonQuery();
						}
					}
					catch(Exception ee)
					{
						MessageBox.Show("出错，数据项：" + rw["itemname"] + "------" + ee.ToString());
					}
				}

			}
			catch (Exception er)
			{
			    MessageBox.Show(this,er.Message);
			}

			conn.Close();
		}
	}
}