dbaccess.cs

ppc上的sqlce数据库c#接口类

using System;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.IO;
using System.ComponentModel;


namespace PDACheck
{
	/// <summary>
	/// 用于访问数据库
	/// </summary>
	 public enum INTERR
	{
		IntErr = 54321,
	}
	public class DBAccess
	{
		/// <summary>
		/// 连接数据库
		/// </summary>
		private SqlCeConnection m_sccConn;
		/// <summary>
		/// 发送数据库命令
		/// </summary>
		private SqlCeCommand m_scmCom;
		/// <summary>
		/// 查询结果获得
		/// </summary>
		//private SqlCeDataReader m_scdrReader;
		/// <summary>
		/// 记录影响行数
		/// </summary>
		private int m_iCount;

		/// <summary>
		/// 连接适配器
		/// </summary>
		private SqlCeDataAdapter m_scdaAdapter;

		/// <summary>
		/// 数据集
		/// </summary>
		private DataSet m_dsDataSet;
		/// <summary>
		///是否成功连接数据库 
		/// </summary>
		public bool m_bSuc;

		/// <summary>
		/// 索引
		/// </summary>
		public int m_iIndex;

		

		public DBAccess()
		{
			try
			{
				//连接数据库
				m_sccConn = new SqlCeConnection( "Data Source = ElectricDB.sdf");

				//打开数据库
				m_sccConn.Open();

				//创建命令传输控件
				m_scmCom = m_sccConn.CreateCommand();

				this.m_scdaAdapter = new SqlCeDataAdapter();
				
				this.m_scdaAdapter.SelectCommand = this.m_scmCom;

				this.m_dsDataSet = new DataSet();
								
				m_bSuc = true;
				
			}
			catch( SqlCeException err)
			{
				//捕获错误码
				string errorMessages ="";
				
				for (int i=0; i < err.Errors.Count; i++)
				{
					errorMessages += "索引 #" + i + "\n" +
						"消息： " + err.Errors[i].Message + "\n" +
						"源： " + err.Errors[i].Source + "\n" +
						"错误码： " + err.Errors[i].NativeError.ToString() + "\n";
				}
				if( err.Errors[0].NativeError.ToString() == "25035" )
				{
					MessageBox.Show( "有其他的程序在访问数据库，请先终止该程序再运行本程序。","提示");
					
				}
				else
				{
					MessageBox.Show( errorMessages,"ERROR");
				}
				m_bSuc = false;
			}
			
		}
		public DBAccess( string sConnection )
		{
			try
			{
				//连接数据库
				m_sccConn = new SqlCeConnection( sConnection );

				//打开数据库
				m_sccConn.Open();

				//创建命令传输控件
				m_scmCom = m_sccConn.CreateCommand();

				this.m_scdaAdapter = new SqlCeDataAdapter();
				
				this.m_scdaAdapter.SelectCommand = this.m_scmCom;

				this.m_dsDataSet = new DataSet();
								
				m_bSuc = true;
				
			}
			catch( SqlCeException err )
			{
				//捕获错误码
				string errorMessages ="";
				
				for (int i=0; i < err.Errors.Count; i++)
				{
					errorMessages += "索引 #" + i + "\n" +
						"消息： " + err.Errors[i].Message + "\n" +
						"源： " + err.Errors[i].Source + "\n" +
						"错误码： " + err.Errors[i].NativeError.ToString() + "\n";
				}
				if( err.Errors[0].NativeError.ToString() == "25035" )
				{
					MessageBox.Show( "有其他的程序在访问数据库，请先终止该程序再运行本程序。","提示");
					
				}
				else
				{
					MessageBox.Show( errorMessages,"ERROR");
				}
				m_bSuc = false;
			}
			
		}
		//是否连接成功
		public bool GetAccessStatus()
		{
			return m_bSuc;
		}
		/// <summary>
		/// 激活并执行SQL语句
		/// </summary>
		public bool Active(string sSql)
		{
			try
			{

				this.m_scmCom.CommandText = sSql ;
				
				//清空数据集
				this.m_dsDataSet.Clear();

				//填充数据集
				this.m_scdaAdapter.Fill( this.m_dsDataSet );

				//记录索引
				this.m_iIndex = 0;

				//数据集记录数
				this.m_iCount = this.m_dsDataSet.Tables[0].Rows.Count;
			
				/*
				this.m_scdrReader = this.m_scmCom.ExecuteReader();
				this.m_scdrReader.Read();

				MessageBox.Show( this.m_scdrReader.GetString(0) );
				this.m_iCount = this.m_scdrReader.RecordsAffected;*/
				return true;
			}
			catch( SqlCeException err )
			{
				//捕获错误码
				string errorMessages ="";
				
				for (int i=0; i < err.Errors.Count; i++)
				{
					errorMessages += "索引 #" + i + "\n" +
						"消息： " + err.Errors[i].Message + "\n" +
						"源： " + err.Errors[i].Source + "\n" +
						"错误码： " + err.Errors[i].NativeError.ToString() + "\n";
				}
				MessageBox.Show( errorMessages,"ERROR");
				return false;
			}
		}
		/// <summary>
		/// 是否结束
		/// </summary>
		/// <returns>返回是否结束</returns>
		public bool GetEof()
		{
			if( this.m_iIndex >= this.m_iCount  )
			{
				return true;
			}
			return false;
		}
		/// <summary>
		/// 获得字符串型记录
		/// </summary>
		/// <param name="iFieldIndex">字段索引</param>
		/// <returns>返回字符型数据</returns>
		public string GetDataString( int iFieldIndex )
		{
			try
			{
				DataRow drRow = this.m_dsDataSet.Tables[ 0 ].Rows[ this.m_iIndex ] ;
			
				return drRow[iFieldIndex].ToString();
				//return this.m_dsDataSet.Tables[ 0 ].Columns[0].ToString();
			
		//		return this.m_scdrReader.GetString( iFieldIndex );
			}
			catch( SqlCeException err )
			{
				//捕获错误码
				string errorMessages ="";
				
				for (int i=0; i < err.Errors.Count; i++)
				{
					errorMessages += "索引 #" + i + "\n" +
						"消息： " + err.Errors[i].Message + "\n" +
						"源： " + err.Errors[i].Source + "\n" +
						"错误码： " + err.Errors[i].NativeError.ToString() + "\n";
				}
				MessageBox.Show( errorMessages,"ERROR");
				return "";
			}
			
		}
		/// <summary>
		/// 获得整型记录
		/// </summary>
		/// <param name="iFieldIndex">字段索引</param>
		/// <returns>返回整型数据</returns>
		public int GetDataInt( int iFieldIndex )
		{
			try
			{
				DataRow drRow = this.m_dsDataSet.Tables[0].Rows[ this.m_iIndex ];
				
				//string ss = drRow[iFieldIndex].ToString();
				int db = Convert.ToInt32( drRow[iFieldIndex] );
				return db;
			}
			catch( SqlCeException err )
			{
				//捕获错误码
				string errorMessages ="";
				
				for (int i=0; i < err.Errors.Count; i++)
				{
					errorMessages += "索引 #" + i + "\n" +
						"消息： " + err.Errors[i].Message + "\n" +
						"源： " + err.Errors[i].Source + "\n" +
						"错误码： " + err.Errors[i].NativeError.ToString() + "\n";
				}
				MessageBox.Show( errorMessages,"ERROR");
				return (int)INTERR.IntErr;
			}
			catch( Exception err )
			{
				MessageBox.Show( "数据类型错误或者数据空！","ERROR");
				return (int)INTERR.IntErr;
			}
			
		}
		/// <summary>
		/// 获得浮点型记录
		/// </summary>
		/// <param name="iFieldIndex">字段索引</param>
		/// <returns>返回浮点型数据</returns>
		public float GetDataFloat( int iFieldIndex )
		{
			try
			{
				DataRow drRow = this.m_dsDataSet.Tables[0].Rows[ this.m_iIndex ];
				
				double db = (double)drRow[ iFieldIndex ];
				return (float)db;
			}
			catch( SqlCeException err )
			{
				//捕获错误码
				string errorMessages ="";
				
				for (int i=0; i < err.Errors.Count; i++)
				{
					errorMessages += "索引 #" + i + "\n" +
						"消息： " + err.Errors[i].Message + "\n" +
						"源： " + err.Errors[i].Source + "\n" +
						"错误码： " + err.Errors[i].NativeError.ToString() + "\n";
				}
				MessageBox.Show( errorMessages,"ERROR");
				return 0;
			}
			
		}
		public DateTime GetDataDateTime( int iFieldIndex )
		{
			try
			{
				DateTime dtData;
				DataRow drRow = this.m_dsDataSet.Tables[0].Rows[ this.m_iIndex ];
				
				
				dtData = Convert.ToDateTime(drRow[iFieldIndex]);   
				
				
				/*
				string sDateTime = drRow[ iFieldIndex ].ToString();
				
				
				long fDateTime = StringToInt( sDateTime.Substring( 0 , 4 ));
				sDateTime = sDateTime.Substring( 5 );
				fDateTime = fDateTime * 100 + StringToInt( sDateTime.Substring( 0 , 2 ) );
				sDateTime = sDateTime.Substring( 3 );
				fDateTime = fDateTime * 100 + StringToInt( sDateTime.Substring( 0 , 2 ) );
				sDateTime.Trim();
				if( sDateTime.Length == 0 )
				{
					fDateTime *= 1000000;
				}
				else
				{
					fDateTime = fDateTime * 100 + StringToInt( sDateTime.Substring( 0 , 2 ) );
					sDateTime.Substring( 3 );
					fDateTime = fDateTime * 100 + StringToInt( sDateTime.Substring( 0 , 2 ) );
					sDateTime.Substring(3);
					fDateTime = fDateTime * 100 + StringToInt( sDateTime.Substring( 0 , 2 ) );
				}
				dtData = new DateTime( fDateTime );*/
				return dtData;
			}
			catch( SqlCeException err )
			{
				//捕获错误码
				string errorMessages ="";
				
				for (int i=0; i < err.Errors.Count; i++)
				{
					errorMessages += "索引 #" + i + "\n" +
						"消息： " + err.Errors[i].Message + "\n" +
						"源： " + err.Errors[i].Source + "\n" +
						"错误码： " + err.Errors[i].NativeError.ToString() + "\n";
				}
				MessageBox.Show( errorMessages,"ERROR");
				return new DateTime(0,0,0,0,0,0);
			}
		}
		/*
		private int StringToInt( string sString )
		{
			sString = sString.Trim();
			int iResult = 0;
			int iCount = sString.Length ; 
			char [] cBuff = new char[ iCount ];

			cBuff = sString.ToCharArray();
			for( int i = 0 ; i < iCount ; i++ )
			{
				iResult = iResult * 10 + (cBuff[i] - '0');
			}
			return iResult;
		}
*/
		/// <summary>
		/// 下一记录
		/// </summary>
		/// <returns>操作成功与否</returns>
		public bool MoveNext()
		{
			if ( this.m_iIndex < this.m_iCount  )
			{
				this.m_iIndex++;
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 获得记录数
		/// </summary>
		/// <returns>记录数</returns>
		public int GetRecordCount()
		{
			return this.m_iCount;
		}
		/// <summary>
		/// 执行返回值的sql语句
		/// </summary>
		/// <param name="?">sql语句</param>
		/// <returns>是否执行成功</returns>
		public bool ExecuteReader( string sSql )
		{
			try
			{

				this.m_scmCom.CommandText = sSql ;

				//清空数据集
				this.m_dsDataSet.Clear();
				this.m_dsDataSet.Tables.Clear();
				this.m_iCount = -1;
				
				//填充
				this.m_scdaAdapter.Fill( this.m_dsDataSet );

				//记录索引
				this.m_iIndex = 0;

				//数据集记录数
				this.m_iCount = this.m_dsDataSet.Tables[0].Rows.Count;

				/*if( !this.m_scdrReader.IsClosed )
				{
					this.m_scdrReader.Close();
					
				}
				this.m_scdrReader = this.m_scmCom.ExecuteReader();
				this.m_iCount = this.m_scdrReader.RecordsAffected;*/
				return true;
			}
			catch( SqlCeException err )
			{
				//捕获错误码
				string errorMessages ="";
				
				for (int i=0; i < err.Errors.Count; i++)
				{
					errorMessages += "索引 #" + i + "\n" +
						"消息： " + err.Errors[i].Message + "\n" +
						"源： " + err.Errors[i].Source + "\n" +
						"错误码： " + err.Errors[i].NativeError.ToString() + "\n";
				}
				MessageBox.Show( errorMessages,"ERROR");
				return false;
			}

		}
		/// <summary>
		/// 执行无返回记录的sql语句
		/// </summary>
		/// <param name="sSql">sql语句</param>
		/// <returns>是否执行成功</returns>
		public bool ExecuteNonQuery( string sSql )
		{
			try
			{
				this.m_scmCom.CommandText = sSql ;
				this.m_scmCom.ExecuteNonQuery();
				return true;
			}
			catch( SqlCeException err )
			{
				//捕获错误码
				string errorMessages ="";
				
				for (int i=0; i < err.Errors.Count; i++)
				{
					errorMessages += "索引 #" + i + "\n" +
						"消息： " + err.Errors[i].Message + "\n" +
						"源： " + err.Errors[i].Source + "\n" +
						"错误码： " + err.Errors[i].NativeError.ToString() + "\n";
				}
				MessageBox.Show( errorMessages,"ERROR");
				
			}
			return false;
		}
		/// <summary>
		/// 获得字段数
		/// </summary>
		/// <returns>字段数</returns>
		public int GetFieldCount()
		{
			return this.m_dsDataSet.Tables[0].Columns.Count;
		}
		/// <summary>
		/// 获得字段名
		/// </summary>
		/// <param name="iIndex">索引</param>
		/// <returns>字段名</returns>
		public string GetFieldName( int iIndex )
		{
			return this.m_dsDataSet.Tables[0].Columns[ iIndex ].ColumnName;
		}
		/// <summary>
		/// 按字段名获得记录
		/// </summary>
		/// <param name="sField">字段名</param>
		/// <returns>字符串记录</returns>
		public string GetStringByField( string sField )
		{
			//return this.m_iCount;
			DataRow drRow = this.m_dsDataSet.Tables[ 0 ].Rows[ this.m_iIndex ] ;
			return drRow[ sField ].ToString();
		 
		}

		/// <summary>
		/// 依照字段名获取字段类型
		/// </summary>
		/// <param name="sField">字段名</param>
		/// <returns>字段类型</returns>
		public string GetFieldType( string sField )
		{
			DataColumn dc  = this.m_dsDataSet.Tables[0].Columns[ sField ];
			return dc.DataType.ToString();
		}
		/// <summary>
		/// 依照字段索引获得字段类型
		/// </summary>
		/// <param name="iIndex">字段索引</param>
		/// <returns>字段类型</returns>
		public string GetFieldType( int iIndex )
		{
			DataColumn dc  = this.m_dsDataSet.Tables[0].Columns[ iIndex ];
			return dc.DataType.ToString();
		}

	}
}