Technology Excellence  |  General Industry Practice  | Healthcare IT Solutions | Profile | Route Map | Site Map | Contact Us | Search Home Healthcare Apps Solutions Projects ASP.NET - Data Access and Storage Web applications commonly access data sources for storage and retrieval of dynamic data. You can write code to access data using classes from the System.Data namespace (commonly referred to as ADO.NET) and from the System.Xml namespace. This approach was common in previous versions of ASP.NET The following Contents describes the access data sources for storage. Please fill up the form below and we will submit a proposal for your project. Alternatively, you can send email to contact@optionm.net with the project requirements. Contents Database Overview ASP.NET Data Access Overview Securing Data Access Using SQL Server Express Edition with ASP.NET About Extensible Storage Engine Database Overview The ESE database is an indexed sequential access method (ISAM) for storing and retrieving data. An ESE database is stored in a single file and consists of one or more user-defined tables. Data is organized in records in the table with one or more user-defined columns. Indexes that are created provide different organization for the entire set or a subset of records in the table. Using the ESE API, applications can create cursors that navigate records in the database in different sequential orders. The elements of the table are defined below: Column: The column is a field in the table that stores a specific type of information. Columns can be fixed, or variable length, depending on the data type stored in them. Some columns, such as tagged columns take no space when NULL or set to the default value, and can contain multiple values. Records: A record is a collection of columns values that have a unique identity as defined by the primary key. Index: The index is a collection of key columns that define a stored ordering of records in the table. The clustered, or primary, index defines the order in which the records are stored within the table. Multiple indices can be defined in order to specify different orderings of traversal through records in the table. An index may also limit the set of records visible based on simple criteria such as the presence or absence of a particular key column value in the record. Cursor: The cursor indicates the current record in the table and navigates to records in the table using the current index. The cursor also contains information on the state of the currently prepared update. Columns and indices may be added-to or removed-from the table at any time. Although multiple indices may be defined, the data in the table is physically stored and logically clustered according to the primary index definition in a B+ tree. Each secondary index is stored in a separate B+ tree that contains only logical pointers to the actual data that is stored in the primary table. If no index is defined, the records in the table are stored in a B+ tree in the order of insertion and are referred to as the sequential index. Click here to submit your project requirements to Option Matrix, India. Back to top ASP.NET Data Access Overview Web applications commonly access data sources for storage and retrieval of dynamic data. You can write code to access data using classes from the System.Data namespace (commonly referred to as ADO.NET) and from the System.Xml namespace. This approach was common in previous versions of ASP.NET. However, ASP.NET also enables you to perform data binding declaratively. This requires no code at all for the most common data scenarios, including: Selecting and displaying data. Sorting, paging, and caching data. Updating, inserting, and deleting data. Filtering data using run-time parameters. Creating master-detail scenarios using parameters. ASP.NET includes two types of server controls that participate in the declarative data binding model: data source controls and data-bound controls. These controls manage the underlying tasks required by the stateless Web model for displaying and updating data in ASP.NET Web pages. As a result, you are not required to understand details of the page request lifecycle just to perform data binding. Data Source Controls Data source controls are ASP.NET controls that manage the tasks of connecting to a data source and reading and writing data. Data source controls do not render any user interface, but instead act as an intermediary between a particular data store (such as a database, business object, or XML file) and other controls on the ASP.NET Web page. Data source controls enable rich capabilities for retrieving and modifying data, including querying, sorting, paging, filtering, updating, deleting, and inserting. ASP.NET includes the following data source controls: Data source control Description AccessDataSource Enables you to work with a Microsoft Access database. LinqDataSource Enables you to use Language-Integrated Query (LINQ) in an ASP.NET Web page through declarative markup in order to retrieve and modify data from a data object. Supports automatic generation of select, update, insert, and delete commands. The control also supports sorting, filtering, and paging. ObjectDataSource Enables you to work with a business object or other class and create Web applications that rely on middle-tier objects to manage data. SiteMapDataSource Used with ASP.NET site navigation. SqlDataSource Enables you to work with ADO.NET managed data providers, which provide access to Microsoft SQL Server, OLE DB, ODBC, or Oracle databases. XmlDataSource Enables you to work with an XML file, which is especially useful for hierarchical ASP.NET server controls such as the TreeView or Menu control. Data-source controls can also be extended to support additional data access storage providers. Data-bound Controls Data-bound controls render data as markup to the requesting browser. A data-bound control can bind to a data source control and automatically fetch data at the appropriate time in the page request lifecycle. Data-bound controls can take advantage of the capabilities provided by a data source control including sorting, paging, caching, filtering, updating, deleting, and inserting. A data-bound control connects to a data source control through its DataSourceID property. ASP.NET includes the data-bound controls described in the following. List controls: Renders data in a variety of lists format. List controls include the BulletedList, CheckBoxList, DropDownList, ListBox, and RadioButtonList controls. AdRotator : Renders advertisements on a page as an image that users can click to go to a URL associated with the advertisement. DataList :Renders data in a table. Each item is rendered using an item template that you define. DetailsViewDisplays one record at a time in a tabular layout and enables you to edit, delete, and insert records. You can also page through multiple records. FormView :Similar to the DetailsView control, but enables you to define a free-form layout for each record. The FormView control is like a DataList control for a single record. GridView ;Displays data in a table and includes support for editing, updating, deleting, sorting, and paging data without requiring code. Note: In a postback request, if the page contains validator controls, check the IsValid property of the Page and of individual validation controls before performing any processing. ListView :Enables you to define the data layout by using templates. Supports automatic sort, edit, insert, and delete operations. You can also enable paging by using an associated DataPager control. Menu :Renders data in a hierarchical dynamic menu that can include submenus. Repeater :Renders data in a list. Each item is rendered using an item template that you define. TreeView :Renders data in a hierarchical tree of expandable nodes. Note: The DataGrid control available in ASP.NET version 1.0 and version 1.1 has been superseded by the GridView control, which includes expanded capabilities for sorting, paging, and modifying data. Existing pages that use the DataGrid control will continue to function. As with other data controls, the DataGrid control has been enhanced to interact with data source controls. Note: The ListView control supersedes the Repeater control and the DataList control. Existing pages that use those controls will continue to function. The ListView control simplifies the implementation of many common scenarios. LINQ Language-Integrated Query (LINQ) provides a unified programming model for querying and updating data from different types of data sources, and extends data capabilities directly into the C# and Visual Basic languages. LINQ applies the principles of object-oriented programming to relational data. To work with LINQ, you can use the LinqDataSource control. You can also create LINQ queries directly in order to access data from a Web page. Click here to submit your project requirements to Option Matrix, India. Back to top Securing Data Access Most ASP.NET Web applications involve data access. Many applications collect data to be stored in a database or file, and the data to be stored is often based on information that comes from users. Because the original data can come from untrusted sources, because the information is stored in a persistent format, and because you want to be sure that unauthorized users cannot access your data source directly, you need to pay particular attention to security issues surrounding data access. The information in this topic describes best practices that will help you improve the security of the data access in your ASP.NET Web application. While following coding and configuration best practices can improve the security of your application, it is also important that you continually keep your Web server up to date with the latest security updates for Microsoft Windows and Internet Information Services (IIS), as well as any security updates for Microsoft SQL Server or other data source software. Securing Access to a Data Source The following sections provide information on helping secure different aspects of data access: Connection Strings Connecting to a database requires a connection string. Because connection strings can contain sensitive data, you should follow these guidelines: Do not store connection strings in a page. For example, avoid setting connection strings as declarative properties of the SqlDataSource control or other data source controls. Instead, store connection strings in the site's Web.config file. Do not store connection strings as plain text. To help keep the connection to your database server secure, it is recommended that you encrypt connection string information in the configuration file by using protected configuration. Connecting to SQL Server using Integrated Security If possible, connect to an instance of SQL Server using integrated security instead of using an explicit user name and password. This helps avoid the possibility of the connection string being compromised and your user ID and password being exposed. It is recommended that you ensure that the identity of the process (for example, the application pool) that is running ASP.NET be the default process account or a restricted user account. In scenarios where different Web sites connect to different SQL Server databases, it might not be practical to use integrated security. For example, on Web-hosting sites, each customer is typically assigned a different SQL Server database, but all users use the Web server as anonymous users. In such cases, you need to use explicit credentials to connect to an instance of SQL Server. Be sure that you store the credentials in a secure manner. SQL Server Database Permissions It is recommended that you assign the minimum privileges to the user ID that is used to connect to the SQL Server databases used in your application. Restrict SQL Operations Data-bound controls can support a wide variety of data operations, including selecting, inserting, deleting, and updating records in data tables. It is recommended that you configure data controls to perform the minimum functionality that is required on a page or in your application. For example, if a control should not allow users to delete data, do not include a delete query with a data source control and do not enable deleting in the control. SQL Server Express Edition When a process attaches to a SQL Server Express Edition database (.mdf file), the process must have administrative permissions. In general, this makes SQL Server Express Edition databases impractical for production Web sites because the ASP.NET process does not (and should not) run with administrative privileges. Therefore, use SQL Server Express Edition databases only under the following circumstances: Use as a test database while developing your Web application. When you are ready to deploy your application, you can transfer the database from SQL Server Express Edition to a production instance of SQL Server. Use if you are running a Web site that can use impersonation and you can control the privileges of the impersonated user. In practice, this strategy is practical only if the application is running on a local area network (not a public Web site). Store the .mdf file in your site's App_Data folder, because the contents of the folder will not be returned to direct HTTP requests. You should also map the .mdf extension to ASP.NET in IIS and to the HttpForbiddenHandler . Microsoft Access Databases Microsoft Access databases (.mdb files) include fewer security features than SQL Server databases. Access databases are not recommended for production Web sites. However, if you have reason to use an .mdb file as part of your Web application, follow these guidelines: Store the .mdb file in your site's App_Data folder because the contents of the folder will not be returned to direct HTTP requests. You should also map the .mdb extension to ASP.NET in IIS and to the HttpForbiddenHandler Add appropriate permissions for the user account or accounts that will be reading and writing in the .mdb file. If the Web site supports anonymous access, this is generally the local ASPNET user account or the NETWORK SERVICE account. Because Access must create an .ldb file to support locking, the user account must have write permissions for the folder that contains the .mdb file. If the database is protected with a password, do not use the AccessDataSource control to establish a connection to it, because the AccessDataSource control does not support passing credentials. Instead, use the SqlDataSource control with the ODBC provider, and pass the credentials in the connection string. Be sure to secure the connection string XML Files If you are storing data in an XML file, place the XML file in your Web site's App_Data folder, because the contents of the folder will not be returned in response to direct HTTP requests. Guarding Against Malicious User Input If your application accepts input from users, you need to make sure that the input does not contain malicious content that can compromise your application. Malicious user input can be used to launch the following attacks: Script injection A script injection attack attempts to send executable script to your application with the intent of having other users run it. A typical script injection attack sends script to a page that stores the script in a database, so that another user who views the data inadvertently runs the code. SQL injection A SQL injection attack attempts to compromise your database (and potentially the computer on which the database is running) by creating SQL commands that are executed instead of, or in addition to, the commands that you have built into your application. General Guidelines For all user input, follow these guidelines: Use validation controls whenever possible to limit user input to acceptable values. Always be sure that the value of the IsValid property is true before running your server code. A value of false means that one or more validation controls have failed a validation check. Always perform server-side validation even if the browser is also performing client-side validation, to guard against users bypassing client-side validation. This is especially true for CustomValidator controls; do not use only client-side validation logic. Always re-validate user input in the business layer of your application. Do not rely on the calling process to provide safe data. For example, if you are using the ObjectDataSource control, add redundant validation and encoding into the object that performs the data updates. Script Injection To avoid script injection attacks, follow these guidelines: Encode user input with the HtmlEncode method, which turns HTML into its text representation (for example, <b> becomes &ltb&gt;), and helps prevent the markup from being executed in a browser. When using parameter objects to pass user input to a query, add handlers for the data source control's pre-query events and perform the encoding in those events. For example, handle the SqlDataSource control's Inserting event, and in the event, encode the parameter value before the query is executed. If you are using the GridView control with bound fields, set the BoundField object's HtmlEncode property to true. This causes the GridView control to encode user input when the row is in edit mode. For controls that can be put into edit mode, it is recommended that you use templates. For example, the GridView, DetailsView, FormView, DataList, and Login controls can display editable text boxes. However, except for the GridView control (see the previous point), the controls do not automatically validate or HTML-encode the user input. Therefore, it is recommended that you create templates for these controls, and in the template, include an input control such as a TextBox control and add a validation control. In addition, when extracting the value of the control, you should encode it. SQL Injection To avoid SQL injection attacks, follow these guidelines: Do not create SQL commands by concatenating strings together, especially strings that include input from users. Instead, use parameterized queries or stored procedures. If you are creating a parameterized query, use parameter objects to establish the values for the parameters. Encrypting View-State Data Data-bound controls, such as the GridView control, sometimes need to persist information that is considered sensitive. For example, the GridView control can maintain a list of keys in the DataKeys property, even if this information is not displayed. Between round trips, the control stores the information in view state. View state information is encoded and stored with the contents of the page and could be decoded and exposed to an unwanted source. If you must store sensitive information in view state, you can request that the page encrypt view state data. To encrypt the data, set the page's ViewStateEncryptionMode property to true. Caching It is recommended that you avoid storing sensitive information in the Cache object when client impersonation is enabled and the results from the data source are retrieved based on the client identity. If caching is enabled, cached data for a single user can be viewed by all users and sensitive information could be exposed to an unwanted source. Client impersonation is enabled when the impersonate attribute of the identity configuration element is set to true and anonymous identification is disabled for the application at the Web server. Click here to submit your project requirements to Option Matrix, India. Back to top Using SQL Server Express Edition with ASP.NET Microsoft SQL Server 2005 Express Edition provides a simple database solution for building applications. SQL Server Express Edition supports the complete SQL Server 2005 programming model including Transact-SQL, stored procedures, views, triggers, SQL Server CLR Integration (SQLCLR), and the XML data type. When you develop an application using SQL Server Express Edition as the data source, you can ensure that the application will be compatible with production servers running SQL Server 2005. Connecting to a SQL Server Express Edition Database You can connect to a SQL Server Express Edition database just like you would connect to any SQL Server database by specifying the database server as the local SQL Server Express Edition data source. For example, the following connection string connects to a database named Customers. Copy Code Data Source=.\SQLEXPRESS;Initial Catalog=Customers;Integrated Security=True; You can also specify a database file to attach to by using the AttachDBFilename connection-string attribute in place of the InitialCatalog or Database connection-string attributes. Connecting to the database by using a file name simplifies deploying your database with your application (provided the target server is running SQL Server Express Edition). For example, the following connection string connects to a database that is stored in the Customers.mdf file. Copy Code Data Source=.\SQLEXPRESS;AttachDbFileName=e:\data\Customers.mdf;Integrated Security=True;User Instance=True ASP.NET provides a convenient option for storing data in the App_Data directory of a Web application. Contents of the App_Data directory are not served in response to Web requests, which improves the security of the data for your application. As an added convenience, you can supply the |DataDirectory| connection string variable in place of the file path to the App_Data directory for your application. ASP.NET features - such as the SqlDataSource control or the providers for membership, roles, user profiles, Web Parts personalization, and so on - will automatically substitute the file path to the App_Data directory for the |DataDirectory| connection-string variable when opening a connection to the database. This ensures that the path to your database remains current if your Web application is moved to a different directory. The following code example shows a connection string that includes the |DataDirectory| connection-string variable. Copy Code Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|Customers.mdf; Integrated Security=True;User Instance=True Note: In a postback request, if the page contains validator controls, check the IsValid property of the Page and of individual validation controls before performing any processing. You can close the connection held by Visual Web Developer by right-clicking the database in Solution Explorer and selecting the Detach option, or by right-clicking the database in Server Explorer and selecting Close Connection. Visual Web Developer will automatically close any open database connections when you run or debug your Web application. Additionally, if you need to release any open connections to a SQL Server Express Edition database, you can unload your Web application by using Internet Information Services Manager (IIS Manager). You can also unload a Web application by adding an HTML file named App_offline.htm to the root directory of your Web application. To allow your Web application to start responding to Web requests again, simply remove the App_offline.htm file. You will need to release open connections to a SQL Server Express Edition database when you want to copy or move the database to a new location. Setting up a SQL Server Express Edition Database Visual Web Developer provides tools to help you create a SQL Server Express Edition database; manage database elements, such as tables, stored procedures, and so on; and manage connections to the database. You can access these capabilities through the Server Explorer window. You can also create a SQL Server Express Edition database by connecting to a computer running SQL Server Express Edition and issuing a CREATE DATABASE command, or by using the SQL Server management tools provided for SQL Server Express Edition. The default provider for ASP.NET features that store data in a SQL Server database - such as membership, roles, the user profiles, Web Parts personalization, and so on - is configured to connect to the Aspnetdb.mdf SQL Server Express Edition database in the App_Data directory for your application. If you enable any of these data-storage features using the default provider and the Aspnetdb.mdf SQL Server Express Edition database does not exist in the App_Data directory for your application, the database will be automatically created. The App_Data directory for your application will also be created if it does not exist. User Instances SQL Server Express Edition supports user instances, which means that a new process will be started for each user that connects to a SQL Server Express Edition database. The identity of the process will be the user that opened the connection. When you connect to a local database project, Visual Web Developer connects to the SQL Server Express Edition database with user instances enabled, by default. For example, the following code example shows a typical connection string used by Visual Web Developer to connect to a SQL Server Express Edition database. Copy Code Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True Although enabling user instances is suitable for desktop development, starting worker processes is not appropriate on Web servers hosting sites for multiple customers where applications must be separated and secured. ASP.NET applications that run with the same process identity can connect to the same user instance. Because all ASP.NET applications run with the same process identity on Windows 2000 and Windows XP Professional (by default, the local ASPNET account) and ASP.NET applications in the same application pool run with the same process identity on Windows Server 2003 (by default, the NETWORK SERVICE account), shared hosting servers that contain applications that do no trust each other should explicitly disable user instances. This functionality can be turned off by connecting to the SQL Server Express Edition instance (for example, by issuing the following command at a command prompt: osql -E -S .\SQLEXPRESS) and issuing the following Transact-SQL command. EXEC sp_configure 'show advanced option', '1' GO RECONFIGURE WITH OVERRIDE GO EXEC sp_configure 'user instances enabled', 0 GO RECONFIGURE WITH OVERRIDE GO Deploying a SQL Server Express Edition Database A SQL Express Edition database is made up of two files: the .mdf file, which contains the database schema and data; and the .ldf file, which contains log information for the database. When you deploy a Web site by using the Copy Web Site tool, the SQL Server Express Edition database files are copied as well. Your application will continue to run as long as SQL Server Express Edition is installed on the target server. Here are some other options for deploying a SQL Server Express Edition database. If you are making a file-based connection to your SQL Server Express Edition database, these files can be copied with your application to a target server (that has SQL Server Express Edition installed) using XCopy, FTP, or another means. Because SQL Server Express Edition uses the same file format as other versions of SQL Server 2005, you can copy the .mdf and .ldf files to a server running SQL Server and then attach the files as a database. If you want to copy an empty SQL Server Express Edition database that contains database schema, but no data, the SQL Server management tools enable you to generate scripts that can be run in your target database to duplicate the schema from your development database. Note: If you are deploying your SQL Server Express Edition database to a Web server that hosts multiple sites that do not trust each other, then you cannot use file-based connections or user instances to help ensure that your data is not exposed to other applications on the server. In this case, it is recommended that you migrate the contents your SQL Server Express Edition database to another version of SQL Server 2005 that your deployed ASP.NET application can access. If your SQL Server Express Edition database contains encrypted information, such as encrypted passwords stored in a membership database, make sure that your encryption keys are copied to the target server as well. If you want to move the entire SQL Server Express Edition database, you need to ensure that there are no open connections to the database that would cause it to be locked. Unlocking a Locked Database If there is an open connection to a database, the database is locked and cannot be moved or deleted. Open connections can be held by an ASP.NET application, Visual Studio, or some other program or database client. To unlock a database, all open connections to the database must be closed. You can close the open connections in the following ways: You can close the connection held by Visual Web Developer by right-clicking the database in Solution Explorer and selecting the Detach option, or by right-clicking the database in Server Explorer and selecting Close Connection. Visual Web Developer will automatically close any open database connections when you run or debug your Web application. You can close any connections held by an ASP.NET application by ending the application. This can be done by using IIS Manager, or by placing a file named App_offline.htm in the root directory of the ASP.NET application (you must remove this file to restart the application). You can close any connections held by other sources, such as a Windows Forms application, by exiting the program. Click here to submit your project requirements to Option Matrix, India. Back to top About Extensible Storage Engine The extensible storage engine (ESE) is a database engine that stores information in a logical sequence. Information can be retrieved either sequentially or by accessing defined indices. Updates to the database are implemented with a transaction in order to ensure secure operations. ESE enables simultaneous access to multiple databases, including transaction-log file databases that can be used for system recovery. ESE is scalable to large or small applications. The following features are available with the ESE application programming interface (API): Backup and restore: An application can make consistent copies of the data state while it is on-line and actively modifying data state. Cursor Navigation: The application can navigate with the cursor to access data either sequentially or by using indices. Database: A collection of tables that are backed up and restored as a single unit. Logging and crash recovery: The ESE API ensures that application-defined data consistency is honored even in the event of a system crash. Tables: The fundamental structure of the ESE database that is used to store data. Transaction: The ESE database engine provides Atomic Consistent Isolated Durable (ACID) transactions that allow applications to retrieve data only from reliable data states and maintains data consistency in the event of an unexpected process termination or system shutdown. Scalable: The application can create databases as large as 100 GB or as small as 1MB. Click here to submit your project requirements to Option Matrix, India. Back to top Option Matrix Featured Case Studies Option Matrix Whitepapers External Links Portal for Multi-location Hospital The client was a healthcare provider (multi-location hospital) for the local resident patients having sleeping disorders. Option Matrix's scope of consulting was for their Seattle, View Full Details Health Fitness Portal Client was establishing a Health Fitness Center at California established in June 2006 by a team of local Antelope Valley residents. It was offered full complement of new and innovative   View Full Details Sleep Disorders Hospital Application This project was the company service portal of "Client" for patients suffering from sleep disorders. It was information about the company, key personnel, HIPPA privacy, education resources,   View Full Details Batteries Ecommerce Portal The objective was to design an e-commerce website for Client to display their catalog of batteries online and collect orders which was forwarded by e-mail to be serviced by a third   View Full Details Spices & Herbs Ecommerce Website The overall purpose of this project was to develop an intuitive and professional spice and herbal ecommerce web site. This web site was accessed by site visitors, members (retail consumers),   View Full Details Portal for Web Design Firm The client, a web design firm located in USA. It positions itself as an idea company and offers services such as website design / development coupled with creative marketing, Internet   View Full Details Dynamic Content Portal The overall purpose of the project was to redesign a database driven dynamic version of the existing site including migration of existing content on the site to the new site design and   View Full Details Networking Systems Ecommerce The USA Based client was one of the leading provider of used and Refurbished Cisco Systems, Extreme Networks, Juniper and Foundry equipment. The client has the experience   View Full Details 3rd Party Order Fulfillment This project envisages provision of e-commerce for people in continent B countries who cannot do such transactions in normal course with US based online retailers as shipments   View Full Details Multi-lingual Recruiting Portal This project was for building a specialized job portal for Tourism Schools in Austria. This institutions offer training that enables college students or graduates to get trained for jobs in hotels,   View Full Details Corporate Web Portal The client was focused on providing process control solutions to the manufacturing and related industries. Established in 1989, this Client was a BSI-certified ISO 9001:2000   View Full Details Business Community Portal The overall purpose of this project was to extend the features of existing business community web application meant for New Platform of projects for Team Formation and Expertise Management   View Full Details Online Generic Store Portal This project was intended to be a generic store front that was customized for several clients in the future. The intended objective was to build once and deploy for several clients with easy customization.   View Full Details CMS Portal The overall purpose of the project was to redesign a database driven dynamic version of the existing site including migration of existing content on the site to the new site design   View Full Details HR Marketplace This project has focused on development for Human Resources professionals who was seeking quality, unbiased information necessary to make educated buying decisions while keeping