Mostrando postagens com marcador English. Mostrar todas as postagens
Mostrando postagens com marcador English. Mostrar todas as postagens

sábado, 23 de maio de 2009

Cursor with parameters in PL/SQL

Ver este post em Portugûes

Here I'm gonna show another technique to work with cursors in PL/SQL. I had to do something at work where it was very useful: cursor that take arguments.

The biggest advantages of parametrized queries is that the server doesn't have to pass through the parse and plan phases ever again (after the first time, of course) and the compile-time checking for syntax errors (when we use string queries we only know any syntax errors in runtime).

That said let's go to examples:


cursor ProductCursor(pname in varchar2) is
select *
from product
where name like pname;


Nice, isn't it? It's like a procedure in terms of syntax. And opening the cursor is like this:


open ProductCursor('A%');


It'll open the cursor with all the products which name starts with 'A'.

We can create a rowtype based var from this cursor:


prod ProductCursor%rowtype;


Now I'm gonna join it all in a script:


declare
-- Cursor
cursor ProductCursor(pname in varchar2) is
select *
from product
where name like pname;

-- Simple loop
prod ProductCursor%rowtype;

-- bulk collect loop
type Tprods is table of ProductCursor%rowtype;
prods Tprods;
i integer;

begin
-- simple loop
open ProductCursor('A%');
loop
fetch ProductCursor into prod;
exit when ProductCursor%notfound;
DBMS_OUTPUT.put_line(prod.nome);
-- other commands
end loop;
close ProductCursor;

-- bulk collect loop
open ProductCursor('A%');
fetch ProductCursor bulk collect into prods;
for i in prods.first .. prods.last loop
DBMS_OUTPUT.put_line(prods(i).nome);
-- another commands using prods(i)
end loop;
close ProductCursor;
end;


Cool, isn't it? See you next time.

Artigo completo (View Full Post)

Generics - A great addition to .Net

Ver este post em Portugûes

Hello. In this post I'll show something very nice about .Net: Generics.

First, I'll show a simple struct in order to explain the feature:


struct Person {
public string Name;
public string Address;
public string Zip;
}


We have here a classic example of a data struct. Here we define fields with their respective data types (we're talking here about a strongly typed language). That means we have to provide the data type of the attributes at compile time. But, what if we could let the (struct's) user define what type s/he wants to use in their struct? How so? e.g.: A struct, two fields and their data type is open to the user to specify and set the attribute, making it "appear" a weakly typed language, but with the advantage of type checking at compile time.

Let's take a look at an example:


struct Pair<TClass1, TClass2>
where TClass1 : class
where TClass2 : class {

public TClass1 obj1;
public TClass2 obj2;
}


Here comes the generics: We define a struct, asking the user to provide which clases will will represent the TClass1 and TClass2, which we are defining that have to inherit the class object and it automatically defines what data type that fields inside will accept

The nice part is when we start to use this structure. Let's see another code block:


class Customer {
public int ID;
public string Name;

public override string ToString() {
return string.Format("Customer ID = {0}, Name = {1}", ID, Name);
}
}

class Product {
public int ID;
public string Name;

public override string ToString() {
return string.Format("Product ID = {0}, Name = {1}", ID, Name);
}
}

struct Pair<TClass1, TClass2>
where TClass1 : class
where TClass2 : class {

public TClass1 obj1;
public TClass2 obj2;
}

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

var c = new Customer { ID = 1, Name = "Felipe" };
var p = new Product { ID = 1, Name = "Caneta BIC" };

var pair = new Pair<Customer, Product>() { obj1 = c, obj2 = p };

Console.WriteLine(pair.obj1.ToString());
Console.WriteLine(pair.obj2.ToString());

}
}


The braces after the new instanct are a new notation of C# that allows us to create new instances of classes and set values to public fields in the same instruction.

In the progem we create instances of Customer and Product and define these types as the relevant types for the struct Pair. Note that, after we define the struct with these types we can only inform instances of these types for the specific fields. The compiler doens't let us inform any other value; and even the IntelliSense indicates correctly.

I hope you all liked this post and again, sorry for my english, as I am no native English speaker. Thank you very much and see you next time.

Artigo completo (View Full Post)

segunda-feira, 23 de junho de 2008

Linq to SQL - Working with Databases

Clique aqui para ver a versão em português deste post

In this post I continue to show the Linq technology. This tiime we'll see how it's used to access and manipulate data in relational database tables.

In order to use Linq with databases we need to establish a relationship between the business objects in our software and the database tables. To achieve this we use the class/attributes mapping.

