dataaccess.cs

监控系统

		{
			this.sqlCommand.CommandType = CommandType.Text;
			this.sqlCommand.Connection.Open();
			SqlTransaction transaction = this.sqlCommand.Connection.BeginTransaction("BatchExcute");
			this.sqlCommand.Transaction = transaction;

			try
			{
				for(int i=0;i<SqlArgs.Length;i++)
				{
					this.sqlCommand.CommandText = SqlArgs[i] ;
					this.sqlCommand.ExecuteNonQuery();
				}

				transaction.Commit();
			}
			catch(SqlException e )
			{
				transaction.Rollback("BatchExcute");
				throw new Exception(e.Message);
			}
			finally
			{
				this.sqlCommand.Connection.Close();
			}

			return true;
		}

		public bool BatchExcute(ArrayList al)
		{
			this.sqlCommand.CommandType = CommandType.Text;
			this.sqlCommand.Connection.Open();
			SqlTransaction transaction = this.sqlCommand.Connection.BeginTransaction("BatchExcute");
			this.sqlCommand.Transaction = transaction;

			try
			{
				for(int i=0;i<al.Count;i++)
				{
					this.sqlCommand.CommandText = (string) al[i];
					this.sqlCommand.ExecuteNonQuery();
				}

				transaction.Commit();
			}
			catch(SqlException e)
			{
				transaction.Rollback("BatchExcute");
				throw new Exception(e.Message);
			}
			finally
			{
				this.sqlCommand.Connection.Close();
			}

			return true;
		}

		public bool BatchExcute(string sql)
		{
			this.sqlCommand.CommandType = CommandType.Text;
			this.sqlCommand.Connection.Open();
			SqlTransaction transaction = this.sqlCommand.Connection.BeginTransaction("BatchExcute");
			this.sqlCommand.Transaction = transaction;
			this.sqlCommand.CommandText = sql ;

			try
			{
				this.sqlCommand.ExecuteNonQuery();

				transaction.Commit();
			}
			catch(SqlException e )
			{
				transaction.Rollback("BatchExcute");
				throw new Exception(e.Message);
			}
			finally
			{
				this.sqlCommand.Connection.Close();
			}

			return true;
		}

