sql.cs

中小型书店图书购书系统

using System;
using System.Windows.Forms;
using System.Data.OleDb;

namespace 图书管理系统
{
	/// <summary>
	/// SQL 的摘要说明。
	/// </summary>
	public class SQL
	{
		OleDbConnection myConn;
		OleDbCommand myCmd;
		OleDbDataReader myDataReader;
		private int position;
		public object [,]rows;
		private string lastSelect;

		public SQL(string connectionString)
		{
			myConn=new OleDbConnection(connectionString);
			myCmd=new OleDbCommand();
			myCmd.Connection=myConn;
			myDataReader=null;
			position=0;
			rows=new object[0,0];
			lastSelect="";
		}

		private int GetColumnNumber(string commandText)
		{
			try
			{
				myConn.Open();
				myCmd.CommandText=commandText;
				myDataReader=myCmd.ExecuteReader();
				int Col_Number=myDataReader.FieldCount;
				return  Col_Number;
			}
			catch
			{
				return -1;
			}
			finally
			{
				myConn.Close();
			}
		}

		private int GetRowNumber(string commandText)
		{
			try
			{
				myConn.Open();
				myCmd.CommandText=commandText;
				myDataReader=myCmd.ExecuteReader();
				int Row_Number=0;
				while(myDataReader.Read()) ++Row_Number;
				return Row_Number;
			}
			catch
			{
				return -1;
			}
			finally
			{
				myConn.Close();
			}
		}

		private bool Select(string commandText)
		{

			position=0;
			int Row_Pos=0;
			int Col_Pos=0;
			int Row_Number=GetRowNumber(commandText);
			int Col_Number=GetColumnNumber(commandText);
			rows=new object[Row_Number,Col_Number];
			try
			{
				myConn.Open();
				myCmd.CommandText=commandText;
				myDataReader=myCmd.ExecuteReader();
				while(myDataReader.Read())
				{
					for(Col_Pos=0;Col_Pos<Col_Number;Col_Pos++)
						rows[Row_Pos,Col_Pos]=myDataReader[Col_Pos];
					++Row_Pos;
				}
				lastSelect=commandText;
				return true;
			}
			catch
			{
				return false;
			}
			finally
			{
				myConn.Close();
			}
		}

		private bool NonQuery(string commandText)
		{
			try
			{
				myConn.Open();
				myCmd.CommandText=commandText;
				myCmd.ExecuteNonQuery();
				return true;
			}
			catch(Exception e1)
			{
				MessageBox.Show(e1.Message);
				myConn.Close();
				return false;
			}
			finally
			{
				myConn.Close();
			}
		}

		private string trimSpace(string str)
		{
			while(str.Substring(0,1)==" ") str=str.Substring(1);
			return str;
		}

		public bool ExecuteSQL(string commandText)
		{
			commandText=trimSpace(commandText);
			if(commandText.Substring(0,6).ToLower()=="select") return Select(commandText);
			else
			{
				if(NonQuery(commandText)==false)
				{
					if(lastSelect!="") Select(lastSelect);
					return false;
				}
				else 
				{
					if(lastSelect!="") Select(lastSelect);
					return true;
				}
			}
		}

		public bool pointToNext()
		{
			if(position<rows.GetLength(0)-1)
			{
				++position;
				return true;
			}
			else return false;
		}

		public bool pointToPrev()
		{
			if(position>0)
			{
				--position;
				return true;
			}
			else return false;
		}

		public bool pointToFirst()
		{
			if(rows.GetLength(0)!=0)
			{
				position=0;
				return true;
			}
			else return false;
		}

		public bool pointToLast()
		{
			if(rows.GetLength(0)!=0)
			{
				position=rows.GetLength(0)-1;
				return true;
			}
			else return false;
		}

		public int getPos()
		{
			return position;
		}
	}
}