| | | 1 | | using System.Text.RegularExpressions; |
| | | 2 | | |
| | | 3 | | // ReSharper disable once CheckNamespace |
| | | 4 | | namespace Microsoft.EntityFrameworkCore; |
| | | 5 | | |
| | | 6 | | /// <summary>Parameterised <c>EXEC</c> commands for SQL Server stored procedures. Only validated names ever go into the |
| | | 7 | | public static partial class SqlServerParameters |
| | | 8 | | { |
| | | 9 | | private const string CmdTypeName = "CmdType"; |
| | | 10 | | |
| | | 11 | | |
| | | 12 | | /// <summary> |
| | | 13 | | /// Builds one <see cref="SqlParameter"/> per dictionary key (name <c>@Key</c>, value as given, <see langword="null" |
| | | 14 | | /// <see cref="DBNull.Value"/>), plus <c>@CmdType</c> when <paramref name="cmdType"/> is given. |
| | | 15 | | /// </summary> |
| | | 16 | | /// <param name="parameterNames">The argument list for the <c>EXEC</c> text, e.g. <c>@A = @A, @B = @B</c>.</param> |
| | | 17 | | /// <param name="cmdType">The <c>@CmdType</c> value; <see langword="null"/> to leave it out.</param> |
| | | 18 | | /// <param name="parameters">The parameters; keys are names with or without <c>@</c>.</param> |
| | | 19 | | /// <returns>The SQL parameters.</returns> |
| | | 20 | | /// <exception cref="ArgumentNullException"><paramref name="parameters"/> is <see langword="null"/>.</exception> |
| | | 21 | | /// <exception cref="ArgumentException">A name isn't a plain SQL identifier, or appears twice.</exception> |
| | | 22 | | public static List<SqlParameter> GetSqlParameters( out string parameterNames, int? cmdType = null, params Dictionary |
| | | 23 | | { |
| | 20 | 24 | | ArgumentNullException.ThrowIfNull( parameters ); |
| | | 25 | | |
| | 20 | 26 | | var sqlParameters = new List<SqlParameter>(); |
| | 20 | 27 | | var names = new HashSet<string>( StringComparer.OrdinalIgnoreCase ); |
| | | 28 | | |
| | | 29 | | |
| | 100 | 30 | | foreach ( var (key, value) in parameters.Where( p => p is not null ).SelectMany( p => p ) ) |
| | | 31 | | { |
| | 32 | 32 | | var name = ParameterName( key ); |
| | | 33 | | |
| | 28 | 34 | | if ( !names.Add( name ) ) { throw new ArgumentException( $"The parameter '@{name}' is given more than once." |
| | | 35 | | |
| | | 36 | | |
| | 28 | 37 | | sqlParameters.Add( new SqlParameter( $"@{name}", value ?? DBNull.Value ) ); |
| | | 38 | | } |
| | | 39 | | |
| | | 40 | | |
| | 16 | 41 | | if ( cmdType is { } type ) |
| | | 42 | | { |
| | 16 | 43 | | if ( !names.Add( CmdTypeName ) ) { throw new ArgumentException( "Pass @CmdType through cmdType, not as a par |
| | | 44 | | |
| | | 45 | | |
| | 16 | 46 | | sqlParameters.Add( new SqlParameter( $"@{CmdTypeName}", type ) ); |
| | | 47 | | } |
| | | 48 | | |
| | | 49 | | |
| | 16 | 50 | | parameterNames = string.Join( ", ", sqlParameters.Select( p => $"{p.ParameterName} = {p.ParameterName}" ) ); |
| | | 51 | | |
| | 16 | 52 | | return sqlParameters; |
| | | 53 | | } |
| | | 54 | | |
| | | 55 | | |
| | | 56 | | /// <summary> |
| | | 57 | | /// Builds a parameterised <c>EXEC [schema].[proc] @A = @A, …</c> command. Only the validated procedure name and the |
| | | 58 | | /// parameter names go into the text; every value travels as a <see cref="SqlParameter"/>. |
| | | 59 | | /// </summary> |
| | | 60 | | internal static (string Sql, SqlParameter[] Parameters) StoredProcedureCommand( |
| | | 61 | | string storedName, int? cmdType, Dictionary<object, object>[] parameters, SqlParameter? output = null |
| | | 62 | | ) |
| | | 63 | | { |
| | 36 | 64 | | var procedure = ProcedureName( storedName ); |
| | 20 | 65 | | var sqlParameters = GetSqlParameters( out var arguments, cmdType, parameters ); |
| | | 66 | | |
| | | 67 | | |
| | 16 | 68 | | if ( output is not null ) |
| | | 69 | | { |
| | 8 | 70 | | var name = $"@{ParameterName( output.ParameterName )}"; |
| | | 71 | | |
| | 8 | 72 | | if ( sqlParameters.Exists( p => string.Equals( p.ParameterName, name, StringComparison.OrdinalIgnoreCase ) ) |
| | | 73 | | { |
| | 0 | 74 | | throw new ArgumentException( $"The parameter '{name}' is reserved for the output value.", nameof( parame |
| | | 75 | | } |
| | | 76 | | |
| | | 77 | | |
| | 8 | 78 | | output.ParameterName = name; |
| | 8 | 79 | | sqlParameters.Add( output ); |
| | 8 | 80 | | arguments = string.Join( ", ", new[] { arguments, $"{name} = {name} OUTPUT" }.Where( a => a.Length > 0 ) ); |
| | | 81 | | } |
| | | 82 | | |
| | | 83 | | |
| | 16 | 84 | | var sql = arguments.Length == 0 ? $"EXEC {procedure}" : $"EXEC {procedure} {arguments}"; |
| | | 85 | | |
| | | 86 | | |
| | 16 | 87 | | return ( sql, sqlParameters.ToArray() ); |
| | | 88 | | } |
| | | 89 | | |
| | | 90 | | |
| | | 91 | | private static string ProcedureName( string storedName ) |
| | | 92 | | { |
| | 36 | 93 | | var parts = ( storedName ?? "" ).Split( '.' ); |
| | | 94 | | |
| | 36 | 95 | | if ( parts.Length is < 1 or > 2 || !parts.All( p => Identifier().IsMatch( p ) ) ) |
| | | 96 | | { |
| | 16 | 97 | | throw new ArgumentException( $"'{storedName}' is not a valid stored-procedure name. Use 'proc', 'schema.proc |
| | | 98 | | } |
| | | 99 | | |
| | | 100 | | |
| | 20 | 101 | | return string.Join( ".", parts.Select( p => $"[{p.Trim( '[', ']' )}]" ) ); |
| | | 102 | | } |
| | | 103 | | |
| | | 104 | | private static string ParameterName( object key ) |
| | | 105 | | { |
| | 40 | 106 | | var name = ( key?.ToString() ?? "" ).Trim().TrimStart( '@' ); |
| | | 107 | | |
| | | 108 | | |
| | 40 | 109 | | return PlainName().IsMatch( name ) ? name : throw new ArgumentException( $"'{key}' is not a valid SQL parameter |
| | | 110 | | } |
| | | 111 | | |
| | | 112 | | [ GeneratedRegex( @"^(?:\[[A-Za-z_][A-Za-z0-9_]*\]|[A-Za-z_][A-Za-z0-9_]*)$" ) ] |
| | | 113 | | private static partial Regex Identifier(); |
| | | 114 | | |
| | | 115 | | [ GeneratedRegex( "^[A-Za-z_][A-Za-z0-9_]*$" ) ] |
| | | 116 | | private static partial Regex PlainName(); |
| | | 117 | | } |