AzurePoolCrossDbGenerator/DbAccess.cs
/// <summary>
/// Get lists of param names, param definitions and param assignments for building calls to remote SPs.
/// </summary>
/// <param name="connectionString"></param>
/// <param name="procedureName"></param>
/// <returns></returns>
Get lists of param names, param definitions and param assignments for building calls to remote SPs.
Get lists of param names param definitions and param assignments for building calls to remote SPs .
public static ProcedureParts GetProcedureParams(string connectionString, string procedureName)
        {
            string queryString = @"select SPECIFIC_NAME, ORDINAL_POSITION, PARAMETER_MODE, PARAMETER_NAME,
	                DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
                from INFORMATION_SCHEMA.PARAMETERS
                where SPECIFIC_NAME = @procedureName order by ORDINAL_POSITION";
            var sbNames = new StringBuilder(); 
            var sbDefs = new StringBuilder(); 
            var sbAsn = new StringBuilder(); 
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@procedureName", procedureName);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    if (!reader.HasRows) return new ProcedureParts();
                    while (reader.Read())
                    {
                        string parName = reader["PARAMETER_NAME"].ToString();
                        string parMode = reader["PARAMETER_MODE"].ToString();
                        string parType = reader["DATA_TYPE"].ToString();
                        string parLen = reader["CHARACTER_MAXIMUM_LENGTH"].ToString();
                        string parPrecision = reader["NUMERIC_PRECISION"].ToString();
                        string parScale = reader["NUMERIC_SCALE"].ToString();
                        if (parMode?.ToLower() != "in") Program.WriteLine($"Param {procedureName}.{parName} has invalid mode {parMode}", ConsoleColor.Red);
                        sbNames.Append(parName);
                        sbNames.Append(",");
                        sbDefs.Append(ItemDefinition(parName, parType, parLen, parPrecision, parScale));
                        sbAsn.Append(parName);
                        sbAsn.Append("=");
                        sbAsn.Append(parName);
                        sbAsn.Append(",");
                    }
                }
                finally
                {
                    reader.Close();
                }
            }
            var procParts = new ProcedureParts();
            procParts.listOfNames = "(" + sbNames.ToString().Trim(',') + ")";
            procParts.fullDef = sbDefs.ToString().Trim(',');
            procParts.selfAssignment = ",\n" + sbAsn.ToString().Trim(',');
            return procParts;
        }
public static ProcedureParts GetProcedureParams ( string connectionString , string procedureName ) { string queryString = @"select SPECIFIC_NAME, ORDINAL_POSITION, PARAMETER_MODE, PARAMETER_NAME,
	                DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
                from INFORMATION_SCHEMA.PARAMETERS
                where SPECIFIC_NAME = @procedureName order by ORDINAL_POSITION" ; var sbNames = new StringBuilder ( ) ; var sbDefs = new StringBuilder ( ) ; var sbAsn = new StringBuilder ( ) ; using ( SqlConnection connection = new SqlConnection ( connectionString ) ) { SqlCommand command = new SqlCommand ( queryString , connection ) ; command . Parameters . AddWithValue ( " " , procedureName ) ; connection . Open ( ) ; SqlDataReader reader = command . ExecuteReader ( ) ; try { if ( ! reader . HasRows ) return new ProcedureParts ( ) ; while ( reader . Read ( ) ) { string parName = reader [ " " ] . ToString ( ) ; string parMode = reader [ " " ] . ToString ( ) ; string parType = reader [ " " ] . ToString ( ) ; string parLen = reader [ " " ] . ToString ( ) ; string parPrecision = reader [ " " ] . ToString ( ) ; string parScale = reader [ " " ] . ToString ( ) ; if ( parMode ? . ToLower ( ) != " " ) Program . WriteLine ( $" Param  { procedureName } . { parName }  has invalid mode  { parMode } " , ConsoleColor . Red ) ; sbNames . Append ( parName ) ; sbNames . Append ( " " ) ; sbDefs . Append ( ItemDefinition ( parName , parType , parLen , parPrecision , parScale ) ) ; sbAsn . Append ( parName ) ; sbAsn . Append ( " " ) ; sbAsn . Append ( parName ) ; sbAsn . Append ( " " ) ; } } finally { reader . Close ( ) ; } } var procParts = new ProcedureParts ( ) ; procParts . listOfNames = " " + sbNames . ToString ( ) . Trim ( ' ' ) + " " ; procParts . fullDef = sbDefs . ToString ( ) . Trim ( ' ' ) ; procParts . selfAssignment = " \n " + sbAsn . ToString ( ) . Trim ( ' ' ) ; return procParts ; }
Get lists of param names, param definitions and param assignments for building calls to remote SPs.
Get lists of param names param definitions and param assignments for building calls to remote SPs .
// query INFORMATION_SCHEMA.PARAMETERS for a list of params per procedure // output containers // contains names only // contains full definitions // contains self-assignments // get the list of params from the DB // check if there was any data returned at all // read the column definition // only IN params are supported // add to the lists // e.g. @param1 nvarchar(255) // e.g. @PPEId=@PPEId, // build output parts in different formats // remove trailing , along the way
None
connectionString
string
None
procedureName
string
None