ProgramGood.Net

See: http://www.programgood.net/2009/10/20/TestDrivenDevelopmentInNETDALCh5.aspx

ADO.NET (ActiveX Data Objects) consists of two primary parts:

Data provider

These classes provide access to a data source, such as a Microsoft SQL Server.  Each data source has its own set of provider objects, but they each have a common set of utility classes:

• Connection: Provides a connection used to communicate with the data source.
• Command: Used to perform some action on the data source, such as reading, updating, or deleting relational data.
• Parameter: Describes a single parameter to a command. A common example is a parameter to a stored procedure.
• DataAdapter: A bridge used to transfer data between a Data source and a DataSet object (see below).
• DataReader: Used to efficiently process a large list of results one record at a time. It allows records to be accessed in a read-only, forward-only mode, i.e., records have to be accessed in sequential order; they can neither be randomly accessed nor can a record which has been processed previously be accessed again.

DataSets

DataSet objects, a group of classes describing a simple in-memory relational database,

• A DataSet object represents a schema (either an entire database or a subset of one). It can contain tables and relationships between those tables.
  • A DataTable object represents a single table in the database. It has a name, rows, and columns.
    • A DataView object overlays a DataTable and sorts the data (much like an SQL "order by" clause) and filters the records (much like an SQL "where" clause) if a filter is set. An in-memory index is used to facilitate these operations. All DataTables have a default filter, while any number of additional DataViews can be defined, reducing interaction with the underlying database and thus improving performance.
      • A DataColumn represents a column of the table, including its name and type.
      • A DataRow object represents a single row in the table; it allows reading and updating of values in that row, likewise retrieving any rows that are related to it through a primary-key foreign-key relationship.

OLE-DB is a replacement for ODBC.

When adding in a reference (a COM – Component Object Model).. I had to restart VS to get Resharper/VS to recognize and give me intellisense.

The reference was an ADOX reference – ADO ext 2.8 for DDL and Security.  We have to use this as the OLE DB features do not give create new database functionality.

int index;

        string newDB;

        string dbName = txtDatabaseName.Text;

        try

        {

            if (dbName.Length == 0)

                return;

            index = dbName.LastIndexOf('.');

            if (index == -1) // no secondary filename

            {

                dbName += ".mdb";

            }

            string pathName = Application.StartupPath.ToString();

            string combinedName = Path.Combine(pathName, dbName);

            ADOX.CatalogClass myCat = new ADOX.CatalogClass();

            newDB = CONNECTSTRING + combinedName + ";" + CONNECTSTRINGPART2;

            myCat.Create(newDB);

            myCat = null;

        }

CreateDatabase_website

Good bit of functionality, if you double click on an element in the list, it deletes it.

void lstFieldsToAdd_DoubleClick(object sender, EventArgs e)

    {

        ListView.SelectedIndexCollection indexes = lstFieldsToAdd.SelectedIndices;

        foreach (int index in indexes)

        {

            lstFieldsToAdd.Items[index].Remove();

        }

    }

Writing to the database he uses a class in clsDB called ProcessCommand

However to read data he does this from a form object.  So only using the clsDB to get the connection string.

OleDbConnection myDB = new OleDbConnection();

OleDbDataReader myReader;

OleDbCommand myCommand;

clsDB myData = new clsDB("Friend");

try

{

    myDB.ConnectionString = myData.getConnectString + dbName;

    myDB.Open();

    if (txtLastName.Text.Length != 0)

        sql = "SELECT * FROM Friend WHERE LastName = '" +

              txtLastName.Text + "'";

    else

        sql = "SELECT * FROM Friend WHERE ID = " + txtRecordID.Text;

    ClearFields();

    myCommand = new OleDbCommand(sql, myDB);

    myReader = myCommand.ExecuteReader();

    if (myReader.Read() == true)

    {

        record = (int)myReader[0];

        txtRecordID.Text = record.ToString();

Querying and DataGrid

Changing properties on the grid, such as docking to allow window resizing, colour schemes, formatting of cells.

Double click on the database table automatically runs the execute query.  User can drag column headers around, and sort each column.  User can add/edit/del data (code not implemented in this example).

LINQ

var query = from p in numbers // the linq query

                    where p > lo && p < hi

                    select p;

        foreach (var val in query) // display results

        {

            lstOutput.Items.Add(val.ToString());

        }

This is an interesting construct, using anonymous types.  What this is actually saying

var friends = new[]

                      {

                          new {name = "Tom", state = "IN"},

                          new {name = "Alice", state = "VA"},

                          new {name = "Tammy", state = "IN"},

                          new {name = "Ann", state = "KY"},

                      };

var query = from p in friends

                        where p.state == txtLow.Text

                        select p;

            foreach (var val in query)

            {

                lstOutput.Items.Add(val.name.ToString() + ", " + val.state.ToString());

            }