关于golang:GO使用sql-server

参考资料
[1] Go 语言中查问 SqlServer 数据库
[2] gorm 连贯到数据库

1. gorm 的形式

package dao

import (
    "fmt"
    "gorm.io/driver/sqlserver"
    "gorm.io/gorm"
    "testing"
)

type User struct {
    Userid int64  `gorm:"column:userid"`
    Name   string `gorm:"column:name"`
    Age    int64  `gorm:"column:age"`
}

func TestGorm(t *testing.T) {
    dsn := "sqlserver://sa:123456@DESKTOP-HMTA87I:1433?database=wzz"
    db, err := gorm.Open(sqlserver.Open(dsn), &gorm.Config{})
    if err != nil {panic(err)
    }

    var users []User
    db.Table("dbo.users").Limit(10).Order("userid asc").Find(&users)

    for _, u := range users {fmt.Println(u)
    }
}

2.sql 形式

文件 dao/connect_test.go 内容：

package dao

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

func TestConnect(t *testing.T) {
    var isdebug = true
    var server = "DESKTOP-HMTA87I"
    var port = 1433
    var user = "sa"
    var password = "123456"
    var database = "wzz"

    // 连贯字符串
    connString := fmt.Sprintf("server=%s;port%d;database=%s;user id=%s;password=%s", server, port, database, user, password)
    if isdebug {fmt.Println(connString)
    }
    // 建设连贯
    conn, err := sql.Open("mssql", connString)
    if err != nil {t.Fatal("Open Connection failed:", err.Error())
    }
    defer conn.Close()
    t.Log("连贯胜利！")
    // 产生查问语句的 Statement
    stmt, err := conn.Prepare(`select * from dbo.users`)
    if err != nil {t.Fatal("Prepare failed:", err.Error())
    }
    rows, err := stmt.Query()
    if err != nil {t.Fatal("Query failed:", err.Error())
    }
    defer stmt.Close()

    // 建设一个列数组
    cols, err := rows.Columns()
    var colsdata = make([]interface{}, len(cols))
    for i := 0; i < len(cols); i++ {colsdata[i] = new(interface{})
        fmt.Print(cols[i])
        fmt.Print("\t")
    }
    fmt.Println()

    // 遍历每一行
    for rows.Next() {rows.Scan(colsdata...) // 将查到的数据写入到这行中
        PrintRow(colsdata)     // 打印此行
    }
    defer rows.Close()}

// 打印一行记录，传入一个行的所有列信息
func PrintRow(colsdata []interface{}) {
    for _, val := range colsdata {switch v := (*(val.(*interface{}))).(type) {
        case nil:
            fmt.Print("NULL")
        case bool:
            if v {fmt.Print("True")
            } else {fmt.Print("False")
            }
        case []byte:
            fmt.Print(string(v))
        case time.Time:
            fmt.Print(v.Format("2016-01-02 15:05:05.999"))
        default:
            fmt.Print(v)
        }
        fmt.Print("\t")
    }
    fmt.Println()}

3. 应用实体实现的办法

type AccessRegion struct {
    userid int64
    name   string
    age    int64
}

func TestAccess(t *testing.T) {
    var server = "DESKTOP-HMTA87I"
    var port = 1433
    var user = "sa"
    var password = "123456"
    var database = "wzz"

    // 连贯字符串
    connString := fmt.Sprintf("server=%s;port%d;database=%s;user id=%s;password=%s", server, port, database, user, password)

    // 建设连贯
    db, err := sql.Open("mssql", connString)
    if err != nil {t.Fatal("Open Connection failed:", err.Error())
    }
    defer db.Close()

    // 通过连贯对象执行查问
    rows, err := db.Query(`select * from dbo.users`)
    if err != nil {t.Fatal("Query failed:", err.Error())
    }
    defer rows.Close()

    var rowsData []*AccessRegion
    // 遍历每一行
    for rows.Next() {var row = new(AccessRegion)
        rows.Scan(&row.userid, &row.name, &row.age)
        rowsData = append(rowsData, row)
    }

    // 打印数组
    for _, ar := range rowsData {fmt.Print(ar.userid, "\t", ar.name, "\t", ar.age)
        fmt.Println()}
}