Adding Rows to DataGrid

One day my colleague want me to add some rows to existing DataGrid, the rest of the record is populate with query from the database meanwhile the records that will be add should be filled manually and not from database, for few minutes I feel foolish enough to find out that we can't add rows using the DataGrid object directly like we add rows in DataTable (Creating DataTable Manually), but we should manipulate the DataGrid.DataSource that I fill in with DataView object. So we should manipulate the DataTable and not the DataGrid, the code is quite same like my previous post (Creating DataTable Manually) with some lines added.

pop-uptext

DataSet ds = new DataSet();
//Assuming that ds already contains record populated from database
DataRow rs1 = ds.Tables[0].NewRow();
DataRow rs2 = ds.Tables[0].NewRow();
rs1["COL1"] = "Testing row1col1"; rs1["COL2"] = "Testing row1col2";
rs2["COL1"] = "Testing row2col1"; rs2["COL2"] = "Testing row2col2";
ds.Tables[0].Rows.Add(r1);
ds.Tables[0].Rows.Add(r2);
DataView dv = ds.Tables[0].DefaultView;
DataGrid dg = new DataGrid();
dg.DataSource = dv;

DataSet ds = new DataSet();
//Assuming that ds already contains record populated from database
DataRow rs1 = ds.Tables[0].NewRow();
DataRow rs2 = ds.Tables[0].NewRow();

rs1["COL1"] = "Testing row1col1"; rs1["COL2"] = "Testing row1col2";
rs2["COL1"] = "Testing row2col1"; rs2["COL2"] = "Testing row2col2";

ds.Tables[0].Rows.Add(r1);
ds.Tables[0].Rows.Add(r2);

DataView dv = ds.Tables[0].DefaultView;
DataGrid dg = new DataGrid();
dg.DataSource = dv;

How to remove duplicate primary keys rows from DataTable

Let's say that I have a table in database, for some reason the one who created it didn't set any primary keys, but giving assumption that some columns will be use as primary keys. Because of that condition, somehow the columns that assume to be primary keys can have duplicate value and also can have null value. I want to remove records that have null and duplicate values in primary keys. There are two ways to do it, one is using query another one is loop DataRow and check it programmatically.

This is the first way

pop-uptext

This is the first way
SELECT t.*
FROM
( SELECT u.PK1 ,u.PK2 FROM MyTable u
  WHERE u.PK1 is not null and u.PK2 is not null
  GROUP BY u.PK1 ,u.PK2
  HAVING COUNT(*) = 1 ) s
LEFT JOIN MyTable t
ON t.PK1=s.PK1 and t.PK1=s.PK2
WHERE t.PK1 is not null and t.PK2 is not null

This is the first way
SELECT t.*
FROM
( SELECT u.PK1 ,u.PK2 FROM MyTable u
  WHERE u.PK1 is not null and u.PK2 is not null
  GROUP BY u.PK1 ,u.PK2
  HAVING COUNT(*) = 1 ) s
LEFT JOIN MyTable t
ON t.PK1=s.PK1 and t.PK1=s.PK2
WHERE t.PK1 is not null and t.PK2 is not null

You can use inner join too, but in this scenario it is faster to use left join because it doesn't have to check every records for each row. Thanks to Anita for helping me with this query, being inspired too by this article written by Pinal Dave. By the way this query is not the only way, u can use another trick or even use store procedure.

This is the second way, the important thing is YOU MUST SORT the content in dtSource and dtDup base on the primary keys respectively. I noticed that there are different behaviors of DataTable if u create it manually and if u use DataSet then populate the records from database. If u use DataTable that u create manually when u delete the row, then the row will be deleted permanently and the index will changed, but if u use DataSet then it will be marked as deleted it will be deleted if u use DataTable.AcceptChanges. If u use DataSet then look at my code with comment "//THIS LINE SHOULD BE REMOVED", statement "i--;" in that line should be removed, but if you use DataTable that u create manually u need that statement, it took a while for me to test and realize there are something odd with the result before, now it fix :) .

pop-uptext

