sqldatabase.cs

实现文本的特征提取

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Diagnostics;


namespace Njnu.DAL
{
	/// <summary>
	/// SqlDatabase 的摘要说明。
	/// </summary>	
	sealed internal class SqlDatabase : IDisposable
	{
		private SqlCommand m_Command;

		private static string m_ConnectionString;

		//静态构造函数
		static SqlDatabase()
		{
			m_ConnectionString = @"server=127.0.0.1; database=YellowPageProject_TermSelector; uid=sa; pwd=sa";
		}
		
		//构造函数			
		public SqlDatabase(string sprocName, SqlParameter[] parameters)
		{
			CreateCommandObject(sprocName);
			CreateCommandParams(parameters);
		}
		

		//创建SqlCommand对象，并且初始化。
		private void CreateCommandObject(string sprocName)
		{
			m_Command = new SqlCommand(sprocName, new SqlConnection(m_ConnectionString));

			//判断：如果有空格，为text；没有空格，为StoredProcedure
			if(sprocName.IndexOf(" ") != -1)
				m_Command.CommandType = CommandType.Text;
			else
				m_Command.CommandType = CommandType.StoredProcedure;
		}

		// 为SqlCommand对象创建一组参数。
		private void CreateCommandParams(SqlParameter[] pmeters)
		{
			foreach(SqlParameter parameter in pmeters)
			{
				m_Command.Parameters.Add(parameter);
			}
		}

		#region Run方法: 用以执行存储过程或者SQL语句

		//返回影响行数，执行insert/update等操作   -- by cq
		public int Run()
		{
			if(m_Command == null) throw new ObjectDisposedException(GetType().FullName);
			m_Command.Connection.Open();
			return m_Command.ExecuteNonQuery();			
		}

		//填充一个SqlDataReader对象
		public void Run(out SqlDataReader dr)
		{
			if(m_Command == null) throw new ObjectDisposedException(GetType().FullName);
			m_Command.Connection.Open();
			dr = m_Command.ExecuteReader(CommandBehavior.CloseConnection);
		}

		/// <summary>
		/// 为SqlCommand对象增加一个返回值参数 ReturnValue。
		/// </summary>
		private void CreateReturnValueParam()
		{
			m_Command.Parameters.Add( 
				new SqlParameter("ReturnValue",SqlDbType.Int, 4, ParameterDirection.ReturnValue, 
				false, 0, 0, string.Empty, DataRowVersion.Default,null));
		}

		//填充一个DataTable对象
		public int Run(DataTable dataTable)
		{
			CreateReturnValueParam();

			if(m_Command == null) throw new ObjectDisposedException(GetType().FullName);

			SqlDataAdapter dataAdapter = new SqlDataAdapter();		

			dataAdapter.SelectCommand = m_Command;
			dataAdapter.Fill(dataTable);

			return ( int )dataAdapter.SelectCommand.Parameters[ "ReturnValue" ].Value;
		}

		#endregion		

		#region Get*FromRdr
		/// <summary>
		/// 以下是一些从 SqlDataReader 中读取相应类型内容的函数。
		/// </summary>
		/// <param name="dr"></param>
		/// <param name="strColName">待读取的列名</param>
		/// <returns></returns>
		public bool GetBoolFromRdr(SqlDataReader dr, string strColName)
		{
			int colOrd;
				
			if(IsRdrColumnNull(dr, ref strColName, out colOrd))
				return dr.GetBoolean(colOrd);
			else
				return false;
		}


		public short GetInt16FromRdr(SqlDataReader dr, string strColName)
		{
			int colOrd;
			
			if(IsRdrColumnNull(dr, ref strColName, out colOrd))
				return dr.GetInt16(colOrd);
			else
				return 0;
		}


		public int GetInt32FromRdr(SqlDataReader dr, string strColName)
		{
			int colOrd;
			
			if(IsRdrColumnNull(dr, ref strColName, out colOrd))
				return dr.GetInt32(colOrd);
			else
				return 0;
		}


