Cross check your DBML file for any invalid stored procedure or function in it

• Download demo - 8.6 KB

Introduction

I have been working on silverlight business application for about 1.5 year.
During this time period, we have had lots of changes into stored procedures and
so our DBML file changed a lot as well. Our Development manager wanted to cross
check and clean our DBML file from any non-existent stored procedure or
function. It was difficult exercise to manually go and check each stored procedure or function from DBML and
jump to database to verify its existence.

This difficulty has lead me to develop this small application, which can tell
what stored procedure or function is not valid.

Using the code

This application is developed in c# winforms. It takes DBML file as input, once
you select DBML file, it reads and populates the grid with stored procedure and
function name that exist in DBML file. You can select another DBML file, and the
content of new DBML file will be appended into the grid view (This way you can
validate multiple DBML files' content).

User requires to know the ip address of database, username and password that will
be used to connect to SQL Server. You can tick "Remember" to remember the IP
address, username and database name (I thought, storing database password was
bad idea). and obviously, your IP address should have access to the SQL Server
(i.e. if your SQL Server has firewall setup, your IP address needs to be in
"Allowed" list)

Here is the core code of the application:

private void btnConnectAndAnalyse_Click(object sender, EventArgs e)
{
    try
    {
        // store the app settings if "Remember" was ticked, else clear the app settings
        if (cbRemember.Checked)
        {
            CreateAppSettings();
        }
        else
        {
            ClearAppSettings();
        }

        SqlConnection conn = new SqlConnection("Data Source=" + txtDBAddress.Text + ";Initial Catalog=" + txtDBName.Text + ";Persist Security Info=True;User ID=" + txtDBUserName.Text + ";Password=" + txtDBPassword.Text);
        conn.Open();

        int rowCount = dataGridView1.Rows.Count - 1;
        int rowCountTotalStoredProcAndFunctions = rowCount;
        int rowCountOfValidStoredProcAndFunctions = 0;

        SqlCommand command = conn.CreateCommand();

        bool isOnlyShowInvalid = cbOnlyShowInvalid.Checked;
        string strType = string.Empty;

        if (cbStoredProc.Checked) strType = "type = 'P'";

        if (strType.Length > 0)
        {
            strType += "OR type = 'FN'";
        }
        else if (cbScalerFunction.Checked)
        {
            strType = "type = 'FN'";
        }

        if (strType.Length > 0)
        {
            strType = "(" + strType + ") AND ";
        }

        txtOutput.Text = "Start analysis for database " + txtDBName.Text + Environment.NewLine + Environment.NewLine;

        while (rowCount > 0)
        {
            // read the method name from the grid view
            string storedProcName = dataGridView1.Rows[rowCount - 1].Cells[cnst_xmlattr_Method].Value.ToString();

            // build the command text and pass it to sql command
            command.CommandText = "SELECT * FROM sysobjects WHERE " + strType + " name = '" + storedProcName + "'";

            // execute the command that returns a SqlDataReader
            SqlDataReader reader = command.ExecuteReader();

            // default isExist false
            bool isExist = false;

            // display the results
            while (reader.Read())
            {
                isExist = true;
                rowCountOfValidStoredProcAndFunctions++;
            }

            if (isOnlyShowInvalid)
            {
                if (!isExist)
                    txtOutput.Text += "stored proc " + storedProcName + (isExist ? " Exist" : " do not exist") + Environment.NewLine;
            }
            else
            {
                txtOutput.Text += "stored proc " + storedProcName + (isExist ? " Exist" : " do not exist") + Environment.NewLine;
            }

            // close the connection
            reader.Close();
            rowCount--;
            Application.DoEvents();
        }

        txtOutput.Text += "out of " + rowCountTotalStoredProcAndFunctions + " stored proc in dbml, there are " + rowCountOfValidStoredProcAndFunctions + " only in database";
        txtOutput.Text += Environment.NewLine + Environment.NewLine + "Analysis ends";
        conn.Close();
    }
    catch (Exception)
    {
        MessageBox.Show("Oops, error occurred while cross checking dbml");
    }
}

You can see that code queries the "sysobjects" of the SQL Database and
determines that if the stored procedure or function exist or not

Points of Interest

This application is useful if you want to cross check the cleanliness of your
DBML file, also there is small usage of "ConfigurationManager" to read and write
the AppSettings. During this development exercise, I learned about sysobjects.
This is just small glimpse of what can be done and verified using sysobjects.
When I wanted to clean my DBML file, I googled to see if anyone has already done
anything like this. But surprisingly, I didn't find anything of this sort, so I
decided to just get this done and share with community.

Finally, this is initial version of CrossCheckDBML, any comments and feedback
most welcome. Based on comments and feedback, I intend to work and improve on
the existing code base. I am in the process of uploading demo application for this, which I think, will be very useful for lot of developers out there.

History

* September 5, 2010: CrossCheckDBML v1.0