class Program
{
   static void Main(string[] args)
   {
       //DataTable that contain all records
        DataTable dtSource = new DataTable();
      
       dtSource.Columns.Add("PK1");
       dtSource.Columns.Add("PK2");
       dtSource.Columns.Add("Column1");
       dtSource.Columns.Add("Column2");
       Random rnd = new Random();
       int j = 0;
       ArrayList arr = new ArrayList();
       for (int i = 0; i < 10 ; i ++ )
       {
         j = rnd.Next(1, 3);
         if (j == 2) arr.Add(i);
         for (int k = 0; k < j ; k ++ )
         {
             DataRow dr = dtSource.NewRow();
             dr["PK1"] = "PK1" + i;
             dr["PK2"] = "PK2" + i;
             dr["Column1"] = "C1" + i;
             dr["Column2"] = "C2" + i;
             dtSource.Rows.Add(dr);
         }
       }
       //DataTable that only contains rows with the duplicate primary keys
        DataTable dtDup = new DataTable();
       dtDup.Columns.Add("PK1");
       dtDup.Columns.Add("PK2");
       for (int i = 0; i < arr.Count ; i ++ )
       {
           DataRow dr = dtDup.NewRow();
           dr["PK1"] = "PK1" + arr[i].ToString();
           dr["PK2"] = "PK2" + arr[i].ToString();
           dtDup.Rows.Add(dr);
       }
       PrintRows(dtSource);
       Console.WriteLine();
       Program obj = new Program();
       string [] dupPrimary = new string[2]{"PK1", "PK2"};
       obj.RemoveDuplicatePrimaryKey( ref dtSource, dtDup, dupPrimary );
       PrintRows(dtSource);
       Console.ReadLine();
   }
   private static void PrintRows(DataTable dt)
   {
       for (int i = 0; i < dt.Rows.Count; i++)
           Console.WriteLine("PK1: {0}, PK2: {1}, Column1: {2}, Column2: {3}",
               dt.Rows[i]["PK1"], dt.Rows[i]["PK2"],
               dt.Rows[i]["Column1"], dt.Rows[i]["Column2"]);
   }
   public void RemoveDuplicatePrimaryKey
       (ref DataTable Source, DataTable Target, string[] PrimaryKeys)
   {
       DataColumn[] keyColumns = new DataColumn[PrimaryKeys.Length];
       for (int i = 0; i < PrimaryKeys.Length; i++)
       {
           keyColumns[i] = Source.Columns[ PrimaryKeys[i] ];
       }
       bool bStart = false, bEnd = false;
       int j = 0;
       bool bDuplicate;
       for (int i = 0; i < Source.Rows.Count; i++)
       {
           DataRow drSource = Source.Rows[i];
           bDuplicate = IsDuplicate(drSource, Target.Rows[j], keyColumns);
           if (!bDuplicate)
           {
               if (bEnd) break;
               else if (bStart)
               {
                   bStart = false;
                   j++;
                   i--;
               }
               else if (j == 0) continue;
           }
           else
           {
               Source.Rows[i].Delete();
               bStart = true;
               if (j == Target.Rows.Count - 1) bEnd = true;
               i--; //THIS LINE SHOULD BE REMOVED
            }
       }
       Source.AcceptChanges();
   }
   private bool IsDuplicate
       (DataRow drSource, DataRow drTarget, DataColumn[] dcPrimaryKeys)
   {
       bool retVal = true;
       foreach (DataColumn column in dcPrimaryKeys)
       {
           retVal = retVal &&
               (drSource[column.ColumnName].ToString() ==
               drTarget[column.ColumnName].ToString());
           if (!retVal) break;
       }
       return retVal;
   }
}

