Chapter 05





Per te punuar ne kete leksion duhet te keni mjetet ndihmese si me poshte:
- Sql Server 2008 R2
Mund ta gjeni tek: 
http://www.microsoft.com/en-us/download/details.aspx?id=30438
Hapat e Instalimit i keni ne PDF bashkengjitur. Gjithashtu bashkengjitur ndodhet
databaza Northwind dhe Helper.pdf qe ju orienton ne lidhje me restore te databazes ne fjale.


SHTIMI I EVENT HANDLERAVE NE MENYRE DINAMIKE:


Shtimi i funksionit të klikimit të butonit të krijuar në mënyrë dinamike në loadim të faqes

 Ne ushtrimin me poshte shikojme se si i shtohet ne menyre dinamike nje butoni, mundesi per te therritur nje handler ne klikim, kur behet fjale per klikim ne anen Server Site. Per kete perdoret operatori i shtimit “+=” dhe si vlere jepet emri i funksionit. 

<%@ Page Language="C#" %>

<!DOCTYPE html>

<html>

<head runat="server">

    <title></title>

    <script runat="server">

       

        private void Page_Load(object sender, EventArgs e)

        {

            Button btn1 = new Button();

            btn1.ID = "btn1";

            btn1.Text = "Button i shtuar ne Loadim";

            btn1.Click += klikim;

            form1.Controls.Add(btn1);           

        }

 

        protected void klikim(object sender, EventArgs e)

        {

           // Button btn1 = (Button)form1.FindControl("btn1");

            Button btn1 = (Button)sender;           

            if (btn1 != null)

            {

                btn1.Text = "Button i klikuar";

            }           

        }

       

    </script>

</head>

<body>

    <form id="form1" runat="server">   

    </form>

</body>

</html>

 

Shtimi i funksionit keydown të textboxit të krijuar në mënyrë dinamike në loadim të faqes

Ne ushtrimin me poshte shikojme se si i shtohet ne menyre dinamike nje TextBoxi, mundesia per te therritur nje handler ne keydown, kur behet fjale per event  ne anen Client Site. Per kete perdoret atributi “onkeydown” dhe per tia shtuar qe ne loadim textboxit duhet te perdorim “Attributes” dhe vlera qe merr eshte nje string qe permban kod JavaScript. Konkretisht nese kemi klikuar butonin Enter te cilit i korrespondon kodi “13” atehere na shfaqet nje “J”.

<%@ Page Language="C#" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

    <script type="text/C#" runat="server">

        private void Page_Load(object sender, EventArgs evt)

        {

            TextBox txt1 = new TextBox();

            txt1.Text = "Fillim";

            txt1.ID = "txt1";

            txt1.Attributes["onkeydown"] = "if(event.keyCode == 13){alert(':)')} else{alert(':(')}";

            form1.Controls.Add(txt1);

        }

    </script>

</head>

<body>

    <form id="form1" runat="server">

    </form>

</body>

</html>

 

Thirrja e nje funksioni ne Client Side

 

<%@ Page Language="C#" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head id="Head1" runat="server">

    <title></title>

    <script type="text/javascript" >

        function KeyUpProva(event)

        {

            if (event.keyCode == 13)

            {

                 alert(':)') ;

            }

            else

            {

                alert(':(');

            }

        }

    </script>

    <script type="text/C#" runat="server">

        private void Page_Load(object sender, EventArgs evt)

        {

            TextBox txt1 = new TextBox();

            txt1.ID = "txt1";

            txt1.Attributes["placeholder"] = "shkruaj";

            txt1.Attributes["onkeyup"] = "KeyUpProva(event)";

            form1.Controls.Add(txt1);

        }

    </script>

</head>

<body>

    <form id="form1" runat="server">

    </form>

</body>

</html>

 

DATABASE COMMUNICATION


 ADO.NET Data Providers 

Nje data provider eshte nje bashkesi klasash ADO.NET qe na lejon te aksesojme nje databaze specifike, te ekzekutojme komanda sql dhe te marrim te dhena. Ne thelb, data provider  eshte nje ure midis application dhe  data source.

Klasat perfshijne:

Connection: Perdoret per te vendosur nje connection me nje data source.

Command: Perdoret per te akzekutuar komanda SQL dhe stored procedures.

DataReader: Mundeson akses te shpejte read-only, forward-only ne te dhenat qe jane marre me ane te nje query.

DataAdapter : Ky object realizon dy pune. Se pari perdoret per te mbushur nje DataSet , e cila eshte nje bashkesi e shkeputur tabelash dhe relacionesh me informacion te marre nga nja DataSource. Se dyti, perdoret per te bere modifikime ne DataSource ne baze te modifikimeve qe i jane bere DataSet.

 Cdo data provider, ke nje implementim specific te klasave te Connection, Command, DataReader, dhe DataAdapter qe i perket nje RBDMS. Per shembull nese nevojitet nje lidhje me nje SQL Server database, do te perdorim nje SqlConnection.

Modeli i ADO.NET provider eshte i shtueshem. Ne fjale te tjera, do te thote qe programuesit mund te krijojne providerat e tyre per tu lidhur me data sources te tjera.

 .NET Framework ka kater providera:

SQL Server provider: Mundeson akses ne SQL Server database (version 7.0 ose me i vone).

OLE DB provider: Mundeson akses ne cdo data source qe ka nje OLE DB driver. Kjo perfshin SQL Server databases me te hershme se versioni 7.0.

Oracle provider: Mundeson akses ne Oracle database (version 8 ose me i vone).

ODBC provider: Mundeson akses ne cdo data source qe ka nje ODBC driver.

 

 

ADO.NET ka dy tipe objektesh:  connection-based dhe  content-based.

Connection-based: Keto jane objekte si Connection, Command, DataReader, dhe DataAdapter. Ato mundesojne lidhjen me DB, ekzekutojne SQL statements, levizin ne readonly result set, dhe mbushin nje DataSet. Keto objekte connection-based jane specifike per datasource te ndryshme.

Content-based: Keto objekte jane ne te vertet “packages” per te dhenat. Perfshijne DataSet, DataColumn, DataRow, DataRelation, dhe dis ate tjera. Jane te pavarura nga tipi I data source dhe gjenden ne System.Data namespace.

 Connection class

Connection class na lejon te vendosim nje connection me data source me te cilen duam te nderveprojme. 

Connection Strings

Kur krijojme nje objekt Connection, na duhet ti japim nje connectiontion string. Connectiontion string eshte nje seri atributesh te perbera nga name/value dhe te ndara nga njera-tjetra me pikepresje (;). Rradha e ketyre veprimeve dhe shkronjat e medha jane te parendesishme. Ato shikohen sin je bashkesi.

Megjithese kemi njefare varesie nga RDBMS dhe provider qe ne perdorim, disa pjese jane gjithnje te perdorshme dhe te domosdoshme:

  • Serveri ku gjendet database:
  • Database qe do te perdorim:
  • Si do te na autentifikoje database 

Per shembull me poshte shikoni nje connection string qe perdoret nese do te lidhemi me Northwind database ne kompjuterin aktual duke perdorur integrated security (e cila perdor userin e loguar aktualisht ne Windows per te aksesuar database):

 string connectionString = "Data Source= GENA\GENAINSTANCE; Initial Catalog=Northwind;" +

  "Integrated Security=SSPI";

 Nese nuk suportohet integrated security atehere connection duhet te tregoje nje valid user dhe password. Per nje SQL Server database, accounti sa (system administrator) eshte me i perdorshmi. Me poshte nje string qe perdor kete account:

string connectionString = "Data Source=localhost; Initial Catalog=Northwind;" +

  "user id=sa; password=opensesame";

 Nese perdorim OLE DB provider, connection string do te jete I ngjashem me shtesen e percaktimit ten je provider setting qe identifikon OLE DB driver. Per shembull, mund te perdorim connection stringun e meposhtem per to lidhur me nje Oracle database me ane te MSDAORA OLE DB provider:

string connectionString = "Data Source=localhost; Initial Catalog=Sales;" +

  "user id=sa; password=da#ta_li#nk_43;Provider=MSDAORA";

 Nje shembull qe tregon lidhjen me nje Access database file:

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

  @"Data Source=C:\DataSources\Northwind.mdb";

 

Kur krijojme nje objekt Connection mund te kalojme nje connection string sin je parameter te konstruktorit. 

<configuration>

  <connectionStrings>

    <add name="Northwind" connectionString="Data Source=localhost; Initial Catalog=Northwind; Integrated Security=SSPI"/>

  </connectionStrings>

  ...

</configuration>

Me pas mund te merni connection string me ane te emrit te saj duke perdorur koleksionin WebConfigurationManager.ConnectionString, si me poshte, pa haruar te importoni System.Web.Configuration namespace:

string connectionString =

  WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

 Testimi i Connection

Ne tashme mund te perdorim metodat Open() dhe Close(). Per te perdorur kete kod do te duhet te importojme System.Data.SqlClient namespace.

string connectionString =

  WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

SqlConnection con = new SqlConnection(connectionString);

try

{

    con.Open();

    lblInfo.Text = "<b>Server Version:</b> " + con.ServerVersion;

    lblInfo.Text += "<br /><b>Connection Is:</b> " + con.State.ToString();

}

catch (Exception err)

{

    lblInfo.Text = "Error reading the database. " + err.Message;

}

finally

{

    con.Close();

    lblInfo.Text += "<br /><b>Now Connection Is:</b> " +

    con.State.ToString();

}

 

CONNECTION POOLING

Lidhja me aplikacionin kerkon nje kohe relativisht te shkurter, por te mire percaktuar. Ne nje aplikacion web ne te cilen kerkesat jane te shumta, koneksionet hapen dhe mbyllen pafundesisht per sa kohe qe vijne kerkesa te reja. Ne kete ambient, vonesa per lidhjen e klientit me aplikacionin behet e konsiderueshme ne qofte se sasia e klienteve rritet, pra me rritjen e klienteve (koneksioneve) do te vihej re dhe nje rritje ne vonesen e pergjithshme me aplikacionin qe do te shkaktonte vonesa dhe ngadalesim te pergjithshem te aplikacionit. Zgjidhja per kete rast quhet connection pooling (grupim i lidhjeve, pishine me lidhje). Grupimi i lidhjeve është praktika e mbajtjes së një grup të përhershëm të lidhjeve të hapura bazës së të dhënave që do të ndahen midis nje grupi perdoruesish permes nje sesioni qe do te kete gjithmone lidhje me te njejten data source. Kjo anashkalon nevojen per krijim dhe shkaterrim te koneksioneve me databasen. Connection pool ne ADO.NET jane teresisht transparente ndaj programuesit. Kur nje klient dergon nje kerkese duke therritur funksionin Open(), ai sherbehet direkt nga pooli pa patur nevoje per rikrijimin e nje sesioni te ri. Kur nje klient e leshon lidhjen me databasen, ai therret funksionin Close() ose Dispose() . Ne kete rast koneksioni ne fakt nuk fshihet por lihet ne pool, ne menyre te tille qe t’i sherbej klientit pasardhes. ADO.NET nuk perfshin nje mekanizem per connection pool, por gjithsesi shumica e provider-ave te ADO.NET implementojne disa forma te connection pooling. 

string connectionString = "Data Source=localhost; Initial Catalog=Northwind;" +

  "Integrated Security=SSPI; Min Pool Size=10";

SqlConnection con = new SqlConnection(connectionString);

con.Open();

con.Close();


Klasat Command dhe DataReader

Command

Para perdorimit te command, duhet te zgjedhim command type, te vendosim  command text, dhe ta lidhim me nje connection. Per kete  percaktojme attributet: CommandType, CommandText, Connection.

Command text mund te jete SQL statementstored procedure, ose emri i nje table ne varesi te komandes qe perdorim. 

CommandType.Text Ekzekuton nje SQL statement direkte. Kjo SQL statement merret nga atributi CommandText. Kjo eshte vlera default.

CommandType.StoredProcedure Command executon nje stored procedure ne data source. Atributi CommandText  ka emrin e kesaj stored procedure.

CommandType.TableDirect  Command ben query mbi te gjithe recordet ne tabele. Atributi CommandText eshte emri i tabeles nga di te meren rekordet . (Ky opsion nuk suportohet nga SQL Server data provider)

 Shembull:

string connectionString = "Data Source=localhost; Initial Catalog=Northwind;user id=sa;“ +“password=opensesame";

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

