ASP.Net, C#, SQL Server

MAKs (MultipleActivation) allow a predetermined number of activations. This number depends on the type of agreement you have.The number of activations can be revised (at the request of the customer or by Microsoft) to accommodate your regular usage.

On the other hand, One retail product key can only be activated for 10 times.

Two new features to C# 4.0 are optional parameters and named parameters. Optional parameters have been a part of VB.Net but it's now possible to do this in C#. Instead of using overload methods, you can do this in C#:

private string Method1(string givenName,
    string surname = "Prakash", int age = 23)
{
return givenName + " " + surname;
}

The second and third parameters, surname & age, both have default values. The only parameter required is givenName. To call this method you can either write this:

string name = null;
name = Method1("Ved");

That will return Ved Prakash. You can also do this:

string name = null;
name = Method1 ("Prakash ", "Tejwani");

The value returned is Prakash Tejwani. But what if you didn't want to specify a surname but you did want to pass the age? You can do this by using named parameters:

string name = null;
name = Method1 ("Shyam", age: 24);

These are nice additions to the language.

Overview

One of the fundamental concepts of object oriented software development is polymorphism. The term polymorphism (from the Greek meaning "having multiple forms") in OO is the characteristic of being able to assign a different meaning or usage to something in different contexts - specifically, to allow a variable to refer to more than one type of object.

Example Class Hierarchy

Let's assume the following simple class hierarchy with classes A, B and C for the discussions in this text. A is the super- or base class, B is derived from A and C is derived from class B. In some of the easier examples, we will only refer to a part of this class hierarchy.

Inherited Methods

A method Foo() which is declared in the base class A and not redeclared in classes B or C is inherited in the two subclasses

using System;
namespace Polymorphism
{
class A
{
public void Foo() { Console.WriteLine("A::Foo()"); }
}

class B : A {}

class Test
{
static void Main(string[] args)
{
A a = new A();
a.Foo(); // output --> "A::Foo()"

B b = new B();
b.Foo(); // output --> "A::Foo()"
}
}
}

The method Foo() can be overridden in classes B and C:

using System;
namespace Polymorphism
{
class A
{
public void Foo() { Console.WriteLine("A::Foo()"); }
}

class B : A
{
public void Foo() { Console.WriteLine("B::Foo()"); }
}

class Test
{
static void Main(string[] args)
{
A a;
B b;

a = new A();
b = new B();
a.Foo(); // output --> "A::Foo()"
b.Foo(); // output --> "B::Foo()"

a = new B();
a.Foo(); // output --> "A::Foo()"
}
}
}

There are two problems with this code.

The output is not really what we, say from Java, expected. The method Foo() is a non-virtual method. C# requires the use of the keyword virtual in order for a method to actually be virtual. An example using virtual methods and polymorphism will be given in the next section.

Although the code compiles and runs, the compiler produces a warning: ...\polymorphism.cs(11,15): warning CS0108: The keyword new is required on 'Polymorphism.B.Foo()' because it hides inherited member 'Polymorphism.A.Foo()'

This issue will be discussed in section Hiding and Overriding Methods.

Virtual and Overridden Methods

Only if a method is declared virtual, derived classes can override this method if they are explicitly declared to override the virtual base class method with the override keyword.

using System;
namespace Polymorphism
{
class A
{
public virtual void Foo() { Console.WriteLine("A::Foo()"); }
}

class B : A
{
public override void Foo() { Console.WriteLine("B::Foo()"); }
}

class Test
{
static void Main(string[] args)
{
A a;
B b;

a = new A();
b = new B();
a.Foo(); // output --> "A::Foo()"
b.Foo(); // output --> "B::Foo()"

a = new B();
a.Foo(); // output --> "B::Foo()"
}
}
}

Method Hiding

Why did the compiler in the second listing generate a warning? Because C# not only supports method overriding, but also method hiding. Simply put, if a method is not overriding the derived method, it is hiding it. A hiding method has to be declared using the new keyword. The correct class definition in the second listing is thus:

using System;
namespace Polymorphism
{
class A
{
public void Foo() { Console.WriteLine("A::Foo()"); }
}

class B : A
{
public new void Foo() { Console.WriteLine("B::Foo()"); }
}

class Test
{
static void Main(string[] args)
{
A a;
B b;

a = new A();
b = new B();
a.Foo(); // output --> "A::Foo()"
b.Foo(); // output --> "B::Foo()"

a = new B();
a.Foo(); // output --> "A::Foo()"
}
}
}