class Program
{
   static void Main(string[] args)
   {
       //DataTable that contain all records
        DataTable dtSource = new DataTable();
      
       dtSource.Columns.Add("PK1");
       dtSource.Columns.Add("PK2");
       dtSource.Columns.Add("Column1");
       dtSource.Columns.Add("Column2");

       Random rnd = new Random();
       int j = 0;
       ArrayList arr = new ArrayList();

       for (int i = 0; i < 10 ; i ++ )
       {
         j = rnd.Next(1, 3);
         if (j == 2) arr.Add(i);
         for (int k = 0; k < j ; k ++ )
         {
             DataRow dr = dtSource.NewRow();
             dr["PK1"] = "PK1" + i;
             dr["PK2"] = "PK2" + i;
             dr["Column1"] = "C1" + i;
             dr["Column2"] = "C2" + i;
             dtSource.Rows.Add(dr);
         }
       }

       //DataTable that only contains rows with the duplicate primary keys
        DataTable dtDup = new DataTable();
       dtDup.Columns.Add("PK1");
       dtDup.Columns.Add("PK2");

       for (int i = 0; i < arr.Count ; i ++ )
       {
           DataRow dr = dtDup.NewRow();
           dr["PK1"] = "PK1" + arr[i].ToString();
           dr["PK2"] = "PK2" + arr[i].ToString();
           dtDup.Rows.Add(dr);
       }

       PrintRows(dtSource);
       Console.WriteLine();

       Program obj = new Program();
       string [] dupPrimary = new string[2]{"PK1", "PK2"};
       obj.RemoveDuplicatePrimaryKey( ref dtSource, dtDup, dupPrimary );
       PrintRows(dtSource);
       Console.ReadLine();
   }

   private static void PrintRows(DataTable dt)
   {
       for (int i = 0; i < dt.Rows.Count; i++)
           Console.WriteLine("PK1: {0}, PK2: {1}, Column1: {2}, Column2: {3}",
               dt.Rows[i]["PK1"], dt.Rows[i]["PK2"],
               dt.Rows[i]["Column1"], dt.Rows[i]["Column2"]);
   }

   public void RemoveDuplicatePrimaryKey
       (ref DataTable Source, DataTable Target, string[] PrimaryKeys)
   {
       DataColumn[] keyColumns = new DataColumn[PrimaryKeys.Length];
       for (int i = 0; i < PrimaryKeys.Length; i++)
       {
           keyColumns[i] = Source.Columns[ PrimaryKeys[i] ];
       }
       bool bStart = false, bEnd = false;
       int j = 0;
       bool bDuplicate;
       for (int i = 0; i < Source.Rows.Count; i++)
       {
           DataRow drSource = Source.Rows[i];
           bDuplicate = IsDuplicate(drSource, Target.Rows[j], keyColumns);

           if (!bDuplicate)
           {
               if (bEnd) break;
               else if (bStart)
               {
                   bStart = false;
                   j++;
                   i--;
               }
               else if (j == 0) continue;

           }
           else
           {
               Source.Rows[i].Delete();
               bStart = true;
               if (j == Target.Rows.Count - 1) bEnd = true;
               i--; //THIS LINE SHOULD BE REMOVED
            }
       }
       Source.AcceptChanges();
   }

   private bool IsDuplicate
       (DataRow drSource, DataRow drTarget, DataColumn[] dcPrimaryKeys)
   {
       bool retVal = true;
       foreach (DataColumn column in dcPrimaryKeys)
       {
           retVal = retVal &&
               (drSource[column.ColumnName].ToString() ==
               drTarget[column.ColumnName].ToString());
           if (!retVal) break;
       }
       return retVal;
   }
}

Collapse Code

Expand Code

In the real case, I use query to populate data into dtSource and dtDup, here they are:

pop-uptext

SELECT PK1, PK2, Column1, Column2
FROM MyTable
WHERE PK1 is not null and PK2 is not null
ORDER BY PK1, PK2
SELECT PK1, PK2
FROM MyTable
WHERE PK1 is not null and PK2 is not null
GROUP BY PK1, PK2
HAVING COUNT(*) = 1
ORDER BY PK1, PK2

SELECT PK1, PK2, Column1, Column2
FROM MyTable
WHERE PK1 is not null and PK2 is not null
ORDER BY PK1, PK2

