Pagination in Oracle 11g

Assalamualikum wr br.

Today we discuss about one requirement where data needs to be displayed in GridView from table abc which has more than 2,66,000  records. This was coded to fetch data using DataAdapter on page_load event later it was done pagination from memory using GridView pagination. Later it was reported that memory leakage encountered and pagination is not responding.

Hence it was suggested to retrieve data on demand which means based on page index of gridview, no. of records needs to be fetched from data table.

Also most interesting point is Oracle 11g is being used which does not support Fetch and Limit keywords.

Fetch is used to limit the result set  generated  from the query. Since we use 11g we need to modify the existing query  as below:

Select   *  from

(         Select  p.record_type,

p.reject_reason,

p.dealer_id,

p.dealer,

p.company,

p.country,

p.state,

p.pin,

rownum rm

from

(select   p.record_type,

p.reject_reason,

p.dealer_id,

p.dealer,

p.company,

p.country,

p.state,

p.pin

from  abc) p

where rownum <=   50 <max_limit>)

where rm >= 10  <min_limit>;

 

With the help of above modifications I can able to retrieve the records in table data between max_limit and min_limit. Otherwise the rownum would return from records 1 to specified constraint.

 

That’s all for the day.

Jazakallah khair..Happy Programming..:)

What is Entity Framework..?

Assalamualaykum wr br..:
Today we discuss what EF is..?
EF is ORM framework. ORM -> Object Relational Mapping. ORM automatically creates classes based on Database tables, and vice versa is also true. ie. it can also automatically generate SQL to create database tables based on classes.
Lets start a small project on it to understand better. To achieve it,
1. Create Department and Employee Classes.
2. Write ADO.Net code to retrieve data from the database.
3. Need to create objects of Department and classes to populate data.
Entity Framework can do all the above statements automatically , if we provide it with database shema.
Create the Database and tables as shown below:
dbscripts
Now add the Ado.net Entity Data Model and name it as EmployeeModel.edmx file and this results in wizard, supply the necessary values and finish.
Connectionstring.png
compile the solution and add new webform in which add gridveiw and entity datasource control and then configure the Entity datasource with database and tables of employee and department and allow it to generate from database.
Thats it for the day..also  please have a look on the versions of EF.
The following table lists all the important versions of Entity Framework.

EF Version Introduced Features
EF 3.5 Basic O/RM support with Database First approach.
EF 4.0 POCO Support, Lazy loading, testability improvements, customizable code generation and the Model First approach.
EF 4.1 First to available in the NuGet, Simplified DBContext API over ObjectContext, Code First approach. EF 4.1.1 patch released with bug fixing of 4.1.
EF 4.3 Code First Migrations feature that allows a database created by Code First to be incrementally changed as your Code First model evolves. EF 4.3.1 patch released with bug fixing of EF 4.3.
EF 5.0 Announced EF as Open Source. Introduced Enum support, table-valued functions, spatial data types, multiple-diagrams per model, coloring of shapes on the design surface and batch import of stored procedures, EF Power Tools and various performance improvements.
EF 6.0 – Current release EF 6.0/6.1 is the latest release of Entity Framework. It includes many new features related to Code First & EF designer like asynchronous query & save, connection Resiliency, dependency resolution etc.

Entity Framework Webapp

Assalamualaykum wr br..:)
Today we are discuss about the code first approach of EF where code is designed based on which database is generated with tables and keys.
Lets quick start with web application. Create New Project -> WebSite -> <NameApp>.
Create the two classes in separate files Employee and Department also add two more classes with names as EmployeeDBContext.cs and EmployeeRepository.cs as shown
solution

Code the Employee class as shown below. Every Employee is associated with Department hence it is defined using Department property whose return type is Department and as it is available in both tables(Classes) hence it is referenced as Navigation Properties and remaining properties like Id, Firstname,  secondname etc are Static Properties.
employee
Now code the Department class as below. Every Department have Id, Name which will be Static Properties and each department holds multiple employees for which list is considered with return type as employee which is navigation properties.

department

Now lets create EmployeeDBContext.cs class where we create datasets of tables to populate the data as applicable.
employeedbcontext

