ADO.NET Tutorial

The ADO.NET Object Modell

Introduction:
ADO.NET is an object-oriented set of libraries that allows you to interact with data sources. Commonly, the data source is a database, but it could also be a text file, an Excel spreadsheet, or an XML file. For the purposes of this tutorial, we will look at ADO.NET as a way to interact with a data base.

Data Providers:
We know that ADO.NET allows us to interact with different types of data sources and different types of databases.

ADO.NET Data Providers are class libraries that allow a common way to interact with specific data sources or protocols. The library APIs have prefixes that indicate which provider they support.

Provider Name	API prefix	Data Source Description
ODBC Data Provider	Odbc	Data Sources with an ODBC interface. Normally older data bases.
OleDb Data Provider	OleDb	Data Sources that expose an OleDb interface, i.e. Access or Excel.
Oracle Data Provider	Oracle	For Oracle Databases.
SQL Data Provider	Sql	For interacting with Microsoft SQL Server.
Borland Data Provider	Bdp	Generic access to many databases such as Interbase, SQL Server, IBM DB2, and Oracle.

The SqlConnection Object:
To interact with a database, you must have a connection to it. The connection helps identify the database server, the database name, user name, password, and other parameters that are required for connecting to the data base. A connection object is used by command objects so they will know which database to execute the command on.

The SqlCommand Object :
The process of interacting with a database means that you must specify the actions you want to occur. This is done with a command object. You use a command object to send SQL statements to the database. A command object uses a connection object to figure out which database to communicate with. You can use a command object alone, to execute a command directly, or assign a reference to a command object to an SqlDataAdapter, which holds a set of commands that work on a group of data as described below.
The SqlDataReader Object :
Many data operations require that you only get a stream of data for reading. The data reader object allows you to obtain the results of a SELECT statement from a command object. For performance reasons, the data returned from a data reader is a fast forward-only stream of data. This means that you can only pull the data from the stream in a sequential manner This is good for speed, but if you need to manipulate data, then a DataSet is a better object to work with.
The DataSet Object :
DataSet objects are in-memory representations of data. They contain multiple Datatable objects, which contain columns and rows, just like normal database tables. You can even define relations between tables to create parent-child relationships. The DataSet is specifically designed to help manage data in memory and to support disconnected operations on data, when such a scenario make sense. The DataSet is an object that is used by all of the Data Providers, which is why it does not have a Data Provider specific prefix.

The SqlDataAdapter Object :
Sometimes the data you work with is primarily read-only and you rarely need to make changes to the underlying data source Some situations also call for caching data in memory to minimize the number of database calls for data that does not change. The data adapter makes it easy for you to accomplish these things by helping to manage data in a disconnected mode. The data adapter fills a DataSet object when reading the data and writes in a single batch when persisting changes back to the database. A data adapter contains a reference to the connection object and opens and closes the connection automatically when reading from or writing to the database. Additionally, the data adapter contains command object references for SELECT, INSERT, UPDATE, and DELETE operations on the data. You will have a data adapter defined for each table in a DataSet and it will take care of all communication with the database for you. All you need to do is tell the data adapter when to load from or write to the database.

Creating a SqlConnection Object :
A SqlConnection is an object, just like any other C# object. Most of the time, you just declare and instantiate the SqlConnection all at the same time, as shown below:

SqlConnection conn = new SqlConnection( "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");

The SqlConnection object instantiated above uses a constructor with a single argument of type string This argument is called a connection string. Table 1 describes common parts of a connection string.

 ADO.NET Connection Strings contain certain key/value pairs for specifying how to make a database connection. They include the location, name of the database, and security credentials.

Connection String Parameter Name	Description
Data Source	Identifies the server. Could be local machine, machine domain name, or IP Address.
Initial Catalog	Database name.
Integrated Security	Set to SSPI to make connection with user's Windows login
User ID	Name of user configured in SQL Server.
Password	Password matching SQL Server User ID.

Exceptional Handling

Exceptional handling is the most important factor in program debugging. In programs an error can occur at almost any statement, for almost any reason. Checking for all these errors becomes unbearably complex. Exception handling separates this logic. It simplifies control flow.

Programming :
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication4
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
try
{
int a = 10 / int.Parse("0");
}
catch(Exception e123)
{
    MessageBox.Show(e123.ToString());
    //error logging
throw e123;
}
        }
    }
}

Result: 

Error Dialogbox will appear with error cause message.

Array

