ADO.net DataSet : Sort Data in a DataSet: DataView

Dataset: Sort data in a Dataset: DataView

// Prerequisites: SqlConnection, SqlDataAdapter, DataSet, DataGrid objects
// Pass SQL connection with connection string
// initialize data adapter passing the query and connection object
// call fill method of data adapter with dataset object
// initialize dataview from tables propeties from dataset
// call Sort method of DataView
// Specify dataview object at the data source for datagrid
// call the bind method of datagrid

SqlConnection cn;
cn=new SqlConnection(ConnectionString);

SqlDataAdapter da;
DataSet ds=new DataSet();

da=new SqlDataAdapter("select emp_id, fname, lname from employee",cn);
da.Fill(ds);

DataView dtView = ds.Tables[0].DefaultView;
dtView.Sort = "fname ASC";

DataGrid1.DataSource=dtView;
DataGrid1.DataBind();

using Keyword: Two uses of using keyword

using: Keyword
• Defines a scope, outside of which an object/objects will be disposed
• using is also used to specify the namespace like: using System.Xml
• using statement allows the programmer to specify when objects that use resources should release them
• The object provided to the using statement must implement the Idisposable interface
• Idisposable interface provides Dispose method, which should release object’s resources
• Example:
using (SqlConnection connection = new SqlConnection(connectionString))
{
// using ensures connection object is disposed when control exits using
//block
}

Web.Config: Encrypt Sections Programatically

Web.Config: Encrypt Configuration Section programatically:

Configuration config = Configuration.GetWebConfiguration(Request.ApplicationPath);
ConfigurationSection section = config.Sections[“ConnectionStrings”];
section.ProtectSection(“DataProtectionConfigurationProvider”);
config.Update();

Alternate Code:

Configuration config = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath);

ConfigurationSection section = config.GetSection(‘sectionNameHere’);

if(section != null && !section.SectionInformation.IsProtected)
{
section.SectionInformation.ProtectSection(‘providerName’);
config.Save();
}

Web.Config: Encrypt Sections using Aspnet_regiis

Web.Config: Encrypting Configuration Sections through Aspnet_regiis

• Use the IIS Registration tool (Aspnet_regiis.exe) to encrypt or decrypt sections of a Web Configuration file
• Tool is located in: windows\Microsoft.NET\Framework\versionNumber\
• ASP.net will automatically decrypt the information when web.config is processed
• System.Configuration classes can also be used
• Syntax for Encrypting:
aspnet_regiis –pe “connectionStrings” –app “/SampleApplication” –prov “RSAProtectedConfigurationProvider”
• Syntax for Decrypting:
aspnet_regiis –pd “connectionStrings” –app “/SampleApplication”

XML: Code Listing: XmlElement, XmlAttributeCollection

XML: Basics, Attributes Collection

XML: Process XML Data In-Memory: System.XML
• DOM is an in-memory tree representation of an XML document.
• XMLDocument, XpathDocument, XpathNavigator classes allow construction of XML documents, load and access data, modify and save the changes
• XpathDocument class is read-only based on Xpath data model
• XpathNavigator class offers several editing options and navigation capabilities using a cursor model

Accessing Attributes in the DOM:
• Attributes are properties of the element, not children of the element
• When the current node is an element, use the HasAttribute method to see if there are any attributes associated with the element
• Some important methods for retrieving XML data:
• XmlElement.GetAttribute, GetAttributeNode: Get attributes as collection
• XmlElement.Attributes property to retrieve all the attributes
• XMLAttributeCollection.Count to get the number of attributes

ADO.net DataAdapter Update Code Listing

Code Listing: Updating through DataAdapter

private static void AdapterUpdate(string connectionString)
{
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlDataAdapter dataAdpater = new SqlDataAdapter(
"SELECT CategoryID, CategoryName FROM Categories",connection);

dataAdpater.UpdateCommand = new SqlCommand(
"UPDATE Categories SET CategoryName = @CategoryName " +
"WHERE CategoryID = @CategoryID", connection);

dataAdpater.UpdateCommand.Parameters.Add(
"@CategoryName", SqlDbType.NVarChar, 15, "CategoryName");

SqlParameter parameter = dataAdpater.UpdateCommand.Parameters.Add(
"@CategoryID", SqlDbType.Int);
parameter.SourceColumn = "CategoryID";
parameter.SourceVersion = DataRowVersion.Original;

DataTable categoryTable = new DataTable();
dataAdpater.Fill(categoryTable);

DataRow categoryRow = categoryTable.Rows[0];
categoryRow["CategoryName"] = "New Beverages";

dataAdpater.Update(categoryTable);

Console.WriteLine("Rows after update.");
foreach (DataRow row in categoryTable.Rows)
{
{
Console.WriteLine("{0}: {1}", row[0], row[1]);
}
}
}
}