LINQ to SQL - Enabling Fulltext searching

LINQ to SQL allows you write your data access statements in your .Net code in LINQ which is then translated into SQL. LINQ supports many of the same constructs, predicates of SQL however some aren't supported.



One set of predicates that aren't supported are those for full text, i.e. CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE That means you can't write something like,

var mySearchList = from s in new SQLBitsDataContext().sessions

                  where s.Contains("Description,Title","simon")

                 select s;

You can however do

var mySearchList = from s in new SQLBitsDataContext().sessions

                  where s.description.Contains("simon")

                 select s;

So you might think that full text is supported. Unfortunately Contains is translated into a LIKE predicate with two wildcards, i.e.

select *

  from sessions

 where description like '%enrico%'


So how do you get it working. Well the magic is in Inline Table Valued Functions. I've talked about them before when discussing performance of scalar functions. An inline table valued function is essentially a prameterised SQL statement, and importantly only one statement. This means the optimiser can merge the SQL Statement into the query that uses it.

To get it working you need to create a table valued function that does nothing more than a CONTAINSTABLE query based on the keywords you pass in,

create function udf_sessionSearch

      (@keywords nvarchar(4000))

returns table

as

  return (select [SessionId],[rank]

            from containstable(Session,(description,title),@keywords))

 

You then add this function to your LINQ 2 SQL model and he presto you can now write queries like.

var sessList = from s   in DB.Sessions

               join fts in DB.udf_sessionSearch(SearchText) on s.sessionId equals fts.SessionId

             select s;


If you want you can extend this to limit the number of results from containstable. If you want to search different columns then you will need different functions as the colum list used by containstable cannot take a parameter.


Enable your device for development

Important  Currently you cannot enable devices installed with Windows 10 Insider Preview with the System Setting Developer Mode controls as outlined in this article. This feature is not currently supported, but will be enabled in a future release of Windows 10. So you don't need to follow the steps in the future approach in this article. But you must follow the links directly below to enable your specific Windows 10 device.

Enable developer mode

If you use Visual Studio on a Windows 10 desktop and you open a solution for a Windows 8.1 or Windows 10 app, you will be prompted to enable your device with this dialog. (You also need your device to be enabled to use the designers and debug your app.)

Enable developer mode dialog that is displayed in Visual Studio

When you see this dialog, click OK. Then follow the steps below to enable your desktop from Windows 10 Insider Preview.

For Windows 10 desktop

Use gpedit.msc to set the group policies to enable your device, unless you have Windows 10 Insider Preview Home Edition. If you do have Home Edition, you need to use regedit or PowerShell commands to set the registry keys directly to enable your device.

Dn706236.wedge(en-us,WIN.10).gifUse gpedit to enable your device

  1. Open a cmd prompt with administrator privileges.
  2. Run Gpedit.msc.
  3. Go to Local Computer Policy > Computer Configuration > Administrative Templates > Windows Components > App Package Deployment
  4. Edit the policies to enable the following:
    • Allow all trusted apps to install (Enables your device for sideloading apps)
    • Allows development of Windows Store apps and installing them from an integrated development environment (IDE) (Enables your device for development from Visual Studio)
  5. Reboot your machine.

Dn706236.wedge(en-us,WIN.10).gifUse regedit to enable your device

  1. Open a cmd prompt with administrator privileges.
  2. Run regedit.
  3. Set the value of this DWORD to 1: HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\AppModelUnlock\AllowAllTrustedApps
  4. Set the value of this DWORD to 1: HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\AppModelUnlock\AllowDevelopmentWithoutDevLicense

Dn706236.wedge(en-us,WIN.10).gifUse PowerShell to enable your device

  1. Run Windows PowerShell with administrator privileges.
  2. Run the following command: PS C:\WINDOWS\system32> reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\AppModelUnlock" /t REG_DWORD /f /v "AllowDevelopmentWithoutDevLicense" /d "1"
  3. Run this command too: PS C:\WINDOWS\system32> reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\AppModelUnlock" /t REG_DWORD /f /v "AllowAllTrustedApps" /d "1"

