Study Notes as at Nov 14, 2007

I have decided to put my study notes in a Microsoft Words file rather than to create lengthy and time-consuming blogs.

You can download these files from the links I will put up on this blogsite each time there is an updated file.

However, you will need WinRAR to unzip the files. You can download WinRAR from here:

http://www.rarlab.com/download.htm

My Study Notes as at Nov 14, 2007:

http://rapidshare.com/files/69562940/70526_StudyNotes.rar.html

Once certified, how long is MCTS and MCPD valid?

http://www.microsoft.com/learning/mcp/newgen/faq/default.mspx:

MCTS:
The Technology Series certifications will expire when mainstream support for the version of the product ends.


MCPD:
The Professional Series certifications will require certification refresh every three years from the date of issue.

How To Use Text Files as Database Storage

We are going to create an application that can import a Text File and display it as follows:

The MDI Child form entitled TestTextToDataSetForm shows a Tooltip button called Import Text Database. When you click on this, the program will read the file called:

C:\Users\Paul\Documents\test2.txt

and display it in the GridView Object.

There is also another button called Save which can save any changes made back to the TXT file.

Insert the child MDI form with the following objects in Document Explorer:

The red arrows above indicate the objects. Insert in this order:

1. Dock statusStrip1 at bottom of TestTextToDataSetForm (“the Form”).
2. Dock splitContainer1 in Fill mode docking.
3. Set the splitContainer1 orientation to Horizontal.
4. Reduce splitContainer1.Panel1 to minimum by dragging the separator up.
5. Dock toolStrip1 in splitContainer1.Panel1.
6. Insert Import Text Database Button (with Apple icon).
7. Name it ImportTextDatabaseButton.
8. Insert Save Button (Penguin Icon)
9. Name it SaveButton.

Then insert event handler for the buttons:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using TestTextToDataSet;

using System.IO;

namespace DataBasePractice

{

public partial class TestTextToDataSetForm : Form

{

string path = @"C:\Users\Paul\Documents\test2.txt";

StringBuilder strDataItem = new StringBuilder();

DataSet ds;

public TestTextToDataSetForm()

{

InitializeComponent();

}

private void ImportTextDatabase_Click(object sender, EventArgs e)

{

ds = TextToDataSet.Convert(path, "MyNewTable", "\t");

dataGridView1.DataSource = ds.Tables[0];

dataGridView1.Update();

dataGridView1.Show();

toolStripStatusLabel1.Text = ds.Tables[0].Columns[0].ToString();

insertColumnNames2string();

}

private void insertColumnNames2string()

{

strDataItem.Remove(0, strDataItem.Length);

int numOfCols = ds.Tables[0].Columns.Count, count = 1;

foreach (DataColumn dataCol in ds.Tables[0].Columns)

{

strDataItem.Append(dataCol.ToString());

if (count < style="color: rgb(163, 21, 21);">"\t");

count++;

}

strDataItem.Append(Environment.NewLine);

}

private void Save_Click(object sender, EventArgs e)

{

int numOfCols = ds.Tables[0].Columns.Count, count = 1;

insertColumnNames2string();

StreamWriter sw;

foreach (DataRow theRow in ((DataTable)dataGridView1.DataSource).Rows)

{

foreach (DataColumn theColumn in

(DataTable)dataGridView1.DataSource).Columns)

{

strDataItem.Append(theRow[theColumn].ToString());

if (count < style="color: rgb(163, 21, 21);">"\t");

count++;

}

strDataItem.Append(Environment.NewLine);

count = 1;

}

sw = new StreamWriter(path);

sw.WriteLine(strDataItem);

sw.Close();

}

}

}

Thanks to Dave, the reference using TestTextToDataSet refers to this class below:

(http://www.codeproject.com/cs/database/DataSetFrmDelimTxt.asp?

df=100&forumid=38170&select=2270400&msg=2270400)

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
namespace TestTextToDataSet

{
public class TextToDataSet
{

public TextToDataSet()

{ }

public static DataSet Convert(string File,

string TableName, string delimiter)

{

//The DataSet to Return

DataSet result = new DataSet();

//Open the file in a stream reader.

StreamReader s = new StreamReader(File);

//Split the first line into the columns

string[] columns = s.ReadLine().Split(delimiter.ToCharArray());

//Add the new DataTable to the RecordSet

result.Tables.Add(TableName);

//Cycle the colums, adding those that don't exist yet

//and sequencing the one that do.

foreach (string col in columns)

{

bool added = false;

string next = "";

int i = 0;

while (!added)

{

//Build the column name and remove any unwanted characters.

string columnname = col + next;

columnname = columnname.Replace("#", "");

columnname = columnname.Replace("'", "");

columnname = columnname.Replace("&", "");

//See if the column already exists

if (!result.Tables[TableName].Columns.Contains(columnname))

{

//if it doesn't then we add it here and mark it as added

result.Tables[TableName].Columns.Add(columnname);

added = true;

}

else

{

//if it did exist then we increment the sequencer and try again.

i++;

next = "_" + i.ToString();

}

}

}

//Read the rest of the data in the file.

string AllData = s.ReadToEnd();

//Split off each row at the Carriage Return/Line Feed

//Default line ending in most windows exports.

//You may have to edit this to match your particular file.

//This will work for Excel, Access, etc. default exports.

string[] rows = AllData.Split("\r\n".ToCharArray());

//Now add each row to the DataSet

foreach (string r in rows)

{

//Split the row at the delimiter.

string[] items = r.Split(delimiter.ToCharArray());

//Paul: Solves blank row import problem

if (r != "")

{

//Add the item

result.Tables[TableName].Rows.Add(items);

}

}

//Return the imported data.

return result;

}

}

}

