DEV Community

Cover image for GOLANG- MSSQL CRUD OPERATIONS
Muhammet Tan
Muhammet Tan

Posted on

GOLANG- MSSQL CRUD OPERATIONS

In this tutorial, you will learn basic crud operations in golang.

Project structre looks like this.
Image description

Sql table design looks like this
Image description

After the creating pages and table, you need to download sql-golang framework with this github link
github.com/denisenkom/go-mssqldbImage description

As you see we have three different go code pages.
in main.go , we call just one function(CheckDbConn()) to keep our aplication simple and clean.In golang main function entry point of the application we develop. Calling just one function is sufficient right now.

In db.go
Image description
We need to fill this area to establish connection between our application and sql database.If you get connection error, you had better check sql username, password,sql port number.

Full code
db.go

package main

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

_ "github.com/denisenkom/go-mssqldb"
Enter fullscreen mode Exit fullscreen mode

)

var (
Server = "localhost"
Port = 1433
User = "sa"
Password = 1453
Db = "Products"
)

func CheckDbConn() {

var err error

ConnString := f.Sprintf("server=%s;user id=%s;password=%d;port=%d;database=%s;",
    Server, User, Password, Port, Db)

conn, err := sql.Open("sqlserver", ConnString)
if err != nil {
    log.Fatal("Open connection failed:", err.Error())
}
f.Printf("Connected!\n")
defer conn.Close()
option := 0
f.Println("0.GET \n1.INSERT \n2.UPDATE \n3.DELETE")
f.Scanln(&option)
switch option {
case 0:
    GetProducts(conn)
case 1:
     result,_:= CreateProduct(conn)

    f.Println(result)
case 2:
    UpdateProduct(conn)
case 3:
    DeleteProduct(conn)
default:
    f.Println("Invalid operation request")
}
Enter fullscreen mode Exit fullscreen mode

}

db.handler.go

package main

import (
"database/sql"
f "fmt"
"strings"
)

func GetProducts(db *sql.DB) (int, error) {

getProduct_sql := "select * from Products"

rows, err := db.Query(getProduct_sql)
if err != nil {
    f.Println("Error reading records: ", err.Error())
}
defer rows.Close()

count := 0
for rows.Next() {
    var name string
    var price float64
    var id int
    err := rows.Scan(&id, &name, &price)
    if err != nil {
        f.Println("Error reading rows: " + err.Error())
        return -1, err
    }
    f.Printf("ID: %d, Name: %s, Price: %f\n", id, name, price)
    count++
}
return count, nil
Enter fullscreen mode Exit fullscreen mode

}

func CreateProduct(db *sql.DB)(int64,error){

var name string
f.Print("Please enter your product name: ")
f.Scanln(&name)

var price float64
f.Print("Please enter your product's price: ")
f.Scanln(&price)

insertProduct_sql := f.Sprintf("INSERT INTO Products (name,price) VALUES ('%s' , %f ); select ID = convert(bigint, SCOPE_IDENTITY()) ",strings.Title(strings.ToLower(name)),price)


rows,err:=db.Query(insertProduct_sql)
if err !=nil{
    f.Println("Error occured while inserting a record", err.Error())
    return -1,err
}


defer rows.Close()
var lastInsertId1 int64
for rows.Next() {
    rows.Scan(&lastInsertId1)

}


return lastInsertId1,err
Enter fullscreen mode Exit fullscreen mode

}

func InfoMsG(db *sql.DB,id int64) {
infoQuery:=f.Sprintf("Select name from Products where id=%d",id)
rows,err := db.Query(infoQuery)
if err !=nil{
f.Println("Error occured while giving info: ", err.Error())
}
defer rows.Close()

for rows.Next(){
    var name string
    var id =id
    err:=rows.Scan(&name)
    if err !=nil {
        f.Println("Error reading end process product id with, " , id, err)
    }else{
        f.Printf(name + " product has been created " )
    }



}
Enter fullscreen mode Exit fullscreen mode

}

func UpdateProduct(db *sql.DB) {
f.Print("Please enter product id which you want to change: ")
var id int
f.Scanln(&id)

f.Print("Please enter new product name ")
var name string
f.Scanln(&name)

f.Print("Please enter new product'price ")
var price float64
f.Scanln(&price)

update_query := f.Sprintf("UPDATE Products set name='%s', price=%f where id=%d",name,price,id)

_, err := db.Exec(update_query)
if err != nil {
    f.Println("Failed: " + err.Error())
}
f.Println("Product informations updated successfully")
Enter fullscreen mode Exit fullscreen mode

}

func DeleteProduct(db *sql.DB){
f.Print("Please enter product id which you want to delete: ")
var id int
f.Scanln(&id)

delete_query:=f.Sprintf("DELETE FROM Products where id=%d",id)
_, err := db.Exec(delete_query)
if err != nil {
    f.Println("Failed: " + err.Error())
}
f.Println("Product deleted successfully")
Enter fullscreen mode Exit fullscreen mode

}

main.go
package main

func main() {
CheckDbConn()
}

Discussion (0)