//		public bool BatchExcute(SqlRequest[] sqlRequests)
//		{
//			this.sqlCommand.CommandType = CommandType.Text;
//			this.sqlCommand.Connection.Open();
//			SqlTransaction transaction = this.sqlCommand.Connection.BeginTransaction("BatchExcute");
//			this.sqlCommand.Transaction = transaction;
//
//			try
//			{
//				for(int i=0;i<sqlRequests.Length;i++)
//				{
//					this.sqlCommand.CommandText = sqlRequests[i].CommandText ;
//					if(sqlRequests[i].Params!=null)
//					{
//						for(int j=0;j<sqlRequests[i].Params.Length;j++)
//							this.sqlCommand.Parameters.Add((SqlParameter) sqlRequests[i].Params[j]);
//					}
//					this.sqlCommand.Prepare();
//					this.sqlCommand.ExecuteNonQuery();
//				}
//
//				transaction.Commit();
//			}
//			catch(SqlException ex)
//			{
//				transaction.Rollback();
//				throw new Exception(ex.Message);
//			}
//			finally
//			{
//				this.sqlCommand.Connection.Close();
//			}
//
//			return true;
//		}

		public Object ExecuteScalar(string sql)
		{
			return this.ExecuteScalar(CommandType.Text,sql);
		}
		
		public bool Select(string sql,DataTable dt)
		{
			this.ClearParameters();
			return this.Excute(CommandType.Text,sql,dt);
		}

		public bool Select(string sql, DataSet dataSet, string tableName)
		{
			this.ClearParameters();
			return this.Excute(CommandType.Text,sql, dataSet, tableName);
		}

		public bool Select(string sql, DataSet dataSet, int startRecord,int maxRecords, string tableName)
		{
			this.ClearParameters();
			return this.Excute(CommandType.Text,sql, dataSet,startRecord, maxRecords, tableName);
		}

		public bool Excute(string sql)
		{
			int affectedRow = 0;
			return this.Excute(sql ,ref affectedRow);
		}

		public bool Excute(string sql ,ref int affectedRow)
		{
			this.ClearParameters();
			return this.Excute(CommandType.Text,sql ,ref affectedRow);
		}

		public Object GetParamValue(string ParamName)
		{
			return this.sqlCommand.Parameters[ParamName];
		}

		public SqlResult CallProcedure(string ProcedureName,params object[] parameters)
		{
			SqlResult sqlResult = new SqlResult();
			DataTable dt = new DataTable();
			string sqlString = "select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" +ProcedureName + "' order by ORDINAL_POSITION";
			if(!Select(sqlString,dt))
				return null;

			ClearParameters();
			sqlCommand.CommandText = ProcedureName;
			sqlCommand.CommandType = CommandType.StoredProcedure;

			AddParameter("@Value",SqlDbType.Int,ParameterDirection.ReturnValue);

			try
			{
				for(int i=0;i<dt.Rows.Count;i++)
				{
					SqlParameter sqlParameter = new SqlParameter();
					sqlParameter.ParameterName = dt.Rows[i]["PARAMETER_NAME"].ToString();
					sqlParameter.Direction = (dt.Rows[i]["PARAMETER_MODE"].ToString()=="IN")?ParameterDirection.Input:ParameterDirection.Output;

					#region 匹配参数类型
					switch(dt.Rows[i]["DATA_TYPE"].ToString())
					{
						case "bit" :
							if(sqlParameter.Direction == ParameterDirection.Input)
								sqlParameter.Value = (bool)parameters[i];
							sqlParameter.SqlDbType = SqlDbType.Bit;
							break;

						case "bigint" :
							if(sqlParameter.Direction == ParameterDirection.Input)
								sqlParameter.Value = (Int64)parameters[i];
							sqlParameter.SqlDbType = SqlDbType.BigInt;
							break;

						case "int" :
							if(sqlParameter.Direction == ParameterDirection.Input)
								sqlParameter.Value = (int)parameters[i];
							sqlParameter.SqlDbType = SqlDbType.Int;
							break;
						
						case "decimal" :
							if(sqlParameter.Direction == ParameterDirection.Input)
								sqlParameter.Value = (double)parameters[i];
							sqlParameter.SqlDbType = SqlDbType.Decimal;
							sqlParameter.Precision = (byte)dt.Rows[i]["NUMERIC_PRECISION"];
							sqlParameter.Scale = (byte)dt.Rows[i]["NUMERIC_SCALE"];
							break;

						case "nvarchar" :
							if(sqlParameter.Direction == ParameterDirection.Input)
								sqlParameter.Value = (string)parameters[i];
							sqlParameter.Size = (int)dt.Rows[i]["CHARACTER_MAXIMUM_LENGTH"];
							sqlParameter.SqlDbType = SqlDbType.NVarChar;
							break;

						case "varchar" :
							if(sqlParameter.Direction == ParameterDirection.Input)
								sqlParameter.Value = (string)parameters[i];
							sqlParameter.Size = (int)dt.Rows[i]["CHARACTER_MAXIMUM_LENGTH"];
							sqlParameter.SqlDbType = SqlDbType.VarChar;
							break;

						case "nchar" :
							if(sqlParameter.Direction == ParameterDirection.Input)
								sqlParameter.Value = (string)parameters[i];
							sqlParameter.Size = (int)dt.Rows[i]["CHARACTER_MAXIMUM_LENGTH"];
							sqlParameter.SqlDbType = SqlDbType.NChar;
							break;

						case "char" :
							if(sqlParameter.Direction == ParameterDirection.Input)
								sqlParameter.Value = (string)parameters[i];
							sqlParameter.Size = (int)dt.Rows[i]["CHARACTER_MAXIMUM_LENGTH"];
							sqlParameter.SqlDbType = SqlDbType.Char;
							break;

						case "ntext" :
							if(sqlParameter.Direction == ParameterDirection.Input)
								sqlParameter.Value = (string)parameters[i];
							sqlParameter.SqlDbType = SqlDbType.NText;
							break;

						case "text" :
							if(sqlParameter.Direction == ParameterDirection.Input)
								sqlParameter.Value = (string)parameters[i];
							sqlParameter.SqlDbType = SqlDbType.Text;
							break;

						case "datetime" :
							if(sqlParameter.Direction == ParameterDirection.Input)
								sqlParameter.Value = (DateTime)parameters[i];
							sqlParameter.SqlDbType = SqlDbType.DateTime;
							break;
						case "smalldatetime" :
							if(sqlParameter.Direction == ParameterDirection.Input)
								sqlParameter.Value = (DateTime)parameters[i];
							sqlParameter.SqlDbType = SqlDbType.DateTime;
							break;
						case "image" :
							if(sqlParameter.Direction == ParameterDirection.Input)
							{
								HttpPostedFile PostedFile = (HttpPostedFile)parameters[i];

								Byte[] FileByteArray = new Byte[PostedFile.ContentLength];
								Stream StreamObject = PostedFile.InputStream;
								StreamObject.Read(FileByteArray,0,PostedFile.ContentLength);

								sqlParameter.Value = FileByteArray;
							}

							sqlParameter.SqlDbType = SqlDbType.Image;
							break;

						case "uniqueidentifier" :
							sqlParameter.SqlDbType = SqlDbType.UniqueIdentifier;
							break;

						default : break;
					}
					#endregion

					sqlCommand.Parameters.Add(sqlParameter);
				}

				sqlCommand.Connection.Open();
				sqlCommand.Prepare();
				sqlDataAdapter.Fill(sqlResult.dataSet ,"Table" );
				sqlResult.Value = (int)sqlCommand.Parameters["@Value"].Value;

				foreach(SqlParameter parameter in sqlCommand.Parameters)
				{
					if(parameter.Direction == ParameterDirection.Output)
						sqlResult.Output.Add(parameter.ParameterName, parameter.Value);
				}
			}
			catch(SqlException e ) 
			{
				throw new Exception( e.Message);
			}
			finally
			{
				sqlCommand.Connection.Close();
			}
			return sqlResult;
		}
	}
}