Category Archives: SQL

Developing Windows Store App Part 1: Database Model


This is the first of several blog posts about developing Windows 8 Store app. The first blog post will show how to implement database model which will be used in our application. If you want to get more information about the application we are developing please see the previous announcment post. Before we start with the tutorial, several requirements must be satisfied:

1. You need to have installed at least Visual Studio 2012 Express

2. You need to have at least SQL Server 2008 or 2012 Express.

Our database model is very simple. We have three tables: Places, Dishes and Towns. Relation of those tables shows the picture below.

SQL database will also be provided by the end of the blog post. Now that we have database model on the paper, we can start with implementation. We will use Entity Framework 5, and Code First technology in order to implement db model.
Start Visual Studio 2012, choose File->New->Project. Choose ClassLibrary similar as picture below.

After we created the empty Class Library project, we can start with implementation of the entities. According to db model shoed earlier, we are going to create three .NET classes: Dish, Town, Place. In order to do that:

  • Right Click at the Project
  • Add New Class
  • Give name: Dish

Repeat the process for Town and Place. Text below shows the implementation of classes.
Town class:

public partial class Town
{
public int? TownID { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public string Image { get; set; }
//relation 0-oo
//many places can be in one Town
public ICollection Places { get; set; }
}

Dish class:

public partial class Dish
{
    public int? DishID { get; set; }
    public string Name { get; set; }
    public string Slogan { get; set; }
    public string Description { get; set; }
    public string Image { get; set; }
    //relation 0-oo
    //many places can have one dish
    public ICollection<Place> Places { get; set; }
}

Place class:

public partial class Place
{
    public int? PlaceID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public int? DishID { get; set; }
    public string Slogan { get; set; }
    public string Type { get; set; }
    public int? TownID { get; set; }
    public string Image { get; set; }
    //one relation to Dish
    public Dish PlaceDish { get; set; }
    //one relation to Dish
    public Town PlaceTown { get; set; }
}

Since we are using Code First, we need to specify the relation between entities too. That’s why we have put the last two properties in Place class, and also IColletion property of the previos classes. This is enough information that Code First make relation between tables on SQL Server.  By default PlaceID is primary key for Place table, as well as DishId and TownID for corresponded tables.
Now that we have entities, we need to implement DBContex, central class for handling all operation and transaction against database. Before we implement DBContext we need to add Entity Framework reference. Since EF is separated from the .NET we can use NuGet to accomplish this.
1. Right click on Reference, choose Manage NuGet package

In Search box type: Entity Framework and hit enter. Package will appear in the list. Click Install button. Picture below shows Manage NuGet Package dialog. Accept license agreement, and close the dialog.

CExplorer DataContext class:

public class CExplorerContext: DbContext
{
    public CExplorerContext() : base("cexplorer_db")
    {
        Configuration.LazyLoadingEnabled = false;
    }

