#region Copyright
/*
* LGPL Licence
* Created by sri_canesh@users.sourceforge.net to Adp Project
* Especial thanks: Eduardo Canuto and Melitta(Capuccino dust)
*/
#endregion
using System;
using Advanced.Data.Provider;
namespace Advanced.Data.Provider.Samples{
/// <summary>
/// Essa classe mostra como utilizar o ADP
/// </summary>
public class ADPShow
{
private AdpConnection connection;
private string lastError = "";
#region Construtores
public ADPShow()
{
}
/// <summary>
/// Cria uma nova instncia da classe de demonstrao j conectando ao banco de dados;
/// </summary>
/// <param name="connStr">String de Conexo</param>
public ADPShow(string connStr)
{
this.ConnectDB(connStr);
}
#endregion
#region Conexo ao banco e seu monitoramento
/// <summary>
/// Conecta ao banco
/// </summary>
/// <param name="connStr">String de conexo</param>
/// <returns>Verdadeiro se conseguiu conectar ao banco</returns>
public bool ConnectDB(string connStr)
{
try
{
/* O ADP possui uma certa "inteligncia" para descobri quando sua string de conexo foi passada
/* com todos os parmetros
/* ex.: AdpConnection("adpprovider=MySql;host=127.0.0.1;user id=root;password=xxxx;database=employers)");
/* ou apenas o nome de uma chave que dever ser buscada no app.config
/* ex.: supondo que exista uma chave no app.config: <add key="mysql" value="adpprovider=MySql;host=127.0.0.1;user id=root;password=xxxx;database=employers" />
/* voc poder chamar essa chave simplesmente assim: AdpConnection("mysql"). O ADP cuidar do resto (desde que o app.config esteja no diretrio claro!;
*/
this.connection = new AdpConnection(connStr);
connection.Open();
// Garante retornar verdadeiro somente se a conexo estiver realmente aberta
if(IsOpen())
return true;
else
return false;
}
catch (Exception e)
{
this.lastError = e.Message;
return false;
}
}
/// <summary>
/// Retorna se a conexo est aberta ou no
/// por aberta nesse contexto entende-se qualquer estado diferente de broken ou closed
/// </summary>
/// <returns>Verdadeiro se estiver aberta</returns>
public bool IsOpen()
{
if(this.connection == null || this.connection.State == System.Data.ConnectionState.Broken || this.connection.State == System.Data.ConnectionState.Closed)
return false;
else
return true;
}
/// <summary>
/// Retorna a ltima mensagem de erro
/// </summary>
public string LastError
{
get
{
return this.lastError;
}
}
#endregion
#region Exemplos AdpCommand.ExecuteNonQuery()
/// <summary>
/// Cria tabelas nos bancos de dados
/// Exemplo de uso do mtodo AdpCommand.ExecuteNonQuery()
/// </summary>
/// <returns>Mensagem de erro ou sucesso</returns>
public string CreateTables()
{
string create = "create table adp_artists(id varchar(5), name varchar(50), record varchar(50));";
create += "create table adp_albuns(artistId varchar(5), albumName varchar(50));";
try
{
AdpCommand comm = this.connection.CreateCommand();
comm.CommandText = create;
comm.ExecuteNonQuery();
return "ok";
}
catch(Exception e)
{
return e.Message;
}
}
/// <summary>
/// Insere valores nas tabelas
/// Exemplo de uso do mtodo AdpCommand.ExecuteNonQuery()
/// </summary>
/// <returns>Mensagem de erro ou sucesso</returns>
public string Populate()
{
string insert = "";
try
{
AdpCommand comm = this.connection.CreateCommand();
// Thanks to Amazon.com ;-)
insert += "INSERT INTO adp_artists (id, name, record) VALUES ('1', 'U2', 'Mono Records'); ";
insert += "INSERT INTO adp_artists (id, name, record) VALUES ('2', 'Queen', 'Novell Discs');";
insert += "INSERT INTO adp_artists (id, name, record) VALUES ('3', 'The Doors', 'Mono Records');";
insert += "INSERT INTO adp_artists (id, name, record) VALUES ('4', 'Led Zeppelin', 'Novell Discs');";
insert += "INSERT INTO adp_artists (id, name, record) VALUES ('5', 'Frank Zappa', 'Mono Records');";
insert += "INSERT INTO adp_artists (id, name, record) VALUES ('6', 'No Doubt', 'Novell Discs');";
insert += "INSERT INTO adp_artists (id, name, record) VALUES ('7', 'Kraftwerk', 'SuSe Records Ab');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('1', 'All That You Can Leave Behind');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('1', 'Vertigo');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('1', 'The Joshua Tree');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('1', 'Pop');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('2', 'Live at Wembley Stadium ');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('2', 'The Game');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('2', 'The Works');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('3', 'Morrison Hotel ');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('3', 'The Doors');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('3', 'Waiting for the Sun');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('4', 'Led Zeppelin I');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('4', 'Led Zeppelin II');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('4', 'Led Zeppelin III');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('4', 'Led Zeppelin IV');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('5', 'Apostrophe');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('5', 'Sheik Yerbouti');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('6', 'No Doubt');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('6', 'Return of Saturn');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('7', 'Computer World');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('7', 'The Mix');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('7', 'Man Machine');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('7', 'Autobahn');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('7', 'Trans-Europe Express');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('7', 'Electric Cafe');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('7', 'Aerodynamik');";
insert += "INSERT INTO adp_albuns (artistId, albumName) VALUES ('7', 'Tour De France Soundtracks');";
comm.CommandText = insert;
int i = comm.ExecuteNonQuery(); //o valor de i pode variar de acordo com o banco.
return i + " row(s) inserted";
}
catch(Exception e)
{
return e.Message;
}
}
#endregion
#region Exemplo AdpCommand.ExecuteScalar()
/// <summary>
/// Retorna os valores da tabela de albuns
/// Exemplo de uso do mtodo AdpCommand.ExecuteScalar()
/// </summary>
/// <returns>string com o total de linhas na tabela Albuns</returns>
public string ReturnRowsInTable()
{
try
{
AdpCommand comm = this.connection.CreateCommand();
string query = "select count(id) from adp_artists";
comm.CommandText = query;
Int64 i = (Int64)comm.ExecuteScalar(); //estou convertendo para Int64 pois as ultimas versoes
//do MySql retornam inteiros de 64 bits
return "Table adp_artists contais "+i+" records" ;
}
catch(Exception e)
{
return e.Message;
}
}
#endregion
#region Exemplo AdpCommand.ExecuteReader()
/// <summary>
/// Mostra os albuns da tabela adp_albus
/// exemplo de uso da funcao ExecuteReader()
/// </summary>
/// <param name="close_after">define se fecha a conexao apos a exibicao ou nao (exemplo de CommandBehavior</param>
/// <returns>string formatada com os albuns cadastrados</returns>
public string ShowAlbuns(bool close_after)
{
try
{
AdpCommand comm = this.connection.CreateCommand();
string query,retorno = "";
query = "select adp_albuns.*, adp_artists.name ";
query += "from adp_albuns, adp_artists ";
query += "where adp_artists.id = adp_albuns.artistId ";
query += "order by albumName";
comm.CommandText = query;
AdpDataReader dr = new AdpDataReader();
if(close_after)
dr = comm.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
else
dr = comm.ExecuteReader();
retorno = "Id, Album, Artist\n";
retorno += "=====================================\n";
while(dr.Read())
{
retorno += dr["artistId"].ToString()+", "+dr["albumName"].ToString()+", "+dr["name"].ToString()+ "\n";
}
dr.Close(); // fec ha o datareader pois nao pode haver 2 datareaders abertos ao mesmo tempo
return retorno;
}
catch(Exception e)
{
return e.Message;
}
}
#endregion
#region Exemplo AdpCommand.ExecuDataSet()
/// <summary>
/// Mostra os albuns da tabela adp_artists
/// exemplo de uso da funcao ExecuteDataSet()
/// </summary>
/// <returns>string formatada com os artistas cadastrados</returns>
public string ShowArtists()
{
try
{
AdpCommand comm = this.connection.CreateCommand();
string query,retorno = "";
query = "select * from adp_artists ";
query += "order by name";
comm.CommandText = query;
System.Data.DataSet ds = comm.ExecuteDataSet();
// Pega a primeira table e jgoa em um DataTable
System.Data.DataTable tbl = ds.Tables[0];
retorno = "Tabela: "+tbl.TableName.ToString()+"\n\n";
retorno += "Id, Artist, Record\n";
retorno += "=====================================\n";
foreach(System.Data.DataRow dr in tbl.Rows)
{
retorno += dr["id"] +", "+ dr["name"]+", "+dr["record"] + "\n";
}
return retorno;
}
catch(Exception e)
{
return e.Message;
}
}
#endregion
#region Exemplo AdpCommand.ExecuDataTable()
/// <summary>
/// Mostra a quantidade de albuns por gravadora
/// exemplo de uso da funcao ExecuteDataTable()
/// </summary>
/// <returns>string formatada com o nmero de albuns por gravadora</returns>
public string ShowAlbunsByRecord()
{
try
{
AdpCommand comm = this.connection.CreateCommand();
string query,retorno = "";
query = "select count(adp_albuns.albumName) as qt, adp_artists.record "+
" from adp_albuns, adp_artists "+
" where adp_albuns.artistId = adp_artists.id "+
" group by record ";
comm.CommandText = query;
// Pega a primeira table e jgoa em um DataTable
System.Data.DataTable tbl = comm.ExecuteDataTable();
retorno = "\nAlbuns quantity by record:\n";
retorno += "=====================================\n";
foreach(System.Data.DataRow dr in tbl.Rows)
{
retorno += dr["qt"] +" as "+dr["record"] + "\n";
}
return retorno;
}
catch(Exception e)
{
return e.Message;
}
}
#endregion
#region Exemplo AdpCommand.CrateParameter()
/// <summary>
/// Mostra os albuns da tabela adp_albus filtrados pela string "albumSearch"
/// exemplo de uso da funcao ExecuteReader() com parmetros
/// </summary>
/// <returns>string formatada com os albuns que atendem o critrio</returns>
public string SearchAlbum(string albumSearch)
{
try
{
AdpCommand comm = this.connection.CreateCommand();
string query,retorno = "";
query = "select adp_albuns.*, adp_artists.name ";
query += " from adp_albuns, adp_artists ";
query += " where adp_artists.id = adp_albuns.artistId ";
query += " and albumName like @album";
query += " order by albumName";
comm.CommandText = query;
comm.CreateParameter("@album",System.Data.DbType.String).Value = "%"+albumSearch.Trim()+"%";
AdpDataReader dr = new AdpDataReader();
dr = comm.ExecuteReader();
retorno += "Albuns with word %"+albumSearch+"%\n";
retorno += "Id, Album, Artist\n";
retorno += "=====================================\n";
while(dr.Read())
{
retorno += dr["artistId"].ToString()+", "+dr["albumName"].ToString()+", "+dr["name"].ToString()+ "\n";
}
dr.Close(); // fec ha o datareader pois nao pode haver 2 datareaders abertos ao mesmo tempo
return retorno;
}
catch(Exception e)
{
return e.Message;
}
}
#endregion
}
}
|