Example of SQL Injection.

A SQL injection attack is exactly what the name suggests – it is where a hacker tries to “inject”
his harmful/malicious SQL code into someone else’s database, and force that database to run
his SQL. This could potentially ruin their database tables, and even extract valuable or
private information from their database tables.

http://www.programmerinterview.com/index.php/database-sql/sql-injection-example/

Removing Duplicate Records From DataTable.

public DataTable RemoveDuplicateRows(DataTable table, string DistinctColumn)
{
    try
    {
        ArrayList UniqueRecords = new ArrayList();
        ArrayList DuplicateRecords = new ArrayList();

        // Check if records is already added to UniqueRecords otherwise,
        // Add the records to DuplicateRecords
        foreach(DataRow dRow in table.Rows)
        {
            if (UniqueRecords.Contains(dRow[DistinctColumn]))
                DuplicateRecords.Add(dRow);
            else
                UniqueRecords.Add(dRow[DistinctColumn]);
        }

        // Remove dupliate rows from DataTable added to DuplicateRecords
        foreach (DataRow dRow in DuplicateRecords)
        {
            table.Rows.Remove(dRow);
        }

        // Return the clean DataTable which contains unique records.
        return table;
    }
    catch (Exception ex)
    {
        return null;
    }
}

===========================================================================================================
DataTable DuplicateRecords = objDatabase.getTable("SQL Query");
DataTable UniqueRecords = RemoveDuplicateRows(DuplicateRecords,"Column Name to check for duplicate records");
===========================================================================================================
http://www.codeproject.com/Articles/540859/C-sharp-Removing-Duplicate-Records-From-DataTable


By Sriramjithendra Posted in C#.NET

Check for DBNull in .NET.

The shortest   is:

int stockvalue = (reader["StockValue"] as int?) ?? 0;

Explanation:

  • If reader[“StockValue”] is of type int, the value will be returned, and the “??” operator will return the result
  • If reader[“StockValue”] is NOT of type int (e.g. DBNull), null will be returned, and the “??” operator will return the value 0 (zero).

===============================================================================

 

1) Using System.Convert:

There is a direct method available to Check for DBNull. Here is what you have to do:

// Assuming that a DataReader is returned when query executed
SqlDataReader rdr= SqlHelper.RetreiveDataReader(“select [ID], [Name], [Addr], [Addr2] from customer”); 
If(rdr != null && rdr.HasRows)
{
          rdr.Read();
          if(!System.Convert.IsDBNull(rdr[“Addr2”]))  // can also be check for other cases
              customer[i].SecondaryAddress = rdr[“Addr2”];
}

For VB.Net sample you can simply convert this code through this converter

2) Using EQUALS Method:

Here is how you can use Equals() method to check for NULL values:

// Assuming that a DataReader is returned when query executed
SqlDataReader rdr= SqlHelper.RetreiveDataReader(“select [ID], [Name], [Addr], [Addr2] from customer”);                
If(rdr != null && rdr.HasRows)
{
     rdr.Read();
     if(!rdr[“Addr2”].Equals(DBNull.Value))   // can also be check for other cases
         customer[i].SecondaryAddress = rdr[“Addr2”];
}

For VB.Net sample you can simply convert this code through this converter

3) Using Extension Method:

The above two procedures are very common and in-use. I would also suggest to have look at the methodology I am going to discuss. The above two ways are fine if we are going to read few columns. But what if you have a table with 30+ columns and every column need to validate before use ?

I would suggest to extend this functionality and shrink your code.

Using extension method you can extend object datatype to have IsDBNull method. Here is a quick guide for how to use it:

    public static class MyExtensionForDBNull
    {
        public static bool IsDBNull(this object obj)
        {
            return (obj.Equals(DBNull.Value));
        }

        public static bool IsDBNullOrEmpty(this object obj)
        {
            return (obj.Equals(DBNull.Value) ||
                   (string.IsNullOrEmpty(obj.ToString())));
        }
   }

Add this to above code to your class where SqlDataReader is going to consume. Then use it like this:

// Assuming that a DataReader is returned when query executed
SqlDataReader rdr= SqlHelper.RetreiveDataReader(“select [ID], [Name], [Addr], [Addr2] from customer”);  

If(rdr != null && rdr.HasRows)
{
   rdr.Read();

   if(!rdr[“Addr”].IsDBNull())       // Here check for DBNull Only
      customer[i].SecondaryAddress = rdr[“Addr”];

   if(!rdr[“Addr2”].IsDBNullOrEmpty())      // Here you can also check for DBNull or Empty string
       customer[i].SecondaryAddress = rdr[“Addr2”];
}

In case of VB.NET, it is bit different to extension methods in a same way as you do in C#. If you will try to extend [Object] class as you can see in “MyExtensionForDBNull” you will not find any extended method in object datatype instead it will be visible in all other types like Integer, String etc.

So in order to use extension method in VB.NET the above implementation will look like:

Either create a module or in existing module paste this code:

    <System.Runtime.CompilerServices.Extension()> _
    Public Function IsDBNullOrEmpty(ByVal obj As [Object]) As Boolean
        Return (obj.Equals(DBNull.Value) Or (Not obj.Equals(DBNull.Value) AndAlso String.IsNullOrEmpty(obj)))
    End Function

and then use it like:

        
        Dim rdr As SqlDataReader = SQLHelper.RetrieveDataReader()  ' Expected that a DataReader will be returned

        If (rdr IsNot Nothing) Then
            If (rdr.HasRows) Then
                rdr.Read()
                If (rdr.GetString(rdr.GetOrdinal("Addr2")).IsDBNullOrEmpty()) Then
                    Console.WriteLine("Empty Or Null")
                Else
                    Console.WriteLine("Has some value")
                End If
            End If
        End If

        If (rdr IsNot Nothing) Then
            rdr.Close() 
            rdr = Nothing
        End If

I hope it is more easier and convenient to go with extension methods. You can also extend “MyExtensionForDBNull” class for some other functionalities like IsFieldPopulated (you can check for whether field contains data, can be use when you are expecting this lookup as a trigger for some operation) Or IsNotNullAndInteger (here you can check if the value is not null and contains a integer value (Use int.TryParse()) , can be use when sql query has COUNT(*) function used) etc.

Conclusion:

Using the first two method you do not have the functionalities as you can implement via extension methods. So I believe, this can provide more ease in writing code than the first two methods.

By Sriramjithendra Posted in C#.NET

Explicit Interface Implementation.

Explicit interface implementation is a handy technique for changing the way an interface implementation appears in the implementing type. You can use it to hide the interface method in the implementing type, resolve conflicting overloads with the implementing type, give different implementations between the interface(s) and/or implementing type, etc.
http://blackrabbitcoder.net/archive/2013/02/07/c.net-little-wonders-explicit-interface-implementation.aspx

By Sriramjithendra Posted in C#.NET