This first week we will cover everything in chapter 6 except

• flat files (pages 466-480) and
• XML data (pages 524-539).

The  code examples for chapter 5   (Data Binding,  remember)  are still relevant to the classes listed below.

One more thing before we start:  chapters 5 and 6 in Kalani discuss the subject of ADO.NET.   Warning:  ADO.NET is so different from "classic" ADO that it would be dangerous for you to assume that it's a simple extension.

Here are just some of the things that are in Kalani that you must know about:

Four ways of running SQL queries:

• Server Explorer.   The panel in the upper left of the screen that competes for screen space with the .NET ToolBox.
• osql.   A DOS program that probably won't be on the exam because it's independent of .NET,  but handy for investigating SQL.   And you must know SQL well for the exam.  
  • The up and down arrows save you a lot of trouble by giving you previous lines you typed in.
  • osql  will give you header and summary lines you won't get by running SQL commands through .NET.
• The SQL Query Analyzer.   A program independent of .NET,  one of the SQL Server tools which you start from the Start button.   (It's not in the free downloadable MSDE version of SQL Server.)
• Ad-hoc programs running from .NET,  or what are you doing in this course?

SQL Statements, their clauses, functions, and syntax

• The six parts of a  SELECT  statement:
  • SELECT  (and its list of fields)
  • FROM 
  • WHERE 
  • GROUP BY 
  • HAVING 
  • ORDER BY 
  • (Why won't  ROLLUP  and  CUBE  be on the exam?)
• Other keywords that occur in SQL statements:
  • AS  and the two places where it's used
  • INNER JOIN  and its syntax,  including where the paretheses go,  and especially when joining three tables
  • Single quotes.   That's what SQL uses.
  • Where case  (upper/lower)  matters in SQL
  • Where you have to use "[" and "]" around a column name
  • LIKE  and its unexpectedly important followers:
    • Wildcard characters:  % and _
    • How you specify a range of allowed characters between "[" and "]"
    • The difference between LIKE  and IN  and  =
• The SQL functions you must know include at least
  • CAST 
  • Count() 
  • Sum() 
  • Avg() 
  • Max() 
  • Min() 

• The  INSERT  statement: 
  • its list of columns and the VALUES clause
  • copying from another table
• The  UPDATE  statement:
  • the grammar of the   SET  clause
  • using two jointed tables in an   UPDATE
• The  DELETE  statement:
  • whether you need a "*" character

Stored Procedures   

• Very important,  apparently,  although there's an awful aroma of  Eat-your-spinach-or-else  around the whole subject.
• How you create one
• How you get rid of one, for heaven's sake—which isn't in Kalani
• CommandType  and  CommandText  for stored procedures
• The difference between  ExecuteNonQuery()  and  ExecuteScalar()  and what each one returns to the calling C# routine,  and when to use each one
• When you need to   Open()  and   Close()  a connection
• Exactly how to use parameters in stored procedures.   (Kalani, page 457.)   This code will also be very useful for the homework in this course.)
  • How you start the stored procedure:
    • the list of parms
    • AS
  • The use of the  "@"  character
  • The types for variables:
    • char(n)
    • money
    • etc., etc.
  • The correspondence of the parameter types declared in SQL to those declared in C#.   See the table below under  "Correspondence of Parameter Types".
  • How you assign a  SELECTed value to a parameter
  • OUTPUT  and other directions for a parameter.

    The following table is lifted from the same on-line Help page as the large table given below.

    This table shows the values you can use with the ParameterDirection enumeration to set the Direction of the Parameter.

    ParameterDirection	Description
    Input	The parameter is an input parameter. This is the default.
    InputOutput	The parameter is capable of both input and output.
    Output	The parameter is an output parameter.
    ReturnValue	The parameter represents a return value.

  • What namespace you need to add if you use SQL parameters
  • How you create a parameter
  • What you must add your new parameter to
  • The arguments to the  SqlParameter  constructor:
    • @parmName
    • SqlDbType
    • (optional)  length
  • The  SqlDbType  enumeration,  and how it differs from the SQL data types:
    • SqlDbType.Text  etc., etc.
  • How you retrieve the value of a parameter when you're back in your C# routine
  • The  @@IDENTITY  variable.   How do you get it?   Where do you use it?   And what's its data type?

Correspondence of Parameter Types   

The following table was cheerfully lifted from .NET's on-line help.   To find the original,  you have to do a search on "Using Parameters with a DataAdapter"—that's the name of the Help page you're looking for,  but it doesn't appear in the Help index.

Here begins the quote from .NET's on-line help:

The .NET Framework data provider type of a Parameter object is inferred from the .NET Framework type of the Value of the Parameter object,  or from the DbType of the Parameter object.   The following table shows the inferred Parameter type based on the object passed as the Parameter value or the specified DbType.  

.NET Framework type	System.Data.DbType	SqlDbType	OleDbType	OdbcType	OracleType
bool	Boolean	Bit	Boolean	Bit	Byte
byte	Byte	TinyInt	UnsignedTinyInt	TinyInt	Byte
byte[ ]	Binary	VarBinary. This implicit conversion will fail if the byte array is greater than the maximum size of a VarBinary, which is 8000 bytes. For byte arrays larger than 8000 bytes, explicitly set the SqlDbType.	VarBinary	Binary	Raw
char		Inferring a SqlDbType from char is not supported.	Char	Char	Byte
DateTime	DateTime	DateTime	DBTimeStamp	DateTime	DateTime
Decimal	Decimal	Decimal	Decimal	Numeric	Number
double	Double	Float	Double	Double	Double
float	Single	Real	Single	Real	Float
Guid	Guid	UniqueIdentifier	Guid	UniqueIdentifier	Raw
Int16	Int16	SmallInt	SmallInt	SmallInt	Int16
Int32	Int32	Int	Int	Int	Int32
Int64	Int64	BitInt	BigInt	BigInt	Number
object	Object	Variant	Variant	Inferring an OdbcType from Object is not supported.	Blob
string	String	NVarChar. This implicit conversion will fail if the string is greater than the maximum size of an NVarChar, which is 4000 characters. For strings greater than 4000 characters, explicitly set the SqlDbType.	VarWChar	NVarChar	NVarChar
TimeSpan	Time	Inferring a SqlDbType from TimeSpan is not supported.	DBTime	Time	DateTime
UInt16	UInt16	Inferring a SqlDbType from UInt16 is not supported.	UnsignedSmallInt	Int	UInt16
UInt32	UInt32	Inferring a SqlDbType from UInt32 is not supported.	UnsignedInt	BigInt	UInt32
UInt64	UInt64	Inferring a SqlDbType from UInt64 is not supported.	UnsignedBigInt	Numeric	Number
	AnsiString	VarChar	VarChar	VarChar	VarChar
	AnsiStringFixedLength	Char	Char	Char	Char
	Currency	Money	Currency	Inferring an OdbcType from Currency is not supported.	Number
	Date	Inferring a SqlType from Date is not supported.	DBDate	Date	DateTime
	SByte	Inferring a SqlType from SByte is not supported.	TinyInt	Inferring an OdbcType from SByte is not supported.	SByte
	StringFixedLength	NChar	WChar	NChar	NChar
	Time	Inferring a SqlType from Time is not supported.	DBTime	Time	DateTime
	VarNumeric	Inferring a SqlDbType from VarNumeric is not supported.	VarNumeric	Inferring an OdbcType from VarNumeric is not supported.	Number

Here ends the quote from .NET's on-line help.

(Remember,  we do flat files, Kalani's pages 465-480, in the second week.)

The five Data Provider objects

• SqlConnection.   This is the SQL Server version of  Connection  in  System.Data.Common.   It has other implementations,  with names beginning with  Oracle,  or  OleDb,  which includes Microsoft Access.   When we went over chapter 5  (Data Binding),  I showed how to create one in  code.   All five of the members Kalani lists are important:   (Help: "SqlConnection members")
  • ConnectionString   The three required parts  (and no,  I don't know why they're all so long):
    • Data source
    • Initial catalog
    • Integrated security
  • CreateCommand.   You create it on the Connection object,  not the DataAdapter.
  • Open() 
  • Close()  and  Dispose().   The difference is that  Close()  closes the connection,  but  Dispose()  destroys the connection object.   You can reopen a connection that has been closed,  but you have to recreate the connection object if you have disposed of it.
  • In addition, if you get a message reading "Too many Connections open", you have to use Dispose();  Close()  is not sufficient.
  • The requirements for re-using a Connection string.
  • BeginTransaction()  relating to Connection  objects.   Only mentioned in the examples at the end of the chapter.
  • Several of these points about  Connection  are discussed further in the second week on this Kalani chapter.
• SqlCommand.   There are several important members here,  too:
  • CommandText 
  • CommandType 
  • Connection,  unless you create it with  SqlConnection.CreateCommand()
  • CreateParameter() 
  • ExecuteNonQuery().   What does this return,  if anything?
  • ExecuteReader().   Reads forward only.
  • ExecuteScalar()   You have to know what this returns.
  • ExecuteXmlReader() 
  • Parameters   A collection.
• SqlParameter.   Created by  CreateParameter(),  mentioned above.
• SqlDataReader.  
  • This is executed by  SqlCommand.ExecuteReader()  mentioned above.
  • What does this return,  if anything?
  • How does it work?
  • When should you use it?
  • It has a lot of members,  which you only have to know in outline:
    • Read()
    • Close() 
    • GetByte() 
    • GetBoolean() 
    • GetChar() 
    • GetDouble() 
    • GetString()   etc.,  etc,  etc.
• SqlDataAdapter.   The class supplying methods transferring data between DataSets and external databases.
  • Its members fall into two groups:
    • Two commands:
      • Fill() 
      • Update() 
    • Four properties which are created elsewhere and then assigned to this DataAdapter:
      • DeleteCommand 
      • InsertCommand 
      • SelectCommand 
      • UpdateCommand 
  • Distinguish  DataAdapter.Update()  from  SQL UPDATE.

DataSets.   One of the most important classes on the exam.   Discussed extensively the second week on this Kalani chapter.

• There are several important members of this class.   The two most important properties in the class we went over with chapter 5,  on Data Binding:
  • Tables  holds the DataSet's collection of Tables
  • Relations  holds the DataSet's collection of DataRelation objects
• Some of the methods have to do with the relations between the DataGrid  (or other GUI)  and the DataSet:
  • AcceptChanges() 
  • Clear() 
  • GetChanges().   This returns another DataSet object containing just the changes.
  • Reset(),  which Kalani doesn't talk about
  • Merge()
  • AcceptChanges(),  which we will discuss next week
• The following methods,  dealing with XML,  we will deal with next week:
  • GetXml() 
  • GetXmlSchema() 
  • ReadXml() 
  • ReadXmlSchema() 
  • WriteXml() 
  • WriteXmlSchema() 
• DataRelation  objects  (contained in DataSets).   Three important members:
  • the  Name  of the relation 
  • ParentColumns,   a collection
  • ChildColumns,   another collection
  • Note Kalani's example starting on page 502:  he defines two cascading relations to relate three tables.   But all three tables are displayed in a single DataGrid object,  so he never has to specify the DataMember for a second  (or third)  DataGrid,  which, you remember,  is
          "DataTable.Relation name"
• DataTable  objects  (contained in DataSets).   Two important collections:
  • Columns,   and
  • Rows
  
  DataTable  objects  in turn contain two important classes:
  • DataRow  objects,  whose members are concerned with the editing of the contained data
    • BeginEdit() 
    • CancelEdit() 
    • Delete() 
    • EndEdit() 
    • BeginEdit() 
    • RowState,  probably the most important of these properties.   You have to know what it means,  and its enumeration.   And you have to know how this interacts with some of the other methods on the DataSet, like  AcceptChanges(),  Reset(),  etc.   And what is the  RowStateFilter?   We will talk about all of these class members next week.  

  • DataColumn  objects,  whose members describe individual columns.   Note that these are members of  DataTable ,  not of  DataRow,  as you might expect.   See this next example.
  • Kalani gives an example on page 498 in which he goes through the data structure and accesses the contents of each cell:

            foreach (DataTable dt in ds.Tables)
              foreach (DataRow dr in dt.Rows)
                foreach (DataColumn dc in dt.Columns)
                  /* now each cell is at dr[dc] */

    Note:  in the third line,  not   "DataColumn dc in dr.Columns".   Columns are in tables,  not in rows.
  • DataTable  objects  have selection capabilities.   (LIsted out of order here.   Kalani's example occurs on page 506.)
    • You use a statement like this  (Kalani, page 507):

                    DataRow[] adr = dataset.Tables["Customers"].Select(
                    "Country = '" + countryValue + "'",
                    "ContactName ASC");

      where ContactName  is a sort key.   The result of the Select  is an array of  DataRow objects.   Unfortunately,  this array is not quite bindable to a DataGrid.   You won't get an exception,  but you won't get data displayed,  either.
    • There are various rules for expressions,  variables,  and functions,  listed in Kalani on page 508.
    • You can also select DataRows according to  DataViewRowState  constants,  such as  Added, Unchanged,  even  Deleted.   The list is on page 509.   (Yes,  you can do things like selecting deleted rows.)   This is discussed extensively next week.

DataView  objects are independent of both DataSets and DataTables,  although they are created "on" a DataTable.   (Kalani, page 494)

• Keep these distinct from Views created in the database.
• There are a few important members:
  • Find() 
  • FindRows() 
  • RowFilter   (Kalani, page 510)
  • RowStateFilter
  • Sort     (also in Kalani, page 510)
• We will talk a lot about this one next week,  too.

Strongly-Typed  DataSet  objects  (Kalani,  page 499)

• You create them with menu item  "Data / Create Dataset"  after you have a known database table present in your application.   (The simplest way to get a table is to drag one from the Server Explorer.)
• Know how this affects the names of columns—specifically,  that being early-bound,  the column names are available to both the programmer and the IDE,  including Intellisense
• It might be nice to know what happens to the DataSet definition if the table is changed in the database before the next run.

Updating Data in the Database  (Kalani,  page 513.)   As you might expect,  it's complicated.   It's easy enough to change the data appearing in the DataGrid,  but getting it from there to the external database is another matter.  

• You need extra SQL commands,  where we previously needed only a  SELECT  command:
  • UPDATE  makes a change in an existing record in the database
  • INSERT  adds a new record to the database
  • DELETE  drops a record already existing in the database
• You need all three commands to do almost anything,  for the DataAdapter will figure out what changes you have made to the DataSet and call the appropriate commands to change the external database.
• All these commands have to be created,  and you have to supply  SqlParameter  objects,  too.
• Note,  on page 516,  the possibilities of specifying value states when you're updating a database.
• The Delete  command.   Kalani has an example on pages 519 and 520.   Note the difference between deleting rows in a table in the database,  which you can do with a simple  "DELETE FROM ... WHERE ..."  clause,  from dropping rows in a table in a DataSet,  where you must iterate through the DataRows to make sure you have deleted all the matching rows.  
• The exercises at the end of the chapter include an Awful Example of updating more than one table.   See page 551.
• More on this next week,  including discussion of  CommandBuilder.

(Remember,  we do XML, Kalani's pages 524-539, in the second week.)

Handling Data Errors.

• SqlException  objects.
  • There is code copied  (and improved)  from Kalani's page 540  here,  where I have given two examples,  one long and one compact.
  • You must remember that the  SqlException  holds a collection  of  SqlError  objects,  unlike other kinds of Exception we've handled up to now.  
  • It is very difficult to predict whether a given error will cause a set of SQL errors or a single exception of another type.   For instance,  errors in the 
  SqlConnection  string cause both types.
  • The  SqlError  class has a severity property called  Class  (yes it is;  I couldn't make that up).   It's a byte severity code,  1 to 25.   (Help: "SqlException.Class property", but the sample code doesn't include the "Class" property!)
• Concurrency,  or Multi-user Errors.   What happens when you read a record,  change it,  and then try to update it,  only to discover that someone else has changed it between the time of your initial read and your attemped update?   Such problems in concurrency get very involved,  and Kalani merely touches upon the subject.  
  • Optimistic concurrency control will allow an update to succeed only if no one else has updated the row.
  • "Last one wins" concurrency control allows the second update always to succeed.
  • The issue is decided by the presence or absense of an extended  WHERE  clause in your  UPDATE  statement comparing original with current values in the database record.   Then the question is,  how many columns should you check in the  WHERE  clause?
  • Which kind of concurrency control do you get if you drag a table onto your Form from the Server Explorer?   (You do get a lot of the code to create commands done for you if you do this.)

Transactions.   These are SQL transactions,  which combine several SQL actions into one,  so that if one action fails,  you can recover to a known place or condition of data.   Transactions are covered only in the exercises at the end of the chapter,  on page 553.  

• Start by calling  SqlConnection.BeginTransaction()  to create a transaction.
• Assign this transaction to the  Transaction  property of the current  Command  object.
• Put  SqlTransaction.Commit()  or  SqlTransaction.Rollback()  in the appropriate places in your code.

Miscellaneous details

• If you have used an  Open()  command, a  Finally  clause is the perfect place to  Close()  it.
• One thing not in Kalani:  ADO.NET uses SQL classes designed for SQL Server 7 or later.
• One thing that is in Kalani:  SQL Server uses T-SQL,  which implements SQL-92.

Stuff we didn't get to last year

• How to debug SQL stored procedures

I cannot emphasize too much the importance of your mastery of this chapter's subject.   It supplies more exam questions than any other subject—in fact,  the first dozen or so questions on the exam all seemed to me to be Awful Examples of these issues.   If you're not confident of your knowledge of this stuff,  you are going to have a dreadful experience on the test.

See you Saturday.

The questions relevant to week one from the questions at the end of this chapter in short Kalani are 1-5,  7,  9.

The questions relevant to week one from the two tests at the end of short Kalani are

    Test 1:  questions  3  (page 351;  answer:  page 389),  7,  8,  9,  18.

• Question 18 means "fastest for the machine,  not for the programmer".

    Test 2:  questions  36  (page 422;  answer:  page 445),  38,  40,  41,  43,  45,  50.

(Some of these questions may duplicate those in long Kalani.   Both texts are listed here.)