This mapping is simple: We create a class that represent a database table and in this class we set which table it is mapped to. In the attributes we set to which database fields they're mapped.

In the current version, Linq only supports the MS SQL Server, but there are already many developers out there working on solutions to cover other RDBMSs too.

To begin let's show a table in the database: Produtos

CREATE TABLE [dbo].[Produtos](
[ProdutoID] [uniqueidentifier] ROWGUIDCOL
NOT NULL CONSTRAINT [DF_Produtos_ProdutoID] DEFAULT (newid()),
[Descricao] [nvarchar](100) COLLATE Latin1_General_CI_AS NOT NULL,
[Preco] [decimal](10, 2) NOT NULL,
[Saldo] [int] NOT NULL,
[Versao] [timestamp] NOT NULL
)


Let's pay close attention at the column Versao, type timestamp. This datatype stores the record version. This is very useful to work with optimistic updates and concurrency issues and Linq makes good use of it.

Before we advance, insert some records in this table.

Now let's create a class that uses this table. In order to use Linq to SQL we need to add reference to the assembly System.Data.Linq to the project. Here is the code:

using System;
using System.Data.Linq.Mapping;

[Table(Name="Produtos")]
class Produto {

[Column(IsPrimaryKey = true,
IsDbGenerated = true,
AutoSync = AutoSync.OnInsert)]
public Guid ProdutoID { get; set; }

[Column(CanBeNull = false)]
public string Descricao { get; set; }

[Column(CanBeNull = false)]
public decimal Preco { get; set; }

[Column(CanBeNull = false)]
public int Saldo { get; set; }

[Column(IsDbGenerated = true,
IsVersion = true, AutoSync = AutoSync.Always)]
public System.Data.Linq.Binary Versao { get; set; }
}


Now let's create a Linq query in this database table. Up until here we just defined the entity and attributes mapping, but we didn't define anything about the database itself. How do we do that?

There is an object in Linq called DataContext. We can think of it more like a database connection in other technologies, but it hardly acts like. It does so much more! It's responsible for the database connection, making and the exectution of the queries, mapping, record updates and even transactions and record concurrency!

We just need to create a instance and it does everything on its own. In other words, we don't need to make code to open or close connection, build string queries and treat results anymore. Let's see the code:

using System;
using System.Data.Linq;
using System.Linq;

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

var connStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=DadosSql;User ID=felipe;Password=123";

using (var db = new DataContext(connStr)) {
var Produtos = db.GetTable<Produto>();

var qry =
from p in Produtos
orderby p.Descricao
select p;

foreach (var prod in qry) {
Console.WriteLine("{0}, {1}, {2}", prod.Descricao,
prod.Preco,
prod.Saldo);
}
}
}
}


What we've done:

- Created a DataContext to connect to the database;
- Created a local variable to represent our mapped table;
- Built the query;
- Display the results;

The wonder of wonders is: Will the DataContext retrieve the entire Produtos table when we create the local variable Produtos? Answer is no! Then, will it occur when we create the qry? Nope. So, where is it?

One of the coolest things in Linq is the ability to send the query to the database only at the moment its data is actually required. In our case, in the foreach loop. That query returned an instance of IOrderedQueryable that seems like a List. But it isn't quite a common list. When its data is required the DataContext build a query based on its definition and send the query to the database and then convert the resultset in an object list, thanks to the mapping features. This technique is called Deferred execution.

How would we do in order to open connection, build a parametrized query and convert the resultset in a list of instances of our business object? And all other tables?

I'll change the code a bit and add some verbose. The DataContext has a property called Log that allows us to view the DataContext's log:

using System;
using System.Data.Linq;
using System.Linq;

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

var connStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=DadosSql;User ID=felipe;Password=123";

using (var db = new DataContext(connStr)) {
db.Log = Console.Out;

Console.WriteLine("** Pegando a tabela Produtos");
var Produtos = db.GetTable<Produto>();

Console.WriteLine("** Criando a query");
var qry =
from p in Produtos
where p.Descricao.Contains("a")
orderby p.Descricao
select p;

Console.WriteLine("** Começando a iteração");
foreach (var prod in qry) {
Console.WriteLine("{0}, {1}, {2}", prod.Descricao,
prod.Preco,
prod.Saldo);
}
}
}
}


Here's the console output:

** Pegando a tabela Produtos
** Criando a query
** Começando a iteração
SELECT [t0].[ProdutoID], [t0].[Descricao], [t0].[Preco], [t0].[Saldo], [t0].[Ver
sao]
FROM [Produtos] AS [t0]
WHERE [t0].[Descricao] LIKE @p0
ORDER BY [t0].[Descricao]
-- @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%a%]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Caderno Universitário 500 folhas, 7,90, 50
Caneta BIC, 1,99, 25
Lapiseira 0,7mm, 5,00, 10
Pasta polionda A4, 2,40, 6


