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