Monday, 17 December 2012

how to bind generic list to gridview | Bind Database values to Generic list using asp.net | Binding custom generic class list to Gridview

Description:

Now I will explain how to use Lists in our application to bind data to gridview and I will explain how to bind database values to generic list using asp.net.

First Create new website and Design your aspx page like this

<title>Bind Generic List to Gridview</title>

</head>

<body>

<div>

<asp:GridView runat="server" ID="gvDetails" AutoGenerateColumns="false">

<asp:BoundField HeaderText="UserName" DataField="UserName" />

<asp:BoundField HeaderText="FirstName" DataField="FirstName" />

<asp:BoundField HeaderText="LastName" DataField="LastName" />

<asp:BoundField HeaderText="Location" DataField="Location" />

</Columns>

</asp:GridView>

</div>

</form>

</body>

</html>

After that add one class file to your website for that Right click on your website and select Add New Item à one window will open in that select Class file and give name as UserDetails.cs because here I am using this name in my sample if you want to give another name change UserDetails reference name in your sample.

Now open your Class file UserDetails.cs and write the following code

After that write the following code in code behind

public class UserDetails

{

string username = string.Empty;

string firstname = string.Empty;

string lastname = string.Empty;

string location = string.Empty;

public string UserName

{

get { return username; }

set { username = value; }

}

public string FirstName

{

get { return firstname; }

set { firstname = value; }

}

public string LastName

{

get { return lastname; }

set { lastname = value; }

}

public string Location

{

get { return location; }

set { location = value; }

}

After completion writing code in your class file open your Default.aspx.cs page add the following namespaces

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

After that write the following code in code behind

List<UserDetails> objUserDetails = new List<UserDetails>();

protected void Page_Load(object sender, EventArgs e)

{

if(!IsPostBack)

{

BindUserDetails();

}

protected void BindUserDetails()

{

objUserDetails = GetUserDetails();

gvDetails.DataSource = objUserDetails;

gvDetails.DataBind();

}

protected List<UserDetails> GetUserDetails()

{

string strConnection = "Data Source=MukramJahDB;User Id="sqlSa"; Password="Sql";Initial Catalog=mukramJahDB";

DataTable dt = new DataTable();

SqlConnection con = new SqlConnection(strConnection);

con.Open();

SqlCommand cmd = new SqlCommand("select * from UserInformation", con);

SqlDataAdapter da = new SqlDataAdapter(cmd);

da.Fill(dt);

if(dt.Rows.Count>0)

{

for(int i=0;i<dt.Rows.Count;i++)

{

UserDetails userinfo = new UserDetails();

userinfo.UserName = dt.Rows[i]["UserName"].ToString();

userinfo.FirstName = dt.Rows[i]["FirstName"].ToString();

userinfo.LastName = dt.Rows[i]["LastName"].ToString();

userinfo.Location = dt.Rows[i]["Location"].ToString();

objUserDetails.Add(userinfo);

}

return objUserDetails;

}

Wednesday, 12 December 2012

SQL Injection

What is sql injection?

SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution.

Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. Even parameterized data can be manipulated by a skilled and determined attacker.

The primary form of SQL injection consists of direct insertion of code into user-input variables that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings that are destined for storage in a table or as metadata. When the stored strings are subsequently concatenated into a dynamic SQL command, the malicious code is executed.

The injection process works by prematurely terminating a text string and appending a new command. Because the inserted command may have additional strings appended to it before it is executed, the malefactor terminates the injected string with a comment mark "--". Subsequent text is ignored at execution time.

The following script shows a simple SQL injection. The script builds an SQL query by concatenating hard-coded strings together with a string entered by the user:

 
var Shipcity;
ShipCity = Request.form ("ShipCity");
var sql = "select * from OrdersTable where ShipCity = '" + ShipCity + "'";

The user is prompted to enter the name of a city. If she enters Redmond, the query assembled by the script looks similar to the following:

SELECT * FROM OrdersTable WHERE ShipCity = 'Redmond'

However, assume that the user enters the following:

Redmond'; drop table OrdersTable--

In this case, the following query is assembled by the script:

SELECT * FROM OrdersTable WHERE ShipCity = 'Redmond';drop table OrdersTable--'

The semicolon (;) denotes the end of one query and the start of another. The double hyphen (--) indicates that the rest of the current line is a comment and should be ignored. If the modified code is syntactically correct, it will be executed by the server. When SQL Server processes this statement, SQL Server will first select all records in OrdersTable where ShipCity is Redmond. Then, SQL Server will drop OrdersTable.

As long as injected SQL code is syntactically correct, tampering cannot be detected programmatically. Therefore, you must validate all user input and carefully review code that executes constructed SQL commands in the server that you are using. Coding best practices are described in the following sections in this topic.

Best practices for prevention of sql injection?

Use validation for input values: -

You can check for User input inside the textboxes and validate them according to the expected value so no other value will be inserted into the database. In below example database expects only Numeric value so we have use a RegularExpressionValidator which will allow only numeric value to be entered inside the textBox


         <asp:TextBox ID="txtid" runat="server"></asp:TextBox></span>
        <asp:RegularExpressionValidator ID="regExp" runat="server" ErrorMessage="*" ValidationExpression="^(-)?\d+(\.\d\d)?$"
            ControlToValidate="txtid"></asp:RegularExpressionValidator>



Make no assumptions about the size, type, or content of the data that is received by your application. For example, you should make the following evaluation:  

How will your application behave if an errant or malicious user enters a 10-megabyte MPEG file where your application expects a postal code? 
How will your application behave if a DROP TABLE statement is embedded in a text field? 
Test the size and data type of input and enforce appropriate limits. This can help prevent deliberate buffer overruns. 
Test the content of string variables and accept only expected values. Reject entries that contain binary data, escape sequences, and comment characters. This can help prevent script injection and can protect against some buffer overrun exploits.
When you are working with XML documents, validate all data against its schema as it is entered.
Never build Transact-SQL statements directly from user input. 
Use stored procedures to validate user input.

Use Type-Safe SQL Parameters

The Parameters collection in SQL Server provides type checking and length validation. If you use the Parameters collection, input is treated as a literal value instead of as executable code. An additional benefit of using the Parameters collection is that you can enforce type and length checks. Values outside the range will trigger an exception. The following code fragment shows using the Parameters collection:

 

SqlDataAdapter myCommand = new SqlDataAdapter("AuthorLogin", conn);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm = myCommand.SelectCommand.Parameters.Add("@au_id",
     SqlDbType.VarChar, 11);
parm.Value = Login.Text;

In this example, the @au_id parameter is treated as a literal value instead of as executable code. This value is checked for type and length. If the value of @au_id does not comply with the specified type and length constraints, an exception will be thrown.

Use of Stored Procedure: Another way of preventing SQL Injection is using Stored Procedure, where you pass the required parameters with values to the stored procedure which is defined for the stored procedure. As to stored procedure you pass value stored procedure takes it as Parameter so there is no risk of Database attack.


        SqlConnection conn = new SqlConnection(connectionString)

DataSet ds = new DataSet();

SqlDataAdapter da= new SqlDataAdapter("p_get_user_details", conn);   
        da.SelectCommand.CommandType = CommandType.StoredProcedure;
        da.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);
        da.SelectCommand.Parameters["@au_id"].Value = txtUsername.Text;
        da.Fill(ds);

LIKE Clauses

Note that if you are using a LIKE clause, wildcard characters still must be escaped:

 

s = s.Replace("[", "[[]");
s = s.Replace("%", "[%]");
s = s.Replace("_", "[_]");

Tuesday, 11 December 2012

SQL Server JOINs with examples

I will explain types of SQL JOINs in in this article. JOINs in SQL Server can be classified as follows:

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• CROSS JOIN

Each type is explained below with suitable examples:
Let’s consider two tables as Table1 & Table2 for our example.

– CREATE TEST TABLES
CREATE TABLE Table1(ID [int], Code [varchar](10));
GO
CREATE TABLE Table2(ID [int], Amount [int]);
GO

– INSERT DUMMY DATA
INSERT INTO Table1 VALUES
(1,’AAA’),(2,’BBB’),(3,’CCC’)
GO
INSERT INTO Table2 VALUES
(1,50),(2,30),(4,40)
GO

INNER JOIN:
Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Below is the query for inner join:

SELECT *
FROM Table1 A
INNER JOIN Table2 B
ON A.ID = B.ID
GO

LEFT OUTER JOIN
Left Outer joins return all rows from the left table referenced with a left outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Left Outer Join:

SELECT *
FROM Table1 A
LEFT OUTER JOIN Table2 B
ON A.ID = B.ID
GO

RIGHT OUTER JOIN
Right Outer joins return all rows from the right table referenced with a right outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Right Outer Join:

SELECT *
FROM Table1 A
RIGHT OUTER JOIN Table2 B
ON A.ID = B.ID
GO

FULL OUTER JOIN
Full Outer joins return all rows from both the tables. Unmatched records will be NULL. Below is the query for Full Outer Join:

SELECT * FROM Table1 A
FULL OUTER JOIN Table2 B
ON A.ID = B.ID
GO

CROSS JOIN
In cross joins, each row from first table joins with all the rows of another table. If there are m rows from Table1 and n rows from Table2 then result set of these tables will have m*n rows. Below is the query for

SELECT * FROM Table1 A
CROSS JOIN Table2 B
GO

Monday, 10 December 2012

Different Ways of using the C# Null Coalescing Operator

The C# Null-Coalescing operator or the ?? operator is a binary operator that returns the left-hand operand if it is not null; otherwise it returns the right operand. It can be used with both nullable and reference types.

Here’s a simple example of using the Null-Coalescing operator

int? i = null;
int j = i ?? 2;
// here the resultant value of j is 2

Here are some additional ways of using the Null-Coalescing operator in your code.

Method 1

string address = homeAddress1 ?? homeAddress2 ?? officeAddress
                 ??  "No Address";

returns the first non-null value in this long expression. This expression returns “No Address” if homeAddress1, homeAddress2 and officeAddress are Null.

Method 2

While converting a Nullable Type to Non-Nullable type, we do an explicit cast to avoid assigning null values to a non-nullable type, as shown below:

int? a = NullType(); // returns nullable int
int b = (int)a;

instead you can avoid the explicit cast using a null-coalescing operator and write:

int? a = NullType();
int b = a ?? 0;

Method 3

private IList<Person> person;
public IList<Person> PersonList
{
    get
    {
        return person ?? (person = new List<Person>());
    }
}

Typical usage of the null-coalescing operator in a lazy instantiated private variable.

Method 4

string conn = Connection["TestMachineStr1"]
            ?? AppConnection["ProdMachineStr2"]
            ?? DefaultConnection["DefaultConn"];

Accessing NameValueCollections and choosing the first non-null value.

Note: The Null-Coalescing operator is useful but may sometimes be an overkill, especially when developers use it to flaunt their coding skills. I sometimes choose ‘understandable code’ over ‘terse code’, focusing on the intent.

Feel free to use the comments section to demonstrate how you have used the null-coalescing operator in your projects.

Reference:http://www.dotnetcurry.com/ShowArticle.aspx?ID=745

How to use control state Control State in asp.net

What is Control State?
The control state is a way to save a control’s state information when the EnableViewState property is turned off. Unlike ViewState a developer can’t turn off control state. The control state can be implemented only in controls that you implement.

How to Use Control State?

Control state implementation is easy. First, override the OnInit method of the control and add the call for the Page.RegisterRequiresControlState method with the instance of the control to register. Then, override the LoadControlState and SaveControlState in order to save the required state information.
The next example shows a simple implementation for a control that saves a string as a state information:

 
public class ControlStateWebControl : Control

{

#region Members



private string _strStateToSave;



#endregion

#region Methods



protected override void OnInit(EventArgs e)

{

   Page.RegisterRequiresControlState(this);

   base.OnInit(e);

}



protected override object SaveControlState()

{

   return _strStateToSave;

}



protected override void LoadControlState(object state)

{

   if (state != null)

   {

   _strStateToSave = state.ToString();

   }

}



#endregion

}

You need to remember only one thing – the control state takes away the choice to disable ViewState. You should
only use it whenever you have to keep a state information that without it your control won’t work.

Summary

To sum up the post, I showed what is the control state and how to enable it in your controls. The control state takes away the decision of a developer whether to use ViewState or not. Whenever you want to use the control state you should think whether to implement it or give the decision of how to save the state information to the developers that use your control. I prefer the second choice. In the next post in this series I’ll start to explain the server side state
management.

Save multiple properties into Control State

To save multiple properties into Control State:
private int _prop1;
public int Prop1
{
get { return _prop1; }
set { _prop1 = value; }
}

private string _prop2;
public string Prop2
{
get { return _prop2; }
set { _prop2 = value; }
}

protected override object SaveControlState()
{
object[] state = new object[2]; // save the 2 properties
state[0] = _prop1;
state[1] = _prop2;

return state;
}

protected override void LoadControlState(object savedState)
{
object[] state = (object[])savedState;
_prop1 = (int)state[0];
_prop2 = (string)state[1];
}