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 += "&nbsp;&nbsp;&nbsp;" + 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 += "&nbsp;&nbsp;&nbsp;" + 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());

        }