OOPS - Object Oriented Programming Languages & Systems.
Everything in the world is an object. The type of the
object may vary. In OOPS, we get the power to create
objects of our own, as & when required.
Class - A class is an organized store-house in
object-oriented programming that gives coherent
functional abilities to a group of related code. It is
the definition of an object, made up of software code.
Using classes, we may wrap data and behaviour together
(Encapsulation). We may define classes in terms of
classes (Inheritance). We can also override the
behaviour of a class using an alternate behaviour
(Polymorphism).
Using inhertance, we may assign different traits to different
classes. Yet, the child classes will inherit some common
traits from the base class. As in the figure above, the
classes for "Permanent" and "Wage Worker" will inherit
some common traits from the class of "Employee".
A class may contain class members like fields, properties,
events & methods with different types of access modifiers
like private, public, protected or friend, to process the
way they are accessed. In VB.NET, a class may be declared
as below...
Public Class SampleClass
'define class members
End Class
Key Concepts of .NET - To work with classes and modules,
the key concepts to know are definition, access, inheritance,
constructors, destructors, delegates, abstract classes & interfaces..
Class members - The different types of entities in a class,
like fields, properties, events & methods.
Object - An object is an instance of a class. This instance
may be used to access the members of the class. It is pretty
easy to define an object. See sample code below...
Dim objSampleObject as SampleClass
Structure - It is a bit similar to a class. Semantically,
structures are known as value types, while classes as reference types. We do'nt instantiate an object using the New keyword while working with a structure. We can not inherit from a structure.
Public Structure Student
Public RollNo as Integer
Public Name as String
End Structure
Dim objStud as Student
objStud.RollNo=31
objStud.Name="Monu"
Here, note that the object objStud is not exactly an instance,
it is a simple object (object of a structure) that is used to
access the members of this structure. It would have behaved
differently had it been an object of a class, as it would have
invoked the constructor associated with the class.
Public Class ClassCalc
Public Function FnAdd(ByVal dblA as double, ByVal dblB _
as double) as Double
FnAdd = dblA + dblB
End Function
End Class
Now, lets make use of the method FnAdd defined in the class
above. To use it, first create an object of this class, say
objX. Using this object, we can invoke the methods of this
class. See code below...
Dim objX as ClassCalc
Dim dblResult as Double
dblResult = objX.FnAdd(4.56,2.35)
Property - A property is a thing that describes the features of an object. A property is a piece of data contained within a class that has an exposed interface for reading/writing. Looking at that definition, you might think you could declare a public variable in a class and call it a property. While this assumption is somewhat valid, the true technical term for a public variable in a class is a field. The key difference between a field and a property is in the inclusion of an interface.
We make use of Get and Set keywords while working with
properties. We prefix the variables used within this code block with an underscore. Value is a keyword, that holds the value
which is being retrieved or set.
Private _Color As String
Public Property Color()
Get
Return _Color
End Get
Set(ByVal Value)
_Color = Value
End Set
End Property
Event - An action that an object does. When something happens, we say an event has happened. For example, when a button is clicked, we say it is the click( ) event. When a mouse hovers on an image, we say the mouseover( ) event has taken place.
Access Modifiers - Keywords used to vary the way members of a class are used. Following are different types...
1) Public - These classes can be used anywhere in the code.
There are no restrictions.
Available only to code outside our class
2) Private - These classes are accessible only within their
declaration contexts. This includes nested procedures. When a variable is declared Public inside a Private class, it is
accessible only from inside that class.
Available only to code inside our class
3) Protected - These classes extend the accessibility of their
members to their child classes (means, to the classes that derive from them). They extend their members to both themselves & their child classes.
Available only to classes that inherit from our class
4) Friend - Friend access means that elements are accessible only within the program. Friend is the default access modifer for any class that does not have a modifier.
Available only to code within our project/component
5) Protected Friend - Available only to classes that inherit from our class (in any project) or to code within our project/component. This is a combination of Protected and Friend.
Default - A Default property is a single property of a class that can be set as the default. This allows developers that use your class to work more easily with your default property because they do not need to make a direct reference to the property. Default properties cannot be initialized as Shared or Private and all must be accepted at least on argument or parameter. Default properties do not promote good code readability, so use this option sparingly.
Overloads - The Overloads property allows a function to be
described using deferent combinations of parameters. Each
combination is considered a signature, thereby uniquely defining an instance of the method being defined. You can define a function with multiple signatures without using the keyword Overloads, but if you use the Overloads keyword in one, you must use it in all of the function's Overloaded signatures.
Shared -The Shared keyword is used in an inherited or base class to define a property or method as being shared among all instances of a given class. If multiple instances of a class with shared properties or methods are loaded, the shared properties or methods will provide the same data across each instance of the class. When one class alters the value for a shared property, all instances of that class will reflect the change. Shared properties of all instances of the class
point to the same memory location.
Overridable -The Overridable keyword is used when defining a property or method of an inherited class, as overridable by the inheriting class.
Overides - The Overides keyword allows the inheriting class to disregard the property or method of the inherited class and implements ts own code.
NotOverridable - The NotOverridable keyword explicitly declares a property or method as not overridable by an inheriting class, and all properties are "not overridable" by default. The only real advantage to using this keyword is to make your code more readable.
MustOverride - The MustOverride keyword forces the inheriting class to implement its own code for the property or method.
Shadows - The Shadows keyword will effectively hide all of the other methods in the baseclass. It is like forcefully getting rid of the overloading that has been done on the methods of the base class.
The Shadows keyword works like the Overloads keyword except that with shadows we do not have to follow rules such as implementing the same signature. The Shadows keyword does not require the consent (override ability) of the inherited class to replace the property or method's implementation code. A method does not have to be defined as overridable for the Shadows keyword to work. Read the example...
'This is the Base Class
Public Class Parent
Public Sub MyProc(ByVal num As Integer)
MsgBox("Number in Parent is " & num)
End Sub
Public Sub MyProc(ByVal st As String)
MsgBox("String in Parent is " & st)
End Sub
End Class
'This is the Child Class
Public Class Child
Inherits Parent
Overloads Sub MyProc(ByVal num As Integer)
'overloads the method with the same parameter list
MsgBox("Number in Child is " & num)
End Sub
Overloads Sub MyProc(ByVal ch As Char)
' overloads the method
MsgBox("Character in Child is " & ch)
End Sub
End Class
When we execute the following code...
Dim c As New Child()
' prints out "String in Parent is Hello Wazzup!"
c.MyProc("Hello Wazzup!")
' prints out "Number in Child is 12"
c.MyProc(12)
' prints out "Character in DerivedClass is B"
c.MyProc(Chr(66))
When we use Shadows keyword...
Public Class ChildNumber2
Inherits Parent
Shadows Sub MyProc(ByVal num As Integer)
' hides all the different argument list
MsgBox("Number in ChildNumber2 is " & num)
End Sub
End Class
Dim c2 As New DerivedClass2()
c2.MyProc(7) 'only one method is exposed, rest of the
'methods are hidden
Constructor - When a class instance is created in our code,
a special method of that class, called the constructor, is called. Similarly, when the class is destroyed, the destructor
method is called. These are general terms and usually not the actual member names in most object-oriented languages. It is initialized using the keyword New, and is destroyed using the keyword Finalize. In .NET, we tend to forget using Finalize as
the instances(means the object) are automatically destroyed by the Garbage Collecter, when the object is not in use by he CLR(Common Language Runtime).
Dim objSampleObject as New SampleClass
' write the code here...
objsampleobject.Finalize
We can add parameters to the constructors. This was'nt allowed in VB6. We can overload the constructors, change the order of parameters, datatypes of parameters which ultimately change the way the constructor works whenever an instance of that class is invoked.
Also note that a constructor can have any access modifier. If
no argument is added in a constructor, VB.NET adds a no-argument constructor during compile time. It adds the Public Sub New( ) with the class declared, if no argument is passed in the constructor. The following code is added...
Public Class ClassA
Public Sub New( )
End Sub
However, when the constructor is added with parameters, the following code is generated...
Public Class ClassA
Public Sub New(ByVal SomeString as String )
End Sub
When a child class' object is declared, the child class
constructor & its parent class constructor, both are invoked.
Read example below for more clarity...
Public Class Books
Public Sub New()
System.Console.WriteLine("Book's constructor.")
End Sub
Public Sub myProc()
System.Console.WriteLine("This is a book.")
End Sub
End Class
Public Class Authors : Inherits Books
Public Sub New()
System.Console.WriteLine("Author's constructor.")
End Sub
End Class
When the Authors class' constructor is invoked, like in the
following code, the Books class' no-argument constructor is also called.
Dim author As Authors
author = New Authors()
The result on the console will be...
Book's constructor.
Author's constructor.
If the base class does'nt have a no-argument constructor, then it would result in a compiler error. Hence, we need to use the MyBase keyword with the constructor. Our child class will look like this...
Public Class Authors : Inherits Books
Public Sub New(ByVal SomeString As String)
MyBase.New(SomeString)
System.Console.WriteLine("Authors's constructor.")
End Sub
End Class
If a class is not inheriting from any base class, then it will
call the base class constructor of System.Object if we are using MyBase.New( ). Summarizing constructors, whenever we initiate a constructor, the following things happen...
Base class constructor is invoked.
Class level variables are initialized.
Code in the class constructor gets executed.
If the argument name passed in the constructor, is same as the variable name used in the constructor, we use the Me keyword to refer to the constructor variable. For example if the variable name is SomeString, and the parameter passed is also SomeString, then the variable is referred as Me.SomeString.
Abstract Class - They are classes that cannot be instantiated. We cannot create an object from such a class for use in our program. We can use an abstract class as a base class, creating new classes that will inherit from it. Creating an abstract class with a certain minimum required level of functionality gives us a defined starting point from which we can derive non-abstract classes.
An abstract class may contain abstract methods & non-abstract methods. When a class is derived from an abstract class, the derived class must implement all the abstract methods declared in the base class. We may use accessibility modifiers in an abstract class (unlike in Interfaces).
An abstract class can inherit from a non-abstract class. In C++, this concept is known as pure virtual method.
Interface - its a kind of class, that has only methods, do not have code, just the definition of the methods. Also, the interface can't be instantiated. Its an abstract class with public abstract methods, all of which must be implemented in the inherited classes. All methods in an interface are public, no other access modifier is used. It is public by default.
Classes can contain code, but interface dont. However, classes that implement an interface do contain code. Keep in mind that there are no instances of interfaces in VB .NET. Every instance is a type that implements an interface, but is itself not an instance of the interface. Also note, in an interface, all methods must be abstract (which is not necessary in an abstract class).
'VB .NET Interface
Public Interface ISalary
Sub CreditSalary(ByVal Amount As Decimal)
ReadOnly Property Incentive() As Decimal
ReadOnly Property Attendance() As Integer
End Interface
To use members of an interface, we make use of the implements keyword.
Public Class Employee
Implements ISalary
Public Function Salary() As Decimal Implements ISalary.CreditSalary()
'code here ...
End Function
End Class
Serializable - This is a class attribute. When we use this attribute with a class, an instance of this class can be taken in whatever state it is, and write it to a disk. The class can then be deserialized, and the class will act as if it is simply stored in the memory.
Boxing & Unboxing - Value Types are stored on the stack and Reference types are stored on the heap. The conversion of value type to reference type is known as Boxing. Converting reference type back to value type
is known as Unboxing.
Value Types - Value types are primitive types that are mapped directly to the FCL. Like Int32 maps to System.Int32, double maps to System.double.
All value types are stored on stack and all the value types are derived from System.ValueType. All structures and enumerated types that are derived from System.ValueType are created on stack, hence known as ValueType.
Reference Types - Reference Types are different from value types in such a way that memory is allocated to them from the heap. All the classes are of reference type. C# new operator returns the memory address of the
object.
Partial Class - This concept has been introduced in .NET framework 2.0. They give you the ability to split a single class into more than one source code (.cs or .vb) file. Here's what a partial class looks like when it's split over two files...
// Stored in file MyClass1.cs
public partial class MyClass
{
public MethodA()
{...}
}
// Stored in file MyClass2.cs
public partial class MyClass
{
public MethodB()
{...}
}
When you build the application, Visual Studio .NET tracks down each piece of MyClass and assembles it into a complete, compiled class with two methods, MethodA() and MethodB().
Partial classes don't offer much in the way of solving programming problems, but they can be useful if you have extremely large, unwieldy classes. (Of course, this might be a sign that you haven't properly factored your problem, in which case you should really break your class down into separate classes.) The real purpose of partial classes in .NET is to hide automatically generated designer code.
Saturday, April 17, 2010
SQL 2005 Strings
SQL Native Client ODBC Driver
Standard security:
"Driver={SQL Native Client};
Server=MyServerName;
Database=pubs;
UID=myUsername;
PWD=myPassword;"
Trusted connection:
"Driver={SQL Native Client};
Server=MyServerName;
Database=pubs;
Trusted_Connection=yes;"
Note: Integrated Security=SSPI equals
Trusted_Connection=yes
Prompt for username and password:
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Native Client};
Server=Aron1;DataBase=pubs;"
Enabling MARS (multiple active result sets):
"Driver={SQL Native Client};
Server=UrServerName;
Database=pubs;
Trusted_Connection=yes;
MARS_Connection=yes"
Note: MultipleActiveResultSets=true equals
MARS_Connection=yes
Encrypt data sent over network:
"Driver={SQL Native Client};
Server=UrServerName;
Database=pubs;
Trusted_Connection=yes;
Encrypt=yes"
Attach a database file on connect to a local
SQL Server Express instance:
"Driver={SQL Native Client};Server=.\SQLExpress;
AttachDbFilename=c:\asd\qwe\mydbfile.mdf;
Database=dbname;Trusted_Connection=Yes;"
OR
"Driver={SQL Native Client};
Server=.\SQLExpress;
AttachDbFilename=|DataDirectory|mydbfile.mdf;
Database=dbname;Trusted_Connection=Yes;"
(Note: use |DataDirectory| when your database file
resides in the data directory)
SQL Native Client OLE DB Provider
Standard security:
"Provider=SQLNCLI;
Server=UrServerName;
Database=pubs;
UID=myUsername;PWD=myPassword;"
Trusted connection:
"Provider=SQLNCLI;
Server=UrServerName;
Database=pubs;
Trusted_Connection=yes;"
Note: Integrated Security=SSPI equals
Trusted_Connection=yes
Prompt for username and password:
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Provider=SQLNCLI;Server=Aron1;DataBase=pubs;"
Enabling MARS (multiple active result sets):
"Provider=SQLNCLI;
Server=UrServerName;
Database=pubs;
Trusted_Connection=yes;
MarsConn=yes"
Note: MarsConn=yes equals
MultipleActiveResultSets=true equals
MARS_Connection=yes
Encrypt data sent over network:
"Provider=SQLNCLI;
Server=UrServerName;
Database=pubs;
Trusted_Connection=yes;
Encrypt=yes"
Attach a database file on connect to a local SQL
Server Express instance:
"Provider=SQLNCLI;
Server=.\SQLExpress;
AttachDbFilename=c:\asd\qwe\mydbfile.mdf;
Database=dbname;
Trusted_Connection=Yes;"
OR
"Provider=SQLNCLI;
Server=.\SQLExpress;
AttachDbFilename=|DataDirectory|mydbfile.mdf;
Database=dbname;
Trusted_Connection=Yes;"
(Note: use |DataDirectory| when your database file
resides in the data directory)
SqlConnection (.NET)
Standard Security:
"Data Source=UrServerName;
Initial Catalog=pubs;
User Id=myUsername;
Password=myPassword;"
OR
"Server=UrServerName;
Database=pubs;
User ID=myUsername;
Password=myPassword;
Trusted_Connection=False"
Trusted Connection:
"Data Source=UrServerName;
Initial Catalog=pubs;
Integrated Security=SSPI;"
OR
"Server=UrServerName;
Database=pubs;
Trusted_Connection=True;"
(Note: use serverName\instanceName as Data Source to use
an specifik SQLServer instance)
Connect via an IP address:
"Data Source=190.190.200.100,1433;
Network Library=DBMSSOCN;
Initial Catalog=pubs;
User ID=myUsername;
Password=myPassword;"
(Note: DBMSSOCN=TCP/IP instead of Named Pipes,
at the end of the Data Source is the port to use (1433
is the default))
Enabling MARS (multiple active result sets):
"Server=UrServerName;
Database=pubs;
Trusted_Connection=True;
MultipleActiveResultSets=true"
Note- Use ADO.NET 2.0 for MARS functionality. MARS is not
supported in ADO.NET 1.0 nor ADO.NET 1.1
Attach a database file on connect to a local SQL Server
Express instance:
"Server=.\SQLExpress;
AttachDbFilename=c:\asd\qwe\mydbfile.mdf;
Database=dbname;
Trusted_Connection=Yes;"
OR
"Server=.\SQLExpress;
AttachDbFilename=|DataDirectory|mydbfile.mdf;
Database=dbname;
Trusted_Connection=Yes;"
(Note: use |DataDirectory| when your database file resides
in the data directory)
Using "User Instance" on a local SQL Server Express
instance:
"Data Source=.\SQLExpress;
integrated security=true;
attachdbfilename=|DataDirectory|\mydb.mdf;user instance=true;"
Note: The "User Instance" functionality creates a new SQL
Server instance on the fly during connect. This works
only on a local SQL
Server 2005 instance and only when connecting using windows
authentication over local named pipes. The purpose is to
be able to create a full rights SQL Server instance to
a user with limited administrative rights on the computer.
To enable the functionality: sp_configure
'user instances enabled','1' (0 to disable)
Using SQL Server 2005 Express? Don't miss the server name
syntax: SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of the computer)
Context Connection - connecting to "self" from within your CLR stored prodedure/function
C#:
using(SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
// Use the connection
}
Visual Basic:
Using connection as new SqlConnection("context connection=true")
connection.Open()
' Use the connection
End Using
The context connection lets you execute Transact-SQL statements
in the same context (connection) that your code was invoked in the first place.
Standard security:
"Driver={SQL Native Client};
Server=MyServerName;
Database=pubs;
UID=myUsername;
PWD=myPassword;"
Trusted connection:
"Driver={SQL Native Client};
Server=MyServerName;
Database=pubs;
Trusted_Connection=yes;"
Note: Integrated Security=SSPI equals
Trusted_Connection=yes
Prompt for username and password:
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Native Client};
Server=Aron1;DataBase=pubs;"
Enabling MARS (multiple active result sets):
"Driver={SQL Native Client};
Server=UrServerName;
Database=pubs;
Trusted_Connection=yes;
MARS_Connection=yes"
Note: MultipleActiveResultSets=true equals
MARS_Connection=yes
Encrypt data sent over network:
"Driver={SQL Native Client};
Server=UrServerName;
Database=pubs;
Trusted_Connection=yes;
Encrypt=yes"
Attach a database file on connect to a local
SQL Server Express instance:
"Driver={SQL Native Client};Server=.\SQLExpress;
AttachDbFilename=c:\asd\qwe\mydbfile.mdf;
Database=dbname;Trusted_Connection=Yes;"
OR
"Driver={SQL Native Client};
Server=.\SQLExpress;
AttachDbFilename=|DataDirectory|mydbfile.mdf;
Database=dbname;Trusted_Connection=Yes;"
(Note: use |DataDirectory| when your database file
resides in the data directory)
SQL Native Client OLE DB Provider
Standard security:
"Provider=SQLNCLI;
Server=UrServerName;
Database=pubs;
UID=myUsername;PWD=myPassword;"
Trusted connection:
"Provider=SQLNCLI;
Server=UrServerName;
Database=pubs;
Trusted_Connection=yes;"
Note: Integrated Security=SSPI equals
Trusted_Connection=yes
Prompt for username and password:
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Provider=SQLNCLI;Server=Aron1;DataBase=pubs;"
Enabling MARS (multiple active result sets):
"Provider=SQLNCLI;
Server=UrServerName;
Database=pubs;
Trusted_Connection=yes;
MarsConn=yes"
Note: MarsConn=yes equals
MultipleActiveResultSets=true equals
MARS_Connection=yes
Encrypt data sent over network:
"Provider=SQLNCLI;
Server=UrServerName;
Database=pubs;
Trusted_Connection=yes;
Encrypt=yes"
Attach a database file on connect to a local SQL
Server Express instance:
"Provider=SQLNCLI;
Server=.\SQLExpress;
AttachDbFilename=c:\asd\qwe\mydbfile.mdf;
Database=dbname;
Trusted_Connection=Yes;"
OR
"Provider=SQLNCLI;
Server=.\SQLExpress;
AttachDbFilename=|DataDirectory|mydbfile.mdf;
Database=dbname;
Trusted_Connection=Yes;"
(Note: use |DataDirectory| when your database file
resides in the data directory)
SqlConnection (.NET)
Standard Security:
"Data Source=UrServerName;
Initial Catalog=pubs;
User Id=myUsername;
Password=myPassword;"
OR
"Server=UrServerName;
Database=pubs;
User ID=myUsername;
Password=myPassword;
Trusted_Connection=False"
Trusted Connection:
"Data Source=UrServerName;
Initial Catalog=pubs;
Integrated Security=SSPI;"
OR
"Server=UrServerName;
Database=pubs;
Trusted_Connection=True;"
(Note: use serverName\instanceName as Data Source to use
an specifik SQLServer instance)
Connect via an IP address:
"Data Source=190.190.200.100,1433;
Network Library=DBMSSOCN;
Initial Catalog=pubs;
User ID=myUsername;
Password=myPassword;"
(Note: DBMSSOCN=TCP/IP instead of Named Pipes,
at the end of the Data Source is the port to use (1433
is the default))
Enabling MARS (multiple active result sets):
"Server=UrServerName;
Database=pubs;
Trusted_Connection=True;
MultipleActiveResultSets=true"
Note- Use ADO.NET 2.0 for MARS functionality. MARS is not
supported in ADO.NET 1.0 nor ADO.NET 1.1
Attach a database file on connect to a local SQL Server
Express instance:
"Server=.\SQLExpress;
AttachDbFilename=c:\asd\qwe\mydbfile.mdf;
Database=dbname;
Trusted_Connection=Yes;"
OR
"Server=.\SQLExpress;
AttachDbFilename=|DataDirectory|mydbfile.mdf;
Database=dbname;
Trusted_Connection=Yes;"
(Note: use |DataDirectory| when your database file resides
in the data directory)
Using "User Instance" on a local SQL Server Express
instance:
"Data Source=.\SQLExpress;
integrated security=true;
attachdbfilename=|DataDirectory|\mydb.mdf;user instance=true;"
Note: The "User Instance" functionality creates a new SQL
Server instance on the fly during connect. This works
only on a local SQL
Server 2005 instance and only when connecting using windows
authentication over local named pipes. The purpose is to
be able to create a full rights SQL Server instance to
a user with limited administrative rights on the computer.
To enable the functionality: sp_configure
'user instances enabled','1' (0 to disable)
Using SQL Server 2005 Express? Don't miss the server name
syntax: SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of the computer)
Context Connection - connecting to "self" from within your CLR stored prodedure/function
C#:
using(SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
// Use the connection
}
Visual Basic:
Using connection as new SqlConnection("context connection=true")
connection.Open()
' Use the connection
End Using
The context connection lets you execute Transact-SQL statements
in the same context (connection) that your code was invoked in the first place.
Saturday, April 10, 2010
Constructor vs Static Constructor
A Constructor is usually used to initialize data. However Static Constructor is used to initialize only static members. Here I am just talking about the Constructors. How they get initialized and how they behave.
Things to know about Static Constructor
1. It is used to initialize static data members.
2. Can't access anything but static members.
3. Can't have parameters
4. Can't have access modifiers like Public, Private or Protected.
Now once you understand the above points, you can appreciate the difference between Static Class and Unstatic Class
1. Static Class cannot be instantiated unlike the unstatic class. You should directly access its Method via the ClassName.MethodName
2. A Program can't tell when it is going to load static class but its definitely loaded before the call.
3. A Static class will always have the static constructor and its called only once since after that its in the memory for its lifetime.
4. A Static class can contain only static members. So all the members and functions have to be static.
5. A Static class is always sealed since it cannot be inherited further. Further they cannot inherit form any other class (except Object)
Let's look at a normal Constructor
class Program
{
static void Main(string[] args)
{
/* Note two things
1.Here you get to instantiate the Constructor in the code wherever you like.
2.If you debug you get to goto the Constructor and see what is being done.
*/
MyExample myExample = new MyExample();
myExample.Print();
}
}
public class MyExample
{
private static int StaticVariable ;
public MyExample()
{
if (StaticVariable < 10)
{
StaticVariable = 20;
}
else
{
StaticVariable = 100;
}
}
public void Print()
{
Console.WriteLine(StaticVariable);
}
}
Now consider this second example for static class
class Program
{
static void Main(string[] args)
{
/* Note the following
1.You dont get to instantiate for sure so you dont know when the constructor was called.
2.Secondly you can access your method directly.
*/
//MyExampleStatic myExampleStatic = new MyExampleStatic();
MyExampleStatic.Print();
}
}
static class MyExampleStatic
{
private static int StaticVariable;
static MyExampleStatic()
{
if (StaticVariable < 10)
{
StaticVariable = 20;
}
else
{
StaticVariable = 100;
}
}
public static void Print()
{
Console.WriteLine(StaticVariable);
}
}
The point is that static member could be used only by a static Constructor or static Function. Hope you have a Static Constructing life.
Things to know about Static Constructor
1. It is used to initialize static data members.
2. Can't access anything but static members.
3. Can't have parameters
4. Can't have access modifiers like Public, Private or Protected.
Now once you understand the above points, you can appreciate the difference between Static Class and Unstatic Class
1. Static Class cannot be instantiated unlike the unstatic class. You should directly access its Method via the ClassName.MethodName
2. A Program can't tell when it is going to load static class but its definitely loaded before the call.
3. A Static class will always have the static constructor and its called only once since after that its in the memory for its lifetime.
4. A Static class can contain only static members. So all the members and functions have to be static.
5. A Static class is always sealed since it cannot be inherited further. Further they cannot inherit form any other class (except Object)
Let's look at a normal Constructor
class Program
{
static void Main(string[] args)
{
/* Note two things
1.Here you get to instantiate the Constructor in the code wherever you like.
2.If you debug you get to goto the Constructor and see what is being done.
*/
MyExample myExample = new MyExample();
myExample.Print();
}
}
public class MyExample
{
private static int StaticVariable ;
public MyExample()
{
if (StaticVariable < 10)
{
StaticVariable = 20;
}
else
{
StaticVariable = 100;
}
}
public void Print()
{
Console.WriteLine(StaticVariable);
}
}
Now consider this second example for static class
class Program
{
static void Main(string[] args)
{
/* Note the following
1.You dont get to instantiate for sure so you dont know when the constructor was called.
2.Secondly you can access your method directly.
*/
//MyExampleStatic myExampleStatic = new MyExampleStatic();
MyExampleStatic.Print();
}
}
static class MyExampleStatic
{
private static int StaticVariable;
static MyExampleStatic()
{
if (StaticVariable < 10)
{
StaticVariable = 20;
}
else
{
StaticVariable = 100;
}
}
public static void Print()
{
Console.WriteLine(StaticVariable);
}
}
The point is that static member could be used only by a static Constructor or static Function. Hope you have a Static Constructing life.
RETRIEVES CURRENT DEFAULT PRINTER AND SET DEFAULT PRINTER CHANGE PRROGRAMMATICALLY AND INSTALLPRINTERLIST(COMBOBOX)
variable declaration
DIM PrinterCheckflag AS Boolean
dim mvarGlobalErrorMsg as string
'Retrieves current default Printer
Dim currentDefault As String
currentDefault = p.PrinterSettings.PrinterName
msgbox(currentdefault)
Dim pkInstalledPrinters As String
' Find all printers installed
For Each pkInstalledPrinters In _
PrinterSettings.InstalledPrinters
cboInstalledPrinters.Items.Add(pkInstalledPrinters)
Next pkInstalledPrinters
' Set the combo to the first printer in the list
If cboInstalledPrinters.Items.Count > 0 Then
cboInstalledPrinters.SelectedIndex = 0
End If
Private Function SetDefaultPrinter(ByVal Printername As String) ' Set the default printer
Dim DefaultPrinter As String
Dim regKey As RegistryKey
Dim Sys_var As String
Dim index As Integer
Try
regKey = Microsoft.Win32.Registry.CurrentUser.OpenSubKey("Software\Microsoft\Windows NT\CurrentVersion\Windows", True)
Sys_var = regKey.GetValue("Device")
DefaultPrinter = Sys_var
index = Sys_var.IndexOf(",")
Sys_var = Sys_var.Remove(0, index)
Sys_var = Printername & Sys_var
regKey.SetValue("Device", Sys_var)
'Sys_var = regKey.GetValue("Device")
'regKey.Close()
PrinterCheckflag = True ' Success in setting default printer
Catch ex As Exception
regKey.SetValue("Device", DefaultPrinter)
PrinterCheckflag = False ' Error in Setting Default printer
mvarGlobalErrorMsg = Printername & " : Failed to set as default Printer."
Finally
regKey.Close()
Sys_var = String.Empty
index = Nothing
End Try
End Function
DIM PrinterCheckflag AS Boolean
dim mvarGlobalErrorMsg as string
'Retrieves current default Printer
Dim currentDefault As String
currentDefault = p.PrinterSettings.PrinterName
msgbox(currentdefault)
Dim pkInstalledPrinters As String
' Find all printers installed
For Each pkInstalledPrinters In _
PrinterSettings.InstalledPrinters
cboInstalledPrinters.Items.Add(pkInstalledPrinters)
Next pkInstalledPrinters
' Set the combo to the first printer in the list
If cboInstalledPrinters.Items.Count > 0 Then
cboInstalledPrinters.SelectedIndex = 0
End If
Private Function SetDefaultPrinter(ByVal Printername As String) ' Set the default printer
Dim DefaultPrinter As String
Dim regKey As RegistryKey
Dim Sys_var As String
Dim index As Integer
Try
regKey = Microsoft.Win32.Registry.CurrentUser.OpenSubKey("Software\Microsoft\Windows NT\CurrentVersion\Windows", True)
Sys_var = regKey.GetValue("Device")
DefaultPrinter = Sys_var
index = Sys_var.IndexOf(",")
Sys_var = Sys_var.Remove(0, index)
Sys_var = Printername & Sys_var
regKey.SetValue("Device", Sys_var)
'Sys_var = regKey.GetValue("Device")
'regKey.Close()
PrinterCheckflag = True ' Success in setting default printer
Catch ex As Exception
regKey.SetValue("Device", DefaultPrinter)
PrinterCheckflag = False ' Error in Setting Default printer
mvarGlobalErrorMsg = Printername & " : Failed to set as default Printer."
Finally
regKey.Close()
Sys_var = String.Empty
index = Nothing
End Try
End Function
Wednesday, April 7, 2010
OperationContract with required parameters(WITH other options description)
1.Why are all parameters in all of your services declared as optional when most of the times they really aren't?"
Now if you navigate to SvcFile.svc?xsd=xsd0 (if not that then xsd=xsd1 or xsd=xsd2), you can see the schema for this operation and the xs:element elements for all 3 parameters and the return value have minOccurs="0" attribute. This essentially makes these parameters optional in the WSDL, SOAP UI and proxy/client code generated using some java toolkit they are using. As is pretty obvious, we don't want the control to even enter the Transfer method if the SOAP (1.2/1.1) request comes in without any of those parameters.
2.At least for simple parameters, you can use an IParameterInspector to enforce that they are present (see below). This solution has two limitations: it doesn't change the WSDL (which will still note the parameters are optional), and it doesn't work for nullable types. To work for nullable types you'd actually need a new formatter (basically doing the deserialization of the different parameters yourself).
ans:
changing the WSDL to reflect the required nature of parameters is my primary goal.
ts good to see how we can validate that required parameters are actually present in a message at the infrastructure level. However, that is not really a problem for most services because when these required parameters are "validated" by the service, in almost all cases a value of 0 or null is invalid anyways.
In this case, sort of rewriting the WSDL for the service (which is easy to do, if not to maintain - you'll need to save the WSDL+XSD files generated for the service, edit them, and link to them via the ExternalMetadataLocation property of the ServiceMetadataBehavior), you're left with the solution listed in your previous blog post.
finally
I'm looking for a way to make method parameters required in an OperationContract without switching to MessageContract. For example, I want to be able to declare a method like the following:
public int Transfer([DataParameter(IsRequired=true)]int SourceAccount, [DataParameter(IsRequired=true)]int TargetAccount, [DataParameter(IsRequired=true)]double Amount);
and have System.ServiceModel.Description.
DataContractSerializerMessageContractExporter.ExportBody method not put minOccurs="0" in the WSDL for the operation.
Now if you navigate to SvcFile.svc?xsd=xsd0 (if not that then xsd=xsd1 or xsd=xsd2), you can see the schema for this operation and the xs:element elements for all 3 parameters and the return value have minOccurs="0" attribute. This essentially makes these parameters optional in the WSDL, SOAP UI and proxy/client code generated using some java toolkit they are using. As is pretty obvious, we don't want the control to even enter the Transfer method if the SOAP (1.2/1.1) request comes in without any of those parameters.
2.At least for simple parameters, you can use an IParameterInspector to enforce that they are present (see below). This solution has two limitations: it doesn't change the WSDL (which will still note the parameters are optional), and it doesn't work for nullable types. To work for nullable types you'd actually need a new formatter (basically doing the deserialization of the different parameters yourself).
ans:
changing the WSDL to reflect the required nature of parameters is my primary goal.
ts good to see how we can validate that required parameters are actually present in a message at the infrastructure level. However, that is not really a problem for most services because when these required parameters are "validated" by the service, in almost all cases a value of 0 or null is invalid anyways.
In this case, sort of rewriting the WSDL for the service (which is easy to do, if not to maintain - you'll need to save the WSDL+XSD files generated for the service, edit them, and link to them via the ExternalMetadataLocation property of the ServiceMetadataBehavior), you're left with the solution listed in your previous blog post.
finally
I'm looking for a way to make method parameters required in an OperationContract without switching to MessageContract. For example, I want to be able to declare a method like the following:
public int Transfer([DataParameter(IsRequired=true)]int SourceAccount, [DataParameter(IsRequired=true)]int TargetAccount, [DataParameter(IsRequired=true)]double Amount);
and have System.ServiceModel.Description.
DataContractSerializerMessageContractExporter.ExportBody method not put minOccurs="0" in the WSDL for the operation.
OperationContract with required parameters
OperationContract with required parameters
I'm looking for a way to make method parameters required in an OperationContract without switching to MessageContract. For example, I want to be able to declare a method like the following:
public int Transfer([DataParameter(IsRequired=true)]int SourceAccount, [DataParameter(IsRequired=true)]int TargetAccount, [DataParameter(IsRequired=true)]double Amount);
and have System.ServiceModel.Description.
DataContractSerializerMessageContractExporter.ExportBody method not put minOccurs="0" in the WSDL for the operation.
READ AND LEAVE IT(convert all services to MessageContracts)
My enhancement request is to provide a way to tag parameters in an OperationContract as required without switching all our services to MessageContracts.
I'm afraid manually editing the WSDLs and publishing them at different URL is not maintainable at all and is a lot more prone to errors. I would rather convert all services to MessageContracts but the fact is I'm not going to do that either for now. Right now, I'm thinking of investigating the possibility of inheriting from the System.ServiceModel.Description.DataContractSerializerMessageContractExporter class and overriding the ExportBody method and then plugging in that custom DCSMCE into the WCF pipeline. Unfortunately, while DataContractSerializerMessageContractImporter is public, DataContractSerializerMessageContractExporter is an internal class. So I will probably need to do hacky and evil things using reflection, which I would rather not have done either. :)
LINK:
http://social.msdn.microsoft.com/Forums/en-US/wcf/thread/e707ed20-c09c-4e26-927a-7c3071d74ed7/#46f3cc78-d70f-48b8-9947-2429e789a300
I'm looking for a way to make method parameters required in an OperationContract without switching to MessageContract. For example, I want to be able to declare a method like the following:
public int Transfer([DataParameter(IsRequired=true)]int SourceAccount, [DataParameter(IsRequired=true)]int TargetAccount, [DataParameter(IsRequired=true)]double Amount);
and have System.ServiceModel.Description.
DataContractSerializerMessageContractExporter.ExportBody method not put minOccurs="0" in the WSDL for the operation.
READ AND LEAVE IT(convert all services to MessageContracts)
My enhancement request is to provide a way to tag parameters in an OperationContract as required without switching all our services to MessageContracts.
I'm afraid manually editing the WSDLs and publishing them at different URL is not maintainable at all and is a lot more prone to errors. I would rather convert all services to MessageContracts but the fact is I'm not going to do that either for now. Right now, I'm thinking of investigating the possibility of inheriting from the System.ServiceModel.Description.DataContractSerializerMessageContractExporter class and overriding the ExportBody method and then plugging in that custom DCSMCE into the WCF pipeline. Unfortunately, while DataContractSerializerMessageContractImporter is public, DataContractSerializerMessageContractExporter is an internal class. So I will probably need to do hacky and evil things using reflection, which I would rather not have done either. :)
LINK:
http://social.msdn.microsoft.com/Forums/en-US/wcf/thread/e707ed20-c09c-4e26-927a-7c3071d74ed7/#46f3cc78-d70f-48b8-9947-2429e789a300
DIFFERENCE ServiceContract & DATACONTRACT
Contract Type Member Attributes Description
ServiceContract OperationContract Describes the operations a service can perform. Maps CLR types to WSDL.
DataContract DataMember Describes a data structure. Maps CLR types to XSD.
MessageContract MessageBody, MessageHeader Defines the structure of the message on the wire. Maps CLR types to SOAP messages.
Q:I'm not sure when I should use a message contract. In fact why would I use a message? After all my operation parameters get serialized into a soap envelope automatically.
So when would I use a message and when would I use a serilizable class?
2. A data contract describes the data that your service operations exchange. Data contracts describe only the data transferred from one side to the other; generally they do not describe the message that carries the data.A message contract would hold all the required information about the message payload itself ie: itself.
Use Message Contracts to implement SOAP Headers, etc.
3. Not wanting to use DataContract versioning and to keep a open Message type across service operations.
4.
I would think MessageContract is better suited for this purpose since it is specifically for custom defining
the Message Headers and Message Body.
Basically a WCF Message ( ie message contract NOT data contract ) will call the serializer once for each member in the body and add the result to the output message .
Now if you send a DataContract it will serialize the entire stream.
5.
My requirement is that I need to Pass in Additional Header information to the WCFservice, this I can do with Message Contracts but I need to maintain two set of Contracts one for the Request (which will contain Message Header + body) and another for Reply which will contain Body Only.
Alternatively if use DataContract I don't have option to pass the Headers, but this can be done in other means(by using Server and Client Message Inspectors).
ServiceContract OperationContract Describes the operations a service can perform. Maps CLR types to WSDL.
DataContract DataMember Describes a data structure. Maps CLR types to XSD.
MessageContract MessageBody, MessageHeader Defines the structure of the message on the wire. Maps CLR types to SOAP messages.
Q:I'm not sure when I should use a message contract. In fact why would I use a message? After all my operation parameters get serialized into a soap envelope automatically.
So when would I use a message and when would I use a serilizable class?
2. A data contract describes the data that your service operations exchange. Data contracts describe only the data transferred from one side to the other; generally they do not describe the message that carries the data.A message contract would hold all the required information about the message payload itself ie:
Use Message Contracts to implement SOAP Headers, etc.
3. Not wanting to use DataContract versioning and to keep a open Message type across service operations.
4.
I would think MessageContract is better suited for this purpose since it is specifically for custom defining
the Message Headers and Message Body.
Basically a WCF Message ( ie message contract NOT data contract ) will call the serializer once for each member in the body and add the result to the output message .
Now if you send a DataContract it will serialize the entire stream.
5.
My requirement is that I need to Pass in Additional Header information to the WCFservice, this I can do with Message Contracts but I need to maintain two set of Contracts one for the Request (which will contain Message Header + body) and another for Reply which will contain Body Only.
Alternatively if use DataContract I don't have option to pass the Headers, but this can be done in other means(by using Server and Client Message Inspectors).
Monday, April 5, 2010
MULTIPLE INTERFACES HAVING SAME METHOD NAME HOW TO IMPLEMENT AND CALLING
IN VB.NET
INTERFACE1
Public Interface Interface1
sub test()
End Interface
INTERFACE2
Public Interface Interface1
sub test()
End Interface
Class1
Public Class Class1
implements Interface1,Interface2
sub Interface1test ()implements Interface1.test
console.WriteLine("i1")
end sub
sub test() implements Interface2.test
console.WriteLine("i2")
end sub
End Class
WINDOWS Form1
Public Class Form1
Private Sub Form1_Load( ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dim obj_I1 as Interface1=new Class1
dim obj_I2 as Interface2=new Class1
obj_I1.test
obj_I2.test
End Sub
End Class
IN C#
interface Interface1
{
void TEST();
}
interface Interface2
{
void TEST();
}
class MultInterfaces: Interface1,Interface2
{
void Interface1.TEST()
{
int k = 740;
}
void Interface2.TEST()
{
int k = 340;
}
}
private void Form1_Load(object sender, EventArgs e)
{
MultInterfaces obj = new MultInterfaces();
Interface1 obj1=new MultInterfaces();
Interface2 obj2 = new MultInterfaces();
obj1.TEST();
obj2.TEST();
}
INTERFACE1
Public Interface Interface1
sub test()
End Interface
INTERFACE2
Public Interface Interface1
sub test()
End Interface
Class1
Public Class Class1
implements Interface1,Interface2
sub Interface1test ()implements Interface1.test
console.WriteLine("i1")
end sub
sub test() implements Interface2.test
console.WriteLine("i2")
end sub
End Class
WINDOWS Form1
Public Class Form1
Private Sub Form1_Load( ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dim obj_I1 as Interface1=new Class1
dim obj_I2 as Interface2=new Class1
obj_I1.test
obj_I2.test
End Sub
End Class
IN C#
interface Interface1
{
void TEST();
}
interface Interface2
{
void TEST();
}
class MultInterfaces: Interface1,Interface2
{
void Interface1.TEST()
{
int k = 740;
}
void Interface2.TEST()
{
int k = 340;
}
}
private void Form1_Load(object sender, EventArgs e)
{
MultInterfaces obj = new MultInterfaces();
Interface1 obj1=new MultInterfaces();
Interface2 obj2 = new MultInterfaces();
obj1.TEST();
obj2.TEST();
}
Saturday, April 3, 2010
How to show number of online users / visitors for ASP.NET website?
There are many ways to count number of active visitors on your ASP.NET website.
They differ in technique and accuracy.
here is the simplest approach that delivers acceptable accuracy when configured optimally:
Step 1:
- first we need to add these lines to our global.asax file
(if your website do not have it, create it by right-clicking on website in solution explorer,
and choosing Add New Item > Global Application Class option)
void Application_Start(object sender, EventArgs e)
{
// Code that runs on application startup
Application["OnlineUsers"] = 0;
}
void Session_Start(object sender, EventArgs e)
{
// Code that runs when a new session is started
Application.Lock();
Application["OnlineUsers"] = (int)Application["OnlineUsers"] + 1;
Application.UnLock();
}
void Session_End(object sender, EventArgs e)
{
// Code that runs when a session ends.
// Note: The Session_End event is raised only when the sessionstate mode
// is set to InProc in the Web.config file. If session mode is set to StateServer
// or SQLServer, the event is not raised.
Application.Lock();
Application["OnlineUsers"] = (int)Application["OnlineUsers"] - 1;
Application.UnLock();
}
This will allow us that whenever some distant web visitor opens our website in his browser,
and new session is created for him, our "OnlineUsers" variable in the global HttpApplicationState class instance
is increased.
Also when user closes his browser or does not click on any links in our website, session expires,
and our "OnlineUsers" global variable is decreased.
To know more about ApplicationState and HttpApplicationState class visit this MSDN link:
msdn2.microsoft.com/en-us/library/system.web.httpapplicationstate(VS.80).aspx
NOTE: we are using Application.Lock and Application.Unlock methods to prevent multiple threads
from changing this variable at the same time.
By calling Application.Lock we are receiving exclusive right to change the values in Application state.
But we must not forget to call Application.Unlock() afterwards.
Step 2:
In order for this to work correctly we need to enable sessionstate and configure its mode to InProc value (in our web.config file):
In-process mode stores session state values and variables in memory on the local Web server.
It is the only mode that supports the Session_OnEnd event that we used previously.
Timeout value (in minutes, not in seconds) configures how long our sessions are kept 'alive' - in other words
here we set how long our users can be inactive before considered Offline.
In this example timeout is set to 20 minutes, so while our visitors click on some links in our website at least once
in a 20 minutes, they are considered online.
If they do not open any pages on our website longer than 20 minutes, they are considered Offline, and their session
is destroyed, so our online visitor counter is decreased by 1.
(You can experiment with lower or higher values of Timeout settings to see what is the best for your website).
Ok, so now we are done with configuration steps, let us see how to use this:
To show number of online visitors/users on your ASPX page you can use this code:
Visitors online: <%= Application["OnlineUsers"].ToString() %>
Next you could put this code snippet in you UserControl, or inside Asp.Net AJAX UpdatePanel control, and
use Timer to refresh it in regular intervals without refreshing the whole page, but that is another story
How to access Session values from my custom HttpHandler in ASP.NET?
If you try to create a custom HttpHandler in ASP.Net just by creating a custom class that implements only the IHttpHandler interface you will come across a strange limitation: your HttpHandler code wont be able to access the Session object, for example in the ProcessRequest method.
This is because in order to access the Session object (and its values) from your custom HttpHandler you need to implement another interface: IRequiresSessionState
(Don't worry, its just a marker interface and it does not enforce you to add any more methods to your class, it just 'tells' the ASP.NET runtime that you need to access the Session object in your code:
public class CaptchaImageGenerator : IHttpHandler, IRequiresSessionState
{
// your code that access the Session object comes here...
}
More info about the topic on this MSDN page: IHttpHandler Interface
link:http://www.aspdotnetfaq.com/Category/General.aspx
They differ in technique and accuracy.
here is the simplest approach that delivers acceptable accuracy when configured optimally:
Step 1:
- first we need to add these lines to our global.asax file
(if your website do not have it, create it by right-clicking on website in solution explorer,
and choosing Add New Item > Global Application Class option)
void Application_Start(object sender, EventArgs e)
{
// Code that runs on application startup
Application["OnlineUsers"] = 0;
}
void Session_Start(object sender, EventArgs e)
{
// Code that runs when a new session is started
Application.Lock();
Application["OnlineUsers"] = (int)Application["OnlineUsers"] + 1;
Application.UnLock();
}
void Session_End(object sender, EventArgs e)
{
// Code that runs when a session ends.
// Note: The Session_End event is raised only when the sessionstate mode
// is set to InProc in the Web.config file. If session mode is set to StateServer
// or SQLServer, the event is not raised.
Application.Lock();
Application["OnlineUsers"] = (int)Application["OnlineUsers"] - 1;
Application.UnLock();
}
This will allow us that whenever some distant web visitor opens our website in his browser,
and new session is created for him, our "OnlineUsers" variable in the global HttpApplicationState class instance
is increased.
Also when user closes his browser or does not click on any links in our website, session expires,
and our "OnlineUsers" global variable is decreased.
To know more about ApplicationState and HttpApplicationState class visit this MSDN link:
msdn2.microsoft.com/en-us/library/system.web.httpapplicationstate(VS.80).aspx
NOTE: we are using Application.Lock and Application.Unlock methods to prevent multiple threads
from changing this variable at the same time.
By calling Application.Lock we are receiving exclusive right to change the values in Application state.
But we must not forget to call Application.Unlock() afterwards.
Step 2:
In order for this to work correctly we need to enable sessionstate and configure its mode to InProc value (in our web.config file):
In-process mode stores session state values and variables in memory on the local Web server.
It is the only mode that supports the Session_OnEnd event that we used previously.
Timeout value (in minutes, not in seconds) configures how long our sessions are kept 'alive' - in other words
here we set how long our users can be inactive before considered Offline.
In this example timeout is set to 20 minutes, so while our visitors click on some links in our website at least once
in a 20 minutes, they are considered online.
If they do not open any pages on our website longer than 20 minutes, they are considered Offline, and their session
is destroyed, so our online visitor counter is decreased by 1.
(You can experiment with lower or higher values of Timeout settings to see what is the best for your website).
Ok, so now we are done with configuration steps, let us see how to use this:
To show number of online visitors/users on your ASPX page you can use this code:
Visitors online: <%= Application["OnlineUsers"].ToString() %>
Next you could put this code snippet in you UserControl, or inside Asp.Net AJAX UpdatePanel control, and
use Timer to refresh it in regular intervals without refreshing the whole page, but that is another story
How to access Session values from my custom HttpHandler in ASP.NET?
If you try to create a custom HttpHandler in ASP.Net just by creating a custom class that implements only the IHttpHandler interface you will come across a strange limitation: your HttpHandler code wont be able to access the Session object, for example in the ProcessRequest method.
This is because in order to access the Session object (and its values) from your custom HttpHandler you need to implement another interface: IRequiresSessionState
(Don't worry, its just a marker interface and it does not enforce you to add any more methods to your class, it just 'tells' the ASP.NET runtime that you need to access the Session object in your code:
public class CaptchaImageGenerator : IHttpHandler, IRequiresSessionState
{
// your code that access the Session object comes here...
}
More info about the topic on this MSDN page: IHttpHandler Interface
link:http://www.aspdotnetfaq.com/Category/General.aspx
Assembly Redirection Doesn't work in web.config (Assembly Binding Bug)
I had a problem with assembly redirection in web.config, when i wrote assembly redirection in the machine.config it worked perfectly, where as when I wrote it in the web.config it didn't work
Problem:
When you try to do assembly redirection in the web.config with a project created using visual studio 2005, it doesn't work, but in machine.config it does
Solution:
it's this line: at the beginning of the web.config .NET automatically creates the xmlns tag and sets it to http://schemas.microsoft.com/.NetConfiguration/v2.0, apparently this is what was causing the problem in my application, i removed the xmlns tag, i.e. I had it written as: and everything worked great
Problem:
When you try to do assembly redirection in the web.config with a project created using visual studio 2005, it doesn't work, but in machine.config it does
Solution:
it's this line:
Find Nth maximum AND minimum value(SALARY) in SQL Server
Find Nth maximum value in SQL Server
This aritlce is written by pradeep kumar. He writes "There are several methods to find out the Nth maximum/minimum value using SQL. This article discusses on such method to find Nth maximum value from a desired table. This article is aimed at users who are in the beginner or intermediate level in SQL Server."
[Note: This article assumes that the reader is familiar with the T-SQL, joins and queries]
I have taken utmost effort to make this article easy to understand, but incase you are not clear with the concept, please raise up your concern and I’ll be more than happy to attend your doubts. All the examples discussed in this article uses Employee table. If you do not have this table, please use the following script to create it.
Use Pubs
Go
Create table Employee
(
Eid int,
Name varchar(10),
Salary money
)
Go
Insert into Employee values (1,'harry',3500)
Insert into Employee values (2,'jack',2500)
Insert into Employee values (3,'john',2500)
Insert into Employee values (4,'xavier',5500)
Insert into Employee values (5,'steven',7500)
Insert into Employee values (6,'susana',2400)
Go
A simple query that can find the employee with the maximum salary, would be:
Select * from Employee where salary = (Select max(Salary) from Employee)
How does this query work?
The SQL Engine evaluates the inner most query and then moves to the next level (outer query). So, in the above example inner query i.e. Select max(Salary) from Employee is evaluated first. This query will return a value of 7500 (based on the sample data shown as above). This value is substituted in the outer query and it is evaluated as:
Select * from Employee where salary = (7500)
Returns:
Eid Name Salary
5 steven 7500
If the same syntax is applied to find out the 2nd or 3rd or 4th level of salary, the query would become bit complex to understand. See the example below:
Select * from Employee where salary =
(Select max(Salary) from Employee where salary
< (Select max(Salary) from Employee where Salary < (Select max(Salary) from Employee where Salary <…………………………………………… N The above query would go on and on, depending on the level of salary that is to be determined. As mentioned earlier, the SQL Engine evaluates the inner most query first and moves the next outer level. One wouldn’t want to write such a big query just to find out this simple information. The same result can be achieved with a simple syntax and easily understandable logic, by using a CORRELATED SUBQUERY. This article doesn’t explain about correlated sub-query as it is out of scope of this article. (You may want to take a quick look on CORRELATED SUBQUERY.) As a "Rule of Thumb" keep these points in mind, when you use a correlated sub-query 1. Correlated sub-query is a performance overhead to the database server and so, you have to use it only if it is required 2. Avoid using Correlated subquery on large tables, as the inner query is evaluated for each row of the outer query Having said that, let’s look at the query that captures the Nth maximum value: MAXIMUM salary
Select * From Employee E1 Where
(N-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)
(Where N is the level of Salary to be determined)
MINIMUM salary
Select * From Employee E1 Where
((Select Count(Distinct(E2.Salary)) From Employee E2)-N) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)
In the above example, the inner query uses a value of the outer query in its filter condition meaning; the inner query cannot be evaluated before evaluating the outer query. So each row in the outer query is evaluated first and the inner query is run for that row. Let’s look into the background process of this query, by substituting a value for N i.e. 4,(Idea is to find the 4th maximum salary):
Select * From Employee E1 Where
(4-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)
Since the outer query’s value is referred in the inner query, the operation is done row-by-row. Based on the sample data as shown above, the process starts with the following record:
Employee E1
----------------------------------
Eid Name Salary
1 harry 3500
The salary of this record is substituted in the inner query and evaluated as:
Select Count(Distinct(E2.Salary)) From Employee E2
Where E2.Salary > 3500
Above query returns 2 (as there are only 2 salaries greater than 3500). This value is substituted in the outer query and will be evaluated as:
Select * From Employee E1 Where (4-1) = (2)
The "where" condition evaluates to FALSE and so, this record is NOT fetched in the result.
Next the SQL Engine processes the 2nd record which is:
Employee E1
----------------------------------
Eid Name Salary
2 jack 2500
Now the inner query is evaluated as:
Select Count(Distinct(E2.Salary)) From Employee E2
Where E2.Salary > 2500
This query returns a value of 3 (as there are 3 salaries greater than 2500). The value is substituted in the outer query and evaluated as:
Select * From Employee E1 Where (4-1) = (3)
The "where" condition evaluates to TRUE and so, this record IS fetched in the result. This operation continues for all the remaining records. Finally the result shows these 2 records:
Eid Name Salary
2 jack 2500
3 john 2500
The above query works in the same manner in Oracle and Sybase as well. Applying the same logic, to find out the first maximum salary the query would be:
Select * From Employee E1 Where
(1-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)
If you are able to understand this functionality, you can workout various other queries in the same manner. The bottom line is, the query should be efficient and NOT resource hungry.
Conclusion
This example is the simplest representation of Correlated sub-query. In the real-time database manipulation, correlated sub-queries will much more extensive.
link:http://www.sqlteam.com/article/find-nth-maximum-value-in-sql-server
This aritlce is written by pradeep kumar. He writes "There are several methods to find out the Nth maximum/minimum value using SQL. This article discusses on such method to find Nth maximum value from a desired table. This article is aimed at users who are in the beginner or intermediate level in SQL Server."
[Note: This article assumes that the reader is familiar with the T-SQL, joins and queries]
I have taken utmost effort to make this article easy to understand, but incase you are not clear with the concept, please raise up your concern and I’ll be more than happy to attend your doubts. All the examples discussed in this article uses Employee table. If you do not have this table, please use the following script to create it.
Use Pubs
Go
Create table Employee
(
Eid int,
Name varchar(10),
Salary money
)
Go
Insert into Employee values (1,'harry',3500)
Insert into Employee values (2,'jack',2500)
Insert into Employee values (3,'john',2500)
Insert into Employee values (4,'xavier',5500)
Insert into Employee values (5,'steven',7500)
Insert into Employee values (6,'susana',2400)
Go
A simple query that can find the employee with the maximum salary, would be:
Select * from Employee where salary = (Select max(Salary) from Employee)
How does this query work?
The SQL Engine evaluates the inner most query and then moves to the next level (outer query). So, in the above example inner query i.e. Select max(Salary) from Employee is evaluated first. This query will return a value of 7500 (based on the sample data shown as above). This value is substituted in the outer query and it is evaluated as:
Select * from Employee where salary = (7500)
Returns:
Eid Name Salary
5 steven 7500
If the same syntax is applied to find out the 2nd or 3rd or 4th level of salary, the query would become bit complex to understand. See the example below:
Select * from Employee where salary =
(Select max(Salary) from Employee where salary
< (Select max(Salary) from Employee where Salary < (Select max(Salary) from Employee where Salary <…………………………………………… N The above query would go on and on, depending on the level of salary that is to be determined. As mentioned earlier, the SQL Engine evaluates the inner most query first and moves the next outer level. One wouldn’t want to write such a big query just to find out this simple information. The same result can be achieved with a simple syntax and easily understandable logic, by using a CORRELATED SUBQUERY. This article doesn’t explain about correlated sub-query as it is out of scope of this article. (You may want to take a quick look on CORRELATED SUBQUERY.) As a "Rule of Thumb" keep these points in mind, when you use a correlated sub-query 1. Correlated sub-query is a performance overhead to the database server and so, you have to use it only if it is required 2. Avoid using Correlated subquery on large tables, as the inner query is evaluated for each row of the outer query Having said that, let’s look at the query that captures the Nth maximum value: MAXIMUM salary
Select * From Employee E1 Where
(N-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)
(Where N is the level of Salary to be determined)
MINIMUM salary
Select * From Employee E1 Where
((Select Count(Distinct(E2.Salary)) From Employee E2)-N) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)
In the above example, the inner query uses a value of the outer query in its filter condition meaning; the inner query cannot be evaluated before evaluating the outer query. So each row in the outer query is evaluated first and the inner query is run for that row. Let’s look into the background process of this query, by substituting a value for N i.e. 4,(Idea is to find the 4th maximum salary):
Select * From Employee E1 Where
(4-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)
Since the outer query’s value is referred in the inner query, the operation is done row-by-row. Based on the sample data as shown above, the process starts with the following record:
Employee E1
----------------------------------
Eid Name Salary
1 harry 3500
The salary of this record is substituted in the inner query and evaluated as:
Select Count(Distinct(E2.Salary)) From Employee E2
Where E2.Salary > 3500
Above query returns 2 (as there are only 2 salaries greater than 3500). This value is substituted in the outer query and will be evaluated as:
Select * From Employee E1 Where (4-1) = (2)
The "where" condition evaluates to FALSE and so, this record is NOT fetched in the result.
Next the SQL Engine processes the 2nd record which is:
Employee E1
----------------------------------
Eid Name Salary
2 jack 2500
Now the inner query is evaluated as:
Select Count(Distinct(E2.Salary)) From Employee E2
Where E2.Salary > 2500
This query returns a value of 3 (as there are 3 salaries greater than 2500). The value is substituted in the outer query and evaluated as:
Select * From Employee E1 Where (4-1) = (3)
The "where" condition evaluates to TRUE and so, this record IS fetched in the result. This operation continues for all the remaining records. Finally the result shows these 2 records:
Eid Name Salary
2 jack 2500
3 john 2500
The above query works in the same manner in Oracle and Sybase as well. Applying the same logic, to find out the first maximum salary the query would be:
Select * From Employee E1 Where
(1-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)
If you are able to understand this functionality, you can workout various other queries in the same manner. The bottom line is, the query should be efficient and NOT resource hungry.
Conclusion
This example is the simplest representation of Correlated sub-query. In the real-time database manipulation, correlated sub-queries will much more extensive.
link:http://www.sqlteam.com/article/find-nth-maximum-value-in-sql-server
n th record using sqlserver
declare sc cursor static for select * from system1.dbo.sys_params
declare @n int
set @n=21
open sc
while @n<25
begin
fetch absolute @n from sc
set @N=@N +1
end
close sc
deallocate sc
declare @n int
set @n=21
open sc
while @n<25
begin
fetch absolute @n from sc
set @N=@N +1
end
close sc
deallocate sc
Friday, April 2, 2010
RANDOM NUMBER SELECTION _USING SQL SERVER 2005 CHECKSUM(NEWID())
You can use the expression:
abs(checksum(newid()))%n + 1
CHECKSUM(NEWID()) returns a random integer. Applying ABS on top
ensures you get a nonnegative integer. Applying %n (modulo n) ensures that
the value is >= 0 and < n. By adding 1 you ensure that the value is >= 1 and
<= n. In short, this is just another way to get a random integer value in the
range 1 through n. So the above query can be rewritten as follows:
select
case rnd
when 1 then 'option one'
when 2 then 'option two'
when 3 then 'option three'
else 'oops'
end
from (select abs(checksum(newid()))%3 + 1 as rnd) as d;
EX:
DECLARE @n int
SET @n = 4
SELECT
AC.ArticleCategoryId,
AC.Description,
A.ArticleId,
A.Title
FROM ArticleCategories AC (NoLock)
CROSS APPLY (
SELECT TOP(@n) ArticleId, Title
FROM Articles A (NoLock)
WHERE A.ArticleCategoryId = AC.ArticleCategoryId
ORDER BY CHECKSUM(NEWID())
) A
abs(checksum(newid()))%n + 1
CHECKSUM(NEWID()) returns a random integer. Applying ABS on top
ensures you get a nonnegative integer. Applying %n (modulo n) ensures that
the value is >= 0 and < n. By adding 1 you ensure that the value is >= 1 and
<= n. In short, this is just another way to get a random integer value in the
range 1 through n. So the above query can be rewritten as follows:
select
case rnd
when 1 then 'option one'
when 2 then 'option two'
when 3 then 'option three'
else 'oops'
end
from (select abs(checksum(newid()))%3 + 1 as rnd) as d;
EX:
DECLARE @n int
SET @n = 4
SELECT
AC.ArticleCategoryId,
AC.Description,
A.ArticleId,
A.Title
FROM ArticleCategories AC (NoLock)
CROSS APPLY (
SELECT TOP(@n) ArticleId, Title
FROM Articles A (NoLock)
WHERE A.ArticleCategoryId = AC.ArticleCategoryId
ORDER BY CHECKSUM(NEWID())
) A
SQL - SELECT TOP n or SELECT TOP Random n Rows From a Table For Each Category or Group
SQL - SELECT TOP n or SELECT TOP Random n Rows From a Table For Each Category or Group
You may need a sql query that will select top n records or random n records for each category in a table. The t-sql query that will solve this problem may be difficult for first timers, especially if you are working on MS SQL Server 2000. Now, with the t-sql enhancements in Microsoft SQL Server 2005 the problem of selecting a definite number of records grouped or categorized according to a column is easier to create.
Let's define the problem once more to make you easy to imagine in your mind.
Assume that you have articles categorized by their topics. Say, articles may be in categories T-SQL, SSAS, SSIS, SSRS, .NET Framework, ASP.NET, VB.NET, C#, VISTA etc.
You want a t-sql query that will display random 3 records from each available category in the main page of your web site.
CREATE TABLE ArticleCategories (
ArticleCategoryId smallint IDENTITY(1,1) NOT NULL,
Description nvarchar(50) NOT NULL,
Active bit NOT NULL
)
GO
CREATE TABLE Articles (
ArticleId int IDENTITY(1,1) NOT NULL,
Title nvarchar(250) NOT NULL,
ArticleCategoryId smallint NOT NULL,
Text nvarchar(max) NOT NULL,
Active bit NOT NULL
)
GO
INSERT INTO ArticleCategories SELECT N'T-SQL', 1
INSERT INTO ArticleCategories SELECT N'SSRS', 1
INSERT INTO ArticleCategories SELECT N'ASP.NET', 1
INSERT INTO ArticleCategories SELECT N'VB.NET', 1
INSERT INTO Articles SELECT N'How to delete duplicate records in a table where no primary key exists', 1, N'', 1
INSERT INTO Articles SELECT N'How to create SQL Server cursor and sample sql cursor code', 1, N'', 1
INSERT INTO Articles SELECT N'How to find the first day of a month and the last day of a month?', 1, N'', 1
INSERT INTO Articles SELECT N'Reporting Services Client-Side Printing & Silent Deployment of RSClientPrint.cab file', 2, N'', 1
INSERT INTO Articles SELECT N'How to Build Your First Report In MS SQL Server 2005 Reporting Services', 2, N'', 1
INSERT INTO Articles SELECT N'How to Add Auto Number Records In Reporting Services by Using RowNumber Function', 2, N'', 1
INSERT INTO Articles SELECT N'How to use ReportViewer Control in Microsoft Visual Studio 2005', 3, N'', 1
INSERT INTO Articles SELECT N'Localization Sample ASP.NET Web Application', 3, N'', 1
INSERT INTO Articles SELECT N'Using the ASP.NET 2.0 Menu Control with Site Maps', 3, N'', 1
INSERT INTO Articles SELECT N'Conditional Statements in VB.NET', 4, N'', 1
INSERT INTO Articles SELECT N'How to check that a unique instance of a process is running', 4, N'', 1
INSERT INTO Articles SELECT N'Format Minute to Hours in VB.NET', 4, N'', 1
After inserting the above records as sample into the Article Categories and Articles by running the above sql code, we are ready for running the first t-sql script.
SELECT
AC.ArticleCategoryId,
AC.Description,
A.ArticleId,
A.Title
FROM ArticleCategories AC (NoLock)
LEFT JOIN Articles A (NoLock) ON A.ArticleCategoryId = AC.ArticleCategoryId
WHERE A.ArticleId IN (
SELECT TOP 2 ArticleId
FROM Articles A (NoLock)
WHERE A.ArticleCategoryId = AC.ArticleCategoryId
ORDER BY A.ArticleId DESC
)
ORDER BY
AC.ArticleCategoryId,
A.ArticleId DESC
What is important about the above t-sql select command is that it can also run on MS SQL Server 2000 successfully.
If you are running SQL Server 2005 or SQL Server 2008 as your database, you can try the following sql select statements also.
Here in this sql select top query, we are using the ROW_NUMBER() OVER (PARTITION BY columnname ORDER BY DESC) to get the list of articles with a row number grouped according to the column values, in our sample ArticleCategoryId. This creates a new numbering starting from 1 for each article category.
SELECT
AC.ArticleCategoryId,
AC.Description,
A.ArticleId,
A.Title
FROM ArticleCategories AC (NoLock)
INNER JOIN (
SELECT
ROW_NUMBER() OVER(PARTITION BY A.ArticleCategoryId ORDER BY A.ArticleId DESC) AS RowNumber,
A.ArticleCategoryId,
A.ArticleId,
A.Title
FROM Articles A (NoLock)
) A ON A.ArticleCategoryId = AC.ArticleCategoryId
WHERE A.RowNumber < 3
An other method of selecting records belonging to different groups or categories can be implemented by using the CROSS APPLY join shown as in the below t-sql select statement.
SELECT
AC.ArticleCategoryId,
AC.Description,
A.ArticleId,
A.Title
FROM ArticleCategories AC (NoLock)
CROSS APPLY (
SELECT TOP 2 ArticleId, Title
FROM Articles A (NoLock)
WHERE A.ArticleCategoryId = AC.ArticleCategoryId
ORDER BY A.ArticleId DESC
) A
ORDER BY A.ArticleId DESC
I think you have noticed that till now we have selected our articles or rows according to an order of column values descending or ascending. We can further alter the select statements in order to select random records from each group of record by using the ORDER BY CHECKSUM(NEWID())
Here is the updated scripts of sql which fetch random n rows from each category in a table.
DECLARE @n int
SET @n = 2
SELECT
AC.ArticleCategoryId,
AC.Description,
A.ArticleId,
A.Title
FROM ArticleCategories AC (NoLock)
CROSS APPLY (
SELECT TOP(@n) ArticleId, Title
FROM Articles A (NoLock)
WHERE A.ArticleCategoryId = AC.ArticleCategoryId
ORDER BY CHECKSUM(NEWID())
) A
GO
DECLARE @n int
SET @n = 3
SELECT
AC.ArticleCategoryId,
AC.Description,
A.ArticleId,
A.Title
FROM ArticleCategories AC (NoLock)
INNER JOIN (
SELECT
ROW_NUMBER() OVER(PARTITION BY A.ArticleCategoryId ORDER BY CHECKSUM(NEWID())) AS RowNumber,
A.ArticleCategoryId,
A.ArticleId,
A.Title
FROM Articles A (NoLock)
) A ON A.ArticleCategoryId = AC.ArticleCategoryId
WHERE A.RowNumber < @n
GO
You may need a sql query that will select top n records or random n records for each category in a table. The t-sql query that will solve this problem may be difficult for first timers, especially if you are working on MS SQL Server 2000. Now, with the t-sql enhancements in Microsoft SQL Server 2005 the problem of selecting a definite number of records grouped or categorized according to a column is easier to create.
Let's define the problem once more to make you easy to imagine in your mind.
Assume that you have articles categorized by their topics. Say, articles may be in categories T-SQL, SSAS, SSIS, SSRS, .NET Framework, ASP.NET, VB.NET, C#, VISTA etc.
You want a t-sql query that will display random 3 records from each available category in the main page of your web site.
CREATE TABLE ArticleCategories (
ArticleCategoryId smallint IDENTITY(1,1) NOT NULL,
Description nvarchar(50) NOT NULL,
Active bit NOT NULL
)
GO
CREATE TABLE Articles (
ArticleId int IDENTITY(1,1) NOT NULL,
Title nvarchar(250) NOT NULL,
ArticleCategoryId smallint NOT NULL,
Text nvarchar(max) NOT NULL,
Active bit NOT NULL
)
GO
INSERT INTO ArticleCategories SELECT N'T-SQL', 1
INSERT INTO ArticleCategories SELECT N'SSRS', 1
INSERT INTO ArticleCategories SELECT N'ASP.NET', 1
INSERT INTO ArticleCategories SELECT N'VB.NET', 1
INSERT INTO Articles SELECT N'How to delete duplicate records in a table where no primary key exists', 1, N'', 1
INSERT INTO Articles SELECT N'How to create SQL Server cursor and sample sql cursor code', 1, N'', 1
INSERT INTO Articles SELECT N'How to find the first day of a month and the last day of a month?', 1, N'', 1
INSERT INTO Articles SELECT N'Reporting Services Client-Side Printing & Silent Deployment of RSClientPrint.cab file', 2, N'', 1
INSERT INTO Articles SELECT N'How to Build Your First Report In MS SQL Server 2005 Reporting Services', 2, N'', 1
INSERT INTO Articles SELECT N'How to Add Auto Number Records In Reporting Services by Using RowNumber Function', 2, N'', 1
INSERT INTO Articles SELECT N'How to use ReportViewer Control in Microsoft Visual Studio 2005', 3, N'', 1
INSERT INTO Articles SELECT N'Localization Sample ASP.NET Web Application', 3, N'', 1
INSERT INTO Articles SELECT N'Using the ASP.NET 2.0 Menu Control with Site Maps', 3, N'', 1
INSERT INTO Articles SELECT N'Conditional Statements in VB.NET', 4, N'', 1
INSERT INTO Articles SELECT N'How to check that a unique instance of a process is running', 4, N'', 1
INSERT INTO Articles SELECT N'Format Minute to Hours in VB.NET', 4, N'', 1
After inserting the above records as sample into the Article Categories and Articles by running the above sql code, we are ready for running the first t-sql script.
SELECT
AC.ArticleCategoryId,
AC.Description,
A.ArticleId,
A.Title
FROM ArticleCategories AC (NoLock)
LEFT JOIN Articles A (NoLock) ON A.ArticleCategoryId = AC.ArticleCategoryId
WHERE A.ArticleId IN (
SELECT TOP 2 ArticleId
FROM Articles A (NoLock)
WHERE A.ArticleCategoryId = AC.ArticleCategoryId
ORDER BY A.ArticleId DESC
)
ORDER BY
AC.ArticleCategoryId,
A.ArticleId DESC
What is important about the above t-sql select command is that it can also run on MS SQL Server 2000 successfully.
If you are running SQL Server 2005 or SQL Server 2008 as your database, you can try the following sql select statements also.
Here in this sql select top query, we are using the ROW_NUMBER() OVER (PARTITION BY columnname ORDER BY DESC) to get the list of articles with a row number grouped according to the column values, in our sample ArticleCategoryId. This creates a new numbering starting from 1 for each article category.
SELECT
AC.ArticleCategoryId,
AC.Description,
A.ArticleId,
A.Title
FROM ArticleCategories AC (NoLock)
INNER JOIN (
SELECT
ROW_NUMBER() OVER(PARTITION BY A.ArticleCategoryId ORDER BY A.ArticleId DESC) AS RowNumber,
A.ArticleCategoryId,
A.ArticleId,
A.Title
FROM Articles A (NoLock)
) A ON A.ArticleCategoryId = AC.ArticleCategoryId
WHERE A.RowNumber < 3
An other method of selecting records belonging to different groups or categories can be implemented by using the CROSS APPLY join shown as in the below t-sql select statement.
SELECT
AC.ArticleCategoryId,
AC.Description,
A.ArticleId,
A.Title
FROM ArticleCategories AC (NoLock)
CROSS APPLY (
SELECT TOP 2 ArticleId, Title
FROM Articles A (NoLock)
WHERE A.ArticleCategoryId = AC.ArticleCategoryId
ORDER BY A.ArticleId DESC
) A
ORDER BY A.ArticleId DESC
I think you have noticed that till now we have selected our articles or rows according to an order of column values descending or ascending. We can further alter the select statements in order to select random records from each group of record by using the ORDER BY CHECKSUM(NEWID())
Here is the updated scripts of sql which fetch random n rows from each category in a table.
DECLARE @n int
SET @n = 2
SELECT
AC.ArticleCategoryId,
AC.Description,
A.ArticleId,
A.Title
FROM ArticleCategories AC (NoLock)
CROSS APPLY (
SELECT TOP(@n) ArticleId, Title
FROM Articles A (NoLock)
WHERE A.ArticleCategoryId = AC.ArticleCategoryId
ORDER BY CHECKSUM(NEWID())
) A
GO
DECLARE @n int
SET @n = 3
SELECT
AC.ArticleCategoryId,
AC.Description,
A.ArticleId,
A.Title
FROM ArticleCategories AC (NoLock)
INNER JOIN (
SELECT
ROW_NUMBER() OVER(PARTITION BY A.ArticleCategoryId ORDER BY CHECKSUM(NEWID())) AS RowNumber,
A.ArticleCategoryId,
A.ArticleId,
A.Title
FROM Articles A (NoLock)
) A ON A.ArticleCategoryId = AC.ArticleCategoryId
WHERE A.RowNumber < @n
GO
Subscribe to:
Posts (Atom)