En la llibreria que crida a SQL Server que m'he fet falta la crida a Stored Procedures, i estic mirant de com fer-ho. He trobat llocs on fan d'una manera, hi ha per tots els gustos. El que he trobat és una forma un xic arriscada: la que fa servir el tipus "object" de C#. Primer cal veure que és el "boxing/unboxing".

int i = 123;
// La següent linia "boxes" (encapsa) i.
object o = i;

Cal observar que encara que sigui un "object" la variable conserva el tipus, i també que ocupa un espai de memòria diferent a la variable que a encapsat. Es a dir, que si "i" modifica el seu valor el "object" no. Igualment un cop des-encapsada si es modifica l'object no es modifica la variable on s'ha assignat el seu valor.

int i = 123;      // un valor 
object o = i;     // encapsat
int j = (int)o;   // des-encapsat

Cal vigilar molt que quan es el des-encapsat la variable a la que es fa l'assignació ha de ser del mateix tipus que la original, si no es produeix una InvalidCastException.

I els SQL Parameters? Doncs resulta que els SqlParameter tenen un constructor que té els següents paràmetres: Constructor (String, Object). On el "string" és el nom del paràmetre i el "object" és el valor. El constructor dedueix el tipus del paràmetre a partir del tipus de l'objecte encapsat. I ho fa seguint la següent taula:

Aleshores és molt perillós passar un object al constructor del SqlParameter perquè es fàcil despistar-se i que no encaixi amb el tipus de base de dades i com està "boxed" no hi ha una visió del que està passant exactament.

M'ha passat que he intentat obrir un projecte del tipus ".rptproj" en Visual Studio m'ha dit que "tururú", que aquest tipus de projecte no està suportat.

El que cal instal·lar per que es puguin obrir NO és cap característica del Visual Studio, és del SQL Server.

En la instal·lació del SQL Server cal marcar el "Bussines intelligence development studio", també conegut com BIDS.

Aleshores en el "About" del Visual Studio apareixen 3 nous dissenyadors, un d'ells el de reports.

I voilà, ja podem fer projectes de reporting.

Quan les taules d'una BBDD creixen i creixen el rendiment es degrada, aleshores l'opció es buidar la BBDD de dades obsoletes, crear taules on deixar que els registres descansin en pau. Quan aquesta opció no és possible cal fer particions de les dades. Des de SQL Server 2005 en endavant això es pot fer. Però cal planificar les particions amb cura. Aquest exemple que poso a continuació és típic i fàcil d'entendre. El primer pas és crear els grups de fitxers, a ser possible amb un nom significatiu:

ALTER DATABASE AdventureWorksDW ADD FILEGROUP [Filegroup_2001]  
GO  
ALTER DATABASE AdventureWorksDW ADD FILEGROUP [Filegroup_2002]  
GO  
ALTER DATABASE AdventureWorksDW ADD FILEGROUP [Filegroup_2003]  
GO  
ALTER DATABASE AdventureWorksDW ADD FILEGROUP [Filegroup_2004]  
GO

Després cal afegir fitxers de debó a aquests grups de fitxers, almenys un a cada grup.

ALTER DATABASE AdventureWorksDW
  ADD FILE
  (NAME = N'data_2001',
  FILENAME = N'C:\mssql2005\data\data_2001.ndf',
  SIZE = 5000MB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Filegroup_2001]  
GO  
ALTER DATABASE AdventureWorksDW
  ADD FILE
  (NAME = N'data_2002',
  FILENAME = N'D:\mssql2005\data\data_2002.ndf',
  SIZE = 5000MB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Filegroup_2002]
GO  
ALTER DATABASE AdventureWorksDW
  ADD FILE
  (NAME = N'data_2003',    
  FILENAME = N'E:\mssql2005\data\data_2003.ndf',    
  SIZE = 5000MB,    
  MAXSIZE = 10000MB,    
  FILEGROWTH = 500MB)  
  TO FILEGROUP [Filegroup_2003]  
GO  
ALTER DATABASE AdventureWorksDW
  ADD FILE  
  (NAME = N'data_2004',
  FILENAME = N'F:\mssql2005\data\data_2004.ndf',
  SIZE = 5000MB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Filegroup_2004]
GO

Un cop fet això cal decidir quin criteri es fa servir per particionar. Normalment es fa servir un criteri temporal, per anys, mesos, etc... Aleshores cal disposar d'una columna DATETIME a la taula. No és obligatori que sigui un datetime, pot ser un sencer (integer), per exemple la part de l'any de la data etc... El criteri que decideix on va cada registre s'anomena funció de particionat i es defineix:

CREATE PARTITION FUNCTION FullOrderDateKeyRangePFN(DATETIME)  AS
  RANGE LEFT FOR VALUES 
  (  '20011231 23:59:59.997',
  '20021231 23:59:59.997',
  '20031231 23:59:59.997',
  '20041231 23:59:59.997'  )

Ara cal crear un esquema de particionat, on es diu al SQL Server que en uns determinats grups de fitxers es farà servir una funció de particionat.

CREATE PARTITION SCHEME FullOrderDateRangePScheme  AS
  PARTITION FullOrderDateKeyRangePFN  TO 
  ([Filegroup_2001],
  [Filegroup_2002],
  [Filegroup_2003],
  [Filegroup_2004],
  [PRIMARY]  )

Un cop es té l'esquema cal crear-hi les taules dins. Es fa amb un create table normal, però enlloc de dir-li "ON PRIMARY" es posa el nom de l'esquema:

CREATE TABLE [DBO].[FACTINTERNETSALES_PARTITIONED] (
  [PRODUCTKEY]            [INT]   NOT NULL,
  [ORDERDATEKEY]          [INT]   NOT NULL,
  [DUEDATEKEY]            [INT]   NOT NULL,
  [SHIPDATEKEY]           [INT]   NOT NULL,
  [CUSTOMERKEY]           [INT]   NOT NULL,
  [PROMOTIONKEY]          [INT]   NOT NULL,
  [CURRENCYKEY]           [INT]   NOT NULL,
  [SALESTERRITORYKEY]     [INT]   NOT NULL,
  [SALESORDERNUMBER]      [NVARCHAR](20)   NOT NULL,
  [SALESORDERLINENUMBER]  [TINYINT]   NOT NULL,
  [REVISIONNUMBER]        [TINYINT]   NULL,
  [ORDERQUANTITY]         [SMALLINT]   NULL,
  [UNITPRICE]             [MONEY]   NULL,
  [EXTENDEDAMOUNT]        [MONEY]   NULL,
  [UNITPRICEDISCOUNTPCT]  [FLOAT]   NULL,
  [DISCOUNTAMOUNT]        [FLOAT]   NULL,
  [PRODUCTSTANDARDCOST]   [MONEY]   NULL,
  [TOTALPRODUCTCOST]      [MONEY]   NULL,
  [SALESAMOUNT]           [MONEY]   NULL,
  [TAXAMT]                [MONEY]   NULL,
  [FREIGHT]               [MONEY]   NULL,
  [CARRIERTRACKINGNUMBER] [NVARCHAR](25)   NULL,
  [CUSTOMERPONUMBER]      [NVARCHAR](25)   NULL,
  [FULLDATE]              [DATETIME]   NULL)
  ON FullOrderDateRangePScheme  (FullDate)

Ara que la taula està creada, cal omplir-la amb dades, normalment d'una taula existent.

INSERT FACTINTERNETSALES_PARTITIONED
SELECT *
FROM   FACTINTERNETSALES

Per veure com han quedat distribuïdes les dades es pot fer servir la següent comanda.

SELECT $PARTITION.FullOrderDateKeyRangePFN(FULLDATE)  AS PARTITIONID,
       COUNT(*)                                   AS ROW_COUNT
FROM     DBO.FACTINTERNETSALES_PARTITIONED
GROUP BY $PARTITION.FullOrderDateKeyRangePFN(FULLDATE)
ORDER BY PARTITIONID