The DataContext produces a query with parameters, instead of concatenate the criteria and build one simple query string. This is very nice, because the RDBMS can make an execution plan for this query and use the same play every time we run this query, even with different parameter values. It means better performance.

Well, that's it for this post, but Linq to SQL goes way further than this. In the next issue I'll write about CRUD operations with Linq to SQL. Thnnk you and take care.

Artigo completo (View Full Post)

terça-feira, 27 de maio de 2008

Linq to Objects - Part 2

Clique aqui para ler este post em Português

Hello everyone. I'll continue to talk about Linq to Objects in this post and show some other features present in this technology I have found very nice. I really hope you all like it as I continue to show the things I have seen and learned. Let's get to it.

In the previous posts I've shown the Linq query syntax, but there's another way to query data with Linq. All of the statements (in fact even more than that) we see in the query syntax can be represented by Collections' special method calls. They're called query expressions. Let's take a look at the previous example, this time using the method calls:

var Meses = new List<string> {
"Janeiro",
"Fevereiro",
"Março",
"Abril",
"Maio",
"Junho",
"Julho",
"Agosto",
"Setembro",
"Outubro",
"Novembro",
"Dezembro"
};

var MesesComJ =
Meses
.Where(mes => mes.StartsWith("J"))
.OrderByDescending(mes => mes)
.Select(mes => mes);

foreach (var m in MesesComJ) {
Console.WriteLine(m);
}


This form, imho, is not as cool as the another one, but it's also nice and shows us clearly what the code is going to get too.

Working with simple lists the select call is optional, but only in query expressions - in query syntax is still mandatory.

Hey, what is that weird operator over there? What, the =>? This is the operator we use for Lambda Expressions. Oh, ok... But, what are Lambda Expressions?

Some object methods take a function definition as an argument instead of a fixed value. Let's see the Where line again:

.Where(mes => mes.StartsWith("J"))


Let's note that there's only one argument passed to the method. When the Where method do the full iteration over the list it'll use the funcion defined in this argument to decide if the item at the given position will return in the result list or not.

For that we define the function right there, on the fly, creating the return condition for the items. But to create a dynamic filter we need a context. And here comes the "goes to" operator. It's used to point out what is the context in the function we're defining. This condition is going to be tested for each item in the main list, where mes is the current item. Use of this kind of expression is common in functional languages. The variable name before the operator is defined by us, and the code on the right side have to use the same name.

There are many query expressions in Linq; and even if I knew all of them this post would take ages! Let's see here other two interesting ones: Skip and Take.

There's no representation for Skip and Take in the query syntax in C#, only via method calls. Well, the Skip "Skips" a given number of items and the Take "Takes" a given number of items of the result.

var Meses456 =
Meses
.Skip(3)
.Take(3);


This'll produce the result:

Abril
Maio
Junho


The order of the methods call already gives us a tip of what's goind to happen: "Skip 3 and take the 3 next".

We can combine the query syntax with query expressions too:

var MesesCom5Letras = (
from mes in Meses
where mes.Length == 5
orderby mes descending
select mes)
.Skip(1)
.Take(2);


Resulting:

Junho
Julho


As I'm lerning new stuff I post here for you guys. Until there practice a bit and post your experiences here too. Thank you for the support and take care.

Artigo completo (View Full Post)

segunda-feira, 26 de maio de 2008

Linq to Objects - Querying in-memory collections

Clique aqui para ver a versão em português deste post

Hello everyone. This post's issue continues about the Linq technology, but this we'll see a specific segment: Linq to Objects.

Before we begin, I want to recommend the book about Linq that I'm reading now: Linq in Action, from the authors Fabrice Marguerie, Steve Eichert, Jim Wooley, published by Manning. It's indeed a great book and will cover not only Linq, but C# features and best practices as well.

Let's get back to Linq to Objects. This technology allows us to query in-memory data collections. Instances of type array, List and others that implement the IEnumerable interface can be used with Linq to Objects. You only need to add a using entry to the namespace System.Linq.

Linq to Objects is particularly useful when we need to query, filter and/or order an in-memory data collection. These tasks when done without Linq (traditional ways) require quite a lot of code that uses loops and temporary variables. Another advantage on using Linq is that the code is easier to read, maintain and debug, as the code clearly shows what we'll get as result. Look at the example:

