Deleting duplicate Rows using With Statement in SQL...

WITH CTE AS
(
    SELECT rn = ROW_NUMBER()
                OVER(
                  PARTITION BY id, parentid, serial_no, text
                  ORDER BY Id ASC), *
    FROM XXXXXXXX where Url like '%_layouts/trav%'
)
delete FROM cte
WHERE  rn > 1

Infopath forms Receive data and submit data from SQL server

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: 

C#

[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: 

C#

[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: 

C#

[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 : 

C#

[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: