Creating New SQL Providers

To create a new SQL provider, select New SQL Provider to launch a set of three dialog boxes that guide you through customizing an SQL Provider:

The dialog boxes will guide you through defining how to retrieve images and annotations files from your Database Schema.

The SQL Data Provider (Page 1 of 3) is displayed. This dialog allows you to define the caching aspects of the SQL Data Provider:

SQL Data Provider Settings (Page 1)	Description
Name	Specifies the name used to reference the Data Provider; this value will be used to reference this data provider.
Maximum Image Size to Cache	This value is in kilobytes. It allows you to put an upper limit on the size of the image that can be cached. When deciding on the value, bear in mind the amount of memory your system has. A larger value on infrequently used images will unnecessarily use up the memory for cache. The default value is 512K.
Cache Time Limit	Represents the amount of time in minutes that the image is to remain in the cache. Upon remaining in the cache for the amount of time specified, the system will flush the cache of the stored image.
Caching	Indicates whether caching is enabled.
SQL Cache Dependency	Indicates whether SQL dependency will be used for caching. For more information, refer to Setting Up SQL Server 2005 and SQL Server 2008 Cache Dependencies.
SQL Cache Dependency Initialization	Indicates whether SqlCacheDependency should be initialized by the image data provider. SQL dependency requires one time initialization. If this initialization has already occurred in your application, then the initSqlCacheDependency attribute should be set to false. See the example below for information on how to perform the initialization in your application.

Once you have defined the Cache Settings, click the Next button. The SQL Data Provider (Page 2 of 3) dialog is displayed and allows you to define how the Data Provider will connect to your database and the SQL to be used to retrieve the image data:

SQL Data Provider Settings (Page 2)	Description
Connection String Name	The drop-down list displays all Connection Strings that are defined within the web.config. The Connection String Name field defines how the Data Provider should connect to your database in order to issue SQL commands. For more information on defining a connection string, refer to the Managing SQL Connection Strings topic.
SQL Command	Defines the SQL query command or a stored procedure that will be executed to retrieve a DocumentIdentifier from your database schema.
SQL Parameter	Represents the column / data field being searched in the query.
SQL Command Type	Specifies a Text (syntax "Text") command or a stored procedure (syntax "StoredProcedure").
SQL Image Data Field Blob	Indicates whether the image data stored in the databases is in a BLOB format. The default value is "enabled", meaning the image data is stored in the database in a "BLOB" format. If this field is not "enabled", the Data Provider will expect the Image File name to be stored in the database field.
SQL Image Data Field	Specifies the name of the column that contains the request data. If the "SQL Image Data Field Blob" setting is enabled, this field will contain 'BLOB' image data. If the "SQL Image Data Field Blob" is not enabled, this field will be the file path where the image data is stored.
Param Key Length	Specifies the length of the Key (sqlParameter) used to query the database for image retrieval. This parameter is useful to prevent SQL injection if you are using a stored procedure in your query. For a detailed description of SQL Injection, refer to the articles on MSDN: Patterns & Practices: Security How To's Index How To: Protect from SQL Injection in ASP.NET - Step 2
Param Key SQL Database Type	Specifies the SQL Database type of the query parameter (SQL Parameter). This attribute is used in conjunction with the above attribute (paramKeyLength) to prevent SQL injection attacks. (Some database engines, like SQL Server Express ignore this feature. Please consult your DBA for advice.)

After you have defined the SQL connection for the Image data, click Next to define the SQL configuration to be used for annotation retrieval. The SQL Data Provider (Page 3 of 3) dialog is displayed and allows you to define the SQL used to retrieve and update annotation data (ART):

SQL Data Provider Settings (Page 3)	Description
ART Connection String Name	The drop-down list displays all Connection Strings that are defined within the web.config. The ART Connection String Name field defines how the Data Provider should connect to your database in order to issue SQL commands. For more information on defining a connection string, refer to the Managing SQL Connection Strings topic.
SQL ART Read Command	SQL query to retrieve annotations data. This query should retrieve annotations data associated with the Document Identifier of the image document being retrieved.
SQL ART Read Command Type	A SQL command can either be in a command format or a stored procedure call. This attribute specifies if it is a Text (syntax "Text") command or a stored procedure (syntax "StoredProcedure").
SQL ART Update Command	A SQL query that is run to update the annotations data.
SQL ART Update Parameter	Represents the column / data field being searched in the query.
SQL ART Update Command Type	Specifies if the SQL Art Update Command is in a Text format (syntax "Text") or a stored procedure call (syntax "StoredProcedure").
SQL ART Data Field	Represents the database table field/column that holds the annotation data. If using the built-in SqlImageDataProvider, even when images are stored as files, the annotations data is expected to be stored as CLOBs (character large objects or VARCHAR(MAX) SQL data type).
ART Field Parameter Length	This specifies the length of the ART Data Field (SQL ART Data Field). This parameter is useful for preventing SQL injection if you are using a stored procedure in your query. For a detailed description of SQL Injection, refer to the articles on MSDN: Patterns & Practices: Security How To's Index How To: Protect From SQL Injection in ASP.NET - Step 2
ART Field Parameter Database Type	Specifies the SQL Database type of the SQL ART Data field parameter. This attribute is used in conjunction with the above attribute (ART Field Parameter Length) to prevent SQL injection attacks.

This section provides information about the following: