LibraryWeb/App_Code/SQLBuilder.cs

using Microsoft.Data.SqlClient;
using System.Text;
using System.Data;
using System.Data.SqlTypes;

namespace Library;

public class SQLBuilder
{
	private readonly SqlConnection m_DBConnection;
	private readonly string m_BaseQuery;
	private readonly string? m_OrderBy;
	private List<string>? m_Where;
	private Dictionary<string,object?>? m_Parameters;

	private struct OutputParameter
	{
		public OutputParameter( string id , SqlDbType type )
		{
			m_ID = id;
			m_Parameter = new SqlParameter
			{
				ParameterName = id ,
				SqlDbType = type ,
				Direction = ParameterDirection.Output ,
			};
		}

		public string ID
		{
			get { return m_ID; }
		}

		public SqlParameter Parameter
		{
			get { return m_Parameter; }
		}

		private readonly string m_ID;
		private readonly SqlParameter m_Parameter;
	}
	private List<OutputParameter>? m_OutputParameters;

	private int m_ItemsPerPage = 0;
	private int m_CurrentPage = 0;

	public SQLBuilder( SqlConnection conn , string query , string? order_by = null )
	{
		m_DBConnection = conn;
		m_BaseQuery = query;
		m_OrderBy = order_by;
	}

	public SQLBuilder SetPaging( int page , int items_per_page )
	{
		m_ItemsPerPage = items_per_page;
		m_CurrentPage = page;
		return this;
	}

	public SQLBuilder AddWhere( string where )
	{
		if ( m_Where == null )
			m_Where = new( );
		m_Where.Add( where );
		return this;
	}

	public SQLBuilder AddClause( string where , string parameter , object value )
	{
		AddWhere( where );
		AddParameter( parameter , value );
		return this;
	}

	static private string StripVariable( string input )
	{
		StringBuilder result = new( );
		foreach ( char c in input )
			if ( char.IsLetterOrDigit( c ) )
				result.Append( c );
		return result.ToString( );
	}

	public SQLBuilder AddLike( string column , string value )
	{
		if ( value == null || value.Length <= 0 )
			return this;

		string[] parts = value.Split( ' ' , System.StringSplitOptions.RemoveEmptyEntries );
		if ( parts != null && parts.Length > 0 )
		{
			int part_index = 0;
			string variable_prefix = StripVariable( column );
			foreach ( string p in parts )
			{
				string variable = "@" + variable_prefix + (++part_index).ToString( ) + "@";
				AddClause( column + " LIKE " + variable , variable , "%" + p + "%" );
			}
		}
		return this;
	}

	public SQLBuilder AddParameter( string name , object? value )
	{
		if ( m_Parameters == null )
			m_Parameters = new( );
		m_Parameters[name] = value;
		return this;
	}

	public SQLBuilder AddOutputParameter( string name , SqlDbType type )
	{
		if ( m_OutputParameters == null )
			m_OutputParameters = new( );
		m_OutputParameters.Add( new OutputParameter( name , type ) );
		return this;
	}

	public object? GetOutputParameter( string name )
	{
		if ( m_OutputParameters == null )
			return null;

		foreach ( OutputParameter p in m_OutputParameters )
		{
			if ( p.ID == name )
				return p.Parameter.Value;
		}

		return null;
	}

	public SqlCommand PrepareCommand( )
	{
		string sql = m_BaseQuery;
		if ( m_Where != null && m_Where.Count > 0 )
		{
			sql += " WHERE ( " + m_Where[0] + " ) ";
			for ( int x = 1 ; x < m_Where.Count ; x++ )
				sql += " AND ( " + m_Where[x] + " ) ";
		}
		if ( m_OrderBy != null )
			sql += " ORDER BY " + m_OrderBy;

		if ( m_ItemsPerPage > 0 )
			sql += " OFFSET " + ( m_CurrentPage * m_ItemsPerPage ).ToString( ) + " ROWS FETCH FIRST " + m_ItemsPerPage.ToString( ) + " ROWS ONLY";

		SqlCommand command = new( sql , m_DBConnection );
		command.CommandType = CommandType.Text;
		if ( m_Parameters != null )
		{
			foreach ( var it in m_Parameters )
			{
				if ( it.Value != null )
					command.Parameters.AddWithValue( it.Key , it.Value! );
				else
					command.Parameters.AddWithValue( it.Key , System.DBNull.Value );
			}
		}

		if ( m_OutputParameters != null )
		{
			foreach ( var it in m_OutputParameters )
				command.Parameters.Add( it.Parameter );
		}

		return command;
	}

	public SqlDataReader ExecuteReader( )
	{
		return PrepareCommand( ).ExecuteReader( );
	}

	public int ExecuteNonQuery( )
	{
		return PrepareCommand( ).ExecuteNonQuery( );
	}
	public int ExecuteAndReadInteger( int on_error_value )
	{
		using SqlDataReader reader = ExecuteReader( );
		if ( reader == null || !reader.Read( ) )
			return on_error_value;

		SqlInt32 value = reader.GetSqlInt32( 0 );
		if ( value.IsNull )
			return on_error_value;

		return value.Value;
	}

	public int ReadSingleInteger( int on_error_value )
	{
		using SqlDataReader reader = ExecuteReader( );
		if ( reader == null || !reader.Read( ) )
			return on_error_value;

		SqlInt32 value = reader.GetSqlInt32( 0 );
		if ( value.IsNull )
			return on_error_value;

		return value.Value;
	}

	public string? ReadSingleStringUnsafe( )
	{
		using SqlDataReader reader = ExecuteReader( );
		if ( reader == null || !reader.Read( ) )
			return null;

		return reader.String( 0 );
	}

	public string? ReadSingleString( )
	{
		try
		{
			return ReadSingleStringUnsafe( );
		}
		catch ( Exception )
		{
			return null;
		}
	}
}