billdailymanager.cs

《C#和.NET第一步》中的财务系统 利用三层结构做的

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;
namespace Business
{
	public class BillDailyManager
	{
		private enum DTColumns { 交易日期, 收支项目, 收入, 支出, 说明, 余额 }
		public DataSet GetBillDailys(string aid)
		{
			string sql = string.Format("SELECT 交易日期=BDDate,收支项目=BDItem,收入=BDINMoney,支出=BDOUTMoney,说明=BDRemarks ,余额=BDBalance FROM T_BillDaily WHERE BAID={0}", aid);
			return new DataAccess.BillDailys().GetBillDailys(sql);
		}
		public bool Insert(BillDaily t)
		{
			return new DataAccess.BillDailys().Insert(t);
		}
		public DataRow CreateDataRow(BillDaily bill, DataTable dtCur)
		{
			float balance = 0;
			if (dtCur.Rows.Count > 0)
			{
				DataRow lastDr = dtCur.Rows[dtCur.Rows.Count - 1];
				balance = Convert.ToSingle(lastDr["余额"]) + (bill.BDINMoney - bill.BDOUTMoney);
			}
			else
			{
				balance = bill.BDINMoney - bill.BDOUTMoney;
			}
			DataRow r = dtCur.NewRow();
			r[0] = bill.BDDate;
			r[1] = bill.BDItem;
			r[2] = bill.BDINMoney;
			r[3] = bill.BDOUTMoney;
			r[4] = bill.BDRemarks;
			r[5] = balance;
			return r;
		}
		public bool Delete(DataSet newDs, string aid)
		{
			DataAccess.BillDailys billDA = new DataAccess.BillDailys();
			string sql = string.Format("SELECT BDID,BAID,BDDate,BDItem,BDINMoney,BDOUTMoney,BDRemarks ,BDBalance FROM T_BillDaily WHERE BAID={0}", aid);

			DataSet oldDs = billDA.GetBillDailys(sql);
			string cmdsql = string.Empty;
			cmdsql = "DELETE FROM dbo.T_BillDaily WHERE BDID=@BDID";
			SqlCommand deleteCommand = new SqlCommand(cmdsql, billDA.Connection);
			deleteCommand.Parameters.Add("@BDID", SqlDbType.Int, 4, "BDID");
			for (int i = 0; i < newDs.Tables[0].Rows.Count; i++)
			{
				DataRow r = newDs.Tables[0].Rows[i];
				if (r.RowState == DataRowState.Deleted)
				{
					oldDs.Tables[0].Rows[i].Delete();
				}
			}
			return billDA.Delete(oldDs, deleteCommand);
		}
		public bool Update(DataSet newDs, string aid)
		{
			DataAccess.BillDailys billDA = new DataAccess.BillDailys();
			string sql = string.Format("SELECT BDID,BAID,BDDate,BDItem,BDINMoney,BDOUTMoney,BDRemarks ,BDBalance FROM T_BillDaily WHERE BAID={0}", aid);

			DataSet oldDs = billDA.GetBillDailys(sql);

			string cmdsql = string.Empty;
			cmdsql += " UPDATE dbo.T_BillDaily";
			cmdsql += " SET BAID = @BAID";
			cmdsql += ",BDINMoney = @BDINMoney";
			cmdsql += ",BDOUTMoney = @BDOUTMoney";
			cmdsql += ",BDItem = @BDItem";
			cmdsql += ",BDDate = @BDDate";
			cmdsql += ",BDRemarks = @BDRemarks";
			cmdsql += ",BDBalance = @BDBalance";
			cmdsql += " WHERE BDID=@BDID";

			SqlCommand updateCommand = new SqlCommand(cmdsql, billDA.Connection);
			updateCommand.Parameters.Add("@BAID", SqlDbType.Int, 4, "BAID");
			updateCommand.Parameters.Add("@BDINMoney", SqlDbType.Money, sizeof(decimal), "BDINMoney");
			updateCommand.Parameters.Add("@BDOUTMoney", SqlDbType.Money, sizeof(decimal), "BDOUTMoney");
			updateCommand.Parameters.Add("@BDItem", SqlDbType.NVarChar, 10, "BDItem");
			updateCommand.Parameters.Add("@BDDate", SqlDbType.DateTime, 8, "BDDate");
			updateCommand.Parameters.Add("@BDRemarks", SqlDbType.NVarChar, 50, "BDRemarks");
			updateCommand.Parameters.Add("@BDBalance", SqlDbType.Float, 4, "BDBalance");
			updateCommand.Parameters.Add("@BDID", SqlDbType.Int, 4, "BDID");

			for (int i = 0; i < newDs.Tables[0].Rows.Count; i++)
			{
				DataRow r = newDs.Tables[0].Rows[i];
				if (r.RowState == DataRowState.Modified)
				{
					DataRow cr = oldDs.Tables[0].Rows[i];
					cr["BDDate"] = r[(int)DTColumns.交易日期];
					cr["BDINMoney"] = r[(int)DTColumns.收入];
					cr["BDItem"] = r[(int)DTColumns.收支项目];
					cr["BDRemarks"] = r[(int)DTColumns.说明];
					cr["BDBalance"] = r[(int)DTColumns.余额];
					cr["BDOUTMoney"] = r[(int)DTColumns.支出];
				}
			}
			return billDA.Update(oldDs, updateCommand);
		}
	}
}