Future approach using System Setting Developer Mode controls

There is a different approach for development for Windows 10 devices. A developer license is no longer required for each device that you want to use to develop, install or test your app. You just enable a device once for these tasks from the settings for the device. That's it. No more renewing your developer licenses every 30 or 90 days!

If you are still using a Windows 8.1 device to develop or test your apps with Visual Studio 2013 or Visual Studio 2015, you still need to get a developer license or register your Windows Phone.

Develop your app with Visual Studio

If you use Visual Studio on a Windows 10 device and you open a solution for a Windows 8.1 or Windows 10 app, you will be prompted to enable your device with this dialog. (You also need your device to be enabled to use the designers and debug your app.)

Enable developer mode dialog that is displayed in Visual Studio

When you see this dialog, click OK. Then follow the steps below to enable your device from Windows 10 Insider Preview.

Enable your Windows 10 devices

For Windows 10 Insider Preview, you choose what tasks you want to enable on the device. This includes any devices: Windows 10 desktops, tablets and phones. You can enable a device for development, or just sideloading.

Sideloading is installing and then running or testing an app that has not been certified by the Windows store. For example, an app that is internal to your company only. (Does the Windows store allow sideloading?)

Note  If you sideload apps, you should still only install apps from trusted sources. When you install a sideloaded app that has not been certified by the Windows store, you are agreeing that you have obtained all rights necessary to sideload these apps and that you are solely responsible for any harm that results from installing and running these apps. See this privacy statement.

Dn706236.wedge(en-us,WIN.10).gifWindows 10 Desktops/tablets

  1. On your device that you want to enable, go to Settings. Choose Update & security. Then choose For developers.

    Go to Settings, choose Update and security and then choose For developers to view your options
  2. Choose the level that you need. Developer mode allows you to sideload apps too.

Dn706236.wedge(en-us,WIN.10).gifWindows 10 Phones

  1. On your device that you want to enable, go to Settings. Choose Update & security. Then choose For developers.

    From Settings on your phone, choose Update and security

  2. Choose the level that you need. Developer mode allows you to sideload apps too.

Web.config: encryption and decryption

Introduction

In this article I will explain how to encrypt or decrypt connection Strings in web.config file using asp.net.

Description

In Previous posts I explained lot of articles regarding Asp.net, Gridview, SQL Server, Ajax, JavaScript etc. In many of articles I used connection Strings section in web.config file to store database connection. The connection Strings section contains sensitive information of database connections including username and password of database. Is it secured to store the sensitive information of database connections in plain text files called web.config and machine.config files?

If we are using applications in our internal servers with security then it’s ok if we deploy our applications in shared host environment then we have chance to arise security problems to avoid these problems asp.net 2.0 provided built in protected configuration model functionality to encrypt or decrypt few sections of web.config file those are

RSAProtectedConfigurationProvider: This is default provider and uses the RSA public key encryption algorithm to encrypt and decrypt data.

DataProtectionConfgurationProvider: This provider uses windows data protection application programming interface to encrypt and decrypt the data.

The encrypting and decrypting of connection strings in web.config file will do by using aspnet_regiis.exe command line tool and code behind.

First Method

First we will do encryption and decryption using aspnet_regiis.exe command line tool in file system website

To implement encryption and decryption first create one new website using visual studio.

After that open web.config file in application and add sample db connection in connectionStringssection like this

Collapse | Copy Code
<connectionStrings>
<add name="dbconnection" connectionString="Data Source=RahulMittal;Integrated Security=true;Initial Catalog=MySampleDB"/>
</connectionStrings >

After add dbconnection in connectionString check the below steps to encrypt or decrypt the connection string in web.config.

1. 1) Go to Start >> All Programs >> Microsoft visual studio 2008 >> Visual Studio Tools >> Visual Studio 2008 Command Prompt (Note: if you’re using windows 7 right click on command prompt and select Run as administrator)

2. 2) after open command prompt type the following command aspnet_regiis.exe -pef “connectionStrings” “C:\VisualStudio2008\Authorization”

