Go语言中查询SqlServer数据库

main.go package main import ( "database/sql" "fmt" "log" "time" _ "github.com/denisenkom/go-mssqldb" ) func main() { var isdebug = true var server = "localhost" var port = 1433 var user = "sa" var password = "123456xx" var database = "MyTestDB" //连接字符串 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 { log.Fatal("Open Connection failed:", err.Error()) } defer conn.Close() //产生查询语句的Statement stmt, err := conn.Prepare(`select * from [account_region]`) if err != nil { log.Fatal("Prepare failed:", err.Error()) } defer stmt.Close() //通过Statement执行查询 rows, err := stmt.Query() if err != nil { log.Fatal("Query failed:", err.Error()) } //建立一个列数组 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() }

二、效果：

　　

server=localhost;port1433;database=MyTestDB;user id=sa;password=123456xx
region_id    provider_id    region_name    billing_region_name    description    
1    5    us-east-1    US-EAST    AWS US EAST Data Center    
2    5    us-west-2    US_WEST_OREGON    AWS Oregon Data Center    
3    5    ap-southeast-1    ASIA_SIGN    AWS Singapore Data Center    
4    5    ap-southeast-2    ASIA_SYDENY    AWS Sydney Data Center    
5    5    ap-northeast-1    ASIA_TOKYO    AWS Tokyo Data Center    
6    5    eu-central-1    EU_FRANKFURT    AWS Frankfurt Data Center    
7    5    eu-west-1    EU_IRELAND    AWS Europe Data Center    
8    5    us-west-1    US_WEST_CA    AWS CA Data Center    
9    5    sa-east-1    SOUA_SAOP    AWS Sao Paulo Data Center    
10    5    ap-northeast-2    ASIA_SEOUL    AWS Seoul Data Center    
11    5    ap-south-1    ASIA_MUMBAI    AWS Mumbai Data Center    
12    2    Central US    US-CENTRAL    Azure Center US Data Center    
13    2    North Central US    US-NORTH-CENTRAL    Azure North US Data Center    
14    2    East US    US-EAST    Azure East US Data Center    
15    2    South Central US    US-SOUTH-CENTRAL    Azure South US Data Center    
16    2    West US    US-WEST    Azure West US Data Center    
17    2    North Europe    EUROPE-NORTH    Azure North Europe Data Center    
18    2    West Europe    EUROPE-WEST    Azure North Europe Data Center    
19    2    East Asia    ASIA-PACIFIC-EAST    Azure East Aisa Data Center    
20    2    Southeast Asia    ASIA-PACIFIC-SOUTHEAST    Azure Singapore Data Center    
21    2    Japan East    JAPAN-EAST    Azure East Japan Data Center    
22    2    Japan West    JAPAN-WEST    Azure West Japan Data Center    
23    2    Brazil South    BRAZIL-SOUTH    Azure Sao Paulo Data Center    
24    2    Australia East    AUSTRALIA-EAST    Azure East Australia Data Center    
25    2    Australia Southeast    AUSTRALIA-SOUTHEAST    Azure Southeast Australia Data Center    
26    2    East US 2    US-EAST-2    Azure East US Data Center 2    
27    2    US Gov Virginia    USGOV-VIRGINIA    Azure US Virginia Government Data Center    
28    2    US Gov Iowa    USGOV-IOWA    Azure US Iowa Government Data Center    
29    2    Canada Central    CANADA-CENTRAL    Azure Central Canada    
30    2    Canada East    CANADA-EAST    Azure East Canada    
31    2    Germany Central    GERMANY-CENTRAL    Azure Central Germany    
32    2    Germany Northeast    GERMANY-NORTHEAST    Azure Northeast Germany    
33    2    Korea Central    KOREA-CENTRAL    Azure Central Korea    
34    3    China North    CN-BEIJING    Azure Mooncake Beijing Data Center    
35    3    China East    CN-SHANGHAI    Azure Mooncake Shanghai Data Center    
36    4    cn-hangzhou    CN_HANGZHOU    Aliyun Hangzhou Data Center    
37    4    cn-beijing    CN_BEIJING    Aliyun Beijing Data Center    
38    4    cn-shenzhen    CN_SHENZHEN    Aliyun Shenzhen Data Center    
39    4    cn-qingdao    CN_QINGDAO    Aliyun Qingdao Data Center    
40    4    cn-hongkong    HONGKONG    Aliyun Hong Kong Data Center    
41    4    us-silicon-valley    US-Silicon_Valley    Aliyun Silicon Valley Data Center    

 三、使用实体实现的方法：

// main.go
package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/denisenkom/go-mssqldb"
)

type AccessRegion struct {
    region_id           int64
    provider_id         int64
    region_name         string
    sub_region_names    string
    billing_region_name string
    description         string
}

func main() {
    var server = "localhost"
    var port = 1433
    var user = "sa"
    var password = "123456xxx"
    var database = "MyTestDB"

    //连接字符串
    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 {
        log.Fatal("Open Connection failed:", err.Error())
    }
    defer db.Close()

    //通过连接对象执行查询
    rows, err := db.Query(`select * from [account_region]`)
    if err != nil {
        log.Fatal("Query failed:", err.Error())
    }
    defer rows.Close()

    var rowsData []*AccessRegion
    //遍历每一行
    for rows.Next() {
        var row = new(AccessRegion)
        rows.Scan(&row.region_id, &row.provider_id, &row.region_name, &row.billing_region_name, &row.description)
        rowsData = append(rowsData, row)
    }

    //打印数组
    for _, ar := range rowsData {
        fmt.Print(ar.region_id, "\t", ar.provider_id, "\t", ar.region_name, "\t", ar.billing_region_name, "\t", ar.description)
        fmt.Println()
    }
}

 

Go语言中查询SqlServer数据库

标签：