bizoledb.cs

.Net控件

			{
				intEffect = ExecuteSql(commandText, autoIdFieldName);
				return intEffect;
			}
			catch (Exception ex)
			{
				mTransSuccess = false;
                throw new Exception("错误：操作数据库失败！" + "\n" + ex.Message);
			}
		}

		/// <summary>
		/// 用指定连接执行操作更新查询SQL语句(DELETE/INSERT/UPDATE),返回值：-1:失败    >=0:成功，并返回执行的行数
		/// </summary>
		/// <Input>
		///		<param name="commandText">更新查询SQL语句(DELETE/INSERT/UPDATE)或执行型存储过程</param>
		///		<param name="bizDbCn">数据库连接</param>
		/// </Input>   
		/// <Other>
		/// 这里将定义的_SqlCmd、_OleDbCmd注释掉主要是用已定义类的command对象，便于事务执行，要独立运行，去掉定义注释。
		/// </Other>
		/// <returns>返回值：-1:失败 >=0:成功，并返回执行的行数</returns>

		private int ExecuteSql(string commandText)
		{
			string cmdType;
			int intEffect = -1;
			string errorInfo = "";
			bool isStoredProcedure = false;
			OleDbCommand bizDbCmd = new OleDbCommand();

			//检查第一参数//DELETE/INSERT/UPDATE			
			cmdType = commandText.Trim().Substring(0, 6).ToUpper();
			if (cmdType != "DELETE" && cmdType != "INSERT" && cmdType != "UPDATE") isStoredProcedure = true;

			try
			{
				bizDbCmd.Connection = mBizDbCn;
				bizDbCmd.Transaction = mBizDbTr;
				bizDbCmd.CommandText = commandText;
				bizDbCmd.CommandType = isStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
				if (mBizDbCn.State != ConnectionState.Open) mBizDbCn.Open();
				intEffect = bizDbCmd.ExecuteNonQuery();
			}
			catch(Exception ex)
			{			                
				bizDbCmd.Dispose();
				bizDbCmd = null;
				errorInfo = "错误：更新数据库失败！" + "\n" + ex.Message;
				throw new Exception("错误：操作数据库失败！" + "\n" + ex.Message);
			}	
			bizDbCmd.Dispose();
			bizDbCmd = null;
			return intEffect;
		}


		private int ExecuteSql(string commandText, string autoIdFieldName)
		{
			string cmdType;
			int intEffect = -1;
			string errorInfo = "";
			bool isStoredProcedure = false;
			bool returnIdValue = false;
			string tableName = "";
			OleDbCommand bizDbCmd = new OleDbCommand();

			//检查第一参数//DELETE/INSERT/UPDATE			
			cmdType = commandText.Trim().Substring(0, 6).ToUpper();
			if (cmdType != "DELETE" && cmdType != "INSERT" && cmdType != "UPDATE") isStoredProcedure = true;
			if (cmdType == "INSERT" && autoIdFieldName.Length > 0) 
			{
				tableName = commandText.Trim().Substring(6).Trim();
				tableName = tableName.Substring(4).Trim();
				tableName = tableName.Substring(0, tableName.IndexOf(" ")).Trim();
				if (tableName.Length > 0)
				{
					if (tableName.IndexOf("\"") >= 0) tableName = tableName.Replace("\"", "");
					returnIdValue = true;
				}
			}

			try
			{
				bizDbCmd.Connection = mBizDbCn;
				bizDbCmd.Transaction = mBizDbTr;
				bizDbCmd.CommandText = commandText;
				bizDbCmd.CommandType = isStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
				if (mBizDbCn.State != ConnectionState.Open) mBizDbCn.Open();
                intEffect = bizDbCmd.ExecuteNonQuery();

				if (returnIdValue)
				{
                    intEffect = -1;
                    bizDbCmd.CommandText = "select top 1 " + autoIdFieldName + " from " + tableName + " order by " + autoIdFieldName + " desc";
                    DataSet ds = GetDataSet(bizDbCmd);
                    if (ds != null)
                    {
                        if (ds.Tables[0].Rows.Count > 0)
                            intEffect = Convert.ToInt32(ds.Tables[0].Rows[0][autoIdFieldName].ToString());
                        ds.Dispose();
                        ds = null;
                    }
				}
				else
					intEffect = bizDbCmd.ExecuteNonQuery();
			}
			catch(Exception ex)
			{			                
				bizDbCmd.Dispose();
				bizDbCmd = null;
				errorInfo = "错误：更新数据库失败！" + "\n" + ex.Message;
				throw new Exception("错误：操作数据库失败！" + "\n" + ex.Message);
			}	
			bizDbCmd.Dispose();
			bizDbCmd = null;
			return intEffect;
		}



		/// <summary>
		/// 根据CommandText，返回Command对象
		/// </summary>
		/// <param name="commandText"></param>
		/// <returns></returns>
        public OleDbCommand GetDbCmd(string commandText)
		{
			string cmdType;
			bool isStoredProcedure = false;

			cmdType = commandText.Trim().Substring(0, 6).ToUpper();
			if (cmdType != "DELETE" && cmdType != "INSERT" && cmdType != "UPDATE") isStoredProcedure = true;

			OleDbCommand bizDbCmd = new OleDbCommand(commandText, mBizDbCn, mBizDbTr);
			bizDbCmd.CommandType = isStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
            return bizDbCmd;
		}

		/// <summary>
		/// 将参数加入command对象
		/// </summary>
		/// <param name=""></param>
		/// <returns></returns>
        public void AddParToCmd(OleDbCommand bizDbCmd, string parameterName, ParameterDirection direction, DbType dbType, int size, object parameterValue)
		{
			OleDbParameter bizDbPar = new  OleDbParameter();
			bizDbPar.ParameterName = parameterName;
			bizDbPar.Direction = direction;
			bizDbPar.DbType = dbType;
			bizDbPar.Size = size;
			bizDbPar.Value = parameterValue;
			bizDbCmd.Parameters.Add(bizDbPar);
        }

		/// <summary>
		/// 执行command对象
		/// </summary>
		/// <param name=""></param>
		/// <returns></returns>
        public int ExeDbCmd(OleDbCommand bizDbCmd)
		{
			int intEffect = -1;

			try
			{
				if (mBizDbCn.State != ConnectionState.Open) mBizDbCn.Open();
				intEffect = bizDbCmd.ExecuteNonQuery();
				bizDbCmd.Dispose();
				bizDbCmd = null;				
				return intEffect;
			}
			catch (Exception ex)
			{
				throw new Exception("错误：操作数据库失败！" + "\n" + ex.Message);
			}
		}


		/// <summary>
		/// 执行command对象，Insert操作并指定自动增加的id字段名时，返回操作后的id值
		/// </summary>
		/// <param name=""></param>
		/// <returns></returns>
        public int ExeDbCmd(OleDbCommand bizDbCmd, string autoIdFieldName)
		{
			string commandText;
			int intEffect = -1;
			bool returnIdValue = false;
			string tableName = "";

			try
			{
				commandText = bizDbCmd.CommandText;
				if (commandText.Trim().Substring(0, 6).ToUpper() == "INSERT" && autoIdFieldName.Length > 0) 
				{
					tableName = commandText.ToUpper();
					tableName = tableName.Substring(tableName.IndexOf("INTO") + 4).Trim();
					tableName = tableName.Substring(0, tableName.IndexOf(" ")).Trim();
					if (tableName.Length > 0) returnIdValue = true;
				}

				if (mBizDbCn.State != ConnectionState.Open) mBizDbCn.Open();
                intEffect = bizDbCmd.ExecuteNonQuery();

                if (returnIdValue)
				{
                    intEffect = -1;
                    bizDbCmd.CommandText = "select top 1 " + autoIdFieldName + " from " + tableName + " order by " + autoIdFieldName + " desc";
                    DataSet ds = GetDataSet(bizDbCmd);
                    if (ds != null)
                    {
                        if (ds.Tables[0].Rows.Count > 0)
                            intEffect = Convert.ToInt32(ds.Tables[0].Rows[0][autoIdFieldName].ToString());
                        ds.Dispose();
                        ds = null;
                    }
				}
				bizDbCmd.Dispose();
				bizDbCmd = null;				
				return intEffect;
			}
			catch (Exception ex)
			{
				throw new Exception("错误：操作数据库失败！" + "\n" + ex.Message);
			}
		}

		/// <summary>
		/// 返回指定表中指定序列号字段的值
		/// </summary>
		/// <param name="tableName">序列号数据表名</param>
		/// <param name="snName">序列号名</param>
		/// <returns></returns>
		public int GetNextSerialNo(string tableName, string snName)
		{
			int snValue;
			string sqlInsert;
			string sqlUpdate;
			OleDbDataAdapter bizDbDA = null;
			DataSet ds = new DataSet();
			DataTable dt;
			DataRow dr;


            try
            {
	            bizDbDA = new OleDbDataAdapter("select snName, snValue from " + tableName + " where snName = '" + snName + "'", mBizDbCn);
	            bizDbDA.Fill(ds, tableName);
	            if (ds.Tables.Count == 0)
	            {
		            snValue = 1;
		            dt = new DataTable(tableName);
		            dt.Columns.Add("snName", System.Type.GetType("System.String"));
		            dt.Columns.Add("snValue", System.Type.GetType("System.Int32"));
		            dr = dt.NewRow();
		            dr["snName"] = snName;
		            dr["snValue"] = 1;
		            dt.Rows.Add(dr);
		            ds.Tables.Add(dt);
	            }
	            else if (ds.Tables[tableName].Rows.Count == 0)
	            {
		            snValue = 1;
		            dt = ds.Tables[tableName];
		            dr = dt.NewRow();
		            dr["snName"] = snName;
		            dr["snValue"] = 1;
		            dt.Rows.Add(dr);
	            }
	            else
	            {
		            dr = ds.Tables[tableName].Rows[0];
		            snValue = Convert.ToInt32(dr["snValue"].ToString()) + 1;
		            dr["snValue"] = snValue;
	            }
	            sqlInsert = "insert into " + tableName + " (snName, snValue) Values('" + snName + "', " + snValue.ToString() + ")";
	            sqlUpdate = "update " + tableName + " set snValue = " + snValue.ToString() + " where snName = '" + snName + "'";
	            bizDbDA.InsertCommand = new OleDbCommand(sqlInsert, mBizDbCn);
	            bizDbDA.UpdateCommand = new OleDbCommand(sqlUpdate, mBizDbCn);
	            bizDbDA.Update(ds, tableName);
	            bizDbDA.Dispose();
	            bizDbDA = null;
	            ds.Dispose();
	            ds = null;
            }
            catch (Exception ex)
            {
	            System.Windows.Forms.MessageBox.Show("错误：获取序列号失败！" + "\n" + ex.Message, "提示", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning);
	            snValue = 0;
            }
            return snValue;

		}

		/// <summary>
		/// 根据Command，返回数据集
		/// </summary>
		/// <param name="bizDbCmd">Command对象</param>
		/// <returns></returns>
        public System.Data.DataSet GetDataSet(OleDbCommand bizDbCmd)
		{
			string errorInfo = "";

			DataSet ds = new DataSet();
			OleDbDataAdapter bizDbDA = null;
			try
			{
				bizDbDA = new OleDbDataAdapter();
				bizDbDA.SelectCommand = bizDbCmd;
				bizDbDA.Fill(ds);
				bizDbDA.Dispose();
				bizDbDA = null;
				if (ds != null)
				{
					if (ds.Tables.Count == 0)
						ds = null;
					else if (ds.Tables[0].Rows.Count == 0)
						ds = null;
				}
			}
			catch (Exception ex)
			{
				if (bizDbDA != null)
				{
					bizDbDA.Dispose();
					bizDbDA = null;
				}

				ds.Dispose();
				ds = null;
				errorInfo = "错误：获取数据信息失败！" + "\n" + ex.Message;
			}	
			if (errorInfo != "")
				System.Windows.Forms.MessageBox.Show(errorInfo, "提示", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning);
			return ds;
		
		}

        public System.Data.DataSet GetDataSet(OleDbCommand bizDbCmd, string tableName)
		{
			DataSet ds = GetDataSet(bizDbCmd);
			if (ds != null) ds.Tables[0].TableName = tableName;
			return ds;
		}

		public DataSet GetDataSet(string commandText)
		{
            OleDbCommand bizDbCmd;

			bizDbCmd = new OleDbCommand(commandText, mBizDbCn);
			bizDbCmd.Transaction = mBizDbTr;
			bizDbCmd.CommandText = commandText;
			return GetDataSet(bizDbCmd);
		}

		public DataSet GetDataSet(string commandText, string tableName)
		{
			DataSet ds = GetDataSet(commandText);
			if (ds != null) ds.Tables[0].TableName = tableName;
			return ds;
		}


        /// <summary>
        /// 根据Command，返回数据表结构
        /// </summary>
        /// <param name="bizDbCmd">Command对象</param>
        /// <returns></returns>
        public System.Data.DataSet GetDataSetSchema(OleDbCommand bizDbCmd)
        {
            string errorInfo = "";

            DataSet ds = new DataSet();
            OleDbDataAdapter bizDbDA = null;
            try
            {
                bizDbDA = new OleDbDataAdapter();
                bizDbDA.SelectCommand = bizDbCmd;
                bizDbDA.FillSchema(ds, SchemaType.Mapped);
                bizDbDA.Dispose();
                bizDbDA = null;
            }
            catch (Exception ex)
            {
                if (bizDbDA != null)
                {
                    bizDbDA.Dispose();
                    bizDbDA = null;
                }

                ds.Dispose();
                ds = null;
                errorInfo = "错误：获取数据表结构信息失败！" + "\n" + ex.Message;
            }
            if (errorInfo != "")
                System.Windows.Forms.MessageBox.Show(errorInfo, "提示", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning);
            return ds;

        }

        public DataSet GetDataSetSchema(string commandText)
        {
            OleDbCommand bizDbCmd;

            bizDbCmd = new OleDbCommand(commandText, mBizDbCn);
            bizDbCmd.Transaction = mBizDbTr;
            bizDbCmd.CommandText = commandText;
            return GetDataSetSchema(bizDbCmd);
        }


        public DataSet GetDataSetSchema(string commandText, string tableName)
        {
            DataSet ds = GetDataSetSchema(commandText);
            if (ds != null) ds.Tables[0].TableName = tableName;
            return ds;
        }



		public bool IsDataExisting(string tableName, string condition)
		{
            OleDbCommand bizDbCmd;
			string commandText;
			DataSet ds = null;

			commandText = "select top 1 * from " + tableName;
			if (condition.Trim() != String.Empty) commandText += " where " + condition;
			bizDbCmd = new OleDbCommand(commandText, mBizDbCn);
			bizDbCmd.Transaction = mBizDbTr;
			bizDbCmd.CommandText = commandText;
			ds = GetDataSet(bizDbCmd);
			if (ds != null)
			{
				ds.Dispose();
				ds = null;
				return true;
			}
			else 
				return false;
		}
	}
}