dba.cs

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

using System;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Windows.Forms;
using System.IO;

namespace WWAM.DBA
{
	#region SqlParameter构造
	[Serializable]
	public class SqlPara
	{
		#region SqlPara(Class)
		object[] values;
		string[] names;
		public SqlPara()
		{
			values = new object[]{};
			names = new string[]{};
		}

		public SqlPara(string[] names ,object[] values)
		{
			SetPara(names,values);
		}

		public SqlPara(DataRow dr)
		{
			SetPara(dr);
		}

		public SqlPara(SqlParameter[] paras)
		{
			SetPara(paras);
		}

		public SqlPara(int id)
		{
			SetPara(id);
		}

		public SqlPara(string name, object val)
		{
			SetPara(name,val);
		}

		public void SetPara(string[] names , object[] values)
		{
			this.names = names;
			this.values = values;
		}
		
		public void SetPara(DataRow dr)
		{
			names = new string[dr.Table.Columns.Count];
			values = new object[dr.Table.Columns.Count];
			for(int i = 0 ; i< dr.Table.Columns.Count ; i++)
			{
				try
				{
					names[i] = dr.Table.Columns[i].ColumnName;
					values[i] = dr[i];
				}
				catch
				{
				}
			}
		}

		public void SetPara(int id)
		{
			names = new string[]{"id"};
			values = new object[]{id};
		}

		public void SetPara(string name , object val)
		{
			names = new string[]{name};
			values = new object[]{val};
		}

		public void SetPara(SqlParameter[] paras)
		{
			names = new string[paras.Length];
			values = new object[paras.Length];
			for(int i=0 ; i<paras.Length ; i++)
			{
				names[i] = paras[i].ParameterName.Replace("@","");
				values[i] = paras[i].Value;
			}
		}
		public SqlParameter[] GetPara()
		{
			return GetSqlParas(this.names,this.values);
		}
		#endregion
		#region GetSqlParas(static)
		/// <summary>
		/// 获取参数列表
		/// </summary>
		/// <param name="names">参数名（不带"@"）</param>
		/// <param name="values">参数值</param>
		/// <returns></returns>
		public static SqlParameter[] GetSqlParas(string[] names, object[] values)
		{
			if(names.Length != values.Length)
			{
				throw new Exception("参数与值的数量不一致!");
			}
			else
			{
				SqlParameter[] sqlParas = new SqlParameter[names.Length];
				for(int i = 0 ; i< names.Length ; i++)
				{
					SqlParameter param = new SqlParameter("@"+names[i],values[i]); 
					switch(values[i].GetType().Name)
					{
						case "Int32":
							param.SqlDbType = SqlDbType.Int;
							break;
						case "Single":
							param.SqlDbType = SqlDbType.Real;
							break;
						case "String":
							param.SqlDbType = SqlDbType.VarChar;
							param.Size = 4000;
							break;
						case "Boolean":
							param.SqlDbType = SqlDbType.Bit;
							break;
						case "DateTime":
							param.SqlDbType = SqlDbType.DateTime;
							break;
						case "Byte[]":
							param.SqlDbType = SqlDbType.Image;
							break;
						case "long":
							param.SqlDbType = SqlDbType.BigInt;
							break;
						default:
							param.SqlDbType = SqlDbType.Variant;
							break;
					}
					sqlParas[i] = param;
				}
				return sqlParas;
			}
		}