string[] Months = {"January",
"February",
"March",
"April",
"May",
"June",
"July",
"August",
"September",
"October",
"November",
"December"};

var MonthsWithJ =
from mon in Months
where mon.StartsWith("J")
orderby mon descending
select mon;

foreach (var m in MonthsWithJ) {
Console.WriteLine(m);
}


The result will be:

June
July
January


How would we produce the same result without Linq? If you want to have some fun and practice some algorithms, suit yourself :)

Now, let's say if, instead of an array of strings we could query an Object list? Yeah, it's possible:

class Program {

class Person {
public string FirstName { get; set; }
public string LastName { get; set; }
}

static void Main(string[] args) {

var Felipe = new Person { FirstName = "Felipe", LastName = "Guerço" };
var Gerson = new Person { FirstName = "Gerson", LastName = "Motta" };

var People = new List<Person> {Gerson, Felipe};

var qry =
from person in People
orderby person.FirstName
select person;

foreach (var p in qry) {
Console.WriteLine("{0} {1}", p.FirstName, p.LastName);
}
}
}


Now we'll take a little break on Linq to show some of the new C# features for those that doesn't know them yet. First, auto properties:

public string FirstName { get; set; }


This implementation creates the property and, behind the scenes create the encapsulated field to store the property value. Very usefil for those properties that are read and written directly in the fields and don't need any specific validation logic behind them. But, at same time, provide structure to future changes, as the get and set are already there.

Next, type inference

var Felipe = new Person { FirstName = "Felipe", LastName = "Guerço" };


In early versions we had to define the variable type before initialize it. With this new feature the compiler infers the type in the first value assignment. Make no mistake: the type definition is still strong (not variant); we just don't need to explicitly inform the type because the compiler knows it from the assignment. To define a variable without initialize we do need to inform the type.

At last, a special constructor:

var Felipe = new Person { FirstName = "Felipe", LastName = "Guerço" };


This constructor allows us to create an instance of a class setting the properties rigth away.

Now Let's get back to Linq. Here we'll query all people on the list sorting by FirstName. If we wanted, we could add a search criteria by Name adding a where clause:

where person.FirstName.Contains("xxx")


Cool, isn't it? But there's more! We can query related data in more complex collections:


class Program {

class Person {
public string FirstName { get; set; }
public string LastName { get; set; }
}

class Pet {
public string Name { get; set; }
public Person Owner { get; set; }
}

static void Main(string[] args) {

var Felipe = new Person { FirstName = "Felipe", LastName = "Guerço" };
var Gerson = new Person { FirstName = "Gerson", LastName = "Motta" };

var Meg = new Pet { Name = "Meg", Owner = Felipe };
var Rex = new Pet { Name = "Rex", Owner = Gerson };
var Max = new Pet { Name = "Max", Owner = Gerson };
var Nina = new Pet { Name = "Nina", Owner = Felipe };

var Pets = new List<Pet> { Meg, Rex, Max, Nina };

var qry =
from pet in Pets
orderby pet.Owner.FirstName
select new {
OwnerName = pet.Owner.FirstName + ' ' + pet.Owner.LastName,
PetName = pet.Name
};

foreach (var p in qry) {
Console.WriteLine("{0} {1}", p.OwnerName, p.PetName);
}

}
}


Now let's see another new interesting feature: Anonymous types.

select new {
OwnerName = pet.Owner.FirstName + ' ' + pet.Owner.LastName,
PetName = pet.Name
};


Here we define a new type (anonymous because it's defined at same time that we set the properties). Now we have an object with properties named OwnerName and PetName. The compiler automatically infers the properties' types according with their initialization.

Now we're goind to do an aggregation. We'll show the owner and how many pets they have:

var qry2 =
from pet in Pets
group pet by pet.Owner.FirstName + ' ' + pet.Owner.LastName into ownerPets
select new {
Name = ownerPets.Key,
Pets = ownerPets.Count()
};

foreach (var p in qry2) {
Console.WriteLine("{0}'s Pets: {1}", p.Name, p.Pets);
}


Now we created a group in the query counting pets with a key being the Owner's name. In this group we have a property named Key, which represents the information we're grouping. Then we project the group key and the group count in the resultset.

Before we finish, let me point out something: We can use these query results as data sources for data-aware controls, such as GridViews on WinForms or WebForms.

Well, it ended up more lengthy than I expected, but i think that it could show some of the power and usefulness of Linq to Objects technique. Thank you all for the support and see you at the next post. Take care!

Artigo completo (View Full Post)

Data access with Linq

Clique aqui para ver este post em português

Hello everyone. This is the first post in Estação ZN in English. We decided to give an "English version" to our posts due to the increasing non-Brazilian public of our blog, which we would like say thank you very much. From now on I'll try to make an English translation of every post i make. I ain't a native English speaker, so please bear with me a little bit. :)

Let's get started. In this post I'm bringing an issue that left me and Gerson speechless: A new data access technology present in Visual Studio 2008 and .NET 3.5.

Linq stands for Language-integrated Query. It's a technology (a few of them, in fact) that adds query capabilities at language (compiler) level.

What does it mean? It means that Linq allows us (amongst others things) to query in-memory data, XML files and even relational database tables in a declarative manner (like funcional languages) instead of the traditional, imperative way we're used to.

Linq also came to propose a solution for the "impedance mismatch", that is the difference between the program's object model (OOP) and the relational databases/XML files data model. Going a bit deeper: Let's begin with the principle: Relational data <> XML files <> Objects hierarchy/relationships. At the software we have the OOP model, with inheritance, polimorphism and so on. The data our software usually accesses are represented in relational database tables and XML files. In these worlds we have no compatibility whatsoever; even the data types are different.
In order to work with data nowadays we need to use APIs that the languages provide us. But we always have to write more code than we should, and the code that we write to access the data doesn't clearly represent what we want to do. That is because of the imperative way that the languages require to access and manipulate data on external sources.

Eh..., ..., ... Ok. Here's an example:

We want to query data from a database. There are minor differences between the languages but it all comes down to one path in most cases:


  • 1. Open connection with the database

  • 2. Instantiate an object to execute the query

  • 3. Pass the query as string

  • 4. Send the query to the database and get the resultset

  • 5. Release the resources and uses the resultset



This model comes with the necessity of implement, in a imperative way, what the program has to do at each moment. We also have to write code to explicitly connect to the database and run the query, which isn't part of our program logic, and at last, but not least, we have to code the query as string, not using the compiler's type checking at design time - if our query has any errors, we'll discover it only at run time. Then, when the application retrieve the data from the database we have to manually "translate" the database's resultset into objects. It takes too many "unnecessary" code (I would say code that doesn't belong there) and produces code that is difficult to read, mantain and debug.

But how's Linq different? First, let's see the several "flavors" of Linq. We'll see the Linq to Objects first, which allows us to query, filter and sort in-memory collections. Let's go on a example:

string[] Months = {
"January",
"February",
"March",
"April",
"May",
"June",
"July",
"August",
"September",
"October",
"November",
"December"};

var MonthsWithA =
from mon in Months
where mon.StartsWith("A")
orderby mon descending
select mon;

foreach (var m in MonthsWithA) {
Console.WriteLine(m);
}


Take a look at the part where we query the string array Months. In this fragment we search the Months array for any month that starts with the letter A and sort the results descending. This way we access the data in a declarative way, like we would do with funcional languages. Imagine how would be the code to achieve the same results in the traditional, imperative way...

In Linq to Objects we can query any object that implements the IEnumerable interface (the array implenents this interface even though it doens't look like).

This querying model can be applied in relational data and XML files too. But how? Let's see the relational database first - We won't dive too deep into it for now because this post's objective is to show what is Linq and what it is capable of.

With Linq to SQL we can query the database in the same declarative way we do on object collections. But Felipe, if I have to retrieve the entire table from the database and convert it into an array before I can use the Linq, then I surely don't want it! For sure Microsoft wouldn't do it with a technology that is intended to make the developers' life easier. Linq to SQL works with another new language enhancement: Entity Mapping.

In Linq to SQL we have the classes in our program decorated with the special Mapping attributes (I'll talk about and show it in another posts). In other words, we'll "mark" our classes as "Entity classes" and inform which tables and fields are related to which classes and properties; and then, when we query these objects, Linq "mounts" the query and retrieve the results into the objects - all thanks to the mapping attributes.

Linq to SQL can do way more than this. It also provides APIs for data updates, transactions and concurrency control. We'll see more of that later.

Linq to XML provides a simple and unified API to query and also create/edit XML files; an API that is way simpler than those we know nowadays (DOM, XSL, XPath, XQuery). The query syntax is very alike to the Linq to Objects, and this is very nice to us developers to get used to quickly.

As the goal of this post being talk about a relatively new technology and have a peek into all this, I'll let the specific "flavors" of Linq to other posts. See you all and have fun.

Thank you for spending time with us, and please excuse me about any English errors, as i'm no native English speaker, and feel free to send any suggestions and even corrections (please correct me!). Bye...

Artigo completo (View Full Post)

 
BlogBlogs.Com.Br