/// 1
DataTable myTable;
// Create an array for the values.
object[] newRow = new object[3];
// Set the values of the array.
newRow[0] = "Hello";
newRow[1] = "World";
newRow[2] = "two";
for (int i = 0;i<3;i++)
{
myTable.Columns.Add(new DataColumn(i.ToString())); // title of the columns
}
DataRow myRow;
myTable.BeginLoadData();
// Add the new row to the rows collection.
myRow = myTable.LoadDataRow(newRow, true);
myTable.EndLoadData();
/// 2
DataTable dt = new DataTable();
dt.Columns.Add("ID", Type.GetType("System.Int32"));
dt.Columns.Add("Item", Type.GetType("System.String"));
dt.Columns.Add("RandomNumber", Type.GetType("System.Int32"));
DataRow dr;
for (int x = 1; x <= 10; x++)
{
dr = dt.NewRow();
dr["ID"] = x;
dr["Item"] = "Item " + x;
dr["RandomNumber"] = random.Next(1000);
dt.Rows.Add(dr);
}
3
DataTable tblProducts = new DataTable();
// fill the table here
//
// create new table
DataTable tblProductsCopy = new DataTable();
// Use Clone method to copy the table structure (Schema).
tblProductsCopy = tblProducts.Clone();
// Use the ImportRow method to copy from Products table to its clone.
for (i=0; i<=4;++i)
{
tblProductsCopy.ImportRow(tblProducts.Rows[i]);
}
/ 4
// create new datatable from exist one, remove some columns and reset the column order
// Parameters : source table, columns we need(include the column order)
private DataTable CopyColumns(DataTable source, string[] columns)
{
DataTable dest = source.Clone();
foreach (DataRow sourcerow in source.Rows)
{
DataRow destRow = dest.NewRow();
foreach (string colname in columns)
{
destRow[colname] = sourcerow[colname];
}
dest.Rows.Add(destRow);
}
// reset the column order
for (int i = 0; i < columns.Length; i++)
{
dest.Columns[columns[i]].SetOrdinal(i);
}
// remove the column we don't need. (columns which don't include in string[] columns)
while (dest.Columns.Count > columns.Length)
{
dest.Columns.RemoveAt(columns.Length);
}
return dest;
}
5
DataTable datatable = new DataTable();
DataRow[] advRow = datatable.Select("id=1");
for (int i = advRow.Length - 1; i >= 0; i--)
{
datatable.Rows.Remove(advRow[i]);
}
datatable.AcceptChanges();
DateTime dt_from = dateTimePicker_transactionFrom.Value;
DateTime dt_to = dateTimePicker_transactionTo.Value;
DataRow[] rowRemove1 = exportDataTable.Select("CreatedDate < '" + dt_from.Date + "'"); // don't forget '
DataRow[] rowRemove2 = exportDataTable.Select("CreatedDate >= '" + dt_to.AddDays(1).Date + "'" );
for (int i = rowRemove1.Length - 1; i >= 0; i--)
{
exportDataTable.Rows.Remove(rowRemove1[i]);
}
for (int i = rowRemove2.Length - 1; i >= 0; i--)
{
exportDataTable.Rows.Remove(rowRemove2[i]);
}
exportDataTable.AcceptChanges();
/ 6 DataTable useful function
public static class CollectionExtensions
{
// join two table, select certain columns, could reset the column order.
// key1,key2 are column names in two table and they are hold same data for join
// dt1 dt2 could be empty. if dt1(left table) is empty, the function return a empty table, no matter the dt2 empty or not.
public static DataTable LeftJoinDataTables(string joinTableName, DataTable dt1, DataTable dt2, string key1, string key2, params string[] fieldList)
{
DataTable dt = new DataTable(joinTableName);
foreach (var fieldName in fieldList)
{
if (dt1.Columns.Contains(fieldName))
{
var column = dt1.Columns[fieldName];
dt.Columns.Add(new DataColumn(column.ColumnName, column.DataType));
}
else if (dt2.Columns.Contains(fieldName))
{
var column = dt2.Columns[fieldName];
dt.Columns.Add(new DataColumn(column.ColumnName, column.DataType));
}
}
// join dt
var joinedDS = from r1 in dt1.AsEnumerable()
join r2 in dt2.AsEnumerable() on r1[key1].ToString() equals r2[key2].ToString()
select new
{
r1,
r2
};
// flat dt
foreach (var record in joinedDS)
{
var row = dt.NewRow();
foreach (var fieldName in fieldList)
{
if (record.r1.Table.Columns.Contains(fieldName))
{
row[fieldName] = record.r1[fieldName];
}
else if (record.r2.Table.Columns.Contains(fieldName))
{
row[fieldName] = record.r2[fieldName];
}
}
dt.Rows.Add(row);
}
return dt;
}
public static DataSet ToDataSet<T>(this IEnumerable<T> collection, string dataTableName)
{
if (collection == null)
{
throw new ArgumentNullException("collection");
}
if (string.IsNullOrEmpty(dataTableName))
{
throw new ArgumentNullException("dataTableName");
}
DataSet data = new DataSet("NewDataSet");
data.Tables.Add(FillDataTable(dataTableName, collection));
return data;
}
private static DataTable FillDataTable<T>(string tableName, IEnumerable<T> collection)
{
PropertyInfo[] properties = typeof(T).GetProperties();
DataTable dt = CreateDataTable<T>(tableName,
collection, properties);
IEnumerator<T> enumerator = collection.GetEnumerator();
while (enumerator.MoveNext())
{
dt.Rows.Add(FillDataRow<T>(dt.NewRow(),
enumerator.Current, properties));
}
return dt;
}
private static DataRow FillDataRow<T>(DataRow dataRow, T item, PropertyInfo[] properties)
{
foreach (PropertyInfo property in properties)
{
dataRow[property.Name.ToString()] = property.GetValue(item, null);
}
return dataRow;
}
private static DataTable CreateDataTable<T>(string tableName, IEnumerable<T> collection, PropertyInfo[] properties)
{
DataTable dt = new DataTable(tableName);
foreach (PropertyInfo property in properties)
{
dt.Columns.Add(property.Name.ToString());
}
return dt;
}
public static DataSet GetDataSetWithFields(DataSet ds, string[] fieldList)
{
DataSet returnDS = new DataSet();
DataTable dt = new DataTable();
if (ds.Tables.Count > 0)
{
var dt1 = ds.Tables[0];
// create dt schema
foreach (var fieldName in fieldList)
{
if (dt1.Columns.Contains(fieldName))
{
var column = dt1.Columns[fieldName];
dt.Columns.Add(new DataColumn(column.ColumnName, column.DataType));
}
}
// add dt
foreach (DataRow record in dt1.Rows)
{
var row = dt.NewRow();
foreach (var fieldName in fieldList)
{
row[fieldName] = record[fieldName];
}
dt.Rows.Add(row);
}
}
returnDS.Tables.Add(dt);
return returnDS;
}
}