Combining Method Overriding and Hiding

Methods of a derived class can both be virtual and at the same time hide the derived method. In order to declare such a method, both keywords virtual and new have to be used in the method declaration:

class A
{
public void Foo() {}
}

class B : A
{
public virtual new void Foo() {}
}

A class C can now declare a method Foo() that either overrides or hides Foo() from class B:

class C : B
{
public override void Foo() {}
// or
public new void Foo() {}
}

Conclusion

• C# is not Java.
• Only methods in base classes need not override or hide derived methods. All methods in derived classes require to be either defined as new or as override.
• Know what your doing and look out for compiler warnings.

Connection Pooling:

1. When is the connection pool created :

When a connection is opened for the first time a connection pool is created and the pool is determined by the exact match of the connection string in the connection. Each connection pool is associated with a distinct connection string. When a new connection is requested, if the connection string is not an exact match to an existing pool, a new pool is created.

2. When is the connection pool destroyed :

When process who created the connections is closed, the pool is destroyed. Like if you are working with windows applications, then as soon as you close your window application, the pool will be destroyed.

3. What happens when all the connections in the connection pool are consumed and a new connection request comes :

If the maximum pool size has been reached and no usable connection is available, the request is queued. The connection pooler satisfies these requests by reallocating connections as they are released back into the pool. Connections are released back into the pool when you call Close or Dispose on the Connection.

4. How can I enable connection pooling ?

For .Net applications it is enabled by default. Well, to make sure the same we can use the Pooling=true; in the connection string for the SQLConnection Object.

5. How can I disable connection pooling?

ADO.NET Data Providers automatically use connection pooling turned on. If you want to turn this functionality off:

In an SQLConnection object, Add this to the connection string:

Pooling=False; 

In An OLEDBConnection object, add this:

OLE DB Services=-4;

This way, the OLE DB data provider will mark your connection so that it does not participate in connection pooling.

• the ASP.NET page can either obtain information from server resources, such as from data that has been persisted to a database, or
• the ASP.NET page can obtain information from within the application.

Retrieving information from a resource outside the application will require more processing steps, and will therefore require more time and resources on the server than if the information can be obtained from within the application space.

If the information that will be sent to the browser has already been prepared by a previous request, the application will be able to retrieve that information faster if it has been stored in memory, somewhere along the request/response stream.

Known as caching, this technique can be used to temporarily store page output or application data either on the client or on the server, which can then be re-used to satisfy subsequent requests and thus avoid the overhead of re-creating the same information.

Caching is particularly suitable when you expect to return the same information in the same format for many different requests.

ASP.NET provides the following types of caching that can be used to build highly responsive Web applications:

• Output caching, which caches the dynamic response generated by a request.
• Fragment caching, which caches portions of a response generated by a request.
• Data caching, which allows developers to programmatically retain arbitrary data across requests.

<%@ OutputCache Duration = "#ofseconds" 
   Location = "Any | Client | Downstream | Server | None" 
   VaryByControl = "controlname" 
   VaryByCustom = "browser | customstring" 
   VaryByHeader = "headers" 
   VaryByParam = "parametername" %>

<%@ OutputCache Duration = "10" VaryByParam = "none" %>

<%@ OutputCache Duration = "10" VaryByParam = "myParam1; myParam2" %>

Visual Basic was Object-Based, Visual Basic .NET is Object-Oriented, which means that it's a true Object-Oriented Programming Language. Visual Basic .NET supports all the key OOP features like Polymorphism, Inheritance, Abstraction and Encapsulation. It's worth having a brief overview of OOP before starting OOP with VB.

Why Object Oriented approach?

A major factor in the invention of Object-Oriented approach is to remove some of the flaws encountered with the procedural approach. In OOP, data is treated as a critical element and does not allow it to flow freely. It bounds data closely to the functions that operate on it and protects it from accidental modification from outside functions. OOP allows decomposition of a problem into a number of entities called objects and then builds data and functions around these objects. A major advantage of OOP is code reusability.

Some important features of Object Oriented programming are as follows:

• Emphasis on data rather than procedure
  
• Programs are divided into Objects
  
• Data is hidden and cannot be accessed by external functions
  
• Objects can communicate with each other through functions
  
• New data and functions can be easily added whenever necessary
  
• Follows bottom-up approach

Concepts of OOP:

• Objects
  
• Classes
  
