SQL Server for beginners and Best Practices

SQL Server for beginners and Best Practices

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx

http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx

Par Romagny13
Ecrire un commentaire - Voir les 0 commentaires - Recommander

récupérer la clé auto incrémentée et le GUID d'une base SQL Server

Récupérer la clé auto incrémentée et le GUID d'une base SQL Server

I - récupérer la clé auto incrémentée d'une base SQL Server

Insérer une ligne dans une table d'une base SQL Server dont la clé primaire est auto incrémentée et récupérer la valeur de la clé qui vient d'être créée pour cette ligne

private void button1_Click(object sender, EventArgs e)

{

System.Data.SqlClient.SqlConnection oSqlConnection = new System.Data.SqlClient.SqlConnection();

oSqlConnection.ConnectionString = @"Data Source=.;Initial Catalog=ContactDB;Integrated Security=SSPI;Connect Timeout=30;";

System.Data.SqlClient.SqlCommand oSqlCommand = new System.Data.SqlClient.SqlCommand("INSERT INTO [ContactType]([ContactTypeLibelle]) VALUES('test');SELECT SCOPE_IDENTITY(); ", oSqlConnection);

oSqlConnection.Open();

object nresult = oSqlCommand.ExecuteScalar();

oSqlConnection.Close();

}

On peut égalemet utiliser SELECT @@identity from [ContactType]
(valable pour Access aussi)

> result contiendra l'entier correspondant à la clé venant d'être créée(ex : 6)

II - pour récupérer un GUID

DECLARE @var uniqueidentifier;
SET @var = NEWID();
INSERT INTO MaTable(ChampA, ChampB)
VALUES(@var, 'abcdef');
SELECT @var

Par Romagny13
Ecrire un commentaire - Voir les 0 commentaires - Recommander

sqlcmd

SQLCMD

http://msdn2.microsoft.com/fr-fr/library/ms162773.aspx

un didacticiel

http://msdn2.microsoft.com/fr-fr/library/ms170207.aspx

un petit e-learning gratuit

https://www.microsoftelearning.com/france/eLearning/courseContent.aspx?courseId=50441&lc=1036

Par Romagny13
- Voir les 0 commentaires - Recommander

SQL Server Management Objects (SMO)

SQL Server Management Objects (SMO)

http://msdn2.microsoft.com/en-us/library/ms162169.aspx

Par Romagny13
- Voir les 0 commentaires - Recommander

Récupérer la liste des procédures stockées de SQL Server 2005

La requête de base

SELECT name FROM sysobjects WHERE type='P'

Un exemple de méthode qui retourne la collection de procédures stockées d’une base :

public NET2CsSQL.BObject.StoredProcedureCollection FindStordProcedures()

{

NET2CsSQL.BObject.StoredProcedureCollection oStoredProcedureCollection;

System.Data.SqlClient.SqlCommand oSqlCommand;

System.Data.DataTable oDataTableStoredProcedures;

oDataTableStoredProcedures = new System.Data.DataTable();

oSqlCommand = new System.Data.SqlClient.SqlCommand();

oStoredProcedureCollection = new NET2CsSQL.BObject.StoredProcedureCollection();

oSqlCommand.Connection = oSqlConnection;

oSqlCommand.CommandType = System.Data.CommandType.Text;

oSqlCommand.CommandText = "SELECT name FROM sysobjects WHERE type='P'";

System.Data.SqlClient.SqlDataReader oSqlDataReader = oSqlCommand.ExecuteReader();

while (oSqlDataReader.Read())

{

NET2CsSQL.BObject.StoredProcedure oStoredProcedure;

oStoredProcedure = new NET2CsSQL.BObject.StoredProcedure();

oStoredProcedure.Name = oSqlDataReader["Name"].ToString();

oStoredProcedureCollection.Add(oStoredProcedure);

}

return oStoredProcedureCollection;

}

Il suffit bien entendu de définir la connection :

private System.Data.SqlClient.SqlConnection oSqlConnection;

private void CreateConnection(String sConnectionString)

{

try

{

oSqlConnection = null;

oSqlConnection = new System.Data.SqlClient.SqlConnection();

oSqlConnection.ConnectionString = sConnectionString;

oSqlConnection.Open();

}

catch (Exception ex)

{

throw ex;

}

private void CloseConnection()

{

try

{

oSqlConnection.Close();

}

catch (Exception ex)

{

throw ex;

}

Par Romagny13
- Voir les 0 commentaires - Recommander

Manager ses données

1 - Microsoft SQL Server Database Publishing Wizard 1.0

générer rapidement le script SQL d'une base de données ou la publier

http://www.microsoft.com/downloads/details.aspx?FamilyID=29b4ffd8-ac3a-4481-b352-9b185619a901&DisplayLang=en

2 - >> NET2CsExportData

un peu de pub pour un petit outil bien sympathique (qui demanderait à prendre de l'ampleur) que j'ai posté sur CodeS-SourceS

(permet d'exporter rapidement les données et structure d'une base de données Access ou SQL Server ou fichier Xml ou Excel des uns aux autres, permet également de générer le script SQL et un rapport simple sur la base chargée)

http://www.csharpfr.com/codes/EXPORTER-VOS-DONNEES-SQL-SERVER-ACCES-XML-EXCEL_41784.aspx

3 SQL Server 2005 Books Online (February 2007)

une aide qui a l'air trés complête avec didacticiels sur les différents aspects de SQL Server 2005

> les outils et utiliaires SQL Server

> Analysis Services

> Data mining

> Integration Services

> Notification Services

> Reporting Services

http://www.microsoft.com/downloads/details.aspx?familyid=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

ou plus simplement directement en ligne

http://msdn2.microsoft.com/fr-fr/library/ms169620.aspx

Par Romagny13
- Voir les 0 commentaires - Recommander

Le type Xml de Sql Server 2005

MSDN (us) > http://msdn2.microsoft.com/en-us/library/ms345117.aspx

Kader Yildirim – Techheaderbrothers > http://www.techheadbrothers.com/Articles.aspx?Id=66cd0214-3138-4762-93ca-5c5324860234&p=1

TechDays > http://www.microsoft.com/france/technet/produits/sql/xml.mspx

Suite à l’excellent webcast issus des techdays sur le sujet je commence à me faire une mini synthèse sur le sujet … à n’en pas douter xml dans sql server n’est pas un simple gadget et je l’utiliserais d’avantage

Le xml m’a toujours semblé un bon moyen de stockage d’informations ou de données, toutefois il présente également des inconvénients :

- Je n’oserais pas faire des fichiers xml trop volumineux

- On peut facilement perdre toutes ses données (ex : fichier supprimé)

- La sérialisation n’est pas sans me poser des problèmes, assez complexe et de ce fait pas assez fiable on a vite fait de se retrouver dans l’incapacité de lire les données afin de les stocker dans la couche métier

- Il n’est pas forcément simple de créer des fichiers xml complets(comportant toutes les « tabes ») aussi facilement qu’une base de données

- Le xml n’est pas aussi simple d’accès qu’avec une requête SQL

Mais si on regarde bien le format xml correspond bien mieux à un modèle objet complexe, d’un autre côté le modèle relationnel offre une simplicité d’utilisation et une robustesse qui n’est plus à démontrer

L’implémentation du type xml dans sql server n’est pas forcément un gadget et peut résoudre pas mal des points énoncés ci-dessus, maintenant il ne va pas falloir faire l’inverse et mettre du xml partout dans la base, étudier quand ce type peut etre utile, la manière de l’implémenter et le gérer

2 approches :

- Le modèle « classique » de stockage de type relationnel (où tout est mis « à plat ») convient et est suffisant

- Un modèle de type hiérarchique ou en arbre est plus adapté > type xml

1 - Création d’un champ de table xml

create table ContactGroup

(

groupid int not null identity(1,1) primary key,

groupname char(200) null,

groupmembers xml null

)

2 – Insérer une ligne

(Ici une structure très simple à 1 seul niveau, mais la structure pourrait être sur plusieurs niveaux)

insert into ContactGroup(groupname,groupmembers)

values ('Amis',

'

<contact>

<contactid>1</contactid>

<contactname>Romagny Jérôme</contactname>

<contactemail>romagny13@yahoo.fr</contactemail>

</contact>

<contact>

<contactid>2</contactid>

<contactname>Bzllin Marie</contactname>

<contactemail>mb3@yahoo.fr</contactemail>

</contact>

');

b- insérer du xml dans du xml existant

ex : j’ajoute un contact aux deux contacts existants

update ContactGroup

set groupmembers.modify('

insert

<contactname>Martin Pierre</contactname>

<contactemail>Martin@yahoo.fr</contactemail>

</contact>

after (contact)[2]')

3 – Modifier une ligne existante

> ex : Oups je m’aperçois que j’ai une erreur de saisie le nom du second contact est Bzllin au lieu de Bellin …

a-remplacer l’ensemble du champ

update ContactGroup

set groupmembers='

<contactname>Romagny Jérôme</contactname>

<contactemail>romagny13@yahoo.fr</contactemail>

</contact>

<contactname>Bellin Marie</contactname>

<contactemail>mb3@yahoo.fr</contactemail>

</contact>

'

where groupid=1;

Note : on ne peut pas modifier directement un champ au format xml dans sql server managament apparemment

b- Ne modifier que la valeur désirée (au lieu de tout remplacer)

update ContactGroup

set groupmembers.modify('replace value of (contact/contactname/@nb)[1] with "Bellin Marie"')

where groupmembers.exist('contact/contactname/@nb="Bzllin Marie"')=1

4 - Sélection / Consultation

a- Renvoyer le contenu

select * from ContactGroup for xml auto

Renvoie le contenu de manière hiérarchique :

<ContactGroupgroupid="1"groupname="Amis ">

<contactname>Romagny Jérôme</contactname>

<contactemail>romagny13@yahoo.fr</contactemail>

</contact>

<contactname>Bellin Marie</contactname>

<contactemail>mb3@yahoo.fr</contactemail>

</contact>

<contactname>Martin Pierre</contactname>

<contactemail>Martin@yahoo.fr</contactemail>

</contact>

</groupmembers>

</ContactGroup>

b- Renvoyer le contenu sous forme de “lignes”

select * from ContactGroup for xml raw

<rowgroupid="1"groupname="Amis ">

<contactname>Romagny Jérôme</contactname>

<contactemail>romagny13@yahoo.fr</contactemail>

</contact>

<contactname>Bellin Marie</contactname>

<contactemail>mb3@yahoo.fr</contactemail>

</contact>

<contactname>Martin Pierre</contactname>

<contactemail>Martin@yahoo.fr</contactemail>

</contact>

</groupmembers>

</row>

c- Renvoyer une partie (ou totalité du xml contenu dans le champ)

select groupmembers.query('//contactname')

from ContactGroup

Ou

select groupmembers.query(

'for $contactgroup in contact

return $contactgroup/contactname

')

from ContactGroup

Ex : ici renverra 1 ligne contenant :

<contactname>Romagny Jérôme</contactname>

<contactname>Bellin Marie</contactname>

d- Renvoyer seulement une valeur (sans les balises xml)

select groupmembers.value('(contact/contactname)[1]','char(200)')

from ContactGroup

Ex : ici renverra « Romagny Jérôme » (oui lol c’est moi)

Supprimer

De manière classique :

delete ContactGroup

where groupid=1;

Ajouter un schéma

Les données rentrées dans le champ xml devra respecter ce schema

1 – préparer le schéma :

create xml schema collection contactgroupschema as

'<?xml version="1.0" encoding="utf-8"?>

<xs:schema id="contactgroup" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

<xs:element name="contactgroup" msdata:IsDataSet="true" msdata:Locale="en-US">

<xs:complexType>

<xs:choice minOccurs="0" maxOccurs="unbounded">

<xs:element name="contact">

<xs:complexType>

<xs:sequence>

<xs:element name="contactid" type="xs:string" minOccurs="0" />

<xs:element name="contactname" type="xs:string" minOccurs="0" />

<xs:element name="contactemail" type="xs:string" minOccurs="0" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xs:schema>'

2 – création d’une table dont le champ groupmembers respectera le schéma ci-dessus

create table ContactGroupWithType

(

groupid int not null identity(1,1) primary key,

groupname char(200) null,

groupmembers xml(content contactgroupschema)

)

Par Romagny13
- Voir les 0 commentaires - Recommander

Microsoft SQL Server Database Publishing Wizard 1.0

http://www.microsoft.com/downloads/details.aspx?FamilyID=29b4ffd8-ac3a-4481-b352-9b185619a901&DisplayLang=en

Exporter une base de données à l’aide du Database Publishing Wizard (Technos-sources)

http://www.technos-sources.com/tutorial-exporter-base-donnees-aide-database-publishing-wizard-35.aspx

Par Romagny13
- Voir les 1 commentaires - Recommander

Récupérer les relations (clés étrangères) de bases de données Sql Server

Récupérer les relations (clés étrangères) de bases de données Sql Server

La requête :

select o.name as TABLEFILLE, fc.name as CLEFILLE,ro.name as TABLEMERE, c.name as CLEMERE

from sysobjects o

join sysforeignkeys fk on fk.fkeyid = o.id

join sysobjects ro on ro.id = fk.rkeyid

join syscolumns c on c.id = ro.id and c.colid = fk.rkey

join syscolumns fc on fc.id = o.id and fc.colid = fk.fkey

le code C# :

public List<ForeignKey> RecupererForeignKeys(System.Data.SqlClient.SqlConnection oSqlConnection)

{

List<ForeignKey> ListForeignKeys;

ListForeignKeys = new List<ForeignKey>();

System.Data.SqlClient.SqlCommand oSqlCommandForeignKey;

oSqlCommandForeignKey = new System.Data.SqlClient.SqlCommand();

oSqlCommandForeignKey.Connection = oSqlConnection;

oSqlCommandForeignKey.CommandType = System.Data.CommandType.Text;

oSqlCommandForeignKey.CommandText = "select o.name as TABLEFILLE, fc.name as CLEFILLE,ro.name as TABLEMERE, c.name as CLEMERE from sysobjects o join sysforeignkeys fk on fk.fkeyid = o.id join sysobjects ro on ro.id = fk.rkeyid join syscolumns c on c.id = ro.id and c.colid = fk.rkey join syscolumns fc on fc.id = o.id and fc.colid = fk.fkey";

System.Data.SqlClient.SqlDataReader oSqlDataReader = oSqlCommandForeignKey.ExecuteReader();

while(oSqlDataReader.Read())

{

// ici je stocke les différents champs recuperes dans une liste générique

ForeignKey oForeignKey;

oForeignKey = new ForeignKey();

oForeignKey.TABLEFILLE = oSqlDataReader[0].ToString();

oForeignKey.CLEFILLE = oSqlDataReader[1].ToString();

oForeignKey.TABLEMERE = oSqlDataReader[2].ToString();

oForeignKey.CLEMERE = oSqlDataReader[3].ToString();

ListForeignKeys.Add(oForeignKey);

}

oSqlDataReader.Close();

return ListForeignKeys;

}

Par Romagny13
- Voir les 0 commentaires - Recommander

Procédures stockées avec SQL Server 2005

Procédures stockées

avec Visual Studio 2005

et SQL Server 2005

// Executer une requete execute nomrequete ‘parametre1’

execute dbo.Procedure_test 'bellin'

//

oSqlCommand.CommandType = CommandType.StoredProcedure;

oSqlCommand.CommandText = "nom_procedure";

+ parametres !! attention ceux-ci doivent porter exactement le même nom que dans le SQL de la procedure stockée

Exemple complet :

Procédure stockée définie dans SQL Server mangament Studio 2005 Express

CREATE PROCEDURE Procedure_test(@nom char(100))

AS

BEGIN

select * from dbo.PARENT where nomparent=@nom

END

GO

Code C#

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

namespace Procedures_Stockees_sql2005

{

public partial class Form1 : Form

{

System.Data.SqlClient.SqlConnection oSqlConnection;

System.Data.SqlClient.SqlDataReader oSqlDataReader;

public Form1()

{

InitializeComponent();

}

private void button1_Click(object sender, EventArgs e)

{

// 1 connection (connectionstring)

// 2 command (connection,commandtype,commandtext,+ parametres)

// 3 execution requete avec ouverture connection(executereader,executenonquery,executenonscalar,fill)

// 4 affichage (parcourt enregistrements ou affectation datasource + binding)

// fermeture datareader + connection si acces datareader(mode connecte)

this.Ouvrir_Connection();

oSqlDataReader = this.retourner_liste_parents_du_nom(textBox1.Text);

this.Afficher_liste_parents(oSqlDataReader);

this.Fermer_datareader(oSqlDataReader);

this.Fermer_Connection();

}

///<summary>

/// But : executer de la command definie et retourner un datareader rempli

///</summary>

///<param name="sNom"></param>

///<returns></returns>

public System.Data.SqlClient.SqlDataReader retourner_liste_parents_du_nom(string sNom)

{

System.Data.SqlClient.SqlCommand oSqlCommand;

System.Data.SqlClient.SqlParameter oSqlParameter;

System.Data.SqlClient.SqlDataReader oSqlDataReader;

try

{

// Command

oSqlCommand = new System.Data.SqlClient.SqlCommand();

oSqlCommand.Connection = oSqlConnection;

// si on avait plusieur sparametres pour notre procedure stockee

// oSqlCommand.Parameters.Add(oSqlParameter2); //etc.

oSqlCommand.CommandType = CommandType.StoredProcedure;

oSqlCommand.CommandText = "Procedure_test";

// PARAMETRES

// ATTENTION LE(S) PARAMETRE(S) DECLARES DANS LE CODE .NET ET SQL DE LA PROCEDURE STOCKEE DOIVENT PORTER EXACTEMENT LE MEME NOM

//>

////oSqlParameter = new System.Data.SqlClient.SqlParameter("@nom", SqlDbType.Char, 100);

////oSqlParameter.Value = sNom;

//> detaille

oSqlParameter = new System.Data.SqlClient.SqlParameter();

oSqlParameter.ParameterName = "@nom";

oSqlParameter.SqlDbType = SqlDbType.Char;

oSqlParameter.Value = textBox1.Text;

oSqlCommand.Parameters.Add(oSqlParameter);

// Execution de la command

oSqlDataReader = oSqlCommand.ExecuteReader();

}

catch (Exception ex)

{

throw ex;

}

return oSqlDataReader;

}

///<summary>

/// But : Afficher la liste correspondante

///</summary>

///<param name="oSqlDataReader"></param>

public void Afficher_liste_parents(System.Data.SqlClient.SqlDataReader oSqlDataReader)

{

while (oSqlDataReader.Read())

{

//> Par index

//listBox1.Items.Add("Nom : " + oSqlDataReader[0] + " - Prenom : " + oSqlDataReader[1]);

//> Par le nom de colonne

listBox1.Items.Add("Nom : " + oSqlDataReader["nomparent"] + " - Prenom : " + oSqlDataReader["prenomparent"]);

}

this.Fermer_datareader(oSqlDataReader);

}

///<summary>

/// But : initialiser la chaine de connection et ouvre la connection si celle ci est fermée

///</summary>

public void Ouvrir_Connection()

{

oSqlConnection = new System.Data.SqlClient.SqlConnection();

oSqlConnection.ConnectionString = @"Data Source=.SQLEXPRESS;Initial Catalog=mestests;Integrated Security=True";

// ouverture de la connection

if (oSqlConnection.State == ConnectionState.Closed)

{

oSqlConnection.Open();

}

///<summary>

/// But : fermer la connection

///</summary>

public void Fermer_Connection()

{

oSqlConnection.Close();

}

///<summary>

/// But : Fermer le datareader

///</summary>

///<param name="oSqlDataReader"></param>

public void Fermer_datareader(System.Data.SqlClient.SqlDataReader oSqlDataReader)

{

oSqlDataReader.Close();

}

Par Romagny13
- Voir les 0 commentaires - Recommander

Romagny13 - Actualité et Développement DotNet

Calendrier

Présentation

Catégories

Articles récents

Recherche

Liens

W3C

SQL Server

SQL Server for beginners and Best Practices

récupérer la clé auto incrémentée et le GUID d'une base SQL Server

sqlcmd

SQL Server Management Objects (SMO)

Récupérer la liste des procédures stockées de SQL Server 2005

Manager ses données

Le type Xml de Sql Server 2005

Microsoft SQL Server Database Publishing Wizard 1.0

Microsoft SQL Server Database Publishing Wizard 1.0

Récupérer les relations (clés étrangères) de bases de données Sql Server

Procédures stockées avec SQL Server 2005