LINQ to SQL: How to use SQL "IN" keyword in LINQ?

If you have been worked with T-SQL, You know that we have an IN keyword in our WHERE clause like the following instance:

SELECT * FROM MyTable WHERE ID IN (1, 2, 3)

In the example above, I selected a table from my database named "MyTable" and then select rows that the ID column of each row is equal to 1, 2 and 3. If rows are exists so the select statement should return 3 results.

When you’re working with .NET framework 3.5, You may need to do such a thing using LINQ to SQL. In this post I’ll show you how to do that.

Before we get started, lets review some simple LINQ commands and basics:

  • In order to start using LINQ to SQL, You have to create a new item named LINQ to SQL Class by right click on your project in solution explorer, point to add menu and select new item as shown below:

 AddNewItem

  • In the new item windows select LINQ to SQL class and name it SampleDb:

 SelectLinqtoSqlClass

  • Now it’s time to add some SQL tables from our SQL Server Database by drag them from Server Explorer (Available in View menu) and drop them to our LINQ to SQL Class designer. When you do such a thing, Visual Studio adds some classes with properties and methods to your project automatically.

AddSomeTables 

  • We can add some code in order to access our data. First you have to declare a new variable from our LINQ to SQL class. By default, the name of our LINQ to SQL class is YouClassName + DataContext. So here it is:

private SampleDbDataContext db = new SampleDbDataContext();

You can select, insert, update and delete rows from your selected table(s). What we’re looking for right now is to select 4 specific rows from our table to simulate SQL "IN" keyword. In order to do this, have to create an array of your selected IDs. In this case I want to select rows with IDs (1, 2, 3); So just create an integer array as following:

int[] MySelectedRows = {1, 2, 3} ;

Now lets write some codes in order to select some columns from our table:

var MyData = (from data in db.Mails
                          select data);

With the code above, you can select all rows in Mails table. Now we have to specify to select only rows with IDs 1, 2 and 3 so we must add a where clause to our select statement:

var MyData = (from data in db.Mails
                          where data.MailID == Convert.ToInt32(MySelectedRow)
                          select data);

Upcoming Project: WithXml

I’ve just started working on my new project named WithXml with my friend Omid Mafakher.

What is WithXml?

WithXml is a core for publishing website in a full-XML-based environment for make websites faster like never before.

As you know XML is a common data storage for blogging engines and most of blog engines like Dasblog, BlogEngine.NET and more are using XML as a data storage.

WithXml is a very easy to use engine enable you to create blogs, forums, pages and even websites using XML technologies and Microsoft .NET Framework 3.5 or later.

WithXml project will be available to download and use in 2 months.

My best co-worker ever, left me alone for a while

In my opinion when you’re working in a company (specially developing companies), you will work most of your day, so you will live with your co-workers. I had a lot of co-workers till now but I think Omid Mafakher was the best co-worker I’ve ever had. Omid was not only a professional developer but he was a person who helps me a lot in my life. He left us because of his military service and he will be available in 2 months. Anyway, Thank you Omid for all your supports.

MEIZU! Is it really an "iPod killer"?

I really love listening to music while I’m working. I was my cell phone (Nokia N70 Music Edition) in order to listen to music when I was at work.

sc006

A few weeks ago I decided to buy a MP3 player In order to listen to music much better. I went to buy a iPod Nano because I think it’s the best MP3 player in the world. I went to the shop to buy it, suddenly one of the customers that was went there in order to buy something, told me something about a new MP3 player that is an iPod Killer. It name was MEIZU and the person was talked about a lot; For example: "This is much better that iPod" he said.

As a matter of fact, MEIZU has some advantages against iPod:

  1. The cost is much lower than IPod.
  2. It supports a lot of extensions like: .MP3, .WMA, .OGG, .FLAC, .WAV, …
  3. It supports .AVI (XVID) format for movies.

and lots of other advantages.

When I saw that with all of it’s advantages, I decided to buy MEIZU instead of I pod. Here is a picture of my MP3 player:

30-C

 

What’s your idea about it?

What is Master Page?

master page is similar to an ordinary ASP.NET page except for the top @Master directive and the presence of one or more ContentPlaceHolder server controls. A ContentPlaceHolder control defines a region in the master page that can be customized in a derived page. A master page without content placeholders is technically correct and will be processed correctly by the ASP.NET runtime. However, a placeholderless master fails in its primary goal—to be the supertemplate of multiple pages that look alike. A master page devoid of placeholders works like an ordinary Web page but with the extra burden required to process master pages. Here is a simple master page:

