get-dataset-control.ascx

Professional ASP.NET source code

<%@Control Language="C#"%>

<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.Common" %>
<%@Import Namespace="System.Data.OleDb" %>

<script language="C#" runat="server">

	public DataSet BooksDataSet(string strConnect, string strISBN)
	{
		// specify the SELECT statement to extract the BookList data
		string strSelectBooks = "SELECT * FROM BookList WHERE ISBN LIKE'" + strISBN + "'";

		// specify the SELECT statement to extract the BookAuthor data
		string strSelectAuthors = "SELECT * FROM BookAuthors WHERE ISBN LIKE'" + strISBN + "'";

		// specify the SELECT statement to extract the BookPrices data
		string strSelectPrices = "SELECT * FROM BookPrices WHERE ISBN LIKE'" + strISBN + "'";

		// 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
			OleDbConnection objConnect = new OleDbConnection(strConnect);

			// create a new Command object
			OleDbCommand objCommand = new OleDbCommand();

			// set the properties
			objCommand.Connection = objConnect;
			objCommand.CommandType = CommandType.Text;
			objCommand.CommandText = strSelectBooks;

			// create a new DataAdapter object
			OleDbDataAdapter objDataAdapter = new  OleDbDataAdapter();
			// 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 ADOCommand 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");

			// 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);
		}
		catch (Exception objError)
		{
			// display error details
			// return Nothing on error
			return null;
		}

		// return our new disconnected DataSet object
		return objDataSet;
	}

</script>