On April 14, 2011 I was happy to do my part for CMAP’s Certification Study Group 1 Meeting – ASP.NET. I gave a presentation on Chapter 11 of the MS Certification Book 70-515. Here I have attached my slides and Samples. I hope that this helps someone to pass their Certification Test or at least get a better understanding of LINQ.

Download Slides and Samples: CMAP 70-515 Chapter 11

I came into the C# .NET Development World just as the .NET Framework did a lightning speed transition from 2.0 one day, 3.0 the next day, then 3.5 the very next day and let’s not forget the 3.5 SP1 over that weekend. Now we are in Framework 4.0. That’s a lot of catching up for someone coming in at the Framework Boom.

That being said I jumped straight into Data Access via Typed DataSets and LINQ to SQL. Why bother creating an Untyped DataSet, why create an Untyped Disconnected DataTable. Well, sometimes you have to backwards in order to go forward.

So I will never forget again below are snippets to create an Untyped Disconnected DataTable and an Untyped Connected DataSet.

Untyped Disconnected DataTable

private DataTable GetDataTable()
{
//Create a new instance of a DataTable
DataTable employee = new DataTable(“Employee”);

//Create the Columns that the DataTable will have
DataColumn eid = new DataColumn(“Eid”);
DataColumn firstName = new DataColumn(“FirstName”);
DataColumn lastName = new DataColumn(“LastName”);
DataColumn salary = new DataColumn(“Salary”, typeof(decimal));
DataColumn lastNameFirstName = new DataColumn(“LastName and FirstName”);

//Define the Contraints for the Each Column. There are default Constraints
// I recommend that you investigate those on your own.
eid.DataType = typeof(string);
eid.MaxLength = 10;
eid.Unique = true;
eid.AllowDBNull = false;
eid.Caption = “EID”;
employee.Columns.Add(eid);

firstName.MaxLength = 35;
firstName.AllowDBNull = false;
employee.Columns.Add(firstName);

lastName.AllowDBNull = false;
employee.Columns.Add(lastName);

salary.DefaultValue = 0.00m;
employee.Columns.Add(salary);

lastNameFirstName.DataType = typeof(string);
lastNameFirstName.MaxLength = 70;
lastNameFirstName.Expression = “lastName + ‘, ‘ + firstName”;
employee.Columns.Add(lastNameFirstName);

//Always make sure to create a Primary Key Column for your DataTable.
employee.PrimaryKey = new DataColumn[] { eid };

//Now you can add as many columns as you would like.
employee.Rows.Add(“001DK”, “Dave”, “KoZ”, 10.00m);
employee.Rows.Add(“002DB”, “David”, “Benoit”, 15.00m);
employee.Rows.Add(“003BJ”, “Boney”, “James”, 25.00m);
employee.Rows.Add(“004MA”, “Mindi”, “Abair”, 17.00m);
employee.Rows.Add(“005NB”, “Norman”, “Brown”, 85.00m);

//You can also update columns that you have created.
employee.LoadDataRow(new object[] { “002DB”, “Will”, “Downing”, 20.00m }, LoadOption.OverwriteChanges);

return employee;

}

protected void Page_Load(object sender, EventArgs e)
{
// A little LINQ to the DataTable never hurts
var query = GetDataTable().AsEnumerable()
.Select(em => em);

mygrid.DataSource = query.AsDataView();
mygrid.DataBind();

}

Untyped Connected DataSet

// Create a String variable to store the connection string
string conn = “Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\NORTHWND.MDF;Integrated Security=True;User Instance=True”;
string query = “SELECT * “ +
“FROM Orders “ +
“JOIN Customers ” +
“ON Orders.CustomerID = Customers.CustomerID “;

// Create and instance of a SqlConnection Class.
SqlConnection mySQLconnection = new SqlConnection(conn);
SqlCommand mySqlCommand = new SqlCommand(query, mySQLconnection);
SqlDataAdapter mySqlAdapter = new SqlDataAdapter(mySqlCommand);

//Create an instance of a DataSet and Fill the DataAdapter with it to make the call
//to the Database using the query in the SqlCommand object.
DataSet myDataSet = new DataSet();
mySqlAdapter.Fill(myDataSet);

// A little LINQ to DataSet never hurts
var lq = from c in myDataSet.Tables[0].AsEnumerable()
where c.IsNull(“ShippedDate”) == false &&
c.Field<string>(“Country”) == “USA”
orderby c.Field<DateTime>(“ShippedDate”) descending
select c;

GridView1.DataSource = lq.AsDataView();
GridView1.DataBind();

I want to point out something very important here. You can see that the connection string is hard coding and seats in your code behind, not the most secure thing to do. The best thing to do here is to put you connection string in the Web.conf or app.config file. Here I will put it within the appsetting of the Web.config. So let’s replace the connection string variable with this:

string conn = ConfigurationManager.AppSettings["ConnectionString"].ToString();

Now in the Web.config or App.config, if there isn’t a tag already, let’s add the following:

<appSettings>
<add key=“ConnectionString” value=“Data Source=.\SQLEXPRESS;Initial Catalog=NORTHWND.MDF;Integrated Security=True”/>
</appSettings>

Regular Expressions(RegEx) are notoriously difficult to write. I spent a day or so looking around the Internet trying to find a RegEx to validate a Windows file name. I also reread Chapter 3: Searching, Modifying and Encoding Text – Lesson 1: Forming Regular Expressions in the MCTS 70-536 Microsoft .NET Framework Application Development Foundation.

Unfortunately, the online resources were more cryptic to me than the book. The book only brushed the surface. I’m not saying that I didn’t find anything, just not exactly what I was looking for. Additionally, I don’t have the time to read an entire book on RegEx.

