Replacing a DataReader with a DataTable
Use the DataTable.Load method to fill your table with values from the SqlDataReader: using (SqlDataReader dr = command.ExecuteReader()) { var tb = new DataTable(); tb.Load(dr); return tb; }
datatable
DataTable, How to conditionally delete rows
You could query the dataset and then loop the selected rows to set them as delete. var rows = dt.Select(“col1 > 5”); foreach (var row in rows) { row.Delete(); } dt.AcceptChanges(); … and you could also create some extension methods to make it easier … myTable.Delete(“col1 > 5”); public static DataTable Delete(this DataTable table, string … Read more
Find row in datatable with specific id
Make a string criteria to search for, like this: string searchExpression = “ID = 5” Then use the .Select() method of the DataTable object, like this: DataRow[] foundRows = YourDataTable.Select(searchExpression); Now you can loop through the results, like this: int numberOfCalls; bool result; foreach(DataRow dr in foundRows) { // Get value of Calls here result … Read more
Best way to remove duplicate entries from a data table
Do dtEmp on your current working DataTable: DataTable distinctTable = dtEmp.DefaultView.ToTable( /*distinct*/ true); It’s nice.
How to convert DataTable to class Object?
Initialize DataTable: DataTable dt = new DataTable(); dt.Columns.Add(“id”, typeof(String)); dt.Columns.Add(“name”, typeof(String)); for (int i = 0; i < 5; i++) { string index = i.ToString(); dt.Rows.Add(new object[] { index, “name” + index }); } Query itself: IList<Class1> items = dt.AsEnumerable().Select(row => new Class1 { id = row.Field<string>(“id”), name = row.Field<string>(“name”) }).ToList();
reading Excel Open XML is ignoring blank cells
This makes sense since Excel will not store a value for a cell that is null. If you open your file using the Open XML SDK 2.0 Productivity Tool and traverse the XML down to the cell level you will see that only the cells that have data are going to be in that file. … Read more
get index of DataTable column with name
You can use DataColumn.Ordinal to get the index of the column in the DataTable. So if you need the next column as mentioned use Column.Ordinal + 1: row[row.Table.Columns[“ColumnName”].Ordinal + 1] = someOtherValue; Warning: This code returns the next column, so the one after ColumnName, as requested in the question.
Getting a count of rows in a datatable that meet certain criteria
One easy way to accomplish this is combining what was posted in the original post into a single statement: int numberOfRecords = dtFoo.Select(“IsActive=”Y””).Length; Another way to accomplish this is using Linq methods: int numberOfRecords = dtFoo.AsEnumerable().Where(x => x[“IsActive”].ToString() == “Y”).ToList().Count; Note this requires including System.Linq.
Get a DataTable Columns DataType
What you want to use is this property: dt.Columns[0].DataType The DataType property will set to one of the following: Boolean Byte Char DateTime Decimal Double Int16 Int32 Int64 SByte Single String TimeSpan UInt16 UInt32 UInt64 DataColumn.DataType Property MSDN Reference
Datatable to html Table
use this function: public static string ConvertDataTableToHTML(DataTable dt) { string html = “<table>”; //add header row html += “<tr>”; for(int i=0;i<dt.Columns.Count;i++) html+=”<td>”+dt.Columns[i].ColumnName+”</td>”; html += “</tr>”; //add rows for (int i = 0; i < dt.Rows.Count; i++) { html += “<tr>”; for (int j = 0; j< dt.Columns.Count; j++) html += “<td>” + dt.Rows[i][j].ToString() + “</td>”; … Read more