In computer memory every byte is an array element. Abstractions translate these bytes into objects and give them meaning. Arrays are a foundational type. They are the basis of more usable collections. They use a special syntax form.

An array is a fixed collection of same-type data that are stored contiguously and that are accessible by an index.

Example:
We allocate and provide integers to three different values such as 2 , 2*2 and 4*3 respectively.

Programming:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;


 namespace WindowsFormsApplication3
 {
     public partial class Form1 : Form
     {
         public Form1()
         {
             InitializeComponent();
         }

         private void button1_Click(object sender, EventArgs e)
         {
             int[] values = new int[3];
             values[0] = 2;
             values[1] = values[0] * 2;
             values[2] = values[1] * 3;

             foreach (int value in values)
             {
              MessageBox.Show(value.ToString());
             }
         }
     }
 }

Result:

Output in Message box  =  2

Output in Message box = 4

Output in Message box = 12

Enums

Enums store special values. They make programs simpler.

Example :
In this example, we see an enum type that indicates the importance of something. An enum type internally contains an enumerator list. We use enums when we want readable code that uses constants.
An enum type is a distinct value type that declares a set of named constants.
Karachi ., sindh @ Pakistan (Sarib;)

Programming :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
 enum food
            {
            snacks,
            coldrinks,
            candies
            };

 namespace WindowsFormsApplication3
 {
     public partial class Form1 : Form
     {
         public Form1()
         {
             InitializeComponent();
         }

         private void button1_Click(object sender, EventArgs e)
         {
             string a;
             string b;
             b = "I don't want";
             a = "Want this";
             food value = food.snacks;
             if (value == food.snacks)
             {
                 MessageBox.Show(b);
             }
             else if (value == food.candies)
             {
                 MessageBox.Show(a);
             }
         }
     }
 }

Result : 

Show "I don't want" on messagebox or dialog box.

IF and IF Else

If statement is used to declare and obeyed the single conditions while wlse if for multiple conditions of the program just like an electric button functions (ON / OFF)

Syntax (Single Condition) :

int a;

string b = "true";

a = 10 / 5; if (a == 2) 

{ 

 MessageBox.Show(b);

}

Result:

Display "True" on messagebox or dialog box.

Syntax (Multiple Conditions) :

int a;

string b = "true";

string c = "wow";

a = 5 / 5;

if (a == 2)

{

   MessageBox.Show(b);

}

else if (a == 1)

{

   MessageBox.Show(c);

}

Result:

Display "wow" on messagebox or dialob box.

Time Example

Programming:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication5
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            DateTime val = DateTime.Now;
            label1.Text = val.ToLongDateString();

        }
    }
}

Result:

While Loop Example

Description :

Ingredients:
1) One List Box
2) One Button

Programming (Ascending Order):
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication5
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            int A;
            A = 1;
            while (A < 10)
            {
                listBox1.Items.Add(A);
                A++;
            }
            }
         
        }
    }

Result:
1 to 10

Programming (Descending Order):

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication5
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            int A;
            A = 10;
            while (A > 1)
            {
                listBox1.Items.Add(A);
                A--;
            }
            }
         
        }
    }

Result:
10 to 1

For Loop Example

Description :
In this example, we are using (for loop)statement to show counting on listbox.

Ingredients:
1) One List Box
2) One Button

Programming (Ascending Order) :
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication5
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            int A;
            for (A = 1; A < 10; A++ )
            {
                listBox1.Items.Add(A);
           
            }

            int B;
            for (B = 1; B < 10; B++)
            {
                listBox1.Items.Add(B);

            }
            }
         
        }
    }

Output:
1 to 9

Programming (Decending Order):

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication5
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
         
            int B;
            for (B = 10-1; B >= 0; B--)
            {
                listBox1.Items.Add(B);

            }
            }
         
        }
    }

Output :
9 to 1

Switch Example

Description:
The switch statement is a control statement that selects a switch section to execute from a list of candidates.
Each switch section contains one or more case labels and a list of one or more statements. The following example shows a simple switch statement that has three switch sections. In this tutorial we want to get result of garde in Labelbox.

Ingredients:
1) Two Label Boxes
2) One Textbox
3) One Button

Programming:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication5
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string grade;
            string Value;
            Value = textBox1.Text;
            grade = Value;
                       
            switch (grade)
            {
                case "A":
                    label1.Text = ("Excellent Sarib!");
                    break;
                case "B":
                    label1.Text = ("Good Sarib");
                    break;
                default:
                    label1.Text =("Invalid grade");
                    break;
            }
         
            }
         
        }
    }

