Difference between revisions of "DD Class9"
(→Homework) |
(→Homework) |
||
Line 168: | Line 168: | ||
SQL Subquery | SQL Subquery | ||
− | <sql> | + | <sql>SELECT CourseID, CourseName, CourseNumber |
− | SELECT CourseID, CourseName, CourseNumber | ||
FROM Course | FROM Course | ||
WHERE | WHERE |
Revision as of 04:52, 29 March 2007
review Bios and Logon
Contents
Logon
Insert into the People Table
<sql> INSERT into People (salutationID, firstName, lastName, logonName, passwd) values (1, 'jeff', 'meyers', 'jmeyers', 'monkey') </sql>
Check the new users PeopleID
<sql> SELECT PeopleID from People WHERE firstName = 'jeff' and lastName='meyers' and logonName='jmeyers' </sql>
results PeopleID = 1
Give the new user a Role by inserting the PeopleID and a roleID into the roles table
<sql> INSERT into roles ( PeopleID, roleTypeID, RoleActive ) values ( 1, 1, 1) </sql>
Note that we need two INSERT statements to create a working account and role. Since these two statements depend on each other and we will be left with an Person without a role or a role without a person if one statement fails the entrire operation should be wrapped in a transaction. More on this next week...
Now we have a user and that user has a role. We need to be able to read back the userName, passowrd, roleActive and roleTypeName in order to check if a user is valid.
The easiest way to do this is with a view
<sql> SELECT dbo.people.peopleID, dbo.people.firstName, dbo.people.lastName, dbo.people.logonName,
dbo.people.passwd, dbo.peopleSalutationTypes.salutationID, dbo.peopleSalutationTypes.salutation, dbo.roles.roleActive, dbo.roleTypes.RoleTypeID, dbo.roleTypes.RoleTypeName, dbo.roleTypes.RoleTypeHier, dbo.roleTypes.roleTypeActive
FROM dbo.people INNER JOIN
dbo.peopleSalutationTypes ON dbo.people.salutationID = dbo.peopleSalutationTypes.salutationID
INNER JOIN
dbo.roles ON dbo.people.peopleID = dbo.roles.PeopleID
INNER JOIN
dbo.roleTypes ON dbo.roles.RoleTypeID = dbo.roleTypes.RoleTypeID
</sql>
Now we can use the view to test a logon SELECT statement <sql> SELECT peopleID, firstName, lastName, logonName, passwd, salutationID,
salutation, roleActive, RoleTypeID, RoleTypeName, RoleTypeHier, roleTypeActive
FROM logon_vw WHERE logonName = 'jmeyers' and passwd = 'monkey' and
roleActive = 1 and
roleTypeActive = 1 </sql>
Now that we have the DataBase working correctly we can move on to the c#
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
<csharp> //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
</csharp>
The above code binds to a control called gvTestOrig
<csharp>
<asp:GridView ID="gvTestOrig" runat="server" AutoGenerateColumns="true" />
</csharp>
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 <csharp>
DataTable dt = ds.Tables["Course"]; DataRow dr = dt.NewRow(); dr["CourseName"] = "Applcations Design"; dr["CourseNumber"] = "36-3601"; dt.Rows.Add(dr);
</csharp>
Modify and DataTable <csharp>
DataTable dt = ds.Tables["Course"]; DataRow dr = dt.Rows[3]; //Hack hard coded index dr["CourseName"] = "Application Design"; dr["CourseNumber"] = "36-4601";
</csharp>
Delete from a DataTable <csharp>
DataTable dt = ds.Tables["Course"]; DataRow dr = dt.Rows[1]; //Hack hard coded index dt.Rows.Remove(dr);
</csharp>
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.
<csharp> //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 }
</csharp>
http://iam.colum.edu/dd/classsource/class8/ADOTest3Commit.aspx - source
Homework
Course adder example
http://iam.colum.edu/dd/classsource/class8/student.aspx
SQL Subquery
<sql>SELECT CourseID, CourseName, CourseNumber FROM Course WHERE (
CourseID not in ( SELECT CourseID FROM StudentsCourses_vw WHERE StudentID = @StudentID )
) </sql>