A friend put me on to RegEx Buddy. The RegEx I was able to create from the resources I reviewed was close but not exact. After dropping it into the RegEx Buddy tool I was able to create exactly what I needed. Below is the finish RegEx and the C# Code I used to compare it against a string in my TextBox.

if (Regex.IsMatch(myTextBox.Text, @”^([\w\s]{1,40})([\.])([\w]{2,4})$”))
{
MessageBox.Show(“Match Found”);
}
else
{
MessageBox.Show(“No Match Found”);
}

Last night was the kick off of the 70-536 Certification Group (Microsoft .NET Framework – Application Development Foundation). Today I decided to work through the exercises in the First Chapter . I created a Struct with Members and an Enum. I decided to have some fun seeing what I could to with the Enum.

I created an enum that held some strings and wanted to see if I could load them in a combobox. After doing that I wondered how I could use this to actually do something. So I created a new enum and filled it with the names of colors and loaded them into a combobox and wanted to see if I could change the background  color of a shape by using a selected item from the combobox.

I created a WPF application to hold my combobox and rectangle. I had fun doing it and it didn’t take very long at all. Here is how I did it.

First I created the WPF Application:

<Window x:Class="WpfTestApp.TestWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="TestWindow" Height="300" Width="300"
        Background="#CCCCCC" Loaded="Window_Loaded">
    <Grid>
        <ComboBox Height="23" HorizontalAlignment="Left"
        Margin="80,34,0,0" Name="comboBox1" VerticalAlignment="Top"
        Width="120" SelectionChanged="comboBox1_SelectionChanged"/>
        <Rectangle Height="100" HorizontalAlignment="Left"
        Margin="38,92,0,0" Name="rectangle1" Stroke="Black"
        VerticalAlignment="Top" Width="200" Fill="Transparent"
        StrokeThickness="5" />
    </Grid>
</Window>

Then I created a class to hold my enum:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WpfTestApp
{
    class ColorEnum
    {
        public enum CarColor
        {
            White,
            Black,
            Red,
            Gold,
            Blue,
        }
    }
}

In the Window_Load event I filled the combobox with the items in the enum:

private void Window_Loaded(object sender, RoutedEventArgs e)
{
    comboBox1.ItemsSource =
          Enum.GetValues(typeof(ColorEnum.CarColor));
}

First I created an if statement to test that I was getting the values out. I was but it wasn’t working quite the way I wanted. I was trying to compare the text values in the combobox against the text of the color I wanted from the SolidColorBursh. Then I remembered that the enum was zero (0) based and once I compared them against that I was done.

private void comboBox1_SelectionChanged
(object sender, SelectionChangedEventArgs e)
        {
            var cmbColor = comboBox1.SelectedIndex;

            switch (cmbColor)
            {
                case 0:
                    mySolidColorBrush.Color = Colors.White;
                    rectangle1.Fill = mySolidColorBrush;
                    break;
                case 1:
                    mySolidColorBrush.Color = Colors.Black;
                    rectangle1.Fill = mySolidColorBrush;
                    break;
                case 2:
                    mySolidColorBrush.Color = Colors.Red;
                    rectangle1.Fill = mySolidColorBrush;
                    break;
                case 3:
                    mySolidColorBrush.Color = Colors.Gold;
                    rectangle1.Fill = mySolidColorBrush;
                    break;
                case 4:
                    mySolidColorBrush.Color = Colors.Blue;
                    rectangle1.Fill = mySolidColorBrush;
                    break;
                default:
                    mySolidColorBrush.Color = Colors.Transparent;
                    rectangle1.Fill = mySolidColorBrush;
                    break;
            }
        }

One more thing I want to point out is that mySolidColorBrush was giving me an error because it was out of scope. The reason it was out of scope was because I was trying to put in the switch statement. I also tried to place it in the case portion of the switch statement but I could only put one in. The solution was to place it on the class level.

I was working on a WinForm that currently has a SQL Contains query in it. I want to be able to do the same kind of function in my LINQ query. I first tried to accomplish this by creating an Array and iterate through it using a [foreach loop and a for loop] neither worked. I was only getting the first item in the array each time.

So I created a Generic list and added each item to it as needed. Additionally, I am using check boxes for each of the items to be added to the list. Below is the resulting code.

List<string> status = new List<string>();

if (chkbAll.Checked == true)
{
status.Add(chkbA.Text);
status.Add(chkbB.Text);
status.Add(chkbC.Text);
status.Add(chkbD.Text);
status.Add(chkbE.Text);
}
if (chkbA.Checked == true)
status.Add(chkbA.Text);
if (chkbB.Checked == true)
status.Add(chkbB.Text);
if (chkbC.Checked == true)
status.Add(chkbC.Text);
if (chkbD.Checked == true)
status.Add(chkbD.Text);
if (chkbE.Checked == true)
status.Add(chkbE.Text);

var orderDetail = objDataSet.Tables["objDataTable"].AsEnumerable()
.Where(od => status.Contains(od.Field<string>(“ColumnStatus”)) &&
od.Field<string>(“ColumnName”) == (string.IsNullOrEmpty(cmbBargeID.Text)
? od.Field<string>(“ColumnName”) : cmbBargeID.Text)
od.Field<DateTime>(“ColumnDate”) >= dtpUnloadFrom.Value &&
od.Field<DateTime>(“ColumnDate”) <= dtpUnloadTo.Value)
.OrderBy(od => od.Field<string>(“ColumnStatus”))
.Select(od => od).ToList();

In the list I know I could have hard coded the String values for each. I just like the idea of using the text value for each of the CheckBoxes.