Introduction
Using SQL queries to (Add, Edit & Delete) data from DB via Info Path forms programmatically.
Steps
A) Creating the Database:
- Open SQL Server Enterprise Manager through : (Start > All Programs >
Microsoft SQL Server > Enterprise Manager), then in Console Root folder expand the Microsoft
SQL Servers then expand the SQL Server Group then expand your instance then at the
Databases folder right click on it and create a new database called “QueryDB” then
expand the QueryDB database and right click on Tables to create a new table called
“Resource” this table holds two columns: (ResourceID – ResourceName)
as shown in this figure:
Also , add a stored procedure called “UpdateResourceName” in order to update the
ResourceName as shown in the figure:
B) Creating the Web Service:
- Open MS- visual studio .net 2005 then click File menu > New > Web Site >
ASP.Net Web Service : Then in the App_Code folder open Service.cs file then add
these namespaces: Using System. Data; Using System.Data.SqlClient; Then, add this
web method to get all of the resource names from Resource table in the QueryDB database:
|
[WebMethod]
public
DataSet GetResourceNames()
{
SqlConnection con = new
SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial
Catalog=QueryDB");
SqlDataAdapter da = new
SqlDataAdapter("SELECT Resource.ResourceName FROM
Resource", con);
DataSet ds = new DataSet();
con.Open();
da.Fill(ds,
"Resource");
con.Close();
con =
null;
da =
null;
return ds;
}
Then , add
this web method to insert a new resource name:
[WebMethod]
public
DataSet SetResourceNames(string
strInputResourceName)
{
SqlConnection con = new
SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial
Catalog=QueryDB");
SqlCommand com = con.CreateCommand();
com.Parameters.AddWithValue("@InputResourceName", strInputResourceName);
com.CommandText = "INSERT INTO Resource (Resource.ResourceName) VALUES (@InputResourceName)";
strInputResourceName
= strInputResourceName.Replace("'",
"''");
SqlDataAdapter daa =
new SqlDataAdapter();
daa.SelectCommand =
com;
DataSet Ds = new DataSet();
con.Open();
daa.Fill(Ds,
"Resource");
con.Close();
con =
null;
daa =
null;
return Ds;
}
Then , add
this web method to delete a certain selected resource name:
[WebMethod]
public
DataSet DeleteResourceNames(string
strDeleteResourceName)
{
SqlConnection con = new
SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial
Catalog=QueryDB");
SqlCommand com = con.CreateCommand();
com.Parameters.AddWithValue("@DeleteResourceName", strDeleteResourceName);
com.CommandText = "DELETE FROM Resource WHERE Resource.ResourceName = @DeleteResourceName";
strDeleteResourceName
= strDeleteResourceName.Replace("'",
"''");
SqlDataAdapter daa = new
SqlDataAdapter();
daa.SelectCommand =
com;
DataSet Ds = new DataSet();
con.Open();
daa.Fill(Ds,
"Resource");
con.Close();
con =
null;
daa =
null;
return Ds;
}
Then , add
this web method to update a selected resource name :
[WebMethod]
public
DataSet UpdateResourceNames(string
strOldResourceName, string strNewResourceName)
{
SqlConnection con = new
SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial
Catalog=QueryDB");
SqlCommand com = con.CreateCommand();
SqlDataAdapter daa = new
SqlDataAdapter("UpdateResourceName",
con);
daa.SelectCommand.CommandType
= CommandType.StoredProcedure;
daa.SelectCommand.Parameters.AddWithValue("@OldResourceName", strOldResourceName);
daa.SelectCommand.Parameters.AddWithValue("@NewResourceName ", strNewResourceName);
strOldResourceName
= strOldResourceName.Replace("'",
"''");
strNewResourceName
= strNewResourceName.Replace("'",
"''");
DataSet ds = new DataSet();
con.Open();
daa.Fill(ds);
con.Close();
con =
null;
daa =
null;
return ds;
}
|
Which shown in this figure:
Then, run this service as shown in this figure :
Then click on the GetDomainNames link , then invoke the webmethod as shown in the
figure:
Then after invoking the webmethod , it should return All of the resource names as shown in the figure:
C) Creating the Compatible InfoPath form & Publishing:
- Open Microsoft Office InfoPath 2007 then design a new blank form template which
explained before in the previous articles ,then add a table with title layout and
add a list box control , txt box control and three buttons controls ( Add , Update
& Delete ) , as
shown in the figure:
Then from Tools menu > Data Connections, as shown in the figure:
Then select receive data in order to receive data via the web service as shown in
the figure:
Then select the resource of your data as a web service as shown in the figure:
Then write the URL of the location of the web service you want to use as your data
connection as shown in the figure:
Then you’ll find the web methods which wrote in the web service which called “GetResourceName”,
“SetResourceNames”, and “DeleteResourceName” & ‘UpdateResourceNames” as shown in the figure:
Then store a copy of the data in the form template as
shown in the figure:
Then check on the automatically retrieve data when form is opened as shown in the
figure:
Then you’ll get the data connection source
for the form template as shown in the figure:
Then repeat these steps again but this time for “SetResourceNames” web method as
shown in this figures: (But this time for submit data not receive)
Then Click on Next, as shown in the figure:
Then, Click on Finish button, as shown in the figure:
Then, Click on Close button, as shown in the figure:
Then repeat these steps again but this time for “DeleteResourceNames” web method
as shown in this figures: (But this time for submit data not receive)
Then, click on Next button,
as shown in the figure:
Then, Click on Finish button, as shown on the figure:
Then, Click on Close button, as shown in the figure:
Then repeat these steps again but this time for “UpdateResourceNames” web method
as shown in this figures: (But this time for submit data not receive)
Then, Click on Next button,
as shown
in the figure:
Then, Click on Finish button, as
shown in the figure:
Then, Click on Close button, as shown in the figure:
Then right click on the list box and click on drop down list box properties as shown in the figure:
Then at the list box entries select look-up values from an external data source
as shown in the figure:
Then click on the entries button
as shown in the figure:
Then expand the data fields till reach to the ResourceName data filed
as shown in the
figure:
Then you’ll find the X-path to the ResourceName data field in the entries textbox
as shown in the figure:
Then right click on the text box and click on text box properties as shown in the figure:
Then at Default value set the value to the value of the selected list box item
as shown in the figure:
Then right click on the Add button to show its properties as shown in the figure:
Then add these rules as shown in the figure:
Also repeat these steps for the Edit button as shown in the figure but with another
rules:
Also repeat these steps for the Delete button as shown in these figures but also
with another rules:
Then, click on Ok button, as shown in the figure:
Then from the preview tab click on form item to run the form as shown in the figure:
Here’s the result after running the form:
Then, you can add data using Add button, as shown in the figure:
Here’s the added data, as shown in the figure:
Then, you can delete data using Delete button, as shown in the figure:
Then, you can also edit your data using Edit button,
as shown in the figure:
Here’s the result of the edited data, as shown in the figure:
Then close the preview as shown in the figure:
Then from Tools menu > Form
options >: At the security and trust category
> security level > set full trust (the form has access to files and settings
on the computer, then at the form template signature > check on the sign this
form template, as shown in the figure :
Then, click on File menu > Publish, as shown in the figure:
Then select to a network location, as shown in the figure:
Then write the path and the file name for the published form template, as shown
in the figure:
Then remove the path from this textbox, as shown in the figure:
Then click on Publish button, as shown in the figure:
Then click on close button, as shown in the figure:
Then open the central administration which explained before in the previous articles
and click on the Application Management tab > InfoPath forms services > Manage
form templates, as shown in the figure:
Then click on upload form templates, as shown in the figure:
Then click on the browse button to upload the published form template, as shown
in the figure:
Then click on upload button as shown in the figure:
Then click on Ok button, as shown in the figure:
Then right click on the new published form template > Activate to a site collection,
as shown in the figure:
Then at the activation location section, right click on the site collection drop-down
list and select change site collection, as shown in the figure:
Then right click on the web application drop-down list and select change web application,
as shown in the figure:
Then click on the sharepoint-80 link, as shown in the figure:
Then from the URL section select your site link, as shown in the figure:
Then click ok button, as shown in the figure:
D) Creating the Sharepoint Site Document-Library:
- Use the same steps that were explained before in the previous articles in order to
create a sharepoint site and a document library so , here I created another document
library called “QueryDocLib” in order to publish the published form template to
it as a content type , as shown in the figure :
Then click on Settings tab > Document library settings, as shown in the figure:
Then, at the content types section click on add from existing site content type’s
link, as shown in the figure:
Then from the available site content types, select the published content type “Newtemp4”
and add it, as shown in the figure:
Then go back to the QueryDocLib page and click on New tab , then click on the Newtemp4
item , as shown in the figure:
Then, the result will be like this:
Then you can save the document as shown in the figure:
Then you can go back to the QueryDocLib page and see the saved document as shown
in the figure:
|