cmd.CommandType = CommandType.Text;

cmd.CommandText = "SELECT * FROM Employees";

 Shembull me konstruktor te Command. Nuk vendoset CommandType, sepse CommandType.Text  eshte vlera default.

 

SqlCommand cmd = new SqlCommand("SELECT * FROM Employees", con);

 Rast i  stored procedure:

 

SqlCommand cmd = new SqlCommand("GetEmployees", con);

cmd.CommandType = CommandType.StoredProcedure;

 

Keto komanda specifikojne nje objekt Command; ato nuk e ekzekutojne ate.

 

ExecuteNonQuery() Ekzekuton komandat non-SELECT, sic jane SQL commandat qe bejne insert, delete, ose update. Vlera e kthyer tregon numrin e rreshtave qe jane ndikuar nga zbatimi I komandes. Gjithashtu e perdorim per komandat create, alter, ose delete.

ExecuteScalar() Ekzekuton nje SELECT query dhe kthen vleren e pare te rreshtit te pare te bashkesise se rreshtave te kthyera nga komanda. Kjo  etode perdoret zakonisht kur behet funksion agregat COUNT() ose SUM() per te kalkuluar nje vlere te vetme.

ExecuteReader() Ekzekuton nje SELECT query dhe kthen nje objekt DataReader qe krijon nje kursor read-only, forward-only.

 

DataReader

Read() E con kursorin ne rreshtin tjeter te informacionit ne stream. Kjo duhet te therritet perpara leximit te rreshtit te pare te te dhenave. Metoda Read() kthen true nese ka nje rresht per te lexuar ose false ne te kundert.

GetValue() Kthen vleren e vendosur ne fushen e specifikuar me ane te indeksit, ne rreshtin aktual. Tipi i vleres se kthyer eshte tipi i ngjashem qe ofron .NET per ate tip te dhenash ne datasource. Nese i kalojme si parameter nje indeks i cili eshte jashte kufijve te fushave ekzistuese atehere do te marim nje exception IndexOutOfRangeException. Mund te aksesohen te dhenat me ane te emrit te fushes. Qe do te thote se myDataReader.GetValue(0) dhe myDataReader["NameOfFirstField"] jane ekuivalente. Percaktimi me ane te fushes eshte me i lexueshem por me pak eficient.

GetValues() Saves the values of the current row into an array. The number of fields that are saved depends on the size of the array you pass to this method. You can use the DataReader.FieldCount property to determine the number of fields in a row, and you can use that infor-mation to create an array of the right size if you want to save all the fields.

GetInt32(),GetChar(),GetDateTime(), Get Xxx ()

These methods return the value of the field with the specified index in the current row, with the data type specified in the method name. Note that if you try to assign the returned value to a variable of the wrong type, you’ll get an InvalidCastException exception. Also note that these methods don’t support nullable data types. If a field might contain a null value, you need to check it before you call one of these methods. To test for a null value, compare the unconverted value (which you can retrieve by position using the GetValue() method or by name using the DataReader indexer) to the constant DBNull.Value.

NextResult() If the command that generated the DataReader returned more than one rowset, this method moves the pointer to the next rowset (just before the first row).

Close() Closes the reader. If the originator command ran a stored procedure that returned an output value, that  value can be read only from the respective parameter after the reader has been closed.

 

Nje komande mund te ktheje me teper se nje result set ne dy raste:

-          Ekzekuton nje stored procedure

-          Nese perdorim komande text te thjeshte me disa select te ndara me “;”

 

SQL INJECTION ATTACKS

Shembull:

WHERE Orders.CustomerID = 'ALFKI' OR '1'='1'

            ---------------------------------------

            ALFKI'; DELETE  FROM Customers

 

string ID = txtID.Text().Replace(" ' ", " ' ' ");

 

Stored Procedurat

 

Stored procedures kane disa benefite:

·         Jane te lehta per tu mirembajtur

·         Na lejojne te impementojme nje perdorim me te sigurt te bazes se te dhenave

·         Risin performancen

 

Transaksionet 

A transaction is a set of operations that must either succeed or fail as a unit. The goal of a transaction is to ensure that data is always in a valid, consistent state.

When implementing a transaction, you can follow these practices to achieve the best results:

• Keep transactions as short as possible.

• Avoid returning data with a SELECT query in the middle of a transaction. Ideally, you should return the data before the transaction starts. This reduces the amount of data your transaction will lock.

• If you do retrieve records, fetch only the rows that are required so as to reduce the number of locks.

• Wherever possible, write transactions within stored procedures instead of using ADO.NET transactions. This way, your tr ansaction can be started and completed more quickly, because the database server doesn’t need to communicate with the client (the web application).

• Avoid transactions that combine multiple independent batches of work. Put separate batches into separate transactions.

• Avoid updates that affect a large range of records if at all possible.

 

Shembull nje result set:

protected void Page_Load(object sender, EventArgs e)

{

string connectionString =

      WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

    SqlConnection con = new SqlConnection(connectionString);

    string sql = "SELECT * FROM Employees";

    SqlCommand cmd = new SqlCommand(sql, con);

    con.Open();

    SqlDataReader reader = cmd.ExecuteReader();

    StringBuilder htmlStr = new StringBuilder("");

    while (reader.Read())

    {

        htmlStr.Append("<li>");

        htmlStr.Append(reader["TitleOfCourtesy"]);

        htmlStr.Append(" <b>");

        htmlStr.Append(reader.GetString(1));

        htmlStr.Append("</b>, ");

        htmlStr.Append(reader.GetString(2));

        htmlStr.Append(" - employee from ");

        htmlStr.Append(reader.GetDateTime(6).ToString("d"));

        htmlStr.Append("</li>");

    }

       reader.Close();

       con.Close();

       HtmlContent.Text = htmlStr.ToString();

}

 Shembull me shume se nje result set:

StringBuilder htmlStr = new StringBuilder("");

int i = 0;

do

{

    htmlStr.Append("<h2>Rowset: ");

    htmlStr.Append(i.ToString());

    htmlStr.Append("</h2>");

    while (reader.Read())

    {

        htmlStr.Append("<li>");

        for (int field = 0; field < reader.FieldCount; field++)

        {

            htmlStr.Append(reader.GetName(field).ToString());

            htmlStr.Append(": ");

            htmlStr.Append(reader.GetValue(field).ToString());

            htmlStr.Append("&nbsp;&nbsp;&nbsp;");

        }

        htmlStr.Append("</li>");

    }

    htmlStr.Append("<br /><br />");

    i++;

} while (reader.NextResult());

reader.Close();

con.Close();

HtmlContent.Text = htmlStr.ToString();

Metoda ExecuteScalar()

SqlConnection con = new SqlConnection(connectionString);

string sql = " SELECT COUNT(*) FROM Employees ";

SqlCommand cmd = new SqlCommand(sql, con);

con.Open();

int numEmployees = (int)cmd.ExecuteScalar();

con.Close();

HtmlContent.Text += "<br />Total employees: <b>" + numEmployees.ToString() + "</b><br />";

 Metoda ExecuteNonQuery ()

SqlConnection con = new SqlConnection(connectionString);

string sql = "DELETE FROM Employees WHERE EmployeeID = " + empID.ToString();

SqlCommand cmd = new SqlCommand(sql, con);

try

{

    con.Open();

    int numAff = cmd.ExecuteNonQuery();

    HtmlContent.Text += string.Format("<br />Deleted <b>{0}</b> record(s)<br />",  numAff);

}

catch (SqlException exc)

{

    HtmlContent.Text += string.Format( "<b>Error:</b> {0}<br /><br />", exc.Message);

}

finally

{

    con.Close();

}

  

Hapi 1: Krijimi i Stored Procedurave

CREATE PROCEDURE InsertEmployee

@EmployeeID      int OUTPUT,

@FirstName       varchar(10),

@LastName        varchar(20),

@TitleOfCourtesy varchar(25)

AS

INSERT INTO Employees

  (TitleOfCourtesy, LastName, FirstName, HireDate)

  VALUES (@TitleOfCourtesy, @LastName, @FirstName, GETDATE());

SET @EmployeeID = @@IDENTITY

GO

-----------------------------------------

CREATE PROCEDURE DeleteEmployee

@EmployeeID      int

AS

DELETE FROM Employees WHERE EmployeeID = @EmployeeID

GO

-----------------------------------------

