altertabel.cs

能对数据库进行维护

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using DBMaintainDll.Data_Structure;

namespace DBMaintainDll
{
	/// <summary>
	/// AlterTabel 的摘要说明。
	/// </summary>
	public class AlterTabel : System.Windows.Forms.Form
	{
		private System.Windows.Forms.DataGrid dataGrid1;
		private System.Windows.Forms.GroupBox groupBox1;
		private System.Windows.Forms.TextBox textBox1;
		private System.Windows.Forms.Button btnAdd;
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.Label label3;
		private System.Windows.Forms.Label label4;
		private System.Windows.Forms.TextBox textBox2;
		private System.Windows.Forms.ComboBox comboBox1;
		private System.Windows.Forms.GroupBox groupBox2;
		private System.Windows.Forms.Button btnDel;
		private System.Windows.Forms.Button btnAlt;
		/// <summary>
		/// 必需的设计器变量。
		/// </summary>
		private System.ComponentModel.Container components = null;
		public string tablename=null;
		public DataTable dt=null;
		public DataRowCollection drc=null;
		public Data_Structure.DataAccess da=new DataAccess();
		private System.Windows.Forms.TextBox textBox3;
		public DataTable dt1=null;
		public string a=null;
        public string b=null;
		public string c=null;
		public string d=null;
		public string owner;
		public int CR=0;
		public string IdentityCol=null;
		public string tablename0=null;

		public AlterTabel()
		{
			//
			// Windows 窗体设计器支持所必需的
			//
			InitializeComponent();

			//
			// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
			//
		}
		public AlterTabel(string tab,string owner)
		{
			//
			// Windows 窗体设计器支持所必需的
			//
			InitializeComponent();
            this.tablename=tab;
			this.owner=owner;
			//
			// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
			//
		}

		/// <summary>
		/// 清理所有正在使用的资源。
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}


		#region Windows 窗体设计器生成的代码
		/// <summary>
		/// 设计器支持所需的方法 - 不要使用代码编辑器修改
		/// 此方法的内容。
		/// </summary>
		private void InitializeComponent()
		{
			this.dataGrid1 = new System.Windows.Forms.DataGrid();
			this.groupBox1 = new System.Windows.Forms.GroupBox();
			this.textBox3 = new System.Windows.Forms.TextBox();
			this.comboBox1 = new System.Windows.Forms.ComboBox();
			this.textBox2 = new System.Windows.Forms.TextBox();
			this.label4 = new System.Windows.Forms.Label();
			this.label3 = new System.Windows.Forms.Label();
			this.label2 = new System.Windows.Forms.Label();
			this.label1 = new System.Windows.Forms.Label();
			this.textBox1 = new System.Windows.Forms.TextBox();
			this.btnAdd = new System.Windows.Forms.Button();
			this.groupBox2 = new System.Windows.Forms.GroupBox();
			this.btnAlt = new System.Windows.Forms.Button();
			this.btnDel = new System.Windows.Forms.Button();
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
			this.groupBox1.SuspendLayout();
			this.groupBox2.SuspendLayout();
			this.SuspendLayout();
			// 
			// dataGrid1
			// 
			this.dataGrid1.AlternatingBackColor = System.Drawing.Color.LightBlue;
			this.dataGrid1.CaptionBackColor = System.Drawing.Color.LightSkyBlue;
			this.dataGrid1.DataMember = "";
			this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.dataGrid1.Location = new System.Drawing.Point(8, 8);
			this.dataGrid1.Name = "dataGrid1";
			this.dataGrid1.PreferredColumnWidth = 102;
			this.dataGrid1.ReadOnly = true;
			this.dataGrid1.RowHeadersVisible = false;
			this.dataGrid1.SelectionBackColor = System.Drawing.Color.FromArgb(((System.Byte)(0)), ((System.Byte)(0)), ((System.Byte)(192)));
			this.dataGrid1.Size = new System.Drawing.Size(328, 168);
			this.dataGrid1.TabIndex = 4;
			this.dataGrid1.MouseDown += new System.Windows.Forms.MouseEventHandler(this.dataGrid1_MouseDown);
			this.dataGrid1.MouseUp += new System.Windows.Forms.MouseEventHandler(this.dataGrid1_MouseUp);
			// 
			// groupBox1
			// 
			this.groupBox1.Controls.Add(this.textBox3);
			this.groupBox1.Controls.Add(this.comboBox1);
			this.groupBox1.Controls.Add(this.textBox2);
			this.groupBox1.Controls.Add(this.label4);
			this.groupBox1.Controls.Add(this.label3);
			this.groupBox1.Controls.Add(this.label2);
			this.groupBox1.Controls.Add(this.label1);
			this.groupBox1.Controls.Add(this.textBox1);
			this.groupBox1.Location = new System.Drawing.Point(8, 184);
			this.groupBox1.Name = "groupBox1";
			this.groupBox1.Size = new System.Drawing.Size(192, 152);
			this.groupBox1.TabIndex = 5;
			this.groupBox1.TabStop = false;
			// 
			// textBox3
			// 
			this.textBox3.Location = new System.Drawing.Point(80, 120);
			this.textBox3.Name = "textBox3";
			this.textBox3.Size = new System.Drawing.Size(104, 21);
			this.textBox3.TabIndex = 8;
			this.textBox3.Text = "";
			// 
			// comboBox1
			// 
			this.comboBox1.Location = new System.Drawing.Point(80, 88);
			this.comboBox1.Name = "comboBox1";
			this.comboBox1.Size = new System.Drawing.Size(104, 20);
			this.comboBox1.TabIndex = 6;
			this.comboBox1.SelectedIndexChanged += new System.EventHandler(this.comboBox1_SelectedIndexChanged);
			// 
			// textBox2
			// 
			this.textBox2.Location = new System.Drawing.Point(48, 56);
			this.textBox2.Name = "textBox2";
			this.textBox2.Size = new System.Drawing.Size(136, 21);
			this.textBox2.TabIndex = 5;
			this.textBox2.Text = "";
			// 
			// label4
			// 
			this.label4.Location = new System.Drawing.Point(8, 120);
			this.label4.Name = "label4";
			this.label4.Size = new System.Drawing.Size(64, 16);
			this.label4.TabIndex = 4;
			this.label4.Text = "数据长度:";
			// 
			// label3
			// 
			this.label3.Location = new System.Drawing.Point(8, 88);
			this.label3.Name = "label3";
			this.label3.Size = new System.Drawing.Size(72, 16);
			this.label3.TabIndex = 3;
			this.label3.Text = "数据类型:";
			// 
			// label2
			// 
			this.label2.Location = new System.Drawing.Point(8, 56);
			this.label2.Name = "label2";
			this.label2.Size = new System.Drawing.Size(40, 16);
			this.label2.TabIndex = 2;
			this.label2.Text = "列名:";
			// 
			// label1
			// 
			this.label1.Location = new System.Drawing.Point(8, 24);
			this.label1.Name = "label1";
			this.label1.Size = new System.Drawing.Size(40, 16);
			this.label1.TabIndex = 1;
			this.label1.Text = "表名:";
			// 
			// textBox1
			// 
			this.textBox1.Location = new System.Drawing.Point(48, 24);
			this.textBox1.Name = "textBox1";
			this.textBox1.Size = new System.Drawing.Size(136, 21);
			this.textBox1.TabIndex = 0;
			this.textBox1.Text = "";
			// 
			// btnAdd
			// 
			this.btnAdd.Location = new System.Drawing.Point(16, 16);
			this.btnAdd.Name = "btnAdd";
			this.btnAdd.Size = new System.Drawing.Size(64, 32);
			this.btnAdd.TabIndex = 1;
			this.btnAdd.Text = "增加";
			this.btnAdd.Click += new System.EventHandler(this.btnAdd_Click);
			// 
			// groupBox2
			// 
			this.groupBox2.Controls.Add(this.btnAlt);
			this.groupBox2.Controls.Add(this.btnDel);
			this.groupBox2.Controls.Add(this.btnAdd);
			this.groupBox2.Location = new System.Drawing.Point(216, 184);
			this.groupBox2.Name = "groupBox2";
			this.groupBox2.Size = new System.Drawing.Size(96, 152);
			this.groupBox2.TabIndex = 6;
			this.groupBox2.TabStop = false;
			// 
			// btnAlt
			// 
			this.btnAlt.Location = new System.Drawing.Point(16, 112);
			this.btnAlt.Name = "btnAlt";
			this.btnAlt.Size = new System.Drawing.Size(64, 32);
			this.btnAlt.TabIndex = 3;
			this.btnAlt.Text = "修改";
			this.btnAlt.Click += new System.EventHandler(this.btnAlt_Click);
			// 
			// btnDel
			// 
			this.btnDel.Location = new System.Drawing.Point(16, 64);
			this.btnDel.Name = "btnDel";
			this.btnDel.Size = new System.Drawing.Size(64, 32);
			this.btnDel.TabIndex = 2;
			this.btnDel.Text = "删除";
			this.btnDel.Click += new System.EventHandler(this.btnDel_Click);
			// 
			// AlterTabel
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.ClientSize = new System.Drawing.Size(344, 373);
			this.Controls.Add(this.groupBox2);
			this.Controls.Add(this.groupBox1);
			this.Controls.Add(this.dataGrid1);
			this.MaximizeBox = false;
			this.MinimizeBox = false;
			this.Name = "AlterTabel";
			this.Text = "修改表结构";
			this.Load += new System.EventHandler(this.AlterTabel_Load);
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
			this.groupBox1.ResumeLayout(false);
			this.groupBox2.ResumeLayout(false);
			this.ResumeLayout(false);

		}
		#endregion