How to Insert Data into Access Database Programmatically

Using Microsoft Access, create a Database called MyAccessDatabase and create a Table within it called MyTable with two fields: ID of type AutoNumber and MyName of type Text.




Then, close the database. Open Visual Studio 2005 and create a new Data Source by clicking on the Data Menu then in the dropdown menuitem, select Add New Datasource... Navigate to the Access database that you created earlier MyAccessDatabase.mdb and add it.

To see the new database, click on the View Menu and select Server Explorer.



Then, select the database MyAccessDatabase from the Server Explorer. The connection string will be displayed in the Properties Grid on the bottom right.


Copy the Connection String. It looks something like this:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\Users\Paul\Documents\Visual Studio 2005\Projects\DataBasePractice\DataBasePractice\MyAccessDatabase.mdb"

Use the Connection String above in your code:

OleDbConnection accessConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Paul\Downloads\Downloaded MCTS\70-526\MyAccessDatabase\MyAccessDatabase.mdb");

private void createTableInAccessToolStripMenuItem_Click(object sender,EventArgs e)
{
OleDbCommand AccessCommand = new OleDbCommand();
AccessCommand.Connection = accessConnection;
AccessCommand.CommandType = CommandType.Text;
AccessCommand.CommandText =
//"CREATE TABLE MyTable ([ID][int] NOT NULL,[Name][nvarchar](10)NULL)";

"INSERT INTO MyTable(ID,MyName) VALUES ('1', 'Adrian')";
AccessCommand.Connection.Open();
AccessCommand.ExecuteNonQuery();
AccessCommand.Connection.Close();
}

The above code will insert a new row (record) with ID = 1 and MyName = Adrian

Open MS Access to verify:



Note:
Note the commented out code (in green):

//"CREATE TABLE MyTable ([ID][int] NOT NULL,[Name][nvarchar](10)NULL)";

It will produce an error if executed for an MS Access Database. This is because the syntax for creating an Access Table is different from the syntax for creating an MS SQL 2005 Table. The command above is for SQL Table.

The Meaning of IDENTITY(1,1) in SQL Server

Using the Northwind Database as an example, a new Table called SalesPersons can be created with the following lines of code:

SqlCommand CreateTableCommand = new SqlCommand();
CreateTableCommand.Connection = NorthwindConnection;
CreateTableCommand.CommandType = CommandType.Text;
CreateTableCommand.CommandText =
"CREATE TABLE SalesPersons " +
"([SalesPersonID][int]IDENTITY(1,1)NOT NULL," +
"[FirstName][nvarchar](50) NULL," +
"[LastName][nvarchar](50) NULL)";

CreateTableCommand.Connection.Open();
CreateTableCommand.ExecuteNonQuery();
CreateTableCommand.Connection.Close();


Notice the SQL Command:

CREATE TABLE SalesPersons
(
[SalesPersonID][int]IDENTITY(1,1)NOT NULL,
[FirstName][nvarchar](50) NULL,
[LastName][nvarchar](50) NULL
)

What is the meaning of IDENTITY(1,1) ?


Answer:
It is the Property of the SalesPersonID. See figure below:



It sets the Property of SalesPersonID to be auto-incrementing (Identity Increment = 1) and also uniquely identifies the rows of this Table (Identity Seed = 1). These are the pre-requisite properties of a potential Primary Key.

How to Perform SQL Query from within Visual Studio 2005 Using Server Explorer

You can perform SQL Query from within Visual Studio 2005 using Server Explorer. I will demonstrate using the Northwind database.

1. Click Server Explorer from the View Menu.
2. Click on the Northwind Database.
3. Click on the Data Menu and select New Query.
4. In the Add Table Dialog that appears, select the Table Tab then select CompanyName field.
5. The SQL Query is automatically created :

SELECT CompanyName FROM Customers

6. Right-Click on the SQL Query above. In the Context Menu that appears, click on :

! Execute SQL

7. The result will be immediately displayed below the SQL Command Box.

8. Alternatively, you can click on the Execute SQL icon in the Toolbar as shown in the red arrow below.

What is Inner Join?

Assuming we have two tables, viz. Customers and Orders:



The relationship between them is one-to-many as indicated by the red arrow. The Customer Primary Key is inserted into the Order Table as a CustomerID field. This implies that each Order can have only one Customer. On the other hand, each Customer can have more than one order. Hence, one-to-many.

In order to display a table which shows which customer ordered what item, we need to join the two tables in a meaningful way. A possible solution is the following SQL Query:

SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

This will yield:




Notice this syntax:

FROM Customers INNER JOIN Orders

It reads as "Join the Customers Table to the Orders Table".

The next condition is:

ON Customers.CustomerID = Orders.CustomerID

It reads as "based on CustomerID". And we know that Customer ID from Customer Table is inserted into Order's Table as a field under the CustomerID field. This is what makes the Join possible.