InfoPath Development Combat (3): Getting Data from SQL Server

The content of this section:

How to use a data connection in InfoPath to get data from a SQL Server database

How to modify SQL queries in a data connection through code

Before you begin designing an InfoPath form, you prepare a database and InfoPath supports two types of database connections:

Microsoft Office Access database (. mdb file or. accdb file)

Microsoft SQL Server Database

Note that InfoPath, while supporting the SQL Server Express database, does not support connecting SQL Server Express (directly attached to the. mdf file) as a file, so if you want to use the SQL Server Express database, Please attach the database file (. mdf file) to SQL Server Express. This means that the database cannot be distributed with InfoPath, and InfoPath using SQL Server data connections must ensure that clients have access to the SQL Server database (queries cannot be taken offline) if they want to query normally. Using an Access database also requires ensuring that clients have access to the Access database. If the Access database is in a network location, you need to make sure that the location is accessible to the client, and if the Access database is local, you need to pay attention to distributing the InfoPath form along with the database (this way you can implement an offline query).

In query mode, both databases use SQL syntax uniformly.

Because Office Access is not installed, this example uses SQL Server as the target database. First create a database in SQL Server named TestDB, and then use the following script to add the blogs table and its contents:

/****** object: Table [dbo]. [Blogs] Script date: 01/19/2009 13:45:22 ******/
IF EXISTS (SELECT * from sys.objects WHERE object_id = object_id (N ' [dbo].[ Blogs] ') and type in (N ' U '))
DROP TABLE [dbo]. [Blogs]
Go
/****** object: Table [dbo]. [Blogs] Script date: 01/19/2009 13:45:22 ******/
SET ANSI_NULLS on
Go
SET QUOTED_IDENTIFIER ON
Go
IF not EXISTS (SELECT * from sys.objects WHERE object_id = object_id (N ' [dbo].[ Blogs] ') and type in (N ' U '))
BEGIN
CREATE TABLE [dbo]. [Blogs] (
[Name] [nvarchar] (m) COLLATE chinese_prc_ci_as NULL,
[Blog] [nvarchar] (MB) COLLATE chinese_prc_ci_as NULL
)
End
Go
INSERT [dbo]. [Blogs] ([name], [blog]) VALUES (n ' Windie Chai ', n ' http://xiaoshatian.cnblogs.com ')
INSERT [dbo]. [Blogs] ([name], [blog]) VALUES (n ' Terry Lee ', n ' http://terrylee.cnblogs.com ')
INSERT [dbo]. [Blogs] ([name], [blog]) VALUES (n ' Anytao ', n ' http://anytao.cnblogs.com ')
INSERT [dbo]. [Blogs] ([name], [blog]) VALUES (n ' Anders Liu ', n ' http://andersliu.cnblogs.com ')/
INSERT [dbo]. [Blogs] ([name], [blog]) VALUES (n ' kaneboy ', n ' http://blog.joycode.com/kaneboy ')