To fetch and operate on tables data we need to have one generic class which is referred to as EmployeeRepository.cs as shown below. It has one Get method which populates the department class data and returns.
employeerepository
Finally add new Webform.aspx and drag and drop grid view control and also datasource control as shown below. Before you add webform compile your project to let EF create dlls which is used to reference in datasource control. In above image of employeerepository class we see Include(“Employees”). This Include method used to display Navigation Properties where in Department table Employees is the Navigation Property.
webform
Thats it for the Day..:)    Jazakallah khair

Entity Framework – CodeFirst Approach

Assalamualaykum Wr Br..:)
Today we are going to discuss Entity Framework Code First approach with a simple console application demonstrating  School activities with two classes named as Student & Standard.
As we know student have attributes like StudentId, Name, Standard, Age & Date Of Birth hence we are going to create properties with same name in class Student.
student
Similarly, Standard class do have properties like StandardId, StandardName but this class can have multiple students hence we are using ICollections of type Student as shown below:
standard
Now I would create a new class with SchoolDBContext name where DBContext is mandatory for flexible default operations with built in libraries of Entity Framework. This DBContext have dataset hence its return type would be Dbset and its properties are classes with s appended atlast with name as shown below:
schoolcontext
Finally it is required to call the DBContext class in main method as shown below:
program
With this Execution of program, Entity Framework creates the New database with tables as designed with classes as shown below:
dbcontext

With this it is observed that based on the code design DB is being generated hence it is regarded as Code First Approach.

Thats it for today..:Jazakallah khair

 

Asp.Net Dropdown Control items from Database

Assalamualaykum Wr Br…Today we explore dropdown control list items binding from database table on to UI. Before we quick start, there are few properties that we need to remember when dealing with Dropdown controls. They are:
1. DataTextField
2. DataValueField
3. DataSource
4. DataBind
There are many other properties like selectedIndex, IndexChanged but we focus on above.
Lets start the POC:
DDl
I just drag & drop the Dropdownlist control from toolbox on form beside Qualification label. The items of this dropdownlist should be populated from Table Register and column Qualification. To accomplish it, I write Sql Query as: select qualification from register in sqlcommand class or sqldataadapter as shown below:
ddl1
As I need dropdownlist to be populated on page_load hence I am calling method filldropdown
In filldropdown method,sqlconnection is established then sqldataadapter executes the query fetches data and fills in dataset which becomes datasource to dropdownlist and finally binds to it. The above code produces following output:
ddl5
One thing to be noted is that all items are being populated from database, if we comment the statement Dropdownlist1.AppendDataBoundItems=true.
If we uncomment it then it is possible to add items manually to dropdownlist which is done by statement as dropdownlist1.items.add(“”); whose output is shown as:
ddl4
Jazakallah khair…thats all for the day…

Asp.Net GridView 4

Assalamualaykum Wr Br…GridView displays data directly on to the page with out any proper style as shown below:
gridview_unstyle
If we apply style over to gridview as shown below:

<div style=” height:100%; width:100%; overflow:auto;”>
<asp:gridview runat=”server” id=”gv1″ >
…respective columns…
</asp:gridview>
we get scroll bar when data exceeds than specified height and width on page.
Note: the syntax is pure sensitive, if you miss any colon (:) or double quotes (“) or semi-colon(;) then that does not reflect on UI of Page.
Thats all for the day…hope would be useful in future…
Jazakallah Khair…

Asp.Net GridView 3

Assalamualaykum Wr Br…In this post we would like to explore the few events of Asp.Net GridView. Lets explore RowEditing, RowUpdating Events here. Goto Properties of the GridView and click the Events tab as shown:
GridView_property
You can see list of events of Gridview like SelectedIndexChanged, RowCancelingEdit, RowCommand,RowDeleted, RowDeleting, RowUpdated, RowUpdating etc.
Double click the RowEditing Event textbox in the property strip that takes you to the eventhandler method as shown below:
GridviewEdit
The above pic have RowEditing, RowDataBound Events whose code is shown.
RowDataBound event is used to bind data to specific row which is called foreach row.
We can filter out the execution of RowDataBound specific to Controls, as in our case we are finding out dropdownlist controls. we create reference entity of DropDownList ad to which FindControl method of GridViewRowEventArguments. Specify the control id of Dropdownlist as parameter to FindControl method. Later we execute the establish connection, execute the command and binds to dropdownlist, whose output is as shown:
gridViewEditOutput
Thats all for the day..Hope enjoyed…
Jazakallah Khair…