		#region //修改表 初始化dataGrid
		private void InitDataGrid(string tab)
		{
			
			string TableName =tab;
			string sql=null;
			sql="SELECT a.Name N'列名',c.Name N'数据类型',a.Length N'长度' FROM SysObjects b,SysTypes c,SysColumns a LEFT JOIN SysProperties d ON(a.Id = d.Id AND a.Colid=d.Smallid) WHERE (b.Xtype ='u' OR b.Xtype='v') AND b.Id=a.Id AND c.XType=a.XType AND c.Name<> 'sysname' AND b.Name= '"+TableName+"' ORDER BY object_name(a.id), a.colorder"; 
			dt1=da.GetDataTable(sql);//获得数据
			// 不显示自动增长字段
			dt=dt1.Clone();
			for (int i=0;i<dt1.Rows.Count;i++)
			{
				if(dt1.Rows[i][0].ToString().Trim()!=IdentityCol)
				{
					dt.ImportRow(dt1.Rows[i]);
				}
			}
			object[] distinctDataType = GetDistinctValues(dt,"数据类型");//取数据类型
			dataGrid1.DataSource=dt;//绑定dataGrid
			this.dataGrid1.Select(0);
			this.textBox1.Text=tablename;
			this.textBox1.ReadOnly=true;
			this.textBox2.Text=this.dataGrid1[0,0].ToString();
			this.textBox3.Text=this.dataGrid1[0,2].ToString();
			this.comboBox1.Text=this.dataGrid1[0,1].ToString();
			//将数据类型绑定到combox，如果不含有int float nvarchar 则添加
			for (int i=0;i<distinctDataType.Length;i++)
			{
				this.comboBox1.Items.Add(distinctDataType[i].ToString().Trim());
			}


			if (!(this.comboBox1.Items.Contains("int")))
				this.comboBox1.Items.Add("int");
			if (!(this.comboBox1.Items.Contains("float")))
				this.comboBox1.Items.Add("float");
			if (!(this.comboBox1.Items.Contains("nvarchar")))
				this.comboBox1.Items.Add("nvarchar");
	
			dataGrid1.CaptionText=TableName;
			
			
		}


		private void InitDataGrid2()
		{
			string sql=null;
			sql="SELECT a.Name N'列名',c.Name N'数据类型',a.Length N'长度' FROM SysObjects b,SysTypes c,SysColumns a LEFT JOIN SysProperties d ON(a.Id = d.Id AND a.Colid=d.Smallid) WHERE (b.Xtype ='u' OR b.Xtype='v') AND b.Id=a.Id AND c.XType=a.XType AND c.Name<> 'sysname' AND b.Name= '"+tablename+"' ORDER BY object_name(a.id), a.colorder"; 
			dt1=da.GetDataTable(sql);//获得数据
			// 不显示自动增长字段
			dt=dt1.Clone();
			for (int i=0;i<dt1.Rows.Count;i++)
			{
				if(dt1.Rows[i][0].ToString().Trim()!=IdentityCol)
				{
					dt.ImportRow(dt1.Rows[i]);
				}
			}

			object[] distinctDataType = GetDistinctValues(dt,"数据类型");//取数据类型

			dataGrid1.DataSource=dt;//绑定dataGrid
			int x=dt.Rows.Count-1;
			this.dataGrid1.Select(x);
			this.dataGrid1.CurrentRowIndex=x;
			this.textBox1.Text=tablename;
			this.textBox1.ReadOnly=true;
			this.textBox2.Text=this.dataGrid1[x,0].ToString();
			this.textBox3.Text=this.dataGrid1[x,2].ToString();
			this.comboBox1.Items.Clear();
			this.comboBox1.Text=this.dataGrid1[x,1].ToString();
			//将数据类型绑定到combox，如果不含有int float nvarchar 则添加
			for (int i=0;i<distinctDataType.Length;i++)
			{
				this.comboBox1.Items.Add(distinctDataType[i].ToString().Trim());
			}

			if (!(this.comboBox1.Items.Contains("int")))
				this.comboBox1.Items.Add("int");
			if (!(this.comboBox1.Items.Contains("float")))
				this.comboBox1.Items.Add("float");
			if (!(this.comboBox1.Items.Contains("nvarchar")))
				this.comboBox1.Items.Add("nvarchar");
	
			dataGrid1.CaptionText=tablename;
			if (this.owner=="dbo")
			{
				this.btnAlt.Enabled=false;
				this.btnDel.Enabled=false;
			}
			
		}
		
        #region //获得datatable中固定列的唯一值 
		public object[] GetDistinctValues(DataTable dtable,string colName)
			 
		{
			Hashtable hTable = new Hashtable();
			foreach(DataRow drow in dtable.Rows)
			{
				try
		
				{
					hTable.Add(drow[colName],string.Empty);
				}