SELECT PK1, PK2
FROM MyTable
WHERE PK1 is not null and PK2 is not null
GROUP BY PK1, PK2
HAVING COUNT(*) = 1
ORDER BY PK1, PK2

Braviax Virus

At the first time, I didn't install any antivirus or spyware, one day when I was surfing the internet, my laptop suddenly restart. I think maybe the operating system was crash or something, after the computer reboot, I open windows task manager and found something strange, there is process name braviax and almost all the process have blank user name. I check msconfig and I found another braviax and start up. geezz after that I realized I've got infected, paniiiiccccccccc.

Calm down mann, soon I installed PC tools spyware doctor, it can detect braviax but can't clean it perfectly, because it keep show up. sighh this virus is something, after that I try to installed Symantec Antivirus, guess what, I can't installed it, it say that I don't have sufficient priviledge, but heyy I'm the only admin in this laptop. Later I found out it is because the virus, I'm getting moreeeee paniccccccc

ok ok, calm downn, google it first, found one article that suggest me to kill the process and delete the braviax.exe, hey dude I've done it few timess it's not that simple. Finally i've found one article How To Remove Braviax. I download the SDFix and combofix and follow the step, and voila it workss thx Godd, whoever wrote it, thx a lot dude. Here is the step taken from the the url above :

Download SDFix and save the file to your desktop. Double click SDFix.exe and it will extract the files to %systemdrive% (Drive that contains the Windows Directory, typically C:\SDFix)
Download combofix.

Reboot your PC in Safe mode.

1. Restart your computer
2. After hearing your computer beep once during startup, but before the Windows icon appears, press F8.
3. Instead of Windows loading as normal, a menu should appear
4. Select the first option, to run Windows in Safe Mode.

Open the SDFix folder and double-click RunThis.bat.

• Type Y to begin the cleanup process.
• It will remove any Trojan Services and Registry Entries that it finds then prompt you to press any key to Reboot.
• Press any Key and it will restart the PC.
• When the PC restarts the Fixtool will run again and complete the removal process then display Finished, press any key to end the script and load your desktop icons.
• Close any open browsers.
• Double click on combofix.exe and follow the prompts.

Note 1: Can`t run anti spyware programs ? rename them and try again.

Note 2: Some variants of braviax very difficult for removing from PC.
If in a combofix log you have found Win32.Agent.zb header with list of infected files, then you should remove and install these apps again.

If you are still having problems with spyware after completing these instructions, it`s possible, then please follow the steps: How to use Spyware Removal Forum - MUST READ