<%@ Master Language="C#" CodeFile="Simple.master.cs" Inherits="Simple" %>
<html>
<head runat="server">
    <title>Hello, master pages</title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:Panel ID="HeaderPanel" runat="server"
            BackImageUrl="Images/SkyBkgnd.png" Width="100%">

            <asp:Label ID="TitleBox" runat="server"
                Text="Programming ASP.NET 2.0" />
        </asp:Panel>
        <asp:contentplaceholder id="PageBody" runat="server">
         <!-- derived pages will define content for this placeholder -->
       </asp:contentplaceholder>
        <asp:Panel ID="FooterPanel" runat="server"
            BackImageUrl="Images/SeaBkgnd.png">
            <asp:Label ID="SubTitleBox" runat="server"
                 Text="Dino Esposito" />
        </asp:Panel>
    </form>
</body>
</html>

As you can see, the master page looks like a standard ASP.NET page. Aside from the identifying @Master directive, the only key differences are ContentPlaceHolder controls. A page bound to this master automatically inherits all the contents of the master (the header and footer, in this case) and can attach custom markup and server controls to each defined placeholder. The content placeholder element is fully identified by its ID property and normally doesn’t require other attributes.

The @Master Directive

The @Master directive distinguishes master pages from content pages and allows the ASP.NET runtime to properly handle each. A master page file is compiled to a class that derives from the MasterPage class. The MasterPage class, in turn, inherits UserControl. So, at the end of the day, a master page is treated as a special kind of ASP.NET user control.

Description

ClassName

Specifies the name for the class that will be created to render the master page. This value can be any valid class name but should not include a namespace. By default, the class name for simple.master is ASP.simple_master.

CodeFile

Indicates the URL to the file that contains any source code associated with the master page.

Inherits

Specifies a code-behind class for the master page to inherit. This can be any class derived from MasterPage.

MasterPageFile

Specifies the name of the master page file that this master refers to. A master can refer to another master through the same mechanisms a page uses to attach to a master. If this attribute is set, you will have nested masters.

The master page is associated with a code file that looks like the following:

public partial class Simple : System.Web.UI.MasterPage {
    protected void Page_Load(object sender, EventArgs e) {
        ...
    }
}

The @Master directive doesn’t override attributes set at the @Page directive level. For example, you can have the master set the language to Visual Basic .NET and one of the content pages can use C#. The language set at the master page level never influences the choice of the language at the content page level. You can use other ASP.NET directives in a master page—for example, @Import. However, the scope of these directives is limited to the master file and does not extend to child pages generated from the master.

Note

You can create master pages programmatically. You build your own class and make it inherit MasterPage. Then you create .master files in which the Inherits attribute points to the fully qualified name of your class. Rapid application development (RAD) designers such as the one embedded in Microsoft Visual Studio .NET 2005 use this approach to create master pages.

The ContentPlaceHolder Container Control

The ContentPlaceHolder control acts as a container placed in a master page. It marks places in the master where related pages can insert custom content. A content placeholder is uniquely identified by an ID. Here’s an example:

<asp:contentplaceholder runat="server" ID="PageBody" />

A content page is an ASP.NET page that contains only <asp:Content> server tags. This element corresponds to an instance of the Content class that provides the actual content for a particular placeholder in the master. The link between placeholders and content is established through the ID of the placeholder. The content of a particular instance of the Content server control is written to the placeholder whose ID matches the value of the ContentPlaceHolderID property, as shown here:

<asp:Content runat="server" contentplaceholderID="PageBody">
    ...
</asp:Content>

In a master page, you define as many content placeholders as there are customizable regions in the page. A content page doesn’t have to fill all the placeholders defined in the bound master. However, a content page can’t do more than just fill placeholders defined in the master.

Note

A placeholder can’t be bound to more than one content region in a single content page. If you have multiple <asp:Content> server tags in a content page, each must point to a distinct placeholder in the master.

Specifying Default Content

A content placeholder can be assigned default content that will show up if the content page fails to provide a replacement. Each ContentPlaceHolder control in the master page can contain default content. If a content page does not reference a given placeholder in the master, the default content will be used. The following code snippet shows how to define default content:

<asp:contentplaceholder runat="server" ID="PageBody">
    <!-- Use the following markup if no custom
        content is provided by the content page -->
    ...
</asp:contentplaceholder>

The default content is completely ignored if the content page populates the placeholder. The default content is never merged with the custom markup provided by the content page.

Note

A ContentPlaceHolder control can be used only in a master page. Content placeholders are not valid on regular ASP.NET pages. If such a control is found in an ordinary Web page, a parser error occurs.

Writing a Content Page

