databaseoperate.cs

列车时刻查询Visual Studio 2005 + sql2005

using System;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;

namespace train
{
	/// <summary>
	/// 数据层操作类
	/// </summary>
	public class DataBaseOperate
	{
		private SqlConnection conn;
		private TrainConfiguration config;
		private string path;
		public DataBaseOperate()
		{
			conn=new SqlConnection();
			config=new TrainConfiguration();
			path=Environment.CurrentDirectory+"\\config\\config.txt";
		}


		/// <summary>
		/// 打开数据库连接
		/// </summary>
		/// <returns>返回bool值，true表示连接成功,false表示连接失败</returns>
		public bool ConnectDataBase()
		{
			try
			{
				if(conn.State!=ConnectionState.Open)
				{
					conn.ConnectionString=config.GetConfigFromFile(this.path);
					conn.Open();
					return true;
				}
				else
				{
					return true;
				}
			}
			catch(SqlException e)
			{
				Console.WriteLine(e.Message);
				return false;
			}
		}


		/// <summary>
		/// 关闭数据库连接
		/// </summary>
		/// <returns>返回bool值，true表示关闭成功,false表示关闭失败</returns>
		public bool CloseDataBase()
		{
			try
			{
				if(conn.State!=ConnectionState.Closed)
				{
					conn.Close();
				}
				return true;
				
			}
			catch(SqlException)
			{
				return false;
			}
		}

		/// <summary>
		/// 根据sql语句执行数据库查询
		/// </summary>
		/// <param name="querysql">要执行的sql语句</param>
		/// <returns>返回一个DataTable集合</returns>
		public DataTable search(string querysql)
		{
			if(conn.State!=ConnectionState.Open)
			{
				ConnectDataBase();
			}
			ConnectDataBase();
			SqlCommand cmd=new SqlCommand(querysql,conn);
			SqlDataReader dr=null;
			try
			{
				dr=cmd.ExecuteReader();
			}
			catch(SqlException e)
			{
				Console.WriteLine(e.Message);
				return null;
			}
			//根据DataReader重组一个DataTable
			DataTable table=new DataTable();
			int colum=dr.FieldCount;
			table.BeginLoadData();
			for(int i=0;i<colum;i++)
			{
				table.Columns.Add(dr.GetName(i),dr.GetType());
			}
			object[] values=new object[colum];
			while(dr.Read())
			{
				DataRow row=table.NewRow();
				dr.GetValues(values);
				for(int i=0;i<values.Length;i++)
				{
					row[i]=values[i];
				}
				table.Rows.Add(row);
			}
			table.EndLoadData();
			dr.Close();
			return table;
		}
	}
}