FAQ VB.NETConsultez toutes les FAQ

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Imports System.Data.SqlClient 
  
Class adobasic 
  
    Public Shared Sub Main() 
  
    Try 
        ' Chaîne de connexion 
        Dim connectString As String = "database=test;server=am01;User ID=samfisher;pwd=echelon" 
        ' Objet connection 
        Dim connection As SqlConnection = new SqlConnection(connectString) 
        'Ouverture 
        connection.Open() 
        ' Fermeture 
        connection.Close() 
    Catch ex As Exception 
        System.Diagnostics.Debug.WriteLine(ex.ToString()) 
    End Try 
  
    End Sub 
  
End Class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Trace.Listeners.Add(New TextWriterTraceListener(Console.Out)) 
Dim connectString As String = "database=test;server=SRV01;User ID=samfisher;pwd=echelon" 
Dim connection As SqlConnection = New SqlConnection(connectString) 
connection.Open() 
Dim command As SqlCommand = New SqlCommand("SELECT * FROM usr_contract", connection) 
Dim reader As SqlDataReader = command.ExecuteReader 
Dim row As Object() = Nothing 
While reader.Read 
    If row Is Nothing Then 
    row = New Object(reader.FieldCount) {} 
    End If 
    reader.GetValues(row) 
    Dim i As Integer = 0 
    While i < row.GetLength(0) 
        If Not row(i) Is Nothing AndAlso Not (row(i) Is DBNull.Value) Then 
            Trace.Write(row(i).ToString()) 
        Else 
            Trace.Write("NULL") 
        End If 
        If i < row.GetUpperBound(0) Then 
            Trace.Write(" | ") 
        End If 
        i = i + 1 
    End While 
    Trace.Write(vbCrLf) 
End While 
reader.Close() 
connection.Close()

1
2
3
4
5
6
7
8
9
10
11
12
13
' Chaîne de connexion 
Dim connectString As String = "database=derivatives;server=SRV01;User ID=samfisher;pwd=echelon" 
' Objet connection 
Dim connection As SqlConnection = new SqlConnection(connectString) 
' Ouverture 
connection.Open() 
' Objet Command 
Dim command As SqlCommand = new SqlCommand("UPDATE usr_contract set ctr_n = ctr_n + 1", connection) 
' Execution 
Dim affectedrows As Integer = command.ExecuteNonQuery() 
Console.WriteLine("Nombre de lignes affectées {0}", affectedrows) 
' Fermeture connection 
connection.Close()

1
2
3
4
Dim sql As String = "SELECT * FROM pasta WHERE name = 'aujourd''hui'" 
' Ou 
Dim name As String = "aujourd'hui" 
sql = "SELECT * FROM pasta WHERE name = " & name.Replace("'", "''")

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
using System.Data.SqlClient; 
Imports System.Data.SqlClient 
  
' Chaîne de connexion 
Dim connectString As String = "database=equities;server=JV01;User ID=samfisher;pwd=echelon" 
' Objet connection 
Dim connection As SqlConnection = New SqlConnection(connectString) 
' Ouverture 
connection.Open() 
' Objet Command 
Dim command As SqlCommand = New SqlCommand("SELECT * FROM usr_contract " & _ 
"WHERE ctr_ref = @contract AND ctr_exg_ref = @exg", _ 
connection) 
' Paramètres 
command.Parameters.Add(New SqlParameter("@contract", SqlDbType.VarChar, 5)) 
command.Parameters("@contract").Value = "FTE" 
command.Parameters.Add(New SqlParameter("@exg", SqlDbType.VarChar, 8)) 
command.Parameters("@exg").Value = "SBF" 
' Object datareader 
Dim reader As SqlDataReader = command.ExecuteReader() 
Dim row As Object() 
While reader.Read() 
  
    If row Is Nothing Then 
    row = New Object(reader.FieldCount - 1) {} 
    End If 
    reader.GetValues(row) 
    For i As Integer = 0 To row.GetLength(0) - 1 
        If Not row(i) Is DBNull.Value Then 
            Console.Write(row(i)) 
        Else 
            Console.Write("NULL") 
        End If 
        If i < row.GetUpperBound(0) Then 
            Console.Write(" | ") 
        End If 
    Next 
    Console.WriteLine() 