CREATE PROCEDURE UpdateEmployee

@EmployeeID      int,

@TitleOfCourtesy varchar(25),

@LastName        varchar(20),

@FirstName       varchar(10)

AS

UPDATE Employees

    SET TitleOfCourtesy = @TitleOfCourtesy,

    LastName = @LastName,

    FirstName = @FirstName

    WHERE EmployeeID = @EmployeeID

GO

------------------------------------------

CREATE PROCEDURE GetAllEmployees

AS

SELECT EmployeeID, FirstName, LastName, TitleOfCourtesy FROM Employees

GO

---------------------------------------------

CREATE PROCEDURE CountEmployees

AS

SELECT COUNT(EmployeeID) FROM Employees

GO

CREATE PROCEDURE GetEmployee

@EmployeeID      int

AS

SELECT EmployeeID, FirstName, LastName, TitleOfCourtesy FROM Employees

  WHERE EmployeeID = @EmployeeID

GO

 

Hapi 2: Klasa EmployeeDetails

 

public class EmployeeDetails

{

    private int employeeID;

    public int EmployeeID

    {

        get {return employeeID;}

        set {employeeID = value;}

    }

 

    private string firstName;

    public string FirstName

    {

        get {return firstName;}

        set {firstName = value;}

    }

 

    private string lastName;

    public string LastName

    {

        get {return lastName;}

        set {lastName = value;}

    }

 

    private string titleOfCourtesy;

    public string TitleOfCourtesy

    {

        get {return titleOfCourtesy;}

        set {titleOfCourtesy = value;}

    }

 

    public EmployeeDetails(int employeeID, string firstName, string lastName,

      string titleOfCourtesy)

    {

        EmployeeID = employeeID;

        FirstName = firstName;

        LastName = lastName;

        TitleOfCourtesy = titleOfCourtesy;

    }

} 

Hapi 3: Klasa e marrjes se te dhenave

 

public class EmployeeDB

{

    private string connectionString;

    public EmployeeDB()

    {

        // Merr connection string i cili eshte default

        connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

    }

    public EmployeeDB(string connectionString)

    {

        // Percakton si connection string ate qe merr si parameter

        this.connectionString = connectionString;

    }

    public int InsertEmployee(EmployeeDetails emp)

    {

                SqlConnection con = new SqlConnection(connectionString);

    SqlCommand cmd = new SqlCommand("InsertEmployee", con);

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));

    cmd.Parameters["@FirstName"].Value = emp.FirstName;

    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));

    cmd.Parameters["@LastName"].Value = emp.LastName;

    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy",

      SqlDbType.NVarChar, 25));

    cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;

    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));

    cmd.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;

    try

    {

        con.Open();

        cmd.ExecuteNonQuery();

        return (int)cmd.Parameters["@EmployeeID"].Value;

    }

    catch (SqlException err)

    {

        //Me ane te kodit te meposhtem percaktohet nje shenim errori me pak specifik

       //dhe me i kuptueshem

       throw new ApplicationException("Data error.");

    }

    finally

    {

        con.Close();

    }

 

    }

    public void DeleteEmployee(int employeeID)

    {

                SqlConnection con = new SqlConnection(connectionString);

    SqlCommand cmd = new SqlCommand("DeleteEmployee", con);

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));

    cmd.Parameters["@EmployeeID"].Value = employeeID;

    try

    {

        con.Open();

        cmd.ExecuteNonQuery();

    }

    catch (SqlException err)

    {

        throw new ApplicationException("Data error.");

    }

    finally

    {

        con.Close();

    }

    }

    public void UpdateEmployee(EmployeeDetails emp)

    {

                SqlConnection con = new SqlConnection(connectionString);

    SqlCommand cmd = new SqlCommand("UpdateEmployee", con);

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));

    cmd.Parameters["@FirstName"].Value = firstName;

    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));

    cmd.Parameters["@LastName"].Value = lastName;

    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar,

      25));

    cmd.Parameters["@TitleOfCourtesy"].Value = titleOfCourtesy;

    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));

    cmd.Parameters["@EmployeeID"].Value = EmployeeID;

    try

    {

        con.Open();

        cmd.ExecuteNonQuery();

    }

    catch (SqlException err)

    {

        throw new ApplicationException("Data error.");

    }

    finally

    {

        con.Close();

    }

    }

    public EmployeeDetails GetEmployee()

    {

                SqlConnection con = new SqlConnection(connectionString);

    SqlCommand cmd = new SqlCommand("GetEmployee", con);

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));

    cmd.Parameters["@EmployeeID"].Value = employeeID;

    try

    {

        con.Open();

        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

        // Kontrollon nese query ka kthyer pergjigje

        if (!reader.HasRows()) return null;

        // Get the first row.

        reader.Read();

        EmployeeDetails emp = new EmployeeDetails(

         (int)reader["EmployeeID"], (string)reader["FirstName"],

         (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);

        reader.Close();

        return emp;

    }

    catch (SqlException err)

    {

        throw new ApplicationException("Data error.");

    }

    finally

    {

        con.Close();

    }

    }

    public List<EmployeeDetails> GetEmployees()

    {

                SqlConnection con = new SqlConnection(connectionString);

    SqlCommand cmd = new SqlCommand("GetAllEmployees", con);

    cmd.CommandType = CommandType.StoredProcedure;

    // Krijon nje koleksion per te gjithe rreshtat e employee

    List<EmployeeDetails> employees = new List<EmployeeDetails>();

    try

    {

        con.Open();

        SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())

        {

            EmployeeDetails emp = new EmployeeDetails(

            (int)reader["EmployeeID"], (string)reader["FirstName"],

            (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);

            employees.Add(emp);

        }

        reader.Close();

        return employees;

    }

    catch (SqlException err)

    {

        throw new ApplicationException("Data error.");

    }

    finally

    {

        con.Close();

    }

    }

    public int CountEmployees()

    {

                SqlConnection con = new SqlConnection(connectionString);

    SqlCommand cmd = new SqlCommand("CountEmployees", con);

    cmd.CommandType = CommandType.StoredProcedure;

    try

    {

        con.Open();

        return (int)cmd.ExecuteScalar();

    }

    catch (SqlException err)

    {

        throw new ApplicationException("Data error.");

    }

    finally

    {

        con.Close();

    }

    } 

}

 

Hapi 1: WebForm1.aspx

<%@ Page Language="C#" CodeBehind="WebForm1.aspx.cs" Inherits="B1.WebForm1" %>

<!DOCTYPE html>

<html>

<head runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:GridView ID="GridView1" runat="server">           

        </asp:GridView>

    </div>

    </form>

</body>

</html>

 

Hapi 2: WebForm1.aspx.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Data;

 

namespace B1

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            string constring = "Data Source=GENA-PC;Initial Catalog=Northwind;User Id=sa;Password=12";

            SqlConnection conn = new SqlConnection(constring);

            string sql_query = "SELECT top(10) orderID, unitprice  FROM [dbo].[order details]";

            SqlCommand cmd = new SqlCommand(sql_query, conn);

            conn.Open();

            DataSet ds = new DataSet();

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            da.Fill(ds);

            cmd.ExecuteNonQuery();

            GridView1.DataSource = ds;

            GridView1.DataBind();

            conn.Close();          

        }

    }

}

 

Hapi 2: WebForm2.aspx

<asp:gridview ID="GridView2" runat="server" AutoGenerateColumns="False"

            DataSourceID="SqlGW" style="margin-top: 0px">

        <Columns>

            <asp:BoundField DataField="orderID" HeaderText="Id" />

            <asp:BoundField DataField="unitprice" HeaderText="Cmim" />

            <asp:BoundField DataField="quantity" HeaderText="Sasi" />

        </Columns>

        </asp:gridview>

    <asp:SqlDataSource

            ID="SqlGW" runat="server"

            ConnectionString="Data Source=GENA-PC;Initial Catalog=Northwind;User Id=sa;Password=12"            

            SelectCommand="SELECT top(10) orderID, unitprice,quantity  FROM [dbo].[order details]">

    </asp:SqlDataSource>


Ċ
Leogena Zhaka,
Dec 8, 2014, 5:56 AM
Ċ
Leogena Zhaka,
Dec 8, 2014, 5:20 AM
ċ
Northwind.bak.zip
(524k)
Leogena Zhaka,
Dec 8, 2014, 5:23 AM