Searching in DataView [C# VS2005]

We can export records in dataset to xml file and vice versa, it's a straight forward one, we took example from dtCustomer in previous posting (Creating DataTable Manually) .
To write records in DataTable just use WriteXml function

pop-uptext

String path = "C:\\TEMP\Customer.xml"
dtCustomer.WriteXml( path );

String path = "C:\\TEMP\Customer.xml"
dtCustomer.WriteXml( path );

To read xml file and store the records in DataTable, use ReadXml

pop-uptext

dtCustomer.ReadXml( path );

dtCustomer.ReadXml( path );

I can select which columns that will became primary key in DataTable

pop-uptext

DataColumn[] dcKeys = new DataColumn[1];
dcKeys[0] = dtCustomer.Columns["Name"] ;
dtCustomer.PrimaryKey = dcKeys;

DataColumn[] dcKeys = new DataColumn[1];
dcKeys[0] = dtCustomer.Columns["Name"] ;
dtCustomer.PrimaryKey = dcKeys;

DataTable.PrimaryKey accept array of DataColumn, that's why I must make dcKeys an array, eventhough I only use 1 column only. There are several ways to search certain record in DataTable, one of it is using DataView

pop-uptext

DataView dv = dtCustomer.DefaultView;

DataView dv = dtCustomer.DefaultView;

Please remember that if you use primary key in DataTable and want to create view from it, you must define sort column for DataView base on the primary key, I am not certain should we define all primary key column or we can just use some of it, or we can mix it with another column, try to figure it out later.

pop-uptext

dv.Sort = "Name, Address";

dv.Sort = "Name, Address";

We can use DataView.FindRows, DataView.Find, or DataView.RowFilter to do searching. The differences between those 3 are, FindRows will return array of DataRowView meanwhile Find will return int that contain the row number where the record is. RowFilter will reindex the DataView so if you using it to often it will cost more resource. FindRows and Find need primary key to define, meanwhile RowFilter doesn't need it. RowFilter is used like where statement in SQL Query

pop-uptext

Object[] key = new Object[2]{ "silenr0c", "Bugis street" }
int row = dv.Find( key );
DataRowView drv = dv.FindRows( key );
dv.RowFilter = "Name='Minamoto' or Name='silenr0c' ";

Object[] key = new Object[2]{ "silenr0c", "Bugis street" }
int row = dv.Find( key );
DataRowView drv = dv.FindRows( key );

dv.RowFilter = "Name='Minamoto' or Name='silenr0c' ";

Creating DataTable manually [C# VS2005]

We can create our own DataTable, named the column and fill the rows manually without accessing the database

DataTable dtCustomer = new DataTable();
dtCustomer.Columns.Add("Name");
dtCustomer.Columns.Add("Address");

DataRow dr = dtCustomer.NewRow();
dr["Name"] = "silenr0c";
dr["Address"] = "Bugis Street";
dtCustomer.Rows.Add(dr);

dr = dtCustomer.NewRow();
dr["Name"]= "Matsumoto";
dr["Address"] = "Tokyo";
dtCustomer.Rows.Add(dr);

DataView dv = new DataView( dtCustomer);

file operation [C# VS2005]

Some notes about file operation that I have used before, including write file, listing file in directory, delete file. To use file/directory existing function don't forget to include System.IO

try
{
 sPath = "C:\\TEMP";
 if( Directory.Exists(sPath) )
 {
   string[] sFiles = Directory.GetFiles(sPath);
   for (int i = 0; i < sFiles.Length; i++)
   {
     File.Delete(sFiles[i]);
   }
 }
 else Console.WriteLine( sPath + " directory is not exist!");
}
catch (Exception ex)
{
 Console.WriteLine( ex.Message );
}

Let say that I want to delete all files in a specified folder but excluding its sub folder, first I check whether the path is valid or not => Directory.Exists, if valid then next step is I populate all files in the directory => Directory.GetFiles, the last one is we iterate and delete all existing files in directory => File.Delete

try
{
 string sErrLogPath = "C:\\TEMP";
 string sErrMsg = "Error to be written...";
 string sYear = DateTime.Now.Year.ToString();
 string sMonth = DateTime.Now.Month.ToString("00");
 string sDay = DateTime.Now.Day.ToString("00");
 string sErrFilePath = sErrLogPath + "\\" + sYear + "_" + sMonth + "_" + sDay;
 string sFileName;
 if( !Directory.Exists( sErrFilePath ) )
 {
    Directory.CreateDirectory(sErrFilePath);
 }
 sFileName = "ErrLog" + DateTime.Now.Hour.ToString("00") +
    DateTime.Now.Minute.ToString("00") + DateTime.Now.Second.ToString("00");
 StreamWriter sw = new StreamWriter(sErrFilePath + "\\" + sFileName + ".txt", false);
 sw.Write(sErrMsg);
 sw.Close();
}
catch (Exception ex)
{
 Console.WriteLine("Failed to write log file " + ex.Message);
}

Sometimes I want to make a note in a file for errors in my programs, so I just write the error message in file I created, rather than have to debug it first, quite helpful for me :P. In the code above I'm trying to make sub folder in C:\TEMP that contains my error message, the sub folder name format is YYYY_MM_DD. After that I create file for errors naming with ErrLog
hh_mm_ss

postback [C# ASP.NET VS2005]

I have often heard this term, so I thought it might be useful if I add some notes to remind me. Postback is a condition when a web page send data to itself as a target from the form action. The Page know whether it is postback or not from its property : bool Page.IsPostBack. If it return true then it is postback.

Few notes about postback related to DropDownList component. I add the following code in my website to fill the drop down list with number from 15-30. The result for the following code is a DropDownList filled with empty string as it selected value and number from 15-30.

protected void Page_Load(object sender, EventArgs e)
{
#region Fill Age Dropdownlist
ddlAge.Items.Clear();
ddlAge.Items.Add("");
for (int i = 15; i <= 30; i++)
{
    ddlAge.Items.Add(i.ToString());
}
#endregion
}

Assuming that I put this DropDownList named ddlAge in a form and submit it to itself to make a postback condition. The result is whatever value I choose in the ddlAge and then submit it, it always show empty string value. Why is that? Because the code to fill ddlAge is always run, regardless it is postback or not, so it will always fill again after the form submit to itself and the default value is always empty string. How do we solve it? We must check whether it is postback or not, if it is, then no need to run code for filling the ddlAge again. See the following code.

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
    #region Fill Age Dropdownlist
    ddlAge.Items.Clear();
    ddlAge.Items.Add("");
    for (int i = 15; i <= 30; i++)
    {
        ddlAge.Items.Add(i.ToString());
    }
    #endregion
}
}

Kindly refer to other references :

• Which Control Posted Back?
• Maintain Scroll Position on Postback in ASP.NET 2.0
• Maintain focus between postbacks in ASP.NET 2.0, also in ASP.NET AJAX
• Implementing Client Callbacks Without Postbacks in ASP.NET Web Pages
  

partial class [C# ASP.NET VS2005]

Partial class allow us to separate definition of one class in separate file, but it behave like usual class, u can call a function that define in other partial class.
So what the use of it?

• when the size of the file is getting bigger, it is harder to maintain, so one of the way is to split classes base on its functionality.
• it will allow programmers to add some definition of the class simultaneously
• when working with automatically generated source, code can be added to the class without having to recreate the source file. Visual Studio uses this approach when creating Windows Forms, Web Service wrapper code, and so on. You can create code that uses these classes without having to edit the file created by Visual Studio.

class definition without partial class, for example we save the code in Company.cs

using System;
using System.Collections.Generic;
using System.Text;
public class Company
{
static void Main(string[] args)
{
    ITDepartment a = new ITDepartment();
    HRDepartment b = new HRDepartment();
    Console.ReadLine();
}

private class ITDepartment
{
    public ITDepartment()
    {
        Console.WriteLine(”itdepartment”);
    }
}

private class HRDepartment
{
    public HRDepartment()
    {
        Console.WriteLine(”hrdepartment”);
    }
}
}

we can break down the code into several files
Company.cs

using System;
using System.Collections.Generic;
using System.Text;

public partial class Company
{
 static void Main(string[] args)
 {
     ITDepartment a = new ITDepartment();
     HRDepartment b = new HRDepartment();
     Console.ReadLine();
 }
}

Company.HRDepartment.cs

using System;
using System.Collections.Generic;
using System.Text;

public partial class Company
{
 private class HRDepartment
 {
     public HRDepartment()
     {
         Console.WriteLine("hrdepartment");
     }
 }
}

Company.ITDepartment.cs

using System;
using System.Collections.Generic;
using System.Text;

public partial class Company
{
 private class ITDepartment
 {
     public ITDepartment()
     {
         Console.WriteLine("itdepartment");
     }
 }
}

Kindly refer to this tutorial for other reference

as keyword for typecast [C# ASP.NET VS2005]

Usually in C or C++, we use this kind of typecast

float b = 10.9f;int a;
a = (int)b;

But the type that you are making type cast must be reference type such as object, primitive data type is not by reference but by value. So if you force using as like this example below it will give an error.

a = b as int;
//Error:The as operator must be used with a reference type ('int' is a value type)

Kindly refer to this tutorial for other reference.

#region [C# ASP.NET VS2005]

#region is used to block an area and create a collapsible structure on the editor, you can also put comments on it. It will make your code more neater.

#region put your comment over here...
if (true) Console.Write("Hello nel..");
#endregion