Difference between revisions of "DD Advanced ADO"
m (Text replacement - "syntaxhighlight lang="csharp" line="1" " to "syntaxhighlight lang="csharp"") |
|||
(2 intermediate revisions by the same user not shown) | |||
Line 9: | Line 9: | ||
==Simple ADO Binding== | ==Simple ADO Binding== | ||
− | <csharp> | + | <syntaxhighlight lang="csharp"> |
//Connection string comes from web config | //Connection string comes from web config | ||
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["IamConnectionJeff"].ConnectionString); | SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["IamConnectionJeff"].ConnectionString); | ||
Line 25: | Line 25: | ||
gvTestOrig.DataSource = dt; //Set the DataTable as the source for the GridView noe AutoGenerateColumns = true | gvTestOrig.DataSource = dt; //Set the DataTable as the source for the GridView noe AutoGenerateColumns = true | ||
gvTestOrig.DataBind(); //Bind the data form the table to the GridView | gvTestOrig.DataBind(); //Bind the data form the table to the GridView | ||
− | </ | + | </syntaxhighlight> |
The above code binds to a control called gvTestOrig | The above code binds to a control called gvTestOrig | ||
− | <csharp> | + | <syntaxhighlight lang="csharp"> |
<asp:GridView ID="gvTestOrig" runat="server" AutoGenerateColumns="true" /> | <asp:GridView ID="gvTestOrig" runat="server" AutoGenerateColumns="true" /> | ||
− | </ | + | </syntaxhighlight> |
http://iam.colum.edu/dd/classsource/class8/SimpleADO.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class8/SimpleADO.aspx - source] | http://iam.colum.edu/dd/classsource/class8/SimpleADO.aspx [http://iam.colum.edu/dd/gbrowser.php?file=/classsource/class8/SimpleADO.aspx - source] | ||
Line 42: | Line 42: | ||
Add to a DataTable | Add to a DataTable | ||
− | <csharp> | + | <syntaxhighlight lang="csharp"> |
DataTable dt = ds.Tables["Course"]; | DataTable dt = ds.Tables["Course"]; | ||
DataRow dr = dt.NewRow(); | DataRow dr = dt.NewRow(); | ||
Line 48: | Line 48: | ||
dr["CourseNumber"] = "36-3601"; | dr["CourseNumber"] = "36-3601"; | ||
dt.Rows.Add(dr); | dt.Rows.Add(dr); | ||
− | </ | + | </syntaxhighlight> |
Modify and DataTable | Modify and DataTable | ||
− | <csharp> | + | <syntaxhighlight lang="csharp"> |
DataTable dt = ds.Tables["Course"]; | DataTable dt = ds.Tables["Course"]; | ||
DataRow dr = dt.Rows[3]; //Hack hard coded index | DataRow dr = dt.Rows[3]; //Hack hard coded index | ||
dr["CourseName"] = "Application Design"; | dr["CourseName"] = "Application Design"; | ||
dr["CourseNumber"] = "36-4601"; | dr["CourseNumber"] = "36-4601"; | ||
− | </ | + | </syntaxhighlight> |
Delete from a DataTable | Delete from a DataTable | ||
− | <csharp> | + | <syntaxhighlight lang="csharp"> |
DataTable dt = ds.Tables["Course"]; | DataTable dt = ds.Tables["Course"]; | ||
DataRow dr = dt.Rows[1]; //Hack hard coded index | DataRow dr = dt.Rows[1]; //Hack hard coded index | ||
dt.Rows.Remove(dr); | dt.Rows.Remove(dr); | ||
− | </ | + | </syntaxhighlight> |
There are better method to select which row to modify see the example below | There are better method to select which row to modify see the example below | ||
Line 75: | Line 75: | ||
The DataAdapter can use the SqlStringBuilder class to create select, update, and delete statements. Once these statements are built the DataAdapter can update the DataProvider. | The DataAdapter can use the SqlStringBuilder class to create select, update, and delete statements. Once these statements are built the DataAdapter can update the DataProvider. | ||
− | <csharp> | + | <syntaxhighlight lang="csharp"> |
//Uses an SqlBuilder to update the DataAdapter also displays the generated sql in a lable | //Uses an SqlBuilder to update the DataAdapter also displays the generated sql in a lable | ||
public void CommitChanges() | public void CommitChanges() | ||
Line 85: | Line 85: | ||
da.Update(ds.Tables["Course"]); //User tha DataAdpater to update the Data Provider | da.Update(ds.Tables["Course"]); //User tha DataAdpater to update the Data Provider | ||
} | } | ||
− | </ | + | </syntaxhighlight> |
http://iam.colum.edu/dd/classsource/class8/ADOTest3Commit.aspx | http://iam.colum.edu/dd/classsource/class8/ADOTest3Commit.aspx |
Latest revision as of 03:23, 9 February 2016
Review ADO
- Connection - used to connect the data consumer ASP.NEt to a Data Provider
- Command - used to issue SQL commands to a Data Provider
- DataReader - A fast forward only DataSet that holds one or more resultsets.Fast and efficient
- DataSet - can contain multiple tables and relationships between tables
- DataAdapter - Passes Data from a Connection object to a DataSet and updates that data. ( A bridge used to fill dataSets with DataAdapter.Fill and DataAdapter.Update)
- DataTable - structure that holds a set of rows and columns
- DataRelation - holds relations between DataTables in a DataSet
- DataRow - data within data row
- DataView - a specific view of the DataTables within a Dataset
| |||||||||||||||
|
|
||||||||||||||
|
Simple ADO Binding
//Connection string comes from web config
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["IamConnectionJeff"].ConnectionString);
string strSQL = "Select * from Course";
SqlCommand dc = new SqlCommand(strSQL , conn);
SqlDataAdapter da = new SqlDataAdapter(dc);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
da.Fill(ds, "Course"); //Use the DataAdapter to fill the DataSet with a named Table
dt = ds.Tables["Course"]; //Retreive the named table from the DataSet
gvTestOrig.DataSource = dt; //Set the DataTable as the source for the GridView noe AutoGenerateColumns = true
gvTestOrig.DataBind(); //Bind the data form the table to the GridView
The above code binds to a control called gvTestOrig
<asp:GridView ID="gvTestOrig" runat="server" AutoGenerateColumns="true" />
http://iam.colum.edu/dd/classsource/class8/SimpleADO.aspx - source
Same code but I moved some of the code into a function and split the declareation and initialization of some object to that they are scoped to the page not to Page_Load. This allows other functions on the page to use these objects.
http://iam.colum.edu/dd/classsource/class8/ADOTest1.aspx - source
Inserting Editing Deleteing With ADO
Add to a DataTable
DataTable dt = ds.Tables["Course"];
DataRow dr = dt.NewRow();
dr["CourseName"] = "Applcations Design";
dr["CourseNumber"] = "36-3601";
dt.Rows.Add(dr);
Modify and DataTable
DataTable dt = ds.Tables["Course"];
DataRow dr = dt.Rows[3]; //Hack hard coded index
dr["CourseName"] = "Application Design";
dr["CourseNumber"] = "36-4601";
Delete from a DataTable
DataTable dt = ds.Tables["Course"];
DataRow dr = dt.Rows[1]; //Hack hard coded index
dt.Rows.Remove(dr);
There are better method to select which row to modify see the example below
http://iam.colum.edu/dd/classsource/class8/ADOTest2AddModifyDelete.aspx - source
notice that changes made to the DataSet are not synchronized back to the Data Provider. The DataAdapter is able to sync these changes, remeber that the DataAdapter acts as a bridge between the DataSet and the DataProvider.
The DataAdapter can use the SqlStringBuilder class to create select, update, and delete statements. Once these statements are built the DataAdapter can update the DataProvider.
//Uses an SqlBuilder to update the DataAdapter also displays the generated sql in a lable
public void CommitChanges()
{
//Get a stringBuilder from out DataAdapter
SqlCommandBuilder cb = new SqlCommandBuilder(da);
da.UpdateCommand = cb.GetUpdateCommand();
lblUpdateCommand.Text = da.UpdateCommand.CommandText; //Display the update Command in a Label
da.Update(ds.Tables["Course"]); //User tha DataAdpater to update the Data Provider
}
http://iam.colum.edu/dd/classsource/class8/ADOTest3Commit.aspx - source
Homework
1 Build a page that demonstrates a single table update with ADO,
2 Write a proposal for final project the proposal should include
Proposal Template: http://iam.colum.edu/janell/edu/wi/docs/proposal_wi.rtf
Proposal Template (pdf): http://iam.colum.edu/janell/edu/wi/docs/proposal_wi.pdf
- Purpose
- Objectives
- Audience
- Website Scope
- Simple map of web site(can be on paper or hyperlinks)
- Number of web pages
- Function of web pages
- DataBase Design
- Mock up of tables and relations
- ERD on paper or diagram in sql