Friday, 18 February 2011

Chapter 8 Iteration Statements

Iteration is to repeat or loop a certain block of code. the number of time this iteration will take place depends on the condition specified by the programmer. every loop must have a proper ending else it becomes an infinite loop.
The following statements can be used to create loop or iterations
  1. do
  2. for
  3. foreach
  4. while

do statement

The do statement executes a statement or a block of statements enclosed in {} repeatedly until a specified expression evaluates to false.Unlike the while statement, a do-while loop is executed once before the conditional expression is evaluated.
At any point within the do-while block, you can break out of the loop using the break statement. You can step directly to the while expression evaluation statement by using the continue statement; if the expression evaluates to true, execution continues at the first statement in the loop. If the expression evaluates to false, execution continues at the first statement after the do-while loop.
using System;

/**
 * Author       : vblord
 * Date         : 10/03/2011
 * Description  : this is an example of do while statement, it will ask for your name 
 *                 and number of times you want to print it
 *                 
 *                
 */
namespace HelloWorld
{
    class dowhileexample
    {
        static void Main(string[] args)
        {
            Console.Write("Enter your name : ");
            string myname = Console.ReadLine();
            Console.Write("How many times do you want your name to be printed : ");
            int num = int.Parse(Console.ReadLine());
            int i = 0;

            do
            {
                Console.WriteLine("Printing  {0} {1} of {2} times", myname, i, num);
                i = i + 1;
            } while (i < num);
            Console.ReadKey(false);
        }

    }
}

As you can see it is quite simple program, it starts with asking the user for his name and the number of times you want to print it. Then it initializes a variable i to zero. After this the loop starts by printing a message, then it increments the variable i, the last step is to check if the variable i is less than the variable num, if the condition is true then the loop is repeated else the loop finishes and the program exits.

for statement

The for loop executes a statement or a block of statements repeatedly until a specified expression evaluates to false. The for loop is handy for iterating over arrays and for sequential processing
using System;

/**
 * Author       : vblord
 * Date         : 10/03/2011
 * Description  : this is an example of for statement, it will print the table of the number 
 *                 you input
 *                 
 *                
 */
namespace HelloWorld
{
    class forexample
    {
        static void Main(string[] args)
        {
            Console.Write("The number for which you want the table : ");
            int num = int.Parse(Console.ReadLine());
            for (int i = 1; i <= 10; i++)
            {
                Console.WriteLine("{0} * {1} = {2}", num, i, num * i);
            } 
            
            Console.ReadKey(false);
        }

    }
}

Another simple example.The for statement executes the enclosed statement or statements repeatedly as follows:

  • First, the initial value of the variable i is evaluated.
  • Then, while the value of i is less than or equal to 10, the condition evaluates to true, the Console.WriteLine statement is executed and i is reevaluated.
  • When i is greater than 10, the condition becomes false and control is transferred outside the loop. Because the test of conditional expression takes place before the execution of the loop, therefore, a for statement executes zero or more times.All of the expressions of the for statement are optional

foreach statement

The foreach statement repeats a group of embedded statements for each element in an array or an object collection. The foreach statement is used to iterate through the collection to get the desired information, but should not be used to change the contents of the collection to avoid unpredictable side effects.

using System;

/**
 * Author       : vblord
 * Date         : 10/03/2011
 * Description  : this is an example of foreach statement, it will print the all the names
 *                 from the string array
 *                
 */
namespace HelloWorld
{
    class foreachexample
    {
        static void Main(string[] args)
        {
            string[] namearray = new string[] { "sudeep", "sanjeev", "ashish", "tia", "aanchal" };
            foreach (string name in namearray)
            {
                Console.WriteLine(name);
            }
            Console.ReadKey(false);
        }
    }
}

Sample Output

while statement

The while statement executes a statement or a block of statements until a specified expression evaluates to false.Because the test of the while expression takes place before each execution of the loop, a while loop executes zero or more times. This differs from the do loop, which executes one or more times.
A while loop can be terminated when a break, goto, return, or throwstatement transfers control outside the loop. To pass control to the next iteration without exiting the loop, use the continuestatement.

Thursday, 17 February 2011

Chapter 9 Arrays

Arrays are one of the most useful and powerful tools of programing, they are used everywhere from the mundane task like sorting and searching to the exotic like 3-D graphics and modelling. Lets look at the concept of an array.
If we have 100 similar data or homogeneous data then instead of declaring 100 same type of variables we take an array, it is a collection of values with the same or similar data type.Again if we simplify it a bit further then the best example will be a row of houses in a colony. Lets assume you live on the E-Block of colony or city. But is it enough to say to your friend "Come visit me, my address is E-Block". the answer is no because in E-Block there can be thousands of houses which look alike, therefore you qualify your address with a number. That number identifies your house in that block for e.g E-1209. Same can be said about an array, instead of taking different variables we take one and each cell under this variable has a number as an address called index. This concept is further explained in the diagram below.

C# provides with you a 3 types of array which derive from the framework class System.Array they are Single-Dimensional, Multidimensional or Jagged.

Single Dimensional Array


Syntax for declaring an array of this type is
<datatype>[]<variable name> = new <data type>[size];
This can be illustrated in C# by the following example
int[] myArr=new int[10];
This array contains the elements from array[0] to array[9]. The new operator is used to create the array and initialize the array elements to their default values. In this example, all the array elements are initialized to zero.
If you want you can initialize array at the time of declaration, in this case size specifier is not needed
int[] myArr=new int[]{1,45,67,89,90};
int[] myArr22={1,45,67,89,90};


Multidimensional Array


As mentioned above an array can have more than one dimension. the most popular 2-Dimensional and 3-dimensional array. Most of you will seldom use more than 2 dimensions but you can have as many dimensions as you want
int[,] myArr=new int[5,6]
In the above example we declare an array that has 5 rows and 6 columns. Initialization can be done at the time of declaration too
int[,] myArr=new int[,]{{1,2},{44,55},{12,45}};
In the above example we declare and initialize an array with 3 rows and 2 columns.
There is another way of doing the above when declaration and initialization need to be separated
int[,] array5;
array5 = new int[,] { { 1, 2 }, { 3, 4 }, { 5, 6 }, { 7, 8 } };

Jagged Arrays


A jagged array is an array whose elements are arrays. The elements of a jagged array can be of different dimensions and sizes. A jagged array is sometimes called an "array of arrays."
After declaring the array we need to initialize every row of that array because they themselves are arrays too
int[][] myJArray = new int[3][];
myJArray[0] = new int[2];
myJArray[1] = new int[3];
myJArray[2] = new int[8];
/*It can also be initialized too
myJArray[0] = new int[] { 111, 123, };
myJArray[1] = new int[] { 6, 24, 14 };
myJArray[2] = new int[] { 6, 5,8,9,11,99,44,32 };

*/

There is another way of declaring everything in a single line
int[][] myJArray = new int[][] 
{
    new int[] {1,3,5,7,9},
    new int[] {0,2,4,6},
    new int[] {11,22}
};

That was all about declaration and initialization but what about the usage? More often than not arrays and loops are used together. The beauty of C# is that you can use for loop, while loop and for..each loop . Let me explain this concept with the help of few example

Sample Program

using System;

/**
 * Author       : vblord
 * Date         : 10/03/2011
 * Description  : this is an example of using an array with the for loop and foreach loop
 *                 
 *                
 */
namespace HelloWorld
{
    class ArrayExample
    {
        static void Main(string[] args)
        {
            
            string[] namearray = new string[5];
              //ACCEPT INPUT FROM THE USER
            for(int i=0;i<5;i++)
            {
                Console.Write("Enter your name  : );
                namearray[i]=Console.ReadLine();
            }
             //CLEAR SCREEN AND PRINT THe ARRAY
            Console.Clear();
            foreach (string name in namearray)
            {
                Console.WriteLine(name);
            }
            Console.ReadKey(false);
        }
    }
}
Always remember arrays can be your friend and greatest asset if used judiciously but it is very easy to waste space and complicate matters by using arrays. So be careful.
This was the introduction to arrays we will go into OOPS in the next chapter.

Wednesday, 16 February 2011

Chapter 10 Object & Classes in C# Part I

C# is a powerful Object Oriented programming language. Lets first look at some basics of OOPs then we will see how C# implements it.

What are Objects ?

Objects are key to understanding object-oriented technology. Look around right now and you'll find many examples of real-world objects: your bicycle, you yourself, your car etc.
Real-world objects share two characteristics: They all have state and behavior. Dogs have state (name, color, breed, hungry) and behavior (barking, fetching, wagging tail). Cars also have state (current gear, current speed) and behavior (changing gear, applying brakes). Identifying the state and behavior for real-world objects is a great way to begin thinking in terms of object-oriented programming.
Observe the real-world objects. For each object that you see, ask yourself two questions: "What possible states can this object be in?" and "What possible behavior can this object perform?". Write down your observations.You'll notice that real-world objects vary in complexity; your table fan may have only two possible states (on and off) and two possible behaviors (turn on, turn off), but your cell phone radio might have additional states (on, off, current volume, current station) and behavior (turn on, turn off, increase volume, decrease volume, seek, scan, and tune). You may also notice that some objects, in turn, will also contain other objects. These real-world observations all translate into the world of object-oriented programming.
Software objects are conceptually similar to real-world object, they consist of state and related behavior. An object stores its state in variables and exposes its behavior through methods/functions. Methods operate on an object's internal state and serve as the primary mechanism for object-to-object communication. Hiding internal state and requiring all interaction to be performed through an object's methods is known as data encapsulation — a fundamental principle of object-oriented programming.


By attributing state (speed, current gear,fuel) and providing methods for changing that state, the object remains in control of how the outside world is allowed to use it. For example, if the car has 4 gears, a method to change gears could reject any value that is less than 1 or greater than 4.
Bundling code into individual software objects provides a number of benefits, including:

Modularity: The source code for an object can be written and maintained independently of the source code for other objects. Once created, an object can be easily passed around inside the system.
Information-hiding: By interacting only with an object's methods, the details of its internal implementation remain hidden from the outside world.
Code re-use: If an object already exists (perhaps written by another software developer), you can use that object in your program. This allows specialists to implement/test/debug complex, task-specific objects, which you can then trust to run in your own code.
Pluggability and debugging ease: If a particular object turns out to be problematic, you can simply remove it from your application and plug in a different object as its replacement. This is analogous to fixing mechanical problems in the real world. If a bolt breaks, you replace it, not the entire machine.

What is a Class ?

Many individual objects belong to the same family. There may be thousands of cars in existence, all of the same make and model. Each car was built from the same set of blueprints and therefore contains the same kind of machinery. In OOPs terminology, we can say that our vehicle is an instance of the class of objects known as CARS. A class is the blueprint from which individual objects are created.

What Is Inheritance?


Different kinds of objects often have a certain amount in common with each other. SUV's, Sports Cars and Family cars, for example, all share the characteristics of cars (speed, fuel,heat, gear). Yet each also defines additional features that make them different: SUV's are four wheel drive; sports car have more powerful engine and smaller size; family cars have more space and more safety features with lower speed.
Object-oriented programming allows classes to inherit commonly used state and behavior from other classes. In this example, Car becomes the superclass of SUV's, SportsCar, and FamilyCar.


What is Polymorphism ?

Poly means many. morph mens to change. There fore polymorphism is a concept of OOPs under which a variable, function, or an object can have more than one meaning within the program.
If we try to define it a bit more technically "polymorphism is the ability to hide alternative implementations behind a common interface. This concept leverages inheritance and encapsulation among other OO concepts.Polymorphism is the ability for objects of different types to respond to messages of the same type. The concept behind polymorphism is that a function or data type can be written in a generic way, so that it can handle any interactions to it regardless of the acting object’s type."
This can be sub-divided in two broad categories Compile-time and Run-time polymorphism. we will look into this more deeply in the next few sections given below.

C# implementation of OOPs

Objects in C#(MS guidlines)