• Data Abstraction and Encapsulation
  
• Inheritance
  
• Polymorphism

Briefly on Concepts:

Objects

Objects are the basic run-time entities in an object-oriented software. Programming problem is analyzed in terms of objects and nature of communication between them. When a program is executed, objects interact with each other by sending messages. Different objects can also interact with each other without knowing the details of their data or code.

Classes

A class is a collection of objects of similar type. Once a class is defined, any number of objects can be created which belong to that class.

Data Abstraction and Encapsulation

Normally, in a Class, variables used to hold data (like the age of a dog) is declared as Private. Functions or property routines are used to access these variables. Protecting the data of an object from outside functions is called Abstraction or Data Hiding. This prevents accidental modification of data by functions outside the class.

Putting all the data and related functions in a Class is called Encapsulation. Data cannot be accessible to the outside world and only those functions which are stored in the class can access it.

Inheritance

Inheritance is the process by which objects can acquire the properties of objects of other class. In OOP, inheritance provides reusability, like, adding additional features to an existing class without modifying it. This is achieved by deriving a new class from the existing one. The new class will have combined features of both the classes.

Polymorphism

Polymorphism means the ability to take more than one form. An operation may exhibit different behaviors in different instances. The behavior depends on the data types used in the operation. Polymorphism is extensively used in implementing Inheritance.

Advantages of OOP

Object-Oriented Programming has the following advantages over conventional approaches:

• OOP provides a clear modular structure for programs which makes it good for defining abstract datatypes where implementation details are hidden and the unit has a clearly defined interface.
  
• OOP makes it easy to maintain and modify existing code as new objects can be created with small differences to existing ones.
  
• OOP provides a good framework for code libraries where supplied software components can be easily adapted and modified by the programmer. This is particularly useful for developing graphical user interfaces.

Reflection: GetType() & TypeOf()

 All .NET assemblies contain vivid type information which describes the structure of every internal and external type (this can be verified by loading an assembly into ildasm.exe and clicking the ctrl-m keystroke). On a related note, many methods in the .NET base class libraries require you to pass in type information for a given item. When you need to obtain type information for a given method invocation, you have numerous approaches. First, all types inherit the public System.Object.GetType() method:

 // Obtain type information from a variable.

SomeType c = new SomeType();

Type t = c.GetType();

INeedYourTypeInfo(t);While this works, it is overkill to create a type simply to call the inherited GetType() method. To streamline the process, the C# language offers the typeof operator. The benefit is you are not required to create the item in question, rather simply specify the name of the type as an argument:

 // Obtain type information via typeof.

INeedYourTypeInfo(typeof(SomeType));

Stored Procedure:

CREATE PROCEDURE DBO.GetUserName

@UseName varchar(50) =' ' output,

@UID int =1 

AS

SELECT @UseName= FNAME+' ' + LNAME FROM USERS WHERE USERID=@UID

return @@rowcount

GO

----

Calling a SP:

private string GetUserName(int UID)

    {  

   string strConn = ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;

        SqlConnection conn = new SqlConnection(strConn);

        conn.Open();

        SqlCommand cmd = new SqlCommand("GetUserName", conn);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@UseName", SqlDbType.VarChar,50);

        cmd.Parameters[0].Direction = ParameterDirection.Output;

        cmd.Parameters.AddWithValue("@UID",UID);

        cmd.ExecuteNonQuery();

        conn.Close();

        return cmd.Parameters[0].Value.ToString();

    }

Crystal Report

Schema(*.xsd)

<?xml version="1.0" encoding="utf-8"?>

<xs:schema id="CompanyDS" targetNamespace="http://tempuri.org/CompanyDS.xsd" elementFormDefault="qualified" xmlns="http://tempuri.org/CompanyDS.xsd" xmlns:mstns="http://tempuri.org/CompanyDS.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="Survey">

<xs:complexType>

<xs:sequence>

<xs:element name="SID" type="xs:int" />

<xs:element name="UID" type="xs:int" />

<xs:element name="UNAME" type="xs:string" />

<xs:element name="SNAME" type="xs:string" />

<xs:element name="DATE" type="xs:date" />

<xs:element name="ISACTIVE" type="xs:boolean" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>

----

<CR:CrystalReportViewer AutoDataBind="False" ID="CrystalReportViewer1" runat="server"

Height="50px" Width="350px" />

-----

using System.Data;

//--SqlClient for SqlConnection and etc.

using System.Data.SqlClient;

//--for CrystalReports's ReportDocument.

using CrystalDecisions.CrystalReports.Engine;

private void GenerateReport()

{

string strConn = ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;

//--Sql string

String strCmd = "SELECT S.SURVEY_ID [SID],S.USER_ID [UID],U.FNAME+' ' +U.LNAME [UNAME],S.SURVEY_NAME [SNAME],S.CREATED_DATE [DATE],S.ACTIVE [ISACTIVE] FROM SURVEYS S,USERS U WHERE S.USER_ID=U.USERID";

//--Opening Sql Connection

//string strConn = ConfigurationManager.AppSettings["Conn"];

SqlConnection sqlConn = new SqlConnection(strConn);

DataSet ds = new DataSet();

SqlDataAdapter da = new SqlDataAdapter(strCmd, sqlConn);

//--this statement is very important, here the table name //should match with the XML Schema table name

da.Fill(ds, "Survey");

//--Closing Sql Connection

sqlConn.Close();

//--(Optional) I have used it to disable the properties

CrystalReportViewer1.DisplayGroupTree = false;

CrystalReportViewer1.HasCrystalLogo = false;

//--Initializing CrystalReport

ReportDocument myReportDocument;

myReportDocument = new ReportDocument();

myReportDocument.Load(Server.MapPath("MyCompanyRpt.rpt"));

myReportDocument.SetDataSource(ds);

//--Binding report with CrystalReportViewer

CrystalReportViewer1.ReportSource = myReportDocument;

CrystalReportViewer1.DataBind();

}

-----------------

SELECT * FROM EMPLOYEE, LEAVE

è If EMPLOYEE contains 5 records & Leave contains 3 records then the query returns 5X3=15 records.

è If EMPLOYEE contains 5 records & Leave contains 0 records then the query returns 5X0=0 records.

Select * from employee where (substring (name, 1, 1) in ('v')) and (substring (name,len(name),1) in ('h'))

è It fetches all emps whose name starts with ‘v’ and ends with ‘h’

SELECT DEPT.NAME, COUNT (*) FROM employee,DEPT WHERE employee.DEPTID=DEPT.ID GROUP BY DEPT.NAME HAVING COUNT(*)>1

-----------------

JOIN

Join is to query data from more than one table. Normally, there are five different joining schemes.

1. Cartesian product. If no joining condition is specified, every row in the first table with be matched to the every row of the second tables and so on for the second table and the third joining table. This should be avoided.

2. Equal-Join. Tables are joined based on some column equality condition.

3. Non-equal Join. Tables are joined based on some column inequality condition.

4. Outer-Join. Tables are joined and rows that do NOT meet the join condition are also returned.

5. Self-Join. Joining a table with itself.

The schemes 2 to 4 are described in details in the following.

Equal-Join

Use table prefix to qualify column names to remove column ambiguity.

For example,

SQL> SELECT empno, employee.name, employee.dept_no, dept.name

FROM employee, dept

WHERE employee.dept_no = dept.dept_no;

Non-equal-Join

Use Between-And clause in non-equal join queries.

For example, to answer the query "find the students whose mark falls in Grade A range" (assuming the student mark table only stores the raw marks (with values ranging from 0 from 100, and the grade_class table stores the low-end and high-end marks for each grade.), the query is:

SQL> SELECT a.name, a.matric_no, a.mark

FROM student_marks a, grade_class b

WHERE b.grade = 'A' AND

a.mark BETWEEN b.low_end AND b.high_end;

Outer-Join

Outer-join operator is the plus sign (+). Out join queries return the normal joining results plus the rows in two tables that do not meet the join condition.

For example, assume we have a module_taken table which keeps the relationship on what modules taken by which student, and a module table which keeps the module details.

The following statement returns the student matric no, and module names taken that student, and in addition to this, it also list the modules which are not taken by anyone.

SQL> SELECT a.matric_no, a.module_no, b.module_name

FROM module_taken a, module b

WHERE a.module_no (+) = b.module_no;

Assume module CS9999 is not taken by any student but is listed in the module table, then this module will also appear in the result, with empty value for matric_no.

Self-Join

To join the table with itself, a table must be given an alias to distinguish each other.

For example, the following statement retrieves all employees who have a manager (who don't have a boss :)).

SQL> SELECT worker.name || ' works for ' || mgr.name

FROM employee worker, employee mgr

WHERE worker.mgr_no = mgr.empno;