Quick and Simple CSharp Windows Form


By:   |   Updated: 2020-07-20   |   Comments (2)   |   Related: More > Application Development

Problem

As a DBA, you'd like to learn more about the .Net platform, but don't have the time because of trying to keep up with disaster recovery, performance monitoring, SSIS, SSRS, SSAS, Azure, Always On availability, replication, extended events, backup encryption, security, clustering, master data services, total data encryption (TDE), auditing, columnstore indexes, issues with SQL 2000, 2005, 2008, 2008 R2, 2012, 2014, 2016, 2017, 2019, licensing, CLR, R, Powershell, NoSQL, MySQL, AWS, and Oracle. And what exactly is polybase ? To begin delving into .Net forms or web stuff you expect you'll need another Visual Studio install or there might be a free version you can use for 6 months when it demands you buy something. Life is too short for the added hassles of getting setup.

Solution

You may not realize it, but you have a CSharp compiler on your PC at this very moment. You might have many. With this compiler you can take some not-to-complex code and compile it into a CSharp exe tool that aids you in your job. And it will take you less than a lunch hour! It might only take 10 minutes. These tools will impress your coworkers and make you more popular. (yeah that's stretching it a bit). The code below will compile into an exe that displays basic information about a SQL Server's databases. It will show: database name, filegroup, file size, percent full, last backup date and file location. This is all done with a T-SQL query that the form sends to the server and autoformats upon return. It is hoped that this article will give you a simple introduction into CSharp forms and how they can help you do your job.

STEP 1: Where is the compiler?

Find out how many CSharp compilers you have and where they are. A CSharp compiler is named "csc.exe".

Run at root:

C:\> dir csc.exe /s 

How many do you see ? I had over a dozen. (see after this paragraph) I'm guessing they put a new version out when you upgrade your management studio or .Net stuff. I'm not 100% sure why they actually send a compiler...perhaps to save space by compiling text files that get uncompressed upon installing and need to be compiled for the app to use ? Maybe someone reading this article knows the whys.

Volume in drive C has no label.
 Volume Serial Number is 3EB5-41A2

 Directory of C:\Windows\Microsoft.NET\Framework\v2.0.50727
07/13/2016  02:33 PM            77,960 csc.exe

 Directory of C:\Windows\Microsoft.NET\Framework\v3.5
05/25/2016  10:56 AM         1,545,864 csc.exe

 Directory of C:\Windows\Microsoft.NET\Framework\v4.0.30319
06/27/2019  01:07 PM         2,170,976 csc.exe

 Directory of C:\Windows\Microsoft.NET\Framework64\v2.0.50727
07/14/2016  02:18 PM            88,712 csc.exe

 Directory of C:\Windows\Microsoft.NET\Framework64\v3.5
05/25/2016  02:29 PM         2,288,264 csc.exe

 Directory of C:\Windows\Microsoft.NET\Framework64\v4.0.30319
06/27/2019  01:07 PM         2,739,296 csc.exe

 Directory of C:\Windows\WinSxS\amd64_netfx-csharp_compiler_csc_b03f5f7f11d50a3a_10.0.14393.0_none_6dcc6d88b8cdbd76
07/14/2016  02:18 PM            88,712 csc.exe

 Directory of C:\Windows\WinSxS\amd64_netfx35linq-csharp_31bf3856ad364e35_10.0.14393.0_none_44a01a852adc7d33
05/25/2016  02:29 PM         2,288,264 csc.exe

 Directory of C:\Windows\WinSxS\amd64_netfx4-csc_exe_b03f5f7f11d50a3a_4.0.14305.0_none_8438590c2d18cc51
07/08/2019  11:59 AM           221,636 csc.exe

 Directory of C:\Windows\WinSxS\amd64_netfx4-csc_exe_b03f5f7f11d50a3a_4.0.15552.17062_none_e384afd1611f03fa
06/27/2019  01:07 PM         2,739,296 csc.exe

 Directory of C:\Windows\WinSxS\x86_netfx-csharp_compiler_csc_b03f5f7f11d50a3a_10.0.14393.0_none_b579a45fcd49e67c
07/13/2016  02:33 PM            77,960 csc.exe

 Directory of C:\Windows\WinSxS\x86_netfx35linq-csharp_31bf3856ad364e35_10.0.14393.0_none_e8817f01727f0bfd
05/25/2016  10:56 AM         1,545,864 csc.exe

 Directory of C:\Windows\WinSxS\x86_netfx4-csc_exe_b03f5f7f11d50a3a_4.0.14305.0_none_cbe58fe34194f557
07/08/2019  12:07 PM           328,592 csc.exe

 Directory of C:\Windows\WinSxS\x86_netfx4-csc_exe_b03f5f7f11d50a3a_4.0.15552.17062_none_2b31e6a8759b2d00
06/27/2019  01:07 PM         2,170,976 csc.exe

     Total Files Listed:
              14 File(s)     18,372,372 bytes
               0 Dir(s)  72,931,565,568 bytes free

Step 2: Create compile line

Create a compile line and stick into a batch file. My line is:

C:\Windows\Microsoft.NET\Framework64\v3.5\csc.exe  Csharp_Query_SQL_Server.cs

I'm using the ancient and venerable .NET 3.5 compiler and the file that contains the CSharp stuff is "Csharp_Query_SQL_Server.cs". Some of you may have had experience with adding the .NET 3.5 stuff before installing SQL Server. I know I have. I think later versions of .NET should compile this code but I haven't tested that.

I called the batch file "Compile_bat.bat" and it contains the above line.

Step 3: Get the code into .cs file

Put the "Csharp_Query_SQL_Server.cs" code into a text file and use the ".cs" extension. Put this file and the compile batch file in the same directory and double-click (or run in DOS) the "Compile_bat" file. Below is the cs code:

// Here is the entire program.  After playing around with it...seems to work.
// 2 labels, 2 buttons, a textbox, and a gridview.

    using System;  
    using System.Drawing; 
    using System.Windows.Forms;

    using System.Data;
    using System.Data.SqlClient;

    class MyForm : System.Windows.Forms.Form  
    {  
        Label label1;  
        Label label2;  
        TextBox txtbx1;  
        Button btn1;  
        Button exit;  

        DataGridView dataGridView1 = new DataGridView();
        BindingSource bindingSource1 = new BindingSource();
        SqlDataAdapter dataAdapter = new SqlDataAdapter();

        public MyForm()  
        {           
            label1 = new Label();  
            label2 = new Label();  
            txtbx1 = new TextBox();  
            btn1 = new Button();  
            exit = new Button(); 
 
            label1.UseMnemonic = true;  
            label1.Text = "Enter Server:";  
            label1.Location = new Point(20, 15); 
            label1.BackColor = System.Drawing.Color.Transparent; 
            //label1.BackColor = Color.LightGray;  
            label1.ForeColor = Color.Maroon;  
            label1.Size = new Size(label1.PreferredWidth, label1.PreferredHeight + 2);  

            txtbx1.Text = "";  
            txtbx1.Location = new Point(25 + label1.PreferredWidth + 5, 15);  
            txtbx1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;  
            txtbx1.BackColor = Color.LightGray;  
            txtbx1.ForeColor = Color.Maroon;  
            txtbx1.Size = new Size(110, 20);  

            btn1.Text = "&Run";  
            btn1.Location = new Point(15 + txtbx1.Location.X + txtbx1.Size.Width, 15);  
            btn1.Size = new Size(50, 20);  
 
            label2.UseMnemonic = true;  
            label2.Text = "                                                                               ";  
            label2.Location = new Point(100 + txtbx1.Location.X + btn1.Location.X , 15);  
            label2.BackColor = Color.LightGray;  
            label2.ForeColor = Color.Maroon;  
            label2.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;  
            label2.Size = new Size(500,20);
            label2.AutoSize = true;

  
            exit.Text = "Exit";  
            exit.Location = new Point(60 + btn1.Location.X , 15);  
            exit.Size = new Size(50, 20);  
            exit.BackColor = Color.Maroon;  
            exit.ForeColor = Color.White;

            dataGridView1.Location = new Point(15, 50);
            dataGridView1.Size = new Size(1000,500);
            dataGridView1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;  
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;

            //Text to be Displayed in the Caption-Title Bar  
            this.Text = "CSharp Form for SQL Server";  
            this.StartPosition = FormStartPosition.CenterScreen;  
            this.AutoScaleBaseSize = new Size(5, 13);  
            this.ClientSize = new Size(1100, 600); //Size except the Title Bar-CaptionHeight  
            this.AutoScroll = true;  
            this.MaximizeBox = false;  
            this.Controls.Add(label1);  
            this.Controls.Add(txtbx1);  
            this.Controls.Add(btn1);  
            this.Controls.Add(exit); 
            this.Controls.Add(label2);  
            this.Controls.Add(dataGridView1); 

            btn1.Click += new EventHandler(Btn_Clicked);  
            exit.Click += new EventHandler(Ext_Clicked);  
        }  
//// Next part runs when the RUN button is clicked.
        public void Btn_Clicked(object ob, EventArgs e)  
        { 
         if (string.IsNullOrEmpty(txtbx1.Text))
            {
                MessageBox.Show("PLEASE ENTER A SERVER ", "ERROR");
            }
            else
            {
                string connectionString = "Data Source=" + txtbx1.Text + ";Initial Catalog=msdb;Integrated Security=True";
           // Next line if you try to use a drop-down
           //     string connectionString = "Data Source=" + comboBoxServ1.Text + ";Initial Catalog=msdb;Integrated Security=True";

                using (SqlConnection SqlCon = new SqlConnection(connectionString))
                {
                    try
                    {
                        SqlCon.Open();
                    }
                    catch
                    {
                        MessageBox.Show("Cant connect to the server. Check your spelling. ", "Connect ERROR");
                    }

                    //  string sql = "select top 20 server_name, backup_finish_date, database_name from backupset where database_name = ''msdb'';";

                    string sql =
                   "Create Table #dbInfo (dId smallint, tgroupID smallint NULL, dbName sysname,  segName varchar(256) NULL, " +
                          " filName varchar(520) NULL, sizeMg decimal(10,2) null, " +
                          " usedMg decimal(10,2) null,  " +
                         " pcntUsed decimal(10,2) null, backup_finish_date Datetime null, recov_type char(15) NULL ) " +
                   " Declare @sSql varchar(1000) " +
                   " Set @sSql = 'Use [?]; " +
                   " Insert #dbInfo (dId, tgroupID, dbName, segName, filName, sizeMg, usedMg, recov_type) " +
                   " Select db_id(), groupid, db_name(),  rtrim(name), filename, Cast(size/128.0 As Decimal(10,2)), " +
                   " Cast(Fileproperty(name, ''SpaceUsed'')/128.0 As Decimal(10,2)), CAST(DATABASEPROPERTYEX(db_name(),  ''RECOVERY''  ) as CHAR(15)) " +
                   " From dbo.sysfiles Order By groupId Desc; ' " +

                   " Exec sp_MSforeachdb @sSql " +
                   " Update #dbInfo Set " +
                   " pcntUsed = (usedMg/sizeMg)*100 " +

                   " Update db  " +
                   " Set db.backup_finish_date = T2.backup_finish_date  " +
                   " From msdb.dbo.backupset T2 " +
                   " Inner Join #dbInfo db on (db.dbName =T2.database_name) " +
                   " where db.dbName = T2.database_name " +
                   " and T2.type = 'D' " +
                   " and db.tgroupID > 0 " +
                   "  and T2.backup_finish_date =   " +
                   " (select MAX(backup_finish_date) from msdb.dbo.backupset T3 inner join msdb.dbo.backupmediafamily bmf on (T3.media_set_id = bmf.media_set_id) " +
                   "  where T3.database_name = T2.database_name and T3.type='D'  AND LEFT(bmf.physical_device_name,1) <> '{') " +

                   " Update db " +
                   " Set db.backup_finish_date = T2.backup_finish_date " +
                   " From msdb.dbo.backupset T2 " +
                   " Inner Join #dbInfo db on (db.dbName =T2.database_name) " +
                   " where db.dbName = T2.database_name  " +
                   " and T2.type = 'L' " +
                   "  and db.tgroupID = 0 " +
                   "  and T2.backup_finish_date =  (select MAX(backup_finish_date) from msdb.dbo.backupset T3 where T3.database_name = T2.database_name and T3.type='L' ) " +

                   " select dbName, segName as 'FileGroup', sizeMg as 'DB Size MB',  pcntUsed as '% Used', recov_type as 'Recovery Type', backup_finish_date as 'Backup Date', filName as 'File' from #dbInfo " +
                   " drop table #dbInfo ";

                    try
                    {
/// Next lines fill a data table and bind to the gridview.
                        SqlDataAdapter sqlDa = new SqlDataAdapter(sql, SqlCon);
                        DataTable dtb1 = new DataTable();
                        sqlDa.Fill(dtb1);
                        dataGridView1.DataSource = dtb1;
/// Next lines setup version info.  There are linefeeds in @@version hence the LEFT.
                        SqlCommand sqlcmd1 = new SqlCommand("Select LEFT(@@version,charindex(char(10),@@version)-1);", SqlCon);
                        SqlDataReader da = sqlcmd1.ExecuteReader();
                        while (da.Read())
                        {
                            label2.Text = da.GetValue(0).ToString();
                        }

                    }
                    catch
                    {
                     // Next line used to debug if you try new/changed query
                     //  MessageBox.Show("query= " + sql, "ERROR");
                    }

                }
            }  //end blank server  
         
        }  
        public void Ext_Clicked(object ob, EventArgs e)  
        {  
            Application.Exit();  
            MessageBox.Show("Successfully Closed", "EXIT");//not Shown! Do you know Why?  
        }  
        public static void Main()  
        {  
            Application.Run(new MyForm());  
        }  
    }  

STEP 4: Run the executable

The directory I put everything in is "C:\Junk\Csharp2". At a DOS prompt: (or double click on it when using windows)

c:\Junk\Csharp2\Csharp_Query_SQL_Server.exe

Enter a SQL Server that you want to examine and hit the "RUN" button. Below is a run of mine. (Note I "******" out the server name since its top secret).

c sharp app

There are some advantages to creating an executable to show query results. You can create the .exe and stick it on a users pc. If the user has rights to the server (the connection is active directory) he/she can see the info. This may be much easier than giving the user tools to run adhoc queries AND its safer. You can also make sure the query is optimized. Even simple reports can be run this way, just change the query. No training is needed for the user to use this.

Next Steps


Last Updated: 2020-07-20


get scripts

next tip button



About the author
MSSQLTips author Bernard Black Bernard Black is a DBA for Beacon Health System in South Bend, IN and has been working with databases for over 15 years.

View all my tips





Comments For This Article




Monday, July 20, 2020 - 11:25:52 AM - Dave B Back To Top (86159)

This is fabulous!


Monday, July 20, 2020 - 9:59:14 AM - Craig Back To Top (86158)

This is really cool, thanks!



download


Recommended Reading

Working with SQL Server Stored Procedures and .NET

Querying SQL Server Tables from .NET

How to Get Started with SQL Server and .NET

Connecting a Java Program to SQL Server

Data Entry for SQL Server - building quick, efficient data input forms using InfoPath





get free sql tips
agree to terms


Learn more about SQL Server tools