frmaddassets.cs

资产数据库设计

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

namespace AdminGoods
{
	/// <summary>
	/// frmAddAssets 的摘要说明。
	/// </summary>
	public class frmAddAssets : System.Windows.Forms.Form
	{
		private System.Windows.Forms.TextBox txtname;
		private System.Windows.Forms.TextBox txtCode;
		private System.Windows.Forms.ComboBox cbWorkerID;
		private System.Windows.Forms.ComboBox cbAssetsClassID;
		private System.Windows.Forms.Button button1;
		private System.Windows.Forms.Button button2;
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.Label label3;
		private System.Windows.Forms.Label label4;
		/// <summary>
		/// 必需的设计器变量。
		/// </summary>
		private System.ComponentModel.Container components = null;

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

			//
			// 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.txtname = new System.Windows.Forms.TextBox();
			this.txtCode = new System.Windows.Forms.TextBox();
			this.cbWorkerID = new System.Windows.Forms.ComboBox();
			this.cbAssetsClassID = new System.Windows.Forms.ComboBox();
			this.button1 = new System.Windows.Forms.Button();
			this.button2 = new System.Windows.Forms.Button();
			this.label1 = new System.Windows.Forms.Label();
			this.label2 = new System.Windows.Forms.Label();
			this.label3 = new System.Windows.Forms.Label();
			this.label4 = new System.Windows.Forms.Label();
			this.SuspendLayout();
			// 
			// txtname
			// 
			this.txtname.Location = new System.Drawing.Point(24, 56);
			this.txtname.Name = "txtname";
			this.txtname.Size = new System.Drawing.Size(144, 21);
			this.txtname.TabIndex = 0;
			this.txtname.Text = "";
			// 
			// txtCode
			// 
			this.txtCode.Location = new System.Drawing.Point(264, 56);
			this.txtCode.Name = "txtCode";
			this.txtCode.Size = new System.Drawing.Size(144, 21);
			this.txtCode.TabIndex = 1;
			this.txtCode.Text = "";
			// 
			// cbWorkerID
			// 
			this.cbWorkerID.Location = new System.Drawing.Point(24, 152);
			this.cbWorkerID.Name = "cbWorkerID";
			this.cbWorkerID.Size = new System.Drawing.Size(144, 20);
			this.cbWorkerID.TabIndex = 2;
			this.cbWorkerID.Text = "comboBox1";
			// 
			// cbAssetsClassID
			// 
			this.cbAssetsClassID.Location = new System.Drawing.Point(264, 152);
			this.cbAssetsClassID.Name = "cbAssetsClassID";
			this.cbAssetsClassID.Size = new System.Drawing.Size(144, 20);
			this.cbAssetsClassID.TabIndex = 3;
			this.cbAssetsClassID.Text = "comboBox2";
			this.cbAssetsClassID.SelectedIndexChanged += new System.EventHandler(this.cbAssetsClassID_SelectedIndexChanged);
			// 
			// button1
			// 
			this.button1.Location = new System.Drawing.Point(104, 208);
			this.button1.Name = "button1";
			this.button1.Size = new System.Drawing.Size(88, 24);
			this.button1.TabIndex = 4;
			this.button1.Text = "Add";
			this.button1.Click += new System.EventHandler(this.button1_Click);
			// 
			// button2
			// 
			this.button2.Location = new System.Drawing.Point(248, 208);
			this.button2.Name = "button2";
			this.button2.Size = new System.Drawing.Size(104, 24);
			this.button2.TabIndex = 5;
			this.button2.Text = "Close";
			this.button2.Click += new System.EventHandler(this.button2_Click);
			// 
			// label1
			// 
			this.label1.Location = new System.Drawing.Point(16, 24);
			this.label1.Name = "label1";
			this.label1.Size = new System.Drawing.Size(112, 16);
			this.label1.TabIndex = 6;
			this.label1.Text = "资产名称";
			// 
			// label2
			// 
			this.label2.Location = new System.Drawing.Point(248, 24);
			this.label2.Name = "label2";
			this.label2.Size = new System.Drawing.Size(112, 16);
			this.label2.TabIndex = 7;
			this.label2.Text = "资产编码";
			// 
			// label3
			// 
			this.label3.Location = new System.Drawing.Point(8, 120);
			this.label3.Name = "label3";
			this.label3.Size = new System.Drawing.Size(112, 16);
			this.label3.TabIndex = 8;
			this.label3.Text = "员工用户名";
			// 
			// label4
			// 
			this.label4.Location = new System.Drawing.Point(248, 120);
			this.label4.Name = "label4";
			this.label4.Size = new System.Drawing.Size(112, 16);
			this.label4.TabIndex = 9;
			this.label4.Text = "资产类别";
			// 
			// frmAddAssets
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.ClientSize = new System.Drawing.Size(456, 261);
			this.Controls.Add(this.label4);
			this.Controls.Add(this.label3);
			this.Controls.Add(this.label2);
			this.Controls.Add(this.label1);
			this.Controls.Add(this.button2);
			this.Controls.Add(this.button1);
			this.Controls.Add(this.cbAssetsClassID);
			this.Controls.Add(this.cbWorkerID);
			this.Controls.Add(this.txtCode);
			this.Controls.Add(this.txtname);
			this.MaximizeBox = false;
			this.MinimizeBox = false;
			this.Name = "frmAddAssets";
			this.Text = "添加资产";
			this.Load += new System.EventHandler(this.frmAddAssets_Load);
			this.ResumeLayout(false);

		}
		#endregion

