Resources

Accessing data from within C# applications

Lesson 1

Disconnected Data Access

Data Set

Data Provider

DataReader Vs DataSet

Lesson 2

Connecting to DB

OleDbConnection myConnection = new OleDbConnection();

myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\\Northwind.mdb";

Using Command

SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

SqlCommand salesCMD = new SqlCommand("SalesByCategory", nwindConn);

salesCMD.CommandType = CommandType.StoredProcedure;

SqlParameter myParm = salesCMD.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15);

myParm.Value = "Beverages";

nwindConn.Open();

SqlDataReader myReader = salesCMD.ExecuteReader();

Console.WriteLine("{0}, {1}", myReader.GetName(0), myReader.GetName(1));

while (myReader.Read())
{
Console.WriteLine("{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1));
}

myReader.Close();

nwindConn.Close();
SqlConnection pubsConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=pubs;");

SqlCommand logoCMD = new SqlCommand("SELECT pub_id, logo FROM pub_info", pubsConn);

FileStream fs; // Writes the BLOB to a file

BinaryWriter bw; // Streams the BLOB to the FileStream object.

int bufferSize = 100; // Size of the BLOB buffer.

byte[] outbyte = new byte[bufferSize]; // The BLOB byte[] buffer to
// be filled by GetBytes.

long retval; // The bytes returned from
// GetBytes.

long startIndex = 0; // The starting position in
// the BLOB output.

string pub_id = ""; // The publisher id to use in
// the file name.

// Open the connection and read data into the DataReader.
pubsConn.Open();

SqlDataReader myReader = logoCMD.ExecuteReader(CommandBehavior.SequentialAccess);

while (myReader.Read())
{
// Get the publisher id, which must occur before getting the logo.

pub_id = myReader.GetString(0);

// Create a file to hold the output.
fs = new FileStream("logo" + pub_id + ".bmp", FileMode.OpenOrCreate, FileAccess.Write);

bw = new BinaryWriter(fs);

// Reset the starting byte for the new BLOB.
startIndex = 0;

// Read the bytes into outbyte[] and retain the number of bytes
// returned.

retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);

// Continue reading and writing while there are bytes beyond the
// size of the buffer.
while (retval == bufferSize)
{
bw.Write(outbyte);

bw.Flush();

// Reposition the start index to the end of the last buffer and
// fill the buffer.
startIndex+= bufferSize;

retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
}

// Write the remaining buffer.
bw.Write(outbyte);

bw.Flush();

// Close the output file.
bw.Close();

fs.Close();
}

// Close the reader and the connection.
myReader.Close();

pubsConn.Close();

Using DataReaders

System.Data.SqlClient.SqlDataReader mySqlReader;

mySqlReader = mySqlCommand.ExecuteReader();
while(mySqlReader.Read())
{
object myObject = myDataReader[3];

object myOtherObject = mydataReader["CustID"];
}
do
{
while(myReader.Read())
{
// Loop through records of RecordSet
}
} while (myReader.NextResult());
string Cmd = "DELETE * FROM Employees WHERE Name='" & aString & "'";

OleDbCommand myCommand = new OleDbCommand(Cmd, myConnection);

myConnection.Open();

myCommand.ExecuteNonQuery();

myConnection.Close();
DataTable schemaTable = myReader.GetSchemaTable();

foreach (DataRow myRow in schemaTable.Rows)
{
foreach (DataColumn myCol in schemaTable.Columns)
{
Console.WriteLine(myCol.ColumnName + " = " +
myRow[myCol]);
}

Console.WriteLine();
}

Creating Data Adapters

Using Data Adapters

DataSet myDataSet = new DataSet();

// Assume existence of Data Adapter named myDataAdapter
myDataAdapter.Fill(myDataSet, "Customers");
// Populate list of customers from SQL 2000 database and Orders from 
// Access database, establish relation between tables, display list
// of customers and their orders

SqlConnection custConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind;");

SqlDataAdapter custDA = new SqlDataAdapter("SELECT * FROM Customers", custConn);

OleDbConnection orderConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=c:\\Program Files\\Microsoft Office\\Office\\Samples\\northwind.mdb;");

OleDbDataAdapter orderDA = new OleDbDataAdapter("SELECT * FROM Orders", orderConn);

custConn.Open();

orderConn.Open();

DataSet custDS = new DataSet();

custDA.Fill(custDS, "Customers");

orderDA.Fill(custDS, "Orders");

custConn.Close();

orderConn.Close();

DataRelation custOrderRel = custDS.Relations.Add("CustOrders",

custDS.Tables["Customers"].Columns["CustomerID"],

custDS.Tables["Orders"].Columns["CustomerID"]);

foreach (DataRow pRow in custDS.Tables["Customers"].Rows)
{
Console.WriteLine(pRow["CustomerID"]);

foreach (DataRow cRow in pRow.GetChildRows(custOrderRel))
Console.WriteLine("\t" + cRow["OrderID"]);
}
// Obtain schema via FillSchema()
DataSet custDS = new DataSet();

custDA.FillSchema(custDS, SchemaType.Source, "Customers");

custDA.Fill(custDS, "Customers");

// Obtain schema via Fill()
DataSet custDS = new DataSet();

custDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;

custDA.Fill(custDS, "Customers");
workAdapter.TableMappings.Add("AuthorsMapping", "MyAuthors");
DataTableMapping custMap = custDA.TableMappings.Add("Table", "NorthwindCustomers");

custMap.ColumnMappings.Add( "CompanyName", "Company");

custMap.ColumnMappings.Add( "ContactName", "Contact");

custMap.ColumnMappings.Add( "PostalCode", "ZIPCode");

custDA.Fill(custDS);
custDA.TableMappings.Add("Customers1", "Orders")

custDA.Fill(custDS, "Customers")

Typed DataSets

- Standard DataSet = weekly typed - must convert data point from object to desired type 
- Typed DataSet = strongly typed 
- Instance of class derived from DataSet 
- Class structure defined by XML schema file (XSD) defining table names and types 
- Dependence on XSD means only use where know structure of data being used in advance 
- Create using Generate Dataset button in Data dialog box - generates XSD file and optionally adds instance of DataSet to designer 

Lesson 3

Filling DataSet without DataAdapter

DataSet myDataSet = new DataSet();

DataTable myTable = new DataTable();

myDataSet.Tables.Add(myTable);

DataColumn AccountsColumn = new DataColumn("Accounts");

myDataSet.Tables[0].Columns.Add(accountsColumn);

DataRow myRow;

myRow = myDataSet.Tables[0].NewRow();

for(int i=0; I < StringCollection.Count; i++)
{
myRow.Item[counter] = StringCollection [i];
}

myDataSet.Tables(0).Rows.Add(myRow);
DataSet myDataSet = new DataSet();

DataTable aTable = new DataTable("Table 1");

myDataSet.Tables.Add(aTable);

DataColumn aColumn;

for(int counter=0; counter <7 ;counter++)
{
aColumn = new DataColumn("Column " + counter.ToStgring());

mydataSet.Tables["Table 1"].Columns.Add(aColumn);
}

System.IO.StreamReader myReader = new System.IO.StreamReader("C:\\File.txt");

String myString;

while (myReader.Peek() != -1)
{
myString = myReader.ReadLine();

myDataSet.Tables["Table 1"].Rows.Add(myString.Split(char.Parse(",")));
}

Data Relation Objects

DataRelation myRelation = new DataRelation("Data Relation 1", column1, column2);

myDataSet.Relations.Add(myRelation);
DatasRow[] ChildRows;

DataRow ParentRow;

// Return all child rows of row 1 as defined by CustomersOrders
ChildRows = myDataSet.Tables["Customers"].Rows[1].GetChildRows(CustomersOrders);

// Return parent of row 5 as defined by CustomersOrders
ParentRow = myDataSet.Tables["Orders"].Rows[5].GetParentRow(CustomersOrders);

Constraints

UniqueConstraint myConstraint = new 

UniqueConstraint(myDataRow);

myDataTable.Constraints.Add(myConstraint);

Can specify multiple columns

DataColumn[] myColumns = new DataColumn[2];

myColumns[0] = EmployeesTable.Columns["FirstName"];

myColumns[0] = EmployeesTable.Columns["LastName"];

UniqueConstraint myConstraint = new UniqueConstraint(myColumns);

