dba.cs

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

		{
			SqlCommand cmd = new SqlCommand(sqlText,conn);
			cmd.CommandTimeout = (timeout >= TIMEOUTDEFAULT)?TIMEOUTDEFAULT:timeout;
			int result = -99;
			try
			{
				if(conn.State == ConnectionState.Closed)
				{
					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 , SqlConnection conn)
		{
			SqlCommand cmd = new SqlCommand(sqlText,conn);
			cmd.CommandTimeout = (timeout >= TIMEOUTDEFAULT)?TIMEOUTDEFAULT:timeout;
			SqlDataAdapter da = new SqlDataAdapter();
			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>
		/// 执行Sql语句
		/// </summary>
		/// <param name="sqlText">Sql语句</param>
		/// <param name="timeout">延时时间(单位：秒，小于60不设定)</param>
		/// <param name="conn">数据库链接对象</param>
		/// <returns>DataRow 第一行数据</returns>
		public static DataRow RunCmdGetRow(string sqlText , int timeout , SqlConnection conn)
		{
			SqlCommand cmd = new SqlCommand(sqlText,conn);
			cmd.CommandTimeout = (timeout >= TIMEOUTDEFAULT)?TIMEOUTDEFAULT:timeout;
			SqlDataAdapter da = new SqlDataAdapter();
			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();
			}
			if(dt.Rows.Count>0)
				return dt.Rows[0];
			else
				return null;
		}

		#endregion
		#region Run StoreProcedure
		#region RunSPGetInt
		/// <summary>
		/// 执行Sql存储过程
		/// </summary>
		/// <param name="procedureName">存储过程名</param>
		/// <param name="param">参数列</param>
		/// <param name="timeout">延时时间(单位：秒，小于60不设定)</param>
		/// <param name="con">数据库链接对象</param>
		/// <returns>Int32 -- 存储过程Return值(-99:出错)</returns>
		public static int RunSPGetInt(string procedureName ,SqlParameter[] param, int timeout, SqlConnection conn)
		{
			SqlCommand cmd = new SqlCommand(procedureName,conn);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandTimeout = (timeout >= TIMEOUTDEFAULT)?TIMEOUTDEFAULT:timeout;
			cmd.Parameters.Add("@Return",SqlDbType.Int);
			cmd.Parameters["@Return"].Direction = ParameterDirection.ReturnValue;
			int result = -99;
			try
			{
				SetParam(param,cmd);
				if(conn.State == ConnectionState.Closed)
				{
					cmd.Connection.Open();
				}
				cmd.ExecuteNonQuery();
				result = (int)cmd.Parameters["@Return"].Value;
			}
			catch(Exception ex)
			{
				throw (new Exception(ex.Message+", cmd = "+cmd.CommandText));
			}
			finally
			{
				cmd.Connection.Close();
			}
			return result;
		}
		public static int RunSPGetInt(string procedureName ,DataRow param, int timeout, SqlConnection conn)
		{
			return RunSPGetInt(procedureName,SqlPara.GetSqlParas(param),timeout,conn);
		}
		public static int RunSPGetInt(string procedureName ,SqlPara param, int timeout, SqlConnection conn)
		{
			return RunSPGetInt(procedureName,param.GetPara(),timeout,conn);
		}
		#endregion
		#region RunSPGetTable
		/// <summary>
		/// 执行Sql存储过程
		/// </summary>
		/// <param name="procedureName">存储过程名</param>
		/// <param name="param">参数列</param>
		/// <param name="timeout">延时时间(单位：秒，小于60不设定)</param>
		/// <param name="con">数据库链接对象</param>
		/// <returns>DataTable -- 数据集</returns>
		public static DataTable RunSPGetTable(string procedureName ,SqlParameter[] param, int timeout, SqlConnection conn)
		{
			SqlCommand cmd = new SqlCommand(procedureName,conn);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandTimeout = (timeout >= TIMEOUTDEFAULT)?TIMEOUTDEFAULT:timeout;
			cmd.Parameters.Add("@Return",SqlDbType.Int);
			cmd.Parameters["@Return"].Direction = ParameterDirection.ReturnValue;
			SqlDataAdapter da = new SqlDataAdapter();
			DataTable dt = new DataTable();
			da.SelectCommand = cmd;
			try
			{
				SetParam(param,cmd);
				da.Fill(dt);
			}
			catch(Exception ex)
			{
				throw (new Exception(ex.Message+", cmd = "+cmd.CommandText));
			}
			finally
			{
				cmd.Connection.Close();
			}
			return dt;
		}
		public static DataTable RunSPGetTable(string procedureName ,DataRow param, int timeout, SqlConnection conn)
		{
			return RunSPGetTable(procedureName,SqlPara.GetSqlParas(param),timeout,conn);
		}
		public static DataTable RunSPGetTable(string procedureName ,SqlPara param, int timeout, SqlConnection conn)
		{
			return RunSPGetTable(procedureName,param.GetPara(),timeout,conn);
		}
		#endregion
		#region RunSPGetTable(has result)
		/// <summary>
		/// 执行Sql存储过程
		/// </summary>
		/// <param name="procedureName">存储过程名</param>
		/// <param name="param">参数列</param>
		/// <param name="timeout">延时时间(单位：秒，小于60不设定)</param>
		/// <param name="con">数据库链接对象</param>
		/// <param name="result">存储过程Return值(-99:出错)</param>
		/// <returns>DataTable -- 数据集</returns>
		public static DataTable RunSPGetTable(string procedureName ,SqlParameter[] param, int timeout, SqlConnection conn, ref int result)
		{
			SqlCommand cmd = new SqlCommand(procedureName,conn);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandTimeout = (timeout >= TIMEOUTDEFAULT)?TIMEOUTDEFAULT:timeout;
			cmd.Parameters.Add("@Return",SqlDbType.Int);
			cmd.Parameters["@Return"].Direction = ParameterDirection.ReturnValue;
			SqlDataAdapter da = new SqlDataAdapter();
			DataTable dt = new DataTable();
			da.SelectCommand = cmd;
			result = -99;
			try
			{
				SetParam(param,cmd);
				da.Fill(dt);
				result = (int)cmd.Parameters["@Return"].Value;
			}
			catch(Exception ex)
			{
				throw (new Exception(ex.Message+", cmd = "+cmd.CommandText));
			}
			finally
			{
				cmd.Connection.Close();
			}
			return dt;
		}

		public static DataTable RunSPGetTable(string procedureName ,DataRow param, int timeout, SqlConnection conn, ref int result)
		{
			return RunSPGetTable(procedureName,SqlPara.GetSqlParas(param),timeout,conn,ref result);
		}

		public static DataTable RunSPGetTable(string procedureName ,SqlPara param, int timeout, SqlConnection conn, ref int result)
		{
			return RunSPGetTable(procedureName,param.GetPara(),timeout,conn,ref result);
		}
		#endregion
		#endregion
		#region SqlParameter Process		
		/// <summary>
		/// 为存储过程设置参数
		/// </summary>
		/// <param name="param">参数数组</param>
		/// <param name="cmd">存储过程对象</param>
		private static void SetParam(SqlParameter[] param, SqlCommand cmd)
		{
			foreach(SqlParameter p in param)
			{
				cmd.Parameters.Add(p);
			}
		}
		#endregion
		#region SqlConnection String
		/// <summary>
		/// 建立连接字
		/// </summary>
		/// <param name="server">SQL服务器名或IP</param>
		/// <param name="database">SQL数据库名</param>
		/// <param name="user">登录用户名</param>
		/// <param name="password">登录密码</param>
		/// <returns>连接字</returns>
		public static string MakeConnString(string server, string database, string user, string password)
		{
			//			return "Server="+server+";"
			//				+ "User ID="+user+";"
			//				+ "Password="+password+";"
			//				+ "Database="+database+";"
			//				+ "Connection Reset=FALSE";
						return "data source="+server+";"
							+ "user id="+user+";"
							+ "password="+password+";"
							+ "initial catalog="+database+";"
							+ "Connection Reset=FALSE;integrated security=SSPI;";
		}
		#endregion
		#region 批量执行（带事务）
		public static int[] RunSpBatch(SqlConnection con, string spName, DataTable dt)
		{
			if(dt==null || dt.Rows.Count==0)
			{
				return new int[0];
			}
			int[] returnvalues = new int[dt.Rows.Count];
			if(con.State == ConnectionState.Closed)
			{
				con.Open();
			}

			SqlTransaction trans = con.BeginTransaction();
			SqlCommand cmd = new SqlCommand(spName,con,trans);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandTimeout = TIMEOUTDEFAULT;
			cmd.Parameters.Add("@Return",SqlDbType.Int);
			cmd.Parameters["@Return"].Direction = ParameterDirection.ReturnValue;
			SetParam(SqlPara.GetSqlParas(dt.Rows[0]),cmd);


			try
			{
				for(int i = 0 ; i<dt.Rows.Count ; i++)
				{
					SqlPara.SetParaValues(cmd,dt.Rows[i]);
					cmd.ExecuteNonQuery();
					returnvalues[i] = (int)cmd.Parameters["@Return"].Value;
				}
				trans.Commit();
			}
			catch(Exception ex)
			{
				trans.Rollback();
				throw new Exception("BUError cmd = "+cmd.CommandText+" "+ex.Message);
				//returnvalues = new int[0];  
  
			}
			finally 
			{
				con.Close();
			}
			return returnvalues;
		}
		public static DataTable RunSpBatchReturnTable(SqlConnection con, string spName, DataTable dt)
		{
			if(dt==null || dt.Rows.Count==0)
			{
				return new DataTable();
			}
			DataTable returnTable = new DataTable();
			if(con.State == ConnectionState.Closed)
			{
				con.Open();
			}

			SqlTransaction trans = con.BeginTransaction();
			SqlCommand cmd = new SqlCommand(spName,con,trans);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Parameters.Add("@Return",SqlDbType.Int);
			cmd.Parameters["@Return"].Direction = ParameterDirection.ReturnValue;
			cmd.CommandTimeout = TIMEOUTDEFAULT;
			SetParam(SqlPara.GetSqlParas(dt.Rows[0]),cmd);
			SqlDataAdapter da = new SqlDataAdapter();
			//DataTable dttemp = new DataTable();
			da.SelectCommand = cmd;
			try
			{
				for(int i = 0 ; i<dt.Rows.Count ; i++)
				{
					//dttemp.Rows.Clear();
					SqlPara.SetParaValues(cmd,dt.Rows[i]);
					da.Fill(returnTable);
					//					DataRow dr = returnTable.NewRow();
					//					FHSoft.DBA.DataRowProcess.CloneDataRow(dttemp.Rows[0],dr);
					//					returnTable.Rows.Add(dr);
				}
				trans.Commit();
			}
			catch(Exception ex)
			{
				trans.Rollback();
				throw new Exception("BUError cmd = "+cmd.CommandText+" "+ex.Message);
				//returnTable = new DataTable();    
			}
			finally 
			{
				con.Close();
			}
			return returnTable;
		}
		public static int[] RunSpBatch(SqlConnection con, string spName, DataView dv)
		{
			if(dv==null || dv.Count==0)
			{
				return new int[0];
			}
			int[] returnvalues = new int[dv.Count];
			SqlTransaction trans = con.BeginTransaction();
			SqlCommand cmd = new SqlCommand(spName,con,trans);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Parameters.Add("@Return",SqlDbType.Int);
			cmd.Parameters["@Return"].Direction = ParameterDirection.ReturnValue;
			cmd.CommandTimeout = TIMEOUTDEFAULT;
			IEnumerator ie = dv.GetEnumerator();
			ie.Reset();
			SetParam(SqlPara.GetSqlParas(((DataRowView)ie).Row),cmd);
			int i = 0;
			try
			{
				while(ie.MoveNext())
				{
					SqlPara.SetParaValues(cmd,((DataRowView)ie).Row);
					cmd.ExecuteNonQuery();
					returnvalues[i] = (int)cmd.Parameters["@Return"].Value;
					i++;
				}
				trans.Commit();
			}
			catch(Exception ex)
			{
				trans.Rollback();
				throw new Exception("BUError cmd = "+cmd.CommandText+" "+ex.Message);
				//returnvalues = new int[0];    
			}
			finally 
			{
				con.Close();
			}
			return returnvalues;
		}
		#endregion
		#endregion

	}
	#endregion

	#region Excel数据库连接
	public class ExcelConn
	{
		OleDbConnection conn = new OleDbConnection();
		string lastError = string.Empty;
		public ExcelConn(string excelFile, string user, string password)
		{
			conn.ConnectionString = MakeConnString(excelFile,user,password);
		}

		public ExcelConn(string excelFile)
		{
			conn.ConnectionString = MakeConnString(excelFile);
		}

		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 DataTable GetSheet(string sheetName)
		{
			try
			{
				return GetSheet(sheetName,conn);
			}
			catch(Exception ex)
			{
				lastError = ex.Message ;
				return new DataTable();
			}
		}
		public void GetSheet(string sheetName, DataTable dt)
		{
			try
			{
				GetSheet(sheetName,conn,dt);
			}
			catch(Exception ex)
			{
				lastError = ex.Message ;
			}
		}
		#endregion

		#region 数据访问(static)
		/// <summary>
		/// 获取Sheet全部数据
		/// </summary>
		/// <param name="sheetName">Sheet名</param>
		/// <param name="conn">数据连接对象</param>
		/// <returns>DataTable -- 数据集</returns>
		public static DataTable GetSheet(string sheetName, OleDbConnection conn)
		{
			OleDbDataAdapter da = new OleDbDataAdapter("Select * From ["+sheetName+"$]",conn);
			DataTable dt = new DataTable();