El TecnoBaúl de Kiquenet

Kiquenet boring stories

Archive for the ‘ASP.NET’ Category

MS Search Service, Indexing Service and SQL Server: search engine for ASP.NET website

Posted by kiquenet en 21 junio 2016

Quick and Dirty method to Combine MS Search Service, Indexing Service and SQL Server to provide a unified search engine for your ASP.NET website

WebSite for a company and search engine is required.

They had the contents in .aspx pages, not a problem, but they also had forums whose contents were collated in a database table, in a column to be precise. They wanted me to display the results from these two sources through a common search engine. Since I had little time to write a search engine of my own, I put the power of MS Search Service, Indexing Service and SQL Server together to do the task for me. There is a lot of scope for enhancement but here is how you can implement a very basic yet powerful search engine of your own.

STEP I: Create a Web Catalog in Indexing Service

By default, the indexing service has two catalogs, one for the file system (System) and one for the default web site (Web).
If Web catalog is not present, you can easily create one.

1. Open Control Panel –> Administrative Tools –> Computer Management
2. Scroll down to Computer Management–> Services and Applications –> Indexing Service in the MMC
3. Right click Indexing Service and choose New –> Catalog
4. In the name field give Web and choose C:\Inetpub as the location
5. Right click the newly created catalog, choose Properties. Click on the Tracking tab of the properties window. Select "Default Web Site" as the WWW server.
6. Restart Indexing Service
7. Go to Computer Management –> Services and Applications –> Services and configure Indexing Service as Automatic if it is Manual or Disabled.

STEP II: Optimize ASPX and ASCX files for full-text search

By default, the *.aspx and *.ascx file types are treated as text files which are not optimized for searching by the Indexing Service. To optimize searching for these two file types copy the following into a new .reg file and run it in your computer. The customary warning: Editing registry incorrectly may prohibit your computer to run properly. Edit the registry at your own risk. I may not be held responsible for the damage you do to your computer by incorrectly following the steps below.

   REGEDIT4
   [HKEY_CLASSES_ROOT\.aspx\PersistentHandler]
   @="{eec97550-47a9-11cf-b952-00aa0051fe20}"
   [HKEY_CLASSES_ROOT\.ascx\PersistentHandler]
   @="{eec97550-47a9-11cf-b952-00aa0051fe20}"

You must have Index files with Unknown Extensions enabled. To enable this, right click on Indexing Service, choose Properties and click on Generation tab on the window. Check Index files with Unknown Extensions checkbox. Restart the computer, stop Indexing Service, delete all the contents of the catalog.wci folder (not the folder itself) corresponding to your catalog (in this case C:\Inetpub\catalog.wci), start the Indexing Service and allow it to rebuild the catalog.

STEP III: Using Full-Text Searches directly in ASP.NET Applications

This is not actually a step but a side step where you can take a pause for a moment and test whether your newly created catalog is returning some results. If you don’t have a database to worry about, then this might be your last step unless you want to link the Indexing Service with SQL Server.

Indexing service exposes itself via the OLEDB provider MSIDXS. You can take the full advantage of the server in your ASP.NET application via ADO.NET. If  you have a TextBox (TextBox1), a Button (Button1) and a DataGrid (DataGrid1) on your web form and the Web catalog in place, this might as well be the content of your button click handler:

using System.Data;
using System.Data.OleDb;
…….
…….
private void Button1_Click(object sender, EventArgs e)
{
      string strCatalog = "Web";
      string strQuery = "Select Filename, Rank, VPath from SCOPE() where FREETEXT(‘" + TextBox1.Text + "’)";
      string connString = "Provider=MSIDXS.1;Integrated Security .=”;Data Source=’" + strCatalog + "’";
      OleDbConnection Connection = new OleDbConnection(connString);
      Connection.Open();
      OleDbDataAdapter da = new OleDbDataAdapter(strQuery, Connection);
      DataSet ds = new DataSet();
      da.Fill(ds);
      Connection.Close();
      DataView source= new DataView(ds.Tables[0]);
      DataGrid1.DataSource = source;
      DataGrid1.DataBind();
}

