Cross check your DBML file for any invalid stored procedure or function in it
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