billinvests.cs

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

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;
namespace DataAccess
{
	public class BillInvests
	{
		private readonly string connection = "server=.;database=homemoney;uid=sa;pwd=";
		private readonly string tablename = "T_BillInvest";
		private SqlDataAdapter dataAdapter = null;
		private SqlConnection cn = null;

		private SqlConnection Connection
		{
			get
			{
				if (cn == null || cn.State == ConnectionState.Closed)
					cn = new SqlConnection(connection);
				return cn;
			}
		}
		public DataSet GetBillInvests(string aid)
		{
			//要返回的数据集
			DataSet ds = new DataSet();
			//构造插入数据的SQL语句
			string sql = string.Format("SELECT 交易日期=BIDate,代码=BICode,名称=BIName+'['+BIItem+']',份数=BICopys,交易价格=BIPrice ,方向=BIType,交易金额=BIPrice*BICopys,说明=BRemarks FROM T_BillInvest WHERE BIAID={0}", aid);
			if (dataAdapter == null) dataAdapter = new SqlDataAdapter(sql, connection);
			try
			{
				dataAdapter.Fill(ds, this.tablename);
			}
			catch (SqlException e)
			{
				Console.WriteLine(e.Message);
			}
			return ds;
		}
		
		public bool Insert(BillInvest t)
		{
			//构造插入数据的SQL语句
			string sql = string.Format("INSERT INTO T_BillInvest VALUES({0},'{1}','{2}',{3},{4},'{5}','{6}','{7}','{8}','{9}')",
			t.BIAID, t.BIName, t.BICode,t.BICopys,t.BIPrice, t.BIType,t.BIDate,t.BIItem,t.BIUser,t.BRemarks
			);
			SqlCommand cmd = new SqlCommand(sql, Connection);
			try
			{
				//插入数据库
				cmd.Connection.Open();
				cmd.ExecuteNonQuery();
			}
			catch (SqlException e)
			{
				Console.WriteLine(e.Message);
				return false;
			}
			finally
			{
				cmd.Connection.Close();
			}
			return true;
		}
	}
}