An Introduction to C# and

Database Programming with

ADO.NET

Antonios Daskos

CSci485 C# Lecture 1 - Antonios Daskos

 Outline
 Object Oriented Programming (OOP)
 .NET Introduction
 C# basics
 VS.NET
 Databases with C# (ADO.NET)

CSci485 C# Lecture 1 - Antonios Daskos

 .NET Framework
 Development languages
 C++, C#, VB, J#, JScript, Python, Perl, Fortran, S#,
Cobol, ASP, XSLT, Eiffel… More than 20 languages
 Integrated Development Environments (IDEs)
 Visual Studio.NET, Mono Project
 Common Language Infrastructure (CLI) and
Common Language Runtime (CLR)
 A specification and its implementation
 Provides a common managed runtime for all
supported languages
 Common Type System (CTS)
CSci485 C# Lecture 1 - Antonios Daskos
 .NET Framework (cont.)
 IL (Intermediate Language) and Just In Time (JIT) Compilation
 Language independence
 Abstraction similar to Java’s Virtual Machine only more generic
 Portability
 Framework Base Class Library
 Namespaces
 Memory Management and Garbage Collection
 Plethora of documentation, examples or tips
 MSDN: provided to you; also available online at
http://msdn.microsoft.com/library/default.asp
 Programming web sites and forums (most of the times your question has
been answered before – needs patience and good search keywords)
 Search engines
 Books, magazines, newsgroups…

CSci485 C# Lecture 1 - Antonios Daskos

 OOP: a software construction
paradigm
 Objects
 Abstract concepts, basic entities that describe internal information
(variables) and the means to access it, change its state, perform actions
(functions)
 In C# (like Java and most OOP languages) everything is an object
 Classes
 Programmer-defined types that model the parts of the system (objects)
 a blueprint for instances
 Instances
 Building blocks of the code
 Allocated in memory sharing the code but not the data
 Methods / Fields
 Primary mechanisms (code common to all instances of a class) and
data variables
 Static parts: belong to a class, accessible and shared by all instances
 Scope

CSci485 C# Lecture 1 - Antonios Daskos

 OOP: a software construction
paradigm (cont.)
 Abstraction
 Inheritance
 Polymorphism
 Encapsulation
 Simple example (don’t worry about all the new things):
…
public class MyNumber {
int val = 20;
public int value() {
return val;
}
public int increase(int how_much) {
val += how_much; //val = val + how much;
return val;
}
}
…
MyNumber m = new MyNumber();
int l = m.value(); // l will be 20
int k = m.increase(12); // k will be 32

CSci485 C# Lecture 1 - Antonios Daskos

 C# Basics
 Case sensitive
 Common data types (primitives):
 bool, byte, short/int/long, float/double, char/string
 Conversion
 implicitly
 float f = 56;
 by casting
 int i = (int)5.6;
 with convert class
 int i = System.Convert.ToInt32(3.25d);
 provided methods
 string s = (5.25).ToString(); //a bit uncommon but it works

CSci485 C# Lecture 1 - Antonios Daskos

 C# Basics II
 Operators:
 +,-,*,/,%
 &,|,…, &&, ||, …
 ++,--…
 <, >, ==,…
 ?
 Decision making:
 if...then…else
 do…while, while, for
 Foreach
e.g foreach (int i in list_of_ints) { … }
 switch (more strict than c++/java)
 Declarations and scope
 Comments like c++ (try Ctrl-K-C and Ctrl-K-U for mass commenting ;-)

CSci485 C# Lecture 1 - Antonios Daskos

 C# Classes
 Everything is a class derived from class Object
 Namespaces to avoid confusion
 Create new instances with keyword new
string s1 = new string(‘T’,3); //s1 = “TTT”
 Reference their methods with dot (.)
string s2 = s2.ToLower (); //s2 = “ttt”
 Parameters inside brackets as in the constructor
 Public/private, static, final
 Try looking at existing examples all around if
unfamiliar with syntax; may find it easier to edit
existing code at first
CSci485 C# Lecture 1 - Antonios Daskos
 C#: Hello world!
 using System;
class MyApp
{
static void Main ()
{
Console.WriteLine ("Hello world!");
}
}
 To execute this you need:
 Install .NET Framework (you already have if you installed VS.NET)
 Write the code as text in a file, e.g. MyApp.cs
 Compile it with C# compiler (csc.exe)
 C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\csc.exe in my machine
 Add it to path for convenience
 Run the produced executable e.g. MyApp.exe

CSci485 C# Lecture 1 - Antonios Daskos

 C#: Useful libraries
 System.Math class
e.g. double d = Math.Pow(5,3); //d = 53
 System.Collections class e.g.ArrayList, Hashtable,
