By: Bernard Black | Updated: 2020-07-20 | Comments (3) | Related: > 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).
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
- Instead of a textbox, make the server input a drop-down.
- Change the query to another query and see the results.
- Link that pointed out the CSC.EXE
- Getting started with Visual Studio and .NET
- How to do the above WITH Visual Studio
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2020-07-20