( Before )

( After )

Integer Example

Description :
In this example, User can insert two numbers in texboxes and when it will click on button1, A messagebox window will appear with result.

Ingredients:
1) Two Textboxes
2) One Button
3) One MessageBox

Programming :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            int a;
            int b;
            int c;
            a = int.Parse(textBox1.Text);
            b = int.Parse(textBox2.Text);
            c = a + b;
            MessageBox.Show(c.ToString());
        }
    }
}

( Before )

( After )

String Example

Description :
In this example, User can insert data on TextBox and when it will click on button 1, it will display on Labelbox. We introduce text formating on Labelbox  such as color change and font weight.

Ingredients :
1) One Textbox
2) One LabelBox
3) One Button

Programming :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            String A;
            A = textBox1.Text;
            label1.ForeColor = System.Drawing.Color.Chocolate;
          label1.Font = new Font(label1.Font, FontStyle.Bold);
          label1.Text = A;
        }
    }
}

( Before )

( After )

Introduction

C-Sharp (C#) :

C# is a modern, general-purpose, object-oriented programming language developed by Microsoft and approved by Ecma and ISO.

C# was developed by Anders Hejlsberg and his team during the development of .Net Framework.

C# is designed for Common Language Infrastructure (CLI), which consists of the executable code and runtime environment that allows use of various high-level languages to be used on different computer platforms and architectures.

The following reasons make C# a widely used professional language:

1. Modern, general-purpose programming language
2. Object oriented.
3. Component oriented.
4. Easy to learn.
5. Structured language.
6. It produces efficient programs.
7. It can be compiled on a variety of computer platforms.
8. Part of .Net Framework.
9. Integration with Microsoft Windows.

Object Oreinted Programming:

A type of programming in which programmers define not only the data type of a data structure, but also the types of operations (functions) that can be applied to the data structure. In this way, the data structure becomes an object that includes both data and functions. In addition, programmers can create relationships between one object and another. For example, objects can inheritcharacteristics from other objects.

One of the principal advantages of object-oriented programming techniques over procedural programming techniques is that they enable programmers to create modules that do not need to be changed when a new type of object is added. A programmer can simply create a new object that inherits many of its featuresfrom existing objects. This makes object-oriented programs easier to modify.

Data Type:

In computer science and computer programming, a data type or simply type is a classification identifying one of various types of data, such as real-valued, integer or Boolean, that determines the possible values for that type; the operations that can be done on values of that type; the meaning of the data; and the way values of that type can be stored.

Almost all programming languages explicitly include the notion of data type, though different languages may use different terminology. Common data types may include:

• Integers,
• Booleans,
• Characters,
• Floating-point numbers,
• Alphanumeric strings.

Boolean Type :
The Boolean type represents the values: true and false. Although only two values are possible, they are rarely implemented as a single binary digit for efficiency reasons. Many programming languages do not have an explicit boolean type, instead interpreting (for instance) 0 as false and other values as true.

Integers Type:

The integer type represents the numeric values and ranges. The following table sorts out the C# integer variables so you’ll always know the range and size of each.

C# Floating Point Variable Types:

C# floating point variables come in two types: float and double. The following table compares these two types in terms of size, range, and accuracy.

Type	Size (bytes)	Range	Accuracy	In Use
float	8	1.5 x 10–45 to 3.4 x 1038	6–7 digits	float f = 1.2F;
double	16	5.0 x 10–324 to 1.7 x 10308	15–16 digits	double d = 1.2;

Characters Data Type:
To store alphanumeric text, such as letters, numbers, spaces, symbols, and punctuation, use the Character data type. To prevent data in Character fields from being translated across code pages, use the Character (Binary) field type.

Character fields or variables and Character (Binary) fields can store text information that is not used in mathematical calculations, such as names, addresses, and numbers. For example, phone numbers or zip codes, though they include mostly numbers, are actually best stored as character values.

Define a Class in C# ?
In C#, as in most object-oriented programming languages, a class is a bundling of unlike data and functions that logically belong together into one tidy package. Good classes are designed to represent concepts.
When you define a class, you define a blueprint for a data type. This doesn't actually define any data, but it does define what the class name means, that is, what an object of the class will consist of and what operations can be performed on such an object. Objects are instances of a class. The methods and variables that constitute a class are called members of the class.

Syntax:
{
class "name"

{
data types or formulas used;
}
}

Example:

{
    class Box
    {
       public double length;   // Length of a box
       public double breadth;  // Breadth of a box
       public double height;   // Height of a box
    }