		public static SqlParameter[] GetSqlParas(DataRow dr)
		{
			SqlParameter[] sqlParas = new SqlParameter[dr.Table.Columns.Count];
			for(int i = 0 ; i< dr.Table.Columns.Count ; i++)
			{
				SqlParameter param = new SqlParameter("@"+dr.Table.Columns[i].ColumnName,dr[i]); 
				switch(dr.Table.Columns[i].DataType.Name)
				{
					case "Int32":
						param.SqlDbType = SqlDbType.Int;
						break;
					case "Single":
						param.SqlDbType = SqlDbType.Real;
						break;
					case "String":
						param.SqlDbType = SqlDbType.VarChar;
						param.Size = 200;
						break;
					case "Boolean":
						param.SqlDbType = SqlDbType.Bit;
						break;
					case "DateTime":
						param.SqlDbType = SqlDbType.DateTime;
						break;
					case "Byte[]":
						param.SqlDbType = SqlDbType.Image;
						break;
					default:
						param.SqlDbType = SqlDbType.Variant;
						break;
				}
				sqlParas[i] = param;
			}
			return sqlParas;
		}
		public static SqlParameter[] GetSqlParas(string con , string spName)
		{
			using (SqlConnection cn = new SqlConnection(con)) 
			using (SqlCommand cmd = new SqlCommand(spName,cn))
			{
				cn.Open();
				cmd.CommandType = CommandType.StoredProcedure;

				SqlCommandBuilder.DeriveParameters(cmd);

				cmd.Parameters.RemoveAt(0);

				SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];;

				cmd.Parameters.CopyTo(discoveredParameters, 0);

				return discoveredParameters;
			}
		}
		#endregion
		#region SetParaValues
		public static void SetParaValues(SqlCommand cmd, DataRow dr)
		{
			for(int i = 0 ; i< dr.Table.Columns.Count ; i++)
			{
				DataColumn col = dr.Table.Columns[i];
				if(cmd.Parameters["@"+col.ColumnName]!=null)
				{
					cmd.Parameters["@"+col.ColumnName].Value = dr[i];
				}
			}
		}
		#endregion
	}
	#endregion
	
	#region SQL数据库连接
	/// <summary>
	/// SQL数据库连接类
	/// </summary>
	public sealed class SqlConn
	{
		#region Varible
		SqlConnection conn = new SqlConnection();
		string lastError = string.Empty;
		const int TIMEOUTDEFAULT = 600;
		EventHandler onErrored;
		#endregion

		#region Creator
		public SqlConn(string server, string database, string user, string password)
		{
			conn.ConnectionString = MakeConnString(server, database, user, password);
		}
		public SqlConn(string constring)
		{
			conn.ConnectionString = constring;
		}
		#endregion

		#region Event
		public event EventHandler Errored
		{
			add
			{
				onErrored += value;
			}
			remove
			{
				onErrored -= value;
			}
		}

		void OnErrored()
		{
			if(onErrored!=null)
				onErrored.Invoke(this,System.EventArgs.Empty);
		}
		#endregion

		#region Method
		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;
		}

		#endregion

		#region Property
		public SqlConnection Conn
		{
			get
			{
				return conn;
			}
		}

		public string LastError
		{
			get
			{
				return lastError;
			}
		}
		private void SetLastError(string text)
		{
			lastError = text;
			OnErrored();
		}
		public void ReLoadConString(string constring)
		{
			conn.ConnectionString = constring;
		}
		#endregion

		#region 数据访问
		public int RunCmdGetInt(string sqlText, int timeout)
		{
			try
			{
				return RunCmdGetInt(sqlText, timeout, conn);
			}
			catch(Exception ex)
			{
				SetLastError( ex.Message );
				return -99;
			}
		}
		public DataTable RunCmdGetTable(string sqlText, int timeout)
		{
			try
			{
				return RunCmdGetTable(sqlText, timeout, conn);
			}
			catch(Exception ex)
			{
				SetLastError( ex.Message );
				return new DataTable();
			}
		}
		public DataRow RunCmdGetRow(string sqlText, int timeout)
		{
			try
			{
				return RunCmdGetRow(sqlText, timeout, conn);
			}
			catch(Exception ex)
			{
				SetLastError( ex.Message );
				return null;
			}
		}
		public int RunSPGetInt(string procedureName ,DataRow param, int timeout)
		{
			try
			{
				return RunSPGetInt(procedureName, param, timeout, conn);
			}
			catch(Exception ex)
			{
				SetLastError( ex.Message );
				return -99;
			}
		}
		public int RunSPGetInt(string procedureName ,SqlPara param, int timeout)
		{
			try
			{
				return RunSPGetInt(procedureName, param, timeout, conn);
			}
			catch(Exception ex)
			{
				SetLastError( ex.Message );
				return -99;
			}
		}
		public int RunSPGetInt(string procedureName ,SqlParameter[] param, int timeout)
		{
			try
			{
				return RunSPGetInt(procedureName, param, timeout, conn);
			}
			catch(Exception ex)
			{
				SetLastError( ex.Message );
				return -99;
			}
		}
		public DataTable RunSPGetTable(string procedureName ,DataRow param, int timeout)
		{
			try
			{
				return RunSPGetTable(procedureName, param, timeout, conn);
			}
			catch(Exception ex)
			{
				SetLastError( ex.Message );
				return new DataTable();
			}
		}
		public DataTable RunSPGetTable(string procedureName ,SqlParameter[] param, int timeout)
		{
			try
			{
				return RunSPGetTable(procedureName, param, timeout, conn);
			}
			catch(Exception ex)
			{
				SetLastError( ex.Message );
				return new DataTable();
			}
		}

		public DataTable RunSPGetTable(string procedureName ,SqlPara param, int timeout)
		{
			try
			{
				return RunSPGetTable(procedureName, param, timeout, conn);
			}
			catch(Exception ex)
			{
				SetLastError( ex.Message );
				return new DataTable();
			}
		}
		public DataTable RunSPGetTable(string procedureName ,DataRow param, int timeout, ref int result)
		{
			try
			{
				return RunSPGetTable(procedureName, param, timeout, conn, ref result);
			}
			catch(Exception ex)
			{
				SetLastError( ex.Message );
				return new DataTable();
			}
		}
		public DataTable RunSPGetTable(string procedureName ,SqlParameter[] param, int timeout, ref int result)
		{
			try
			{
				return RunSPGetTable(procedureName, param, timeout, conn, ref result);
			}
			catch(Exception ex)
			{
				SetLastError( ex.Message );
				return new DataTable();
			}
		}
		public DataTable RunSPGetTable(string procedureName ,SqlPara param, int timeout, ref int result)
		{
			try
			{
				return RunSPGetTable(procedureName, param, timeout, conn, ref result);
			}
			catch(Exception ex)
			{
				SetLastError( ex.Message );
				return new DataTable();
			}
		}
		#endregion

		#region 数据访问(static)
		#region Run SqlCommand
		/// <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, SqlConnection conn)