salam to everyone..
as a new bee in .net i m finding lot of difficulty. i want to get all records of a table from database via store procedure.but spending many days i dont know how can i do that.
some work that i do is:
store procedure is ::
kindly help me in aspx page, store procedure and aspx.vb page.
as a new bee in .net i m finding lot of difficulty. i want to get all records of a table from database via store procedure.but spending many days i dont know how can i do that.
some work that i do is:
Code:
[vbcode]
Imports System
Imports System.Configuration
'Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Partial Class detail_procedure
Inherits System.Web.UI.Page
Private da As SqlDataAdapter Private table As DataTable
Public Const PAGING_SP As String = "pageorder"
Private Const ORDERID_FIELD As String = "DDO_id"
Private currentPage As Integer
Private firstOrderId As Integer
Private lastOrderId As Integer
Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
da = New SqlDataAdapter("SELECT * FROM DDO", System.Configuration.ConfigurationManager.ConnectionStrings("DBcon").ConnectionString)
table = New DataTable("DDO")
da.FillSchema(table, SchemaType.Source)
Dim cmd As New SqlCommand()
cmd.CommandText = PAGING_SP
cmd.Connection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("DBcon").ConnectionString)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@PageCommand", SqlDbType.NVarChar, 10)
cmd.Parameters.Add("@FirstOrderId", SqlDbType.Int)
cmd.Parameters.Add("@LastOrderId", SqlDbType.Int)
cmd.Parameters.Add("@PageCount", SqlDbType.Int).Direction = ParameterDirection.Output
cmd.Parameters.Add("@CurrentPage", SqlDbType.Int).Direction = ParameterDirection.InputOutput
da = New SqlDataAdapter(cmd)
GetData("FIRST")
dataGrid.DataSource = table.DefaultView
End Sub
Public Sub GetData(ByVal pageCommand As String)
da.SelectCommand.Parameters("@PageCommand").Value = pageCommand
da.SelectCommand.Parameters("@FirstOrderId").Value = firstOrderId
da.SelectCommand.Parameters("@LastOrderId").Value = lastOrderId
da.SelectCommand.Parameters("@CurrentPage").Value = currentPage
table.Clear()
da.Fill(table)
If table.Rows.Count > 0 Then
firstOrderId = CInt(table.Rows(0)(ORDERID_FIELD)) lastOrderId = CInt(table.Rows(table.Rows.Count - 1)(ORDERID_FIELD))
Else
firstOrderId = lastOrderId = -1
End If
Dim pageCount As Integer = CInt(da.SelectCommand.Parameters("@PageCount").Value)
currentPage = CInt(da.SelectCommand.Parameters("@CurrentPage").Value)
dataGrid.Caption = "Orders: Page " + currentPage + " of " + pageCount End Sub
Private Sub previousButton_Click(ByVal sender As Object, ByVal args As EventArgs) GetData("PREVIOUS")
End Sub
Private Sub nextButton_Click(ByVal sender As Object, ByVal args As EventArgs) GetData("NEXT")
End Sub
Private Sub firstButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) GetData("FIRST")
End Sub
Private Sub lastButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) GetData("LAST")
End Sub
Private Sub gotoPageButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Try
currentPage = Convert.ToInt32(gotoPageTextBox.Text)
Catch ex As Exception Response.Write("Error: " & ex.Message & "<br/>")
Return
End Try
GetData("GOTO")End Sub
End Class
[vbcode]
store procedure is ::
Code:
ALTER PROCEDURE pageorder
@PageCommand nvarchar(10),
@FirstOrderId int = null,
@LastOrderId int = null,
@PageCount int output, @CurrentPage int output
AS
SET NOCOUNT ON
select @PageCount = CEILING(COUNT(*)/10) from DDO
-- first page is requested or previous page when the current
-- page is already the first
if @PageCommand = 'FIRST' or (@PageCommand = 'PREVIOUS' and
@CurrentPage <= 1)
begin
select top 10 *
from DDO
order by DDO_id
set @CurrentPage = 1
return 0
end
-- last page is requested or next page when the current
-- page is already the last
if @PageCommand = 'LAST' or (@PageCommand = 'NEXT' and
@CurrentPage >= @PageCount)
begin
select a.*
from
(select TOP 10 *
from DDO
order by DDO_id desc) aorder by DDO_id
set @CurrentPage = @PageCount
return 0
end
if @PageCommand = 'NEXT'
begin
select TOP 10 *
from DDO
where DDO_id > @LastOrderIdorder by DDO_id
set @CurrentPage = @CurrentPage+1
return 0
end
if @PageCommand = 'PREVIOUS'
begin
select a.*
from (
select TOP 10 *
from DDO
where DDO_id < @FirstOrderId
order by DDO_id desc) a
order by DDO_id
set @CurrentPage = @CurrentPage-1
return 0
end
if @PageCommand = 'GOTO'
begin
if @CurrentPage < 1
set @CurrentPage = 1
else if @CurrentPage > @PageCountset @CurrentPage = @PageCount
declare @RowCount int
set @RowCount = (@CurrentPage * 10)
exec ('select * from
(select top 10 a.* from
(select top ' + @RowCount + ' * from DDO order by DDO_id a
order by DDO_id desc) b
order by DDO_id')
return 0
end
return 1