The master page defines the skeleton of the resulting page. If you need to share layout or any UI block among all the pages, placing it in a master page will greatly simplify management of the pages in the application. You create the master and then think of your pages in terms of a delta from the master. The master defines the common parts of a certain group of pages and leaves placeholders for customizable regions. Each content page, in turn, defines what the content of each region has to be for a particular ASP.NET page.

The Content Control

The key part of a content page is the Content control—a mere container for other controls. The Content control is used only in conjunction with a corresponding ContentPlaceHolder and is not a standalone control. The master file that we considered earlier defines a single placeholder named PageBody. This placeholder represents the body of the page and is placed right below an HTML table that provides the page’s header.

Attaching Pages to a Master

In the previous example, the content page is bound to the master by using the MasterPageFile attribute in the @Page directive. The attribute points to a string representing the path to the master page. Page-level binding is just one possibility—although it is the most common one.

You can also set the binding between the master and the content at the application or folder level. Application-level binding means that you link all the pages of an application to the same master. You configure this behavior by setting the Master attribute in the <pages> element of the principal web.config file:

<configuration>
    <system.web>
        <pages master="MyApp.master" />
    </system.web>
</configuration>

If the same setting is expressed in a child web.config file—a web.config file stored in a site subdirectory—all ASP.NET pages in the folder are bound to a specified master page.

Note that if you define binding at the application or folder level, all the Web pages in the application (or the folder) must have Content controls mapped to one or more placeholders in the master page. In other words, application-level binding prevents you from having (or later adding) a page to the site that is not configured as a content page. Any classic ASP.NET page in the application (or folder) that contains server controls will throw an exception.

Device-Specific Masters

Like all ASP.NET pages and controls, master pages can detect the capabilities of the underlying browser and adapt their output to the specific device in use. ASP.NET 2.0 makes choosing a device-specific master easier than ever. If you want to control how certain pages of your site appear on a particular browser, you can build them from a common master and design the master to address the specific features of the browser. In other words, you can create multiple versions of the same master, each targeting a different type of browser.

How do you associate a particular version of the master and a particular browser? In the content page, you define multiple bindings using the same MasterPageFile attribute, but you prefix it with the identifier of the device. For example, suppose you want to provide ad hoc support for Microsoft Internet Explorer and Netscape browsers and use a generic master for any other browsers that users employ to visit the site. You use the following syntax:

<%@ Page masterpagefile="Base.master"
    ie:masterpagefile="ieBase.master"
    netscape6to9:masterpagefile="nsBase.master" %>

The ieBase.master file will be used for Internet Explorer; the nsBase.master, on the other hand, will be used if the browser belongs to the Netscape family, versions 6.x to 9.0. In any other case, a device-independent master (Base.master) will be used.

Using CommandName and CommandArgument properties of a control

Some of the times you may need to use just one method for many controls like when you can to add a Button control to a GridView control for deleting a row manually (Because ASP.NET itself has such a thing).

 

in order to do this you cannot create a method for each row of GridView because of the following reasons:

  • You don’t know how much rows you may have.
  • You have to spend a lot of times to do such a thing.
  • You cannot (In some cases).

 

So lets see what should we do in order to create a these king of controls:

First of all create a GridView control and name it "GV_Items":

<asp:GridView runat="server" ID="GV_Items"></asp:GridView>

After that, add some columns to our GridView as following. Notice that I create a <TemplateField> as one of my columns and place a Button control in it:

<asp:GridView runat="server" ID="GV_Items" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Tel" HeaderText="Phone number" />
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Button runat="server" ID="B_Delete" CommandName="Del" Text="Delete" CommandArgument='<%#Eval("PersonID")%>’ />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Our GridView control will be something like this:

GridViewControl