		private void button2_Click(object sender, System.EventArgs e)
		{
			this.Close();

		}

		private void button1_Click(object sender, System.EventArgs e)
		{
			int workerID = this.queryWorkID(this.cbWorkerID.SelectedValue.ToString());
			int classID = this.queryClassID(this.cbAssetsClassID.SelectedValue.ToString ());

			string name = this.txtname .Text .Trim ().ToString ();
			string code = this.txtCode .Text .Trim ().ToString ();

			bool save = this.addAssets (workerID,classID,name,code);

			if(save)
			{
				MessageBox.Show (this,"save the Assets Success!");

			}
			else
			{
				MessageBox.Show (this,"save the Assets Failure!");

			}
			
		}
		private bool addAssets(int workid,int classid,string name, string code)
		{
			bool last = false;

			Database db = new Database ();

			System.Data .SqlClient .SqlConnection conn;
			System.Data .SqlClient .SqlCommand cmd;
			System.Data .SqlClient .SqlDataReader rs;
			string sql = "";
			try
			{
				conn = db.openConn ();
				cmd = new System.Data.SqlClient.SqlCommand ();
				sql = "insert into assets(name,code,classID,workerID)values('" +name + "','"+ code +"'," +classid+ " , "+ workid +" )";
				cmd.CommandText = sql;
				cmd.Connection = conn;

				//rs = cmd.ExecuteReader (System.Data .CommandBehavior .CloseConnection );

				cmd.ExecuteNonQuery();
				last = true;
				 
			}
			catch(Exception ex)
			{
				string sm = ex.Message ;
				last = false;
			}
			finally
			{
				db.closeConn ();
			}

			return last;
		}

		private void frmAddAssets_Load(object sender, System.EventArgs e)
		{
			this.cbAssetsClassID.DataSource = this.listAllAssetsClass().DefaultView;
			
			this.cbAssetsClassID .ValueMember ="className";

			this.cbWorkerID .DataSource = this.listAllWorkerUsername().DefaultView ;
			this.cbWorkerID .ValueMember ="username";
			this.cbWorkerID .Refresh ();

			this.cbAssetsClassID .Refresh();

		}

		private  int queryClassID(string className)
		{
			int last = 0;

			Database db = new Database ();

			System.Data .SqlClient .SqlConnection conn;
			System.Data .SqlClient .SqlCommand cmd;
			System.Data .SqlClient .SqlDataReader rs;
			string sql = "";
			try
			{
				conn = db.openConn ();
				cmd = new System.Data.SqlClient.SqlCommand ();
				sql = "select * from assetsClass where  className =  '"+ className +"'  ";
				cmd.CommandText = sql;
				cmd.Connection = conn;

				rs = cmd.ExecuteReader (System.Data .CommandBehavior .CloseConnection );

				while(rs.Read ())
				{
					
					last =Convert.ToInt32 (  rs["ID"].ToString() );

				}
			}
			catch(Exception ex)
			{
				string sm = ex.Message ;
				last = 0;
			}
			finally
			{
				db.closeConn ();
			}

			return last;
		}

		private  int queryWorkID(string className)
		{
			int last = 0;

			Database db = new Database ();

			System.Data .SqlClient .SqlConnection conn;
			System.Data .SqlClient .SqlCommand cmd;
			System.Data .SqlClient .SqlDataReader rs;
			string sql = "";
			try
			{
				conn = db.openConn ();
				cmd = new System.Data.SqlClient.SqlCommand ();
				sql = "select * from worker where  username =  '"+ className +"'  ";
				cmd.CommandText = sql;
				cmd.Connection = conn;

				rs = cmd.ExecuteReader (System.Data .CommandBehavior .CloseConnection );

				while(rs.Read ())
				{
					
					last =Convert.ToInt32 (  rs["ID"].ToString() );

				}
			}
			catch(Exception ex)
			{
				string sm = ex.Message ;
				last = 0;
			}
			finally
			{
				db.closeConn ();
			}

			return last;
		}
		private  DataTable listAllAssetsClass()
		{
			System.Data.SqlClient.SqlConnection conn;		

			SqlDataAdapter dp;
			string sql;
			DataSet ds = new DataSet();
			Database db = new Database();

			DataTable last = new DataTable();
			try
			{
				//sql = "select id,username,realname ,workcode,workage,address from worker  where worktype = '1'";

				sql = "select id,className from assetsClass";

				conn = db.openConn();
				
				dp = new System.Data.SqlClient.SqlDataAdapter(sql,conn);	 
 
				dp.Fill(ds,"worker");

				last = ds.Tables["worker"];

			}
			catch
			{
				last = null;
			}
			finally
			{
				db.closeConn();
			}
			return last;
		}
		private  DataTable listAllWorkerUsername()
		{
			System.Data.SqlClient.SqlConnection conn;		

			SqlDataAdapter dp;
			string sql;
			DataSet ds = new DataSet();
			Database db = new Database();

			DataTable last = new DataTable();
			try
			{
				//sql = "select id,username,realname ,workcode,workage,address from worker  where worktype = '1'";

				sql = "select username from worker";

				conn = db.openConn();
				
				dp = new System.Data.SqlClient.SqlDataAdapter(sql,conn);	 
 
				dp.Fill(ds,"worker");

				last = ds.Tables["worker"];

			}
			catch
			{
				last = null;
			}
			finally
			{
				db.closeConn();
			}
			return last;
		}

		private void cbAssetsClassID_SelectedIndexChanged(object sender, System.EventArgs e)
		{
			
		}
	}
}