ado.net complete reference

Primary ADO.NET Concepts

First We need to understand the following concepts

• Data Provider
• Connection Object
• Command Object
• DataReader Object
• DataSet Object
• DataAdapter Object

ADO.NET is a object oriented set of libraries that allows to interact with database.

Data Providers:
ADO.NET provides class libraries that allow common way to interact with specific datasources.

Different Types of Providers

• ODBC Data Provider For older databases
• OleDb Data provider Access or excel
• Oracle Data provider For Oracle databases
• Sql Data provider MS Sql Server
• Borland Data provider Geeric Access like Sql Server,DB2 and Oracle.

Primary Objects of ADO.NET

• The SqlConnection Object
• The SqlCommand Object
• The SqlDataReader Object
• The DataSet Object
• The SqlDataAdapter Object

Brief Description of each above

SqlConnection:

To interact with database we must open the connection using datasource, User name,Password
It will opens the connection for required database based on parameters specified.


SqlCommand:

Command object is used to send sql commands using SqlConnection object.

SqlDataReader:

This object is used to pull the data for select statement from database, this is
farwardonly stream of data.SqlDataReader operations will be happening only in
connected architecture that means ,sqlconnection kept open when doing operations
using SqlDataReader.

DataSet:

This object uses in-memory representation of data, that means it works on disconnected
architecture.once DataSet fills there is no need of SqlConnection, data stored in
DataSet is in-memory.DataSet is common for all DataProviders that is why it is
not prefixed like other objects mentioned above and below.

SqlDataAdapter:

The DataAdapter makes it easy for you to accomplish these things by helping manage data in
disconnected mode.DataAdapter fills a Dataset object when reading and writing the data to the
database.DataAdapter contains a reference to the connection object and opens and closes the
connection automatically.The DataAdapter contains command object references for SELECT, INSERT, UPDATE and DELETE
operations on data.


Creating a SqlConnection Object:
In SqlConnection object, we need to specify the SqlServer Credentials like below

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

The above SqlConnection object is constructor with single argument.

Connection Parameters:

• Data Source:Specifies the server name
• Initial Catalog:Database name
• Integrated Security:To make connection with user's windows login
• User ID:Sql Server UserName
• Password:Sql Server Password

Sql Connection basic operations.

• Instantiate SqlConnection
• Open the Connection
• Passing the connection to the ADO.NET objects
• Perform the operations with ADO.NET objects
• Close the connection

SqlCommand:

A SqlCommand object is to specify what type of operation we need to perform with the database.

Creating SqlCommand Object:
SqlCommand cmd=new SqlCommand(commandtext,conn);

Querying Data:
ExecuteReader(): Which is used to retrive the dataset for viewing.

// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);

// 2. Call Execute reader to get query results
SqlDataReader rdr = cmd.ExecuteReader();

ExecuteNonQuery(): This method is used to perform INSERT,DELETE,MODIFY,SELECT operations on Database

EX:
 // prepare command string
 string insertString = @"
     insert into Categories
     (CategoryName, Description)
     values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";

 // 1. Instantiate a new command with a query and connection
 SqlCommand cmd = new SqlCommand(insertString, conn);

 // 2. Call ExecuteNonQuery to send command
 cmd.ExecuteNonQuery();

 ExecuteScalar():

 To get single value from Database ,we will make use ExecuteScalar() function,Generally this is used for aggregate functions like count, sum, average etc
 EX:

 // 1. Instantiate a new command
 SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);

 // 2. Call ExecuteNonQuery to send command
 int count = (int)cmd.ExecuteScalar();

 SqlDataReader:
 
 This is mostly used for reading the data in most efficient way, we cannot use this for writing the data.This is also called fast farward streams of data.
 by using this we can read the data only one time, once if you read the data , we have to save it because we cannot go back and see the data.


  Getting an instance of a SqlDataReader is a little different than the way you instantiate other ADO.NET objects. You must call ExecuteReader on a command object, like this:

    SqlDataReader rdr = cmd.ExecuteReader();
   
SqlDataAdapter:

 A couple scenarios illustrate why you would want to work with disconnected data: people working without network connectivity and making Web sites more scalable. Consider sales people who need customer data as they travel. At the beginning of the day, they'll need to sync up with the main database to have the latest information available. During the day, they'll make modifications to existing customer data, add new customers, and input new orders. This is okay because they have a given region or customer base where other people won't be changing the same records. At the end of the day, the sales person will connect to the network and update changes for overnight processing.

Another scenario is making a Web site more scalable. With a SqlDataReader, you have to go back to the database for records every time you show a page. This requires a new connection for each page load, which will hurt scalability as the number of users increase. One way to relieve this is to use a DataSet that is updated one time and stored in cache. Every request for the page checks the cache and loads the data if it isn't there or just pulls the data out of cache and displays it. This avoids a trip to the database, making your application more efficient.


Creating a DataSet Object
    DataSet dsCustomers = new DataSet();
Creating A SqlDataAdapter
SqlDataAdapter daCustomers = new SqlDataAdapter(
    "select CustomerID, CompanyName from Customers", conn);
   
    By using fill method of SqlDataAdapter we will the dataset  as below
   
    dsCustomers.Fill(daCustomers);

    / 3. fill in insert, update, and delete commands
        SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers);

Updating Changes:
 After modifications are made to the data, you'll want to write the changes back to the database. Refer to previous discussion in the Introduction of this article on update guidance. The following code shows how to use the Update method of the SqlDataAdapter to push modifications back to the database.
 daCustomers.Update(dsCustomers, "Customers");

 Parameters:
 As you know, the SQL query assigned to a SqlCommand object is simply a string. So, if you want to filter a query, you could build the string dynamically, but you wouldn't want to. Here is a bad example of filtering a query.

    // don't ever do this
    SqlCommand cmd = new SqlCommand(
        "select * from Customers where city = '" + inputCity + "'";

Don't ever build a query this way! The input variable, inputCity, is typically retrieved from a TextBox control on either a Windows form or a Web Page. Anything placed into that TextBox control will be put into inputCity and added to your SQL string. This situation invites a hacker to replace that string with something malicious. In the worst case, you could give full control of your computer away.

Instead of dynamically building a string, as shown in the bad example above, use parameters. Anything placed into a parameter will be treated as field data, not part of the SQL statement, which makes your application much more secure.

Using parameterized queries is a three step process:

    Construct the SqlCommand command string with parameters.
    Declare a SqlParameter object, assigning values as appropriate.
    Assign the SqlParameter object to the SqlCommand object's Parameters property.

   
preparing a SqlCommand Object for Parameters:

// 1. declare command object with parameter
    SqlCommand cmd = new SqlCommand(
        "select * from Customers where city = @City", conn);
       
Declaring a SqlParameter Object:

// 2. define parameters used in command object
    SqlParameter param  = new SqlParameter();
    param.ParameterName = "@City";
    param.Value         = inputCity;
   
Associate a SqlParameter Object with a SqlCommand Object

// 3. add new parameter to command object
    cmd.Parameters.Add(param);
   
You should use parameters to filter queries in a secure manner.

Stored Procedures:

A stored procedures is a pre-defined, reusable routine that is stored in a database

Executing a Stored Procedure:

// 1. create a command object identifying
    // the stored procedure
    SqlCommand cmd  = new SqlCommand(
        "Ten Most Expensive Products", conn);

    // 2. set the command object so it knows
    // to execute a stored procedure
    cmd.CommandType = CommandType.StoredProcedure;
   
Sending Parameters to Stored Procedures

// 1. create a command object identifying
    // the stored procedure
    SqlCommand cmd  = new SqlCommand(
        "CustOrderHist", conn);

    // 2. set the command object so it knows
    // to execute a stored procedure
    cmd.CommandType = CommandType.StoredProcedure;

    // 3. add parameter to command, which
    // will be passed to the stored procedure
    cmd.Parameters.Add(
        new SqlParameter("@CustomerID", custId));

namespace

The namespace keyword is used to declare a scope. This namespace scope lets you organize code and gives you a way to create globally unique types.

Within a namespace, you can declare one or more of the following types:

    another namespace

1.     class
2.     interface
3.     struct
4.     enum
5.     delegate

What is a browser?

Browser is an application , which is used to locate and display webpages

what is a webserver?

A web server is a computer , which is also a system which servers webpages over internet

default authentication mode of asp.net

Before 4.0-Windows

4.0 and above-Forms

where do session variable stored by default in Asp.Net?

By default it will store in Asp.Net worker process

what is the top .net class in Asp.Net?

system.object

Auto Event WireUp

The AutoEventWireUp value may have the value of true or false

We can specify the default value of the AutoEventWireup in the following locations

=>machine.config
=>web.config
=>Individual webforms
=>WebUser Controls

Web.config

<Configuration>
<system.web>
<pages autoEventWireUp=true/>
</system.web>
</configuration>

If you make this changes in web.config file ,it will effect to all ASP.NET webforms.But this we can ovverride in each ASP.NET page.

EX:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string msg = "Auto Event Wire Up Executed";
        lblAutoEventWireUp.Text = msg;
    }
    protected override void OnInit(EventArgs e)
    {
        this.Load += new System.EventHandler(this.Page_Load);
    }
}

If autoEventWireUp event false then it will not execute page load, we need to explicitly call the page_load in OnInit Event like above.

What is object slicing?

When a derived class object is assigned to base class object, only base class properties copied to base class object and leave the derived class properties.This is called object slicing.

Ex:Class Base
{
 public: int i;
 }
 
 class Derived :Base
 {
 public : intj;
 }
 
 int main()
 {
    Base Bobj;
    Derived Dobj;
    Bobj=Dobj;//Here Dobj contains both i and j
    //But only i is copied to Bobj
 }

Extension Methods in c#

Extension Methods

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExtensionMethods
{
    class Program
    {
        static void Main(string[] args)
        {
            int x = 5;
            Console.WriteLine("Factorial of {0} number is{1}", x, x.fact());
            Console.ReadKey();
        }
    }
    static class factorial
    {
        public static int fact(this int number)
        {
            if (number <= 1) return 1;
            if (number == 2) return 2;
            else
            {
                return number * fact(number - 1);
            }
        }
    }
}

properties in c#

Properties

class student
    {
        public int studentID { get; set; }
        public string studentName { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {
            student obj = new student();
            obj.studentID = 116;
            obj.studentName = "satya";
            Console.WriteLine("Student ID {0} and Student Name {1}", obj.studentID, obj.studentName);
            Console.ReadKey();
        }
    }

Inheritance

 1.Simple Inheritance

 class baseClass
    {
        public baseClass()
        {
            Console.WriteLine("Base Class Constructor Called");
        }
        public void print()
        {
            Console.WriteLine("Base Class Print Method Called");
        }
    }
    class derivedClass:baseClass
    {
        public derivedClass()
        {
            Console.WriteLine("Derived Class Constructor Called");
        }
    }
    class Program
    {
        static void Main(string[] args)
        {
            derivedClass obj = new derivedClass();
            obj.print();
            Console.ReadKey();
        }
    }
   
    2.Use of base and new keywords in Inheritance
   
     class Program
    {
        class baseClass
        {
            public baseClass()
            {
                Console.WriteLine("Base Class Constructor Called");
            }
            public baseClass(String newString)
            {
                Console.WriteLine(newString);
            }
            public void Print()
            {
                Console.WriteLine("Base Class Print Method Called");
            }
        }
        class derivedClass:baseClass
        {
            public derivedClass():base("From Child")
            {
                Console.WriteLine("Child Constructor Called");
            }
            public new void Print()
            {
                base.Print();
                Console.WriteLine("Child Class Print Method Called");
            }

        }
        static void Main(string[] args)
        {
            derivedClass obj = new derivedClass();
            obj.Print();
            ((baseClass)obj).Print();
            Console.ReadKey();
        }
    }
   
    keyword base call the base class constructor with the matching parameter list
    Using the base keyword, you can access any of a base class public or protected class members
    Another way to access base class members is through an explicit cast
    Notice the new modifier on the Child class print() method. This enables this method to hide the Parent class print() method and explicitly states your intention that you don't want polymorphism to occur.

Generic Collections

A generic collection is strongly typed (type safe), meaning that you can only put one type of object into it. This eliminates type mismatches at runtime. Another benefit of type safety is that performance is better with value type objects because they don't incur overhead of being converted to and from type object.
Creating Generic List<T> Collections
Ex:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lists
{
    class Program
    {
        static void Main(string[] args)
        {
            List<int> list = new List<int>();
            list.Add(1);
            list.Add(2);
            list.Add(3);
            list.Add(4);
            foreach (var i in list)
            {
                Console.WriteLine(i);
                Console.WriteLine("\n");
            }
            Console.ReadKey();
        }
    }
}
The first thing you should notice is the generic collection List<int>, which is referred to as List of int. If you looked in the documentation for this class, you would find that it is defined as List<T>, where T could be any type. For example, if you wanted the list to work on string or Customer objects, you could define them as List<string> or List<Customer> and they would hold only string or Customer objects

Dictionary<TKey, TValue> Collections
Another very useful generic collection is the Dictionary, which works with key/value pairs. There is a non-generic collection, called a Hashtable that does the same thing, except that it operates on type object.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Dictionary
{
    class Program
    {
        static void Main(string[] args)
        {
            Dictionary<int, Customer> customers = new Dictionary<int, Customer>();
            Customer customer1 = new Customer(1, "customer1");
            Customer customer2 = new Customer(2, "customer2");
            Customer customer3 = new Customer(3, "customer3");
            customers.Add(customer1.customerID, customer1);
            customers.Add(customer2.customerID, customer2);
            customers.Add(customer3.customerID, customer3);

            foreach (KeyValuePair<int, Customer> cust in customers)
            {
                Console.WriteLine("CustomerID-{0} and CustomerName-{1}\n", cust.Key, cust.Value.customerName);
            }
            Console.ReadKey();
        }
    }
    class Customer
    {
        public Customer(int custID, string Name)
        {
            customerID = custID;
            customerName = Name;
        }
        public int customerID
        {
            get;
            set;
        }
        public string customerName
        {
            get;
            set;
        }
    }
}
Whenever coding with the generic collections, add a using System.Collections.Generic declaration to your file.There are many more generic collections to choose from also, such as Stack, Queue, and SortedDictionary.

Armstrong Number Program in C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ArmstrongNumber
{
    class Program
    {
        static void Main(string[] args)
        {int n,m,s=0,r;
            Console.WriteLine("Enter a Number");
            n=Convert.ToInt16(Console.ReadLine());
            m = n;
            while (n > 0)
            {
                r = n % 10;
                s = s + r * r * r;
                n = n / 10;
            }
            if (m == s)
                Console.WriteLine("Armstrong Number");
            else
                Console.WriteLine("Not an Armstrong Number");
            Console.ReadKey();
        }
    }
}

Palindrome Program in C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PrimeNumber
{
    class Program
    {
        static void Main(string[] args)
        {
            int i, j = 2, flag = 1;
            Console.WriteLine("Enter a Number");
            i =Convert.ToInt16(Console.ReadLine());
            while (j <= i / 2)
            {
                if (i % j == 0)
                {
                    flag = 0;
                    break;
                }
                else
                {
                    j++;
                }
            }
            if (flag == 0)
                Console.WriteLine("Given Number is not a Prime");
            else
                Console.WriteLine("Given Number is Prime");
            Console.ReadKey();
        }
    }
}

Prime Number program in C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PrimeNumber
{
    class Program
    {
        static void Main(string[] args)
        {
            int i, j = 2, flag = 1;
            Console.WriteLine("Enter a Number");
            i =Convert.ToInt16(Console.ReadLine());
            while (j <= i / 2)
            {
                if (i % j == 0)
                {
                    flag = 0;
                    break;
                }
                else
                {
                    j++;
                }
            }
            if (flag == 0)
                Console.WriteLine("Given Number is not a Prime");
            else
                Console.WriteLine("Given Number is Prime");
            Console.ReadKey();
        }
    }
}

Factorial Program in C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Factorial
{
    class Program
    {
        static void Main(string[] args)
        {
            int i, n, fact = 1;
            Console.WriteLine("Enter a Number");
            n = Convert.ToInt16(Console.ReadLine());
            for (i = 1; i <= n; i++)
            {
                fact = fact * i;
            }
            Console.WriteLine("Factorial of {0} is {1}",n,fact);
            Console.ReadKey();
        }
    }
}

Maximum length of Query String?

Maximum length of Query String is based on browser not depend on the ASP.NET

Max.Length of a Query String in IE4.0 and above is ~2048 characters
opera-2048 characters
Netscape6 supporsts -2000 characters
Firefox supports-6000 characters

What are the parameters in C#?

There are two types of parameters are there in c#

1.Ref parameter

paramerer should be intilized before passing as ref parameter

EX:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Parameteres
{
    class Program
    {
        static void Main(string[] args)
        {
            int i=0;
            Program obj = new Program();
            obj.show(ref i);
            Console.WriteLine(i);
            Console.ReadKey();

        }

        public void show(ref int i)
        {
            i = 10;
        }
    }
}

2.out paramete we no need to initilize the values, and both the methods we can retrun multiple values

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Parameteres
{
    class Program
    {
        static void Main(string[] args)
        {
            int i, j ;
            Program obj = new Program();
            obj.show(out i, out j);
            Console.WriteLine(i);
            Console.WriteLine(j);
            Console.ReadKey();

        }

        public void show(out int i,out  int j)
        {
            i = 10;
            j = 20;

        }
    }
}

Authetication in ASP.NET

There are three types of Authetication modes are there in ASP.NET

1.Form Authentication
2.Windows Authentication
3.Passport Authentication

How can we create Assembly in GAC?

There are 3 methods
1stmethod:By using command GAC UTIL -I
By using this command in visual studio command prompt

2ndmethod:Whatever we are created, we can drag to the GAC

3rdmethod:
Strong Name Concept:

Strong Name is a public key token of the assembly, which is different(Unique) for each Assembly

By using SNK properties one option is there sign of assembly(When we deploy automatically strong will create)

without strong name we cannot create assembly in GAC.

Benifit of StrongName :

we are creating an Identifier for Assembly that is Unique for each Assembly.

For ex: two assemblies are there Assembly A, Assembly A having same name

While calling conflict will come , application will get confuse which Assembly can be called , When the  Assemblies having same name and same version that time application will get confuse, which one has to use ,this is called dll hell problem.

Name Space: We can give logical group name to types(Classes, Interfaces)

System.dll Assembly
(NameSpace)

It consists of objects,classes,public access specifiers, string class...etc

NameSpace A ,NameSpace B

both namespaces have class x in each one separately , when we use A.x then there might not be any Naming Convention.

How Assemblies are stored in GAC?

By using Framework they are stored in GAC,those are predefine Assemblies.

Assemblies are of 3 types


1.Private Assembly:Only Application A can use, No other application can access ,usually it will be there in local application /bin folder,that's why other applications can't access.
2.Shared Assembly:It is stored in GAC(Global Assembly Cache) any  of applications can access
3.Satellite Assembly:It will use Culture and Multi Language Assemblies

Assembly in .NET

Deployment unit of Application, dll's,.exe's are called Assemblies.

Ex:System.IO

GAC:Global Assembly Cache (We can see in C:Windows\assembly)

There is a repository there we can store assembly, those assembly we can use for multiple applications.

What is Abstract method?

Abstract method doesnot provide implementation and forces derived class to override the method.

There are following ways we can make use of Abstract Classes and Methods

1.We can create Non-Abstract methods inside Abstract Class, and Inherited Class can use this method.

See the Example below

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AbstractMethod1
{
    abstract class Test
    {
        public void MethodInAbstract()
        {
            Console.WriteLine("NonAbstract Method Called");
            Console.ReadKey();
        }
    }

    class Inherit : Test
    {
    }
    class Program
    {
        static void Main(string[] args)
        {
            Inherit obj = new Inherit();
            obj.MethodInAbstract();
        }
    }
}

In above program Test is Abstract Class MethodInAbstract is Non Abstractmethod. Another Class Inherit inherited the Abstract Class Test. Now we created object for
Inherit and called the MethodInAbstract method.

More Observations:
Abstract class will starts with keyword 'Abstract'
We can not create object for abstract class
Test obj=new Test(); it will show the error message 'Cannot create an instance of the abstract class or interface 'AbstractMethod1.Test'

2.Abstract method is meant by method which is not having any body, Who ever class inherited this Abstract Class that class must be override the
abstract methods.

Ex:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AbstractMethod2
{
    abstract class Myclass
    {
       public abstract void AbstractMethod();
    }

    class MyInheritedClass : Myclass
    {
        public override void AbstractMethod()
        {
            Console.WriteLine("Abstract method implemented");
            Console.ReadKey();
        }
    }
    class Program
    {
        static void Main(string[] args)
        {
            MyInheritedClass obj = new MyInheritedClass();
            obj.AbstractMethod();
        }
    }
}

In above AbstractMethod() is abstract method, which was implemented by derived class MyInheritedClass.

More Observations:
Abstract method must be public and starts with abstract keyword
If we not implement the abstract method in derived class we will get error message ''AbstractMethod2.MyInheritedClass' does not implement inherited abstract member 'AbstractMethod2.Myclass.AbstractMethod()'

What is Object?

Object is a real world entity, object will be existing physically which means it will occupy memory where as Class is a logical unit.

If we want to use class methods or variables we need to create object, there is no other way to use class methods without creating object.

Ex:

 class A
    {
       public int sum(int a,int b)
        {
            return a + b;
        }
    }

class Program
    {
        static void Main(string[] args)
        {
            A obj = new A(); // Creating Object of Class A to access public methods of ClassA
            int sum = obj.sum(5, 4);
            Console.WriteLine(sum);
            Console.ReadKey();
           
        }
    }

In Class 'Program' used method 'sum' of another Class A by creating object of Class A

What is Class?

Class is a blue print or template which is having state and behavior and which supports objects of which own types.

Class must support oops concepts ie.
1.Abstraction
2.Encapsulation
3.Inheritance
4.Polymorphism

What is the difference between UserControl and Custom Control?

User Controls are easy to create where as Custom Controls requires extra effort

User Controls are used where layout is static where as custom controls are used in dynamic layouts

A User Control cannot be added to the toolbar where as Custom Control can be

A separate copy of User Control is required in every application that uses it where as since Custom Controls are shared in GAC, only single copy can be used by all applications.

What is Shared Assembly?

A shared assembly is kept in the GAC and can be used by one or more applications on a machine.

What is Private Assembly?

A private assembly is local to the installation directory of an application and is used only by that application

What is flat file?

A flat file as the name implies which can be read or written sequentially

What is Control?

A control is component that provides UI-Capability

What is meant by Business Logic?

It is the functionality which handles the exchange of information between DB and UI.