| Data Bindings |
|
| Windows Forms versus Web Applications |
|
| ASP.NET is almost completely stateless and more request/ response-centric. Windows Forms applications, on the other hand, retain state in memory on the client and user interaction that is not request/ response-based. These differences lead to a divergence of techniques used in each of the environments. This is definitely true for data bindings, as you bring together the user interface with one or more data sources. |
|
| As we make our way through data bindings, not only will we discuss the capabilities of these two environments, but we will also explore the differences between the two as well. We will try hard to not confuse you with terms such as “data source” and “DataSource”. While they are similar, they are also very different. We will try to explain what we mean by that as we push forward through the two words of data binding. |
|
| In the perfect world, there would not be such a drastic difference between the programming styles of Windows Forms applications and ASP.NET applications. Some of this can be attributed to environmental differences, some of it to differences in objects models and concepts. Furthermore, we can’t overlook the fact that some of this difference boil down to a lake of coordination between the different development teams. |
|
| The Concept of Data Binding |
|
| Whether you use ASP.NET applications or Windows Forms, the high-level concept is the same. You get data from somewhere – your database, a file that you load, XML, an application class that is a business object, or from a far-off Web Service that exposes access to data hidden in a remote system. Regardless of the source, you can generically refer to it as a data source. |
|
| Data is not very interesting unless you are doing something with it: displaying it, manipulating it, or simply interworking it into a composite with other data. With respect to data binding, we are interested in the visual display and manipulation by the end user of an application. |
|
| The ultimate goal of data binding is to provide an efficient way to “glue” a data source to user interface controls that enable users to interact with the data without any knowledge of where the data resides, and to automate, optimize, and reduce the amount of effort and code the developer must create to accomplish the binding of data. |
|
| Options for Getting the Data |
|
| One of the difficult decisions you must take in developing your applications is which technique you will use to define the interaction with your actual data source. Many would argue that you should pick one and only use that one technique. Personally, we are of the opinion that the world is not that black and white, and it’s considerably valuable to think through your data access strategy. We think each of the techniques has its place, which is why so many exist in the product. |
|
| The following table highlights the silent pros and cons of each technique you can use to bind your data source. Use this table with the rest of the book and with your personal application architecture beliefs when deciding what type of application you are building. |
|
Bind To?
|
Pros
|
Cons
|
| Database via Dynamic SQL | 1. Easy to build using drag and drop capabilities in Visual Studio.
2. Reduce deployment on the database side compared with using stored procedures. | 1. SQL statements are embedded inside pages of the application and must be duplicated if the same access is required in multiple parts of the application.
2. Security benefits of stored procedures are not obtained.
3. It’s not possible to tune or modify SQL without deployment of the application.
4. Syntax errors are not detected until runtime because they are not precompiled by SQL engine. |
| Database via SQL Stored Procedures | 1. SQL statements are not embedded in each page, only the reference to the stored procedure is embedded in each page.
2. Depending on the SQL engine, some performance improvement may be obtained by the database engine’s ability to precompile when a stored procedure is cataloged. If nothing else, DBA or another performance tuning expert can manipulate the query independent of code modifications during performance tuning.
3. Minor database changes can be hidden from the application by simple modification of the stored procedure, i.e., it doesn’t require an application change. | 1. If business logic is required now or in the future, it will have to be stored either on each page or in the stored procedure, or the code will have to be redesigned to accommodate the business logic. It provides no capability to include “business logic” that is not repeated on each page. Typically, the impact of this isn’t seen during initial development but as the application evolves and more rules are developed.
2. Development can more complex because stored procedures must be deployed at the same time as the code. |
| Business Object | 1. Provides a layer of abstraction on top of a data source, enabling business rules to evolve as the application matures.
2. Clean separation of the UI from the source of the data and takes involved in data management.
3. Rules are consistently implemented and logic is not embedded in each page.
4. Much of the business object code could be generated to reduce required development and to promote consistency.
5. Allows use of language capabilities such as inheritance and partial classes to reduce the amount of code and ensure consistency. | 1. Typically takes longer to develop and cant be created using simple drag and drop techniques. |
| Web Services | 1. Like business objects, can provide a higher level of abstraction from the ultimate data source.
2. Can be wrapped with business objects and the page or form would never know it was a remote service.
3. Typically, not an all-or-nothing approach and adds nicely to an overall architecture, especially in a broader SOA (Service Oriented Architecture) approach to system integration and service aggregation. | 1. Not drag and Drop development.
2. Can consume more time than typical n-tier business object usage due to complexities involve in Web Services invocation and passing of data. |
| XML Data | 1. Can provide a rich capability to express hierarchical data and associated relationships.
2. Great for readability and interoperability with other applications and system. Often used for storing things such as site maps.
3. With increased support in the database to treat XML as a first class citizen, more and more hybrid relation / XML-centric applications will be built. | 1. XML doesn’t reflect the same strict relational properties or achieve the same level of performance of traditional relational data from a database. |
|
|
| One Way Binding versus Two Way Binding |
|
| In one-way data binding, data flows only in one direction: values from properties of the data source are placed into properties on the user interface control, but there is no flow of data back from the control to the data source if those property values change within the control, at least not automatically. |
|
| In two-way data binding, changes to the bound properties of a control also result in those changed values updating the corresponding data source values in the object in memory on the client. Most data-binding mechanisms in Windows Forms are designed to be two-way. |
|
| You present data in user interface controls that comes from a data source, but if changes are made to the control property values through user interaction with the control, you want those changes to be reflected in the underlying data source as well. The idea of two-way data binding is to have this happen without needing to write a bunch of code yourself that collects those changes and makes the updates. |
|
| The other aspect to consider is at what point in the execution of your program the data flows from the data source to the control, and back to the data source from the control if you have two-way data binding. To have data flow in a program, there has to be some executing code that makes it flow. There could be several different triggers: |
|
• The line of code establishing the data binding was just executed.
• A line of code was called that tells the control, the data source, or the data-binding mechanism itself to refresh itself.
• An event handler is called because of a change in the data source or the control values. |
|
| Depending on the data-binding mechanism and the data sources and controls involved, these things may happen implicitly behind the scenes in base class or Framework code, or they may need to be invoked explicitly at points in the program where you need the data to flow. Understanding when the data is expected to flow based on the controls and mechanisms you are using is crucial to getting the data-binding mechanism to behave the way you expect. |
|
|
|
|
|
| Data Bindings |
|
| Data Binding in ASP.NET |
|
| ASP.NET has supported one-way data binding since version 1.x on many of its controls, such as DataGrid and DataList. In version 1.x, you are left on your own when it comes to retrieving the data and binding that to the control. If you have three controls on the page that need the same data, you are responsible for getting each one of its own data. |
|
| Often, data doesn’t need to be retrieved for each request. For example, product catalog information that is the same for each request can be cached to reduce the number of times you have to go to the database. Again, in version 1.x, all of that was left in your hands to code and to determine how best to ensure that you wrote an efficient application. |
|
| ASP.NET 2.0 brings along a new set of controls, concepts, and strategies that can be used in data binding in your Web application. The biggest change in a new control called a Data Source. The data source manifests itself in several derived classes/controls that we will be discussing here that provide access to your data and provide the mechanism for dropping them on a page and building your other controls to the data source. |
|
| These new data source controls becomes your facilitators for providing one-and two way data binding with the controls on your page. New controls such as DetailView, GridView, and others combined with the Data Source controls now enable you to do everything declaratively using drag and drop, properties, and markup. |
|
| In addition, they don’t require writing any code in order to use them. However, they all still support a rich programmable support interface when required. These controls are ASP.NET built-in controls, and they completely implement two-way data binding. |
|
| Benefits of the Data Source Concept |
|
| One of the benefits of the data source control concept is that it reduces the tendency that developers have of retrieving the same data multiple times on the page. One of the challenges that people face when they start componentizing their controls on a page, whether they are using server controls or user controls, is how to manage data when multiple controls on the page need access to the same data. Whether it’s customer information or product information, you need to retrieve that from the database. |
|
| All too frequently, each control will independently go to the data source and retrieve that data. The data source concept helps streamline that process by providing a consistent mechanism that enables the application to drop the data source onto the page and tell it what data to get; then controls that need access to that data simply assign that data source control as the DataSourceID for that visual control(for example, DropDownList). |
|
| Custom control developers need to learn how to enable their custom controls to be able to leverage the new data source controls, so they don’t fall back on simply retrieving the data again. The advantage is realized by using a consistent model, as well as sharing data sources across controls on a page as much as possible. |
|
| The data source concept also provides opportunities to be extended as appropriate. For example, you might be able to make custom DataSource controls that are application specific to make it easier for your development team to use them on the pages. The data source concept enables you to do this without requiring new developers to learn a proprietary new concept. |
|
| Data Source Controls Provided with ASP.NET 2.0 |
|
| The following Data Source controls are provided out of the box with ASP.NET: |
|
| Data Source Control Name | Description |
| SqlDataSource | Provides support for accessing and manipulating SQLrelational database. This is not just Microsoft SQLServer specific and will work with other databases, including oracle, IBM DB2, and MySQL. |
| AccessDataSource | Inherits from SqlDataSource and provides a specialized implementation for working with Microsoft Access databases. Provides only retrieval access and no support for insert, update or delete operations. |
| XmlDataSource | Providing support for accessing XML data that is either tabular or hierarchal. Support for transformation and Xpath filtering is provided. Paging is not available, but caching is, and modification support is provided by directly calling the save method. Derives from HierarchicalDataSourceControl instead of DataSourceControl. |
| ObjectDataSource | Provides for interaction with your business object that exposed properties and query/update style methods. Provides a business layer abstraction on top of the true data source, which is typically a SQL engine from one of the vendors. Enables you to use data-binding concept while still maintaining an n-tier approach to your architecture. |
| SiteMapDataSource | Provides access to data managed by the Site Map provider. Allows starting at a specific location in the Site Hierarchy, but doesn’t support modification, sorting, paging, or caching. Derives from HierarchicalDataSourceControl instead of DataSourceControl. |
|
|
| Data Source Caching |
|
| Data Source caching enables the Data Source control to save a copy in the ASP.NET cache of the data retrieved by the select command for future use. For example, if you had a Data Source control that was used to load product category information into a drop-down that information doesn’t change frequently so there is no need to have the database access performed each time the control is used. |
|
| Using the caching option on the Data Source control means the data would be retained in the ASP.NET cache for a period of time specified on the Data Source control and utilized by the controls on the page without incurring a database access request. |
|
| Data Source provides an application-wide, in-memory repository to store items for up to the life of the ASP.NET application. Unlike a user session that is specific to single users, items stored in the ASP.NET Cache are available to all users of the application. |
|
| The lifetime of items in the ASP.NET Cache is based on a number of factors, including expiration policies established when the item was added. If you are on a Web farm (multiple servers supporting a single application), each server on the Web farm has its own local ASP.NET Cache and is not synchronized with the other servers on the farm. |
|
| Another important thing to understand is that these cached items are stored in the private cache area, not in the public cache area that is accessible to your application. When it is stored in the cache by the Data Source control, the cache key is generated to ensure that it is unique for the type of control being used. |
|
| The following table describes some of the key properties that control caching on the DataSource controls: |
|
| Property | Description/Comments |
| CacheDuration | Time in seconds that the Data Source control will keep the data in ASP.NET Cache after the SelectMethod is invoked. |
| CacheExpirationPolicy | Establishes whether the expiration is a sliding window or an absolute time. |
| CacheKeyDependency | Used to define a key that is used by another cached object (Object #2),so that if that object (Object #1) is removed from cache this object /(Object #2) should be removed also. For example, if you had a CatalogData key that all other catalog data was dependent on and you wanted to force a refresh of all data, you would merely delete the CatalogData object from cache, and all dependent items would be removed. |
| SQLCacheDepedency | Specifies a semicolon-delimited list of connection string/ table name pairs that define dependencies on a SQL database. |
|
|
| Making Your Cache Dependent on SQL Data |
|
| SQLCacheDepedency enables you to set up a cache dependency based on your SQL Server data. The advantage of this is that is avoids having to refresh cache data at a specific time, regardless of whether the source of the data has changed. |
|
| SQLCacheDepedency will work with SQL 7, SQL 2000, and SQL 2005. in SQL 2005, it is optimized and uses the full Notification Services capabilities of the database engine to perform the notification. |
|
| In order to use SQLCacheDepedency, you must enable it on both the database and the specific table. There are currently two standard ways to accomplish this: you can use the aspnet_regsqlcache command-line utility that ships with the Framework, or you can do it programmatically via the SQLCacheDepedencyAdmin class that is part of the System.Web.Caching namespace. |
|
| Choosing a Caching Technique |
|
| A discussion on using the ASP.NET cache would not be complete without a discussion of when you should use the different types of caching available in ASP.NET. In addition to the caching capabilities in the DataSource control, ASP.NET also enables you to do different types of caching: Page-level caching, User-control caching, and programmatically by using the Cache object directly. |
|
| Page-level caching allows the same cached copy of a page to be delivered to requestors based on duration as well as query string to determine how long page contents are cached and how many different instances of the page are kept. |
|
| User-control caching allows the same style of control, but only for the region that is rendered for that user control: all other content on the page is regenerated on each request. Data Source caching provides the capability to store the raw data from a query and doesn’t cache any of the rendered output that the controls using the data create. |
|
| Types of Caching | When to Use/Comments |
| Page-level caching | This is great to use when all of the content on the page doesn’t vary on a request-by-request basis. For example, if you present a product information page, and no pricing or any other information varies by user, then you could probably use page-level caching and vary your caching by the product ID to enable each product to be cached. |
| User-control caching | This approach is appropriate when only a region of a page contains content that is the same for each request. |
| Data Source caching | This option is great for caching data that feeds a specific control on the page. The capability to turn on SQL Cache Dependency on a Data Source provides a convenient way of refreshing the cached data if the underlying data source changes. |
| Direct Storage in ASP.NET Cache | This is great for data that is not necessarily used with a single control, or has other reasons for existence. It requires additional code to load and to maintain data in cache compared to other caching approaches. |
|
|
| One of the other features that page and user-control offer is vary by caching. This type of caching enables you to keep multiple copies based on query string, session, or other custom methods. |
|
| SQLDataSource Control |
|
| The SQLDataSource provides the capability to interact with the database directly to either execute SQL text commands or invoke stored procedures on the target database. Although the name suggests that this control is applicable only for Microsoft SQL Server, that’s not the case at all. The SQLDataSource is capable of interacting with any ADO.NET data provider. |
|
| Configuring the SQLDataSource Using the Wizard |
|
| After dragging a SQLDataSource onto your Web Form, the SQLDataSource Configuration Wizard will start up and begin to prompt you for the options required to let the control know how to access the data. |
|
| All of the options you can set via the wizard you can also set programmatically, or from the properties page, or simply on the element in the markup: |
|
| 1. Configuring the ConnectionString: The first step in the wizard is establishing the connection to your database. This is where you will be prompted to select an existing connection string or to create a new one, as shown in figure below. |
|
 |
|
| 2. Configuring where to save the ConnectionString: The most important decision you will make now is where to store the connection string. You can either store the connection string on the markup for the control, or have it update your application configuration(web.config) and insert an entry in the new section. The best practice here is to utilize the configuration file to prevent duplication of the setting on each data source. |
|
 |
|
| if you were to peek into web.config after the wizard runs, you would see the connection string that was added to the new section that is new for .NET 2.0, as shown below: |
|
<configuration>
<appSettings/>
<connectionStrings>
<add name="AdventureWorksConnectionString"
connectionString="Data Source=UJJWAL22;Initial
Catalog=AdventureWorks;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
|
|
| 3. In this step, you will configure the columns of the SELECT statement that will be used or indicate that you desire to use a custom query or stored procedure. |
|
 |
|
| Keep in mind that if you select the columns here, the query will be embedded on the aspx page markup and will be duplicated if you have other pages that use the same query. |
|
| Now is a good time to reiterate that using SQL embedded will ultimately result in increased maintenance costs. It is recommended that larger applications, at a minimum, use stored procedures or the ObjectDataSource with either your own business objects or typed datasets and data adapters. |
|
| Another important thing to consider hare is that if you select “*”, you will get back every column for the table. If you only needed two of the columns, this would be wasting resources, especially if you are enabling caching of the results. |
|
| Additionally, if you are using a control like GridView and have the Auto Generated Columns option turned on, the columns that appear on your page could change in the future simply by someone adding a column to the table. While some might view that as a benefit to using the “*” approach, you might expose some data that you did not intend to show the end user. Another problem that might occur is that your page formatting might suffer when new fields are added. |
|
| 4. Configuring the Custom Select, Insert, Update, or Delete: Either by typing in a SQLstatement or by using Query Builder, this page (See figure) enables you to define the commands separately for SELECT, UPDATE, INSERT, and DELETE. |
|
| If you need to use parameters in your SQL statement, you must specify them as placeholders in your query, as shown below: |
|
| Select eventide, eventtitle from event where eventide = @eventid |
|
| By specifying a placeholder for a parameter, the wizard will take you to a new page that enables you to configure a source for the parameters you defined. |
|
| Alternatively, you can select the stored procedure radio button, which enables you to select from existing stored procedures. If you have not defined any stored procedures in your database, this list will not be enabled. You would have to add your stored procedures and then return the configuration on the SQLDataSource control in order to see them. |
|
 |
|
| Key Properties of the SQLDataSource |
|
| The following table describes a subset of the key properties of the SQLDataSource control and some associated comments regarding their use. Consult the MSDN documentation for a complete list of every property on the control. |
|
| Property | Description/ Comments |
| DataSourceMode | Controls how the data is retrieve either in a DataSet or using a DataReader. If controls that use the DataSource require control over paging and sorting, DataSet is required or it will generate an error. If you only require a simple population of a control, using a DataSet would not provide any advantage over using the DataReader will typically perform better in this case. |
| CancelSelectOnNullParameter | This gives you control if the Select should happen when any value within the select parameter collection is null. This can be powerful to control the query from happening but it can also be tough to debug if you are not careful with your selection of parameters. For example, if you have a state and country drop-down, you don’t want to do the query if the user hasn’t picked a country yet; therefore, if country is null, you don’t want the Select query to fier. |
| ConflictDetection | Only applies during Update or Delete operations and specifies whether the control should try to detect changes in the underlying data source. The default value is Overwrite and will not do checks of the current values on the underlying data. |
| ProviderName | ProviderName enables you to specify a different ADO.NET provider using the invariant name. This defaults to System.Data.SqlClient, but it can be set to any registered provider. For example, Oracle would be Syatem.Data.OracleClient. |
|
|
| Beware of Provider-Specific Syntax |
|
| The SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand properties all provide the SQL strings that are passed to the active provider set using the ProviderName property. These strings are sensitive to the specific SQL syntax supported by the provider. |
|
| The provider-specific nature of the commands highlights an important disadvantage associated with using the SQLDataSource in your applications, as it ties them directly to the provider you choose. Many believe that this lack of abstraction on the top of your data is reason enough to not use this type of control. |
|
| Conversely, on the positive side, because the syntax is specific to a provider, you can leverage provider capabilities without having to dumb down your syntax to a common specification that typically would preclude use of vender-specific features. |
|
| One way to partially mitigate some of your exposure to this problem is to define a library of DataSource controls that inherit from SQLDataSource and are used by developers. These controls can specify all of the necessary provider-specific information. This would be more business-centric controls- for example, ProductDataSource. |
|
| While under the covers it is just a SQLDataSource, the developer could drag it from the Toolbox and use it without setting any of the properties unless required to deviate from the typical settings. |
|
| This is also a way to promote consistency across your pages and prevents specific SQL or SP calls from littering the pages. An even more advanced version of this concept could be leveraged using tools such as CodeSmith or other-generation helpers to build these based on templates for all of the data objects in your ultimate data source. |
|
| ObjectDataSource Control |
|
| The ObjectDataSource is one of the most powerful feature additions to data binding inASP.NET 2.0. It provides a declarative method of using objects in your data binding. Previously, if you wanted to bind to objects, you did it either programmatically or via cryptic “eval” statements in your page HTML markup. The ObjectDataSource control provides the glue, like all other Data Source controls, between your object(the data source) and your UI elements, such as a GridView. |
|
| The ObjectDataSource provides the means to further abstract your application from the specifics of your data storage strategy. With the SQLDataSource, if you want to insert business logic, you would have to either write it in SQL or use a managed CLR object in the database. |
|
| The ObjectDataSource, however, enables you to create middle-tier objects to interact with, which can implement more sophisticated business logic shared across all aspects of your user interface. This type of strategy is even more important if you are building an application that might be expose multiple UI faces – for example, an application that has a Smart Client, a Web, and a Mobile client. |
|
| Like other DataSource controls, ObjectDataSource supports the full range of parameters to pass information to selects, updates, and deletes. The ObjectDataSource also extends the capability to utilize the Cache for information returned from the Select method. You can also still get access to the powerful event model, which can be used to validate query parameters and handle errors that occurred in the process of executing the queries. |
|
| Keep in mind that this control can be used for binding to almost any data object; it’s not limited to binding to only data retrieved from a database. You can bind to data that is calculated, generated, or pulled from a remote Web Service. You can also use this control to call your own utility methods or pass through to built-in methods. |
|
| For example, using the new GetSchema support in ADO.NET, you can create a drop-down of every table in your database. This would be appropriate for utility-type applications, and demonstrate the value of being able to invoke a broad number of object methods and to facilitate binding to the data returned by them. |
|
| Key Properties of the ObjectDataSource Control |
|
| The following table described a subset of the properties of the ObjectDataSource control and some associated comments regarding their use. |
|
| Property | Description/ Comments |
| ConflictDetection | Use to CompareAllValues or OverwriteChanges. |
| DataObjectTypeName | Partially or fully qualified type string that specifies the data type that can be used as a parameter for the various commands. |
| SelectMethod | Identifies the method to be called when select action is invoked on the data source control. This method can return an IEnumerable or a DataSet. If the method returns a DataSet, it can be cached using the automatic caching capability using EnableCaching=true. |
| UpdateMethod | Identifies the method to be called when an update is required by the data source. Parameters to this method are identified by the UpdateParameters collection. |
| InsertMethod | Method called to insert data; parameters are identified by the InsertParameters property. |
| DeleteMethod | Method called to delete data; parameters are identified by the DeleteParameters property. |
| MaximumRowsParameterName | Identifies the parameter on the SelectMethod that will identify the maximum number of rows to retrieve when data paging is enabled. |
| SelectCountMethod | Method that ObjectDataSource will call to get a count of rows. |
|
|
| Key Events on the ObjectDataSource |
|
| The following table represents a subset of the public events on the control: |
|
| Property | Description/ Comments |
| Deleted | Occurs after the delete operation has already been completed. |
| Deleting | Occurs before delete operation happens. |
| Inserted | Occurs after the new data has been inserted. |
| Inserting | Occurs prior the insert operation happening. |
| ObjectCreated | Occurs after the object identified by TypeName is created. |
| ObjectCreating | Occurs before the object identified by TypeName is created. |
| ObjectDisposing | Allow notification that the TypeName object is being disposed. |
| Selecting | Occurs before the select operation is performed. This would be a good place to validate that parameters for select are valid. You can use the Cancel flag in the EventArgs if you need to cancel the query, as demonstrated earlier in the SQLDataSource control. |
| Selected | Occurs after the select operation has completed. This is a good place to handle errors that were raised as a result of the query. |
| Updating | Occurs before the update is invoked. |
| Updated | Occurs after the update is invoked. |
|
|
| Note that events are only called if you are not using Static methods. If you are using static methods, then the events are not invoked as part of the processing life cycle. |
|
| Table Adapter and Typed DataSets |
|
| The typed DataSet designer has been reinvented for Visual Studio 2005 and is now separate from the XML schema editor. While having a schema for the typed dataset is still a cornerstone of its implementation, it was important to enable the DataSet designer to evolve on a separate course. |
|
| One of the architectural decisions you will make in building an application is whether you use typed dataset in your application. In addition to the capability to define the DataSet and related tables, you can now define a type-safe data access layer using TableAdapters. A simple way of looking at a table adapter is to think of it as a typed-safe DataAdapter. |
|
| By default, the TableAdapter will inherit from Component and will encapsulate a Data Adapter. This means that while it looks and smells like a DataAdapter, you will get an InvalidCastException if you try to cast a TableAdapter to a DataAdapter object. Note that you can change the base class of the generated TableAdapter to be a custom project-specific class that inherits from Component. |
|
| You do this by setting the base class property in Dataset Designer. Doing this in a project would allow common capabilities to be shared across all of your TableAdapters. This is a great way to make sure your favorite helper methods are always available. |
|
| When you add a DataSet to your project and select the Configure option on a table, the TableAdapter Configuration Wizard will help you add methods to the typed DataSet. If you already have a Dataset defined you can still leverage the TableAdapter Configuration Wizard on existing datasets. |
|
| Once configured and defined, a Typed Dataset can be utilized on a page by dragging an ObjectDataSource onto the page and pointing it at the Typed Dataset. |
|
| From an architectural perspective, this technique provides one more layer of separation than is provided by directly using the SQLDataSource. By having that layer of abstraction, you could also share your typed DataSets easily across projects and gain more continuity in data access. |
|
| Without getting stuck on the debate of whether an object or a typed dataset approach is better architecture, there is a general consensus that using either is more robust than doing direct SQL queries from the page, as SQLDataSource encourages. |
|
| Generating DataSource Controls |
|
| You might have noticed that the data source concept does a good job of reducing the amount of code; you have to write to get your data to show on the screen. It also provides the flexibility to choose an approach that is appropriate for your application architecture. |
|
| What you might not realize yet is that there is very little out of the box that helps you avoid creating these data sources repeatedly. If you had 10 pages that needed a drop-down list containing states or countries, you would be defining 10 Data Source controls, one on each page. |
|
| At first, this might not sound all that bad, and in fact it might not even be required all at once – it might creep up on you during the maintenance of your application. Granted, some of this could be dealt with by using User Controls to create page fragments that you reuse on each of the 10 pages, but that approach is not always appropriate. |
|
| One of the more powerful solutions you have for this problem is custom data sources in a library that can be reuse across the project. You can further enhance that idea by combining your custom data sources with a powerful code generator such as Code Smith, or use other custom techniques – for example, XML/XSLT or a custom application built to generate the controls. These could be configured using metadata from either your object model or data model. |
|
| Once you create this library, you could register it on the ToolBox and then your developers would have a simple drag-and-drop approach to adding preconfigured DataSource controls to their pages. This would reduce the redundant work, and it would promote consistency across the pages that use the same type of control. The benefits would likely pay off the first time you had to do maintenance and only had to change one control instead of 10 pages. |
|
| So what do we mean by the code generation? Well, typically programmers re more and more developing code that will go out and do the data access for you. You see a lot of this directly in Visual Studio 2005, where the wizards are generating code that saves you time and yet still preserves the type-safe nature that promotes quality code. |
|
| If you are already using some form of code generation for your data access layer, you could expand the capabilities of your code-generation process to also generation process to also generate a DataSource class. |
|
| For example, if you are using SQLDataSource, you could generate a ProductDataSource that inherits from SQLDataSource but also automatically sets its properties to be specific to a product Data Source. This can then be added to the Toolbox and less experienced developers could drag and drop them onto a page for use. This would avoid having the developer reinvent the definition on each page where a similar Data Source is used. |
|
| You don’t have to do code generation to use this concept. The key is building a common library that contains customized versions of the data source controls, whether you are using a SQL data source, the XML data source, or the object data source. These are then referenced and used in your ASP.NET applications. |
|
| Using this custom library, you can derive your own custom class from a SQL data source, aXML data source, or any of the data source controls. Leverage the power of what was already created in terms of the functionality of those controls, but name them in a way that would be meaningful to your development team. Why would you want to do this? By promoting consistency and ease of use, you will see the reward in a more maintainable system that gets to market faster. |
|
|
|
|
|
| Data Bindings |
|
| Windows Forms Application |
|
| Windows Forms applications continue to evolve their data-binding strategy with .NET 2.0,including a reduction in complexity, which enables a developer to more easily, get the data to show on the form. ASP.NET data binding focuses on binding only the data-oriented properties – Windows Forms enable you to bind almost any property of a control to a data source property. This rich capability can be used to control not only the data that is displayed, but the size, color, and many other aspects of the control. |
|
| Keep in mind the difference between the execution environments for ASP.NET and a Windows Forms application. These differences are most noticeable in the way that the Windows Form application is built on a non-request /response basis. This leverage the fact that it is allowed to maintain state easily between user interaction with controls. |
|
| Added to Visual Studio 2005 is a new Data Source window that will act as the focal point for all data sources defined to your Windows Form project. Using the Data Source Configuration Wizard, it is easy to add new sources and configure them for your project. |
|
| Sources for binding are very flexible, requiring the minimal implementation of the IList interface or one of the ADO.NET data containers such as the DataTable, DataView, etc. The Data Source Window is not just limited to Database – related binding, as you will see when we describe further details about how it works. |
|
| It will be clear from your first impression that typed Datasets have improved considerably in the area of usability, and that they are here to stay. In fact, there is no equivalent to theASP.NET SQLDataSource control that encourages direct database access from your form. You are, however, encouraged to use the Data Source Window and to bind to data that is retrieved either via your own custom object or through a Data Component (a typed Dataset and a type-safe DataAdapter). |
|
| BindingSource is a new component control that is designed to simplify binding to data, including typed Dataset, Web Services, and custom business objects. Paired with the BindingSource is a new control called the BindingNavigator that provides VCR-like navigation on your BindingSource. |
|
| One major change in Windows Form application is that designer-generated code is no longer intermixed with your code, but instead placed in a formname.designer.cs file. This eliminates the problem in prior versions whereby the generated code could possibly step on your custom code. |
|
| This capability is supported thanks to the new support for partial classes that was added in.NET 2.0. If you are interested in what happens under the hood, take a peek and browse through the file as you make changes to your application. That can be a great way to understand how some of the parts work together. |
|
| Another area highlighting the difference between the capabilities of ASP.NET and Windows Forms involves the loading and saving the data. In an ASP.NET application, the DataSource controls manage the load and save of the data. |
|
| The BindingSource, while it will pull data from the source, it expects that you have loaded the data into it. Likewise, it expects you to manage the persistence of the data, because you might not necessarily be binding to a persistable object. Ultimately, you are responsible for the code to populate and load the object that is associated with the BindingSource that you are using. |
|
| As you start to explore how binding in Windows Forms works, remember that all of this happens in real time and no request / response happens like it does in an ASP.NETapplication. With that in mind, let’s start exploring the changes in more details: |
|
| Where Did My Data Components Go? |
|
| Prior to Visual Studio 2005, the toolbox used to have a Data section that contained SQLConnection, SQLCommand and so on. These low-level components are no longer available on the toolbox because they promote use of untyped components and the Windows Forms team is pushing hard to convert people to the new model. You will notice that after you add a typed Dataset and TableAdapter to your project, they will automatically show up in the toolbox after your next project build. |
|
| The underlying components such as SQLConnection and others are still around, and if you are an old dog that can’t learn new tricks, you can add them back to your toolbox manually. Doing so, however, would be like doing math with an abacus instead of a calculator. Using the typed capabilities or object reference, which are also typed, will promote more robust applications. |
|
| Dragging and Dropping a Table |
|
| You already saw in ASP.NET that if you drag a table from Server Explorer, it generates a bunch of code for you, but it is not very robust or useful in production application. The Windows Forms team took a different approach. They decided not to provide this capability and instead want you to add a new Data Source in the Data Source window. |
|
| You can also drag and drop a table from the Server Explorer window onto the DataSet designer surface and it will create the table in the DataSet and create an associated TableAdapter that contains the basic Fill method. This will also cause it to show up in your Data Source window. |
|
| Once you have defined an item to the Data Source window, you then have the ability to drag and drop that on a from, after which automation will kick into add a DataGridView and associated binding objects that are appropriate. |
|
| If you take a second and think about it, this is actually a much more logical process because not only does it allow database-related Data Sources, it also allows for other sources to be corralled into a single window that manage their binding options and allows for drag and drop onto a form. |
|
| Data Sources |
|
| The Data Source window is new in 2.0 and provides a central focal point for your Data Sources. Unlike Server Explorer, which is not project-specific, the Data Source window only contains data sources that are specific to your active project. |
|
| If you are confused about how the Data Source window differs from the Server Explorer window, think of Server Explorer as a list of all your raw database sources. This could be generated from multiple servers. A table in a database on Server Explorer might contain 10 columns, of which only five are used on the data source when it is defined on the Data Source window. |
|
| It is further possible that Table A and Table B are joined together to create a single data source on the Data Source window, whereas they physically exist as two tables on the Server Explorer database list. Items that appear in the Data Source Window are not just database-related data source. They can also be objects or collections of objects that were configured by the Data Source Configuration Wizard. |
|
| Unlike ASP.NET, there is no set of “Data Source” controls that you drag onto your form. Data Sources are interacted with using the new Data Source window, which enables you to drag and drop all or part of the given Data Source onto your form surface. The following table describes the data source type supported: |
|
| Data Source | Description/ Comments |
| Database | Allows binding via typed datasets to an SQL provider. |
| Web Service | Allows for binding to a Web Service via adding a reference. |
| Object | Allows for referencing an object and easy binding. |
|
|
| The Windows Form UI Controls |
|
| The following table describes two of the new controls in visual studio 2005 that are designed to leverage the capabilities of the data source concept for data binding: |
|
| Control | Description/ Comments |
| DataGridView | Replaces the DataGrid control from previous versions and adds additional capabilities. |
| BindingNavigator | The BindingNavigator control provides VCR- like controls for navigating through either a grid or detail view of the data. |
|
|
| BindingSource |
|
| The BindingSource control is a component control that is the workhorse of the data-binding engine in a window form application. The BindingSource is the glue between a data source and the UI element that it is binding to, and it acts as an intermediary to ensure a smooth two-way flow of data. The BindingSource is a nonvisual control and doesn’t render any user interface itself. |
|
| By design, the BindingSource provides simplification by shielding your form having to deal directly with concurrency management, change notification and other services, which are encapsulated in the BindingSource control. |
|
| If you were to try to find the ASP.NET equivalent, the closest you would get is the DataSource controls (SqlDataSource, ObjectDataSource, etc.). These provide similar glue between the user interface element and the item acting as the data source. |
|
| It’s possible you have used this control and didn’t ever know it. Each time you drag a new item from the Data Source window onto the form surface, Visual Studio 2005 will create a BindingSource object instance for you and place it in the component area at the bottom of the form. |
|
| The following table describes some of the key properties of the BindingSource component: |
|
| Property | Description/ Comments |
| AllowNew | True when the user is allowed to add new rows to the BindingSource. If the data source implements IBindingList, the data sources will determine the value for this property. This is auto-set to false if the data source is read-only, fixed-size, or doesn’t have a default constructor. |
| Current | Gets the current item from the data source. |
| Filter | If the data source implements the IBindingListView, a filter string can be set to filter the list. A NotSupported exception will be thrown if the data source doesn’t support filtering. |
| DataSource | Establishes the actual data that will be managed by the BindingSource. If the object referenced contains multiple lists (e.g. DataSet), you must also set the DataMember property to indicate the specific list within the source you wish to manage. |
| DataMember | Specifies the list within a data source that the BindingSource will manipulate. |
|
|
| BindingList |
|
| BindingList is a generic implementation of the IBindingList interface and focuses on making binding to business object easier. Generics are a new feature in .NET 2.0 that enables building strongly typed collections with less code. The following example code builds a BindingList specifically for the object Event, and ensure that all instances returned or added to the collection are of that type: |
|
| BindingList<Event> bindingList = new BindingList<Event> (); |
|
|
|
|
|
| Binding Controls to Database |
|
| Simple Binding |
|
| Simple binding is a general way to bind a property on a user interface element (Control) to a property on an instance of a type (object). For example, if a developer had an instance of a Customer type they could bind the Customer "Name" property to the "Text" property of a TextBox. |
|
| When "binding" these two properties, changes to the TextBox.Text property will be propagated to the Customer.Name property and changes to the Customer.Name property will be propagated to the TextBox.Text property. Windows Forms simple binding supports binding to any public or internal .NET Framework property. |
|
|
|
|
|
|
|
|
| Binding Controls to Database |
|
| Complex Binding |
|
| Complex binding is a way to bind a list based user interface element (e.g. ComboBox, Grid) to a list of data instances (e.g. DataTable). Like simple binding, complex binding is generally two-way in that changes to the user interface element are propagated to the data list and changes to the data list are propagated to the user interface element. Windows Forms complex binding supports binding to data lists that support the IList interface (or IEnumerable when using a BindingSource component). |
|
| Property | Description/ Comments |
| DataSource | The data source, typically a dataset, such as DataSet11. |
| DataMember | The data member you want to work with in the data source, typically a table in a dataset, such as the authors table in the Pubs database. Data grids use this property to determine which table they should display. |
| DisplayMember | The field you want a control to display, such as the author’s last name, au_lname. List boxes use the DisplayMember and ValueMember properties, instead of a DataMember property. |
| ValueMember | The field you want the control to return in properties, like SelectedValue, such as au_id. List boxes use the DisplayMember and ValueMember properties, instead of a DataMember property. |
|
|
|
|
|
|
| Binding Controls to Database |
|
| Binding Data to Controls |
|
| We are doing here is using simple and complex databinding as we have already seen. In this case, we have created a dataset, DataSet11, and filled it with data from the authors table in the Pubs database, and bound it to various controls. |
|
| For example, we have bound the Text property of the ‘textbox2’ to the authors.au_phone field, the checked property of the check box to the authers.contract field (which is a Boolean field type, holding True/False values), the entire authors table to the data grid at bottom, the DisplayMember of the checked list box to the authors.au_lname field and so on. |
|
| This works fine, and you will see the data you have bound to the various controls when you run the program. But there’s a problem – the simple-bound controls, such as text boxes only display one data item (for example, the current author’s phone number) at a time. How can the user move to the next record? |
|
|
|
|
|
| Binding Controls to Database |
|
| Navigating in Datasets |
|
| Displaying the Current Location |
|
| When the forms load , it fills its dataset from its data adapter. It displays the current position in the dataset’s binding context for the authors table by using the form’s BindingContext property’s Position and Count members, like this: |
|
private void Form1_Load(object sender, EventArgs e)
{
…
ToolStripStatusLabel1.Text = “You are currently at record ” + Convert.ToStrin
g (this.BindingContext[DataSet11, “authors”].Position + 1) + “of” +
Convert.ToString(this.BindingContext[DataSet11, “authors”].Count);
…
}
|
|
| This code gives you the information about the position of current record in the Status bar when the user moves through the dataset. |
|
| Moving to the Next Record |
|
| So, how does the user actually moves through the dataset? Suppose we use an arrow (>) button to navigate DataSet; when the user clicks the > arrow, the code just increment the Position property of the binding context for the authors table, and then displays the new location in the label between the navigation buttons: |
|
private void Button1_Click(object sender, EventArgs e)
{
this.BindingContext[DataSet11, “authors”].Position
= (this.BindingContext[DataSet11, “authors”].Position + 1);
ToolStripStatusLabel1.Text = “You are currently at record
” + Convert.ToString
(this.BindingContext[DataSet11, “authors”].Position + 1) + “of” +
Convert.ToString(this.BindingContext[DataSet11, “authors”].Count);
}
|
|
| Note that if you try to move beyond the end of the recordset, the Position property isn’t incremented. |
|
| Moving to the Previous Record |
|
| In the same way, when the user clicks the arrow (<) button to move to the previous record, the code simply decrements the Position value: |
|
private void Button2_Click(object sender, EventArgs e)
{
this.BindingContext[DataSet11, “authors”].Position
= (this.BindingContext[DataSet11, “authors”].Position - 1);
ToolStripStatusLabel1.Text = “You are currently at record ”
+ Convert.ToString (this.BindingContext[DataSet11, “authors”].Position + 1)
+ “of” + Convert.ToString(this.BindingContext[DataSet11, “authors”].Count);
}
|
|
| Moving to the First Record |
|
| Moving to the first record in the binding context for the authors table is easy; you just have to set the Position property to 0: |
|
private void Button1_Click(object sender, EventArgs e)
{
this.BindingContext[DataSet11, “authors”].Position = 0;
ToolStripStatusLabel1.Text = “You are currently at record
” + Convert.ToString (this.BindingContext[DataSet11, “authors”].Position + 1)
+
“of” + Convert.ToString(this.BindingContext[DataSet11, “authors”].Count);
}
|
|
| Moving to the Last Record |
|
| Moving to the last record is also easy, because we know that the Count property returns the total number of records in the table: |
|
private void Button1_Click(object sender, EventArgs e)
{
this.BindingContext[DataSet11, “authors”].Position =
(this.BindingContext[DataSet11, “authors”].Count - 1);
ToolStripStatusLabel1.Text = “You are currently at record
” + Convert.ToString (this.BindingContext[DataSet11, “authors”].Position + 1)
+ “of” + Convert.ToString(this.BindingContext[DataSet11, “authors”].Count);
}
|
|
| And that gives us a good introduction to using navigation controls to move around in the dataset simply bound to controls. You might also note that even the controls that are complex-bound display the current binding context record; the current record is highlighted in the checked list box and in the list box. Also, the datagrid at the bottom of the figure displays a small arrow indicating the current record. |
|
|
|
|
|
| Binding Controls to Database |
|
| Using SQL Parameters |
|
| The ParameterizedQueries uses an SQL parameter in a data adapter, which is much like a variable in other programming languages. Here’s the SQL – in this case, we are using a parameter, indicated by the variable named param9, for the state field in a WHERE clause in the SQL for the second data adapter: |
|
SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract
FROM “ & ” authors
WHERE (state = @Param9)
|
|
| The question is how do you place a value into the SQL parameter corresponding to the state field at runtime? That turns out to be easy enough – you just refer to that parameter as oleDbDataAdapter1.SelectCommand,Parameters (“@param9”). If we will set this parameter to the state the user has selected in a combo box then: |
|
private void ComboBox1_SelectedIndexChanged( object sender, EventArgs e)
{
SqlDataAdapter2.SelectCommand.Parameters
[“@param9”].Value = Convert.ToString(ComboBox1.Text);
myset1.Clear();
SqlDataAdapter2.Fill(myset1);
}
|
|
|
|
|
|
| Handling Database in Code |
|
| The OleDbConnection Class |
|
| The OleDbConnection object supports a connection to an OLEDB data provider. In practice, you usually use OLE DB connections with all data providers except Microsoft’s SQL Server. Note that, depending on the OLEDB data provider, not all properties of an OleDbConnection object may be supported. |
|
| A central property of connection objects is the ConnectionString property, which holds a string full of attribute/ value pairs that contain data needed to log on to a data provider and choose a specific database. |
|
| These attribute/value pairs are specific to the data provider you are using, and creates a list of items separated by semicolons. You can either assign a connection string to the connection’s ConnectionString property, or you can pass the connection string to the connection object’s constructor, like this: |
|
string ConnectionString = “Provider=sqloledc;
Data Source= ‘Ujjwal22’; Initial Catalog= pubs; User Id= sa; Password= ‘’”;
System.Data.OleDb.OleDbConnection
connection1 = new System.Data.OleDb.OleDbConnection (ConnectionString);
|
|
| If you have no idea what a connection string should look like a specific data provider and database, use the visual tools built into Visual C# to construct a few sample strings to that data provider, which you can either use directly in the code or modify as you need. |
|
| To do that, create a connection to the source you want to use then drag a data adapter to a project’s main form, which creates both data connection and data adapter objects. Then look at the connection object’s ConnectionString property in the property window. |
|
| Note: the most common attribute/ value pairs used in OLE DB connection strings are also supported with properties of connection objects, such as DataSource, DataBase, UserId, and Password, which means that when you work with a connection object, you can either set the ConnectionString property as a string, or you can set various connection properties one-by-one and let Visual C# creates a connection string for you. |
|
| After you have created a connection object, you can open it with the Open method, and assign it to the Connection property of a command object. Then you can use the command object with a data adapter. |
|
| For example, you might assign the command object to the SelectCommand property of a data adapter, and you can use a data adapter’s Fill method to execute that command and fill a dataset. When done with the connection, use its Close method to close it. |
|
|
|
|
|
| Handling Database in Code |
|
| The SqlConnection Class |
|
| A SqlConnection object supports a connection to a SQL Server data source. For the most part, the difference between SqlConnection and OleDbConnection object take place behind the scenes, and the programming interface of these two types of objects is very similar. |
|
| The main difference between SqlConnection and OleDbConnection objects lie in the performance – SQL connections to the Microsoft SQL Server have been found to be up to 70 per cent faster than OLE DB connections. So if you are using SQL Server, consider usingSQL connections for all your connections. |
|
string ConnectionString =
"Data Source=(local);" +
"Initial Catalog=myDatabaseName;" +
"Integrated Security=SSPI";
//Or
// "Server=(local);" +
// "Database=myDatabaseName;" +
// "Trusted_Connection=Yes";
SqlConnection SQLConn = new SqlConnection(ConnectionString);
|
|
|
|
|
|
|
| Handling Database in Code |
|
| The OracleConnection Class |
|
| An OracleConnection object supports a connection to an Oracle data source. While working with the OracleConnection object, you should always close any open object explicitly in your code. |
|
| If you don’t close any open OracleConnection object, then this task will be assigned to garbage collection of framework which might not dispose this object immediately and you might encounter a maximum connection error. Also keep in mind that if you are planning to use connection pooling with OracleConnectionobejct, you don’t need to do anything extra; this will be automatically handled by the provider. |
|
string ConnectionString =
"Data Source=Oracle8i;" +
"Integrated Security=SSPI";
OracleConnection oOracleConn = new OracleConnection(ConnectionString);
|
|
|
|
|
| Handling Database in Code |
|
| The OleDbCommand Class |
|
| Command object represent SQL commands or SQL stored procedures that you execute in a database. For example, to retrieve the data from a database, you can create a connection object, open the connection with its Open method, and then assign the open connection object to the Connection property of a command object. |
|
| You can then assign the command object to a command property of a data adapter, such as the SelectCommand property. Besides the SelectCommand property, data adapter also support UpdateCommand, InsertCommand and DeleteCommand properties, each of which takes connection objects that perform these various functions. |
|
| As you can guess, you use OleDbCommand objects with OLE DB connections, and SqlCommand objects with SQL Server connections. How do you place the SQL you want to use in a command object? You can either assign that text to the command object’s CommandText property, or you can pass it to the command object’s constructor, like this, where we are selecting all the records in the AdventureWorks database’s Contact table: |
|
System.Data.OleDb.OleDbCommand command1 = new
System.Data.OleDb.OleDbCommand (“SELECT * FROM Contact”); |
|
| Now we can set the type of the command, which, for SQL statements, is CommandType.Text(this is the default), and assign an open connection to the command’s Connection property: |
|
System.Data.OleDb.OleDbCommand
command1 = new System.Data.OleDb.OleDbCommand (“SELECT * FROM Contact”);
Command1.CommandType = CommandType.Text;
Connection1.Open();
Command1.Connection = connection1;
|
|
| Now this command object is ready to go. In this case, you can assign it to a data adapter’s SelectCommand objects built-in methods to execute the commands in a database, (no data adapter needed). These built-in methods are as follows: |
|
| ? ExecuteReader – It executes SQL commands that return rows. Note that this method doesn’t return a dataset, it creates a data reader, which is much more simplistic. |
|
| ? ExecuteNonQuery – It executes a command that doesn’t return data rows (such as SQLINSERT, DELETE, UPDATE and SET statements). |
|
| ? ExecuteScalar – It calculates and returns a single value, such as a sum, from a database. |
|
|
|
|
|
| Handling Database in Code |
|
| The SqlCommand Class |
|
| SQLCommand objects are very nearly the same as OleDbCommand objects, except they are designed to be used with SQL Connections, not with OLE DB connections. You can use them in the same way as outlined in the previous topic, and SQLCommand objects support all the methods listed in that topic, as well as one more – ExecuteXmlReader – which creates an XMLReader object that makes handling the database with XML easy. |
|
|
|
|
|
|
|
|
|
|
|
| Handling Database in Code |
|
| The OracleCommand Class |
|
| OracleCommand objects are very nearly the same as OleDbCommand and SQLCommand objects, except they are designed to be used with Oracle database. Please note that unlike the SQL Server, OLE DB , and ODBC command objects, OracleCommand doesn’t support CommandTimeOut property. If you try to set this property, it will have no effect and will always return zero. |
|
|
|
|
|
|
|
|
|
|
|
| Handling Database in Code |
|
| The OleDbDataAdapter Class |
|
| OleDbDataAdapter objects act as a bridge between datasets and data sources. As you know, datasets are really just repositories of data; they are not directly connected to a database. OleDbDataAdapter objects connect datasets and data sources by supporting the Fill method to load data from the data source into the dataset, and the update method to send changes you have made in the dataset back to the data source. |
|
| After you have created a data connection and used it to create a command object, you can assign the command object to one of the command properties of the data adapter – SelectCommand, InsertCommand, DeleteCommand, and UpdateCommand. These commands are used as needed by the data adapter. |
|
| You also have to specify a table mapping when creating a data adapter object. The names of the tables you use in a dataset can be different fro those in the database, depending on how you have named them, and a table mapping relates the table names in the database to the names in the dataset. For example, here’s how we connect the tables in the database to names we have given them in the dataset: |
|
DataTableMappingCollection Table1Mappings = new DataTableMappingCollection();
TableMappings.Add(“authors”, “writers”);
TableMappings.Add(“publishers”, “company”);
|
|
| If you don’t specify a TableName or a TableMapping name when calling the Fill or Update method of a data adapter, the data adapter searches for a TableMapping object named “Table”. If it doesn’t find that object, the data adapter uses the name “Table” for the data source table , and that means you can create a default table mapping by creating a Table-Mapping object using the table name “Table”. |
|
| For example, here’s how we create a new OleDbDataAdapter object; set up the Select Command object it should use to populate datasets, create a default table, and fill a dataset named ds with the authors table, by using this adapter: |
|
OleDbDataAdapter OleDbDataAdapter1 = new OleDbDataAdapter();
OleDbDataAdapter1.SelectCommand = Command1;
OleDbDataAdapter1.TableMappings.Add(“Table”, “authors”);
OleDbDataAdapter1.Fill(ds);
|
|
|
|
|
|
| Handling Database in Code |
|
| The SqlDataAdapter Class |
|
| The SqlDataAdapter class is the SQL Server counterpart of the OleDbDataAdapter class. Like the OleDbDataAdapter class, the SqlDataAdapter class includes the SelectCommand, InsertCommand, DeleteCommand, UpdateCommand and TableMappings properties that you use for loading and updating data. |
|
|
|
|
|
|
|
|
|
|
|
|
| Handling Database in Code |
|
| The DataSet Class |
|
| We have already worked a good deal with the DataSet class, which is how datasets are supported in Visual C# 2005. A dataset is a cache of data retrieved from the database, and as we know, it’s the major component of ADO.NET. |
|
| A DataSet object is made up of a collection of DataTable objects that you can relate to each other using DataRelation objects. You can also guarantee data integrity with the UniqueConstraint and ForeignKeyConstraint objects. |
|
| Dataset reads and writes data and schema as XML documents, which can be transported using the HTTP protocol, and this makes it great for the Internet. You can save the schema as an XML schema with the WriteXmlSchema method, and the schema and data can be saved by using the WriteXml method. If you need to read an XML document that includes both schema and data, use the ReadXml method that infers and creates a schema from the document. |
|
| When the user edits data in data-bound controls, changes are made to the dataset’s data immediately. You can use the GetChanges method to get a new dataset holding only the rows that have changed, and you typically send this new dataset to the database in the data provider with the data adapter’s Update method. |
|
| The data provider may make changes itself in the dataset of changes you send it and return a new dataset, which you can then merge into the dataset you are working with, using the dataset’s Merge method. Then you use the AcceptChanges method on the original dataset to accept the changes (and use RejectChanges to cancel the changes). |
|
| Datasets can be typed or untyped. Usually, datasets are typed in Visual C#. a typed dataset is a dataset that is derived from the DataSet class and uses information in an XMLschema file (an XSD file). An untyped dataset , on the other hand, has no built-in schema. An untyped dataset can contain tables, columns, and rows, but those are exposed only as collections. |
|
|
|
|
|
| Handling Database in Code |
|
| The OleDbDataReader Class |
|
| The OleDbDataReader class gives you a way of reading a forward-only stream of data rows from a database. Because this stream of data is ‘forward-only’, you can read rows only one after the other, and can’t choose randomly any row you want, or go backward. |
|
| Data readers are really low-level objects that give you direct access to the data in a way that’s not as structured as a dataset, but with faster access. You use this class, the OleDbDataReader class, with OLE DB providers. |
|
| To create an OleDbDataReader object, you call the ExecuteReader method of an OleDbCommand object – you don’t use a class constructor. You use the Read method of a data reader to read a new row from a database; you can use methods like GetString, GetInt32, and GetBoolean to read the values of the individual fields in the row, one after the other. You can also read a database’s XML schema to determine field names and types. |
|
|
|
|
|
| Handling Database in Code |
|
| The SqlDataReader Class |
|
| The SqlDataReader class is the SQL Server version of the OleDbDataReader class. There are very few differences between the SqlDataReader class and the OleDbDataReader class; for most practical purpose, you use them the same way – you use the Read method to read a new row from the database, and as with the OleDbDataReader class, you can use methods like GetString, GetInt32, and GetBoolean to read the values of the individual fields in the row in succession. |
|
|
|
|
|
|
|
|
|
|
| Handling Database in Code |
|
| The DataTable Class |
|
| DataTable objects store data tables, and as such, they are central to datasets and data views. In code, you create data tables and then add the fields in each row to them. |
|
DataTable Table1 = null;
Table1 =new DataTable(“Employees”);
DataColumn FirstName = new DataColumn(“First Name”);
FirstName.DataType = System.Type.GetType(“System.String”);
Table1.Columns.Add(FirstName);
.
.
.
|
|
| To add rows to a DataTable, you use the NewRow method to return a new DataRow object, because the NewRow method returns a row with the schema of the DataTable. Then you add data to the fields in the row, referring to them by name or index, and the row back to the table’s Rows collection. For example – |
|
row1 = Table1.NewRow();
row1[“First Name”] = “Ujjwal”;
row1[“Last Name”] = “Mehta”;
row1[“ID”] = 1;
row1[“Phone”] = “(91) 0120-4352941”;
Table1.Row.Add(row1);
|
|
| How do you bind a table in code to a control like a data grid to display it? You can add it to a dataset, and bind that dataset to the data grid, like this: |
|
DataSet ds = new DataSet();
ds = new DataSet();
ds.Tables.Add(Table1);
DataGrid1.SetDataBinding(ds, “Employees”);
|
|
| To find out when changes are made to a table, you can use one of the following events: |
|
? RowChanged
? RowChanging
? RowDeleting
? RowDeleted |
|
|
|
|
|
| Handling Database in Code |
|
| The DataRow Class |
|
| DataRow objects represent rows in a DataTable object. You use DataRow objects to get access to, insert, delete, and update the records in a table. |
|
| To create a new DataRow object, you usually use the NewRow method of a DataTable object, and after configuring the row with data, you can use the Add method to add the newDataRow to the table. In addition, you can also call the AcceptChanges method of the DataTable object to make that table treat the new row as it would to its original data. |
|
| You can delete a DataRow form the Rows collection in a data table by calling the Delete method of the DataRow object itself. Note that the Delete method simply marks the DataRow for deletion. (The actual deletion occurs when you use the AcceptChanges method.) |
|
| So how do you actually get the data values stored in a particular field in a row? You can use the Item property, referring to the field by name or index. |
|
|
|
|
| Handling Database in Code |
|
| The DataColumn Class |
|
| DataColumn objects represent the columns, i.e. the fields in a data table. In ADO.NETterms, the columns in a table specify its XML schema. When you create a table and add column to it, you specify the name of the column and the type of data it stores. |
|
|
|
|
|
|
|
|
|
|
|
|
| Handling Database in Code |
|
| The DataRelation Class |
|
| DataRelation objects relate two data table objects to each other through the use of DataColumn objects. Datasets are just simple data repositories, and when you load them, they don’t know anything about the relations between tables in a relational database. To make those relations explicit, you have to use DataRelation objects. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
0 comments: