My Development Notes

ADO.NET

.PrimaryKey = new DataColumn[] {eid};
.Columns.Add
.Rows.Add(Guid.NewGuid(), "value1", "value2");
.LoadDataRow(new object[] { primaryKey, "value1", "value2" }, LoadOption.OverwriteChanges);
.Copy()
.Clone() //Empty copy (just structure)
.ImportRow(tableThatWasCloned.Rows[0]) //Also brings across Current and Original version data.
.WriteXml(Server.MapPath("~/App_Data/data.xml")[, XmlWriteMode.WriteSchema|DiffGram]); //WriteSchema includes schema data in the xml file.
//If Schema is included, DataTable can be loaded entirely from the xml file
.ReadXml(Server.MapPath("~/App_Data/data.xml"));

.DataType, .MaxLength, .Unique, .AllowDBNull, .Caption, .DefaultValue
.ColumnMapping = MappingType.Attribute | Element | Hidden (Not serialized) | SimpleContent (Stored as text in row element tags)

if (row.HasVersion(DataRowVersion.Proposed)) {
    row[columnName, DataRowVersion.Proposed];
}

DataView view = new DataView(table); 
view.Sort = "LastName ASC, FirstName ASC, Salary DESC";
view.RowFilter = "LastName like 'A%' and Salary > 15";
view.RowStateFilter = RowStateFilter.Added|CurrentRows|Deleted|ModifiedCurrent|ModifiedOriginal|None|OriginalRows|Unchanged;

GridView1.DataSource = view; 
GridView1.DataBind(); 

dataSet.Tables.Add
DataRelation dr = dataSet.Relations.Add("Table1_Table2", table1.Columns["id], table2.Columns["id"]);
//DataRelation constructor sets ForeignKeyConstraint to Cascade by default. Could also be None, SetDefault, SetNull.

//This allows for 
dataSet.Tables["table1"].Rows[1].GetChildRows(dr); 
dataSet.Tables["table2"].Rows[1].GetParentRow(dr); 

dataSet.WriteXml(MapPath("data.xml");
dataSet.ReadXml(MapPath("data.xml"));

dataSet.WriteXmlSchema(MapPath("data.xsd"));
dataSet.ReadXmlSchema(MapPath("data.xsd"));

dataSet.RemotingFormat = SerializationFormat.Binary 
BinaryFormatter fmt = new BinaryFormatter(); 
fmt.Serialize(fs, dataSet); //Not initial 20Kb initial overhead for binary serialization so only beneficial for larger files.

//Ensure primary keys are set for the Merge
dataSet.Merge(dataSetToMergeFrom, false (don't overwrite changes in dataSet), MissingSchemaAction.Add|AddWithKey|Error|Ignore); 

LINQ to DataSet

DataTable employees = DataProvider.GetEmployeeData(); 
EnumerableRowCollection<DataRow>  query = 
    from employee in employees.AsEnumerable() 
    where employee.Field<Decimal>("Salary") > 20
    orderby employee.Field<Decimal>("Salary") 
    select employee;

foeach(DataRow e in query) { //or query.ToList() or query.ToArray() -- this is a delayed query.
    e.Field<String>("LastName");
}

var queryAvg = from employee in employees.AsEnumerable() 
    group employee by "Department" into d
    select new
    {
        AvgSalary = d.Average(employee => employee.Field<Decimal>("Salary"));
    };
foreach (var emp in queryAvg) {
    emp.AvgSalary.ToString();
}

Decimal avgSalary = employees.AsEnumerable().Average(employee => employee.Field<Decimal>("Salary"));

Connected Classes

DbConnection connection = new SqlConnection(); 
//or
DbProviderFactory factory = SqlClientFactory.Instance; 
DbConnection connection = factory.CreateConnection(); 

connection.ConnectionString = "Server=.;Database=pubs;Trusted_Connection=true"; 
//or
connection.ConnectionString = ConfigurationManager.ConnectionStrings("MyConn");

connection.InfoMessage += new SqlInfoMessageEventHandler(connection_InfoMessage);

DbCommand cmd = connection.CreateCommand(); 
cmd.CommandType = CommandType.StoredProcedure; 
cmd.CommandText = "storeProcName"; 

DbParameter parm = cmd.CreateParameter(); 
parm.ParameterName = "@Id"; 
parm.Value = "33"; 
cmd.Parameters.Add(parm); 

//Or 
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT Count(*) FROM Customer";

using(connection) {
    connection.Open();

    using (SqlTransaction tran = connection.BeginTransaction()) {
        try {
            using(cmd) {
                int numberOfRowsAffected = cmd.ExecuteNonQuery(); 
                int firstColumnFirstRow = (int)cmd.ExecuteScalar();

                DbDataReader rdr = cmd.ExecuteReader(); 
                while (rdr.Read()) {
                    //note that you can't run any other queries on the same connection while this client side cursor is running, 
                    //unless you set MultipleActiveResultSets=True in the connection string.
                    rdr["column_name"]; 
                }   

                DataTable table = new DataTable(); 
                table.Load(rdr, LoadOption.Upsert);
            }   

            tran.Commit();
        }
        catch (SqlException ex) {
            ex.Message;
            tran.Rollback(); //ACID = Atomic, Consistent, Isolated, Durable
        }
    }
}



void connection_InfoMessage(object sender, SqlInfoMessageEventArgs e) {
    e.Message //these show sql print statements
    foreach(SqlError err in e.Errors)  //these show Raiserror statements
}

SqlBulkCopy

DbDataReader rdr = cmd.ExecuteReader(); 
SqlBulkCopy bc = new SqlBulkCopy(bulkCopyConnection);
bc.DestinationTableName = "Customers";
bc.WriteToServer(rdr); //must map to same columns in destination table. Also accepts DataRow array & DataTable

DataAdapter

SqlDataAdapter da = new SqlDataAdapter(); 
da.SelectCommand = cmd; 
da.UpdateCommand | InsertCommand | DeleteCommand //or use SqlCommandBuilder to generate these automatically
da.Fill(dataSet, "tableName"); 
da.UpdateBatchSize = 3; //updates will be sent in batches of 3 (default to 1, 0 = max)

//modify da ..

da.Update(dataSet "tableName"); 

Asynchronous Queries

//These require Asynchronous Process=true in the connection string
IAsyncResult ar1 = cmd1.BeginExecuteReader(); 
IAsyncResult ar2 = cmd2.BeginExecuteReader(); 

ar1.AsyncWaitHandle.WaitOne(); 
cmd1.EndExecuteReader(ar1); 

ar2.AsyncWaitHandle.WaitOne(); 
cmd2.EndExecuteReader(ar2); 

Blob Buffers

byte[] buffer = new byte[bufferSize];

using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write)) {

    do {
        byteCountRead = (int)rdr.GetBytes(blobColumn, currentIndex, buffer, 0, bufferSize);
        fs.Write(buffer, 0, byteCountRead); 
        currentIndex += byteCountRead;

    }
    while (byteCountRead != 0) 

    }

LINQ to SQL

The LINQ to SQL classes created using the LINQ to SQL designer. Or the .dbml (designer) and class files generated automatically off a database using the command prompt SqlMetal.exe tool.

The classes are decorated with the following attributes:

[Table(Name="Authors")]
public class Author

private int _authorID;
[Column(IsPrimaryKey=true, Storage = "_authorID", Name=="AuthorID")]
public int AuthorID { get; set; }

To connect these objects to the database:

public class MyDb : DataContext
{
    public Table<Author> Authors;
    public Table<Title> Titles;   

    public MyDb(string connectionString) : base(connectionString) {
    }
}

MyDb myDb = new MyDb(myConnectionString); 

var query = from author in myDb.Authors where author.lastName = "Smith" orderby author.ID select author;

GridView1.DataSource = query.ToArray(); 

Author a = new Author(); 
myDb.Authors.InsertOnSubmit(a); 
myDb.SubmitChanges(); 

//or with a fetched instance of author
myDb.Authors.DeleteOnSubmit(a); 

a.LastName = "Name Change"; 
myDb.SubmitChanges(); 

XML

xmlDoc = new XmlDocument(); 
xmlDoc.CreateElement
xmlDoc.CreateAttribute
element.Attributes.Append
xmlDoc.AppendChild
xmlDoc.Load(MapPath("data.xml"));
xmlDoc.ChildNodes
node.ParentNode.RemoveChild(node);
node.ParentNode.InsertAfter(newElement, node);

xmlDoc.GetElementById("elementId");
//or
xmlDoc.SelectSingleNode("//elementName[@ChildID='elementId']"); //xpath query

XmlNodeList nodes = xmlDoc.GetElementsByTagName("tagName");
//or
XmlNodeList nodes = xmlDoc.SelectNodes("//tagName");



XPathNavigator xpathNav = xmlDoc.CreateNavigator(); 
XpathNavigator node = xpathNav.MoveToRoot(); 
node.HasAttributes
node.HasChildren
node.MoveToFirstAttribute()
node.Name, node.Value
node.MoveToNextAttribute()
node.MoveToParent(); 
node.MoveToFirstChild(); 
node.MoveToNext();





XPathExpression ex = xpathNav.Compile("//childTagName");
ex.AddSort("@ChildID", XmlSortOrder.Descending, XmlCaseOrder.None, "", XmlDataType.Text); 

XPathNodeIterator iterator = xpathNav.Select(xpathQuery);
XPathNavigator navResult = iterator.Current;
while(iterator.MoveNext()) {
    navResult.NodeType, navResult.Name
    navResult.HasAttributes
}

XmlTextWriter

XmlTextWriter xmlWriter = new XmlTextWriter(MapPath("data.xml"));
xmlWriter.Formatting = Formatting.Indented; 
xmlWriter.Indentation = 5;

xmlWriter.WriteStartDocument();
xmlWriter.WriteComment("Comment"); 
xmlWriter.WriteStartElement("CustomerList"); 
xmlWriter.WriteStartElement("Customer"); 
xmlWriter.WriteAttributeString("CustomerID", "1"); 
xmlWriter.WriteElementString("Address"); 
//...

//Address
xmlWriter.WriteEndElement(); 
//Customer
xmlWriter.WriteEndElement(); 
//CustomerList
xmlWriter.WriteEndElement(); 
xmlWriter.Close();

XmlReader

XmlTextWriter xmlWriter = new XmlTextWriter(MapPath("data.xml"));
xmlWriter.Formatting = Formatting.Indented; 
xmlWriter.Indentation = 5;

xmlWriter.WriteStartDocument();
xmlWriter.WriteComment("Comment"); 
xmlWriter.WriteStartElement("CustomerList"); 
xmlWriter.WriteStartElement("Customer"); 
xmlWriter.WriteAttributeString("CustomerID", "1"); 
xmlWriter.WriteElementString("Address"); 
//...

//Address
xmlWriter.WriteEndElement(); 
//Customer
xmlWriter.WriteEndElement(); 
//CustomerList
xmlWriter.WriteEndElement(); 
xmlWriter.Close();

Validation

XmlReaderSettings xmlSet = new XmlReaderSettings(); 
xmlSet.ValidationType = ValidationType.DTD; //for xml documents with embedded DTD
xmlSet.ProhibitDtd = false; 
XmlReader reader = XmlReader.Create(fileName, xmlSet); 
XmlDocument xd = new XmlDocument();
try {
    xd.Load(reader); 
    return true; 
}
catch (Exception ex) {
    ex.Message
}

LINQ to XML

XElement customers = XElement.Load(xmlFile); 
XElement customers = XElement.Parse(xmlString); 

//Or
XElement customers = 
    new XElement("Customers"), 
        new XElement("Customer", 
            new XElement("id", "1")
        ), 
        from cust in additionCustFile.Elements() select cust //add in some extra customers 
    );

IEnumerable<XElement> query = from cust in customers.Elements("Customer")
    where (int)cust.Element("Sales") > 1000
    select cust;

foreach (XElement cust in query) {
    cust.Element("FirstName").ToString(); 
    cust.Element("LastName").ToString(); 
}