STEP IV: Link Indexing Service with SQL Server

The next step is to link the Indexing Service with your SQL Server. Open Query Analyzer or your favourite SQL script editor. Run the following script.

EXEC sp_addlinkedserver FTIndexWeb, ‘Index Server’, ‘MSIDXS’, ‘Web’
GO

where FTIndexWeb is the chosen linked server name, and Web is the catalog name you created in STEP I.

STEP V: Querying Indexing Service via SQL Server

Let’s modify the previous query and run it in SQL server. Run the following query in Query Analyzer.

SELECT Q.FileName, Q.Rank, Q.VPath
FROM OpenQuery(
FTIndexWeb,
‘Select Filename, Rank, VPath
from SCOPE()
where FREETEXT(”Calcutta”)
ORDER BY Rank DESC’
              ) AS Q

Replace FTIndexWeb with whatever linked server name you chose in step IV and Calcutta with your search keyword(s).

STEP VI: Enabling a table/column in SQL Server for full-text searches

Open Enterprise Manager.

Browse to Console Root–>; Microsoft SQL Servers –> Databases –> Tables. Check two things before you proceed.

1. The table where you want full-text searching enabled, must have some unique constraint. If a primary key or a unique constraint is not present, create an "ID" column and apply a unique constraint.
2. Microsoft Search Service (mssearch.exe) must have been enabled and running in your computer. If not, browse to Computer Management –> Services and Applications–> Services in Computer Management MMC and configure Microsoft Search Service as Automatic and start the service.

On the Enterprise Manager MMC, right click on your table and choose Full-Text Index Table –> Define Full-Text Indexing on a table. If the option is grayed out, check #2 above.

Click Next on the popped up wizard .
Choose the unique index and click Next.
Choose the columns where you want indexing enabled. Click Next.
Give the catalog a name and specify a physical location to store the catalog. Click Next.

If you want the control over how and when the catalog is filled (full or incremental) click on New Catalog Schedule.
After configuring it, come back to Full-Text Indexing Wizard and click Next. Click Finish.

The wizard takes a minute or two to setup the catalog.

STEP VII: Querying Full-Text Catalog in SQL Server

Let’s test the newly created catalog in SQL Server. Run the following query.

SELECT FT_TBL.subject, KEY_TBL.RANK, FT_TBL.topicid
FROM forums_topics AS FT_TBL,
CONTAINSTABLE ( forums_topics
                ,   message
                , ‘"Calcutta"’ )
AS KEY_TBL
WHERE FT_TBL.topicid = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC

Forums_Topics is the table name and Message is the column name on which full-text catalog is built. Replace Calcutta with your search keyword(s).

STEP VIII: Combining the results

The steps to combine the results would be to

1. Create a temporary table
2. Insert the results of the first query
3. Insert the results of the second query
4. Query the temp table
5. Drop the temp table

We need a stored procedure for this and here it is:

