Unravelling a Large Database

• Download source - 52.69 KB

Introduction

I was recently given the task of rewriting a large application with hundreds of tables, views, and stored procedures. There is not one Data Set listed in the Data Sources. All the Inner Joins, etc. are in the views and stored procedures.

I was forced to write a small application that helps me unravel the project and the database. (I have not been able to make this work with Microsoft SQL 2000 which is what I'm working with. I simply backed up the SQL 2000 data and restored it in SQL 2005 to make this program work.)

This image is intentionally blurred. My employer would not be happy if you saw the data (I am a contractor and this program is my property, not theirs). But you can see enough for me to describe what’s going on.

The first datagrid on the left is a list of all the tables in the project.

The second datagrid from the left has a list of all the fields in the selected table (1^st datagrid) showing the field name, data type, and unique. Sort the columns by clicking on the column headers. This is a big help because I can group the decimals, integers, etc. (Example: If I'm looking for every field that could be related to dollar values, I can look at just the group of decimal fields.)

At the top of the screen is a horizontal datagrid with the TOP four rows in the selected database. Seeing just a small sample of the latest data is a big help. Some of these tables contain a hundred thousand rows and simply viewing the latest table data in Visual Studio is not an option.

The third datagrid from the left serves two purposes.

The “Find Files” button searches the text of every file in the project for the selected table name and fills the third datagrid with the results. I use this to find other forms or files that access the selected table.

When the “Auto Find Files” checkbox is checked, the program automatically searches when the selected table changes without having to press the “Find Files” button.

“Only *.cs files” limits the search to *.cs files.

The “Print Field Names” prints the second datagrid columns of field names and types.

The “Offset” values lets me print up to three columns of table fields side by side as shown below:

I run each piece of paper through the printer two or three times. Each pass through the printer prints another table’s fields only offset to the right by the “Offset” value. This helps me establish inner joins, etc. by drawing lines on the paper. The “Offset” increments automatically each time you print getting ready for the next table. Most of these printouts will get taped to a large whiteboard.

The “Find Field Names w/Text” searches every field name’s text of every table in the project for the text entered in the textbox (just below the button), then fills the third datagrid with the results. Example: Searching for “QuoteNum” would return the two tables shown above (over twenty tables in my project).

When the user selects a table from the first datagrid, I call this procedure.

private void tablesDataGridView_RowEnter(object sender, DataGridViewCellEventArgs e)
{
    this.Cursor = Cursors.WaitCursor;
    DataSet DETAIL = new DataSet();
    ConnectionStringSettings cS = ConfigurationManager.ConnectionStrings[1];
    string connString = cS.ConnectionString;
    SqlConnection conn = new SqlConnection(connString);
    
    string table = tablesDataGridView.Rows[e.RowIndex].Cells[0].Value.ToString();
    string sqlstring = "SELECT * FROM " + table + " WHERE 1 = 0";   // dummy to 
							// just get column names
    
    DETAIL.Clear();
    conn.Open();
    SqlDataAdapter adapter = new SqlDataAdapter(sqlstring, conn);
    adapter.Fill(DETAIL);
    conn.Close();
    adapter.Dispose();
    conn.Dispose();
    
    DetaildataGridView.Columns.Clear();
    DetaildataGridView.Rows.Clear();
    DetaildataGridView.ColumnCount = 3;
    DetaildataGridView.RowCount = DETAIL.Tables[0].Columns.Count;
    DetaildataGridView.Columns[0].HeaderText = "Field Name";
    DetaildataGridView.Columns[1].HeaderText = "Type";
    DetaildataGridView.Columns[2].HeaderText = "Unique";
    
    for (int i = 0; i < DETAIL.Tables[0].Columns.Count; ++i)
    {
        DetaildataGridView.Rows[i].Cells[0].Value = 
			DETAIL.Tables[0].Columns[i].ColumnName.ToString();
        string type = DETAIL.Tables[0].Columns[i].DataType.ToString();
        DetaildataGridView.Rows[i].Cells[1].Value = 
		type.Substring(7, type.Length - 7); // get rid of "System" text
        DetaildataGridView.Rows[i].Cells[2].Value = 
		DETAIL.Tables[0].Columns[i].Unique.ToString();
    }
    DetaildataGridView.Width = DetaildataGridView.Columns[0].Width + 
	DetaildataGridView.Columns[1].Width + DetaildataGridView.Columns[2].Width + 21;
    
    DETAIL.Dispose();
    
    getSamples(table, DetaildataGridView.Rows[0].Cells[0].Value.ToString());
    
    this.Cursor = Cursors.Default;
}

This is where I fill the top horizontal datagrid:

private void ListDirectoriesAndFiles(FileSystemInfo[] FSInfo, string SearchString)
{
    string tablelook = tablesDataGridView.SelectedCells[0].Value.ToString();
    foreach (FileSystemInfo i in FSInfo)
    {
        if (i is DirectoryInfo)    // Check to see if this is a DirectoryInfo object.
        {
            DirectoryInfo dInfo = (DirectoryInfo)i;  	// Iterate through all 
						// sub-directories.
            ListDirectoriesAndFiles(dInfo.GetFileSystemInfos(SearchString), SearchString);
        }
        else if (i is FileInfo)   // Check to see if this is a FileInfo object.
        {
            if (OnlyCSfilescheckBox.Checked && i.Name.Contains(".cs") == false) continue;
            string totalpath = i.FullName;
            StreamReader rdr = new StreamReader(i.FullName);
            string line;
            while ((line = rdr.ReadLine()) != null)
            {
                if (line.Contains(tablelook))
                {
                    CSfilesdataGridView.Rows[rowcounter++].Cells[0].Value = i.Name;
                    break;
                }
            }
            rdr.Dispose();
        }
    }
}

I threw this together just for my benefit. The code was not meant for others to use. But I think it could be useful for others with similar problems. I’m new to Visual Studio and C# and use this site a lot and would like to give something back.

I would like to improve this by having a way to search the views and stored procedures, but I haven't been able to figure that out. If anyone knows of a way, please post it here.

History

• 18^th October, 2009: Initial version