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