		public long GetInt64FromRdr(SqlDataReader dr, string strColName)
		{
			int colOrd;
			
			if(IsRdrColumnNull(dr, ref strColName, out colOrd))
				return dr.GetInt64(colOrd);
			else
				return 0;
		}

		public float GetFloatFromRdr(SqlDataReader dr, string strColName)
		{
			int colOrd;
			
			if(IsRdrColumnNull(dr, ref strColName, out colOrd))
				return dr.GetFloat(colOrd);
			else
				return 0;
		}


		public string GetStringFromRdr(SqlDataReader dr, string strColName)
		{
			int colOrd;
			
			if(IsRdrColumnNull(dr, ref strColName, out colOrd))
				return dr.GetString(colOrd);
			else
				return null;
		}

		public DateTime GetDateTimeFromRdr(SqlDataReader dr, string strColName)
		{
			int colOrd;
			
			if(IsRdrColumnNull(dr, ref strColName, out colOrd))
				try
				{ return dr.GetDateTime(colOrd);}
				catch
				{
					// InvalidCastException:
					return DateTime.MinValue;
				}
			else
				return DateTime.MinValue;
		}

		/// <summary>
		/// 检查一个 SqlDataReader 能否读取到某一特定名字的列，如果能，通过参数colOrd返回其位置。
		/// 如果特定名字的列不存在，那么在日志中记录该信息。
		/// </summary>
		/// <param name="dr"></param>
		/// <param name="strColName">列名</param>
		/// <param name="colOrd">列的位置序号</param>
		/// <returns></returns>
		public bool IsRdrColumnNull(SqlDataReader dr, ref string strColName, out int colOrd)
		{
			bool retVal = false;
			colOrd = -1;

			try
			{
				colOrd = dr.GetOrdinal(strColName);
				if(!dr.IsDBNull(colOrd)) retVal = true;
			}
			catch(IndexOutOfRangeException iore)
			{
				StringBuilder strBuild = new StringBuilder();
				strBuild.Append("A method that calls ");
				strBuild.Append(m_Command.CommandText);
				strBuild.Append(" has an invalid column name or index when processing database results with a SqlDataReader.\n\n");
				strBuild.Append("The exception error message is:\n");
				strBuild.Append(iore.ToString());				
			}
			return retVal;
		}
		
		/// <summary>
		/// 确定一个SqlParameter参数是否是 System.DBNull。
		/// </summary>
		/// <param name="param"></param>
		/// <returns></returns>
		internal static bool IsNull(SqlParameter param)
		{
			if(param.Value == System.DBNull.Value) 
				return true;
			else 
				return false;
		}

		#endregion	

		#region IDisposable 成员

		public void Dispose()
		{
			if(m_Command != null)
			{
				if(m_Command.Connection != null)
				{
					m_Command.Connection.Close();
					m_Command.Connection.Dispose();
				}
				m_Command.Dispose();
			}
		}

		#endregion

		#region 静态方法：
		/// <summary>
		/// 产生一个 SqlParameter，并且初始化其值。
		/// </summary>
		/// <param name="paramName"></param>
		/// <param name="dbType">参数类型</param>
		/// <param name="size">参数大小</param>
		/// <param name="direction">参数的输入输出属性</param>
		/// <param name="oValue">参数初始化值</param>
		/// <returns></returns>
		internal static SqlParameter CreateParam(string paramName, SqlDbType dbType, int size, ParameterDirection direction, object oValue)
		{
			SqlParameter sp;
			
			if (size != 0)
				sp = new SqlParameter(paramName, dbType, size);
			else
				sp = new SqlParameter(paramName, dbType);

			sp.Direction = direction;

			//Only assign a value for Input or InputOutput parameters
			if (oValue != null && (direction == ParameterDirection.Input || direction == ParameterDirection.InputOutput))
				sp.Value = oValue;

			return sp;
		}
		#endregion	

	}
 

}