CREATE PROCEDURE sp_Accounts_SearchSite
@FreeText varchar (255)
AS
SET NOCOUNT ON
CREATE TABLE #tempresults(
ID int IDENTITY,
FileNames varchar (255),
Rank int,
VPath varchar(255))
DECLARE @sql nvarchar(1000)
SET @sql = N’INSERT INTO #tempresults(FileNames, Rank, VPath) ‘ + CHAR(13) +
N’SELECT Q.FileName As FileNames, Q.Rank As Rank, Q.VPath As VPath ‘ + CHAR(13) +
N’FROM OpenQuery(FTIndexWeb, ”Select Filename, Rank, VPath from SCOPE() where FREETEXT(””’ + @FreeText + ””’)” ) AS Q’
EXECUTE sp_executesql @sql
SET @SQL = N’INSERT INTO #tempresults(FileNames, Rank, VPath) ‘ + CHAR(13) +
N’SELECT FT_TBL.subject As FileNames, KEY_TBL.RANK As Rank, FT_TBL.topicid As VPath ‘ + CHAR(13) +
N’FROM forums_topics AS FT_TBL, ‘ + CHAR(13) +
N’CONTAINSTABLE ( forums_topics ‘ + CHAR(13) +
N’, message’ + CHAR(13) +
N’, ”"’ + @FreeText + ‘"” ) ‘ + CHAR(13) +
N’AS KEY_TBL’ + CHAR(13) +
N’WHERE FT_TBL.topicid = KEY_TBL.[KEY] ‘
EXECUTE sp_executesql @sql
SELECT FileNames, Rank, VPath from #tempresults ORDER BY Rank DESC
DROP TABLE #tempresults
SET NOCOUNT OFF
GO

STEP IX: Modify your .NET Application

The rest is a piece of cake. Your Button click handler should now look like this:

using System.Data;
using System.Data.SqlClient; // Bye Bye OleDb
…….
…….
private void Button1_Click(object sender, EventArgs e)
{
      string connString = @"server=****;database=****;uid=****;pwd=****;";
      string storedProcName = "sp_Accounts_SearchSite";
      SqlConnection Connection = new SqlConnection(connString);
      Connection.Open();
      SqlCommand command = new SqlCommand( storedProcName, Connection );
      command.CommandType = CommandType.StoredProcedure;
      command.Parameters.Add("@FreeText", TextBox1.Text);
      SqlDataAdapter sqlDA = new SqlDataAdapter();
      sqlDA.SelectCommand = command;
      DataSet dataSet = new DataSet();
      sqlDA.Fill( dataSet, "mySearchResults" );
      Connection.Close();
      DataView source = new DataView(dataSet.Tables[0]);
      DataGrid1.DataSource = source;
      DataGrid1.DataBind();
}

The grid will show results from your file system as well as from your database tables. With everything indexed, the result is lightening fast for hundreds of results if not millions.

Many of you might think that there remains a lot to be told. But didn’t I say it was quick and dirty? No pun intended.

To learn more about how to compose your own queries for full-text searches, visit the MSDN website at http://msdn.microsoft.com.

With little logic of your own, you can have a nice search engine which would query different sources differently based on your own requirements.

For example, you can redefine the scope (Deep Copy Traversal, Swallow Copy Traversal ring a bell?) and can do regular expression searches. You are the one to set your own limit.

By Ram Dash.

Reference: http://www.gisremotesensing.com/2011/05/unified-search-engine.html

Posted in .NET, ASP.NET, SQLServer | Etiquetado: , , , | Leave a Comment »

Indexing Server notes

Posted by kiquenet en 20 junio 2016

Instalar y configurar los servicios de Index Server
http://support.microsoft.com/kb/954822/es

De forma predeterminada, los servicios de Index Server no están instalados en un equipo basado en Windows Server 2008

Instalar
Herramientas administrativas -> Administrador de servidores.
Servicios de Index Server en Servicios de archivo de Windows Server 2003

Configuración
MMC Servicios de Index Server

Crear un Catálogo:
Nombre: extranetProductos
Ruta  : C:\CatalogosIndexServer\MyApp

Agregar carpetas para incluirlas en el "ámbito del catálogo"
"El ámbito es el conjunto de carpetas que se incluyen y excluyen del catálogo; el ámbito define el contenido que se incluye y excluye del índice"

Directories
    Path: \\serv\MyAppCMS\Dev\Products
        Account: DOMAIN\USER_NAS

    Incluir en el índice:
            Sí para incluir la carpeta en el catálogo
            No para excluir la carpeta del catálogo

Properties

 

Como complemento a Index Server, habría que instalar el complemento Adobe PDF iFilter 9 for 64-bit platforms, para la correcta indexación de archivos PDF:
http://www.adobe.com/support/downloads/thankyou.jsp?ftpID=4025&fileID=3941

