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

MSDEGUI - a GUI tool to help developers use the MSDE database

, 27 Nov 2002
Rate this:
Please Sign up or sign in to vote.
This tool uses ADO.NET to offer browsing of databases and tables, editing values and an SQL window to test queries.

Sample Image - MSDE-GUI.jpg

Introduction

If you've upgraded to Visual Studio .NET, you may not be aware that you have a database server on your hard drive. If you go to <rootdir>/FrameworkSDK/Samples/Setup/msde, there you will find a file called InstallMSDE.exe, which installs the Microsoft SQL Server Desktop Edition. This is basically SQL Server 2000, but designed to work with no more than 5 concurrent users. As far as I can see, the point of this is to allow you as a developer to write code that utilises SQL Server, and then create a product for which your customer will need to buy SQL Server, without your having to make any changes. This is somewhat similar to Oracle, who give their full product away to developers to encourage use of their product.

There's always a catch

Having a database product is all well and good, but sadly MSDE does not come with any GUI tools. If you're a beginner, it can be somewhat worrying to write code and to have only your code's return values to tell you if your database looks the way you expected. Additionally, when you're learning SQL or writing a query that is not simple, you're likely to want to be able to do it in a test environment first. Enter MSDEGUI. This program was my first using ADO.NET, and was written for exactly this reason - as I develop a product with MSDE as my database during development, it really is untenable that I am not able to check on the state of my data. Along the way I learned some ADO.NET (which was the point, after all), which I will share with you as I explain the program.

OleDb vs. Sql

Throughout I am using items with names like SqlConnection, SqlCommand, etc. If you want to use another database, all these objects have equivelents prefixed with OleDb instead of Sql, but as I wrote this to use with MSDE, I used the versions optimised for it.

Why not plug yourself in?

The first thing we need to do is connect to our database. Not surprisingly, the object to do this is called an SqlConnection, and we do it like this:

m_conn = new SqlConnection(sConnection);

try
{
    m_conn.Open();
}
catch(OleDbException e)
{
    MessageBox.Show(e.Message);
    return;
}

Our GUI accepts the a connection string and a database location, the first is used to connect via ADO.NET, the second to connect via SQLDMO. Note that unless both connections work, the program will report failure. This is also a change from the last version, we no longer build the connection string, although defaults are presented in the login dialog.

Listing databases and tables

With some help from fellow CPians, I was able to make the GUI list all databases on the current server with this query: select * from master.dbo.SysDatabases. Having populated the combo-box for databases, we populate our other combo, which lists tables in the current database, like this:

ComboBox cb = (ComboBox)sender;

m_conn.ChangeDatabase(cb.Items[cb.SelectedIndex].ToString());

SqlDataAdapter dataAdapter = 
   new SqlDataAdapter("select * from INFORMATION_SCHEMA.TABLES", 
                                                        m_conn);

DataSet ds = new DataSet();
dataAdapter.Fill(ds);

comboTable.Items.Clear();

foreach (DataRow r in ds.Tables["Table"].Rows)
    comboTable.Items.Add(r["TABLE_NAME"].ToString());

The most important point here is the need to call ChangeDatabase on our connection object. Until I worked that out, it went nowhere.

Using the main screen

So on the main screen we're able to enter a connection string, choose a database and select a table. Once we've done this, we get a grid view of the table, and we're able to browse it and make changes. Any changes you make can be reverted with the revert button, or saved to the database with the Apply button.

This magic is done by the DataGrid component. To fill a DataGrid, we pass our query to a DataAdapter, and call it's fill method. The DataSet we passed in will be filled with a view of the data requested, and we simply attach that DataSet to the grid. To get the grid to show the table right away without the need to expand anything, we need to use a syntax that tells it what table to use. It looks like this:

ComboBox cb = (ComboBox)sender;
m_sDBName = cb.Items[cb.SelectedIndex].ToString();
m_sDBFilter = "";
SqlDataAdapter dataAdapter = 
   new SqlDataAdapter("select * from " + m_sDBName, m_conn);

m_dsTables.Clear();
            
dataAdapter.Fill(m_dsTables, m_sDBName);

dgTables.DataSource = m_dsTables.Tables[m_sDBName].DefaultView;

But wait, there's more

The tool also offers an SQL window, filtering (just type your SQL 'where' clause into the bottom edit box and press 'filter'), and saving of data as XML, and also as text in the SQL window. You can press 'view' in the SQL window to alternate between a text window, and a dataGrid. The grid is nicer, and causes the save button to save as XML, but it does not show things like records affected, or the results of multiple queries.

Visual Studio .NET

After writing this article, several people have posted to present alternative tools, or point to the IDE itself as an alternative. I guess it's inevitable that others would have done what I am doing here, but the fact that so many do would seem to indicate the need for a tool on top of what the IDE offers (although the IDE is quite impressive as well). I'm not claiming to be the best, but I am hosted on the best developer site, so that will have to do.

SQLDMO

The other big request was for SQLDMO support. I had no idea what this means, so thanks to all who asked. The tool now uses SQLDMO to offer adding/removing of databases and of logins. I will shortly add manipulation of triggers and of stored procedures as well. In the meantime, the added items are as follows:

In the SQL dialog we have a button called 'Run File'. It causes a file dialog to allow browsing to an SQL file, then it attempts to execute it. I do not see a way to associate an SQLDMO connection with a database, so if your SQL assumes connection to a specific database, the results are likely not going to be what you're looking for, so beware. The code looks like this:

System.IO.StreamReader filSqlScript;
filSqlScript = System.IO.File.OpenText(dlg.FileName);
m_SQLServerDMO.ExecuteImmediate(filSqlScript.ReadToEnd(),
    SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default,    null);
filSqlScript.Close();

The main page also has a new button, labelled 'Admin'. This brings up a dialog with two combo-boxes, one labelled databases, the other users. In both cases they have a button to their right, and this will show 'Add' if a string is entered which does not correspond to an existing value in the database, and 'Remove' if the item entered does exist. The SQLDMO connection has a number of collections in it, including Databases and Logins. To remove a Database, we pass in it's name, but for some reason to remove a login we pass it's index. We therefore step through them all (they are 1 indexed, asking for item 0 results in an error), until we find the one that matches and remove it. To add an item we create an instance of the item, fill it's members with the required data, and append it to the collection. Here is the code we use for logins. Note that the password is set by calling a method designed for changing passwords. I will add the ability to change passwords shortly.

private void CreateLogin(string sLogin, string sPassword, string sDefault)
{
    SQLDMO.Login login = new SQLDMO.Login();

        login.Name = sLogin;
    login.Database = sDefault;
    login.SetPassword("", sPassword);

    m_SQLServerDMO.Logins.Add(login);
}

private void OnLoginClick(object sender, System.EventArgs e)
{
    try
    {
        switch(btnLogin.Text)
        {
            case "Delete":

                for(int n = 1; n <= m_SQLServerDMO.Logins.Count; ++n)
                    if (m_SQLServerDMO.Logins.Item(n).Name == comboLogin.Text)
                    {
                        m_SQLServerDMO.Logins.Remove(n);
                        break;
                    }
                comboLogin.Items.Remove(comboLogin.Text);
                comboLogin.Text = "";
                btnLogin.Text = "";
                break;
            case "Add":
                CreateDialog dlg = new CreateDialog(m_SQLServerDMO);

                dlg.ShowDialog();

                if (dlg.bOK)
                {
                    CreateLogin(comboLogin.Text, dlg.m_sPassword, dlg.m_sDefault);
                    comboLogin.Items.Add(comboLogin.Text);
                    btnLogin.Text = "";
                }
                break;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

What's next?

Well, first of all I'm going to check CP, and if no-one has offered articles on the other stuff I use in the SQL window particularly, I'l expand the article to cover that. Otherwise I want to add menu options to speed up creating and dropping tables and databases, and a stored procedure window to make dealing with stored procedures easier. Any other suggestions, let me know.

Disclaimer

Thanks to Jason Henderson for the cool article helper tool, it really helped. No animals were harmed in the making of this article, except the cat, and she deserved it.

History

Version 1.2

Added SQLDMO support, including the ability to execute SQL files from disk, and adding and removing tables and logins.

Version 1.1

Fixed the problem with the hard coded string. You should still change the string to one that suits you, but if it fails it will now bring up the connection dialog.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Christian Graus
Software Developer (Senior)
Australia Australia
Programming computers ( self taught ) since about 1984 when I bought my first Apple ][. Was working on a GUI library to interface Win32 to Python, and writing graphics filters in my spare time, and then building n-tiered apps using asp, atl and asp.net in my job at Dytech. After 4 years there, I've started working from home, at first for Code Project and now for a vet telemedicine company. I owned part of a company that sells client education software in the vet market, but we sold that and I worked for the owners for five years before leaving to get away from the travel, and spend more time with my family. I now work for a company here in Hobart, doing all sorts of Microsoft based stuff in C++ and C#, with a lot of T-SQL in the mix.

Comments and Discussions

 
Questionmsdegui Pinmemberdt292917-Aug-07 4:08 
QuestionHow to install PinmemberJKD_8119-Jul-05 3:50 
GeneralProblems Connecting to MSDE Pinmemberstr_test28-Jun-05 18:45 
GeneralRe: Problems Connecting to MSDE PinmemberChristian Graus28-Jun-05 18:51 
GeneralRe: Problems Connecting to MSDE Pinmemberstr_test29-Jun-05 4:46 
GeneralRe: Problems Connecting to MSDE PinmemberChristian Graus29-Jun-05 11:47 
GeneralServer Explorer PinmemberRyan Schreiber24-Nov-04 3:31 
GeneralRe: Server Explorer PinmemberChristian Graus24-Nov-04 7:57 
GeneralThat's all well and good... PinmemberClay McKinney24-May-05 16:44 
GeneralEnterprise manager from Trial PinmemberToAoM20-Jan-04 0:52 
GeneralRe: Enterprise manager from Trial PinmemberBrian Delahunty28-Jun-04 22:16 
GeneralRe: Enterprise manager from Trial PinmemberToAoM29-Jun-04 15:31 
Generalhelp Pinmemberhjain54-Nov-03 22:39 
GeneralRe: help PinmemberSyska17-Dec-03 12:03 
GeneralRe: help PinmemberChristian Graus17-Dec-03 12:05 
GeneralRe: help PinmemberAlex Evans28-Jul-04 22:03 
Questionhow to install Pinmemberwebber1234567-Oct-03 16:16 
AnswerRe: how to install PinmemberGuillermo Rivero7-Oct-03 19:01 
QuestionWhy not use MS Access Adp file? PinmemberOsrald1-Oct-03 20:55 
AnswerRe: Why not use MS Access Adp file? Pinmemberwebber1234567-Oct-03 16:18 
GeneralRe: Why not use MS Access Adp file? PinmemberOsrald8-Oct-03 0:49 
GeneralRe: Why not use MS Access Adp file? Pinmemberwakewakeup6-May-05 1:25 
GeneralRe: Why not use MS Access Adp file? PinmemberOsrald8-May-05 15:43 
GeneralViolates the license agreement. PinsussAnonymous22-Sep-03 10:38 
GeneralRe: Violates the license agreement. PinmemberChristian Graus22-Sep-03 10:42 
GeneralVisual basic PinsussAnonymous18-Sep-03 1:45 
GeneralSeeking for Programmers PinsussAnonymous2-Aug-03 8:55 
GeneralRe: Seeking for Programmers PinsussAnonymous2-Aug-03 8:56 
GeneralRe: Seeking for Programmers Pinmemberrbrennan1031-Dec-03 1:51 
Questionsmall problem? Pinmembergustiman24-Jul-03 1:44 
GeneralCheck out SqlBuddy (WinForm Query Analyzer Clone) PinsussKris Williams17-Jun-03 18:01 
GeneralRe: Check out SqlBuddy (WinForm Query Analyzer Clone) PinmemberAlex Evans28-Jul-04 22:01 
QuestionCrash at startup? PinsussXStone2-Dec-02 22:31 
AnswerRe: Crash at startup? PinsussXStone2-Dec-02 22:42 
Questionwhere? PinmemberFayez Al-Naddaf27-Nov-02 8:52 
AnswerRe: where? PinmemberChristian Graus27-Nov-02 9:57 
GeneralRe: where? PineditorJames T. Johnson27-Nov-02 11:03 
GeneralRe: where? PinmemberSyska16-Dec-03 20:09 
QuestionWhy no SQLDMO? PinmemberFrans Bouma26-Nov-02 4:33 
AnswerRe: Why no SQLDMO? PinmemberChristian Graus26-Nov-02 11:00 
AnswerRe: Why no SQLDMO? Pinmemberpaulb27-Nov-02 16:49 
GeneralRe: Why no SQLDMO? PinmemberChristian Graus27-Nov-02 17:20 
GeneralRe: Why no SQLDMO? Pinmemberpaulb27-Nov-02 17:56 
GeneralRe: Why no SQLDMO? PinmemberChristian Graus27-Nov-02 18:11 
GeneralMS Web Data Admin PinmemberSlavo Furman25-Nov-02 18:57 
GeneralNice work!...A few suggestions PinmemberOldRob23-Nov-02 11:58 
GeneralRe: Nice work!...A few suggestions PinmemberOldRob23-Nov-02 12:51 
GeneralRe: Nice work!...A few suggestions PinmemberChristian Graus25-Nov-02 12:02 
GeneralRe: Nice work!...A few suggestions PinmemberChristian Graus28-Nov-02 0:57 
GeneralRe: Nice work!...A few suggestions PinmemberOldRob28-Nov-02 6:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140814.1 | Last Updated 28 Nov 2002
Article Copyright 2002 by Christian Graus
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid