Professional ASP.NET source code

				
				
				
				
				
				
				
				
				
				The DataSet Object with Multiple Tables
				
				
				
				The DataSet Object with Multiple Tables
				
				
				
				
				
				Connection string: 
				Books SELECT command: 
				Authors SELECT command: 
				Prices SELECT command: 
				+nbsp;
				
				DataSet.Tables Collection
				
				DataSet.Relations Collection
				
				Contents of DataSet.Tables["Books"]
				
				Contents of DataSet.Tables["Authors"]
				
				Contents of DataSet.Tables["Prices"]
				
				
				
				
					void Page_Load(Object sender, EventArgs e)
					{
						// get connection string from ..\global\connect-strings.ascx user control
						string strConnect = ctlConnectStrings.SQLConnectionString;
						outConnect.InnerText = strConnect; // and display it
				
						// specify the SELECT statement to extract the BookList data
						string strSelectBooks = "SELECT * FROM BookList WHERE ISBN LIKE '18610033%'";
						outSelectBooks.InnerText = strSelectBooks ;  // and display it
				
						// specify the SELECT statement to extract the BookAuthor data
						string strSelectAuthors = "SELECT * FROM BookAuthors WHERE ISBN LIKE '18610033%'";
						outSelectAuthors.InnerText = strSelectAuthors ;  // and display it
				
						// specify the SELECT statement to extract the BookPrices data
						string strSelectPrices = "SELECT * FROM BookPrices WHERE ISBN LIKE '18610033%'";
						outSelectPrices.InnerText = strSelectAuthors;   // and display it
				
						// declare a variable to hold a DataSet object
						// note that we have to create it outside the Try..Catch block
						// as this is a separate block and so is a different scope
						DataSet objDataSet = new DataSet();
				
						try
						{
							// create a new Connection object using the connection string
							SqlConnection objConnect = new SqlConnection(strConnect);
				
							// create a new Command object
							SqlCommand objCommand = new SqlCommand();
				
							// set the properties
							objCommand.Connection = objConnect;
							objCommand.CommandType = CommandType.Text;
							objCommand.CommandText = strSelectBooks;
				
							// create a new DataAdapter object
							SqlDataAdapter objDataAdapter = new SqlDataAdapter();
							// and assign the Command object to it
							objDataAdapter.SelectCommand = objCommand;
				
							// get the data from the "BookList" table in the database and
							// put it into a table named "Books" in the DataSet object
							objDataAdapter.Fill(objDataSet, "Books");
				
							// change the SELECT statement in the Command object
							objCommand.CommandText = strSelectAuthors;
							// then get data from "BookAuthors" table into the DataSet
							objDataAdapter.Fill(objDataSet, "Authors");
				
							// and do the same again to get the "BookPrices" data
							objCommand.CommandText = strSelectPrices;
							objDataAdapter.Fill(objDataSet, "Prices");
						}
						catch (Exception objError)
						{
							// display error details
							outError.InnerHtml = "* Error while accesing data."
								+ objError.Message + "" + objError.Source;
							return;		//  and stop execution
						}
				
						// declare a variable to hold a DataRelation object
						DataRelation objRelation;
				
						// create a Relation object to link Books and Authors
						objRelation = new DataRelation("BookAuthors",
													objDataSet.Tables["Books"].Columns["ISBN"],
													objDataSet.Tables["Authors"].Columns["ISBN"]);
						// and add it to the DataSet object's Relations collection
						objDataSet.Relations.Add(objRelation);
				
						// now do the same to link Books and Prices
						objRelation = new DataRelation("BookPrices",
													objDataSet.Tables["Books"].Columns["ISBN"],
													objDataSet.Tables["Prices"].Columns["ISBN"]);
						objDataSet.Relations.Add(objRelation);
				
						// now we're ready to display the contents of the DataSet object
						// bind the collection of Tables to the first DataGrid on the page
						dgrTables.DataSource = objDataSet.Tables;
						dgrTables.DataBind();
				
						// bind the collection of Relations to the second DataGrid on the page
						dgrRelations.DataSource = objDataSet.Relations;
						dgrRelations.DataBind();
				
						// create a DataView object to use with the tables in the DataSet
						DataView objDataView = new DataView();
				
						// get the default view of the Books table into the DataView object
						objDataView = objDataSet.Tables["Books"].DefaultView;
						// and bind it to the third DataGrid on the page
						dgrBooksData.DataSource = objDataView;
						dgrBooksData.DataBind();
				
						// then do the same for the Authors table
						objDataView = objDataSet.Tables["Authors"].DefaultView;
						dgrAuthorsData.DataSource = objDataView;
						dgrAuthorsData.DataBind();
				
						// and finally do the same for the Prices table
						objDataView = objDataSet.Tables["Prices"].DefaultView;
						dgrPricesData.DataSource = objDataView;
						dgrPricesData.DataBind();
				
					}