EmployeesTable.Constraints.Add(myConstraint);
ForeignKeyConstraint myConstraint = new ForeignKeyConstraint(CustomersTbl.Columns["CustomerID"], OrdersTbl.Columns["CustomerID"]);

CustomersTbl.Constraints.Add(myConstraint);

Editing Data

myDataRow[2] = "Splunge";

myDataRow["Customers"] = "Winthrop";
myDataRow.RejectChanges();

Updating Database

myDataAdapter.Update();

myOtherDataAdapter.Update(myDataTable);
System.Data.OleDb.OleDbTransaction myTransaction = null;

try
{
myConnection.Open();

myTransaction = myConnection.BeginTransaction();

---

myTransaction.Commit();
}

catch(Exception ex)
{
mytransaction.Rollback();
}

finally

{
myConnection.Close();
}

Handling Update Errors

private void myDataAdapter_RowUpdated( object sender, System.Data.SqlClient.SqlRowUpdatedEventArgs e)
{
if(e.Status == UpdateStatus.ErrorsOccured)
{
...

e.Status = UpdateStatus.SkipCurrentRow;
}
}

Lesson 4

Data Binding

Data Providers

Data Consumers

Create Simple bound control

TextBox1.DataBindings.Add("Text", DataSet1.Customers, "CustomerID");

Data Currency

- Every data source manages navigation using a CurrencyManager object 
- Data source = 1 or 2 D store (DataTable, DataView, array, collection) 
- DataSet contains many DataTables, thus many data sources 
- CurrencyManager keeps track of current record for its particular data source 
- Form may have multiple CurrencyManagers associated within its data sources, manages these through the BindingContext 
- Can use BindingContext to manage position of current record for each data source 
this.BindingContext[DataSet1.Customers].Position = 0;

thid.bindingContext[DataSet1.Customers].Position++;

Complex Binding

ComboBox1.DataSource = DataSet1.Customers;

ComboBox1.DisplayMember = "CustomerID";

Filtering and Sorting

DataView myDataView = new DataView(myDataTable);

Or

DataView myDataView = new DataView();

myDataView.Table = myDataTable;
myDataView.Sort = "CustomerID";

myOtherView.Sort = "State, City";

myDescView.Sort = "State DESC, City";
myDataView.RowFilter = "City = 'Seattle'";

myDataView.RowFilter = "City = 'Des Moines' AND (NOT State ='IA')";

myDataView.RowFilter = "Price *1.086 <= 500";

myDataView.RowFilter = "City IN ('Seattle', 'Tacoma', 'Blaine');

myDataView.RowFilter = "City LIKE 'Se*t%e'";
DataViewManager myManager = new DataViewManager(myDataSet);

DataViewManager myOtherManager = new DataViewManager();

myOtherManager.DataSet = myOtherDataSet;
// Set RowFilter property of DataView associated   
// with Customers table
myDataViewManager.DataViewSettings["Customers"].RowFilter = "State = 'WA'";
DataView myDataView;

myDataView = myDataViewManager.CreateDataView(DataSet1.Tables[0]);

Lesson 5

Retrieving XmlReader from SqlCommand

System.Xml.XmlReader myReader;

SqlCommand mySQLCommand = new SqlCommand("SELECT * FROM Customers FOR XML AUTO, XMLDATA", SqlConnection1);

SqlConnection1.Open();

myReader = mySQLCommand.ExecuteXmlReader();

while(myReader.Read())
{
// Write content and markup of node and its
// children

Console.WriteLine(myReader.ReadOuterXml());
}

myReader.Close();

SqlConnection1.Close();

Reading XML into DataSet

Writing XML from DataSet

XmlDataDocument

XmlDataDocument myDocument = new XmlDataDocument(myDataSet);
XmlDataDocument mydocument = new XmlDataDocument();

myDocument.DataSet.ReadXmlSchema("C:\\myXml.xml");

myDocument.Load("C:\\myXml.xml");

XSLT Transformations

Xml.Xsl.XslTransform myTransform = new Xml.Xsl.XslTransform();

myTransform.Load("C:\\myStyle.xsl");

System.IO.StreamWriter myWriter = new System.IO.StreamWriter("myfile.txt");

myTransform.Transform(myDocument, null, myWriter);

Downloads