DataGrid paging sorting, ASP.NET Development California

ASP.NET Development California
DataGrid paging sorting
1. Binding to the DataGrid: To
A. DataSet
1. DataTable (in a dataset)
2. DataView (sorted, filtered view of a DataTable)
B. DataReader: Database specific
2. AllowPaging:
A. Adds paging controls and limits to 10 rows per page
B. BUT… you still have to write code to do paging
3. AllowSorting:
A. Turns on links to column headers so you can click on them
B. You still have to write code to sort the data on the click…
C. You don’t sort the grid…you sort the data (dataview) and display in the grid.
4. AutoGenerateColumns:
A. Turns on/off the generation of columns automatically form the datasource
B. Nice but you loose control
5. Hint: This just adds tags to HTML…which you can do manually…
A. But properties are a great way to learn what you can do.
6. Autoformat: Right Click on Grid
A. Changes a number of properties…of which you can modify as needed.
1. Note the changes in the HTML View
7. Managing/Creating Columns: Property builder
a) Columns….uncheck Create auto columns
2. Bound columns: Link to a data source (ie data field/column)
3. Button columns…inserts buttons to trigger various events based upon the action selected(________Command) insert, update, edit, delete
4. HyperLink Column: creates hyperlink in column
5. Template: Lets you define what goes in the column…ie drop down list.
B. Data Formatting: uses “standard expressions”
1. Select Columns (not the format). Note the last box
2. {0:C} the 0 is the replaceable number and C is format to apply to it.
a) D = decimal, C = Currency
3. Hint: Check help for "Formatting Types"
C. Alignment: Format . Columns . Column . Items
D. Font Formatting…Use Format
8. Paging
A. _PageIndexChanged returns the e.NewPageIndex
1. grid.CurrentPageIndex = e.NewPageIndex says set the grid current page to the new page selected
2. Then rebind the grid
B. Property Builder . Paging to define the look of the paging..
9. Sorting:
A. AllowSorting = True (properties)
B. Columns must have a sort expression set in property builder
C. Clicking on header raises “SortCommand” event and e has your sort expression in it.
1. We use this to call the HandleSort sub to set sort and direction variables.
10. How to Sort
A. Key is dv.sort field, order
B. Set session variables to hold the SortColumn & SortDirection
C. Write a HandleSort sub routine to toggle the Sort direction and sort field.
1. Toggle direction.
D. Now we can use dv.sort property to modify the view
E. And rebind


11. Selecting a row: Clicking on the Select (or sort) Hyperlink
A. Raises the ItemCommand event
B. Check the e.CommandName to determine the type of link triggered it.
1. e.CommandName = “Select” means they clicked on a button/hyperlink with “select” in command property
C. Then grab the row from the dataset associated with the selected item
1. rebuild the dataset from a session variable in the same “sorted order”
2. Get the selected row with DataSetIndex property
3. Get the value of a field in that row using the field name.
Case "Select"
dv = CType(Session("DS"), DataSet).Tables(0).DefaultView
dv.Sort = Session("SortColumn").ToString & " " & Session("SortDirection")
drv = dv.Item(e.Item.DataSetIndex)
lblSelectedItem.Text = drv("ProductName").ToString & ": " & drv("UnitsInStock").ToString
D.

Orange county california asp.net developers

SQL Server Stored Procedure, Freelancer California

Freelancer and programmers California

1. What is a SQL Server Stored Procedure ?
A. A compiled set of commands stored on the database server
B. You give it a name…pass it parameters and it does the database manipulation.
2. Question: Should all of your data access be done through stored procedures?
A. Some companies…YES! They require it.
B. New thinking: Maybe
1. Simple selects, inserts, updates and deletes just use command object and executeSQL
2. More complex involving multiple transactions or centralized data validation…use Stored Procedures
3. Either case you need to be able to create Stored Procedures.
3. Creating a stored procedure from Query Analyzer
A. Start Query Analyzer and log in.
B. Select the correct database
C. -- use Create first time
D. -- use Alter there after
1. create proc spu_GetCustomers
2. AS
3. Select CustomerID, CompanyName from customers order by CompanyName
4.
5.
6.
7. Create Proc spu_GetOrders
8. @CustomerID nchar(5)
9. AS
10. Select OrderID, OrderDate, Freight
11. from Orders
12. where CustomerID = @CustomerID
13. Order by Orderdate desc
14.
15. --test
16. exec spu_getorders 'VINET'
17.
18. sp_helptext spu_GetCustomers
4. Calling a stored procedure from VS.Net
Private Sub CustomersLoad()
'calling a stored procedure with no parameters

Dim ds As DataSet = DataSQL.GetDataSet("spu_GetCustomers")


With ddlCustomers
.DataTextField = "CompanyName"
.DataValueField = "CustomerID"
.DataSource = ds
.DataBind()
End With


End Sub

Private Sub OrdersLoad()
'calling a stored procedure with one parameter
Dim cn As New SqlClient.SqlConnection(DataSQL.CS)
Dim cm As New SqlClient.SqlCommand()
Dim dr As SqlClient.SqlDataReader

cn.Open()
With cm
.Connection = cn
.CommandType = CommandType.StoredProcedure
.CommandText = "spu_GetOrders"
With .Parameters.Add("@CustomerID", SqlDbType.VarChar, 10)
.Value = ddlCustomers.SelectedItem.Value
End With
dr = .ExecuteReader(CommandBehavior.CloseConnection)
End With
or

***************
With grdOrders
.DataSource = DataSQL.GetDataSet("spu_GetOrders " & ddlCustomers.SelectedValue)
.DataBind()
End With

ASP.NET programmers Irvine California, DataSet DataTable

ASP.NET programmers Irvine California

1. Two types of objects
A. Connection Dependent
1. Connection, Command, DataAdapter, DataReader
B. Connection Independent
1. DataSet, DataTable,DataView, DataRow
2. Data Reader…a connection specific firehose to a consumer
A. There is no client side caching of data as in a DataSet
1. (it is implicitly created when you create a DataSet to fill the ds)
B. Fastest way to read data into your application
C. You do not create an instance of a DataReader
1. Instead use command object to create it.
D. Cm.ExecuteReader(CommandBehavior.CloseConnection)
1. You are now running in a connected state…use it and close it!
2. The optional argument closes the connection when you close the datareader
3. dr = cm.ExecuteReader(CommandBehavior.CloseConnection)
A. Problem. Firehose is a connection that has to be closed when done
1. But it’s difficult to determine when “done” is
2. The .CloseConnection behavior takes care of that for you.
4. Looping Code is easy
A. dr = cm.ExecuteReader …..creates the dr
B. Do While dr.read
1. dr.read…movesnext and returns T or F based on position
C. Loop
5. Reading the dr
A. dr("CompanyName").ToString, dr("SupplierID").ToString))
1. Slower but easier to read
B. dr.GetString(1), dr.GetInt32(0)))
1. Get… is specific to datatype…#’s are ordinal positions.
6. Fastest is binding to the dr
A. .DataSource = dr
B. .DataTextField = "CompanyName"
C. .DataValueField = "SupplierID"
D. .DataBind()
7. Note Error in book
A. Page 262….SupplierID is an Integer!
If SupplierID <> 0 Then
strSQL += " and SupplierID = " & SupplierID
End If

8. Concept of Wrappers…Wrapping up generic Data Access Code in a Class module
A. DataHandler Class
1. GetDataReader, GetDataSet

9. Listbox…made up of ListItem Objects
A. dim oItem as ListItem
B. oItem= New ListItem(label,value)
1. both are strings
C. List.Items.Add(oItem)…appends
D. List.Items.Insert(index,oitem)
E. Code to insert an initial selection to the dropdown list
1.
F. .Items.Insert(0, "<--Select a Product-->")
10. Setting relations between multiple tables in a Dataset
A. A relation is linking tables together on a common field called a key
B. Very common in the business world!
1. Orders-OrderDetail
2. Employees-WeeklyPayroll
3. Members-Donations
C. Possible to maintain within a dataset
11. Goal is to create a “datarelation” object and add to the Relations tables
A. Identity one column from each table as the linking columns
B. Dim and create two column objects from the table names and ID fields
1. dcParent =ds.Tables(ParentTable).Columns(ParentField).
C. Now we can create a relation object from the two columns
D. Dim a DataRelation create new relation
1. drn = New DataRelation(Name, dcParent, dcChild)
E. Add it to the Relations collection
1. ds.Relations.Add(drn)
12. Primary key is a column of unique keys, one for each record
A. In code you may need to define which column in the dataset is the primary key.
1. Seems strange but this is how you do it.
2. Create a one dimensional array of columns
a) dim dsc(0) as datacolumn
3. Grab the column from the table you want to be the primary key and stuff it into the column you just created
a) dcs(0) = ds.Tables(Name).Columns(name)
4. Set the primary key of table to the column you just created.
a) ds.Tables(Name).PrimaryKey = dcs
13. Putting it all together
A. Goal is to show all the Orders for one Employee
B. Create an empty clone table of the orders table
1. dt = ds.tables(name).clone
C. From the select list get the Parent (row)…ie the employee
1. drwEmp=ds.Tables(name).Rows.Find(primarykeyvalue)
2. note that we have to have a primary key set
3. We will use this row to find all it’s children
D. Loop through the collection of the parents children in the orders table
1. For each row in drwEmp.GetChildRows(“childtable”)
a) dt.ImportRow(row)
2. Next row
E. Bind the clone table to a grid.
1. .datasource = dt
2. .databind()

ASP.NET Data Controls vs Data Objects

1. Data Controls vs Data Objects
A. Data controls are wizards that create code for ADO.NET data objects
B. Data controls are found on the tool box. Data object you create in code.
2. Data Controls are Microsoft tools to create the ADO.NET objects for you.
A. No Code solution to data access
1. Wizards write your code on the fly
B. Careful!!!!
C. Combination of object / wizards
3. Tool Box . Data Tab
A. Note you have duplicate controls for each data access type.
1. Once you select on type…you need to keep using that ‘type’
B. Drag the DataAdapter onto the Form
C. The DataAdapter will create the connection object
1. Caution…use NEW connection the first time.
D. Use the Query Builder to create the SQL statement
E. When you finish…it will tell you what you created
4. So far this is only the connection…but no data
A. Can not bind controls to a connection SO populate a dataset
5. The no code method: Right click on the DataAdapter
A. If you use the Generate Dataset you get a Typed DataSet
1. Creates a Type Library for your Dataset as well as a dataset. Nice for programmatically accessing your data but …. overhead
2. =…a wrapped up (user friendly) dataset
B. Creates an xsd file that defines the XML database created in memory….
1. But does not create the dataset
C. You can bind a control to a Typed or non typed dataset
6. Binding the DataGrid (in code or design time)
A. DataSource = the dataset you created
B. DataMember = there is only one table in the dataset so obvious
C. But…the dataset is empty….
7. Filling the dataset to populate the grid
A. In PageLoad…
1. da.fill(ds) loads the DataSet through the data adapter
2. Grid.DataBind…now the ds has data so bind it.
8. Cool tip: Using Server Explorer to check SQL Syntax
A. Open server explorer (CTRL ALT X) and drill down to the table to Query
B. Right click and retrieve data. View as tab
C. You should have a Query Tool bar that lets you switch views
D. Click on SQL to see SQL code and paste in yours from your code
E. Click on ! to run it…If it works here…it will work in your code.