End While 
' Fermeture reader 
reader.Close() 
' Fermeture base 
connection.Close()

1
2
3
4
5
6
7
' Exemple pour SQL SERVER 
' Objet SqlCommand 
Dim cmd As SqlCommand = New SqlCommand() 
' On indique que l'on souhaite utiliser une procédure stockée 
cmd.CommandType = CommandType.StoredProcedure 
' On donne le nom de cette procédure stockée 
cmd.CommandText = "CustOrderHist"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Imports System.Data.SqlClient 
  
Public Class EcrireXML 
  
    Public Shared Sub Main() 
  
        ' Création de la chaîne de connexion 
        Dim connectString As String = "Server=SRV01;Database=Northwind;User ID=samfisher;Password=echelon" 
        ' Création de la connexion 
        Dim connection As SqlConnection = New SqlConnection(connectString) 
  
        ' Création du SqlDataAdapter 
        Dim da As SqlDataAdapter = New SqlDataAdapter("Select * from Customers", connection) 
  
        ' Création d'un DataSet 
        Dim ds As DataSet = New DataSet 
  
        ' Remplissage du DataSet avec le SqlDataAdapter 
        da.Fill(ds, "Customers") 
  
        ' Écriture du fichier XML au moyen de la méthode WriteXml 
        ds.WriteXml("E:\Temp\TestXml.xml") 
  
    End Sub 
  
End Class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?xml version="1.0" standalone="yes" ?> 
  <NewDataSet> 
  <myTable> 
    <CustomerID>ALFKI</CustomerID> 
    <CompanyName>Alfreds Futterkiste</CompanyName> 
    <ContactName>Maria Anders</ContactName> 
    <ContactTitle>Sales Representative</ContactTitle> 
    <Address>Obere Str. 57</Address> 
    <City>Berlin</City> 
    <PostalCode>12209</PostalCode> 
    <Country>Germany</Country> 
    <Phone>030-0074321</Phone> 
    <Fax>030-0076545</Fax> 
</myTable>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
Imports Microsoft.Office.Interop 
  
' Dans un événement clic de bouton par exemple 
Dim xlApp As Excel.Application 
  
Try 
    xlApp = GetObject(, "excel.application") 
Catch 
    xlApp = New Excel.Application 
Finally 
    xlApp.Visible = False 
End Try 
  
' Ajout d'une page et sélection 
Dim xsTransfert As Excel.Worksheet = xlApp.Workbooks.Add.ActiveSheet 
  
Try 
    ' Ici on crée la chaine de connexion 
    ' (on se connecte à SQL Server dans notre exemple) 
    With xsTransfert.QueryTables.Add(Connection:= _ 
            "ODBC;DRIVER=SQL Server;SERVER= Nom_Server;Integrated 
            Security=SSPI;" & _ 
            "APP=Microsoft® Query;DATABASE= Nom_Base;Trusted_Connection=Yes", _ 
          Destination:=xsTransfert.Range("A1")) 
  
        .CommandText = "Nom_Procedure_stockée" ' ou requête SELECT 
        .Name = "nom_feuille_excel" 
        .FieldNames = True 
        .RowNumbers = True 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .BackgroundQuery = True 
        .RefreshStyle = Excel.XlCellInsertionMode.xlOverwriteCells 
        .SavePassword = False 
        .SaveData = False 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .PreserveColumnInfo = True 
        .Refresh(BackgroundQuery:=False) 
    End With 
  
    ' Affichage 
    xlApp.Visible = True 
Catch ex As Exception 
    MessageBox.Show(ex.Message) 
End Try

1
2
3
4
5
6
7
8
9
Dim maCommand As SqlCommand = New SqlCommand("SELECT * FROM MaTable")  
' Par défaut, CommandTimeout vaut 30 secondes 
  
Console.WriteLine("Temps avant Timeout : {0}", maCommand.CommandTimeout)  
' Met le Timeout à 10 secondes 
maCommand.CommandTimeout = 10 
Console.WriteLine("Temps avant Timeout : {0}", maCommand.CommandTimeout) 
' Permet de ne pas générer de Timeout 
maCommand.CommandTimeout = 0