Here –pef indicates that the application is built as File System website. Second argumentconnectionStrings indicates that name of the configuration section needs to be encrypted. The Third argument is the physical path of the folder where the web.config file is located.

3. 3) after enter the command click enters if everything goes well we will get success message like “Encrypting configuration section… Succeeded!”

Now open your application and check connectionStrings in web.config file that would be like this

Collapse | Copy Code
<connectionStrings configProtectionProvider="RsaProtectedConfigurationProvider">
<EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"
xmlns="http://www.w3.org/2001/04/xmlenc#">
<EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />
<KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
<EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">
<EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />
<KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
<KeyName>Rsa Key</KeyName>
</KeyInfo>
<CipherData>
<CipherValue>ZNUbIEnOwlZzC8qbzHj5F2GS9gLYSkWCIgCJGkrgZAX8A+8oEIssyohhxUKvAubD3jizFc5IjbLGt7HNXhoFhXNTUPYz2y6tdKJDVgDmtCgVf8Z2C990zoMRBJG+VXhmgnlo1vtHYhGx8x/bBzE1prT1+xDpep98vHF22d+LrVI=</CipherValue>
</CipherData>
</EncryptedKey>
</KeyInfo>
<CipherData>
<CipherValue>tODWlPD0Q/B/mP14GQ/5tUxcjmhHcy9a0oPunV5osNrMQRztgi2h5V6sxJOEh+NC+G9gQNkv1huXf1s7eoZRRLy5/LDtLXzzqMUOqLSlJUs9igChvi33c9XG4rwGF15Tpn4N34bpQBt94n0rpSkQ18V9HCPzii+UO64PlA+ykDeQhc9aQr4gO3mCfUzmY2S9gsXzRbzdq0oCWBDvx8UkX2uDxaysVHC9Fo7u6IrlpU0+hOdK95Y3/A==</CipherValue>
</CipherData>
</EncryptedData>
</connectionStrings>

Here we don’t want to write any code to decrypt the encrypted connectionString in our application because .NET automatically decrypts it. If we want to use the connection string just call it like normal way

Collapse | Copy Code
string strconnection = ConfigurationManager.AppSettings["dbconnection"].ToString();

Now if we want to decrypt connectionStrings section in web.config use the following commandaspnet_regiis.exe -pdf “connectionStrings” “C:\VisualStudio2008\Authorization”

After command execute we will get message like “Decrypting configuration section… Succeeded!”

Now check your connctionStrings section in your web.config file you will see decrypted connection string.

Till now we learned how to encrypt and decrypt connectionStrings section in File system website. If I want to encrypt connection string in IIS based site like i.e. Deployed website for that we need to use the following commands

Encrypt connectionStrings in web.config of IIS based site

Collapse | Copy Code
aspnet_regiis.exe -pe "connectionStrings" -app "/SampleWebSite"

Here –pe indicates that the application is built as IIS based site. Second argument connectionStrings is the name of configuration section needs to be encrypted. The Third argument -app indicates virtual directory and last argument is the name of virtual directory where application is deployed.

Decrypt connectionStrings in web.config of IIS based site

Collapse | Copy Code
aspnet_regiis.exe -pd "connectionStrings" -app "/SampleWebSite"

Till now we learned how to encrypt and decrypt connectionStrings section in web.config file using aspnet_regiis.exe command line tool now I will explain code behind method to encrypt and decrypt the connection string section in web.config.

Second Method: In second method I will use RSAProtectedConfigurationProvider and DataProtectionConfgurationProvider to encrypt and decrypt connectionStrings in web.config using asp.net.

First open Default.aspx page and write the following code

Collapse | Copy Code
<html xmlns="http://www.w3.org/1999/xhtml">
<head  runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1"  runat="server">
<div>
<asp:Button id="btnEncrypt" runat="server" Text="Encrypt" onclick="btnEncrypt_Click" />
<asp:Button ID="btnDecrypt" runat="server" Text="Decrypt" onclick="btnDecrypt_Click" />
</div>
</form>
</body>
</html>

After that open code behind page and add the following namespace references

Collapse | Copy Code
using System;
using System.Configuration;
using System.Web.Configuration;

After add namespaces write the following code in code behind

C# code

Collapse | Copy Code
string provider = "RSAProtectedConfigurationProvider";
string section = "connectionStrings";
protected void Page_Load(object sender, EventArgs e)
{   }
protected void btnEncrypt_Click(object sender, EventArgs e)
{
   Configuration confg = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath);
   ConfigurationSection configSect = confg.GetSection(section);
   if (configSect != null)
   {
      configSect.SectionInformation.ProtectSection(provider);
      confg.Save();
   }
}   protected void btnDecrypt_Click(object sender, EventArgs e)
{
   Configuration config = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath);
   ConfigurationSection configSect = config.GetSection(section);
   if (configSect.SectionInformation.IsProtected)
   {
      configSect.SectionInformation.UnprotectSection();
      config.Save();
   }
}

VB.NET

Collapse | Copy Code
Imports System.Web.Configuration
Partial Class _Default
   Inherits System.Web.UI.Page
   Private provider As String = "RSAProtectedConfigurationProvider"
   Private section As String = "connectionStrings"
   Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
   End Sub
   Protected Sub btnEncrypt_Click(ByVal sender As Object, ByVal e As EventArgs)
      Dim confg As Configuration = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath)
      Dim confgSect As ConfigurationSection = confg.GetSection(section)
      If confgSect IsNot Nothing Then
         confgSect.SectionInformation.ProtectSection(provider)
         confg.Save()
      End If
   End Sub
   Protected Sub btnDecrypt_Click(ByVal sender As Object, ByVal e As EventArgs)
      Dim config As Configuration = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath)
      Dim confgSect As ConfigurationSection = config.GetSection(section)
      If confgSect.SectionInformation.IsProtected Then
         confgSect.SectionInformation.UnprotectSection()
         config.Save()
      End If
   End Sub
End Class

After that open web.config file in application and add sample db connection in connectionStrings section like this

Collapse | Copy Code
<connectionStrings>
<add name="dbconnection" connectionString="Data Source=RahulMittal;Integrated Security=true;Initial Catalog=MySampleDB"/>
</connectionStrings >

Now run your application and check your web.config file after click on Encrypt button that would be like this

Collapse | Copy Code
<connectionStrings configProtectionProvider="RsaProtectedConfigurationProvider">
<EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"
xmlns="http://www.w3.org/2001/04/xmlenc#">
<EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />
<KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
<EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">
<EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />
<KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
<KeyName>Rsa Key</KeyName>
</KeyInfo>
<CipherData>
<CipherValue>WagJ9DDjWTNc1nmYVNQXaQqXalQzXaiCHAOtUJvTWBRZiuT6UK1fBElM80PnL6dC5Umb8qvfHdkSMgoMW9CJzwOTZ0zTy17JBGZqRQmlfW2G9LacoWIil0UrxjhgmJmRXhwXHFpdGwEVl7AoQGVlJGabXuChutaTxmfGOoUbCr0=</CipherValue>
</CipherData>
</EncryptedKey>
</KeyInfo>
<CipherData>
<CipherValue>qry5qnr3qxOgyoNPeP7OKEiHpr/PPTsaeQ2mYUsSK7cg4Kkl9uPO4RyUXgBIkgCTsjbObqLlyndcSBnYyek6bxG/IBL82G1R5J1ci8i1eyt8kIDqouzYOx5vtouErld4z1L+7WGf9Wg37QAH5RiiEfkCHndJJq3dTqjxnnXZSno6NgbxSXDfqzwE/eKDVhGV3oaTQSfjVmO8e5a9wvREYeeyasDhojx8J2mdy7/Q9rEIpv98RTiRxA==</CipherValue>
</CipherData>
</EncryptedData>
</connectionStrings> 

Advertsing

125X125_06

TagCloud

MonthList

CommentList