  1. Everything you use in C# is an object, including Windows Forms and controls.
  2. Objects are instantiated; that is, they are created from templates defined by classes and structs.
  3. Objects use Properties/State to obtain and change the information they contain.
  4. Objects often have methods and events that allow them to perform actions.
  5. Visual Studio provides tools for manipulating objects: the Properties Window allows you to change the attributes of objects such as Windows Forms. The Object Browser allows you to examine the contents of an object.
  6. All C# objects inherit from the Object.

Classes in C#


Following is the syntax to create a simple class in C#, you have already seen the basic syntax earlier in our sample programs, because everything in C# is an object or sub-class.
public class Student
{
    //Fields, properties, methods and events go here...
}
Now if you want to use this class you must create an object and following are the methods to do so.
Student obj=new Student();
Note the usage of new operator, if you do not use this then the program will fail at run time.
Student obj1=new Student();
 Student obj2=obj1;
In the above example we have created an object called obj1 and then another object called obj2, which refers to obj1, thus any change in obj2 will be like changing obj1 itself. Because objects are of reference type.(Do not confuse them with pointers)

Fields

Fields are part of class which hold class data, We have referred it to as state in our earlier discussion. They give the class the ability to store values which may be used to define the state of an object. Microsoft advises us to declare all the fields as private and use methods or properties to modify them but for simplicity sake we will define them as public in our sample program.
using System;
/**
 * author         Sudeep Mukherjee
 * description    Basic program to declare fields in C# class and use 
 *                 them to take some input and then print it
 * */
namespace MySamples
{
    class Student
    {
        public string name;
        public int age;
        public float hindi, english, maths;
    }

    class RunSample
    {
        static void Main(string[] args)
        {
            Student obj1 = new Student();
            Console.Write("Enter students name ");
            obj1.name=Console.ReadLine();

            Console.WriteLine("Student name is {0}", obj1.name);

        }
    }
}


Properties

Properties are members that provide a flexible mechanism to read, write, or compute the values of private fields. Properties can be used as though they are public data members, but they are actually special methods called accessors. This enables data to be accessed easily while still providing the safety and flexibility of methods.
using System;

/**
 * author         Sudeep Mukherjee
 * description     Basic program to declare fields and properties 
 *                 in C# class and use them to take some input and 
 *                 then print it
 * */
namespace MySamples
{
    class Student
    {
        public string name;
        private int age;
        public float hindi, english, maths;
        public int studentage {
            get { return age; }
            set
            {
                if (value < 0 || value >= 100)
                {
                    age = 1;
                }
                else
                {
                    age = value;
                }
            }
        }

    }

    class RunSample
    {
        static void Main(string[] args)
        {
            Student obj1 = new Student();
            Console.Write("Enter students name ");
            obj1.name=Console.ReadLine();
            Console.Write("Enter students age ");
            obj1.studentage=int.Parse(Console.ReadLine());
            Console.WriteLine("Student name is {0} and his age is {1}", obj1.name,obj1.studentage);
            Console.ReadKey(false);

        }
    }
}


Sample Output


In the program above I have declared one private field, age and one property, studentage which checks if the age given is between the permitted range of 1 to 100, if it is not then the field is given a value of 1.
Properties enable a class to expose a public way of getting and setting values, while hiding implementation or verification code.
A get property accessor is used to return the property value, and a set accessor is used to assign a new value. These accessors can have different access levels.
The value keyword is used to define the value being assigned by the set indexer.
Properties that do not implement a set method are read only.

Methods

Methods define the actions that a class can perform. Method can take parameters that provide input data, and can return output data through parameters. Methods can also return a value directly, without using a parameter.A method is a code block containing a series of statements. In C#, every executed instruction is done so in the context of a method. Methods are declared within a class by specifying the access level, the return value, the name of the method, and any method parameters. Method parameters are surrounded by parentheses, and separated by commas. Empty parentheses indicate that the method requires no parameters.
using System;
/**
 * author         Sudeep Mukherjee
 * description     Basic program to declare fields,properties and methods
 *                 in C# class and use them to take some input and 
 *                 then print it
 * */
namespace MySamples
{
    class Student
    {
        public string name;
        private int age;
        public float hindi, english, maths;
        public int studentage {
            get { return age; }
            set
            {
                if (value < 0 || value >= 100)
                {
                    age = 1;
                }
                else
                {
                    age = value;
                }
            }
        }
        public bool IsEligible() {
            bool answer = false;
            if (studentage > 14 && studentage < 17)
                answer = true;
            else
                answer = false;
            return answer;
        }

    }

    class RunSample
    {
        static void Main(string[] args)
        {
            Student obj1 = new Student();
            Console.Write("Enter students name ");
            obj1.name=Console.ReadLine();
            Console.Write("Enter students age ");
            obj1.studentage=int.Parse(Console.ReadLine());
            if(obj1.IsEligible())
                Console.WriteLine("Student {0} is elligible because his age is {1}", obj1.name,obj1.studentage);
            else
                Console.WriteLine("Student {0} is not elligible because his age is {1}", obj1.name, obj1.studentage);
            Console.ReadKey(false);

        }
    }
}
In this example a method IsEligible is a member of Student class and returns true if the student is of correct age to sit this exam( arbitrary condition)or false if he/she is overage or under age. Last point I would like to discuss in this chapter is Call by value and call by reference To illustrate my point I will show you two methods and the difference between the two
using System;
/**
 * author         Sudeep Mukherjee
 * description     Basic program to show the difference in call by value
 *                 and call by reference
 * */
namespace MySamples
{
    class Student
    {
        public string name;
        private int age;
        public float hindi, english, maths;
        public int studentage {
            get { return age; }
            set
            {
                if (value < 0 || value >= 100)
                {
                    age = 1;
                }
                else
                {
                    age = value;
                }
            }
        }
        public bool IsEligible() {
            bool answer = false;
            if (studentage > 14 && studentage < 17)
                answer = true;
            else
                answer = false;
            return answer;
        }
        public void valueTypeCall(int val)
        {
            val = 312;
        }

        public void referenceTypeCall(Student val)
        {
            val.studentage = 29;
        }

    }

    class RunSample
    {
        static void Main(string[] args)
        {
            Student obj1 = new Student();
            int i = 40;
            obj1.valueTypeCall(i);
            Console.WriteLine("value of i is {0}",i);
            Student obj2 = new Student();
            obj2.studentage = 11;
            obj1.referenceTypeCall(obj2);
            Console.WriteLine("value of studentage is {0}", obj2.studentage);

            Console.ReadKey(false);

        }
    }
}

Sample Output


In the first instance I send a variable 'i' to a function, where the value of this variable is changed. We expect that the change should be visible in the main function but that is not the case why? because value of the variable is copied into a new local variable , thus any changes made inside the function will not be affect the copy.
On the other hand the next function passes the object by reference thus any changes made inside the function will reflect on the original object and in the main function too.

Next chapter will deal with more OOPS based parts of C#.

Tuesday, 15 February 2011

Chapter 10 Object & Classes in C# Part 2

This chapter continues from the earlier chapter thus if you have not read it. please do so before proceeding further.

Constructor

These are special methods which get called at the time of object creation automatically. They cannot be called later by the user.. Constructors allow the programmer to set default values, limit instantiation, and write code that is flexible and easy to read.
using System;
/**
 * author         Sudeep Mukherjee
 * description     Basic program to show the usage of a constructor
 *                 
 * */
namespace MySamples
{
    class Student
    {
        public string name;
        private int age;
        public float hindi, english, maths;
        public int studentage {
            get { return age; }
            set
            {
                                
                if (value < 0 || value >= 100)
                {
                    age = 1;
                }
                else
                {
                    age = value;
                }
            }
        }

        public Student()
        {
            studentage = 30;
        }
        public Student(int stAge)
        {
            studentage = stAge;
        }
        
        
    }

    class RunSample
    {
        static void Main(string[] args)
        {
            Student obj1 = new Student();
            Console.WriteLine("Student age is {0}", obj1.studentage);
            Console.ReadKey(false);

        }
    }
}

the above program declares a constructor which initializes the studentage property with the value of 30 , thus if we print the property then instead of 0 which is the default value, it will show 30

Sample Output


Constructor can have arguments too as shown in the above program, the second constructor accepts an argument to initialize the studentage property with the given value.

Destructor

Destructor run just before the instance of the class is destroyed. They are used for cleanup routines and are called automatically. Some of the features of a destructor are
  1. A class can only have one destructor.
  2. Destructors cannot be inherited or overloaded.
  3. Destructors cannot be called. They are invoked automatically.
  4. A destructor does not take modifiers or have parameters.
class Sample
 {
    ~Sample()
    {
        //cleanup code
    }
 }

this keyword

The this keyword refers to the current instance of the class. It is mainly used to distinguish variables hidden by similar names or passing the current instance as an argument to a method.Following program shows the three uses of this keyword. First it uses it as object to refer to the class member studentage. Next it uses to call the constructor with arguments to initialize the studentage property. Lastly it passes the current instance of the class to a function called calcGrade.
using System;
/**
 * author         Sudeep Mukherjee
 * description     Basic program to show the usage of a this keyword
 *                 
 * */
namespace MySamples
{
    class Student
    {
        public string name;
        private int age;
        public int hindi, english, maths;
        public int studentage {
            get { return age; }
            set
            {
                                
                if (value < 0 || value >= 100)
                {
                    age = 1;
                }
                else
                {
                    age = value;
                }
            }
        }

        public Student()
            :this(30)
        {
            
        }
        public Student(int studentage)
        {
            this.studentage = studentage;
        }
        public char getGrade()
        {
            char answer = ExternalClass.calcGrade(this);
            return answer;
        }
        
        
    }

    class ExternalClass
    { 
        public static char calcGrade(Student obj)
        {
            char answer='a';
            int tot1=obj.hindi+obj.english+obj.maths;
            if (tot1 >= 90)
                answer = 'a';
            else if (tot1 >= 65 && tot1 < 90)
                answer = 'b';
            else if (tot1 < 65)
                answer = 'c';
            return answer;
        
        }
    
    }
    
    class RunSample
    {
        static void Main(string[] args)
        {
            Student obj1 = new Student();
            Console.WriteLine("Student age is {0}", obj1.studentage);
            Console.ReadKey(false);

        }
    }
}

Events

We will discuss this a bit in detail after few more section, please treat this material as introduction and nothing more. Events are a way for a class or object to notify other classes or objects when something has happened. The class that sends (or raises) the event is called the publisher and the classes that receive (or handle) the event are called subscribers. Events have the following properties:
  1. The publisher determines when an event is raised; the subscribers determine what action is taken in response to the event.
  2. An event can have multiple subscribers. A subscriber can handle multiple events from multiple publishers.
  3. Events that have no subscribers are never called.
  4. Events are commonly used to signal user actions such as button clicks or menu selections in graphical user interfaces.
  5. When an event has multiple subscribers, the event handlers are invoked synchronously when an event is raised. To invoke events asynchronously.
  6. Events can be used to synchronize threads.
  7. In the .NET Framework class library, events are based on the EventHandler delegate and the EventArgs base class.

Monday, 14 February 2011

Chapter 11 Inheritance in C#

As we had discussed earlier inheritance is one of the cornerstones of OOPS. Thus C# follows it too. You may have noticed that all framework classes inherit form one single class called Object. In this chapter we will look into the actual implementation aspect of inheritance.
STEP 1
To begin with lets design a simple Animal class.It will have three methods
  1. Constructor
  2. Talk
  3. Greet
Each one will print out a message with the Animal word included so that we know which class is responding to us
STEP 2
We will build another class called Dog and Donkey which will derive from Animal class . This class will have identical methods except the message printed will have the word Dog or Donkey included. Again to clarify which class is being executed.
using System;

namespace InheritanceSample
{
    class Animal
    {
        public Animal()
        {
            Console.WriteLine("I am an Animal Constructor");

        }

        public void Talk()
        {
            Console.WriteLine("Hi I am an Animal Talking");
        }

        public void Greet()
        {
            Console.WriteLine("Welcome from all Animals my friend");
        }
    }


    class Dog : Animal {
        public Dog()
        {
            Console.WriteLine("I am a DOG Constructor");
        }
        public void Talk()
        {
            Console.WriteLine("Hi I am a DOG Barking/Talking");
        }

        public void Greet()
        {
            Console.WriteLine("Welcome from all DOGS my friend");
        }
        
    }
    class Donkey : Animal {
        public Donkey()
        {
            Console.WriteLine("I am a DONKEY Constructor");
        }
        public void Talk()
        {
            Console.WriteLine("Hi I am a DONKEY Braying/Talking");
        }

        public void Greet()
        {
            Console.WriteLine("Welcome from all DONKEYS my friend");
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            Animal obj = new Animal();
            obj.Talk();
            obj.Greet();
            Console.ReadKey();
        }
    }
}


STEP 3
In the Main function we will declare an object of type Animal and it will reference an object of type Animal too. Then we call all the functions and output is shown below.
I hope this part of the output is clear.
STEP 4
Now we will change the code inside the Main function a bit and use the Dog class instead
The modified code only is given below
static void Main(string[] args)
 {
    Dog obj = new Dog();
    obj.Talk();
    obj.Greet();
    Console.ReadKey();
 }
The output is given below. Please note the first major difference. The Animal constructor gets called too and before the Dog constructor, this is a part of inheritance rule that the parents constructor will be called before the child's constructor. rest of all the output is I hope as you expected. You must notice that in this case all the child methods were called because the object type is Dog thus it calls all the child class methods.


STEP 5
Let us imagine we have many sub classes of Animal class and we decide to create an array of objects but what kind of array can we create, either it has to be Dog or Donkey or both. Instead of having separate arrays for each class we can declare an array of the base class, which can easily store objects of child class. Lets look at a simplified example, again only the code in then Main function is changed. We declare an object of type Animal and in it we reference an object of Dog type.
class Program
{
        static void Main(string[] args)
        {
            Animal obj = new Dog();
            obj.Talk();
            obj.Greet();
            Console.ReadKey();
        }
}
Following is the output


Did you see the difference even though Dog class constructor is called yet the methods of base class Animal are the one to execute. Why?..... Two reasons firstly the object type is Animal thus it will only call those methods which are defined inside the Animal class.This is a problem and beats the whole purpose of this exercise. Only way to overcome this is by overriding the base class method with that of the child class method. We will now tweak our class to do so. Only one of the function "Talk" will be changed, rest everything remains the same.
using System;

namespace InheritanceSample
{
    class Animal
    {
        public Animal()
        {
            Console.WriteLine("I am an Animal Constructor");

        }

        public virtual void Talk()
        {
            Console.WriteLine("Hi I am an Animal Talking");
        }

        public void Greet()
        {
            Console.WriteLine("Welcome from all Animals my friend");
        }
    }


    class Dog : Animal {
        public Dog()
        {
            Console.WriteLine("I am a DOG Constructor");
        }
        public override void Talk()
        {
            Console.WriteLine("Hi I am a DOG Barking/Talking");
        }

       
    }
    class Donkey : Animal
    {
        public Donkey()
        {
            Console.WriteLine("I am a DONKEY Constructor");
        }
        public override void Talk()
        {
            Console.WriteLine("Hi I am a DONKEY Braying/Talking");
        }

        public void Greet()
        {
            Console.WriteLine("Welcome from all DONKEYS my friend");
        }
    }
    class Program
    {
        static void Main(string[] args)
        {
            Animal obj = new Dog();
            obj.Talk();
            obj.Greet();
            Console.ReadKey();
        }
    }
}

Now look at the sample output and see how the base class talk function is called.

Notice the change in the Animal class function "Talk" it is now declared with the keyword virtual and the child class function "Talk" is declared with the keyword override. And I have removed the "Greet" method from Dog class.Yet the program will compile because the derived/child class inherits all the members of base class.
The override modifier is required to extend or modify the abstract or virtual implementation of an inherited method, property, indexer, or event.An override method provides a new implementation of a member inherited from a base class. The method overridden by an override declaration is known as the overridden base method. The overridden base method must have the same signature as the override method.
You cannot override a non-virtual or static method. The overridden base method must be virtual, abstract, or override.An override declaration cannot change the accessibility of the virtual method. Both the override method and the virtual method must have the same access level modifier.You cannot use the modifiers new, static, virtual, or abstract to modify an override method.An overriding property declaration must specify the exact same access modifier, type, and name as the inherited property, and the overridden property must be virtual, abstract, or override.
In a child constructor we can call any base class constructor using the syntax
public constructor()
:base(arguments)
{}
This will call the base class constructor, sometimes we may want to call the base class constructor which is not the default, in that case this syntax is helpful.
We can use the keyword base to call any parent class method with the following syntax base.methodname()

Polymorphism

Recall that polymorphism is the phenomenon where the same message sent to two different objects produces two different set of actions. Polymorphism is of two types static and dynamic or runtime polymorphism. To put simply if we declare two methods with the same name but different signature it is called method overloading and is a type of static polymorphism. While when we override any method it is called dynamic polymorphism or late binding.If we look at it a bit technically then we may define them as
Static polymorphism refers to an entity existing in different physical forms simultaneously. Static polymorphism involves binding of functions based on the number, type, and sequence of arguments. The various types of parameters are specified in the function declaration, and therefore the function can be bound to calls at compile time. This form of association is called early binding. The term early binding stems from the fact that when the program is executed, the calls are already bound to the appropriate functions.
With runtime polymorphism(Dynamic polymorphism), the selection of a method for execution is based on the actual type of the object whose reference is stored, and not on the type of the reference variable on which the method is invoked.
All the programs given above are example of dynamic polymorphism.
Static Polymorphism example can be any method/function which is defined multiple time in the same class with different signature.

Sunday, 13 February 2011

Chapter 12 Strings in C#

A string is an object of type String whose value is text. Internally, the text is stored as a sequential read-only collection of Char objects. There is no null-terminating character at the end of a C# string; therefore a C# string can contain any number of embedded null characters ('\0').
As mentioned earlier string is just an alias for the class System.String. You can use either of the two to use strings and each provide you with multiple functions to make your life easier. We will look at each of them one by one.

Immutability

String are immutable. It means they cannot be changed after they have been created. All of the String methods and C# operators that appear to modify a string actually return the results in a new string object. In the following example, when the contents of x1 and x2 are concatenated to form a single string, the two original strings are unmodified. The += operator creates a new string that contains the combined contents. That new object is assigned to the variable x1, and the original object that was assigned to s1 is released for garbage collection because no other variable holds a reference to it.
string x1 = "This is a string ";
string x2 = "It is a wonderful object";

x1 += x2;

System.Console.WriteLine(x1);
// Output: This is a string It is a wonderful object


Now this immutable property has a hidden problem too. Because each time you change or concatenate a string object, C# creates a new reference but if there is another reference pointing to the older object, then it still points to the older object.
string x1 = "This is a string ";
string x2 = x1;
x1="This will not be printed";
System.Console.WriteLine(x2);

// Output: This is a string

Interning


.NET has the concept of an "intern pool". It's basically just a set of strings, but it makes sure that every time you reference the same string literal, you get a reference to the same string. This is probably language-dependent, but it's certainly true in C# and VB.NET, and I'd be very surprised to see a language it didn't hold for, as IL makes it very easy to do (probably easier than failing to intern literals). As well as literals being automatically interned, you can intern strings manually with the Intern method, and check whether or not there is already an interned string with the same character sequence in the pool using the IsInterned method. This somewhat unintuitively returns a string rather than a boolean - if an equal string is in the pool, a reference to that string is returned. Otherwise, null is returned. Likewise, the Intern method returns a reference to an interned string - either the string you passed in if was already in the pool, or a newly created interned string, or an equal string which was already in the pool.

Escape Sequence

If you want to embed some characters inside a string we use escape sequence quite similar to C++

Escape sequence

Character name

Unicode encoding

\'

Single quote

0x0027

\"

Double quote

0x0022

\\

Backslash

0x005C

\0

Null

0x0000

\a

Alert

0x0007

\b

Backspace

0x0008

\f

Form feed

0x000C

\n

New line

0x000A

\r

Carriage return

0x000D

\t

Horizontal tab

0x0009

\U

Unicode escape sequence for surrogate pairs.

\Unnnnnnnn

\u

Unicode escape sequence

\u0041 = "A"

\v

Vertical tab

0x000B

\x

Unicode escape sequence similar to "\u" except with variable length.

\x0041 = "A"


Substring

Substring is a function that creates a new string from within a string by taking a part of it. It has two overloaded forms
Substring(Int32) Retrieves a substring that starts at a specified character position till the end.
Substring(Int32, Int32) Retrieves a substring that starts at a specified character position and has a specified length.

Lets clarify it a bit further with few examples
string x1="Great India";
Console.WriteLine(x1.Substring(6,5));
//the output will be :  India
Console.WriteLine(x1.Substring(6,5));
//the output will be :  Great India

IndexOf


Reports the index of the first occurrence of one or more characters, or the first occurrence of a string, within this string.
Name Description

IndexOf(Char)

Reports the index of the first occurrence of the specified Unicode character in this string.

IndexOf(String)

Reports the index of the first occurrence of the specified string in this instance.

IndexOf(Char, Int32)

Reports the index of the first occurrence of the specified Unicode character in this string. The search starts at a specified character position.

IndexOf(String, Int32)

Reports the index of the first occurrence of the specified string in this instance. The search starts at a specified character position.

IndexOf(String, StringComparison)

Reports the index of the first occurrence of the specified string in the current String object. A parameter specifies the type of search to use for the specified string.

IndexOf(Char, Int32, Int32)

Reports the index of the first occurrence of the specified character in this instance. The search starts at a specified character position and examines a specified number of character positions.

IndexOf(String, Int32, Int32)

Reports the index of the first occurrence of the specified string in this instance. The search starts at a specified character position and examines a specified number of character positions.

IndexOf(String, Int32, StringComparison)

Reports the index of the first occurrence of the specified string in the current String object. Parameters specify the starting search position in the current string and the type of search to use for the specified string.

IndexOf(String, Int32, Int32, StringComparison)

Reports the index of the first occurrence of the specified string in the current String object. Parameters specify the starting search position in the current string, the number of characters in the current string to search, and the type of search to use for the specified string.
Courtesy Microsoft

LastIndexOf

Reports the index position of the last occurrence of a specified Unicode character or String within this instance.

Name

Description

String.LastIndexOf (Char)

Reports the index position of the last occurrence of a specified Unicode character within this instance.



String.LastIndexOf (String)

Reports the index position of the last occurrence of a specified String within this instance.



String.LastIndexOf (Char, Int32)

Reports the index position of the last occurrence of a specified Unicode character within this instance. The search starts at a specified character position.



String.LastIndexOf (String, Int32)

Reports the index position of the last occurrence of a specified String within this instance. The search starts at a specified character position.



String.LastIndexOf (String, StringComparison)

Reports the index of the last occurrence of a specified string within the current String object. A parameter specifies the type of search to use for the specified string.



String.LastIndexOf (Char, Int32, Int32)

Reports the index position of the last occurrence of the specified Unicode character in a substring within this instance. The search starts at a specified character position and examines a specified number of character positions.



String.LastIndexOf (String, Int32, Int32)

Reports the index position of the last occurrence of a specified String within this instance. The search starts at a specified character position and examines a specified number of character positions.



String.LastIndexOf (String, Int32, StringComparison)

Reports the index of the last occurrence of a specified string within the current String object. Parameters specify the starting search position in the current string, and type of search to use for the specified string.



String.LastIndexOf (String, Int32, Int32, StringComparison)

Reports the index position of the last occurrence of a specified String object within this instance. Parameters specify the starting search position in the current string, the number of characters in the current string to search, and the type of search to use for the specified string.



Replace

Returns a new string in which all occurrences of a specified character or String in the current string are replaced with another specified character or String.


Name Description

Replace(Char, Char)

Returns a new string in which all occurrences of a specified Unicode character in this instance are replaced with another specified Unicode character.

Replace(String, String)

Returns a new string in which all occurrences of a specified string in the current instance are replaced with another specified string.
x1 = "Great India";
   x1=x1.Replace("i","2");
   Console.WriteLine(x1);

// Output will be : Great Ind2a

Compare Strings

By comparing two strings we find out if one string is greater,less or equal to the other string.
Use basic ordinal comparisons when you have to compare or sort the values of two strings without regard to linguistic conventions. A basic ordinal comparison (System.StringComparison.Ordinal) is case-sensitive, which means that the two strings must match character for character: "and" does not equal "And" or "AND". A frequently-used variation is System.StringComparison.OrdinalIgnoreCase, which will match "and", "And", and "AND". StringComparison.OrdinalIgnoreCase is often used to compare file names, path names, network paths, and any other string whose value does not change based on the locale of the user's computer.
string root = "C:\users";
string root2 = "C:\Users";

// Use the overload of the Equals method that specifies a StringComparison.
// Ordinal is the fastest way to compare two strings.
bool result = root.Equals(root2, StringComparison.Ordinal);

Console.WriteLine("Ordinal comparison: {0} and {1} are {2}", root, root2,
                    result ? "equal." : "not equal.");

// To ignore case means "user" equals "User". This is the same as using
// String.ToUpperInvariant on each string and then performing an ordinal comparison.
result = root.Equals(root2, StringComparison.OrdinalIgnoreCase);
Console.WriteLine("Ordinal ignore case: {0} and {1} are {2}", root, root2,
                     result ? "equal." : "not equal.");

// A static method is also available.
bool areEqual = String.Equals(root, root2, StringComparison.Ordinal);

StringBuilder Class

Represents a mutable string of characters. This class cannot be inherited.
String operations in .NET are highly optimized and in most cases do not significantly impact performance. However, in some scenarios such as tight loops that are executing many hundreds or thousands of times, string operations can affect performance. The StringBuilder class creates a string buffer that offers better performance if your program performs many string manipulations. The StringBuilder string also enables you to reassign individual characters, something the built-in string data type does not support.
This class represents a string-like object whose value is a mutable sequence of characters. The value is said to be mutable because it can be modified once it has been created by appending, removing, replacing, or inserting characters.
System.Text.StringBuilder sb = new System.Text.StringBuilder("My Name Is Anthony");
sb[0] = 'Y';
System.Console.WriteLine(sb.ToString());
System.Console.ReadLine();

//Output is Yy Name Is Anthony


StartsWith


Determines whether the beginning of an instance of String matches a specified string.


Name

Description
String.StartsWith (String)

Determines whether the beginning of this instance matches the specified string.



String.StartsWith (String, StringComparison)

Determines whether the beginning of this string matches the specified string when compared using the specified comparison option.



String.StartsWith (String, Boolean, CultureInfo)

Determines whether the beginning of this string matches the specified string when compared using the specified culture.

EndsWith

Determines whether the end of this string instance matches a specified string.


Name

Description

String.EndsWith (String)

Determines whether the end of this instance matches the specified string.



String.EndsWith (String, StringComparison)

Determines whether the end of this string matches the specified string when compared using the specified comparison option.



String.EndsWith (String, Boolean, CultureInfo)

Determines whether the end of this string matches the specified string when compared using the specified culture.


Trimming and Removing Characters

You can use one of the trim methods in the System.String class to remove any number of spaces or other characters from a specified position in the string.

Method name

Use

String.Trim

Removes white spaces from the beginning and end of a string.

end(v=String.TrimEnd

Removes characters specified in an array of characters from the end of a string.

String.TrimStart

Removes characters specified in an array of characters from the beginning of a string.

String.Remove

Removes a specified number of characters from a specified index position in a string.

Wednesday, 2 February 2011

Chapter 13 ADO.NET

Download Sample App from here
ADO.NET provides consistent access to data sources such as Microsoft SQL Server and XML, as well as to data sources exposed through OLE DB and ODBC. Data-sharing consumer applications can use ADO.NET to connect to these data sources and retrieve, manipulate, and update the data that they contain.


ADO.NET separates data access from data manipulation into discrete components that can be used separately or in tandem. ADO.NET includes .NET Framework data providers for connecting to a database, executing commands, and retrieving results. Those results are either processed directly, placed in an ADO.NET DataSet object in order to be exposed to the user in an ad hoc manner, combined with data from multiple sources, or remoted between tiers. The ADO.NET DataSet object can also be used independently of a .NET Framework data provider to manage data local to the application or sourced from XML.


The ADO.NET classes are found in System.Data.dll, and are integrated with the XML classes found in System.Xml.dll. When compiling code that uses the System.Data namespace, reference both System.Data.dll and System.Xml.dll. For an example of an ADO.NET application that connects to a database, retrieves data from it, and then displays that data in a command prompt.


ADO.NET provides functionality to developers writing managed code similar to the functionality provided to native component object model (COM) developers by ActiveX Data Objects (ADO).


ADO.NET Architecture


There are two components of ADO.NET that you can use to access and manipulate data:


.NET Framework data providers
The DataSet


.NET Framework Data Providers


The .NET Framework Data Providers are components that have been explicitly designed for data manipulation and fast, forward-only, read-only access to data. The Connection object provides connectivity to a data source. The Command object enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information. The DataReader provides a high-performance stream of data from the data source. Finally, the DataAdapter provides the bridge between the DataSet object and the data source. The DataAdapter uses Command objects to execute SQL commands at the data source to both load the DataSet with data, and reconcile changes made to the data in the DataSet back to the data source.


The DataSet


The ADO.NET DataSet is explicitly designed for data access independent of any data source. As a result, it can be used with multiple and differing data sources, used with XML data, or used to manage data local to the application. The DataSet contains a collection of one or more DataTable objects made up of rows and columns of data, as well as primary key, foreign key, constraint, and relation information about the data in the DataTable objects.


The following diagram illustrates the relationship between a .NET Framework data provider and a DataSet.


Choosing a DataReader or a DataSet
When deciding whether your application should use a DataReader, you should consider the type of functionality that your application requires. Use a DataSet to do the following:




  • Cache data locally in your application so that you can manipulate it. If you only need to read the results of a query, the DataReader is the better choice.

  • Remote data between tiers or from an XML Web service.

  • Interact with data dynamically such as binding to a Windows Forms control or combining and relating data from multiple sources.

  • Perform extensive processing on data without requiring an open connection to the data source, which frees the connection to be used by other clients.




If you do not require the functionality provided by the DataSet, you can improve the performance of your application by using the DataReader to return your data in a forward-only, read-only fashion. Although the DataAdapter uses the DataReader to fill the contents of a DataSet, by using the DataReader you can boost performance because you will save memory that would be consumed by the DataSet, as well as avoid the processing required to create and fill the contents of the DataSet.


XML and ADO.NET


ADO.NET leverages the power of XML to provide disconnected access to data. ADO.NET was designed hand-in-hand with the XML classes in the .NET Framework; both are components of a single architecture.


ADO.NET and the XML classes in the .NET Framework converge in the DataSet object. The DataSet can be populated with data from an XML source, whether it is a file or an XML stream. The DataSet can be written as World-Wide Web Consortium (W3C) compliant XML, including its schema as XML Schema definition language (XSD) schema, regardless of the source of the data in the DataSet. Because of the native serialization format of the DataSet is XML, it is an excellent medium for moving data between tiers, making the DataSet an optimal choice for remoting data and schema context to and from an XML Web service.


Core Objects of .NET Framework Data Providers



ObjectDescription
ConnectionEstablishes a connection to a specific data source. The base class for all Connection objects is the DbConnection class.
CommandExecutes a command against a data source. Exposes Parameters and can execute within the scope of a Transaction from a Connection. The base class for all Commandobjects is the DbCommand class.
DataReaderReads a forward-only, read-only stream of data from a data source. The base class for all DataReader objects is the DbDataReader class.
DataAdapterPopulates a DataSet and resolves updates with the data source. The base class for all DataAdapter objects is the DbDataAdapter class.




In addition to the core classes listed in the table above, a .NET Framework data provider also contains the classes listed in the following table.
ObjectDescription
TransactionEnables you to enlist commands in transactions at the data source. The base class for all Transaction objects is the DbTransaction class.
CommandBuilderA helper object that will automatically generate command properties of a DataAdapter or will derive parameter information from a stored procedure and populate the Parameters collection of a Command object. The base class for all CommandBuilder objects is the DbCommandBuilder class.
ConnectionStringBuilderA helper object that provides a simple way to create and manage the contents of connection strings used by the Connection objects. The base class for allConnectionStringBuilder objects is the DbConnectionStringBuilder class.
ParameterDefines input, output, and return value parameters for commands and stored procedures. The base class for all Parameter objects is the DbParameter class.
ExceptionReturned when an error is encountered at the data source. For an error encountered at the client, .NET Framework data providers throw a .NET Framework exception. The base class for all Exception objects is the DbException class.
ErrorExposes the information from a warning or error returned by a data source.
ClientPermissionProvided for .NET Framework data provider code access security attributes. The base class for all ClientPermission objects is the DBDataPermission class.

ADO.NET DataSet


The DataSet object is central to supporting disconnected, distributed data scenarios with ADO.NET. The DataSet is a memory-resident representation of data that provides a consistent relational programming model regardless of the data source. It can be used with multiple and differing data sources, with XML data, or to manage data local to the application. The DataSet represents a complete set of data, including related tables, constraints, and relationships among the tables. The following illustration shows the DataSet object model.






The methods and objects in a DataSet are consistent with those in the relational database model.
The DataSet can also persist and reload its contents as XML, and its schema as XML Schema definition language (XSD) schema. 


The DataTableCollection


An ADO.NET DataSet contains a collection of zero or more tables represented by DataTable objects. The DataTableCollection contains all the DataTable objects in a DataSet.
A DataTable is defined in the System.Data namespace and represents a single table of memory-resident data. It contains a collection of columns represented by a DataColumnCollection, and constraints represented by a ConstraintCollection, which together define the schema of the table. A DataTable also contains a collection of rows represented by the DataRowCollection, which contains the data in the table. Along with its current state, a DataRow retains both its current and original versions to identify changes to the values stored in the row.

The DataRelationCollection


A DataSet contains relationships in its DataRelationCollection object. A relationship, represented by the DataRelation object, associates rows in one DataTable with rows in anotherDataTable. A relationship is analogous to a join path that might exist between primary and foreign key columns in a relational database. A DataRelation identifies matching columns in two tables of a DataSet.
Relationships enable navigation from one table to another within a DataSet. The essential elements of a DataRelation are the name of the relationship, the name of the tables being related, and the related columns in each table. Relationships can be built with more than one column per table by specifying an array of DataColumn objects as the key columns. When you add a relationship to the DataRelationCollection, you can optionally add a UniqueKeyConstraint and a ForeignKeyConstraint to enforce integrity constraints when changes are made to related column values.

ExtendedProperties


The DataSet, DataTable, and DataColumn all have an ExtendedProperties property. ExtendedProperties is a PropertyCollection where you can place custom information, such as the SELECT statement that was used to generate the result set, or the time when the data was generated. The ExtendedProperties collection is persisted with the schema information for theDataSet.

Connecting to Data Sources


To connect to Microsoft SQL Server 7.0 or later, use the SqlConnection object of the .NET Framework Data Provider for SQL Server. To connect to an OLE DB data source, or to Microsoft SQL Server 6.x or earlier, use the OleDbConnection object of the .NET Framework Data Provider for OLE DB. To connect to an ODBC data source, use the OdbcConnection object of the .NET Framework Data Provider for ODBC. To connect to an Oracle data source, use the OracleConnection object of the .NET Framework Data Provider for Oracle. For securely storing and retrieving connection strings, see Securing Connection Strings.


Closing Connections


You can also use the Close or Dispose methods of the connection object for the provider that you are using. Connections that are not explicitly closed might not be added or returned to the pool. For example, a connection that has gone out of scope but that has not been explicitly closed will only be returned to the connection pool if the maximum pool size has been reached and the connection is still valid.
Using connection As New SqlConnection(connectionString)
connection.Open()
' Do work here.
End Using


Connection Strings in General


To begin with, connection strings are simply string variables which contain database connection information. There's nothing magical about them so you can use all the string functions you're used to using to manipulate them if you need to. The only thing that makes them a connection string is that they are then passed to ADO which will interpret them and act accordingly. Since they're going to be passed to ADO, they need to be in a format ADO understands. Your standard connection string contains a number of arguments set equal to their associated values and seperated by semicolons. It's basic form is something like this:


argument1=value1; argument2=value2; argument3=value3;
Of those arguments, there are only four that ADO understands. The rest are simply passed through to the provider. The four ADO recognizes are Provider, File Name, Remote Provider and Remote Server. The last two are only used with RDS and I won't be covering them.


Microsoft.Jet.OLEDB.4.0


This is the OLE DB provider for Access. To connect to an Access database using this provider, the only other attribute required to make a connection is the Data Source attribute which is used to specify the full path and file name of the Access .mdb file. A minimal OLE DB Provider for Jet connection string would therefore look something like this:
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\path\filename.mdb;
The only other commonly used parameters are User ID and Password which are used to specify user authentication values. A user name of "admin" and a blank password are the defaults we tend to use resulting in the following default connection string:
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\path\filename.mdb; User ID=admin; Password=;
SQLOLEDB


The OLE DB provider for MS SQL Server is a little bit more complex. The Data Source parameter is once again used, but this time it specifies the name or address of the SQL Server. Because SQL Server can run multiple databases and we haven't yet picked one we need another parameter to indicate the databse we want to use. This parameter is called Initial Catalog. That combined with the same parameters as above for User ID and Password results in this basic form:


Provider=SQLOLEDB; Data Source=server_name_or_address; Initial Catalog=database_name; User ID=username; Password=password;
I'm not sure if it's more because of the networking required or the additional capabilities of the database, but there are more parameters one can specify when utilizing SQLOLEDB then there are with MS Jet. You can get a list of all of them from the SQL Server ADO Programmer's Reference in the SQL Server Books Online. In particular, you're looking for the Provider-Specific Connection Parameters of the connection object.


Honestly though... the only other parameter I ever use is Network Library and this one I use pretty religiously. It's relatively important and has been the key to solving many a connection problem. You see, Named Pipes is often set up as the default network library to use when connecting the client (IIS) to the server (SQL Server). While this can be changed via the SQL Server Client Network Utility, it rarely is. This configuration is fine if the client and server are on the same network, but in today's TCP/IP based world, Named Pipes is often not the network library of choice. To force the client to use TCP/IP to connect (ignoring the defaults altogether) specify a value of dbmssocn for the Network Library parameter.


ODBC Connection Strings


While I indicated earlier that we prefer OLE DB, ODBC (MSDASQL) has been around for a long time and back when ASP was first released it was really your only option for ASP database connections. Since it is still so widely in use and is actually the default provider, I felt I'd be somewhat remiss if I didn't cover it as well.


There are two styles of ODBC connection string: those that utilize a DSN (Data Source Name) and those that do not (often called DSN-less connections). The parameters they use are identical, it's just a matter of where they are specified and stored.


DSN Connections


DSN connections store their connection information in the Windows Registry. Naturally it's not a good idea to have people poking around in the registry so in the Windows NT4 Control Panel you'll find an applet called "ODBC Data Sources" which provides a wizard like interface to collecting connection settings. In Windows 2000, it's been moved to the "Administrative Tools" Folder in the Control Panel and renamed "Data Sources (ODBC)", but it functions the same.


In a nutshell, it's only real functionality is to help you create a connection string, save these connection settings to the registry, and associate them with a unique DSN. If you're using DSNs for ASP, make sure they're System DSNs so that they're available for all users. Since these settings are all previously saved, the connection string becomes simplicity itself:


Provider=MSDASQL; DSN=data_source_name;
or since MSDASQL is the default, simply:


DSN=data_source_name;
If you need to, you can also specify username and password information, but the parameter names are slightly different then with OLE DB resulting in a fully qualified connection to a DSN that looks something like this:
Provider=MSDASQL; DSN=data_source_name; UID=username; PWD=password;
Any other parameters you need, including what driver to use and even what type of database to connect to, is set via the wizard-like setup in the DSN configuration utility in the control panel discussed earlier.


DSN-less Connections


A DSN-less connection is operationally identical to a DSN except that the server doesn't have to access the registry to access the parameters since they're all specified in the connection string itself.


The only parameter required by all ODBC connection strings is Driver which specifies which driver to use. Unfortunately this isn't enough to get connected to anything and additional information is required by each driver. Most have a similar syntax and I'll provide samples of the couple most popular ones below:


Microsoft Access


Provider=MSDASQL; Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\path\filename.mdb;

Microsoft Excel


Provider=MSDASQL; Driver={Microsoft Excel Driver (*.xls)}; DBQ=C:\path\filename.xls;


Microsoft Text
Provider=MSDASQL; Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=C:\path\;

Notice there is no file name. The directory acts like a database with files working in the role of tables. Hence, you don't specify the file name until opening a recordset. It's a little weird... I recommend you experiment with it some first if you find a need to use this driver.


Microsoft SQL Server


The SQL Server driver again requires some additional information to get connected:
Provider=MSDASQL; Driver={SQL Server}; Server=server_name_or_address; Database=database_name; UID=username; PWD=password;


A few final notes about all the ODBC connections above. There is an interesting bug that I seem to remember causing problems for a while. If the above strings aren't working, try removing the space following the semi-colon after the Provider parameter. There used to be a bug in the OLE DB Provider for ODBC that would cause this to fail:
Provider=MSDASQL; Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\path\filename.mdb;
but this to work:
Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\path\filename.mdb;
And remember that MSDASQL is the default value for Provider so if you want to, you can simply leave the parameter off altogether. In fact, you'll notice that almost all the sample code you run across does leave it off. I've included it in the samples above for illustration and because the Provider is a relatively central point to get in order to really understand how all these parameters fit together to allow these connection strings to actually get you connected.


Working with Commands


After establishing a connection to a data source, you can execute commands and return results from the data source using a DbCommand object. You can create a command using one of the command constructors for the .NET Framework data provider you are working with. Constructors can take optional arguments, such as an SQL statement to execute at the data source, a DbConnection object, or a DbTransaction object. You can also configure those objects as properties of the command. You can also create a command for a particular connection using the CreateCommand method of a DbConnection object. The SQL statement being executed by the command can be configured using the CommandText property.


Each .NET Framework data provider included with the .NET Framework has a Command object. The .NET Framework Data Provider for OLE DB includes an OleDbCommand object, the .NET Framework Data Provider for SQL Server includes a SqlCommand object, the .NET Framework Data Provider for ODBC includes an OdbcCommand object, and the .NET Framework Data Provider for Oracle includes an OracleCommand object.



Each .NET Framework data provider included with the .NET Framework has its own command object that inherits from DbCommand. The .NET Framework Data Provider for OLE DB includes anOleDbCommand object, the .NET Framework Data Provider for SQL Server includes a SqlCommand object, the .NET Framework Data Provider for ODBC includes an OdbcCommand object, and the .NET Framework Data Provider for Oracle includes an OracleCommand object. Each of these objects exposes methods for executing commands based on the type of command and desired return value, as described in the following table.
CommandReturn Value
ExecuteReaderReturns a DataReader object.
ExecuteScalarReturns a single scalar value.
ExecuteNonQueryExecutes a command that does not return any rows.
ExecuteXMLReaderReturns an XmlReader. Available for a SqlCommand object only.
Each strongly typed command object also supports a CommandType enumeration that specifies how a command string is interpreted, as described in the following table.
CommandTypeDescription
TextAn SQL command defining the statements to be executed at the data source.
StoredProcedureThe name of the stored procedure. You can use the Parameters property of a command to access input and output parameters and return values, regardless of whichExecute method is called. When using ExecuteReader, return values and output parameters will not be accessible until the DataReader is closed.
TableDirectThe name of a table.


The following code example demonstrates how to create a SqlCommand object to execute a stored procedure by setting its properties. A SqlParameter object is used to specify the input parameter to the stored procedure. The command is executed using the ExecuteReader method, and the output from the SqlDataReader is displayed in the console window.


Shared Sub GetSalesByCategory(ByVal connectionString As String, _
    ByVal categoryName As String)

    Using connection As New SqlConnection(connectionString)

        ' Create the command and set its properties.
        Dim command As SqlCommand = New SqlCommand()
        command.Connection = connection
        command.CommandText = "SalesByCategory"
        command.CommandType = CommandType.StoredProcedure

        ' Add the input parameter and set its properties.
        Dim parameter As New SqlParameter()
        parameter.ParameterName = "@CategoryName"
        parameter.SqlDbType = SqlDbType.NVarChar
        parameter.Direction = ParameterDirection.Input
        parameter.Value = categoryName

        ' Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter)

        ' Open the connection and execute the reader.
        connection.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()

        If reader.HasRows Then
            Do While reader.Read()
                Console.WriteLine("{0}: {1:C}", _
                  reader(0), reader(1))
            Loop
        Else
            Console.WriteLine("No rows returned.")
        End If
    End Using
End Sub


Working with Command Parameters


Command objects use parameters to pass values to SQL statements or stored procedures, providing type checking and validation. Unlike command text, parameter input is treated as a literal value, not as executable code. This helps guard against "SQL injection" attacks, in which an attacker inserts a command into an SQL statement that compromises security on the server. In addition to the security benefits, parameterized commands provide a convenient method for organizing values passed to a data source.


A DbParameter object can be created using its constructor, or by adding it to the DbParameterCollection by calling the Add method of the DbParameterCollection collection. The Add method will take as input either constructor arguments or an existing parameter object, depending on the data provider.


Supplying the ParameterDirection Property


When adding parameters, you need to supply a ParameterDirection property for parameters other than input parameters. The following table shows the ParameterDirection values you can use with the


ParameterDirection enumeration.



Member nameDescription
InputThe parameter is an input parameter. This is the default.
InputOutputThe parameter is capable of both input and output.
OutputThe parameter is an output parameter.
ReturnValueThe parameter represents a return value from an operation such as a stored procedure, built-in function, or user-defined function.
Working with Parameter Placeholders
The syntax for parameter placeholders depends on the data source. The .NET Framework data providers handle naming and specifying parameters and parameter placeholders differently. This syntax is tailored to a specific data source, as described in the following table.



Data providerParameter naming syntax
SqlClientUses named parameters in the format @parametername.
OleDbUses positional parameter markers indicated by a question mark (?).
OdbcUses positional parameter markers indicated by a question mark (?).
OracleClientUses named parameters in the format :parmname (or parmname).


Specifying Parameter Data Types


The data type of a parameter is specific to the .NET Framework data provider. Specifying the type converts the value of the Parameter to the .NET Framework data provider type before passing the value to the data source. You may also specify the type of a Parameter in a generic fashion by setting the DbType property of the Parameter object to a particular DbType.


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 typeSystem.Data.DbTypeSqlDbTypeOleDbTypeOdbcTypeOracleType
boolBooleanBitBooleanBitByte
byteByteTinyIntUnsignedTinyIntTinyIntByte
byte[]BinaryVarBinary. 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 theSqlDbType.VarBinaryBinaryRaw
charInferring a SqlDbType from char is not supported.CharCharByte
DateTimeDateTimeDateTimeDBTimeStampDateTimeDateTime
DecimalDecimalDecimalDecimalNumericNumber
doubleDoubleFloatDoubleDoubleDouble
floatSingleRealSingleRealFloat
GuidGuidUniqueIdentifierGuidUniqueIdentifierRaw
Int16Int16SmallIntSmallIntSmallIntInt16
Int32Int32IntIntIntInt32
Int64Int64BigIntBigIntBigIntNumber
objectObjectVariantVariantInferring anOdbcType fromObject is not supported.Blob
stringStringNVarChar. This implicit conversion will fail if the string is greater than the maximum size of anNVarChar, which is 4000 characters. For strings greater than 4000 characters, explicitly set theSqlDbType.VarWCharNVarCharNVarChar
TimeSpanTimeInferring a SqlDbType from TimeSpan is not supported.DBTimeTimeDateTime
UInt16UInt16Inferring a SqlDbType from UInt16 is not supported.UnsignedSmallIntIntUInt16
UInt32UInt32Inferring a SqlDbType from UInt32 is not supported.UnsignedIntBigIntUInt32
UInt64UInt64Inferring a SqlDbType from UInt64 is not supported.UnsignedBigIntNumericNumber
AnsiStringVarCharVarCharVarCharVarChar
AnsiStringFixedLengthCharCharCharChar
CurrencyMoneyCurrencyInferring anOdbcType fromCurrency is not supported.Number
DateInferring a SqlType from Date is not supported.DBDateDateDateTime
SByteInferring a SqlType from SByte is not supported.TinyIntInferring anOdbcType fromSByte is not supported.SByte
StringFixedLengthNCharWCharNCharNChar
TimeInferring a SqlType from Time is not supported.DBTimeTimeDateTime
VarNumericInferring a SqlDbType from VarNumeric is not supported.VarNumericInferring anOdbcType fromVarNumeric is not supported.Number


Example


This example demonstrates how to call a SQL Server stored procedure in the Northwind sample database. The name of the stored procedure is dbo.SalesByCategory and it has an input parameter named @CategoryName with a data type of nvarchar(15). The code creates a new SqlConnection inside of a using block so that the connection is disposed when the procedure ends. The SqlCommand and SqlParameter objects are created, and their properties set. A SqlDataReader executes the SqlCommand and returns the result set from the stored procedure, displaying the output in the console window.


Shared Sub GetSalesByCategory(ByVal connectionString As String, _
    ByVal categoryName As String)

    Using connection As New SqlConnection(connectionString)

        ' Create the command and set its properties.
        Dim command As SqlCommand = New SqlCommand()
        command.Connection = connection
        command.CommandText = "SalesByCategory"
        command.CommandType = CommandType.StoredProcedure

        ' Add the input parameter and set its properties.
        Dim parameter As New SqlParameter()
        parameter.ParameterName = "@CategoryName"
        parameter.SqlDbType = SqlDbType.NVarChar
        parameter.Direction = ParameterDirection.Input
        parameter.Value = categoryName

        ' Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter)

        ' Open the connection and execute the reader.
        connection.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()

        If reader.HasRows Then
            Do While reader.Read()
                Console.WriteLine("{0}: {1:C}", _
                  reader(0), reader(1))
            Loop
        Else
            Console.WriteLine("No rows returned.")
        End If
    End Using
End Sub


Using Parameters with an OleDbCommand or OdbcCommand


OleDb Example


Dim command As OleDbCommand = New OleDbCommand( _
  "SampleProc", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OleDbParameter = command.Parameters.Add( _
  "RETURN_VALUE", OleDbType.Integer)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OleDbType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OleDbType.VarChar, 28)
parameter.Direction = ParameterDirection.Output


Odbc Example


Dim command As OdbcCommand = New OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OdbcParameter = command.Parameters.Add("RETURN_VALUE", OdbcType.Int)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28)
parameter.Direction = ParameterDirection.Output




Working with DataAdapters


A DataAdapter is used to retrieve data from a data source and populate tables within a DataSet. The DataAdapter also resolves changes made to the DataSet back to the data source. The DataAdapter uses the Connection object of the .NET Framework data provider to connect to a data source, and it uses Command objects to retrieve data from and resolve changes to the data source.


Each .NET Framework data provider included with the .NET Framework has a DataAdapter object: the .NET Framework Data Provider for OLE DB includes an OleDbDataAdapter object, the .NET Framework Data Provider for SQL Server includes a SqlDataAdapter object, the .NET Framework Data Provider for ODBC includes an OdbcDataAdapter object, and the .NET Framework Data Provider for Oracle includes an OracleDataAdapter object.




Populating a DataSet from a DataAdapter


The ADO.NET DataSet is a memory-resident representation of data that provides a consistent relational programming model independent of the data source. The DataSet represents a complete set of data including tables, constraints, and relationships among the tables. Because the DataSet is independent of the data source, a DataSet can include data local to the application, as well as data from multiple data sources. Interaction with existing data sources is controlled through the DataAdapter.


The SelectCommand property of the DataAdapter is a Command object that retrieves data from the data source. The InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter are Command objects that manage updates to the data in the data source according to modifications made to the data in the DataSet. These properties are covered in more detail in Updating Data Sources with DataAdapters.


The Fill method of the DataAdapter is used to populate a DataSet with the results of the SelectCommand of the DataAdapter. Fill takes as its arguments a DataSet to be populated, and a DataTable object, or the name of the DataTable to be filled with the rows returned from the SelectCommand.


Using the DataAdapter to fetch an entire table takes time, especially if there are many rows in the table. This is because accessing the database, locating and processing the data, and then transferring the data over the wire to your client is time consuming. Pulling the entire table to the client also locks all of the rows on the server. To improve performance, you can use the WHERE clause can to greatly reduce the number of rows returned to the client. You can also reduce the amount of data returned to the client by only explicitly listing required columns in the SELECT statement. Another good workaround is to retrieve the rows in batches—such as a few hundred rows at a time—and only retrieve the next batch when the client is done with the current batch.


The Fill method uses the DataReader object implicitly to return the column names and types used to create the tables in the DataSet, as well as the data to populate the rows of the tables in the DataSet. Tables and columns are only created if they do not already exist; otherwise Fill uses the existing DataSet schema. Column types are created as .NET Framework types according to the tables in Mapping .NET Data Provider Data Types to .NET Framework Data Types. Primary keys are not created unless they exist in the data source and DataAdapter.MissingSchemaAction is set to MissingSchemaAction.AddWithKey. If Fill finds that a primary key exists for a table, it will overwrite data in the DataSet with data from the data source for rows where the primary key column values match those of the row returned from the data source. If no primary key is found, the data is appended to the tables in the DataSet. Fill uses any mappings that may exist when populating the DataSet.


The following code example creates an instance of a SqlDataAdapter that uses a SqlConnection to the Microsoft SQL Server Northwind database and populates a DataTable in a DataSet with the list of customers. The SQL statement and SqlConnection arguments passed to the SqlDataAdapter constructor are used to create the SelectCommand property of the SqlDataAdapter.


' Assumes that connection is a valid SqlConnection object.
Dim queryString As String = _
  "SELECT CustomerID, CompanyName FROM dbo.Customers"
Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
  queryString, connection)

Dim customers As DataSet = New DataSet
adapter.Fill(customers, "Customers")


Populating a DataSet from Multiple DataAdapters


Any number of DataAdapter objects can be used with a DataSet. Each DataAdapter can be used to fill one or more DataTable objects and resolve updates back to the relevant data source. DataRelation and Constraint objects can be added to the DataSet locally, enabling you to relate data from dissimilar data sources. For example, a DataSet can contain data from a Microsoft SQL Server database, an IBM DB2 database exposed via OLE DB, and a data source that streams XML. One or more DataAdapter objects can handle communication to each data source.


Example


The following code example populates a list of customers from the Northwind database on Microsoft SQL Server 2000, and a list of orders from the Northwind database stored in Microsoft Access 2000. The filled tables are related with a DataRelation, and the list of customers is then displayed with the orders for that customer.


' Assumes that customerConnection is a valid SqlConnection object.
' Assumes that orderConnection is a valid OleDbConnection object.
Dim custAdapter As SqlDataAdapter = New SqlDataAdapter( _
  "SELECT * FROM dbo.Customers", customerConnection)

Dim ordAdapter As OleDbDataAdapter = New OleDbDataAdapter( _
  "SELECT * FROM Orders", orderConnection)

Dim customerOrders As DataSet = New DataSet()
custAdapter.Fill(customerOrders, "Customers")
ordAdapter.Fill(customerOrders, "Orders")

Dim relation As DataRelation = _
  customerOrders.Relations.Add("CustOrders", _
  customerOrders.Tables("Customers").Columns("CustomerID"), _ 
  customerOrders.Tables("Orders").Columns("CustomerID"))

Dim pRow, cRow As DataRow
For Each pRow In customerOrders.Tables("Customers").Rows
  Console.WriteLine(pRow("CustomerID").ToString())

  For Each cRow In pRow.GetChildRows(relation)
    Console.WriteLine(vbTab & cRow("OrderID").ToString())
  Next
Next 




Using Parameters with a DataAdapter


The DbDataAdapter has four properties that are used to retrieve data from and update data to the data source: the SelectCommand property returns data from the data source; and the InsertCommand , UpdateCommand, and DeleteCommand properties are used to manage changes at the data source. The SelectCommand property must be set before calling the Fill method of the DataAdapter. The InsertCommand, UpdateCommand, or DeleteCommand properties must be set before the Update method of the DataAdapter is called, depending on what changes were made to the data in the DataTable. For example, if rows have been added, the InsertCommand must be set before calling Update. When Update is processing an inserted, updated, or deleted row, the DataAdapter uses the respective Command property to process the action. Current information about the modified row is passed to the Command object through the Parameters collection.


When updating a row at the data source, you call the UPDATE statement, which uses a unique identifier to identify the row in the table be updated. The unique identifier is commonly the value of a primary key field. The UPDATE statement uses parameters that contain both the unique identifier and the columns and values to be updated, as shown in the following Transact-SQL statement.


UPDATE Customers SET CompanyName = @CompanyName
WHERE CustomerID = @CustomerID


The syntax for parameter placeholders depends on the data source. This example shows placeholders for a SQL Server data source. Use question mark (?) placeholders for System.Data.OleDb and System.Data.Odbc parameters.


In this Visual Basic example, the CompanyName field is updated with the value of the @CompanyName parameter for the row where CustomerID equals the value of the @CustomerID
parameter. The parameters retrieve information from the modified row using the SourceColumn property of the SqlParameter object. Following are the parameters for the preceding sample UPDATE statement. The code assumes that the variable adapter represents a valid SqlDataAdapter object.


adapter.Parameters.Add( _
"@CompanyName", SqlDbType.NChar, 15, "CompanyName")
Dim parameter As SqlParameter = _
adapter.UpdateCommand.Parameters.Add("@CustomerID", _
SqlDbType.NChar, 5, "CustomerID")
parameter.SourceVersion = DataRowVersion.Original


The Add method of the Parameters collection takes the name of the parameter, the DataAdapter specific type, the size (if applicable to the type), and the name of the SourceColumn from the DataTable. Notice that the SourceVersion of the @CustomerID parameter is set to Original. This ensures that the existing row in the data source is updated if the value of the identifying column or columns has been changed in the modified DataRow. In that case, the Original row value would match the current value at the data source, and the Current row value would contain the updated value. The SourceVersion for the @CompanyName parameter is not set and will use the default, Current row value.