    //entity sets
    public DbSet<Place> Places { get; set; }
    public DbSet<Dish>  Dishes  { get; set; }
    public DbSet<Town>  Towns  { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //crating one to many relation between Town and Place
        modelBuilder.Entity<Place>()
            .HasRequired(x => x.PlaceTown)
            .WithMany(d => d.Places)
            .WillCascadeOnDelete(true);

        ////crating one to many relation between Dish and Place
        modelBuilder.Entity<Place>()
            .HasRequired(x => x.PlaceDish)
            .WithMany(d=>d.Places)
            .WillCascadeOnDelete(true);

        base.OnModelCreating(modelBuilder);
    }
}

As you can see, we have overridden ModelCreation virtual method and implement relation between entities. This is classic one to many relation.

At the end of the implementation, we need to provide connection string to SQL Server, it must be with the same name as we specified in the implementation od the DBContext.

1. Add New Item: App.config file and put the following configuration.

Based on SQL instance name, change the connection string. Database could not exist on the SQL server. If there is a database on the SQL with the same name, EF will add table in to existing database.

Before we end the today’s post, lets quickly create Console Test application to make sure we implemented correct model.

1. File->New->Add New Project
2. Console Application

Like previous add Entity Framework by NuGet.
Add reference of EFModel.
Open Program.cs and put the following implementation:

class Program
{
    static void Main(string[] args)
    {

        CExplorerContext ctx = new CExplorerContext();

        var dish = new Dish() { DishID = 1, Description = "Description", Name = "DishName", Slogan = "Slogan", Image = "Default.jpg" };
        var town= new Town(){TownID=1, Name= "TownName", Description="Description", Image=""};
        ctx.Dishes.Add(dish);
        ctx.Towns.Add(town);
        ctx.SaveChanges();
        var place = new Place() { PlaceID = 1, Name = "Place Name", Description = "DEscription", Image = "default.jpg", Slogan = "Slogan title", DishID = 1, TownID = 1 };
        ctx.Places.Add(place);
        ctx.SaveChanges();

        var plc = ctx.Places.FirstOrDefault();
        Console.WriteLine("Place from Database");
        Console.WriteLine("Place from database Name:{0}", plc.Name);
        Console.Read();

    }
}

After we run, output console shows that the entities are inserted successively on database.

In other words Cexplorer_DB database is created on the SQL server, and entities are inserted from the test.

Code First approach is very powerful, we only need to specified connection string, and everything is created just like we do, in Model First approach.  Complete source code for this Demo can be found here.

Prenos tabele kao argument storne procedure


Na koji način prenijeti tabelu sa podacima u stornu proceduru kao argument?!

Na ovo pitanje i nije baš lako odgovoriti jer se tabela konvencionalno ne može prenijeti kao argument u stornu proceduru. U tom smislu rješenja koja se nude su različita, i kreću se od toga da se tabela eksportuje u tekst, koji je odvojen karakterom za odvajanje, pa se onda kao varchar prenese u stornu proceduru. Poslije toga je potrebno taj isti text vratiti u privremenu tabelu te onda je koristiti. Ovaj postupak pored toga što je potrebno implementirati logiku vraćanja teksta u tabelu, ima mane limitiranja broja karaktera.

SQL Server 2005 posjeduje XML  data tip, koji pored mnogih korisnih prednosti ima mogućnost da se definiše kao argument u stornoj proceduri. Na ovaj način moguće je tabelu izvesti u xml i na taj način prenijeti podatke. Obzirom da je manipulacija sa xml-om podržana u SQL koristeći se standardnim operacijama i naredbama.

Kao ilustraciju navedenog problema prikazaćemo mali tutorijal na koji način to možemo postići. U biti formiraćemo .NET aplikaciju u VS 2008, formirati Typed DataSet u kojoj ćemo mapirati jednu tabelu koju treba prenijeti u stornu proceduru, te mapirati stornu proceduru koju smo formirali na sql serveru koja prima XML tip i vraća tabelu koja je prenešena XML u SP.

1. Otvorimo VS 2008, i formirajmo .NET aplikaciju kao na slici:

clip_image002

2. Nakon formiranja aplikacije formirajmo TypedDataSet i nazovimo ga DataSet1.xsd

clip_image004

Napomena: Kada formirate DatSet, potrebno je obrisati NameSpace: http://tempuri.org/DataSet2.xsd

image

3. U DataSet1 formirajmo DataTable i nazovimo je „Tabela“.

Napomena: Ako pratite tutorijal vrlo je važno da se imenuje DataSet i DataTable istim imenom kao i ovdje, jer SQL kod koji ćemo kasnije formiati bazira se na ovim imenima.

4. U DataSet1 formiramo tabelu bez Adaptera kao na sljedećoj slici:

clip_image006

clip_image008

5. Kada smo formirali tabelu i definisali tipove podataka koje treba da sadrži, sad smo u mogućnosti da napravimo stornu proceduru koja će kao argument prihvatati našu tabelu.

6. Otvorimo NorthWind bazu preko ServerExplorera, u koliko nemate NorthWind bazu uzmite bilo koju ili je skinite sa ms stranice. Formirajne novu stornu proceduru kao na slici:

clip_image010

7. Storna procedura izgleda kao na sljedećoj slici

clip_image012

U biti procedura sadrži kod za formiranje privremene tabele, te ekstraktovanje podataka iz XML-a. Kod ekstrakta podataka potrebno je paziti na nazive DataSeta, naziva tabele, kao i naziva kolona kao što je prikazano.

8. Odabirom “Save” dugmeta zapisujemo proceduru na server. U DataSet-u sada je potrebno proceduru mapirati da bi je mogli prikazati u našoj testnoj aplikaciji.

9. Formirajmo TableAdapter

clip_image014

10. Odaberimo Postojeću proceduru na serveru

clip_image016

11. Odaberimo proceduru koju smo formirali nedavno

clip_image018

12. Na kraju u našem DataSetu imamo dvije tabele. Jedna offline (koju ćemo prenijeti u SP) i druga tabela koja je mapirala našu proceduru.

13. Otvorimo Form1.cs i odvucimo i spustimo naše formirane tabele kao na slici:

clip_image020

14. Implementirajmo Form_Load događaj, te formirajmo dugme “Napuni tabelu iz SP” preko kojeg ćemo pozvati Stornu proceduru.

clip_image022

Napomena: Na gornjoj slici smo formirali metodu za učitavanje podataka u tabelu. Na isti način smo mogli koristiti bilo koju tabelu sa servera, stim što bi je tada napunili preko tableAdaptera iz baze podataka.

15. Implementacija događaja za dugme “Napuni tabelu iz SP” ima sljedeću formu:

clip_image024

Na ovaj način smo završli tutorijal. Potrebno ga je testirati. U koliko je tutorijal pravilno sproveden nakon pokretanja aplikacije, te klikom na dugme „Napuni tabelu iz SP“ dobijamo sljedeći izgled.

clip_image026

U biti formirali smo tabelu, napunili je podacima, prosljedili je u SP, a SP nam je vratila tu istu tabelu.

Izvorni kod sa implementacijom storne procedure može se skinuti sa ovog linka.

MS SQL Server 2008 konfiguracija za remote connection


Zadnji dana bilo mi je potrebno da podesim SQL Server 2008 za konekciju sa drugog računara u mreži. SQL Server 2005 sam bezbroj puta podesio za remote connection koristeći konfiguracijski alat „SQL Server Surface Area Configuration“. Medjutim, u novoj verziji SQL Servera 2008 ovaj alat ne postoji, a podešavanje za remote connection za SQL Sever 2008 na prvi pogled mi je predstavljao problem, obzirom da nisam profesionalac u administriranju SQL Servera. Prije nego što pojasnim konfiguraciju SQL Servera 2008 za remote connection, prikazat ću kako se to radi standardno za SQL Server 2005.

Svaka instalacija SQL Servera (2005 ili 2008) po dafaultu je podešena samo za lokalnu konekciju. To znači da bez prethodnog podešavanja nije moguće pristupiti bazi podataka sa nekog drugog računara u mreži. Ovo je čisto iz sigurnosnih razloga. Da bi omogućili povezivanje na SQL Server sa drugog računara potrebno je na računaru na kojem se instaliran SQL Server podesiti nekoliko stvari i to:

1. Uključiti mrežne protokole za SQL Server (TCP, Named Pipes)

2. Podesiti port preko kojeg će se vršiti razmjena podataka sa SQL Serverom (ili ostaviti standardni port preko kojeg SQL Server komunicira a to je 1433)

3. U Windows Firewallu manuelno napraviti izuzetak za port 1433

Uz SQL Serveru 2005 dolazi poseban alat za konfiguraciju remote connection a to je SQL Server Surface Area Configuration.

Podešavanje Remote Connection za SQL Server 2005

clip_image002

clip_image004

Pokretanje opcije „Surface Area Configuration for Service and Connections“ dobijamo prozor za podešavanje SQL Servera za remote connection.

clip_image006

Sa lijeve strane odaberemo stavku „Remote Connections“, a sa desne strane prozora pikazuje nam se da li je Server podešen samo za lokalne konekcije odnosno Lokalno i remote connection (udaljene konekcije). Podesimo konekciju kao na slici. Kada se podesi konekcija potrebno je restartovati servis da bi promjene bile aktualizirane.

Pored konekcije potrebno je podesiti i SQL Browser. SQL Browser je servis koji obezbjedjuje konekciju na SQL Server. Naime SQL Browser omogućuje da se na jednom računaru mogu instalirati više instanci SQL Servera i da se SQL Serveru pristupa preko naziva instance. Podešavanje SQL Browsera vršimo preko stavke SQL Server Browser.

clip_image008

Sa ovim smo završili podešavanje SQL Servera 2005 za remote connection. Potrebno je sada da se u Windows Firewall-u napravi izuzetak za port 1433 koji je defaultni port za SQL Server.

Otvorimo Windows Firewall kao na slici:

clip_image009

Klikom na dugme Add Port… popunimo dijaloški okvir kao na donjoj slici te klikenmo na dugme OK.

clip_image011

Ovim je podešavanje završeno.

Podešavanje SQL Server 2008 za remote connection

Obzirom da gornji alat ne postoji za podešavanje remote connection za SQL Server 2008 podešavanje se vrši na više klasični način. Važno je napomenuti da procedura koja će biti navedena moguće je podesiti i remote connection i za SQL Server 2005. Podešavanje remote connection vršimo sa drugim alatom kmoji dolazi uz instalaciju SQL Servera 2005 i 2008 a to je SQL Server Configuration Manager.

clip_image012

Na gornjoj slici vidimo da su protokoli za remote conenction (TCP/IP i Named Pipes) isključeno. Da bi podesili remote connection potrebno je ove potokole uključiti. Da bi uključili TPC/IP protokol potrebno je desnim klikom na stavku izabrati Enable iz iskačućeg menija.

Ako želimo da podesimo port potrebno je odabrati opciju „Properties“ U stavci IPApp dijaloga unesemo broj porta.

clip_image014

Obzirom da kroz ovaj tutorijal koristimo defaultni port 1433 nije ga potrebno posebno specifirati.

Na isti način kao i kod SQL Servera 2005 potrebno je napraviti izuzetak u Windows Firewall-a za port 1433.

Na ovaj nači podešavanje SQL Servera 2008 za remote connecton je završeno.