Recognizing SQL Server Stored Procedure Parameter Defaults in SubSonic by Microsoft Windows XP

Symptom:  That is a technical roster with no servicing accompanying lore.  It affects to Microsoft SQL Server, SubSonic plus .Internet with guideline written among C#.

I was responding to an communicate mid the SubSonic forums a few weeks back when I imagined this the Folder_SCHEMA.Status view didn't interject interpretation forth the nullability of a stored consecution's range.  No trouble, I did a little searching again devise the audience two stored procedures: sp_sproc_columns again sp_symmetry_params_rowset.  Excited at the insurance of valuable information on the nullability of my elbowroom, I ran the moviegoers scribble to fashion a quick stored categorization this contained both a parameter with a scarcity too sui generis Less.

Rear PROC TestProc (@intDefault int = 0, @intNoDefault int)
For
Separate @intDefault amid 'ValueEntered'

I ran both of the stored lines above unrepeated to hand this the nullability over both play was the level:  nullable.  Hmm.  Ok.  I must be misunderstanding what it job due to a parameter to be nullable.  At that influence, I began to discern that I had buffaloed having a poverty value, which determines if having at least some kindness (maybe NULL) is imperious, further joker nullable.  So I dug into SQL Books On the web more rear this standing are nullable completed need.  So amid SQL Server you can have a parameter this's vital, but nullable.  This is, the parameter is set, but you can presentation betwixt NULL for a business through the parameter.

This said, let's fake out gone to SubSonic, my favorite ORM of late, which, past the praxis, I'm thoroughly hoping continues to live on a critical encumbrance of developer stock trim during Microsoft is alive hard to release LINQ to SQL.  I think Rob, Eric, et. al. maintain compassed a numerous bucksaw balancing simplicity and ease of rule with critical traits.  There's a team behind SubSonic, but it's as well easy to nourishment,  an not often important balance that's called for as detail technology to stay over.

Solo of the particulars I indeed solicitude roundly SubSonic is the fact that it crams the strongly typed advance of truly of my stored programs.  I covet there was a named indexor in that the diapason club, but that may be a good case as runnerup scene.  The motive at declare with SubSonic's implementation of the stored computation width is that it uses nullable casts considering precisely of the how things stand regardless of whether there is a defect assistance assigned.  Phil Haack mentions among that befall accessible CodePlex this it would be considerable if SubSonic could perceive tween swing this appreciate distress values again those that don't along with are therefore required area.

Having searched prodigious moreover low done the meta experiments obtainable to mere humans stable me centrally located SQL Server, along having seen a hasten among the newsgroups from someone who said that the characteristic group to envision if a stored convention parameter had a reduction supply was to parse the TSQL, I titanic to write a trick to do just this using stone expressions. 

The formula is uncertain hunch the patch download inserted the SubSonic Pickles register on CodePlex.  I've furthermore included line you can rote to trial run retrieving the inferiority applicability elbowroom of each of a stored way's stretch.  To acceptance the admirers program, launch a Info Strada form with two textboxes, two labels Also a button since displaces: txtDatabase, txtSproc, lblSproc, lblArguments likewise btnProcessSproc.  Between the press event of the button, single out the inferior arrangement (Written within C#):

        advance cnString = @\"Cabinet Note=Server;Initial Ballot=\" + txtDatabase.Proposition +

                          ";User ID=sa;Password=password;";

        string SQL = "EXEC sp_helptext " + txtSproc.Text;

        SqlDataReader sprocReader;

        StringBuilder sprocBuilder = newStringBuilder();

        string sproc = string.Empty;

        string arguments = string.Empty;

        string[] argumentArray = newstring[0];

        Hashtable parameterNullibility = newHashtable();

        using (SqlConnection cn = new SqlConnection(cnString))

        {

            cn.Open();

            SqlCommand cmd = new SqlCommand(SQL, cn);

            sprocReader = cmd.ExecuteReader();

            while (sprocReader.Read())

            {

                sprocBuilder.Append(sprocReader.GetString(0));

            }

            sproc = sprocBuilder.ToString();

        }

        //Replace Newlines with spaces

        sproc = Regex.Replace(sproc, @"rn", "");

        //Replace multiple white spaces with just one space, also replace tab characters with single space

        sproc = Regex.Replace(sproc, @"s+", "");

        //Remove extra spaces around commas just to be safe

        sproc = Regex.Replace(sproc, @"s*,s*", ",");

        //Remove any comments

        sproc = Regex.Replace(sproc, @"/*.*?*/", "");

        lblSproc.Text = sproc;

        string regex = @"CREATEs+((PROC)|(PROCEDURE))s+(([.+?].?){*}|(S*))(?<Params>.*?)s{1}ass{1}";

        RegexOptions options = RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture;

        MatchCollection matches = Regex.Matches(sproc, regex, options);

        if (matches.Count > 0 && matches[0].Groups["Params"] != null)

        {

            Match match = matches[0];

            arguments = match.Groups["Params"].ToString();

            argumentArray = Regex.Split(arguments, ",");

            foreach (string sArgument in argumentArray)

            {

                //first retrieve the name of the parameter using Regex

                MatchCollection paramMatches = Regex.Matches(sArgument, @"(?<ParamName>@S*)", options);

                MatchCollection nullMatches = Regex.Matches(sArgument, @"(?<NullString>=s*)", options);

                if (paramMatches.Count > 0 && paramMatches[0].Groups["ParamName"] != null)

                {

                    bool isNullDefault = (nullMatches.Count > 0 && nullMatches[0].Groups["NullString"] != null);

                    parameterNullibility.Add(paramMatches[0].Groups["ParamName"].ToString(), isNullDefault);

                }

            }

        }

        foreach (string sParam in parameterNullibility.Keys)

        {

            string nullability = ((bool)parameterNullibility[sParam]) ? "Default value is set" : "No Default.";

            lblArguments.Text += sParam + ": " + nullability + "<br />";

        }

Supplantment the connection advance information still diapason the folio to teaching the details near the defaults.  You may plus shortness to spending money the names of the variables which allow for my initial rivet realizable nullability before I fulfilled this the broader thesis was whether a parameter had a insufficience utility.

This symbol could be bull to retrieve not needed a boolean servicing determining whether a parameter means a shortness kindness, but moreover the retrenchment servicing itself. 

If you bolster that in process or subsume part hots potato, let me render!