Using SQL Server in Go: A Complete Guide

With the widespread use of enterprise-level applications, SQL Server has also become one of the most widely used databases in enterprise-level applications. Moreover, with the popularity of Go language, more and more developers hope to use SQL Server in Go language. In this article, we will introduce how to use SQL Server in Go language, involving a complete guide to connecting, querying, reading and writing data, etc.

Connect to SQL Server database

To connect to SQL Server database, we need to use the SQL Server driver provided by Microsoft (ie mssql). This driver is very easy to use, so let's install it.

Install SQL Server Driver

To install the mssql driver, we need to run the following command in the terminal:

go get github.com/denisenkom/go-mssqldb

This command will download and install the mssql driver.

Connect to the database

Once the driver is installed, we can connect to the SQL Server database. The connection requires us to provide the server address of the database, database name, username and password. The following is a basic example of a connection:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/denisenkom/go-mssqldb"
)

func main() {
    server := "localhost\SQLEXPRESS"
    port := 1433
    user := "sa"
    password := "password123"
    database := "MyDatabase"

    connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;",
        server, user, password, port, database)
    db, err := sql.Open("mssql", connString)

    if err != nil {
        panic(err)
    }

    defer db.Close()

    // Connection successful. We can now start working with the database.
    fmt.Println("Connection successful.")
}

In this example, we specify the server address of the database as "localhostSQLEXPRESS", the port as 1433, the user name as sa, the password as password123, and the data name as MyDatabase. The connection string is formatted in "My example connection string" format. We use this string to open the connection and provide a simple message as confirmation after the connection is successful.

Querying data

Once the connection is successful, we can start using SQL commands to query data from the database. We can use db.Query() to execute the query statement and rows.Scan() to scan each row of data in the query results. The following is a basic query example:

func main() {
    server := "localhost\SQLEXPRESS"
    port := 1433
    user := "sa"
    password := "password123"
    database := "MyDatabase"

    connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;",
        server, user, password, port, database)

    db, err := sql.Open("mssql", connString)
    if err != nil {
        panic(err)
    }

    defer db.Close()

    // Query the database.
    query := "SELECT Name, Email FROM Customers WHERE Country = 'USA'"
    rows, err := db.Query(query)

    if err != nil {
        panic(err)
    }

    defer rows.Close()

    // Scan the results.
    for rows.Next() {
        var name string
        var email string
        err = rows.Scan(&name, &email)

        if err != nil {
            panic(err)
        }

        fmt.Println(name, email)
    }
}

In this example, we query the table named "Customers", which is located in the MyDatabase database. We only selected the rows with columns named "Name" and "Email" where "Country" is listed as "USA".

When we execute a query, the results will be stored in the query variable. We then use db.Query() to execute the query. It returns a result set, which we scan using rows.Scan(). Finally, we loop through the returned results and print the data for each row.

Writing Data

When using SQL Server in Go language, writing new data is very similar to reading data. To write data, we simply execute an INSERT, UPDATE, or DELETE statement using db.Exec(). The following is a basic write example:

func main() {
    server := "localhost\SQLEXPRESS"
    port := 1433
    user := "sa"
    password := "password123"
    database := "MyDatabase"

    connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;",
        server, user, password, port, database)

    db, err := sql.Open("mssql", connString)
    if err != nil {
        panic(err)
    }

    defer db.Close()

    // Insert a new row.
    statement := "INSERT INTO Customers (Name, Email, Country) VALUES (?, ?, ?)"
    result, err := db.Exec(statement, "John Doe", "johndoe@example.com", "USA")

    if err != nil {
        panic(err)
    }

    rowsAffected, err := result.RowsAffected()
    if err != nil {
        panic(err)
    }

    // Write successful. Display the number of rows affected.
    fmt.Printf("%d rows affected.
", rowsAffected)
}

In this example, we execute an INSERT statement to insert a row of data into the table named "Customers". We specified the name "John Doe", the email address "johndoe@example.com", and the country "USA". We use db.Exec() to write the data and fmt.Printf() to print the results.

Summary

In this article, we introduced how to use SQL Server in Go language. We learned how to connect to the database, execute queries and write data. We have also seen that the process of using SQL Server with Go language is very simple. Therefore, SQL Server has become one of the preferred data storage solutions for enterprise-level applications. If you are a Go developer and you want to use SQL Server in your next project, this article will provide you with a very useful guide.

The above is the detailed content of Using SQL Server in Go: A Complete Guide. For more information, please follow other related articles on the PHP Chinese website!