Parameter.SourceColumn, Parameter.SourceVersion


The SourceColumn and SourceVersion may be passed as arguments to the Parameter constructor, or set as properties of an existing Parameter. The SourceColumn is the name of theDataColumn from the DataRow where the value of the Parameter will be retrieved. The SourceVersion specifies the DataRow version that the DataAdapter uses to retrieve the value.
The following table shows the DataRowVersion enumeration values available for use with SourceVersion.


DataRowVersion EnumerationDescription
CurrentThe parameter uses the current value of the column. This is the default.
DefaultThe parameter uses the DefaultValue of the column.
OriginalThe parameter uses the original value of the column.
ProposedThe parameter uses a proposed value.


The SqlClient code example in the next section defines a parameter for an UpdateCommand in which the CustomerID column is used as a SourceColumn for two parameters: @CustomerID(SET CustomerID = @CustomerID), and @OldCustomerID (WHERE CustomerID = @OldCustomerID). The @CustomerID parameter is used to update the CustomerID column to the current value in the DataRow. As a result, the CustomerID SourceColumn with a SourceVersion of Current is used. The @OldCustomerID parameter is used to identify the current row in the data source. Because the matching column value is found in the Original version of the row, the same SourceColumn (CustomerID) with a SourceVersion of Original is used.

Working with SqlClient Parameters


The following example demonstrates how to create a SqlDataAdapter and set the MissingSchemaAction to AddWithKey in order to retrieve additional schema information from the database. The SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand properties set and their corresponding SqlParameter objects added to the Parameters collection. The method returns a SqlDataAdapter object.

Public Function CreateSqlDataAdapter( _
    ByVal connection As SqlConnection) As SqlDataAdapter

    Dim adapter As SqlDataAdapter = New SqlDataAdapter
    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

    ' Create the commands.
    adapter.SelectCommand = New SqlCommand( _
        "SELECT CustomerID, CompanyName FROM CUSTOMERS", connection)
    adapter.InsertCommand = New SqlCommand( _
        "INSERT INTO Customers (CustomerID, CompanyName) " & _
         "VALUES (@CustomerID, @CompanyName)", connection)
    adapter.UpdateCommand = New SqlCommand( _
        "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = " & _
        "@CompanyName WHERE CustomerID = @oldCustomerID", connection)
    adapter.DeleteCommand = New SqlCommand( _
        "DELETE FROM Customers WHERE CustomerID = @CustomerID", connection)

    ' Create the parameters.
    adapter.InsertCommand.Parameters.Add("@CustomerID", _
        SqlDbType.Char, 5, "CustomerID")
    adapter.InsertCommand.Parameters.Add("@CompanyName", _
        SqlDbType.VarChar, 40, "CompanyName")

    adapter.UpdateCommand.Parameters.Add("@CustomerID", _
        SqlDbType.Char, 5, "CustomerID")
    adapter.UpdateCommand.Parameters.Add("@CompanyName", _
        SqlDbType.VarChar, 40, "CompanyName")
    adapter.UpdateCommand.Parameters.Add("@oldCustomerID", _
        SqlDbType.Char, 5, "CustomerID").SourceVersion = _
        DataRowVersion.Original

    adapter.DeleteCommand.Parameters.Add("@CustomerID", _
        SqlDbType.Char, 5, "CustomerID").SourceVersion = _
        DataRowVersion.Original

    Return adapter
End Function


OleDb Parameter Placeholders


For the OleDbDataAdapter and OdbcDataAdapter objects, you must use question mark (?) placeholders to identify the parameters.


Dim selectSQL As String = _
  "SELECT CustomerID, CompanyName FROM Customers " & _
  "WHERE CountryRegion = ? AND City = ?"
Dim insertSQL AS String = _
  "INSERT INTO Customers (CustomerID, CompanyName) VALUES (?, ?)"
Dim updateSQL AS String = _
  "UPDATE Customers SET CustomerID = ?, CompanyName = ? " & _
  WHERE CustomerID = ?"

Dim deleteSQL As String = "DELETE FROM Customers WHERE CustomerID = ?"
The parameterized query statements define which input and output parameters must be created. To create a parameter, use the Parameters.Add method or the Parameter constructor to specify the column name, data type, and size. For intrinsic data types, such as Integer, you do not need to include the size, or you can specify the default size.


The following code example creates the parameters for the SQL statement from the preceding example, and then fills a DataSet.


OleDb Example


' Assumes that connection is a valid OleDbConnection object.
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter 

Dim selectCMD AS OleDbCommand = New OleDbCommand(selectSQL, connection)
adapter.SelectCommand = selectCMD

' Add parameters and set values.
selectCMD.Parameters.Add( _
  "@CountryRegion", OleDbType.VarChar, 15).Value = "UK"
selectCMD.Parameters.Add( _
  "@City", OleDbType.VarChar, 15).Value = "London"

Dim customers As DataSet = New DataSet
adapter.Fill(customers, "Customers")
Odbc Parameters


' Assumes that connection is a valid OdbcConnection object.
Dim adapter As OdbcDataAdapter = New OdbcDataAdapter

Dim selectCMD AS OdbcCommand = New OdbcCommand(selectSQL, connection)
adapter.SelectCommand = selectCMD

' Add Parameters and set values.
selectCMD.Parameters.Add("@CountryRegion", OdbcType.VarChar, 15).Value = "UK"
selectCMD.Parameters.Add("@City", OdbcType.VarChar, 15).Value = "London"

Dim customers As DataSet = New DataSet
adapter.Fill(customers, "Customers")


Retrieving Data Using a DataReader


Retrieving data using a DataReader involves creating an instance of the Command object and then creating a DataReader by calling Command.ExecuteReader to retrieve rows from a data source. The following example illustrates using a DataReader where reader represents a valid DataReader and command represents a valid Command object.


reader = command.ExecuteReader();


You use the Read method of the DataReader object to obtain a row from the results of the query. You can access each column of the returned row by passing the name or ordinal reference of the column to the DataReader. However, for best performance, the DataReader provides a series of methods that allow you to access column values in their native data types (GetDateTime, GetDouble, GetGuid, GetInt32, and so on). For a list of typed accessor methods for data provider-specific DataReaders, see OleDbDataReader and SqlDataReader. Using the typed accessor methods, assuming the underlying data type is known, reduces the amount of type conversion required when retrieving the column value.


The following code example iterates through a DataReader object, and returns two columns from each row.


Private Sub HasRows(ByVal connection As SqlConnection)
    Using connection
        Dim command As SqlCommand = New SqlCommand( _
          "SELECT CategoryID, CategoryName FROM Categories;", _
          connection)
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        If reader.HasRows Then
            Do While reader.Read()
                Console.WriteLine(reader.GetInt32(0) _
                  & vbTab & reader.GetString(1))
            Loop
        Else
            Console.WriteLine("No rows found.")
        End If

        reader.Close()
    End Using
End Sub
The DataReader provides an unbuffered stream of data that allows procedural logic to efficiently process results from a data source sequentially. The DataReader is a good choice when retrieving large amounts of data because the data is not cached in memory.


Closing the DataReader


You should always call the Close method when you have finished using the DataReader object.


If your Command contains output parameters or return values, they will not be available until the DataReader is closed.


While a DataReader is open, the Connection is in use exclusively by that DataReader. You cannot execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.


Retrieving Multiple Result Sets using NextResult


If multiple result sets are returned, the DataReader provides the NextResult method to iterate through the result sets in order. The following example shows the SqlDataReader processing the results of two SELECT statements using the ExecuteReader method.


Private Sub RetrieveMultipleResults(ByVal connection As SqlConnection)
    Using connection
        Dim command As SqlCommand = New SqlCommand( _
          "SELECT CategoryID, CategoryName FROM Categories;" & _
          "SELECT EmployeeID, LastName FROM Employees", connection)
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        Do While reader.HasRows
            Console.WriteLine(vbTab & reader.GetName(0) _
              & vbTab & reader.GetName(1))

            Do While reader.Read()
                Console.WriteLine(vbTab & reader.GetInt32(0) _
                  & vbTab & reader.GetString(1))
            Loop

            reader.NextResult()
        Loop
    End Using
End Sub
Download Sample App from here