Stack, SortedList, etc.
ArrayList a = new ArraList();
a.Add(5); a.Add(6.7);
int i = (int)a[0];
double d = (double)a[1];
 Regular expressions (System.Text.RegularExpressions)
 File Input/Output (System.IO)
 Networking (System.Net and System.Net.Sockets)
 Threading
CSci485 C# Lecture 1 - Antonios Daskos
 C#: Console
 Two very common functions for console applications:
 Write(…): Writes the specified information to the standard
output stream
 WriteLine(…): Like Write() only followed by the current line
terminator
 Read(…): Reads the next character from the standard input
stream
 ReadLine(…): Reads the next line of characters from the
standard input stream
 To prevent the execution of a program from terminating
when done you can add all the code in a try-block with a
Console.ReadLine() in the finally-block (see next slide for
exception handling)

CSci485 C# Lecture 1 - Antonios Daskos

 C#: Exceptions
 try {
… //code that can generate errors
//and throw exceptions
} catch (ExceptionType1 ex1) {
… //code to handle type1 exceptions
} catch (ExceptionType2 ex2) {
… //code to handle type1 exceptions
} catch {
… //code to handle the rest of the
exceptions
} finally {
… //code to be executed regardless
}
CSci485 C# Lecture 1 - Antonios Daskos
 Visual Studio.NET
 Allows for all sorts of projects (Windows apps,
ASP projects, Web Services, etc)
 New C# project:
 File  New  Project
 Choose “Visual C# Projects” and
 Console Application for simple console programs
 Windows Application for a project with GUI
 Choose project’s name and path
 Possibly the project will contain VS.NET
generated code
CSci485 C# Lecture 1 - Antonios Daskos
 Visual Studio.NET (cont.)
 Colors help reading your code and indicate simple errors
real time
 Compiling or running the code will indicate all compile
time errors. Runtime errors might still occur
 Debugger, code tracing, breakpoints and watches are
invaluable tools – learn the shortcuts and how to use
them
 Intellisense
 Makes typing faster
 No need to remember exact function names
 Demonstrates parameters and short descriptions
 Reveals an object’s methods – you can do miracles by just a
little guessing

CSci485 C# Lecture 1 - Antonios Daskos

 ADO.NET
 Two sets of classes:
 Managed Provider Classes
 Connecting to the database
 Data manipulation through SQL
 Forward-only reading of data
 Generic Data Classes
 Offline data managing
 Non-sequential access and editing of data by C# code

CSci485 C# Lecture 1 - Antonios Daskos

 ADO.NET:
Managed Provider Classes
 Three different sets of classes for accessing:
 SQL Server databases (System.Data.SqlClient)
 OLE (Object linking and embedding) databases ( System.Data.OleDb)
e.g. Access, Oracle
 ODBC (Open DataBase Connectivity) databases (System.Data.Odbc)
e.g. all major databases but slower than OLE DB
 Includes following classes:
 Connection
 Command
 Parameter
 ParameterCollection
 DataReader
 DataAdapter
 CommandBuilder
 Transaction
 Names follow simple pattern:
e.g. SqlConnection, OleDbConnection, OdbcConnection

CSci485 C# Lecture 1 - Antonios Daskos

 ADO.NET:
A simple example
 using System;
using System.Data;
using System.Data.OleDb;

class SimpleExample {
public static void Main() {
…
}
}
CSci485 C# Lecture 1 - Antonios Daskos
 ADO.NET:
OleDbConnection
 string connString = "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\\Test Files\\Sample.mdb;
User Id=admin;Password=;";
OleDbConnection conn = new OleDbConnection(connString);
…
conn.Open();
…
conn.Close();
 Connection string specifies server, database, user & password (if needed)
 Close connections are added to a pool to be reused if the same connection
string is used. Saves much time in comparison to establishing a new
connection

 Public Properties
 ConnectionString, ConnectionTimeout, Database, DatabaseSource,
ServerVersion, State, WorkstationId
 Public Methods
 BeginTransaction(), ChangeDatabase(), Close(), CreateCommand(), Open()

CSci485 C# Lecture 1 - Antonios Daskos

 ADO.NET:
OleDbCommand
 OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.Transaction = trans;
cmd.CommandText = ““DELETE FROM Students“;
 Or:
OleDbCommand cmd =
new OleDbCommand(“DELETE FROM Students“,conn, trans);
 One can also be created by executing CreateCommand() on a Connection
instance (matter of taste)
 Needs an open connection before execution

 Public Properties
 CommandText, CommandTimeout, CommandType, Connection,
DesignTimeVisible, Parameters, Transaction, UpdatedRowSource
 Public Methods
 Cancel(), CreateParameter(), ExecuteNonQuery(), ExecuteReader(),
ExecuteScalar(), ExecuteXmlReader(), Prepare(), ResetCommandTimeout()

CSci485 C# Lecture 1 - Antonios Daskos

 ADO.NET:
ExecuteNonQuery()
 cmd.ExecuteNonQuery()
or
int rowsAffected = cmd.ExecuteNonQuery();
 Returns the number of database rows affected
by the command, if any.
 Can be used for SQL INSERT, UPDATE, and
DELETE statements, stored procedures that
don't return a value or Data Definition Language
(DDL) statements such as CREATE TABLE and
CREATE INDEX

CSci485 C# Lecture 1 - Antonios Daskos

 ADO.NET:
ExecuteReader()
 OleDbDataReader myReader =
mySqlCommand.ExecuteReader();
 Requires an open connection
 You need to close the reader as soon as you’re
done to release the resources

 The execution is parametrized. Possible values:

CloseConnection, Default, KeyInfo,
SchemaOnly, SequentialAccess, SingleResult,
SingleRow
Usually issued without any (default)

CSci485 C# Lecture 1 - Antonios Daskos

 ADO.NET:
OleDbDataReader
 while (myReader.Read()) {
Console.WriteLine(myReader[“FirstName"]);
Console.WriteLine(myReader[0]);
}
 Second way is faster but depends on schema or query
 Middle solution: myReader[ myReader.GetOrdinal(“FirstName”) ]
 IsDBNull() should be used to avoid an OleDbException

 Public Properties
 Depth, FieldCount, IsClosed, RecordsAffected
 Public Methods
 GetBoolean(), GetByte(), GetBytes(), GetChar(), GetChars(),
GetDataTypeName(), GetDateTime(), GetDecimal(), GetDouble(),
GetFieldType(), GetFloat(), GetGuid(), GetInt16(), GetInt32(), GetInt64(),
GetName(), GetOrdinal(), GetSchemaTable(), GetSqlBinary(), GetSqlBoolean(),
GetSqlByte(), GetSqlDateTime(), GetSqlDecimal(), GetSqlDouble(), GetSqlGuid(),
GetSqlInt16(), GetSqlInt32(), GetSqlInt64(), GetSqlMoney(), GetSqlSingle(),
GetSqlString(), GetSqlValue(), GetSqlValues(), GetString(), GetValue(),
GetValues(), IsDBNull(), NextResult(), Read()

CSci485 C# Lecture 1 - Antonios Daskos

 ADO.NET:
OleDbDataReader (cont.)
 OleDbCommand cmd = OleDb.Connection.CreateCommand();
cmd.CommandText = "SELECT * FROM Students; SELECT *
FROM Faculty;”;
…
OleDbDataReader myReader = cmd.ExecuteReader();
…
do {
while (myReader.Read()) {
Console.WriteLine(..);
…
}
} while (myReader.NextResult());
 The outer loop iterates through all result sets (even empty)
 The inner loop processed only the non-empty result sets
 Do…while instead of While because NextResult() advances to next
result set

CSci485 C# Lecture 1 - Antonios Daskos

 ADO.NET:
OleDbTransaction
 OleDbTransaction trans = conn.BeginTransaction();
…
cmd.Transaction = trans;
…
cmd.ExecuteNonQuery();
…
trans.Commit();
OR
trans.Rollback();
 Either all actions succeed and become persistent or (at least) one fails and
the rest get canceled
 try {
…
} catch (Exception ex) {
if (trans != null)
trans.Rollback();
Console.Out.WriteLine("Exception: " + ex.Message);
Console.Out.WriteLine(ex.StackTrace);
}

CSci485 C# Lecture 1 - Antonios Daskos

 ADO.NET:
DataSet objects
 Generic classes, independent of database
 Make local copy, modify and push
changes back to the database
 Before you populate a DataSet you first
need a Connection, a Command, and a
DataAdapter
 Used for random access to data (not
forward-only) and disconnected work
CSci485 C# Lecture 1 - Antonios Daskos
 C# Trip is over
 There’s only one way to learn a programming language
well: experience through trial-and-error
 One day you’ll hear yourself saying “Oh, I’ve had that
same problem before and…”
 Some tips
 spend time programming
 try things you don’t know how to do yet
 question the source of your errors
 try to do it efficiently and elegantly
 Don’t neglect all experience already made public
 The .NET Framework provides classes for most of the common
tasks but they’re not always obvious. Five minutes of searching
in the help files can save you from reinventing the wheel
CSci485 C# Lecture 1 - Antonios Daskos
 References
 “Mastering C# Database Programming
Jason Price, Sybex, 2003
 MSDN

CSci485 C# Lecture 1 - Antonios Daskos