dba.cs

报刊广告管理系统。CSharp编写

			try
			{
				da.Fill(dt);
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message + ", cmd = "+da.SelectCommand.CommandText);
			}
			finally
			{
				da.SelectCommand.Connection.Close();
			}
			return dt;
		}
		/// <summary>
		/// 获取Sheet全部数据
		/// </summary>
		/// <param name="sheetName">Sheet名</param>
		/// <param name="execlFile">Excel文件名</param>
		/// <returns>DataTable -- 数据集</returns>
		public static DataTable GetSheet(string sheetName, string execlFile)
		{
			OleDbConnection conn = new OleDbConnection(MakeConnString(execlFile));
			return GetSheet(sheetName,conn);
		}
		/// <summary>
		/// 获取Sheet全部数据填充指定的数据集
		/// </summary>
		/// <param name="sheetName">Sheet名</param>
		/// <param name="conn">数据连接对象</param>
		/// <param name="dt">指定的数据集</param>
		public static void GetSheet(string sheetName, OleDbConnection conn, DataTable dt)
		{
			OleDbDataAdapter da = new OleDbDataAdapter("Select * From ["+sheetName+"$]",conn);
			try
			{
				da.Fill(dt);
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message + ", cmd = "+da.SelectCommand.CommandText);
			}
			finally
			{
				da.SelectCommand.Connection.Close();
			}
		}
		/// <summary>
		/// 获取Sheet全部数据填充指定的数据集
		/// </summary>
		/// <param name="sheetName">Sheet名</param>
		/// <param name="execlFile">Excel文件名</param>
		/// <param name="dt">指定的数据集</param>
		public static void GetSheet(string sheetName, string  execlFile, DataTable dt)
		{
			OleDbConnection conn = new OleDbConnection(MakeConnString(execlFile));
			GetSheet(sheetName,conn,dt);
		}
		/// <summary>
		/// 建立连接字
		/// </summary>
		/// <param name="excelFile">Excel文件名</param>
		/// <returns>连接字</returns>
		public static string MakeConnString(string excelFile)
		{
			return "Provider=Microsoft.Jet.OLEDB.4.0"
				+";Data Source="+excelFile
				+";Extended Properties = 'Excel 8.0;HDR=Yes;IMEX=1'";
		}
		/// <summary>
		/// 建立连接字
		/// </summary>
		/// <param name="excelFile">Excel文件名</param>
		/// <param name="user">登录用户名</param>
		/// <param name="password">登陆密码</param>
		/// <returns>连接字</returns>
		public static string MakeConnString(string excelFile, string user, string password)
		{
			return "Provider=Microsoft.Jet.OLEDB.4.0"
				+";Data Source="+excelFile
				+";User ID="+user
				+";Database Password="+password
				+";Extended Properties = 'Excel 8.0;HDR=Yes;IMEX=1'";
		}
		#endregion

		#region Quick Export
		public static bool QuickExport(DataTable dt)
		{
			//存储文件
			using(SaveFileDialog sfd = new SaveFileDialog())
			{
				sfd.Filter = "Excel电子表格(*.xls)|*.xls";
				if(sfd.ShowDialog()!=DialogResult.OK)
				{
					return false;
				}
				QuickExport(dt ,sfd.FileName);
				return true;
			}
		}
		public static void QuickExport(DataTable dt , string fileName)
		{
			//DataTable 对象
			int cols = dt.Columns.Count;
			int rows = dt.Rows.Count;

			StringBuilder sb = new StringBuilder();
			// 列标题
			for(int col=1;col<=cols;col++)
			{
				sb.Append(dt.Columns[col-1].ColumnName);
				sb.Append("\t");
			}
			sb.Append("\n");
				
			//内容
			for(int row = 0;row<rows;row++)
			{
				for(int col = 0;col<cols;col++)
				{
					if(dt.Rows[row][col].GetType() == typeof(byte[]))
					{
						sb.Append(@"<bytes...>");
					}
					else
					{
						sb.AppendFormat("{0}",dt.Rows[row][col]);
					}
					if( col < cols - 1 )
						sb.Append("\t");
				}
				sb.Append("\n");
			}

			WWAM.Cast.SaveToFile(fileName,Encoding.Default.GetBytes(sb.ToString()),true);
			
		}
		public static bool QuickExport(DataView dv)
		{
			//存储文件
			using(SaveFileDialog sfd = new SaveFileDialog())
			{
				sfd.Filter = "Excel电子表格(*.xls)|*.xls";
				if(sfd.ShowDialog()!=DialogResult.OK)
				{
					return false;
				}
				QuickExport(dv ,sfd.FileName);
				return true;
			}
		}
		public static void QuickExport(DataView dv , string fileName)
		{
			//DataTable 对象
			int cols = dv.Table.Columns.Count;

			StringBuilder sb = new StringBuilder();
			// 列标题
			for(int col=1;col<=cols;col++)
			{
				sb.Append(dv.Table.Columns[col-1].ColumnName);
				sb.Append("\t");
			}
			sb.Append("\n");
				
			//内容
			IEnumerator ie = dv.GetEnumerator();
			ie.Reset();
			while(ie.MoveNext())
			{
				for(int col = 0;col<cols;col++)
				{
					if(((DataRowView)ie).Row[col].GetType() == typeof(byte[]))
					{
						sb.Append(@"<bytes...>");
					}
					else
					{
						sb.AppendFormat("{0}",((DataRowView)ie).Row[col]);
					}
					if( col < cols - 1 )
						sb.Append("\t");
				}
				sb.Append("\n");
			}
			WWAM.Cast.SaveToFile(fileName,Encoding.Default.GetBytes(sb.ToString()),false);
		}
		#endregion
	}
	#endregion

	#region Access数据库连接
	public class AccessConn
	{
		OleDbConnection conn = new OleDbConnection();
		string lastError = string.Empty;
		public AccessConn(string accessFile, string user, string password)
		{
			conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
				+";User ID="+user
				+";Database Password="+password
				+";Data source="+accessFile;
		}

		public AccessConn(string accessFile)
		{
			conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
				+";Data source="+accessFile;
		}

		public bool TryConn()
		{
			bool conned = false;
			try
			{
				conn.Open();
				conned = true;
			}
			catch(Exception ex)
			{
				lastError = "Conn = "+conn.ConnectionString+", "+ex.Message;
			}
			finally
			{
				conn.Close();
			}
			return conned;
		}
		public OleDbConnection Conn
		{
			get
			{
				return conn;
			}
		}
		#region 数据库访问
		public int RunCmdGetInt(string sqlText, int timeout)
		{
			try
			{
				return RunCmdGetInt(sqlText, timeout, conn);
			}
			catch(Exception ex)
			{
				lastError = ex.Message;
				return -99;
			}
		}
		public DataTable RunCmdGetTable(string sqlText, int timeout)
		{
			try
			{
				return RunCmdGetTable(sqlText, timeout, conn);
			}
			catch(Exception ex)
			{
				lastError = ex.Message;
				return new DataTable();
			}
		}
		#endregion

		#region 数据库访问(static)
		/// <summary>
		/// 执行Sql语句
		/// </summary>
		/// <param name="sqlText">Sql语句</param>
		/// <param name="timeout">延时时间(单位：秒，小于60不设定)</param>
		/// <param name="con">数据库链接对象</param>
		/// <returns>Int32 -- 影响行数</returns>
		public static int RunCmdGetInt(string sqlText, int timeout, OleDbConnection conn)
		{
			OleDbCommand cmd = new OleDbCommand(sqlText,conn);
			if(timeout >= 60)
			{
				cmd.CommandTimeout = timeout;
			}
			int result = -99;
			try
			{
				cmd.Connection.Open();
				result = cmd.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				throw (new Exception(ex.Message+", cmd = "+cmd.CommandText));
			}
			finally
			{
				cmd.Connection.Close();
			}
			return result;
		}
		public static int RunCmdGetInt(string sqlText, OleDbParameter[] param, int timeout, OleDbConnection conn)
		{
			OleDbCommand cmd = new OleDbCommand(sqlText,conn);
			if(timeout >= 60)
			{
				cmd.CommandTimeout = timeout;
			}
			int result = -99;
			SetPara(param,cmd);
			try
			{
				cmd.Connection.Open();
				result = cmd.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				throw (new Exception(ex.Message+", cmd = "+cmd.CommandText));
			}
			finally
			{
				cmd.Connection.Close();
			}
			return result;
		}
		/// <summary>
		/// 执行Sql语句
		/// </summary>
		/// <param name="sqlText">Sql语句</param>
		/// <param name="timeout">延时时间(单位：秒，小于60不设定)</param>
		/// <param name="con">数据库链接对象</param>
		/// <returns>DataTable -- 数据集</returns>
		public static DataTable RunCmdGetTable(string sqlText , int timeout , OleDbConnection conn)
		{
			OleDbCommand cmd = new OleDbCommand(sqlText,conn);
			if(timeout >= 60)
			{
				cmd.CommandTimeout = timeout;
			}
			OleDbDataAdapter da = new OleDbDataAdapter();
			DataTable dt = new DataTable();
			da.SelectCommand = cmd;
			try
			{
				da.Fill(dt);
			}
			catch(Exception ex)
			{
				throw (new Exception(ex.Message + ", cmd = "+cmd.CommandText));
			}
			finally
			{
				cmd.Connection.Close();
			}
			return dt;
		}
		public static DataTable RunCmdGetTable(string sqlText , OleDbParameter[] param , int timeout , OleDbConnection conn)
		{
			OleDbCommand cmd = new OleDbCommand(sqlText,conn);
			if(timeout >= 60)
			{
				cmd.CommandTimeout = timeout;
			}
			SetPara(param,cmd);
			OleDbDataAdapter da = new OleDbDataAdapter();
			DataTable dt = new DataTable();
			da.SelectCommand = cmd;
			try
			{
				da.Fill(dt);
			}
			catch(Exception ex)
			{
				throw (new Exception(ex.Message+", cmd = "+cmd.CommandText));
			}
			finally
			{
				cmd.Connection.Close();
			}
			return dt;
		}
		/// <summary>
		/// 建立连接字
		/// </summary>
		/// <param name="accessFile">Access文件名</param>
		/// <returns>连接字</returns>
		public static string MakeConnString(string accessFile)
		{
			return "Provider=Microsoft.Jet.OLEDB.4.0"
				+";Data source="+accessFile;
		}
		/// <summary>
		/// 建立连接字
		/// </summary>
		/// <param name="accessFile">Access文件名</param>
		/// <param name="user">登录用户名</param>
		/// <param name="password">登陆密码</param>
		/// <returns>连接字</returns>
		public static string MakeConnString(string accessFile, string user, string password)
		{
			return "Provider=Microsoft.Jet.OLEDB.4.0"
				+";User ID="+user
				+";Database Password="+password
				+";Data source="+accessFile;
		}
		private static void SetPara(OleDbParameter[] param , OleDbCommand cmd)
		{
			cmd.Parameters.Clear();
			foreach(OleDbParameter para in param)
			{
				cmd.Parameters.Add(para);
			}
		}
		public static OleDbParameter[] GetOleDbParas(string[] names,object[] values)
		{
			if(names.Length != values.Length)
			{
				throw new Exception("参数与值的数量不一致!");
			}
			else
			{
				OleDbParameter[] oleDbParas = new OleDbParameter[names.Length];
				for(int i = 0 ; i< names.Length ; i++)
				{
					OleDbParameter param = new OleDbParameter("@"+names[i],values[i]); 
					switch(values[i].GetType().Name)
					{
						case "Int32":
							param.OleDbType = OleDbType.Integer;
							break;
						case "Single":
							param.OleDbType = OleDbType.Single;
							break;
						case "String":
							param.OleDbType = OleDbType.VarChar;
							param.Size = 200;
							break;
						case "Boolean":
							param.OleDbType = OleDbType.Boolean;
							break;
						case "DateTime":
							param.OleDbType = OleDbType.Date;
							break;
						case "Byte[]":
							param.OleDbType = OleDbType.LongVarBinary;
							break;
					}
					oleDbParas[i] = param;
				}
				return oleDbParas;
			}
		}
		#endregion

		#region 批量执行（带事务）
		public static int[] RunCmdBatch(OleDbConnection con, string[] sqlText)
		{
			if(sqlText==null || sqlText.Length==0)
			{
				return null;
			}
			int[] returnvalues = new int[sqlText.Length];
			OleDbTransaction trans = con.BeginTransaction();
			OleDbCommand cmd = new OleDbCommand("",con,trans);
			try
			{
				for(int i = 0 ; i<sqlText.Length ; i++)
				{
					cmd.CommandText = sqlText[i];
					returnvalues[i] = cmd.ExecuteNonQuery();
				}
				trans.Commit();
			}
			catch//(Exception e)
			{
				try
				{
					trans.Rollback();
				}
				catch //(SqlException ex)
				{