Y después habría que “reescanear” el directorio (All Tasks -> Rescan (Full))

Página ASP.NET de búsqueda personalizada para buscar el catálogo:

http://support.microsoft.com/kb/820983

A tener en cuenta para IIS 7 sites:
    Install IIS 6 Management Compatibility
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\ContentIndex\Catalogs\[Catalog Name]
    Set ContentIndexed metabase property. cscript adsutil.vbs set w3svc/[web site instance]/root/[virtual director]/ContentIndexed 1

https://blogs.msdn.microsoft.com/asiatech/2009/11/19/how-to-index-iis-7-0-web-sites-in-windows-server-2008-by-indexing-service/#comment-4135

Sobre catálogos (catalog.wci) en Index Server
http://bcs.fltr.ucl.ac.be/srchadm/help/cathlp.htm

Troubleshooting:    

      REG DELETE \\HKLM\SYSTEM\CurrentControlSet\Control\ContentIndex\Catalogs /v <Catalog Name> 

    
     cisvc.exe = Content Index service or Indexing service
     cidaemon.exe = Indexing Service filter daemon

      Forcing Rescan with Indexing Service on Win2k
      I’ve tried selecting a Full Rescan, Incremental Rescan, and restarting the service.

net stop cisvc
cd "System Volume Information"\catalog.wci\
del *.*
cd \
net start cisvc

Stop the service, then right click the Indexing Service icon in MMC (the one in the tree on the left). Then go All Tasks, Tune Performance. There should be something like "this computer is mostly gonna be used for Index Service". That lets you raise the priority such that it’ll interrupt you. Its default setting, it stops indexing if the computer is doing any work.

 

Set objISAdm = CreateObject("Microsoft.ISAdm")
objISAdm.MachineName="MyServerName"
Set objCatAdm = objISAdm.GetCatalogByName("MyCatalog")
Set objScopeAdm = objCatAdm.GetScopeByPath("c:\FolderToReScan")
objScopeAdm.Rescan("TRUE")

In the last line, TRUE means do a full scan. Change it to FALSE to do an incremental scan.

http://www.piclist.com/techref/language/asp/ix/ixadmsvr.htm
http://www.piclist.com/techref/language/asp/ix/ixscan.htm


Página de Referencia en MSDN Microsoft Indexing Service
https://msdn.microsoft.com/en-us/library/ee805985(v=vs.85).aspx

Note: Indexing Service is no longer supported as of Windows XP and is unavailable for use as of Windows 8. Instead, use Windows Search for client side search and Microsoft Search Server Express for server side search.

Microsoft Index Server Guide

http://bcs.fltr.ucl.ac.be/srchadm/help/default.htm#Top

Getting Started
https://blog.codinghorror.com/getting-started-with-indexing-service/

ODBC data sources on Windows Server 2008 x64

Windows Server 2008 x64 has both 32 bit ODBC data sources and 64 bit ODBC data sources. They are managed separately.

Setting up a 32 bit ODBC connection in Windows Server 2008 x64. Start, Run, C:\Windows\SysWOW64\odbcad32.exe

To setup the 64 bit ODBC connection go to: Control Panel, Administrative Tools, Data Sources (ODBC).

Microsoft OLE DB Provider for Microsoft Indexing Service
https://msdn.microsoft.com/en-us/library/ms681560(v=vs.85).aspx

“Microsoft Indexing Service exposes itself to the developer as an OLE DB provider. Its name is MSIDXS. You can use ADO.NET for querying your Indexing Service”

Connection String: "Provider=MSIDXS;Data Source=myCatalog;Locale Identifier=nnnn;"

https://www.connectionstrings.com/ole-db-provider-for-index-server/

Know all the OLE DB Providers registered on your system
http://omnicode.blogspot.com.es/2007_12_01_archive.html
http://stackoverflow.com/questions/37748495/check-if-microsoft-ole-db-provider-for-index-server-aka-provider-msidxs-is-ins/37785940#37785940
http://stackoverflow.com/questions/11128104/oledb-providers-not-found?rq=1

https://github.com/kjbartel/ConnectionDialog/blob/master/Source/ConnectionUIDialog/NativeMethods.cs

https://github.com/blfsir/benzoa/blob/ad5b015aaabcf63650a8b2e4431cb4b45bda88cf/UDS/SubModule/Query/Listview.aspx.cs


Guid for CLSID OLEDB is C8B522D0-5CF3-11ce-ADE5-00AA0044773D

internal static Guid IID_IUnknown = new Guid("00000000-0000-0000-c000-000000000046");

internal static Guid CLSID_DataLinks = new Guid("2206CDB2-19C1-11d1-89E0-00C04FD7A829");

internal static Guid CLSID_OLEDB_ENUMERATOR = new Guid("C8B522D0-5CF3-11ce-ADE5-00AA0044773D");

internal static Guid CLSID_MSDASQL_ENUMERATOR = new Guid("C8B522CD-5CF3-11ce-ADE5-00AA0044773D");

Guid guidOleDbEnum = new Guid("{C8B522D0-5CF3-11ce-ADE5-00AA0044773D}");

Type type = Type.GetTypeFromCLSID(guidOleDbEnum);

using (OleDbDataReader rdr = OleDbEnumerator.GetEnumerator(type))

{

while (rdr.Read())

Console.WriteLine("{0} – {1}", rdr["SOURCES_NAME"], rdr["SOURCES_DESCRIPTION"]);

}

https://www.gittprogram.com/question/2414475_which-sdk-to-use-for-indexing-service-integration.html

Indexing Service Administration Type Library (c:\Windows\system32\ciodm.dll) <- used for admin functions to add/remove catalogs and scopes

ixsso Control Library (c:\Windows\system32\ixxso.dll) <- used to run queries against the catalogs

Microsoft ActiveX Data Objects Recordset 2.8 Library (c:\Program Files\Common Files\System\ado\msdaor15.dll) <- used to read query records

There are two methods for querying the Indexing Service:

1) IXSSO.dll contains the Indexing Service Query Automation Objects – Query and Utility

This is also refered to as the Query Helper API of the Indexing Service, and runs as a user process.  The Query object can be used to create and execute a query, which causes the Indexing Service OLE DB provider to returnan ADO Recordset object.  This then needs to be converted to an .Net compatible object such as the OleDB DataTable.

2) MSIDXS.dll contains the OLE DB Provider for Indexing Service object.  This object can be used to create a oledb connection object and dataadapter object.  All settings for the search are done in the connection string and in query string using special SQL extensions for Indexing Service.

If using the IXSSO.dll in VB.Net the declarations look like…

Dim IndexQueryHelper As Object = CreateObject("ixsso.Query")

Dim IndexUtilities As Object = CreateObject("ixsso.Util")

Dim IndexAdminHelper As Object = CreateObject("Microsoft.ISAdm") ‘ciodm.dll

Also keep in mind that when using the ixsso.dll the results are returned as an ADO recordset, which is not directly compatible with .Net with utilizes the OLE DB assembly.  To convert the result try…

Dim ODA As OleDbDataAdapter = New OleDbDataAdapter

Dim SearchResults As DataTable = New DataTable("FilesList")

ODA.Fill(SearchResults, IndexQueryHelper.CreateRecordSet("sequential"))

Troubleshooting: https://support.microsoft.com/en-us/kb/954819

HKEY_CLASSES_ROOT\MSIDXS

Permissions for MSIDXS

Group or user names list: SYSTEM, ASPNET


[HKEY_CLASSES_ROOT\CLSID\{F9AE8980-7E52-11d0-8964-00C04FD611D7}\OLE DB Provider]

@="Microsoft OLE DB Provider for Indexing Service"

