Chapter 08
ADO.NET DISCONNECTED CLASSES
Disconnected Classes
Qellimi i ushtrimeve te meposhtme eshte te arrihet te punohet me disconnected data classes ne WebSite:
· DataTable
· DataColumn
· DataRow
· DataView
· DataSet
· Serialize (dhe deserialize) e DataSets drejt dhe nga XML.
· Te perdoret LINQ me DataSet
DataTable object paraqet te dhena tabelare si rows, columns, dhe constraints. DataTable object perdoret per te mbajtur te dhena ne memorie nderkohe qe realizohen operacione te shkeputura nga baza.
1. Ndertoni funksionin e meposhtme ne kod C#:
private DataTable GetDataTable()
{
//Create the DataTable named "employee"
DataTable employee = new DataTable("Employee");
//Add the DataColumn using all properties
DataColumn eid = new DataColumn("Eid");
eid.DataType = typeof(string);
eid.MaxLength = 10;
eid.Unique = true;
eid.AllowDBNull = false;
eid.Caption = "EID";
employee.Columns.Add(eid);
//Add the DataColumn using defaults
DataColumn firstName = new DataColumn("FirstName");
firstName.MaxLength = 35;
firstName.AllowDBNull = false;
employee.Columns.Add(firstName);
DataColumn lastName = new DataColumn("LastName");
lastName.AllowDBNull = false;
employee.Columns.Add(lastName);
//Add the decimal DataColumn using defaults
DataColumn salary = new DataColumn("Salary", typeof(decimal));
salary.DefaultValue = 0.00m;
employee.Columns.Add(salary);
//Derived column using expression
DataColumn lastNameFirstName = new DataColumn("LastName and FirstName");
lastNameFirstName.DataType = typeof(string);
lastNameFirstName.MaxLength = 70;
lastNameFirstName.Expression = "lastName + ', ' + firstName";
employee.Columns.Add(lastNameFirstName);
return employee;
}
Kujdes:
Me poshte eshte nje liste vlerash te mundshme default per propertite e DataColumn:
· DataType String
· MaxLength –1, qe do te thote qe kontrollohet vlera maksimale e gjatesise
· Unique False, lejon vlera te dublikuara
· AllowDBNull True, DataColumn nuk ka nevoje patjeter per nje vlere
· Caption, Vlera e ColumnName
2. Krijimi i Primary Key Columns
Ne funksionin e mesiperm perpara se te perfundoje funksioni, pra para se te behet return vlera, percaktoni Primary Key si me poshte:
//Set the Primary Key
employee.PrimaryKey = new DataColumn[] {eid};
3. Shtimi i te dhenave me ane te objektit DataRow.
Kjo realizohet me ane te dy metodave. Metoda Add mban nje shtese ten je array me objekte te reja ne vend te objekteve DataRow. Ky array duhet te kete aq objekte sa sasia e tipeve te DataColumn ne DataTable.
Metoda Load perdoret per te modifikuar objektet ekzistuese DataRow ose per te loaduar objekte te reja. Load pret si paramatra nje array me objekte dhe nje LoadOption enumeration qe mund te jete nje nga vlerat e meposhtme:
OverwriteChanges (Overwrites the original DataRowVersion and the current DataRowVersion and changes the RowState to Unchanged),
PreserveChanges (default) (Overwrites the original DataRowVersion, but does not modify the current DataRowVersion. New rows have the RowState of Unchanged as well),
Upsert (Overwrites the current DataRowVersion, but does not modify the original DataRowVersion. New rows have the RowState of Added).
Tek funksioni i mesiperm shtoni rreshtat e meposhtem para return:
//Add New DataRow by creating the DataRow first
DataRow newemployee = employee.NewRow();
newemployee["Eid"] = "123456789A";
newemployee["FirstName"] = "Nancy";
newemployee["LastName"] = "Davolio";
newemployee["Salary"] = 10.00m;
employee.Rows.Add(newemployee);
//Add New DataRow by simply adding the values
employee.Rows.Add("987654321X", "Andrew", "Fuller", 15.00m);
//Load DataRow, replacing existing contents, if existing
employee.LoadDataRow(
new object[] { "987654321X", "Janet", "Leverling", 20.00m },
LoadOption.OverwriteChanges);
4. Shtoni nje GridView tek Default.aspx duke bere drag and drop.
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
5. Shtoni nje button dhe nje event listener per klikimin e butonit. Drag drop buttonin ne design mode dhe me pas jepini dubel klik butonit per tu gjeneruar funskioni i handler-it te klikimit.
Tek default.aspx:
<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
Tek default.aspx.cs:
protected void Button1_Click(object sender, EventArgs e)
{
}
6. Binding i te dhenave, objekti DataTable mund te behet bound me cdo data-bound control duke ia dhene vleren e saj DataSource property dhe me ekzekutimin e metodes DataBind me pas.
Per kete tek funskioni i mesiperm beni ndryshimet:
protected void Button1_Click(object sender, EventArgs e)
{
DataTable provaDT = GetDataTable();
GridView1.DataSource = provaDT;
GridView1.DataBind();
}
7. Per marrjen e informacionit nga DataRow shtoni nje label tek Default.aspx:
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
8. Pas kesaj shtoni nje button dhe nje event listener per klikimin e butonit. Drag drop buttonin ne design mode dhe me pas jepini dubel klik butonit per tu gjeneruar funskioni i handler-it te klikimit.
Tek default.aspx:
<asp:Button ID="Button2" runat="server" Text="Button" onclick="Button2_Click" />
Tek default.aspx.cs:
protected void Button2_Click(object sender, EventArgs e)
{
}
9. Shtoni funksionin e meposhtem tek Default.aspx.cs
private string GetDataRowInfo(DataRow row, string columnName)
{
string retVal = string.Format( "RowState: {0}<br />", row.RowState);
foreach (string versionString in Enum.GetNames(typeof(DataRowVersion)))
{
DataRowVersion version = (DataRowVersion)Enum.Parse(
typeof(DataRowVersion), versionString);
if (row.HasVersion(version))
{
retVal += string.Format("Version: {0} Value: {1}<br />",
version, row[columnName, version]);
}
else
{
retVal += string.Format("Version: {0} does not exist.<br />",
version);
}
}
return retVal;
}
10. Per te provuar rezultatin modifikoni Button2_Click te shkruajtur me siper:
DataTable provaDT = GetDataTable();
Label1.Text = GetDataRowInfo(provaDT.Rows[0], "LastName and FirstName");
11. Kur duam te kopjojme tabelen perdorim nje nga dy metodat Copy ose Clone. Copy I ben kopje edhe te dhenave ndersa klonimi vetem struktures se datatable. Per ta demonstruar shtoni dy GridView te tjera tek Default.aspx.
<asp:GridView ID="GridView2" runat="server">
</asp:GridView>
<asp:GridView ID="GridView3" runat="server">
</asp:GridView>
12. Tek funksioni i Button1_Click te ndertuar me siper ne hapin e peste shtoni rreshtat e meposhtem:
DataTable copy = provaDT.Copy();
GridView2.DataSource = copy;
GridView2.DataBind();
DataTable clone = provaDT.Clone();
clone.ImportRow(provaDT.Rows[0]);
GridView3.DataSource = clone;
GridView3.DataBind();
13. Shtoni trembedhjete butona te tjere ne faqe dhe me dubel klik gjeneroni funksionet perkatese te event handlerave te tyre:
<asp:Button ID="Button3" runat="server" Text="Button" onclick="Button3_Click" />
<br /><br />
<asp:Button ID="Button4" runat="server" Text="Button" onclick="Button4_Click" />
<br /> <br />
<asp:Button ID="Button5" runat="server" Text="Button" onclick="Button5_Click" />
<br /> <br />
<asp:Button ID="Button6" runat="server" Text="Button" onclick="Button6_Click" />
<br /><br />
<asp:Button ID="Button7" runat="server" Text="Button" onclick="Button7_Click" />
<br /> <br />
<asp:Button ID="Button8" runat="server" Text="Button" onclick="Button8_Click" />
<br /><br />
<asp:Button ID="Button9" runat="server" Text="Button" onclick="Button9_Click" />
<br /><br />
<asp:Button ID="Button10" runat="server" Text="Button" onclick="Button10_Click" />
<br /><br />
<asp:Button ID="Button11" runat="server" Text="Button" onclick="Button11_Click" />
<br /><br />
<asp:Button ID="Button12" runat="server" Text="Button" onclick="Button12_Click" />
<br /><br />
<asp:Button ID="Button13" runat="server" Text="Button" onclick="Button13_Click" />
<br /><br />
<asp:Button ID="Button14" runat="server" Text="Button" onclick="Button14_Click" />
<br /><br />
<asp:Button ID="Button15" runat="server" Text="Button" onclick="Button15_Click" />
<br /><br />
14.
//C#
protected void Button3_Click(object sender, EventArgs e)
{
DataTable employee = GetDataTable();
employee.WriteXml(Server.MapPath("employee.xml"));
Response.Redirect("employee.xml");
}
15.
protected void Button4_Click(object sender, EventArgs e)
{
DataTable employee = GetDataTable();
employee.TableName = "Person";
employee.Columns["Eid"].ColumnMapping = MappingType.Attribute;
employee.Columns["FirstName"].ColumnMapping = MappingType.Attribute;
employee.Columns["LastName"].ColumnMapping = MappingType.Attribute;
employee.Columns["Salary"].ColumnMapping = MappingType.Attribute;
employee.Columns["LastName and FirstName"].ColumnMapping =
MappingType.Hidden;
employee.WriteXml(Server.MapPath("Person.xml"));
Response.Redirect("Person.xml");
}
16.
protected void Button5_Click(object sender, EventArgs e)
{
DataTable employee = GetDataTable();
employee.TableName = "Person";
employee.Columns["Eid"].ColumnMapping = MappingType.Attribute;
employee.Columns["FirstName"].ColumnMapping = MappingType.Attribute;
employee.Columns["LastName"].ColumnMapping = MappingType.Attribute;
employee.Columns["Salary"].ColumnMapping = MappingType.Attribute;
employee.Columns["LastName and FirstName"].ColumnMapping =
MappingType.Hidden;
employee.WriteXml(Server.MapPath("PersonWithSchema.xml"),
XmlWriteMode.WriteSchema);
Response.Redirect("PersonWithSchema.xml");
}
17.
protected void Button6_Click(object sender, EventArgs e)
{
//add grid to form
GridView gv = new GridView();
gv.Style.Add("position", "absolute");
gv.Style.Add("left", "275px");
gv.Style.Add("top", "200px");
gv.EnableViewState = false;
this.Panel1.Controls.Add(gv);
//get the table and display
DataTable xmlTable = new DataTable();
xmlTable.ReadXml(Server.MapPath("PersonWithSchema.xml"));
gv.DataSource = xmlTable;
gv.DataBind();
}
18. Shtoni funksionin:
private DataSet GetDataSet()
{
DataSet companyData = new DataSet("CompanyList");
DataTable company = companyData.Tables.Add("company");
company.Columns.Add("Id", typeof(Guid));
company.Columns.Add("CompanyName", typeof(string));
company.PrimaryKey = new DataColumn[] { company.Columns["Id"] };
DataTable employee = companyData.Tables.Add("employee");
employee.Columns.Add("Id", typeof(Guid));
employee.Columns.Add("companyId", typeof(Guid));
employee.Columns.Add("LastName", typeof(string));
employee.Columns.Add("FirstName", typeof(string));
employee.Columns.Add("Salary", typeof(decimal));
employee.PrimaryKey = new DataColumn[] { employee.Columns["Id"] };
companyData.Relations.Add(
"Company_Employee",
company.Columns["Id"],
employee.Columns["CompanyId"]);
company = companyData.Tables["Company"];
employee = companyData.Tables["Employee"];
Guid coId, empId;
coId = Guid.NewGuid();
company.Rows.Add(coId, "Northwind Traders");
empId = Guid.NewGuid();
employee.Rows.Add(empId, coId, "JoeLast", "JoeFirst", 40.00);
empId = Guid.NewGuid();
employee.Rows.Add(empId, coId, "MaryLast", "MaryFirst", 70.00);
empId = Guid.NewGuid();
employee.Rows.Add(empId, coId, "SamLast", "SamFirst", 12.00);
coId = Guid.NewGuid();
company.Rows.Add(coId, "Contoso");
empId = Guid.NewGuid();
employee.Rows.Add(empId, coId, "SueLast", "SueFirst", 20.00);
empId = Guid.NewGuid();
employee.Rows.Add(empId, coId, "TomLast", "TomFirst", 68.00);
empId = Guid.NewGuid();
employee.Rows.Add(empId, coId, "MikeLast", "MikeFirst", 18.99);
return companyData;
}
19. Shtoni nje label ne kodin e Default.aspx. Modifikoni Button8_Click:
protected void Button8_Click(object sender, EventArgs e)
{
Label lbl = Label2;
//get the dataset and populate
DataSet companyList = GetDataSet();
//get the relationship
DataRelation dr = companyList.Relations["Company_Employee"];
//display second company
DataRow companyParent = companyList.Tables["company"].Rows[1];
lbl.Text = companyParent["CompanyName"] + "<br />";
//display employees
foreach (DataRow employeeChild in companyParent.GetChildRows(dr))
{
lbl.Text += " " + employeeChild["Id"] + " "
+ employeeChild["LastName"] + " "
+ employeeChild["FirstName"] + " "
+ string.Format("{0:C}", employeeChild["Salary"]) + "<br />";
}
lbl.Text += "<br /><br />";
//display second employee
DataRow employeeParent = companyList.Tables["employee"].Rows[1];
lbl.Text += employeeParent["Id"] + " "
+ employeeParent["LastName"] + " "
+ employeeParent["FirstName"] + " "
+ string.Format("{0:C}", employeeParent["Salary"]) + "<br />";
//display company
DataRow companyChild = employeeParent.GetParentRow(dr);
lbl.Text += " " + companyChild["CompanyName"] + "<br />";
}
20.
protected void Button9_Click(object sender, EventArgs e)
{
//get the dataset and populate
DataSet companyList = GetDataSet();
//write to xml file
companyList.WriteXml(MapPath("CompanyList.xml"));
//display file
Response.Redirect("CompanyList.xml");
}
21.
protected void Button10_Click(object sender, EventArgs e)
{
//get the dataset and populate
DataSet companyList = GetDataSet();
//format xml
companyList.Relations["Company_Employee"].Nested = true;
foreach (DataTable dt in companyList.Tables)
{
foreach (DataColumn dc in dt.Columns)
{
dc.ColumnMapping = MappingType.Attribute;
}
}
//write to xml file
companyList.WriteXml(MapPath("CompanyListNested.xml"));
//display file
Response.Redirect("CompanyListNested.xml");
}
22.
protected void Button11_Click(object sender, EventArgs e)
{
//get the dataset and populate
DataSet companyList = GetDataSet();
DataTable company = companyList.Tables["company"];
company.Rows.Add(Guid.NewGuid(), "UnchangedCompany");
company.Rows.Add(Guid.NewGuid(), "ModifiedCompany");
company.Rows.Add(Guid.NewGuid(), "DeletedCompany");
companyList.AcceptChanges();
company.Rows[1]["CompanyName"] = "ModifiedCompany1";
company.Rows[2].Delete();
company.Rows.Add(Guid.NewGuid(), "AddedCompany");
//format xml
companyList.Relations["Company_Employee"].Nested = true;
foreach (DataTable dt in companyList.Tables)
{
foreach (DataColumn dc in dt.Columns)
{
dc.ColumnMapping = MappingType.Attribute;
}
}
//write to xml diffgram file
companyList.WriteXml(
MapPath("CompanyListDiffGram.xml"), XmlWriteMode.DiffGram);
//display file
Response.Redirect("CompanyListDiffGram.xml");
}
23. Krijo dy GridView si me poshte:
<asp:GridView ID="GridViewCompany" runat="server">
</asp:GridView>
<br /><br />
<asp:GridView ID="GridViewEmployee" runat="server">
</asp:GridView>
24. Ne Solution Explorer, shto nje DataSet grafikisht duke klikuar me te djathten tek Web site project dhe duke bere Add New Item. Select DataSet, dhe quaje CompanyListSchema.xsd. Drag nje DataTable nga ToolBox dhe beje drop tek siperfaqja e DataSet Editor.
protected void Button12_Click(object sender, EventArgs e)
{
//get the dataset and populate schema
DataSet companyList = new DataSet();
companyList.ReadXmlSchema(MapPath("CompanyListSchema.xsd"));
//populate from file
companyList.ReadXml(MapPath("CompanyListNested.xml"));
//display
GridViewCompany.DataSource = companyList;
GridViewCompany.DataMember = "Company";
GridViewEmployee.DataSource = companyList;
GridViewEmployee.DataMember = "Employee";
}
25. Serializimi
//Add the following using statements to the top of the file
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;
protected void Button13_Click(object sender, EventArgs e)
{
//get the dataset and populate
DataSet companyList = GetDataSet();
//set output to binary else this will be xml
companyList.RemotingFormat = SerializationFormat.Binary;
//write to binary file
using (FileStream fs = new FileStream(MapPath("CompanyList.bin"), FileMode.Create))
{
BinaryFormatter fmt = new BinaryFormatter();
fmt.Serialize(fs, companyList);
}
//feedback
Label1.Text = "File Saved.";
}
26. Deserializimi:
protected void Button14_Click(object sender, EventArgs e)
{
//get the dataset from the file
DataSet companyList;
using (FileStream fs = new FileStream(MapPath("CompanyList.bin"), FileMode.Open))
{
BinaryFormatter fmt = new BinaryFormatter();
companyList = (DataSet)fmt.Deserialize(fs);
}
//display
GridViewCompany.DataSource = companyList;
GridViewCompany.DataMember = "Company";
GridViewEmployee.DataSource = companyList;
GridViewEmployee.DataMember = "Employee";
GridViewCompany.DataBind();
GridViewEmployee.DataBind();
}
27. Implementimi i MERGE per te kombinuar te dhenat e DataSet.
protected void Button15_Click(object sender, EventArgs e)
{
//get the dataset
DataSet original = GetDataSet();
//add AdventureWorks
original.Tables["Company"].Rows.Add(
Guid.NewGuid(), "AdventureWorks");
//copy the dataset
DataSet copy = original.Copy();
//modify the copy
DataRow aw = copy.Tables["Company"].Rows[0];
aw["CompanyName"] = "AdventureWorks Changed";
Guid empId;
empId = Guid.NewGuid();
copy.Tables["Employee"].Rows.Add(empId, aw["Id"],
"MarkLast", "MarkFirst", 90.00m);
empId = Guid.NewGuid();
copy.Tables["employee"].Rows.Add(empId, aw["Id"],
"SueLast", "SueFirst", 41.00m);
//merge changes back to the original
original.Merge(copy, false, MissingSchemaAction.AddWithKey);
//display
GridViewCompany.DataSource = original;
GridViewCompany.DataMember = "Company";
GridViewEmployee.DataSource = original;
GridViewEmployee.DataMember = "Employee";
GridViewCompany.DataBind();
GridViewEmployee.DataBind();
}
28. Perdorimi i LINQ per te bere Query mbi te dhenat
protected void Page_Load(object sender, EventArgs e)
{
DataTable employees = GetDataTable();
EnumerableRowCollection<DataRow> query =
from employee in employees.AsEnumerable()
where employee.Field<Decimal>("salary") > 20
orderby employee.Field<Decimal>("salary")
select employee;
foreach (DataRow emp in query)
{
Response.Write(emp.Field<String>("LastName") + ": ");
Response.Write(emp.Field<Decimal>("salary") + "<br />");
}
var queryAvg = from employee in employees.AsEnumerable()
group employee by "" into g
select new
{
AvgSalary = g.Average(employee =>
employee.Field<Decimal>("Salary"))
};
foreach (var emp in queryAvg)
{
Response.Write(emp.AvgSalary.ToString() + "<br />");
}
Decimal avgSalary = employees.AsEnumerable().Average(
employee => employee.Field<Decimal>("Salary"));
Response.Write(avgSalary.ToString());
}