GOLANG- MSSQL CRUD OPERATIONS
Tags: go, sql, crud
Published: 2021-11-20 13:55:47

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


Project structre looks like this.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yaue5nhctwupffex419f.png)

Sql table design looks like this
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/us700wc4rsznk9mk4z0b.png)

After the creating pages and table, you need to download sql-golang framework with this github link
 github.com/denisenkom/go-mssqldb![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nz2dw0rn3f97d9nt1vek.png)
 
 

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/94lxms3erud1zo7cfoh8.png)
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"
)

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")
	}
}


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
}

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
}

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 " )
		}



	}


}



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")
}

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")
}

main.go 
package main

func main() {
	CheckDbConn()
}