C# & ADO.NET

C# language questions and data access with ADO.NET and SQL Server: commands, readers, DataSets, parameters, files and e-mail.

• Convert a SqlDataReader to a DataTable with DataTable.Load (C#)

  Use DataTable.Load to copy the rows of a SqlDataReader into a DataTable: basic usage, what happens to the reader, LoadOption, schema and constraint errors, several result sets, and when this beats SqlDataAdapter.Fill.

• DataSet vs DataTable in ADO.NET

  What a DataTable and a DataSet each hold, how to fill them with a SqlDataAdapter, relations and constraints between tables, memory and serialization costs, and when a typed list or a data reader is the better choice.

• DataSet, DataReader, DataAdapter and DataView: what each ADO.NET class does

  The four ADO.NET classes that confuse beginners, explained by role: the connected DataReader, the disconnected DataSet, the DataAdapter that moves data between them and the database, and the DataView that filters and sorts a DataTable, with code for each.

• ExecuteReader vs ExecuteNonQuery vs ExecuteScalar in ADO.NET

  The three SqlCommand execution methods compared: what each returns, when to use it, how row counts behave with SET NOCOUNT and triggers, reading identity values with ExecuteScalar or OUTPUT, null handling, and their async versions.

• Export a folder's file list to CSV or Excel in C#

  Collect file names, sizes and dates from a folder with DirectoryInfo and export them to a CSV file Excel opens correctly (encoding, separators, quoting, invariant dates) or to a real .xlsx, from a console app or an ASP.NET page.

• How to deserialize an XML document into C# objects

  Turn XML into C# objects with XmlSerializer: matching classes with XmlRoot, XmlElement, XmlAttribute and XmlArray, reading from a file, a string or an uploaded stream, namespaces, generating classes with xsd.exe, common InvalidOperationException causes and safe parsing of untrusted XML.

• Import CSV or Excel data into SQL Server from ASP.NET (SqlBulkCopy and BULK INSERT)

  Load an uploaded CSV file into SQL Server: parsing with TextFieldParser, validating rows into a DataTable, writing them with SqlBulkCopy and column mappings, a staging table pattern, the T-SQL BULK INSERT alternative and reading Excel files.

• List all files in a folder and its subfolders in C#

  Get every file under a directory recursively in C#: Directory.GetFiles versus EnumerateFiles, SearchOption.AllDirectories, EnumerationOptions to skip inaccessible folders, filtering by extension, and listing the result on an ASP.NET page.

• Pass a DataTable as a table-valued parameter to a SQL Server stored procedure

  Send many rows to SQL Server in one call: create a user-defined table type, a stored procedure with a READONLY table parameter, and pass a DataTable (or IEnumerable of SqlDataRecord) from C# with SqlDbType.Structured.

• Send a GridView (all rows or selected rows) by e-mail in ASP.NET (C#)

  E-mail the contents of an ASP.NET GridView: build an HTML table from the data with inline styles, include only the rows the user ticked, send it with SmtpClient and MailMessage, and avoid the RenderControl pitfalls.

• Send an HTML page or template as an e-mail in ASP.NET (C#)

  Send a newsletter-style HTML e-mail from ASP.NET: an HTML template file with placeholders, encoding values, a plain-text alternative, embedded images with LinkedResource, and why rendering a live .aspx page into an e-mail causes problems.

• Sending a test e-mail from ASP.NET through Gmail's SMTP server (C#)

  Send mail from an ASP.NET Web Forms page with SmtpClient and Gmail: smtp.gmail.com on port 587 with STARTTLS, app passwords now that Google no longer accepts plain passwords from apps, web.config mailSettings, TLS 1.2, attachments, common errors, and why to use a transactional service in production.

• Split a date string into day, month and year in C# and JavaScript

  Extract day, month and year from a date string reliably: DateTime.ParseExact and TryParseExact with an explicit format and culture in C#, why String.Split alone is fragile, DateOnly, and the equivalent in JavaScript.

• SqlCommand.ExecuteNonQuery example in ASP.NET (C#)

  A working ExecuteNonQuery example: inserting, updating and deleting SQL Server rows from a Web Forms page with parameters, reading the rows-affected count, running a stored procedure with an output parameter, and wrapping several commands in a transaction.

• SqlCommand.ExecuteReader example in ASP.NET (C#)

  Read SQL Server rows with SqlCommand.ExecuteReader in a Web Forms page: looping with Read, typed getters and NULL checks, building HTML safely, reading several result sets with NextResult, and CommandBehavior options.

• SqlCommand.ExecuteScalar example in ASP.NET (C#)

  ExecuteScalar in practice: counting rows, checking whether a user name is taken, reading a single setting, returning a new identity value, and handling null, DBNull and type conversions correctly.