Sorting, Filtering, and Paging with the Entity Framework in an ASP.NET MVC Application

  public ViewResult Index(string sortOrder, string currentFilter, string searchString, int? page)
        {
  ViewBag.CurrentSort = sortOrder;
            ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "Name_desc" : "";
            ViewBag.DateSortParm = sortOrder == "Date" ? "Date_desc" : "Date";

            if (searchString != null)
            {
                page = 1;
            }
            else
            {
                searchString = currentFilter;
            }

            ViewBag.CurrentFilter = searchString;

     
            var DbMail = from s in db.tbMessages
                         select s;
            if (!String.IsNullOrEmpty(searchString))
            {
                DbMail = DbMail.Where(s => s.FromUserID.ToUpper().Contains(searchString.ToUpper())
                                       || s.ToUserID.ToUpper().Contains(searchString.ToUpper())
                                       || s.Message.ToUpper().Contains(searchString.ToUpper()));
            }
            switch (sortOrder)
            {
                case "Name_desc":
                    DbMail = DbMail.OrderByDescending(s => s.FromUserID);
                    break;
                case "Date":
                    DbMail = DbMail.OrderBy(s => s.SentDate);
                    break;
                case "Date_desc":
                    DbMail = DbMail.OrderByDescending(s => s.SentDate);
                    break;
                default:
                    DbMail = DbMail.OrderBy(s => s.SentDate);
                    break;
            }

            int pageSize = 10;
            int pageNumber = (page ?? 1);

            return View(DbMail.ToPagedList(pageNumber, pageSize));
        }



@model PagedList.IPagedList<Mail.Models.tbMessage>
@using PagedList.Mvc;
@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
@*@using (Html.BeginForm())
{
    <p>
        Find : @Html.TextBox("SearchString")
        <input type="submit" value="Search" />
    </p>
}*@
@using (Html.BeginForm("Index", "mail", FormMethod.Get))
{
    <p>
        Find : @Html.TextBox("SearchString", ViewBag.CurrentFilter as string)
        <input type="submit" value="Search"/>
    </p>
}
<table class="table">
    <tr>
        @*<th>
                @Html.DisplayNameFor(model => model.Code)
            </th>*@
        <th>
            @Html.ActionLink("From Name", "Index", new { sortOrder = ViewBag.NameSortParm })

            @*@Html.DisplayNameFor(model => model.FromUserID)*@
        </th>
        <th>

            @*@Html.DisplayNameFor(model => model.ToUserID)*@
        </th>
        <th>
            @Html.ActionLink("SentDate", "Index", new { sortOrder = ViewBag.NameSortParm })
            @*   @Html.DisplayNameFor(model => model.SentDate)*@
        </th>
        <th>
            <text>Subject</text>
            @*@Html.DisplayNameFor(model => model.SubjectLine)*@
        </th>
        <th>

            <text>Message</text>
        </th>

        <th></th>
    </tr>

    @foreach (var item in Model)
    {
        <tr>
            @*<td>
                    @Html.DisplayFor(modelItem => item.Code)
                </td>*@
            <td>
                @Html.DisplayFor(modelItem => item.FromUserID)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.ToUserID)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.SentDate)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.SubjectLine)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Message)
            </td>

            <td>
                @Html.ActionLink("Edit", "Edit", new { id = item.ID }) |
                @Html.ActionLink("Details", "Details", new { id = item.ID }) |
                @Html.ActionLink("Delete", "Delete", new { id = item.ID })
            </td>
        </tr>
    }

</table>

Page @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber) of @Model.PageCount

@Html.PagedListPager(Model, page => Url.Action("Index", new { page, sortOrder = ViewBag.CurrentSort, currentFilter = ViewBag.CurrentFilter }))

Passively detect attempts to guess passwords

This scripts looks for possible malicious attempts to hack your SQL Server instance. This is done by reading the current error log and searching for multiple failed login attempts from the same IP address. Please note that this requires that “Login auditing for failed logins” is enabled in the properties of your SQL Server instance.

To run this script, simply copy and paste the script into a connected query tab in Microsoft SQL Management Studio and then execute it. It will output a summary list of all failed attempts to guess passwords within the last 24 hours on your SQL Server instance.

begin

create table #errorLog (
LogDate datetime,
ProcessInfo varchar(250),
[Text] varchar(8000)
)


--read the current error log
insert into #errorLog (LogDate, ProcessInfo, [Text])
exec sp_readerrorlog 0, 1 --0 = current(0), 1 = error log


