frmconsume.cs

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

			this.sqlDataAdapter1.UpdateCommand = this.sqlUpdateCommand1;
			// 
			// sqlDeleteCommand1
			// 
			this.sqlDeleteCommand1.CommandText = @"DELETE FROM 帐单明细 WHERE (帐单编号 = @Original_帐单编号) 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)";
			this.sqlDeleteCommand1.Connection = this.sqlConnection1;
			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, 36, 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, 80, 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.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.Money, 8, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "消费金额", System.Data.DataRowVersion.Original, null));
			// 
			// sqlConnection1
			// 
			this.sqlConnection1.ConnectionString = "workstation id=ACER;packet size=4096;integrated security=SSPI;data source=ACER;pe" +
				"rsist security info=False;initial catalog=hotelbook";
			// 
			// sqlInsertCommand1
			// 
			this.sqlInsertCommand1.CommandText = "INSERT INTO 帐单明细(入住单号, 消费内容, 消费金额, 消费时间, 备注) VALUES (@入住单号, @消费内容, @消费金额, @消费时间, " +
				"@备注); SELECT 帐单编号, 入住单号, 消费内容, 消费金额, 消费时间, 备注 FROM 帐单明细 WHERE (帐单编号 = @@IDENTITY" +
				")";
			this.sqlInsertCommand1.Connection = this.sqlConnection1;
			this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@入住单号", System.Data.SqlDbType.VarChar, 36, "入住单号"));
			this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@消费内容", System.Data.SqlDbType.VarChar, 40, "消费内容"));
			this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@消费金额", System.Data.SqlDbType.Money, 8, "消费金额"));
			this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@消费时间", System.Data.SqlDbType.DateTime, 4, "消费时间"));
			this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@备注", System.Data.SqlDbType.VarChar, 80, "备注"));
			// 
			// sqlSelectCommand1
			// 
			this.sqlSelectCommand1.CommandText = "SELECT 帐单编号, 入住单号, 消费内容, 消费金额, 消费时间, 备注 FROM 帐单明细";
			this.sqlSelectCommand1.Connection = this.sqlConnection1;
			// 
			// sqlUpdateCommand1
			// 
			this.sqlUpdateCommand1.CommandText = @"UPDATE 帐单明细 SET 入住单号 = @入住单号, 消费内容 = @消费内容, 消费金额 = @消费金额, 消费时间 = @消费时间, 备注 = @备注 WHERE (帐单编号 = @Original_帐单编号) 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); SELECT 帐单编号, 入住单号, 消费内容, 消费金额, 消费时间, 备注 FROM 帐单明细 WHERE (帐单编号 = @帐单编号)";
			this.sqlUpdateCommand1.Connection = this.sqlConnection1;
			this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@入住单号", System.Data.SqlDbType.VarChar, 36, "入住单号"));
			this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@消费内容", System.Data.SqlDbType.VarChar, 40, "消费内容"));
			this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@消费金额", System.Data.SqlDbType.Money, 8, "消费金额"));
			this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@消费时间", System.Data.SqlDbType.DateTime, 4, "消费时间"));
			this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@备注", System.Data.SqlDbType.VarChar, 80, "备注"));
			this.sqlUpdateCommand1.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.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_入住单号", System.Data.SqlDbType.VarChar, 36, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "入住单号", System.Data.DataRowVersion.Original, null));
			this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_备注", System.Data.SqlDbType.VarChar, 80, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "备注", System.Data.DataRowVersion.Original, null));
			this.sqlUpdateCommand1.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.sqlUpdateCommand1.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.sqlUpdateCommand1.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.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@帐单编号", System.Data.SqlDbType.Int, 4, "帐单编号"));
			// 
			// frmConsume
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.ClientSize = new System.Drawing.Size(506, 350);
			this.Controls.Add(this.GroupBox1);
			this.Name = "frmConsume";
			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

		private void btnOpen_Click(object sender, System.EventArgs e)
		{
			frmReside dlgSelect = new frmReside();
			dlgSelect.bClose = true;
			dlgSelect.ToolBar1.Buttons[ 4 ].Enabled = false;
			dlgSelect.ToolBar1.Buttons[ 5 ].Enabled = false;
			dlgSelect.ToolBar1.Buttons[ 6 ].Enabled = false;
			dlgSelect.ToolBar1.Buttons[ 7 ].Enabled = false;
			dlgSelect.ShowDialog();
			txbInId.Text = dlgSelect.GetInfo();
		}

		public override void Prepare()
		{
			this.Text = "消费记账";
			Label1.Text = "入住单号";
			Label2.Text = "消费内容";
			Label3.Visible = false;
			txbThree.Visible = false;
		}

		public override void LoadData()
		{
			string strFilter = "";
			if ( txbOne.Text.Trim().Length == 0 )
			{
				if ( txbTwo.Text.Trim().Length != 0 )
				{
					strFilter = "where 消费内容 like '";
					strFilter += txbTwo.Text.Trim() + "%'";
				}
			}
			else
			{
				strFilter = "where 入住单号 like '";
				strFilter += txbOne.Text.Trim() + "%'";
				if ( txbTwo.Text.Trim().Length != 0 )
				{
					strFilter += " and 消费内容 like '";
					strFilter += txbTwo.Text.Trim() + "%'";
				}
			}
			// 清空dataSet11
			dataSet11.Clear();
			sqlConnection1.ConnectionString = DataBase.sConn;
			// string strSQL = "";
			sqlDataAdapter1.SelectCommand.CommandText = "select * from 帐单明细 " + strFilter;
			// 根据查询条件重新填充dataSet11
			sqlDataAdapter1.Fill( dataSet11 );
			dtData = dataSet11.Tables[ "帐单明细" ];
			bmData = this.BindingContext[ dataSet11, "帐单明细" ];
			if ( bmData.Count != 0 )
				bmData.Position = 0;
		}

		public override void SetDataGrid()
		{
			LoadData();
			// 定义一个DataGrid表样式
			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;
			// dv.AllowEdit = false;
			// 设置数据源
			dgdList.DataSource = dv;
			bmData = this.BindingContext[ dataSet11, "帐单明细" ];
			lblCount.Text = "记录数:" + dtData.Rows.Count.ToString();
		}

		public override void Clear()
		{
			txbInId.Text = "";
			txbMoney.Text = "";
			txbRemark.Text = "";
			tmConsume.Value = DateTime.Now;
			cbbContent.Text = "";
		}

		public override void SaveForAdd()
		{
			DataRow dr = dataSet11.Tables[ "帐单明细" ].NewRow();
			try
			{
				dr[ "入住单号" ] = txbInId.Text.Trim();
				dr[ "消费内容" ] = cbbContent.Text.Trim();
				dr[ "消费金额" ] = txbMoney.Text.Trim();
				dr[ "消费时间" ] = tmConsume.Value.ToString();
				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 )
			{
				MessageBox.Show( "数据添加失败!" + ex.ToString() );
			}
			LoadData();
			dgdList.DataSource = dtData.DefaultView;
		}

		public override void DeleteData()
		{
			try
			{
				// 删除当前行的数据
				dataSet11.Tables[ "帐单明细" ].Rows[ bmData.Position ].Delete();
				if ( dataSet11.HasChanges() )
				{
					// 更新dataSet11
					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() );
			}
		}
	}
}