Quan es creen els indexos també es fan tenint en compte el particionat:

CREATE INDEX ix_FactInternetSales_Partitioned_ProductKey
  ON FactInternetSales_Partitioned (ProductKey)
  ON FullOrderDateRangePScheme(FullDate)

En una aplicació a vegades cal un petit fitxer de configuració, actualitzable etc... llavors amb la clsConfig hi ha prou. A vegades cal guardar més dades, més estructurades, aleshores és quan es fan servir BBDD, ja que un fitxer XML té un mal rendiment guardant dades (s'ha de llegir sencer per modificar-lo). Però tampoc cal un SQL Server si no es vol més que guardar unes quantes dades, ni pagar llicències ni instal·lar res.

Per això he estat buscant un BBDD local que es pugui enganxar a un programet, i he trobat que el SQLServerCE (made by Microsoft) s'ajusta força a aquestes necessitats. L'únic problema que té és que Microsoft sembla que no el vol mantenir més ja que suposo que estaria menjant-se vendes de petits SQLServers. També he llegit que és possible que el torni a posar al VisualStudio (encara que això ja és més un rumor). El cas és que la última versió del SQLServer CE és força recent i s'integra perfectament amb el VisualStudio 2013 si es sap com.

El primer que cal fer és instal·lar les eines del SQLServer CE (el "ToolBox"), amb això al menú "TOOLS" ens apareix una nova opció que ens permet crear BBDD de SQLServerCE, veure els camps, executar scripts, ...

Un cop les eines estan instal·lades cal poder accedir des de programa a la BBDD. Aquí és on m'ha costat més per que no està del tot clar. El que cal fer és copiar la DLL del SQLServer CE a la carpeta del projecte que estem desenvolupant. Normalment aquesta carpeta està a: "C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Private" (s'ha de copiar sencera, amb subcarpetes). Un cop copiada la carpeta, cal posar a l'arrel del projecte les carpetes "amd64" i "x86" (aquestes dues carpetes són subcarpetes de la "Private"). Totes les DLLs han d'estar marcades com "Copiar si es modifiquen" en l'acció "Copiar al directori de sortida" (es fa fent "Propiedades" sobre la DLL). Un cop fet això es posa la "Referència" a la DLL "System.Data.SqlServerCe.dll". Queda així:

A partir d'aquest moment ja es pot fer servir el SQLServer CE en VisualStudio 2013.

Igualment que poques vegades totes les dades estan en una única taula, poques vegades no cal filtrar per cap camp. El Generador té suport per això.

És en aquest punt on entren els camps que permeten filtrar una taula, també els anomeno "Params extra" ja que són paràmetres s'afegeixen als que hi ha per defecte. Per defecte hi ha la clau primària de la taula per tal de permetre treballar o seleccionar només un registre. Al fer JOINS també es poden incloure camps d'altres taules per filtrar.

En els paràmetres de filtrat cal avaluar quina estratègia es fa servir, ja que depenent de la quantitat de paràmetres cal convertir la consulta a una amb suport de WHERE's dinàmics.

