If you recall in my prior post I wrote a small application using the outdated legacy development tool known as Visual FoxPro and I had to write about 26 lines of code to finish the application and it took about 8 minutes. The legacy Visual FoxPro code and application specifications are in the previous blog entry.
When I was assembling the C# code contained in this post, since neither Sam or anyone else wanted to even quote the application let alone write it, I was thinking of all these witty criticisms to preface the code. However I don't even need to say anything else as I truely believe the volume of code this application required, that Sam referred to as a "toy", speaks for itself.
Update 2/23/2009
I was reading through mini microsofts blog which is one of favorites and I found this quote I wanted to share with you as he defined visual studio, wpf, mvc perfectly!
"Going big and broad and trying to enter and dominate every possible software market is exactly what resulted in Microsoft having reactive and broad, shallow features that are rushed out lacking polish and usually lead to user frustration as the shallow experience putters out. "
Enjoy the show!
.Mark
Dot Bloat proudly present "Developing at Light Speed! "
Act 1: The Screen Shots To Prove The Code Runs!
Act 2: The Source CodeApplication Start Up CodeCode auto generated by Visual Studio to launch the application.using System;
using System.Collections.Generic;
using System.Windows.Forms;
namespace sample
{
static class Program
{
///
/// The main entry point for the application.
/// [STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
}
UI Form CodeThe form designer code auto generated by Visual Studionamespace sample
{
partial class Form1
{
///
/// Required designer variable.
/// private System.ComponentModel.IContainer components = null;
///
/// Clean up any resources being used.
/// ///
true if managed resources should be disposed; otherwise, false.
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.button2 = new System.Windows.Forms.Button();
this.button3 = new System.Windows.Forms.Button();
this.dataGridView1 = new System.Windows.Forms.DataGridView();
this.textBox1 = new System.Windows.Forms.TextBox();
this.label1 = new System.Windows.Forms.Label();
((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit();
this.SuspendLayout();
//
// button1
//
this.button1.Location = new System.Drawing.Point(403, 371);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(75, 23);
this.button1.TabIndex = 0;
this.button1.Text = "button1";
this.button1.UseVisualStyleBackColor = true;
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// button2
//
this.button2.Location = new System.Drawing.Point(479, 371);
this.button2.Name = "button2";
this.button2.Size = new System.Drawing.Size(75, 23);
this.button2.TabIndex = 1;
this.button2.Text = "button2";
this.button2.UseVisualStyleBackColor = true;
//
// button3
//
this.button3.Location = new System.Drawing.Point(556, 371);
this.button3.Name = "button3";
this.button3.Size = new System.Drawing.Size(75, 23);
this.button3.TabIndex = 2;
this.button3.Text = "button3";
this.button3.UseVisualStyleBackColor = true;
this.button3.Click += new System.EventHandler(this.button3_Click);
//
// dataGridView1
//
this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
this.dataGridView1.Location = new System.Drawing.Point(1, 27);
this.dataGridView1.Name = "dataGridView1";
this.dataGridView1.Size = new System.Drawing.Size(630, 341);
this.dataGridView1.TabIndex = 3;
//
// textBox1
//
this.textBox1.Location = new System.Drawing.Point(128, 3);
this.textBox1.Name = "textBox1";
this.textBox1.Size = new System.Drawing.Size(344, 20);
this.textBox1.TabIndex = 4;
//
// label1
//
this.label1.AutoSize = true;
this.label1.Location = new System.Drawing.Point(12, 6);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(35, 13);
this.label1.TabIndex = 5;
this.label1.Text = "label1";
this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
//
// Form1
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(633, 399);
this.Controls.Add(this.label1);
this.Controls.Add(this.textBox1);
this.Controls.Add(this.dataGridView1);
this.Controls.Add(this.button3);
this.Controls.Add(this.button2);
this.Controls.Add(this.button1);
this.Name = "Form1";
this.Text = "Form1";
this.Load += new System.EventHandler(this.Form1_Load);
((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit();
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
private System.Windows.Forms.Button button1;
private System.Windows.Forms.Button button2;
private System.Windows.Forms.Button button3;
private System.Windows.Forms.DataGridView dataGridView1;
private System.Windows.Forms.TextBox textBox1;
private System.Windows.Forms.Label label1;
}
}
The C# Code which was manually written for the form
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
//Sample data layer and generic file utilities class
using SampleDataLayer;
using SampleFileUtilities;
namespace sample
{
public partial class Form1 : Form
{
//Create a property to hold the dataset
DataSet oDataSet;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
//Note: I put the code to populate the grid in the form load event
//when developing this app and I forgot to move it to the populate
// button on the form. Therefore there is no click event code in the
// populate button and I'm too lazy to worry about it now - I do apologize.....
//
//This section of code could have been done using the property sheet
//Set the label captions
this.label1.Text = "Selected Filename:";
// Put the captions on the command buttons
this.button1.Text = "Get File";
this.button2.Text = "Populate Grid";
this.button3.Text = "Export Grid";
//Put a caption on the form
this.Text = "Visual Studio Sample";
//End playing with simple properties
//Create the data layer
DataLayer oDataLayer = new DataLayer();
//Create a binding source object for the grid
BindingSource oBindingSource = new BindingSource();
//We need to get a dataset so the grid has something to play with
//I stored it in a property just to make passing it around easier for clarity in the sample.
this.oDataSet = oDataLayer.PopulateDataSet("Select * from person.contact", "dsPersons");
//Now we need to bind the dataset to the BindingSource
oBindingSource.DataSource = this.oDataSet;
//Finally we hand the bindingsource datasource off to the datagrid
this.dataGridView1.DataSource = oBindingSource.DataSource;
this.dataGridView1.DataMember = "dsPersons";
}
private void button1_Click(object sender, EventArgs e)
{
// Create the save file dialog object
SaveFileDialog dlg = new SaveFileDialog();
//Populate the pull down list in the save file dialogue
dlg.Filter = "Excel Files (*.xls)*.xls";
// Show the box and ensure the ok button has been pressed
if (dlg.ShowDialog() == DialogResult.OK)
{
// Update the form with the name of file the user entered
this.textBox1.Text = dlg.FileName;
}
}
private void button3_Click(object sender, EventArgs e)
{
//Get a reference to the fileutilites namespace
FileUtilities oFile = new FileUtilities();
// Send the data to excel
oFile.ExportToExcel(this.oDataSet, this.textBox1.Text.ToString().Trim());
}
}
}
Yes keep scrolling down there is more code yet to come!
Data Access Class Codeusing System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace SampleDataLayer
{
public class DataLayer
{
public DataLayer()
{
}
// For fairness in the comparision I used basically a simple read only data set.
// To send up to the form to bind to the grid.
public DataSet PopulateDataSet(String cSelectCommand, string cDataSetName)
{
//Create a data set that we can populate
DataSet ds = new DataSet();
//Establish the connection to SQL Server
//For the sake of this sample the connection string is hard coded
//in production applications the SQLConnectionStringBuilder would be used.
//The connection string was removed for security reasons
SqlConnection oSqlConnection = new SqlConnection("MyConnectionString");
//Create the sql command object
SqlCommand oSqlCommand = new SqlCommand();
//Tell the sql object what type of statement we are going to work with
oSqlCommand.CommandType = CommandType.Text;
//Pass the sql statement to the sql command object
//In a production application should set other properities such as the timeout etc.
oSqlCommand.CommandText = cSelectCommand;
//Bind the connection object to the sql command
oSqlCommand.Connection = oSqlConnection;
//Create a data adapter
SqlDataAdapter da = new SqlDataAdapter(oSqlCommand);
//Try Catch Throw structure should be used to handle errors
//But I didn't want to be accused of code bloating - Lighten up it was a joke
//Open the sql connection
oSqlConnection.Open();
//Fill the dataset
da.Fill(ds, cDataSetName);
//Close the sql connection
oSqlConnection.Close();
//Return the data set
return ds;
}
}
}
You are in the home stretch 150+ lines of code left! And remember it took VFP 26 lines of code....Utility File Class CodeNote: The less than and greater than signs and slashes in this section of code was messing up the formatting of the blog. Those characters have been replaced with the word slash, lt and gt.
This code, with the exception of the patching I had to put in to extend the datatype support, was originally published on codeplex.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace SampleFileUtilities
{
public class FileUtilities
{
public FileUtilities()
{
}
// Allows the dataset to be exported to excel
//
// Codeplex introduction
// This function takes in a DataSet and file name and writes the DataSet to an Excel worksheet.
//The code is pretty straightforward. Great thing about this function is that, it's technically an
//XML file that is saved as an XLS file. So it can be used as either file format. No more leading zero
//truncation on numbers that look like strings. Example, if you made a tab delimited file and put a field
//such as "00036" (a field that looks like a number but should be regarded as a string), MS Excel would truncate the leading zeros...
//This problem is solved with this method.
//
//I really enjoy the problem solved with this method - In VFP a single line of code SOLVES this problem
public void ExportToExcel(DataSet source, string fileName)
{
System.IO.StreamWriter excelDoc;
excelDoc = new System.IO.StreamWriter(fileName);
const string startExcelXML = "LTxml versionGT SLASH r SLASH nLTWorkbook " +
"xmlns= SLASH "urn:schemas-microsoft-com:office:spreadsheet SLASH " SLASH r SLASH n" +
" xmlns:o= SLASH "urn:schemas-microsoft-com:office:office SLASH " SLASH r SLASH n " +
"xmlns:x= SLASH "urn:schemas- microsoft-com:office:" +
"excel SLASH " SLASH r SLASH n xmlns:ss= SLASH "urn:schemas-microsoft-com:" +
"office:spreadsheet SLASH "GT SLASH r SLASH n LTStylesGT SLASH r SLASH n " +
"LTStyle ss:ID= SLASH "Default SLASH " ss:Name= SLASH "Normal SLASH "GT SLASH r SLASH n " +
"LTAlignment ss:Vertical= SLASH "Bottom SLASH "/GT SLASH r SLASH n LTBorders/GT" +
" SLASH r SLASH n LTFont/GT SLASH r SLASH n LTInterior/GT SLASH r SLASH n LTNumberFormat/GT" +
" SLASH r SLASH n LTProtection/GT SLASH r SLASH n LT/StyleGT SLASH r SLASH n " +
"LTStyle ss:ID= SLASH "BoldColumn SLASH "GT SLASH r SLASH n LTFont " +
"x:Family= SLASH "Swiss SLASH " ss:Bold= SLASH "1 SLASH "/GT SLASH r SLASH n LT/StyleGT SLASH r SLASH n " +
"LTStyle ss:ID= SLASH "StringLiteral SLASH "GT SLASH r SLASH n LTNumberFormat" +
" ss:Format= SLASH "@ SLASH "/GT SLASH r SLASH n LT/StyleGT SLASH r SLASH n LTStyle " +
"ss:ID= SLASH "Decimal SLASH "GT SLASH r SLASH n LTNumberFormat " +
"ss:Format= SLASH "0.0000 SLASH "/GT SLASH r SLASH n LT/StyleGT SLASH r SLASH n " +
"LTStyle ss:ID= SLASH "Integer SLASH "GT SLASH r SLASH n LTNumberFormat " +
"ss:Format= SLASH "0 SLASH "/GT SLASH r SLASH n LT/StyleGT SLASH r SLASH n LTStyle " +
"ss:ID= SLASH "DateLiteral SLASH "GT SLASH r SLASH n LTNumberFormat " +
"ss:Format= SLASH "mm/dd/yyyy;@ SLASH "/GT SLASH r SLASH n LT/StyleGT SLASH r SLASH n " +
"LT/StylesGT SLASH r SLASH n ";
const string endExcelXML = "LT/WorkbookGT";
int rowCount = 0;
int sheetCount = 1;
excelDoc.Write(startExcelXML);
excelDoc.Write("LTWorksheet ss:Name= SLASH "Sheet" + sheetCount + " SLASH "GT");
excelDoc.Write("LTTableGT");
excelDoc.Write("LTRowGT");
for (int x = 0; x LT source.Tables[0].Columns.Count; x++)
{
excelDoc.Write("LTCell ss:StyleID= SLASH "BoldColumn SLASH "GTLTData ss:Type= SLASH "String SLASH "GT");
excelDoc.Write(source.Tables[0].Columns[x].ColumnName);
excelDoc.Write("LT/DataGTLT/CellGT");
}
excelDoc.Write("LT/RowGT");
foreach (DataRow x in source.Tables[0].Rows)
{
rowCount++;
//if the number of rows is GT 64000 create a new page to continue output
if (rowCount == 64000)
{
rowCount = 0;
sheetCount++;
excelDoc.Write("LT/TableGT");
excelDoc.Write(" LT/WorksheetGT");
excelDoc.Write("LTWorksheet ss:Name= SLASH "Sheet" + sheetCount + " SLASH "GT");
excelDoc.Write("LTTableGT");
}
excelDoc.Write("LTRowGT"); //ID=" + rowCount + "
for (int y = 0; y LT source.Tables[0].Columns.Count; y++)
{
System.Type rowType;
rowType = x[y].GetType();
switch (rowType.ToString())
{
case "System.String":
string XMLstring = x[y].ToString();
XMLstring = XMLstring.Trim();
XMLstring = XMLstring.Replace("&", "&");
XMLstring = XMLstring.Replace("GT", "GT");
XMLstring = XMLstring.Replace("LT", "LT");
excelDoc.Write("LTCell ss:StyleID= SLASH "StringLiteral SLASH "GT" +
"LTData ss:Type= SLASH "String SLASH "GT");
excelDoc.Write(XMLstring);
excelDoc.Write("LT/DataGTLT/CellGT");
break;
case "System.DateTime":
//Excel has a specific Date Format of YYYY-MM-DD followed by
//the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
//The Following Code puts the date stored in XMLDate
//to the format above
DateTime XMLDate = (DateTime)x[y];
string XMLDatetoString = ""; //Excel Converted Date
XMLDatetoString = XMLDate.Year.ToString() +
"-" +
(XMLDate.Month LT 10 ? "0" +
XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
"-" +
(XMLDate.Day LT 10 ? "0" +
XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
"T" +
(XMLDate.Hour LT 10 ? "0" +
XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
":" +
(XMLDate.Minute LT 10 ? "0" +
XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
":" +
(XMLDate.Second LT 10 ? "0" +
XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
".000";
excelDoc.Write("LTCell ss:StyleID= SLASH "DateLiteral SLASH "GT" +
"LTData ss:Type= SLASH "DateTime SLASH "GT");
excelDoc.Write(XMLDatetoString);
excelDoc.Write("LT/DataGTLT/CellGT");
break;
case "System.Boolean":
excelDoc.Write("LTCell ss:StyleID= SLASH "StringLiteral SLASH "GT" +
"LTData ss:Type= SLASH "String SLASH "GT");
excelDoc.Write(x[y].ToString());
excelDoc.Write("LT/DataGTLT/CellGT");
break;
case "System.Int16":
case "System.Int32":
case "System.Guid":
case "System.Int64":
case "System.Byte":
excelDoc.Write("LTCell ss:StyleID= SLASH "Integer SLASH "GT" +
"LTData ss:Type= SLASH "Number SLASH "GT");
excelDoc.Write(x[y].ToString());
excelDoc.Write("LT/DataGTLT/CellGT");
break;
case "System.Decimal":
case "System.Double":
excelDoc.Write("LTCell ss:StyleID= SLASH "Decimal SLASH "GT" +
"LTData ss:Type= SLASH "Number SLASH "GT");
excelDoc.Write(x[y].ToString());
excelDoc.Write("LT/DataGTLT/CellGT");
break;
case "System.DBNull":
excelDoc.Write("LTCell ss:StyleID= SLASH "StringLiteral SLASH "GT" +
"LTData ss:Type= SLASH "String SLASH "GT");
excelDoc.Write("");
excelDoc.Write("LT/DataGTLT/CellGT");
break;
default:
throw (new Exception(rowType.ToString() + " not handled."));
}
}
excelDoc.Write("LT/RowGT");
}
excelDoc.Write("LT/TableGT");
excelDoc.Write(" LT/WorksheetGT");
excelDoc.Write(endExcelXML);
excelDoc.Close();
}
}
}
That's all folks....The End
