Last post Jun 18, 2015 05:08 AM by Edwin Guru Singh
Jun 10, 2015 01:10 PM|starbearer|LINK
I am building a website for my organization and I need to display a combination of three tables (so I am assuming I have to use SQL joins) in one view. The table is essentially a list of employees but the server database has the data on various tables in
the database. I have to put them together and display the information for every employee in one row each. I also have to use an SQL connection to connect to the database first. Sorting should be allowed and I should also add a search bar to the table view.
What is the best approach for this? I found a lot of tutorials but I am confused about which one to choose over the rest. Sorry if this seems like a rookie question, I am still wetting my feet with Visual Studio.
Jun 10, 2015 04:30 PM|mostafasydney|LINK
If you use MVC framework, then you can follow this article: http://www.asp.net/mvc/overview/older-versions/getting-started-with-ef-5-using-mvc-4/sorting-filtering-and-paging-with-the-entity-framework-in-an-asp-net-mvc-application
Hope this will help.
Jun 11, 2015 04:22 AM|Edwin Guru Singh|LINK
I have to put them together and display the information for every employee in one row each. I also have to use an SQL connection to connect to the database first. Sorting should be allowed and I should also add a search bar to the table view.
Create procedure PSearch(
@Sortcolumns varchar(10), -- empname,age,city
@SortType varchar(10) --asc
Declare @where_empname varchar(100),@where_age varchar(50),@where_city varchar(100), @SortBy varchar(50)
if @empname='' or @empname IS NULL
SET @where_empname=' empname = '''+@empname+''' '
if @age=0 or @age IS NULL
SET @where_age=' age = '+@age+' '
if @city='' or @city IS NULL
SET @where_city=' city = '''+@city+''' '
if (@SortType<>'' AND @Sortcolumns<>'')
SET @SortBy=' order by '+@Sortcolumns+' '+@SortType+' '
Declare @DynamicQuery nvarchar(max)
SET @DynamicQuery='select A.EmpId,A.EmpName,A.Age,A.City from '+
'(select * from employee[e] '+
'inner join address[a] on e.empid=a.empid)[A] '+
'where 1=1 '+@where_empname +@where_age+@where_city+@SortBy
--select @DynamicQuery --this is Testing purpose for view the select query
EXEC PSearch '',0,'','',''
EXEC PSearch 'emp1',32,'XXX','Empname,age','desc'
Dynamic Sorting & searching can be done in stored procedure via passing parameter from front-end. You can do paging in front-end by using gridview ,etc...
Jun 11, 2015 05:03 AM|eralper|LINK
You can use the view in your SQL procedures.
It is possible to pass search parameters like name, id, department etc. to the procedure
Then you can use these parameters in the WHERE clause.
Although you can create a dynamic SQL statement for the WHERE clause, SQL Engine will create a new execution plan each time the stored procedure is called.
This will require additional resources (time & cpu) for performance considerations
It will be good to use a query in your procedure which uses parameters for filtering from the sql view. Try to limit the filter criterias on a small number of fields. You can create index on those fields to make the query run faster.
For paging in SQL Server, there is an option for
Order By Offset n Rows Fetch Next
Jun 11, 2015 10:32 AM|starbearer|LINK
Thanks. This is greatly helpful but I only have access to Visual Studio 2010 and MVC 2. Can I still use this tutorial? Also, I only want my view to be read-only. What do you recommend I change in the tutorial's project?
Jun 18, 2015 05:08 AM|Edwin Guru Singh|LINK
I only have access to Visual Studio 2010 and MVC 2. Can I still use this tutorial? Also, I only want my view to be read-only. What do you recommend I change in the tutorial's project?