El interface permet generar els WHEREs més normals, una comparació directa, una altra amb operador habituals (>, <, >=, <=...), hi ha el cas de comparació amb NULL (marcar la casella Null compare, es genera un IS NULL), amb strings fent LIKE (amb dos sabors, que coincideixi el principi del camp o qualsevol lloc del camp. Finalment el BETWEEN per dates (s'especifica el camp i després els paràmetres, per exemple si es vol filtrar per data alta, es posa el camp DataAlta a la informació del camp, i després es posa Inici, Fi i es marca la casella. Això crea dos paràmetres Inici i Fi, i es genera el BETWEEN amb el camp).

També cal pensar que si un mateix camp es pot filtrar per diferents operadors, no hi ha problema en afegir-lo més d'un cop, només cal posar noms diferents.

En poques ocasions totes les dades que requereix un formulari estan en una única taula. En aquest punt és on entren en joc els JOINS.

El Generador té suport per JOINS, de tal manera que es poden obtenir dades de més d'una taula d'una sola consulta. Encara que els UPDATE/INSERT/DELETE només funcionen per la taula en la que s'està treballant. Els JOINs només es generen pels SELECT/QUERY.

La forma d'indicar-los és fàcil, taula origen, camp origen, taula destí, camp destí. També cal indicar si és un LEFT JOIN o un INNER JOIN.

Els LEFT JOIN són els que estan per defecte ja que obtenen dades de la taula principal independentment que tinguin un registre relacionat a la taula a la que es fa el JOIN. Per exemple si estem fent el mestre de Comarques, i es vol obtenir la Província, cal fer un JOIN, es serialitza el camp per enviar-lo al formulari per no es deserialitza al no pertànyer a la taula que s'està editant. Quan es fan servir JOINs aleshores ja es poden afegir camps de la taula que s'ha afegit a la llista de camps a retornar (marcant que s'han de serialitzar), i a més a més també es poden posar com paràmetres del WHERE. Cal anar amb compte perque els JOINs introdueixen duplicats, i potser cal marcar el DISTINCT o valorar la forma de fer el CTE, si amb ROW_NUMBER o DENSE_RANK.

Aquest troç de codi es correspon al botó ADD del Generador. El codi està dividit en dos parts, una del botó pròpiament dit i l'altra de l'operació. El codi del botó està en el codi del formulari (no podria estar en un altra lloc), mentre que el codi que genera la sentència SQL està en la clsCamp. Primer el codi del formulari, bàsicament és un IF que distingeix on enviar l'operació, si a un servidor de BBDD o bé a un fitxer. Quan és l'opcio de BBDD cal obrir i tancar connexió.

private void btnADD_Click(object sender, EventArgs e)
{
    btnADD.Enabled = false;
    try
    {
	clsTaula Taula = GestorTaules.GetTaula(txtNomTaula.Text);
	clsCamp Camp;
	SqlConnection CONN; 
	if (Taula != null)
	{
	    Camp = Taula.BuscaCamp(Taula.NomTaula, txtNomCamp.Text);
	    if (Camp != null)
	    {
		if (rdbBBDD.Checked)
		{
		    CONN = new SqlConnection();
		    CONN.ConnectionString = GestorTaules.ConnString;
		    CONN.Open();
		    Camp.ADD(CONN);
		    CONN.Close();
		}
		else Camp.ADD(GestorTaules.ScriptFile);
		txtMissatges.Text += Environment.NewLine + DateTime.Now.ToShortDateString()
                 + " " + DateTime.Now.ToShortTimeString()
                 + " Camp " + Camp.NomCamp + " creat a la taula " + Taula.NomTaula;
	    }
	}
    }
    catch (Exception E)
    {
	txtMissatges.Text += Environment.NewLine + DateTime.Now.ToShortDateString() + " "
                 + DateTime.Now.ToShortTimeString() + " ERROR afegint camp: " + E.Message;
    }
    btnADD.Enabled = true;
}

Ara els dos mètodes de la clsCamp (1 sobrecarregat a 2) amb l'operació ALTER TABLE ADD.... La clsCamp té tota la informació necessària per muntar la sentència.

public void ADD(string ScriptFile)
{
    StringBuilder SQL = new StringBuilder();

    SQL.Clear();
    SQL.AppendFormat("ALTER TABLE {0} ADD {1} {2} ", NomTaula, NomCamp, strTipus);
    if (Nulable)
    {
	SQL.Append("NULL");
    }
    else
    {
	SQL.Append("NOT NULL");
    }
    SQL.Append("\nGO\n");
    if (File.Exists(ScriptFile)) File.AppendAllText(ScriptFile, SQL.ToString());
    else
    {
	using (StreamWriter w = File.CreateText(ScriptFile))
	{
	    w.Write(SQL.ToString());
	}
    }
}

public void ADD(SqlConnection SQLConn)
{
    StringBuilder SQL = new StringBuilder();

    SQL.Clear();
    SQL.AppendFormat("ALTER TABLE {0} ADD {1} {2} ", NomTaula, NomCamp, strTipus);
    if (Nulable)
    {
	SQL.Append("NULL");
    }
    else
    {
	SQL.Append("NOT NULL");
    }
    Utils.ExecuteSQL(SQL.ToString(), SQLConn);
}

Un dels passos que fa el Generador de classes és obtenir les taules d'una BBDD. És una tasca molt senzilla. S'executa una SQL contra la taula "sysobjects" i llestos. Després es carreguen en un combo els resultats. Com sempre el codi:

btnCarregaTaules.Enabled = false;
string SQL;
DataTable TBL;

try
{
	SqlConnection CONN = new SqlConnection();
	CONN.ConnectionString = GestorTaules.ConnString;
	CONN.Open();

	SQL = "SELECT name FROM sysobjects WHERE xtype='u' ORDER BY name";
	TBL = Utils.ExecuteSQL(SQL, CONN);

	foreach (DataRow DR in TBL.Rows)
	{
	    cboTaules.Items.Add(DR["name"].ToString());
	}

	CONN.Close();
}
catch (Exception E)
{
	MessageBox.Show("Error: " + E.Message);
}
btnCarregaTaules.Enabled = true;

Quan faig un SSIS (Sql Server Integration Service) per mi, vull que quan acabi m'envii un mail amb el resultat de l'execució, tan si ha anat bé com si ha anat malament.

Crear un perfil nou.

Dins del desenvolupament web hi ha un punt que a vegades no es té en compte quan es desenvolupa: la quantitat de dades a tractar en una taula. Quan aplicació entra en manteniment és habitual rebre peticions de que en algun punt l'aplicació va lenta, per que quan es va desenvolupar no es va valorar correctament l'estructura d'aquell punt i ara es pateixen les conseqüències.

Igualment quan hi ha un munt de dades el rendiment tard o d'hora es resenteix, és pot posar un hardware més potent, línies amb més capacitat però el pressupost marca el límit. És aleshores quan s'han de buscar alternatives. En el cas de la clsDades consisteix en activar la caché per una taula en concret. Normalment quan s'executa una stored procedure QUERY contra una taula amb milions de registres aquesta triga el seu temps, per molt que estiguin ben posats els indexos, total per extreure 20-50 registres d'entre milions. Aquests registres després s'empaqueten en JSON i s'envien al navegador de l'usuari. La caché es dedica a guardar els JSONs més usats, de tal manera que

Per aconseguir això calen varies coses:

• Una taula on guardar el JSON, la taula que es diu CACHE.
• Stored procedures per accedir a aquesta taula.
• Interceptar en el codi les peticions dels grids per tal de veure si cal consultar la cache.
• Interceptar en el codi els INS/UPD/DEL que invaliden la cache.

Començaré per taula cache que té la següent estructura:

• Taula: camp que guarda quina taula estem cachejant.
• Pantalla: pantalla on està el grid que estem cachejant.
• Grid: ID del grid que estem cachejant.
• Pag: pàgina del grid.
• NumRegs: número de registres de la pàgina.
• SortOrder: ordre del grid.
• Filtre: filtre aplicat per obtenir aquests registres. Normalment el trec de la URL i el poso aqui tal com raja.

El codi de creació de la taula:

CREATE TABLE [dbo].[CACHE](
	[Taula] [nvarchar](20) NOT NULL,
	[Pantalla] [nvarchar](20) NOT NULL,
	[Grid] [nvarchar](20) NOT NULL,
	[Pag] [int] NOT NULL,
	[NumRegs] [int] NOT NULL,
	[SortOrder] [nvarchar](30) NOT NULL,
	[Filtre] [nvarchar](200) NOT NULL,
	[Resultat] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_DI_EXP_CACHE] PRIMARY KEY CLUSTERED 
(
	[Taula] ASC,
	[Pantalla] ASC,
	[Grid] ASC,
	[Pag] ASC,
	[NumRegs] ASC,
	[SortOrder] ASC,
	[Filtre] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
 ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

La resta de temes els tractaré en el següent post.