Posts Tagged DataSet
Its been a while my apologies. I have been extremely busy.
This topic came up because in existing applications data binding at design time was heavily used. I personally avoid such data binding as much as possible because well in all honesty it causes me a lot of problems later on. I realize many of you live and die with it. So don’t take it personally.
So I am converting one of these apps to XAML and WPF. They insisted I maintain a ComboBox databind. As many of you know data binding has changed a bit in WPF from your standard application. I created a property to bind the ComboBox to. I set the DataContext of the page to itself, which I like to do. It lets me expose various properties and quickly bind to them. Have a great weekend!
public partial class Window1 : Window
DataContext = this;
private DataTable _dataTable = null;
private void LoadData()
SqlConnection cn = null;
SqlCommand cmd = null;
SqlDataAdapter adapter = null;
DataSet dataSet = null;
cn = new SqlConnection(“Data Source=MyMachine;Initial Catalog=MyDb;Integrated Security=True”);
cmd = new SqlCommand(“select top 1 * from MyTable”, cn);
adapter = new SqlDataAdapter(cmd);
dataSet = new DataSet();
_dataTable = dataSet.Tables;
if (cmd != null)
if (adapter != null)
if (dataSet != null)
if (cn != null)
public IEnumerable MyDataColumns
This is actually a redo of code written in vb.net a while back that dumps data from a datatable in a dataset into an excel spreadsheet in an effective and fast manner. I am always getting asked how to do this in C# so I decided to put that up today. Yes, I do C# too! Just didn’t want to admit it….
It’s pretty self explanatory. Don’t forget to clean up your excel instance when done. If you have any questions please feel free to send me an email.
Import an excel reference and at the top of your code you need your import statements. Then simply pass your dataset and datatable (granted you could just pass the datatable – I had my reasons for passing the dataset too at the time) to the method. The array is declared as an object because sometimes the compiler has to deal with unforeseen data issues that might arise and it has been effective for me to do let it handle those situations as they arise. I hope you find this useful.
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
public void CreateSpreadSheetFromDataSet(DataSet ds, DataTable dt)
Excel.Application Excel = new Excel.Application();
Excel.Visible = true;
Excel.Worksheet WSheet = new Excel.Worksheet();
WSheet = Excel.ActiveWorkbook.ActiveSheet;
int rows = ds.Tables[dt.TableName].Rows.Count;
int columns = ds.Tables[dt.TableName].Columns.Count;
int r = 0; int c = 0;
object[,] DataArray = new object[rows + 1, columns + 1];
for (c = 0; c <= columns – 1; c++)
DataArray[r, c] = ds.Tables[dt.TableName].Columns[c].ColumnName;
for (r = 0; r <= rows – 1; r++)
DataArray[r, c] = ds.Tables[dt.TableName].Rows[r][c];
} //end row loop
} //end column loop
//actually write array to Excel Spreadsheet
WSheet.Range[“A2”].Resize[rows, columns].Value = DataArray;
//write header row to spreadsheet
int ExcelColumnCounter = 1; //excel spreadsheets start at 1 when counting columns not zero!
for (DataTableColumnCounter = 0; DataTableColumnCounter <= ds.Tables[dt.TableName].Columns.Count – 1; DataTableColumnCounter++)
WSheet.Cells[1, ExcelColumnCounter].Value = ds.Tables[dt.TableName].Columns[DataTableColumnCounter].ColumnName;
ExcelColumnCounter = ExcelColumnCounter + 1; //moving to next column