dbhelper.cs

一个用于C#开发的

//using System.Data.SqlClient;
using System.Data.OleDb;
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace CIndustry
{
	/// <summary>
	/// 数据库处理
	/// </summary>
	public class DbHelper : System.Web.UI.Page
	{
//		public string src="user id=saa;password=saa;initial catalog=cmindex;data source=.;Connect Timeout=30";
		public DbHelper()
		{
			string DataPath =Server.MapPath(@"bin\cm.mdb");
			string passw = "crccsad321";
//			sCntString="user id=saa;password=saa;initial catalog=guestbook;data source=.;Connect Timeout=30";
			sCntString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "+DataPath+";Jet OLEDB:Database Password =  "+passw+";Mode = Share Exclusive";
		}
		/// <summary>
		/// 数据库连接字符串
		/// </summary>
		private string sCntString="";
		/// <summary>
		/// 执行SQL语句
		/// </summary>
		/// <param name="ssql">所要执行的SQL 语句</param>
		public void ExecuteSQL( string ssql)
		{
			//			SqlConnection myConnection = new SqlConnection(sCntString);
			//			SqlCommand myCommand = new SqlCommand(ssql, myConnection );
			try
			{
				OleDbConnection myConnection = new OleDbConnection(sCntString);
				OleDbCommand myCommand = new OleDbCommand(ssql, myConnection );
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
				myConnection.Dispose();	
			}
			catch(Exception ee)
			{
				string er = ee.ToString();
			}
		}
		/// <summary>
		/// 执行多条SQL语句,失败则回滚
		/// </summary>
		/// <param name="ssql">所要执行的SQL 语句</param>
		public void ExecuteSQL( string[] sqls)
		{
//			SqlConnection myConnection = new SqlConnection(sCntString);
			OleDbConnection myConnection = new OleDbConnection(sCntString);
			myConnection.Open();
//			SqlTransaction myTrans = myConnection.BeginTransaction();
			OleDbTransaction myTrans = myConnection.BeginTransaction();
//			SqlCommand myCommand = myConnection.CreateCommand();
			OleDbCommand myCommand = myConnection.CreateCommand();
			myCommand.Transaction=myTrans;
			
			try
			{
				for(int i=0;i<sqls.Length;i++)
				{
					myCommand.CommandText = sqls[i];
					myCommand.ExecuteNonQuery();
				}
				myTrans.Commit();
			}
			catch(Exception e)
			{
				string error=e.Message;
				try
				{
					myTrans.Rollback();
				}
//				catch (SqlException ex)
//				{
				catch (OleDbException ex)
				{
					if (myTrans.Connection != null)
					{
						string err="An exception of type " + ex.GetType() +
							" was encountered while attempting to roll back the transaction.";
					}
				}
			}
			finally
			{
				myConnection.Close();
				myConnection.Dispose();	
			}
		}
		/// <summary>
		/// 返回记录集
		/// </summary>
		/// <param name="sqlcmd">SQL语句</param>
		/// <param name="stable">数据表</param>
		/// <returns></returns>
		public  DataSet QueryDataSet( String sqlcmd, String stable)
		{
//			SqlConnection myConnection = new SqlConnection(sCntString);
//			SqlDataAdapter myCommand = new SqlDataAdapter(sqlcmd, myConnection);
			try
			{
				OleDbConnection myConnection = new OleDbConnection(sCntString);
				OleDbDataAdapter myCommand = new OleDbDataAdapter(sqlcmd, myConnection);
				DataSet ds = new DataSet();
				myCommand.Fill(ds, stable );
				myCommand.Dispose();
				myConnection.Close();
				myConnection.Dispose();
				return ds;
			}
			catch(Exception er)
			{
				string d = er.ToString();
				throw;
			}
		}
		public  DataSet QueryDataSet( String sqlcmd)
		{
//			SqlConnection myConnection = new SqlConnection(sCntString);
//			SqlDataAdapter myCommand = new SqlDataAdapter(sqlcmd, myConnection);
			OleDbConnection myConnection = new OleDbConnection(sCntString);
			OleDbDataAdapter myCommand = new OleDbDataAdapter(sqlcmd, myConnection);
			DataSet ds = new DataSet();
			myCommand.Fill(ds);
			myCommand.Dispose();
			myConnection.Close();
			myConnection.Dispose();
			return ds;
		}
		public string getSQLStr(string val,string type) 
		{
			if(val==null||val.Trim()=="")
			{
				return "null";
			}
			string strResult = "" ; 
			switch(type.Trim().ToLower()) 
			{
				case "str": 
					strResult = "'"+val.Trim()+"'" ; 
					break ; 
				case "i": 
					strResult = val.Trim() ; 
					break ; 
				case "dt": 
					strResult = " cast('"+val.Trim()+"' as datetime) " ; 
					break ; 
					//				case "sig": 
					//					strResult = "sig" ; 
					//					break ; 
					//				case "dec": 
					//					strResult = "dec" ; 
					//					break ; 
					//				case "db": //double
					//					strResult = "db" ; 
					//					break ; 
					//				case "bl": 
					//					strResult = "bl" ; 
					//					break ; 
				default: 
					throw new Exception("未知类型错误");
			} 
			return strResult ; 
		} 
//		#region 存储过程
//		public decimal FindID( string sTable)
//		{
//			decimal dRst = 0;
//			SqlConnection myConnection = new SqlConnection(sCntString);
//			myConnection.Open();
//			SqlCommand myCommand = new SqlCommand("findnewid", myConnection);
//			myCommand.CommandType = CommandType.StoredProcedure;
//
//			SqlParameter workParam = null;
//
//			workParam = myCommand.Parameters.Add("@sTable", SqlDbType.NChar, 40);
//			workParam = myCommand.Parameters.Add("@dId", SqlDbType.Decimal);
//			workParam.Direction = ParameterDirection.Output;
//
//			myCommand.Parameters["@sTable"].Value = sTable.Trim().ToLower();
//
//			myCommand.ExecuteNonQuery();
//			dRst =  (decimal)(myCommand.Parameters["@dId"].Value);
//			
//			myCommand.Dispose();
//			myConnection.Close();
//			myConnection.Dispose();
//
//			return dRst;
//		}
//		#endregion
	}
	
}