--find brute force attempts to guess a password
select
replace(right([Text],charindex(' ', reverse([Text]))-1), ']', '') as IP,
substring([Text], charindex('''', [Text]) + 1,  charindex('.', [Text]) - charindex('''', [Text]) - 2  ) as [User],
count(LogDate) as [Number of login attempts],
min(LogDate) as [Attack started],
max(LogDate) as [Attack ended],
datediff(minute, min(LogDate), max(LogDate)) as [Attack duration in minutes],
cast(cast(count(LogDate) as decimal(18,2))/isnull(nullif(datediff(minute, min(LogDate), max(LogDate)),0),1) as decimal(18,2)) as [Attack intensity - Login attempts per minute]

from #errorLog

where
--limit data to unsuccessful login attempts in the last 24 hours
ProcessInfo = 'Logon'
and [Text] like 'Login failed for user%'
and datediff(hour, LogDate, getdate()) <= 24

group by
[Text]

having
count(LogDate) > 3 --filter out users just typing their passwords incorrectly

order by
[Number of login attempts] desc,
[Attack ended] desc



--clean up temp tables created
drop table #errorLog


end

What's changed recently and List of Stored Procedures modified in past 7 days

SELECT TOP 22 * FROM sys.objects ORDER BY modify_date DESC


SELECT * FROM sys.objects
WHERE TYPE = 'P' AND DATEDIFF(D,modify_date, GETDATE()) < 7

To remove the specified Characters in the Given String

Alphabetic only: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z')
Numeric only: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^0-9+-/')
Alphanumeric only: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z0-9')
Non-alphanumeric: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', 'a-z0-9')

CREATE FUNCTION GEN_FN_StripCharacters

(

    @strInputString NVARCHAR(MAX), 

    @strMatchExpression VARCHAR(255)

)

/*

---Created By : Ram    

--Date : 15-Feb-2013

--- Purpose : To remove the specified Characters in the Given String

Alphabetic only: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z')

Numeric only: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^0-9+-/')

Alphanumeric only: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z0-9')

Non-alphanumeric: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', 'a-z0-9')

*/

RETURNS NVARCHAR(MAX)

AS

BEGIN

    SET @strMatchExpression =  '%['+@strMatchExpression+']%'

    WHILE PatIndex(@strMatchExpression, @strInputString) > 0

        SET @strInputString = Stuff(@strInputString, PatIndex(@strMatchExpression, @strInputString), 1, '')

    RETURN @strInputString

END

http://www.sqlservercentral.com/scripts/Remove+character+or+number+from+String/99755/

Query for comma-separated ids to comma-separated values

http://stackoverflow.com/questions/14612394/query-for-comma-separated-ids-to-comma-separated-values

use interview

DECLARE @Departments TABLE
(
  ID INT PRIMARY KEY,
  Dept VARCHAR(32) NOT NULL UNIQUE
);

DECLARE @Employees TABLE
(
  ID INT PRIMARY KEY,
  Name NVARCHAR(64) NOT NULL,
  Depts VARCHAR(255) NOT NULL
);

INSERT @Departments VALUES
  (1,'HR'),  (2,'Accts'),  (3,'IT');

INSERT @Employees VALUES
  (1,'Kevin','2,1'), (2,'Michelle','1'),
  (3,'Troy','1,3'),  (4,'Rheesa','2,3,1');

SELECT ID, Name, Depts = STUFF((SELECT ',' + d.Dept
    FROM @Departments AS d
    INNER JOIN @Employees AS ei
    ON ',' + ei.Depts + ',' LIKE '%,' + CONVERT(VARCHAR(12), d.id) + ',%'
    WHERE ei.ID = e.ID
    ORDER BY Dept
    FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
FROM @Employees AS e
ORDER BY ID;

SOAP Interface

http://msdn.microsoft.com/en-us/library/ff512390.aspx

Using the SOAP Interface

The SOAP interface supports client application scenarios and a rich .NET service client programming model. Developers can use their development technology of choice with this interface.

To start using the SOAP interface for the Microsoft Translator service you need SOAP web service reference
To add reference to SOAP web service, right-click on the Visual Studio 2010 project and click on Add Service Reference... Then provide address as http://api.microsofttranslator.com/V2/Soap.svc and click Go. Once service information is downloaded, provide Namespace as "TranslatorService" and click Ok.

This will generate the proxy code in a LanguageServiceClient class in the project. In order to call the SOAP web service in the C# code, an instance of the LanguageServiceClient class is required. This instance is created using a static constructor

C#

                                
TranslatorService.LanguageServiceClient client = new TranslatorService.LanguageServiceClient();
                            

Authorization Header

To make request to any method of Microsoft translator you will need access token. Process of obtaining access token is explained in detailed at Obtain Access Token. Use the value of access token in the Authorization header of the subsequent calls to the Microsoft Translator API. This Token is valid for 10 minutes.

Authorization header value should be in following format. (Note: One BLANK space between Bearer and Access Token Value)

"Bearer" + " " + Access Token Value

To send this authorization header as a part of SOAP request, define a variable of type HttpRequestMessageProperty.

C#

                                
HttpRequestMessageProperty httpRequestProperty = new HttpRequestMessageProperty();
                            

Now to call Translate method, we have to provide all required parameters. appId is required parameter but as we are using Authorization header leave appId parameter blank.
Code block below ensures that Authorization header added to the SOAP request.

C#

                                
// Creates a block within which an OperationContext object is in scope.
using (OperationContextScope scope = new OperationContextScope(client.InnerChannel))
{
    OperationContext.Current.OutgoingMessageProperties[HttpRequestMessageProperty.Name] = httpRequestProperty;
    string sourceText = "Use pixels to express measurements for padding and margins.";
    string translationResult;
    //Keep appId parameter blank as we are sending access token in authorization header.
    translationResult = client.Translate("", sourceText, "en", "de", "text/plain", "");
    Console.WriteLine("Translation for source {0} from {1} to {2} is", sourceText,"en","de");
    Console.WriteLine(translationResult);
}