[HKEY_CLASSES_ROOT\CLSID\{F9AE8980-7E52-11d0-8964-00C04FD611D7}\ProgID]

@="MSIDXS.1"


Proveedor Microsoft OLE DB para servicios de Index Server

     Provider=MSIDXS.1

     C:\WINNT\System32\Query.dll

Microsoft OLE DB Provider for Microsoft Index Server and Microsoft Site Server Search

version 5.0.1781.3

msidxs.dll

SQL Server OLE DLL del proveedor (Sqloledb.dll) como ejemplo

1.En el símbolo del sistema, cambie a la carpeta C:\Program Files\Common Files\System\Ole DB.

2.En el símbolo del sistema, escriba el comando siguiente: regsvr32 sqloledb.dll

Posted in ASP.NET, Windows | Etiquetado: , , , , | Leave a Comment »

Recycling Application Pools in IIS 6.0

Posted by kiquenet en 6 mayo 2010

A quick tip for those of us fond of iisreset. Restarting IIS takes quite a while. Sometimes 15-20 seconds, when my w3wp.exe processes are particularly unruly. In many cases, though, a full iisreset is unnecessary. Since IIS 6.0 expanded its process isolation model to include Application Pools, the simplest way is to simply recycle the application pool – takes only a second, and usually does the trick.

We can do this in .NET with very little effort:

using(DirectoryEntry appPool = new DirectoryEntry(string.Format("IIS://{0}/w3svc/apppools/{1}", host, poolname)))
{
   appPool.Invoke("Recycle");
}

Or, if we want to use this in automation scripts or post-build events, we can wrap this in a console application.

Fuente:
http://weblogs.asp.net/avnerk/archive/2005/10/09/427075.aspx

Posted in .NET, ASP.NET, Scripts | Etiquetado: , | Leave a Comment »

Sending/receiving cookies with HttpWebRequest/HttpWebResponse

Posted by kiquenet en 18 febrero 2010

When using a HttpWebRequest or a HttpWebResponse, you might need to send or receive cookies. If you try to access directly the Cookie property of your HttpWebRequest, no cookie will be returned.

HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://localhost/MyApp/MyPage");
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
// response.Cookies is always empty

It’s a bit tricky, but to be able to access the cookies, you need to first assign the CookieContainer of your request.

A CookieContainer contains cookies for many requests. In fact, while the CookieCollection just store a list of Cookie, the CookieContainer store many CookieCollection, each for a specified URI. It allows storing cookies for different URI in a single place.

HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://localhost/MyApp/MyPage");
CookieContainer cookieContainer = new CookieContainer();
request.CookieContainer = cookieContainer;
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
if (response.Cookies != null && response.Cookies.Count != 0)
{
    foreach(Cookie cookie in response.Cookies)
    {
      // Access to each cookie here
    }
}
else
{
    // No cookie
}

In some cases, you might need to perform several requests, GET or POST, toward several URLs, and pass each time the cookie information gathered from the previous requests. The easiest way to do that is to reuse your CookieContainer object. For instance:

CookieContainer cookieContainer = new CookieContainer();

HttpWebRequest request1 = (HttpWebRequest)WebRequest.Create("http://localhost/MyApp/MyPage1");
request1.CookieContainer = cookieContainer;
HttpWebResponse response1 = (HttpWebResponse)request1.GetResponse();

// Do something here

HttpWebRequest request2 = (HttpWebRequest)WebRequest.Create("http://localhost/MyApp/MyPage2");
request2.CookieContainer = cookieContainer;
HttpWebResponse response2 = (HttpWebResponse)request2.GetResponse();

By doing this way, you share your cookies between the two requests. The cookies retrieve in response1 are store in your cookieContainer, and then sent within request2. Cookies retrieved in response2 are added or updated inside your cookieContainer, and so on.

menet.name/blog/christophe

Posted in ASP.NET | Etiquetado: , , | Leave a Comment »