Click here to Skip to main content
Click here to Skip to main content

Tagged as

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

, 16 Sep 2010 CPOL
Rate this:
Please Sign up or sign in to vote.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

B.Panchal

Australia Australia
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 16 Sep 2010
Article Copyright 2010 by B.Panchal
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid