sqlhelper.cs

一阵子

		///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
		/// </remarks>
		/// <param name="connection">a valid SqlConnection</param>
		/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">the stored procedure name or T-SQL command</param>
		/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
		/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
		public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
		{
			//create a command and prepare it for execution
			SqlCommand cmd = new SqlCommand();
			cmd.CommandTimeout = 60;
			PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
			
			//execute the command & return the results
			object retval = cmd.ExecuteScalar();
			
			// detach the SqlParameters from the command object, so they can be used again.
			cmd.Parameters.Clear();
			return retval;
			
		}

		/// <summary>
		/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction. 
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
		/// </remarks>
		/// <param name="transaction">a valid SqlTransaction</param>
		/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">the stored procedure name or T-SQL command</param>
		/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
		public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
		{
			//pass through the call providing null for the set of SqlParameters
			return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
		}

		/// <summary>
		/// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
		/// using the provided parameters.
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
		/// </remarks>
		/// <param name="transaction">a valid SqlTransaction</param>
		/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">the stored procedure name or T-SQL command</param>
		/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
		/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
		public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
		{
			//create a command and prepare it for execution
			SqlCommand cmd = new SqlCommand();
			cmd.CommandTimeout = 60;
			PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
			
			//execute the command & return the results
			object retval = cmd.ExecuteScalar();
			
			// detach the SqlParameters from the command object, so they can be used again.
			cmd.Parameters.Clear();
			return retval;
		}

	
		#endregion ExecuteScalar	

		#region ExecuteXmlReader

		/// <summary>
		/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");
		/// </remarks>
		/// <param name="connection">a valid SqlConnection</param>
		/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
		/// <returns>an XmlReader containing the resultset generated by the command</returns>
		public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
		{
			//pass through the call providing null for the set of SqlParameters
			return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null);
		}

		/// <summary>
		/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
		/// using the provided parameters.
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
		/// </remarks>
		/// <param name="connection">a valid SqlConnection</param>
		/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
		/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
		/// <returns>an XmlReader containing the resultset generated by the command</returns>
		public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
		{
			//create a command and prepare it for execution
			SqlCommand cmd = new SqlCommand();
			cmd.CommandTimeout = 60;
			PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
			
			//create the DataAdapter & DataSet
			XmlReader retval = cmd.ExecuteXmlReader();
			
			// detach the SqlParameters from the command object, so they can be used again.
			cmd.Parameters.Clear();
			return retval;
			
		}

	
		/// <summary>
		/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. 
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders");
		/// </remarks>
		/// <param name="transaction">a valid SqlTransaction</param>
		/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
		/// <returns>an XmlReader containing the resultset generated by the command</returns>
		public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
		{
			//pass through the call providing null for the set of SqlParameters
			return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null);
		}

		/// <summary>
		/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
		/// using the provided parameters.
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
		/// </remarks>
		/// <param name="transaction">a valid SqlTransaction</param>
		/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
		/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
		/// <returns>an XmlReader containing the resultset generated by the command</returns>
		public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
		{
			//create a command and prepare it for execution
			SqlCommand cmd = new SqlCommand();
			cmd.CommandTimeout = 60;
			PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
			
			//create the DataAdapter & DataSet
			XmlReader retval = cmd.ExecuteXmlReader();
			
			// detach the SqlParameters from the command object, so they can be used again.
			cmd.Parameters.Clear();
			return retval;			
		}

	
		#endregion ExecuteXmlReader

		#region Access
		public static DataTable ExecuteDataTable(string connectionString, string commandText)
		{
			OleDbConnection aConnection = new OleDbConnection(connectionString);
			OleDbCommand aCommand = new OleDbCommand(commandText, aConnection);
			aConnection.Open();
			
			//create the DataAdapter & DataTable
			
			OleDbDataAdapter da = new OleDbDataAdapter(aCommand);
			DataTable dt = new DataTable();

			//fill the DataTable using default values for DataTable names, etc.
			da.Fill(dt);

			return dt;
		}

		public static DataTable ExecuteDataTable(string connectionString,  string commandText, params OleDbParameter[] commandParameters)
		{
			OleDbConnection aConnection = new OleDbConnection(connectionString);
			aConnection.Open();

			OleDbCommand aCommand = new OleDbCommand(commandText, aConnection);
					
			aCommand.CommandTimeout = 60;

			///PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);			

			//associate the connection with the command
			aCommand.Connection = aConnection;

			//set the command text (stored procedure name or SQL statement)
			aCommand.CommandText = commandText;
		
			//attach the command parameters if they are provided
			if (commandParameters != null)
			{
				foreach (OleDbParameter p in commandParameters)
				{
					aCommand.Parameters.Add(p);
				}
			}

			//create the DataAdapter & DataTable
			OleDbDataAdapter da = new OleDbDataAdapter(aCommand);			
			DataTable dt = new DataTable();

			//fill the DataTable using default values for DataTable names, etc.
			da.Fill(dt);
			
			// detach the SqlParameters from the command object, so they can be used again.			
			aCommand.Parameters.Clear();
			
			//return the DataTable
			return dt;						
		}
		
		public static string ExecuteScalar(string connectionString,  string commandText)
		{
			OleDbConnection aConnection = new OleDbConnection(connectionString);						
			OleDbCommand aCommand = new OleDbCommand(commandText, aConnection);				
			aCommand.CommandTimeout = 60;
			aConnection.Open();
		
			object retval = aCommand.ExecuteScalar();
			
			aConnection.Close();
			if(retval!=null)
				return retval.ToString();
			else
				return "0";
		}

		public static void ExecuteNonQuery(string connectionString,  string commandText)
		{
			OleDbConnection aConnection = new OleDbConnection(connectionString);			
			OleDbCommand aCommand = new OleDbCommand(commandText, aConnection);				
			aCommand.CommandTimeout = 60;
			aConnection.Open();
		
			aCommand.ExecuteNonQuery();
			
			aConnection.Close();
		
		}


		public static string ExecuteScalar(string connectionString,  string commandText, params OleDbParameter[] commandParameters)
		{
			OleDbConnection aConnection = new OleDbConnection(connectionString);
			aConnection.Open();

			OleDbCommand aCommand = new OleDbCommand(commandText, aConnection);					
			aCommand.CommandTimeout = 60;

			//attach the command parameters if they are provided
			if (commandParameters != null)
			{
				foreach (OleDbParameter p in commandParameters)
				{
					aCommand.Parameters.Add(p);
				}
			}
			object retval = aCommand.ExecuteScalar();
			
			// detach the SqlParameters from the command object, so they can be used again.
			aCommand.Parameters.Clear();
			return retval.ToString();

		}


		public static OleDbParameter MakeInParam2(string ParamName, OleDbType DbType, Int32 Size, object Value) 
		{
			OleDbParameter param;

			if(Size > 0)
				param = new OleDbParameter(ParamName, DbType, Size);
			else
				param = new OleDbParameter(ParamName, DbType);
			
			param.Value = Value;

			return param;
		}

		#endregion

	}


}