As you can see, I set a CommandName (Del) and a CommandArgument (<%Eval("PersonID")%>) for my button. I preferred to get the value of my CommandArgument property from my database; So I used Eval keyword to that. (With Eval Keyword you can get a data from your database in Databound control such as GridView, Repeater, DataList and more. For more information you can check out: http://www.15seconds.com/issue/040630.htm)

Now it’s time to write some code for this project. So lets go to code-behind mode and add a new method:

What we need is to create a method that delete a specified person from our database:

private void DeleteItem(int ItemId)
        {
            …
        }

GridView Control has a event named RowCommand that enable you to manage your specified command names. I want to write some codes for this event so:

protected void GV_Items_RowCommand(object sender, GridViewCommandEventArgs e)
{
    if (e.CommandName == "Del")
    {
        DeleteItem(Convert.ToInt32(e.CommandArgument));
    }
}

I just used a if clause in order to check if my CommandName that sent to my GridView if "Del" (The CommandName that we set for our Button). If it’s true, So delete the record by the DeleteItem method that we wrote. Our DeleteItem(int ItemId) requires a parameter to set which record of our database should be deleted. In order to set the parameter, I just used the parameter that we specified for my  button (<%#Eval("PersonID")%>) than returns record’s Id.

 

Now you know how to use the CommandName and CommandArgument properties of a control in order to develop better applications.

Create a printable page in your websites using CSS

It may happen to any website developer that he/she want to create a printer-friendly page without having to generate another web page manually or dynamically. In this post I’ll explain how to do this without any server-side languages but in CSS.

First, create a separate style sheet containing CSS rules that dictate the desired look when a page is printed. For this example, the style sheet with print-only CSS rules is named print.css.

Then associate the style sheet and set the media property to print:

<link rel="stylesheet" type="text/css" href="adv.css" media="screen" />

<link rel="stylesheet" type="text/css" href="print.css" media="print" />

We just declared two link tags which contain two css styles for different situations.
Pay attention to the media attributes in the link tags above. As you can see, value of the first media attribute is screen and the second is print. These values will set the css style of the page in different situations. For example if you set the value to "screen" you can see that the css file is working only in colored monitors.

 

So lets see possible values for the media attribute of link tag:

 

Media type Description
all

Suitable for all devices
braille

Intended for Braille tactile feedback devices
embossed

Intended for paged Braille printers
handheld

Intended for handheld devices (typically small-screen, limited-bandwidth devices)
print

Intended for paged material and for documents viewed on-screen in print preview mode
projection

Intended for projected presentationsfor example, projectors
screen

Intended primarily for color computer screens
speech

Intended for speech synthesizers
tty

Intended for media using a fixed-pitch character grid (such as teletypes, terminals, or portable devices with limited display capabilities)
tv

Intended for television-type devices (with low-resolution, limited-scrollable color screens, and available sound)

You can also assign a css style for all situations by set the media attribute’s value to "all":

<link rel="stylesheet" type="text/css" href="uber.css" media="all" />

Or you can use one style sheet for several, but not all, media:

<link rel="stylesheet" type="text/css" href="print.css" media="print,projection" />

In the preceding code, the print.css style sheet is used for projection and print media when rendering the web document.

Read emails data from a CSV file to database

Sometimes you may need to import contact informations that you've exported from a mail service providers like Yahoo!, Windows Live and more to your database. In order to this you have to the following steps:

1. Write a method for add a record to database:
In order to insert only mails, you have to check the strings that come from CSV file to validate if the string is email address so:

Private Function AddNewEmail(ByVal Email As String, ByVal CatId As Int32) As Int32
    Dim check = From ma In c.Mails _
                Where ma.Email = Email _
                Select c
    If check.Count() = 0 Then
        Dim m As New Mail
        m.CatID = CatId
        m.Email = Email
        c.Mails.InsertOnSubmit(m)
        c.SubmitChanges()
    End If
End Function

2. Check the email validations:

Private Function ValidMail(ByVal EmailAddress As String) As Boolean
    If String.IsNullOrEmpty(EmailAddress) Then
        Return False
    End If

    If EmailAddress.IndexOf("@") > -1 Then
        If (EmailAddress.IndexOf(".", EmailAddress.IndexOf("@")) > EmailAddress.IndexOf("@")) Then
            Return True
        End If
    End If

    Return False
End Function

In this section you have to write a method to connect to your database and insert a new record. I used LINQ to SQL becasue my project was based on .NET Framework 3.5:

3. Write a function to read data from CSV file and import them to our database using two previous functions:

Public Function ImportFile(ByVal FilePath As String, ByVal CatID As Int32) As Int32
    Dim result As Int32 = 0
    Dim CurrentLine() As String
    Using Stream As IO.StreamReader = System.IO.File.OpenText(FilePath)
        CurrentLine = Nothing
        While Not Stream.EndOfStream
            CurrentLine = Stream.ReadLine().Split(","c)
            If CurrentLine IsNot Nothing Then
                For Each c As String In CurrentLine
                    If ValidMail(c) Then
                        Dim final As String = c
                        If c.Contains(Left("""", 1)) Then
                            final = final.Replace(Left("""", 1), String.Empty)
                        End If
                        AddNewEmail(final, CatID)
                        result += 1
                    End If
                Next
            End If
        End While
    End Using
    Return result
End Function

I've just created a StreamReader variable that holds the file texts and then read the Steam line-by-line.

How Hackers Attack SQL Server

Direct Connection to the Internet

Exposing any operating system or application directly to the Internet without the use of a firewall is a bad thingno matter whether you are using Linux, UNIX, Windows, or any other operating system. It’s rather like the carnival game where someone sits on a platform above a pool of water waiting for someone else to throw a ball and hit the bull’s eye. When it happens, the person sitting on the platform is plunged into the water. Why expose your system, allowing anyone to take shots until they finally get you soaked? Microsoft has done a lot of work around protecting its operating systems and applications out of the box. When an exploit happens, they quickly address these problems and provide fixes.

This is only half of the battle, though. With all the switches and states of security for various products, it is not that difficult for an administrator or user to accidentally misconfigure something and expose the systems to exploits. To mitigate these issues, it is very important that users isolate their systems from the Internet via firewalls and use other isolation techniques.

Weak SA Passwords

One of the easiest ways to give someone the keys to SQL Server is by providing a weak password for the system administrator account. In previous versions of SQL Server, it was possible to give a blank password for the SA account without much complaint from SQL Server itself. SQL Server 2005 has a lot more functionality around password policies and enforcement. Previously in this chapter, we discussed this topic in regard to SQL Server authentication accounts obeying the group policies of the Windows domain. Configuring a strong password length and account lockout in your domain will ensure that all users of SQL Server are supplying passwords that are more difficult to crack.

SQL Server Browser Service

SQL Server uses UDP port 1434 to return SQL Server instance names and port numbers to the requesting client. A few years back, this enumeration was the key to the "SQL Slammer" DoS virus. By consistently hitting the server with requests for enumeration, the virus left the server too busy to process other requests. In SQL Server 2005, this enumeration functionality is in a separate service called the SQL Server Browser service. The functionality no longer runs in SQL Server’s process space, and it can be turned on and off without affecting SQL Server. If you do not want to use the SQL Server Browser service, you can still connect to other instances on your server, but the connection string must contain additional information (such as a specific port number in the case of TCP connections). If you want to use the Browser service in your organization, you can mitigate additional attacks by blocking UDP port 1434 on your firewall.

SQL Injection

SQL injection is the process by which a malicious user enters SQL statements instead of valid input. For example, suppose a Web site is asking for a user name. Instead of actually typing in a user name, a malicious user might type ‘blah’; DROP TABLE sales;. The Web server will happily take the user input and pass it to its middle layer, where it is executed in code as follows:

SqlCommand cmd = new SqlCommand("SELECT * FROM sales WHERE name='" + s_Customername + "'
",myConnection)

To SQL Server, it looks like the following:

SELECT * FROM sales WHERE name='blah'; DROP TABLE sales;

When it is executed, the sales table will be erased. You can see how easy it can be for malicious users to cause problems and return potentially sensitive information via simple inputs to Web pages or applications that directly use user input. To mitigate this particular issue, you can add the user input as a parameter to the SqlCommand, as shown below:

Using (SqlCommand cmd = new SqlCommand("SELECT * FROM sales WHERE name= @s_CustomerName"
, myConnection));
{
cmd.Parameters.Add("@s_CustomerName",s_CustomerName);
...

To SQL Server, whatever the user types will be considered just the value of the name part of the where clause.

Intelligent Observation

With the advent of powerful search engines such as Google and MSN Search, finding things on the Web is relatively easy. Web crawlers from these search engines go off and fetch key words and place them into their own internal database. These key words are used within their own search algorithms so that when you type something to search on, the search engine can easily return a list of possible choices. These crawlers not only search for and store such things as Web sites for pizza places, but they also obtain various kinds of error information returned from Web servers. This information is very valuable to a hacker. For example, if a hacker types invalid password access denied into the search string in MSN, he’ll get a list of various topics that are, in general, not that interesting. However, one item will show this string: Warning: mysql_pconnect(): Access denied for user ‘root’@’localhost’ (using password: YES) in /home/vhosts/<<removed for legal reasons>>/docs/citeheader.inc.php on line 2. A hacker knows that this site is using MySQL and PHP and now knows some of the directory structure of the Web site /home/vhosts/<<removed for legal reasons>>/docs. Now the hacker can try to query that individual directory path using his Internet browser to see if he can uncover any additional goodiesa script file, perhaps. If he finds a script file in this directory and the developer has hardcoded login credentials to the database, he is one connection away from compromising the database.

The moral of the story is that search engines are very good hacker tools. Never hardcode passwords in script files, and always provide Web page redirects for errors within your Web application. In addition, always pay extra attention to any Web application that receives input. Make sure these kinds of data are protected against SQL injection attacks.