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);