Sunday, 6 April 2014

Stored Procedures Interview Questions

Stored Procedures Interview Questions

Showing Questions 1 - 19 of 19 Questions
Sort by: 
 | 
Asked By: nikhiljain27 | Asked On: Jul 13th, 2013
Can we use SQL-transaction in nested stored procedure(stored procedure calling from stored procedure)? one SQL-transaction object in primary sp. second SQL-transaction object in nested sp(second sp).
Answered by: Akshay Jain on: Jan 29th, 2014
Yes U can... I till behave like a normal transaction
Asked By: preetiv | Asked On: May 28th, 2008
What is the difference between view and stored procedure? Can views accept input parameters as stored procedure? If yes,how could I do that? I am new to database and working on ms SQL,if someone could suggest me good reference websites then that would be great.
Star Read Best Answer

Editorial / Best Answer

Answered by: atdhdrolla
Member Since Mar-2007 | Answered On : Jul 16th, 2008
View - A View in simple terms is a subset of a table. It can be used to retrieve data from thetables, Insert, Update or Delete from the tables. The Results of using View are not permanently  stored in the database.

Stored Procedure -  A stored procedure is a group of SQL statements which can be stored into the database and can be shared over the netwrok with different users.
Answered by: Test on: Oct 16th, 2013
View does not have parameters , SP can have input / output parameters . View is another way to view table data . SP is pre-compiled object which can change /add data into tables.
Answered by: naveenreddy bolla on: Feb 21st, 2013
View: View is virtual table. It is using for security purpose means whenever we need to give only some permissions to the different user then create the view from table with required columns. Their is...
Asked By: ksjana | Asked On: Jul 7th, 2013
Ex: type ty_1 is table of number index by PL/SQL_integer; type ty_2 is table of varchar2(10) index by PL/SQL_integer; type ty_3 is table of date index by PL/SQL_integer; type rec_tab is record ( l_num ty_1, l_str ty_2, l_date ty_3); please explain this with an example having lOOPS
Asked By: Interview Candidate | Asked On: Aug 19th, 2005
A program running in the database that can take complex actions based on the inputs you send it. Using a stored procedure is faster than doing the same work on a client, because the program runs right inside the database server. Stored procedures are nomally written in PL/SQL or Java.
Answered by: guruprasad on: Oct 3rd, 2012
Stored Procedure is a re-usable program which is created once and reused n times .
It stores the procedure at database level
It provides security, better performance.
Answered by: narendra9559 on: May 24th, 2011
Stored Procedure is an already written sql statements that is saved in database.
Asked By: jayshree13 | Asked On: Apr 16th, 2011
Explain the scenario of the best usage
Answered by: ptmich on: May 28th, 2012
It is best to go for a function when a value needs to be computed but a stored procedure is useful when you need to execute business logic.
Answered by: narendra9559 on: May 25th, 2011
Hi, suppose you want top 3 salaries from emp table. syntax for top 3 salaries: select distinct top (3) (sal) from emp order by sal desc we have to use this query dialy or monthly.Mea...
Asked By: vssandilya | Asked On: Dec 12th, 2005
Answered by: Prasanjeet Das on: May 9th, 2012
SP 1-It is a subprogram that perform an action. 2-It does not contain any return clause. 3-Can return none,one or many values. 4-Can contain a return statement. SF 1-Invoke as a part of expression....
Answered by: krishnaindia2007 on: May 26th, 2008
1. A function is used to compute a value. A porucedure is used to execute business logic.2. A functin must return a value. A procedure may not return a value or may return more than one value using ou...
Asked By: plvenkatsenthil | Asked On: Oct 12th, 2011
Answered by: sreemon on: Jan 4th, 2012
I feel this Answer is revelent, It will help in understand the concept Difference between Server Jobs and Parallel Jobs Server Jobs works only if the server jobs datastage has been installed in you...
Asked By: smocherla | Asked On: Jul 27th, 2006
Answered by: akharejhansi on: Jun 8th, 2011
An external procedure, also sometimes referred to as an external routine, is a procedure stored in a dynamic link library (DLL), or libunit in the case of a Java class method. You register the procedu...
Answered by: praveengowda on: May 23rd, 2008
External procedures provide a mechanism for calling out to a non-database program, such as aDLL under NT or a shared library under Unix. Every session calling an external procedure will have its own ...
Asked By: papillon | Asked On: Jan 28th, 2010
How do we pass default value as input parameter in a store procedure?
Answered by: nisargkothari on: Sep 16th, 2010
create procedure sp_test
@param1 INT ,
@param2 INT= value

as

select * from testtable1 where value1 = @param1 and value2 = @param2
Answered by: metal on: Jan 31st, 2010
CREATE or REPLACE PROCEDURE proc_test
(name IN varchar2 DEFAULT 'Me' )
AS
BEGIN
dbms_output.put_line('My name is proc_test ' || name);
END;

SQL> exec proc_test('hello');
My name is proc_test hello

SQL> exec proc_test();
My name is proc_test Me
Asked By: deepikazain | Asked On: Mar 9th, 2010
What authid clause does in executing dynamic SQL?
Answered by: dipanjan80 on: Aug 25th, 2010
One can use the AUTHID CURRENT_USER clause to make stored procedures and functions execute with the privileges and schema context of the calling user. You can create one instance of the procedure,...
Asked By: papillon | Asked On: Jan 28th, 2010
How do we call stored procedure dynamically?
Answered by: suneesece on: May 19th, 2010
We can call using dynamic SQL, Execute Immediate.
Asked By: Suman-Delhi | Asked On: Feb 8th, 2007
Answered by: suneesece on: May 19th, 2010
select * from all_objects where object_type= 'PROCEDURE';
Answered by: Shafik Ismail on: Feb 26th, 2010
I am not too sure if I understand our question properly. If you wanted to find all the stored procedures defined to the database with a CREATE PROCEDURE command (let's say DB2), then run a query from ...
Asked By: Appu | Asked On: Jun 10th, 2007
Answered by: ushanaina on: Nov 24th, 2009
Would like to add one more point for your answer,
Calling a function from select stament is not recomended as it will degrade the performance.
Answered by: krishnaindia2007 on: May 26th, 2008
>>Why the stored functions can not be called from DML Statements?  We can call a stored function from dml statement.Here is simple exampleCREATE OR REPLACE FUNCTION TEST_1 RETURN NUMBER ISB...
Asked By: asd3 | Asked On: Jun 9th, 2007
Answered by: krishnaindia2007 on: May 26th, 2008
Stored procedures are stored in data base in complied form. In dynamic sql the statements are dynamically constructed at run time.
Answered by: sampra on: Mar 10th, 2008
Stored Procedures are compiled and runs on the server. dynamic SQL are not compiled and runs on client.
Asked By: chesy | Asked On: May 26th, 2008
Answered by: krishnaindia2007 on: May 26th, 2008
Yes , we can. Here is simple example.

CREATE OR REPLACE PROCEDURE TEST_PROC(V_NUM OUT NUMBER) AS
BEGIN
V_NUM := 10;
END;
/

CREATE OR REPLACE FUNCTION TEST_FUNC RETURN NUMBER  AS
V_NUM NUMBER;
BEGIN
TEST_PROC(V_NUM);
RETURN V_NUM;
END;
/

SELECT TEST_FUNC() FROM DUAL;
OUTPUT
10
Asked By: Shekhar | Asked On: Apr 14th, 2006
Answered by: krishnaindia2007 on: May 26th, 2008
Both are same.
Answered by: praveengowda on: May 23rd, 2008
There is no difference as such, We use AS when Stored Procedure is defined in a package if SP is outside pkg we use IS.  Here is the exampleEx: ASPakcage Headercreate package schema.testpkg ...
Asked By: Hemant Tiwari | Asked On: Feb 20th, 2007
Answered by: krishnaindia2007 on: May 26th, 2008
A ref cursor is basically a data type. A variable created based on such data type is called a cursor variable.Dynamic cursors are declared using reference cursors.Differences:1. Cusor is static in nat...
Answered by: Ramesh on: Mar 20th, 2007
Ref Cursor is used to retrieve different Active Set from different Table,
whereas Cursor can retrieve only active set from the same table.
Asked By: Sridhar Rao | Asked On: Oct 10th, 2006
Answered by: praveengowda on: May 23rd, 2008
A function can be called in a select statement as well as in a stroed proc. As function call would return a value we need to store return value in a variable.Ex:Assume we have a fuction .. FN_TEST(Dat...
Answered by: sampra on: Mar 10th, 2008
pls explain wd simple example  
Asked By: santanu | Asked On: Aug 8th, 2007
Answered by: senkum123 on: Aug 12th, 2007
PRAGMA AUTONOMOUS_TRANSACTION is used to commit the transaction in the PL/SQL block which is inside another PL/SQL block which is on the verge of rolling back.

To make an independent transaction within a PL/SQL block
Answered by: soorajsk_84 on: Aug 9th, 2007
An autonomous transaction is used to create a seprate transaction which when commited will commit data only belongs to that transactionExample PROCEDURE Proc 1  IS  .  INSERT&...

OOPS IQ



(B) What is Object Oriented Programming ?
It is a problem solving technique to develop software systems. It’s a technique to think real world in terms of objects. Object maps the software model to real world concept. These objects have responsibilities and provide services to application or other objects.

(B) What’s a Class ?
A class describes all the attributes of objects, as well as the methods that implement the behavior of member objects.Its a comprehensive data type which represent a blue print of objects.It’s a template of object.

(B) What’s a Object ?
It’s a basic unit of a system.An object is an entity that has attributes, behavior, and identity. Objects are members of a class.Attributes and behavior of an object are defined by the class definition.

(A) What’s the relation between Classes and Objects ?
They look very much same but are not same.Class is a definition , while object is a instance of the class created.Class is a blue print while objects are actual objects existing in real world.Example we have class CAR which has attributes and methods like Speed,Brakes,Type of Car etc.Class CAR is just a prototype , now we can create real time objects which can be used to provide functionality . Example we can create a Maruti car object with 100 km speed and urgent brakes.
(B) What are different properties provided by Objectoriented systems ?
Twist :- Can you explain different properties of Object Oriented Systems?
Note:- Difference between abstraction and encapsulation is one of the favorite interview question and quiet confusing as both the terminology look alike.Best is if you can brainstorm with your friends or do a little reading.
Following are characteristic’s of Object Oriented System’s :-

Abstraction
It allows complex real world to be represented in simplified manner.Example color is abstracted to RGB.By just making the combination of these three colors we can achieve any color in world.It’s a model of real world or concept.
Encapsulation
The process of hiding all the internal details of an object from the outside world.
Communication using messages
When application wants to achieve certain task it can only be done using combination of objects.A single object can not do all the task.Example if we want to make order processing form. We will use Customer object , Order object , Product object and Payment object to achieve this functionality.In short these objects should communicate with each other.This is achieved when objects send messages to each other.
Object lifetime
All objects have life time.Objects are created , initialized , necessary functionalities are done and later the object is destroyed.Every object have there own state and identity, which differ from instance to instance.
Class hierarchies (Inheritance and aggregation)
Twist :- What’s difference between Association , Aggregation and Inheritance relationships? In object oriented world objects have relation and hierarchies in between them.There are basically three kind of relationship in Object Oriented world :-
Association
This is the simplest relationship between objects.Example every customer has sales.So Customer object and sales object have a association relation between them.
Aggregation
This is also called as composition model.Example in order to make a “Accounts” class it has use other objects example “Voucher”,”Journal” and “Cash” objects.So accounts class is aggregation of these three objects.
Inheritance
Hierarchy is used to define more specialized classes based on a preexisting generalized class.Example we have VEHICLE class and we can inherit this class make more specialized class like CAR, which will add new attributes and use some existing qualities of the parent class.Its shows more of a parent-child relationship .This kind of hierarchy is called inheritance.
Polymorphism
When inheritance is used to extend a generalized class to a more specialized class,it includes behavior of the top clas(Generalized class).The inheriting class often implement a behavior that can be somewhat different than the generalized class, but the name of the behavior can be same.It is important that a given instance of an object use the correct behavior, and the property of polymorphism allows this to happen automatically.
(I) What are abstract classes ?
Following are features of a abstract class :-
√ You can not create a object of abstract class * How would you describe yourself ?
√ Abstract class is designed to act as a base class (to be inherited by other classes). Abstract class is a design concept in program development and provides a base upon which other classes are built.
√ Abstract classes are similar to interfaces. After declaring an abstract class, it cannot be instantiated on it's own, it must be inherited.
√ In VB.NET abstract classes are created using “MustInherit” keyword.In C# we have “Abstract” keyword.
√ Abstract classes can have implementation or pure abstract methods which should be implemented in the child class.
(B) What’s a Interface ?
Interface is a contract that defines the signature of the functionality. So if a class is implementing a interface it says to the outer world , that it provides specific behavior. Example if a class is implementing Idisposable interface that means it has a functionality to release unmanaged resources . Now external objects using this class knows that it has contract by which it can dispose unused unmanaged objects.
√ Single Class can implement multiple interfaces.
√ If a class implements a interface then it has to provide implementation to all its methods.
Below is the source code “IInterface” is the interface and “ClsDosomething”
implements the “IInterface”. This sample just displays a simple message box.

Public Interface IInterFace
Sub DoSomething()
End Interface

Public Class ClsDoSomething
Implements IInterFace
Public Sub DoSomething() Implements
WindowsInterFace.IInterFace.DoSomething
MsgBox(“Interface implemented”)
End Sub
End Class

OOPS (Part II)
(A) What is difference between abstract classes and interfaces?
Following are the differences between abstract and interfaces :-
√ Abstract classes can have concrete methods while interfaces have no methods implemented.
√ Interfaces do not come in inheriting chain , while abstract classes come in
inheritance.

(B) What is a delegate ?
Delegate is a class that can hold a reference to a method or a function.Delegate class has a signature and it can only reference those methods whose signature is compliant with the class. Delegates are type-safe functions pointers or callbacks. Below is a sample code which shows a example of how to implement delegates.

Public Class FrmDelegates
Inherits System.Windows.Forms.Form
Public Delegate Sub DelegateAddString()
Private Sub FrmDelegates_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
End Sub

Private Sub AddString()
lstDelegates.Items.Add(“Running AddString() method”)
End Sub

Private Sub cmdDelegates_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles cmdDelegates.Click
Dim objDelegateAddString As DelegateAddString
objDelegateAddString = AddressOf AddString
objDelegateAddString.Invoke()
End Sub
End Class

In the above there is a method called “AddString()” which adds a string to a listbox. You can also see a delegate declared as :-
Public Delegate Sub DelegateAddString()
This delegate signature is compatible with the “AddString” method.When i mean compatibility that means that there return types and passing parameter types are same. Later in command click of the button object of the Delegate is created and the method pointer is received from “AddressOf ” keyword.Then by using the “Invoke” method the method is invoked.
(B) What are event’s ?
As compares to delegates events works with source and listener methodology . So listener’s who are interested in receiving some events they subscribe to the source.Once this subscription is done the source raises events to all of it’s listener when needed.One source can have multiple listeners.
In example sample given below class “ClsWithEvents” is a event source class , which has a event “EventAddString()”.Now the listener’s who are interested in receiving this event’s they can subscribe to this event. In class “FrmWithEvents” you can see the handles clause which is associated with the “mobjClsWithEvents” objects.

Public Class ClsWithEvents
Event EventAddString(ByVal Value As String)
Public Sub AddString()
RaiseEvent EventAddString(“String added by Event”)
End Sub
End Class

Public Class FrmWithEvents
Inherits System.Windows.Forms.Form
Private WithEvents mobjClsWithEvents As New ClsWithEvents()
Private Sub FrmWithEvents_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub

Private Sub mobjClsWithEvents_EventAddString(ByVal Value As String) Handles mobjClsWithEvents.EventAddString
LstData.Items.Add(Value)
End Sub

Private Sub CmdRunEvents_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles CmdRunEvents.Click
mobjClsWithEvents.AddString()
End Sub
End Class

(I) Do events have return type ?
No events do not have return type.

(A) Can event’s have access modifiers ?
Event’s are always public as they are meant to serve every one registering to it.But you can access modifiers in events.You can have events with protected keyword which will be accessible only to inherited classes.You can have private events only for object in that class.

(A) Can we have shared events ?
Yes you can have shared event’s note only shared methods can raise shared events.

(I) What is shadowing ?
When two elements in a program have same name , one of them can hide and shadow the other one.So in such cases the element which shadowed the main element is referenced. Below is a sample code , there are two classes “ClsParent” and “ClsShadowedParent”.In “ClsParent” there is a variable “x” which is a integer.”ClsShadowedParent” overrides “ClsParent” and shadows the “x” variable to a string.

Public Class ClsParent
Public x As Integer
End Class

Public Class ClsShadowedParent
Inherits ClsParent
Public Shadows x As String
End Class

(A) What’s difference between Shadowing and Overriding ?
Following are the differences between shadowing and overriding :-
√ Overriding redefines only the implementation while shadowing redefines the whole element.
√ In overriding derived classes can refer the parent class element by using “ME” keyword , but in shadowing you can access it by “MYBASE”.

(I) What’s difference between delegate and events?
√ Actually events use delegates in bottom. But they add an extra layer on the delegates, thus forming the publisher and subscriber model.
√ As delegates are function to pointers they can move across any clients. So any of the clients can add or remove events , which can be pretty confusing. But events give the extra protection by adding the layer and making it a publisher and subscriber model.
Just imagine one of your clients doing this

c.XyzCallback = null

This will reset all your delegates to nothing and you have to keep figuring where the error is.

(B) If we inherit a class do the private variables also get inherited ?
Yes the variables are inherited but can not be accessed directly by the class interface.

(B) What are different accessibility levels defined in .NET ?
Following are the five levels of access modifiers :-
√ Private : Only members of class have access.
√ Protected :-All members in current class and in derived classes can access the variables.
√ Friend (internal in C#) :- Only members in current project have access to the elements.
√ Protected friend (protected internal in C#) :- All members in current project
and all members in derived class can access the variables. √ Public :- All members have access in all classes and projects.

(I) Can you prevent a class from overriding ?
If you define a class as “Sealed” in C# and “NotInheritable” in VB.NET you can inherit the class any further.

(I) What’s the use of “MustInherit” keyword in VB.NET ?
If you want to create a abstract class in VB.NET it’s done by using “MustInherit” keyword.This acts only as base type and can not be inherited any further.You can not create a object of a class which is marked as “MustInherit”.

(I) Why can not you specify accessibility modifier in Interface ?
All elements in Interface should be public.So by default all interface elements are public by default.

(A) What are similarities between Class and structure ?
Following are the similarities between classes and structures :-
√ Both can have constructors, methods, properties, fields, constants,
enumerations, events, and event handlers.
√ Structures and classes can implement interface.
√ Both of them can have constructors without parameter and with parameter.
√ Both can have delegates and events.

(A) What’s the difference between Class and structure’s ?
Following are the key differences between them :-
√ Structure are value types and classes are reference types.So structures use stack and classes use heap.
√ Structures members can not be declared as protected , but class members can be. You can not do inheritance in structures.
√ Structures do not require constructors while classes require.
√ Objects created from classes are terminated using Garbage collector. Structures are not destroyed using GC.

(B) What does virtual keyword mean ?
That method and property can be overridden.
(B) What is Dispose method in .NET ?
.NET provides “Finalize” method in which we can clean up our resources.But relying on this is not always good so the best is to implement “Idisposable” interface and implement the “Dispose” method where you can put your clean up routines.

(B) Whats the use of “OverRides” and “Overridable” keywords ?
Overridable is used in parent class to indicate that a method can be overridden.Overrides is used in the child class to indicate that you are overriding a method
OOPS (Part III)
(A) Where are all .NET Collection classes located ?
System.Collection namespace has all the collection classes available in .NET.

(A) What is ArrayList ?
Array whose size can increase and decrease dynamically.Arraylist can hold item of different types.As Arraylist can increase and decrease size dynamically you do not have to use the REDIM keyword.You can access any item in array using the INDEX value of the array position.

(A) What’s a HashTable ?
Twist :- What’s difference between HashTable and ArrayList ?
You can access array using INDEX value of array , but how many times you know the real value of index.Hashtable provides way of accessing the index using a user identified KEY value , thus removing the INDEX problem.

(A) What are queues and stacks ?
Queue is for first-in, first-out (FIFO) structures. Stack is for last-in, first-out (LIFO) structures.

(B) What is ENUM ?
It’s used to define constants.
(B)What’s Operator Overloading in .NET?
It provides a way to define and use operators such as +, -, and / for user-defined classes or structs. It allows us to define/redefine the way operators work with our classes and structs. This allows programmers to make their custom types look and feel like simple types such as int and string. VB.NET till now does not support operator overloading. Operator overloading is done
by using the “Operator” keyword.
(B)What’s the significance of Finalize method in .NET?
.NET Garbage collector does almost all clean up activity for your objects. But unmanaged resources (ex: - Windows API created objects, File, Database connection objects, COM objects etc) is outside the scope of .NET framework we have to explicitly clean our resources. For these types of objects .NET framework provides Object.Finalize method which can be overridden and clean up code for unmanaged resources can be put in this section.

(A)Why is it preferred to not use finalize for clean up?
Problem with finalize is that garbage collection has to make two rounds in order to remove objects which have finalize methods. In this scenario there are three objects Object1, Object2 and Object3. Object2 has the finalize method overridden and remaining objects do not have the finalize method overridden.

Now when garbage collector runs for the first time it searches for objects whose memory has to freed. He sees three objects but only cleans the memory for Object1 and Object3. Object2 it pushes to the finalization queue. Now garbage collector runs for the second time. He see’s there are no objects to be freed and then checks for the finalization queue and at this moment it clears object2 from the memory. So if you notice that object2 was freed from memory in the second round and not first. That’s why the best practice is not to write clean up Non.NET resources in Finalize method rather use the DISPOSE.

I)How can we suppress a finalize method?
GC.SuppressFinalize ()

(B)What’s the use of DISPOSE method?
Dispose method belongs to IDisposable interface. We had seen in the previous section how bad it can be to override the finalize method for writing the cleaning of unmanaged resources. So if any object wants to release its unmanaged code best is to implement

IDisposable and override the Dispose method of IDisposable interface. Now once your class has exposed the Dispose method it’s the responsibility of the client to call the Dispose method to do the cleanup.

(A)How do I force the Dispose method to be called automatically, as clients can forget to call Dispose method?
Call the Dispose method in Finalize method and in Dispose method suppress the finalize method using GC.SuppressFinalize. Below is the sample code of the pattern. This is the best way we do clean our unallocated resources and yes not to forget we do not get the hit of running the Garbage collector twice.
Note:- It will suppress the finalize method thus avoiding the two trip.

Public Class ClsTesting
Implements IDisposable
Public Overloads Sub Dispose()Implements IDisposable.Dispose
' write ytour clean up code here
GC.SuppressFinalize(Me)
End Sub
Protected Overrides Sub Finalize()
Dispose()
End Sub
End Class
(I)In what instances you will declare a constructor to be private?
When we create a private constructor, we can not create object of the class directly from a client. So you will use private constructors when you do not want instances of the class to be created by any external client. Example UTILITY functions in project will have no instance and be used with out creating instance, as creating instances of the class would be waste of memory.

(I)Can we have different access modifiers on get/set methods of a property ?
No we can not have different modifiers same property. The access modifier on a property
applies to both its get and set accessors.
(I)If we write a goto or a return statement in try and catch block will the finally block execute ?
The code in the finally always runs even if there are statements like goto or a return statements.

(A)What is Indexer ?
An indexer is a member that enables an object to be indexed in the same way as an array.

(A)Can we have static indexer in C# ?
No.

(A)In a program there are multiple catch blocks so can it happen that two catch blocks are executed ?
No once the proper catch section is executed the control goes to finally block.So there
will not be any scenarios in which multiple catch blocks will be executed.

(A) What is the difference between System.String and System.StringBuilder classes?
System.String is immutable; System.StringBuilder can have mutable string where a variety of operations can be performed.

Wednesday, 2 April 2014

Validation Jquery

function SaveHotelRegistration() {
    var Msg = "";
    if (document.getElementById("txtHotelName").value == "") {
        Msg = Msg + "<li> Please Enter the HotelName";
    }
    if ($("#ddlRoomType").val() == "0") {
        Msg += "<li> Please Select atleast One Room Type";
    }
    if ($('#txtHoteldes').val() == "") {
        Msg += "<li> Please Enter Description";
    }
    if( $('#txtHoteldes').val() != "")
     {
        if (document.getElementById("txtHoteldes").value.length < 30) {
            Msg += "<li> Description must be greater than 30 characters";
        }
     
    }
   
   
    if (document.getElementById("txtAmount").value == "") {
        Msg += "<li> Please Enter Single day Charge";
    }
    if (Msg != "") {
        jAlert(Msg,'Alert');
    }

Hotel Registration

CREATE TABLE [dbo].[Hotel](
[Hid] [int] IDENTITY(1,1) Primary Key,
[RoomType] [int] NULL,
[HotelName] [varchar](50) NULL,
[HDescription] [varchar](5000) NULL,
[Amount] [decimal](6, 2) NULL)

CREATE Procedure [dbo].[USPHotel]
@Hid int,
@HotelName varchar(50),
@RoomType int,
@Description varchar(5000),
@Amount decimal(6,2),
@Flag int
as
if(@Flag=1)  -- Insert  
begin
insert into Hotel(RoomType,HotelName,HDescription,Amount)values(@RoomType,@HotelName,@Description,@Amount)    
end  
if(@Flag=2) ---Update
begin
update Hotel set RoomType=@RoomType,HotelName=@HotelName,HDescription=@Description,Amount=@Amount where Hid=@Hid      
end  
 
 
if(@Flag=3) ---- delete  
begin
delete from Hotel where Hid=@Hid
end  
 
public class clsHotel
{
    public int Hid { get; set; }
    public string HotelName { get; set; }
    public int RoomType { get; set; }
    public string Description { get; set; }
    public decimal Amount { get; set; }
    public int Flag { get; set; }

}

 public List<clsHotel> GetHotelDetails()
    {
        try
        {
            List<clsHotel> _lstHotel = new List<clsHotel>();
            clsHotel _H = null;
            if (_con.State != System.Data.ConnectionState.Open)
                _con.Open();
            SqlCommand _cmd = _con.CreateCommand();
            _cmd.CommandText = "Select * From Hotel";
            SqlDataReader _Reader = _cmd.ExecuteReader();

            while (_Reader.Read())
            {

                _H = new clsHotel();
                _H.Hid = Convert.ToInt32(_Reader["Hid"]);
                _H.HotelName = _Reader["HotelName"].ToString();
                _H.RoomType = Convert.ToInt32(_Reader["RoomType"]);
                _H.Description = _Reader["HDescription"].ToString();
                _H.Amount = Convert.ToDecimal(_Reader["Amount"]);
                _lstHotel.Add(_H);

            }
            return _lstHotel;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (_con.State != System.Data.ConnectionState.Closed)
                _con.Close();
        }
    }
public string InsertHotel(clsHotel _H)
    {
        try
        {
            if (_con.State != System.Data.ConnectionState.Open)
                _con.Open();
            SqlCommand _cmd = new SqlCommand("USPHotel", _con);
            _cmd.CommandType = CommandType.StoredProcedure;
            _cmd.Parameters.Add(new SqlParameter("@Hid", _H.Hid));
            _cmd.Parameters.Add(new SqlParameter("@HotelName", _H.HotelName));
            _cmd.Parameters.Add(new SqlParameter("@RoomType", _H.RoomType));
            _cmd.Parameters.Add(new SqlParameter("@Description", _H.Description));
            _cmd.Parameters.Add(new SqlParameter("@Amount", _H.Amount));
            _cmd.Parameters.Add(new SqlParameter("@Flag", 1));
            if (_cmd.ExecuteNonQuery() > 0)
                return "Record Sucessfully Saved";
            else
                return "Record not Afftected to DataBase";
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (_con.State != System.Data.ConnectionState.Closed)
                _con.Close();
        }
    }
    public string UpdateHotel(clsHotel _H)
    {
        try
        {

            if (_con.State != System.Data.ConnectionState.Open)
                _con.Open();
            SqlCommand _cmd = new SqlCommand("USPHotel", _con);
            _cmd.CommandType = CommandType.StoredProcedure;
            _cmd.Parameters.Add(new SqlParameter("@Hid", _H.Hid));
            _cmd.Parameters.Add(new SqlParameter("@HotelName", _H.HotelName));
            _cmd.Parameters.Add(new SqlParameter("@RoomType", _H.RoomType));
            _cmd.Parameters.Add(new SqlParameter("@Description", _H.Description));
            _cmd.Parameters.Add(new SqlParameter("@Amount", _H.Amount));
            _cmd.Parameters.Add(new SqlParameter("@Flag", 2));
            if (_cmd.ExecuteNonQuery() > 0)
                return "Record Sucessfully Updated";
            else
                return "Record not Afftected to DataBase";
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (_con.State != System.Data.ConnectionState.Closed)
                _con.Close();
        }
    }
    public string DeleteHotel(int HID)
    {
        try
        {

            if (_con.State != System.Data.ConnectionState.Open)
                _con.Open();
            SqlCommand _cmd = new SqlCommand("USPHotel", _con);
            _cmd.CommandType = CommandType.StoredProcedure;
            _cmd.Parameters.Add(new SqlParameter("@Hid", HID));
            _cmd.Parameters.Add(new SqlParameter("@HotelName", ""));
            _cmd.Parameters.Add(new SqlParameter("@RoomType", "0"));
            _cmd.Parameters.Add(new SqlParameter("@Description", ""));
            _cmd.Parameters.Add(new SqlParameter("@Amount",  "0"));
            _cmd.Parameters.Add(new SqlParameter("@Flag", 3));
            if (_cmd.ExecuteNonQuery() > 0)
                return "Records Sucessfully Delete";
            else
                return "Records not Afftected to DataBase";
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (_con.State != System.Data.ConnectionState.Closed)
                _con.Close();
        }
    }

Sunday, 2 March 2014

Session Using DataSet

DAL
public DataSet check_Sess_DSet(Prop_Sess pro)
    {
        con.Open();
        cmd = new SqlCommand("SP_REG_PER", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@uname", pro.Cname);
        cmd.Parameters.AddWithValue("@pwd", pro.Pwd);
        cmd.Parameters.AddWithValue("@Email", pro.Email);
        da = new SqlDataAdapter(cmd);
        ds = new DataSet();
        da.Fill(ds);
        con.Close();
        return ds;
    }
BLL
public DataSet check_Sess_DSet(Prop_Sess _Pro)
    {
        DataSet ds = d.check_Sess_DSet(_Pro);
        return ds;
    }
Application Layer
 protected void btn_Log_Click(object sender, EventArgs e)
    {
        Label1.Visible = false;
        DataSet ds = null;
        _Pro.Cname = txt_Uname.Text;
        _Pro.Pwd = txt_Pwd.Text;
        _Pro.Email = txt_Email.Text;
        ds = b.check_Sess_DSet(_Pro);


        if (ds.Tables[0].Rows.Count > 0 && txt_Uname.Text == ds.Tables[0].Rows[0]["uname"].ToString() && txt_Pwd.Text == ds.Tables[0].Rows[0]["pwd"].ToString())
        {
            Session["Reg_ID"] = ds.Tables[0].Rows[0]["reg_ID"];
            Session["FirstName"] = ds.Tables[0].Rows[0]["fname"];
            Session["LastName"] = ds.Tables[0].Rows[0]["lname"];
            Session["UserName"] = ds.Tables[0].Rows[0]["uname"];
            Session["Password"] = ds.Tables[0].Rows[0]["pwd"];

            Response.Redirect("Redirect.aspx", true);
        }
        else
        {
            Label1.Visible = true;
            Label1.ForeColor = System.Drawing.Color.Red;
            Label1.Text = "Invalid Login Credentials";
        }

    }
Redirect Page
protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["Reg_ID"] != null)
        {
            txt_REgID.Text = Session["Reg_ID"].ToString();
        }
        if (Session["FirstName"] != null)
        {
            txt_FName.Text = Session["FirstName"].ToString();
        }
        if (Session["LastName"] != null)
        {
            txt_LName.Text = Session["LastName"].ToString();
        }
        if (Session["UserName"] != null)
        {
            txt_UName.Text = Session["UserName"].ToString();
        }
        if (Session["Password"] != null)
        {
            txt_Pwd.Text = Session["Password"].ToString();
        }
       
    }

Session Using DataTable

DAL
 public DataTable check_Sess_DTable(Prop_Sess pro)
    {
        con.Open();
        cmd = new SqlCommand("sp_Reg", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@uname", pro.Cname);
        cmd.Parameters.AddWithValue("@pwd", pro.Pwd);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        con.Close();
        return dt;


    }
BLL
    public DataTable check_Sess_DTable(Prop_Sess _Pro)
    {
       DataTable dt= d.check_Sess_DTable(_Pro);
       return dt;
    }

Application Layer
protected void Btn_Login_Click(object sender, EventArgs e)
    {
        Label1.Visible = false;
        DataTable dt = null;
        _Pro.Cname = txt_UName.Text;
        _Pro.Pwd = txt_Pwd.Text;
        dt = b.check_Sess_DTable(_Pro);
        if (dt.Rows.Count > 0 &&  txt_UName.Text.ToString()== dt.Rows[0][3].ToString() && txt_Pwd.Text.ToString() == dt.Rows[0][5].ToString())
        {
            Session["Reg_ID"] = dt.Rows[0][0].ToString();
            Session["FirstName"] = dt.Rows[0][1].ToString();
            Session["LastName"] = dt.Rows[0][2].ToString();
            Session["UserName"] = dt.Rows[0][3].ToString();
            Session["Password"] = dt.Rows[0][5].ToString();

            Response.Redirect("Redirect.aspx",true);
        }
        else
        {
            Label1.Visible = true;
            Label1.ForeColor = System.Drawing.Color.Red;
            Label1.Text = "Invalid Login Credentials";
        }
       }
Redirect Page
protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["Reg_ID"] != null)
        {
            txt_REgID.Text = Session["Reg_ID"].ToString();
        }
        if (Session["FirstName"] != null)
        {
            txt_FName.Text = Session["FirstName"].ToString();
        }
        if (Session["LastName"] != null)
        {
            txt_LName.Text = Session["LastName"].ToString();
        }
        if (Session["UserName"] != null)
        {
            txt_UName.Text = Session["UserName"].ToString();
        }
        if (Session["Password"] != null)
        {
            txt_Pwd.Text = Session["Password"].ToString();
        }
       

    }