frmbookdoc.cs

采用C#和SQL Server 2000开发 功能比较全面的图书馆管理系统

			this.sqlDeleteCommand1.CommandText = @"DELETE FROM 图书信息 WHERE (编号 = @Original_编号) AND (ISBN = @Original_ISBN OR @Original_ISBN IS NULL AND ISBN IS NULL) AND (书名 = @Original_书名 OR @Original_书名 IS NULL AND 书名 IS NULL) AND (书架名称 = @Original_书架名称 OR @Original_书架名称 IS NULL AND 书架名称 IS NULL) AND (价格 = @Original_价格 OR @Original_价格 IS NULL AND 价格 IS NULL) AND (作者 = @Original_作者 OR @Original_作者 IS NULL AND 作者 IS NULL) AND (借出次数 = @Original_借出次数 OR @Original_借出次数 IS NULL AND 借出次数 IS NULL) AND (入库时间 = @Original_入库时间 OR @Original_入库时间 IS NULL AND 入库时间 IS NULL) AND (出版社 = @Original_出版社 OR @Original_出版社 IS NULL AND 出版社 IS NULL) AND (库存总量 = @Original_库存总量 OR @Original_库存总量 IS NULL AND 库存总量 IS NULL) AND (操作员 = @Original_操作员 OR @Original_操作员 IS NULL AND 操作员 IS NULL) AND (是否注销 = @Original_是否注销) AND (条形码 = @Original_条形码 OR @Original_条形码 IS NULL AND 条形码 IS NULL) AND (现存量 = @Original_现存量 OR @Original_现存量 IS NULL AND 现存量 IS NULL) AND (简介 = @Original_简介 OR @Original_简介 IS NULL AND 简介 IS NULL) AND (类型 = @Original_类型 OR @Original_类型 IS NULL AND 类型 IS NULL) AND (译者 = @Original_译者 OR @Original_译者 IS NULL AND 译者 IS NULL) AND (页码 = @Original_页码 OR @Original_页码 IS NULL AND 页码 IS NULL)";
			this.sqlDeleteCommand1.Connection = this.sqlConnection1;
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_编号", System.Data.SqlDbType.VarChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "编号", System.Data.DataRowVersion.Original, null));
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_ISBN", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ISBN", System.Data.DataRowVersion.Original, null));
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_书名", System.Data.SqlDbType.VarChar, 200, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "书名", System.Data.DataRowVersion.Original, null));
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_书架名称", System.Data.SqlDbType.VarChar, 200, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "书架名称", System.Data.DataRowVersion.Original, null));
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_价格", System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "价格", System.Data.DataRowVersion.Original, null));
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_作者", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "作者", System.Data.DataRowVersion.Original, null));
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_借出次数", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "借出次数", System.Data.DataRowVersion.Original, null));
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_入库时间", System.Data.SqlDbType.DateTime, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "入库时间", System.Data.DataRowVersion.Original, null));
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_出版社", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "出版社", System.Data.DataRowVersion.Original, null));
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_库存总量", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "库存总量", System.Data.DataRowVersion.Original, null));
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_操作员", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "操作员", System.Data.DataRowVersion.Original, null));
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_是否注销", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "是否注销", System.Data.DataRowVersion.Original, null));
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_条形码", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "条形码", System.Data.DataRowVersion.Original, null));
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_现存量", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "现存量", System.Data.DataRowVersion.Original, null));
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_简介", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "简介", System.Data.DataRowVersion.Original, null));
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_类型", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "类型", System.Data.DataRowVersion.Original, null));
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_译者", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "译者", System.Data.DataRowVersion.Original, null));
			this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_页码", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "页码", System.Data.DataRowVersion.Original, null));
			// 
			// sqlConnection1
			// 
			this.sqlConnection1.ConnectionString = "workstation id=JEFF;packet size=4096;integrated security=SSPI;data source=JEFF;pe" +
				"rsist security info=False;initial catalog=libbook";
			// 
			// dataSet11
			// 
			this.dataSet11.DataSetName = "DataSet1";
			this.dataSet11.Locale = new System.Globalization.CultureInfo("zh-CN");
			// 
			// frmBookDoc
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.ClientSize = new System.Drawing.Size(632, 317);
			this.Controls.Add(this.GroupBox1);
			this.Name = "frmBookDoc";
			this.Controls.SetChildIndex(this.dgdList, 0);
			this.Controls.SetChildIndex(this.GroupBox1, 0);
			((System.ComponentModel.ISupportInitialize)(this.dgdList)).EndInit();
			this.GroupBox1.ResumeLayout(false);
			((System.ComponentModel.ISupportInitialize)(this.dataSet11)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion

		public override void Prepare()
		{
			this.Text = "图书档案管理";
			Label1.Text = "  条形码";
			Label2.Text = "    编号";
			Label3.Text = "    书名";
			DataBase db = new DataBase();
			DataView dv = db.RunSelectSQL( "select 类型名称 from 图书类型" );
			foreach ( DataRow dr in dv.Table.Rows )
				cbbType.Items.Add( dr[ 0 ].ToString().Trim() );
			dv = db.RunSelectSQL( "select 出版社名称 from 出版社" );
			foreach ( DataRow dr in dv.Table.Rows )
				cbbPress.Items.Add( dr[ 0 ].ToString().Trim() );
			dv = db.RunSelectSQL( "select 书架名称 from 书架" );
			foreach ( DataRow dr in dv.Table.Rows )
				cbbBookcase.Items.Add( dr[ 0 ].ToString().Trim() );
			db.Dispose();
		}

		public override void LoadData()
		{
			string strFilter;
			if ( txbOne.Text.Trim().Length == 0 )
			{
				if ( txbTwo.Text.Trim().Length == 0 )
				{
					if ( txbThree.Text.Trim().Length == 0 )
						strFilter = "";
					else
					{
						strFilter = "where 书名 like '";
						strFilter += txbThree.Text.Trim() + "%'";
					}
				}
				else
				{
					strFilter = "where 编号 like '";
					strFilter += txbTwo.Text.Trim() + "%'";
					if ( txbThree.Text.Trim().Length != 0 )
						strFilter += " and 书名 like '" + txbThree.Text.Trim() + "%'";
				}
			}
			else
			{
				strFilter = "where 条形码 like '" + txbOne.Text.Trim() + "%'";
				if ( txbTwo.Text.Trim().Length != 0 )
				{
					strFilter += " and 编号 like '" + txbTwo.Text.Trim() + "%'";
					if ( txbThree.Text.Trim().Length != 0 )
						strFilter += " and 书名 like '" + txbThree.Text.Trim() + "%'";
				}
				else
				{
					if ( txbThree.Text.Trim().Length != 0 )
						strFilter += " and 书名 like '" + txbThree.Text.Trim() + "%'";
				}						
			}
			dataSet11.Clear();
			sqlConnection1.ConnectionString = DataBase.sConn;
			sqlDataAdapter1.SelectCommand.CommandText = "select * from 图书信息 " + strFilter;
			sqlDataAdapter1.Fill( dataSet11 );
			dtData = dataSet11.Tables[ "图书信息" ];
			bmData = this.BindingContext[ dataSet11, "图书信息" ];
			if ( bmData.Count != 0 )
				bmData.Position = 0;
		}

		public override void SetDataGrid()
		{				
			LoadData();
			DataGridTableStyle ts = new DataGridTableStyle();
			DataGridTextBoxColumn aCol;
			int numCols = dtData.Columns.Count;
			for ( int i = 0; i < numCols; i ++ )
			{
				aCol = new DataGridTextBoxColumn();
				aCol.MappingName = dtData.Columns[ i ].ColumnName;
				aCol.HeaderText = dtData.Columns[ i ].ColumnName;
				aCol.NullText = "";
				aCol.ReadOnly = true;
				ts.GridColumnStyles.Add( aCol );
			}
			ts.AlternatingBackColor = Color.LightGray;
			ts.AllowSorting = false;
			ts.MappingName = dtData.TableName;
			dgdList.TableStyles.Clear();
			dgdList.TableStyles.Add( ts );
			DataView dv = dtData.DefaultView;
			dv.AllowNew = false;
			dv.AllowDelete = false;
			dgdList.DataSource = dv;
			bmData = this.BindingContext[ dataSet11, "图书信息" ];
			lblCount.Text = "记录数:" + dtData.Rows.Count.ToString();
		}

		public override void Clear()
		{
			txbAuthor.Text = "";
			txbCode.Text = "";
			txbISBN.Text = "";
			txbName.Text = "";
			txbNumber.Text = "";
			txbRemark.Text = "";
			txbTotalPages.Text = "";
			txbTranslator.Text = "";
			txbWorker.Text = "";
			txbPrice.Text = "";
			cbbBookcase.Text = "";
			cbbPress.Text = "";
			cbbType.Text = "";
			ckbRegist.Checked = false;
		}

		public override void SetTextBoxState(bool bState)
		{
			GroupBox1.Enabled = !bState;
		}

		public override void SaveForAdd()
		{
			DataRow dr = dataSet11.Tables[ "图书信息" ].NewRow();
			try
			{
				dr[ "条形码" ] = txbCode.Text.Trim();
				dr[ "编号" ] = txbNumber.Text.Trim();
				dr[ "书名" ] = txbName.Text.Trim();
				dr[ "类型" ] = cbbType.Text.Trim();
				dr[ "作者" ] = txbAuthor.Text.Trim();
				dr[ "译者" ] = txbTranslator.Text.Trim();
				dr[ "ISBN" ] = txbISBN.Text.Trim();
				dr[ "出版社" ] = cbbPress.Text.Trim();
				dr[ "价格" ] = txbPrice.Text.Trim();
				dr[ "页码" ] = txbTotalPages.Text.Trim();
				dr[ "操作员" ] = txbWorker.Text.Trim();
				dr[ "是否注销" ] = ckbRegist.Text.Trim();
				dr[ "书架名称" ] = cbbBookcase.Text.Trim();
				dr[ "简介" ] = txbRemark.Text.Trim();
			}
			catch ( Exception ex )
			{
				MessageBox.Show( ex.ToString() );
				return;
			}
			try
			{
				dataSet11.Tables[ "图书信息" ].Rows.Add( dr );
				if ( dataSet11.HasChanges() )
					sqlDataAdapter1.Update( dataSet11 );
			}
			catch ( Exception ex )
			{
				ex.ToString();
			}
			LoadData();
			dgdList.DataSource = dtData.DefaultView;
		}

		public override void DeleteData()
		{
			try
			{
				dataSet11.Tables[ "图书信息" ].Rows[ bmData.Position ].Delete();
				if ( dataSet11.HasChanges() )
					sqlDataAdapter1.Update( dataSet11 );
			}
			catch ( Exception ex )
			{
				MessageBox.Show( ex.ToString() );
			}
		}

		public override void SaveForUpdate()
		{
			try
			{
				int row = dgdList.CurrentCell.RowNumber;
				dgdList.CurrentCell = new DataGridCell( ( row + 1 ) % bmData.Count, 0 );
				if ( dataSet11.HasChanges() )
					sqlDataAdapter1.Update( dataSet11 );
			}
			catch ( Exception ex )
			{
				MessageBox.Show( ex.ToString() );
			}
		}

	}
}