MSSQL #

Microsoft SQL Server (MSSQL) is an enterprise database widely used in corporate environments, especially on Windows and .NET stacks. Dart can connect to MSSQL through the mssql_connection package, which implements the TDS (Tabular Data Stream) protocol — SQL Server’s native protocol — directly without needing an ODBC driver or native library. This article covers connections, queries, transactions, stored procedures, and the important differences between T-SQL (SQL Server) and MySQL that you need to watch out for.

Package Setup #

dart pub add mssql_connection

# pubspec.yaml
dependencies:
  mssql_connection: ^0.0.8

The mssql_connection package implements the TDS protocol natively in Dart. No ODBC driver or SQL Server Native Client installation needed on the Dart machine. Just make sure SQL Server is reachable from that machine (firewall, TCP/IP enabled).

Connecting to SQL Server #

import 'package:mssql_connection/mssql_connection.dart';

Future<void> main() async {
  final conn = MssqlConnection.getInstance();

  // Connection with SQL Server Authentication
  final berhasil = await conn.connect(
    ip: 'localhost',        // or server name: 'SERVER\INSTANCE'
    port: '1433',           // SQL Server default port
    databaseName: 'TokoDB',
    username: 'sa',
    password: 'P@ssw0rd!',
    timeoutInSeconds: 15,
  );

  if (!berhasil) {
    throw Exception('Failed to connect to SQL Server');
  }
  print('Connected to SQL Server');

  // Close the connection when done
  await conn.disconnect();
}

Connecting with Windows Authentication #

For Windows domain environments, use Windows Authentication (Integrated Security):

// Windows Authentication — username and password empty
// The server must be configured to accept Windows Auth
final berhasil = await conn.connect(
  ip: 'sql-server.corp.example.com',
  port: '1433',
  databaseName: 'CorporateDB',
  username: '',    // empty for Windows Auth
  password: '',    // empty for Windows Auth
  timeoutInSeconds: 30,
);

Connecting to a Named Instance #

SQL Server is often configured with a named instance (not the default instance):

// Named instance — use a backslash
// Example: SERVER\SQLEXPRESS, SERVER\MSSQLSERVER2019
final berhasil = await conn.connect(
  ip: r'SERVER\SQLEXPRESS',  // raw string for the backslash
  port: '1433',
  databaseName: 'MyDB',
  username: 'dbuser',
  password: 'password',
);

Basic Queries #

import 'package:mssql_connection/mssql_connection.dart';
import 'dart:convert';

Future<void> contohQuery(MssqlConnection conn) async {
  // SELECT — returns a JSON string
  final jsonHasil = await conn.getData('SELECT TOP 10 * FROM Pengguna');

  // Parse the JSON into a List
  final List<dynamic> baris = jsonDecode(jsonHasil);
  for (final row in baris) {
    print('ID: ${row['Id']}');
    print('Nama: ${row['Nama']}');
    print('Email: ${row['Email']}');
  }

  // getData with conditions — ALWAYS use parameterized!
  // See the parameterized query section below
}

MssqlConnection.getData() returns a String in JSON format, not a list of objects directly. You need jsonDecode() to convert it to a List<dynamic>. Each element is a Map<String, dynamic> with column names as keys.

Parameterized Queries — Mandatory for Security #

The mssql_connection package uses @namaParam syntax for parameters (unlike MySQL’s :namaParam):

import 'package:mssql_connection/mssql_connection.dart';
import 'dart:convert';

Future<void> contohParameterized(MssqlConnection conn) async {
  // ANTI-PATTERN: direct string interpolation — SQL injection!
  final inputBerbahaya = "'; DROP TABLE Pengguna; --";
  await conn.getData(
    "SELECT * FROM Pengguna WHERE Nama = '$inputBerbahaya'",
  ); // ✗ extremely dangerous!

  // CORRECT: parameterized query with @namaParam (T-SQL syntax)
  final namaInput = "'; DROP TABLE Pengguna; --";
  final jsonHasil = await conn.getData(
    'SELECT * FROM Pengguna WHERE Nama = @nama',
    params: {'@nama': namaInput},  // safe — the driver handles escaping
  );

  // Multiple parameters
  final produk = await conn.getData(
    'SELECT * FROM Produk WHERE Kategori = @kat AND Harga <= @maks AND Aktif = 1',
    params: {'@kat': 'Elektronik', '@maks': 5000000},
  );

  // LIKE with a parameter
  final kata = 'laptop';
  final cari = await conn.getData(
    'SELECT * FROM Produk WHERE Nama LIKE @keyword',
    params: {'@keyword': '%$kata%'},
  );

  final daftar = jsonDecode(produk) as List;
  print('Found ${daftar.length} products');
}

T-SQL Syntax Differences from MySQL #

SQL Server uses T-SQL, which has several important differences from MySQL:

-- MySQL                          | T-SQL (SQL Server)
-- --------------------------------|--------------------------------
-- LIMIT 10                       | TOP 10 (in SELECT)
-- AUTO_INCREMENT                 | IDENTITY(1,1)
-- `backtick`                     | [bracket] or "double quote"
-- IFNULL()                       | ISNULL() or COALESCE()
-- NOW()                          | GETDATE() or SYSDATETIME()
-- DATE_FORMAT()                  | FORMAT() or CONVERT()
-- GROUP_CONCAT()                 | STRING_AGG()
-- INSERT IGNORE                  | INSERT ... WHERE NOT EXISTS
-- ON DUPLICATE KEY UPDATE        | MERGE statement
-- SHOW TABLES                    | SELECT * FROM sys.tables

// T-SQL query examples that differ from MySQL

// Pagination with OFFSET-FETCH (SQL Server 2012+)
final halaman = 1;
final perHalaman = 20;
final offset = (halaman - 1) * perHalaman;

final paginasi = await conn.getData(
  'SELECT Id, Nama, Harga FROM Produk '
  'ORDER BY Nama '
  'OFFSET @offset ROWS '
  'FETCH NEXT @perHalaman ROWS ONLY',
  params: {'@offset': offset, '@perHalaman': perHalaman},
);

// TOP N rows
final topProduk = await conn.getData(
  'SELECT TOP 5 Id, Nama, TotalTerjual FROM Produk ORDER BY TotalTerjual DESC',
);

// Dates with GETDATE()
final hariIni = await conn.getData(
  'SELECT * FROM Order WHERE CAST(TanggalOrder AS DATE) = CAST(GETDATE() AS DATE)',
);

// String aggregation (replacement for GROUP_CONCAT)
final tags = await conn.getData(
  'SELECT ProdukId, STRING_AGG(Tag, \',\') AS Tags '
  'FROM ProdukTag GROUP BY ProdukId',
);

Full CRUD #

import 'package:mssql_connection/mssql_connection.dart';
import 'dart:convert';

class ProdukRepository {
  final MssqlConnection _conn;

  ProdukRepository(this._conn);

  // CREATE — returns the created ID
  Future<int> tambah({
    required String nama,
    required double harga,
    required int stok,
    required String kategori,
  }) async {
    // OUTPUT INSERTED.Id — T-SQL way to get the newly inserted ID
    final result = await _conn.getData(
      'INSERT INTO Produk (Nama, Harga, Stok, Kategori, Aktif, DibuatPada) '
      'OUTPUT INSERTED.Id '
      'VALUES (@nama, @harga, @stok, @kat, 1, GETDATE())',
      params: {
        '@nama': nama,
        '@harga': harga,
        '@stok': stok,
        '@kat': kategori,
      },
    );

    final rows = jsonDecode(result) as List;
    return rows.first['Id'] as int;
  }

  // READ all with pagination
  Future<List<Map<String, dynamic>>> ambilSemua({
    int halaman = 1,
    int perHalaman = 20,
  }) async {
    final offset = (halaman - 1) * perHalaman;
    final result = await _conn.getData(
      'SELECT Id, Nama, Harga, Stok, Kategori '
      'FROM Produk '
      'WHERE Aktif = 1 '
      'ORDER BY DibuatPada DESC '
      'OFFSET @offset ROWS FETCH NEXT @per ROWS ONLY',
      params: {'@offset': offset, '@per': perHalaman},
    );
    return (jsonDecode(result) as List).cast<Map<String, dynamic>>();
  }

  // READ one
  Future<Map<String, dynamic>?> ambilById(int id) async {
    final result = await _conn.getData(
      'SELECT * FROM Produk WHERE Id = @id AND Aktif = 1',
      params: {'@id': id},
    );
    final rows = jsonDecode(result) as List;
    return rows.isEmpty ? null : rows.first as Map<String, dynamic>;
  }

  // UPDATE — writeData for non-SELECT operations
  Future<bool> perbarui(int id, {String? nama, double? harga, int? stok}) async {
    final fields = <String>[];
    final params = <String, dynamic>{'@id': id};

    if (nama != null) { fields.add('Nama = @nama'); params['@nama'] = nama; }
    if (harga != null) { fields.add('Harga = @harga'); params['@harga'] = harga; }
    if (stok != null) { fields.add('Stok = @stok'); params['@stok'] = stok; }

    if (fields.isEmpty) return false;

    // Add DiubahPada
    fields.add('DiubahPada = GETDATE()');

    final rowsAffected = await _conn.writeData(
      'UPDATE Produk SET ${fields.join(', ')} WHERE Id = @id',
      params: params,
    );
    return rowsAffected > 0;
  }

  // DELETE (soft delete)
  Future<bool> hapus(int id) async {
    final rowsAffected = await _conn.writeData(
      'UPDATE Produk SET Aktif = 0, DiubahPada = GETDATE() WHERE Id = @id',
      params: {'@id': id},
    );
    return rowsAffected > 0;
  }
}

Stored Procedures #

SQL Server very commonly uses stored procedures for database-side business logic:

import 'package:mssql_connection/mssql_connection.dart';
import 'dart:convert';

// Calling a stored procedure with EXEC
Future<List<Map<String, dynamic>>> panggilSP(
  MssqlConnection conn,
  String namaSP,
  Map<String, dynamic> params,
) async {
  // Build the parameter string for EXEC
  final paramStr = params.keys.map((k) => '$k = $k').join(', ');
  final query = 'EXEC $namaSP $paramStr';

  final result = await conn.getData(query, params: params);
  return (jsonDecode(result) as List).cast<Map<String, dynamic>>();
}

// A stored procedure returning data
Future<void> contohStoredProcedure(MssqlConnection conn) async {
  // Assumption: CREATE PROCEDURE sp_CariProduk
  //   @Keyword NVARCHAR(100),
  //   @HargaMaks DECIMAL(18,2)
  // AS BEGIN
  //   SELECT * FROM Produk WHERE Nama LIKE '%' + @Keyword + '%' AND Harga <= @HargaMaks
  // END

  final produk = await conn.getData(
    'EXEC sp_CariProduk @Keyword = @keyword, @HargaMaks = @maks',
    params: {'@keyword': 'laptop', '@maks': 10000000},
  );

  final list = jsonDecode(produk) as List;
  for (final p in list) {
    print('${p['Nama']}: Rp${p['Harga']}');
  }
}

// An INSERT/UPDATE stored procedure — use writeData
Future<int> buatPenggunaViaSP(MssqlConnection conn, String nama, String email) async {
  // Using OUTPUT parameters via EXEC
  final result = await conn.getData(
    'EXEC sp_BuatPengguna @Nama = @nama, @Email = @email',
    params: {'@nama': nama, '@email': email},
  );
  final rows = jsonDecode(result) as List;
  return rows.isEmpty ? 0 : rows.first['IdBaru'] as int;
}

Transactions #

import 'package:mssql_connection/mssql_connection.dart';
import 'dart:convert';

Future<void> transferSaldo(
  MssqlConnection conn,
  int idDari,
  int idKe,
  double jumlah,
) async {
  // Start the transaction manually with T-SQL
  await conn.writeData('BEGIN TRANSACTION');

  try {
    // Check the sender's balance
    final saldoResult = await conn.getData(
      'SELECT Saldo FROM Rekening WITH (UPDLOCK) WHERE Id = @id',
      params: {'@id': idDari},
    );
    final rows = jsonDecode(saldoResult) as List;

    if (rows.isEmpty) throw Exception('Sender account not found');

    final saldo = (rows.first['Saldo'] as num).toDouble();
    if (saldo < jumlah) throw Exception('Insufficient balance');

    // Decrease the sender's balance
    await conn.writeData(
      'UPDATE Rekening SET Saldo = Saldo - @jumlah WHERE Id = @id',
      params: {'@jumlah': jumlah, '@id': idDari},
    );

    // Increase the recipient's balance
    await conn.writeData(
      'UPDATE Rekening SET Saldo = Saldo + @jumlah WHERE Id = @id',
      params: {'@jumlah': jumlah, '@id': idKe},
    );

    // Record the transfer log
    await conn.writeData(
      'INSERT INTO LogTransfer (IdDari, IdKe, Jumlah, TanggalWaktu) '
      'VALUES (@dari, @ke, @jumlah, GETDATE())',
      params: {'@dari': idDari, '@ke': idKe, '@jumlah': jumlah},
    );

    // Commit if everything succeeded
    await conn.writeData('COMMIT TRANSACTION');
    print('Transfer successful: Rp${jumlah.toStringAsFixed(0)} from $idDari to $idKe');

  } catch (e) {
    // Rollback if anything failed
    await conn.writeData('ROLLBACK TRANSACTION');
    print('Transfer failed, rolled back: $e');
    rethrow;
  }
}

Mapping to Model Classes #

MSSQL values can be int, double, String, or bool — unlike MySQL, where everything is a String. Use the right casts:

class Produk {
  final int id;
  final String nama;
  final double harga;
  final int stok;
  final bool aktif;
  final DateTime dibuatPada;

  const Produk({
    required this.id,
    required this.nama,
    required this.harga,
    required this.stok,
    required this.aktif,
    required this.dibuatPada,
  });

  factory Produk.dariJson(Map<String, dynamic> json) {
    return Produk(
      // MSSQL can return native types — check with is/as
      id: json['Id'] is int ? json['Id'] as int : int.parse(json['Id'].toString()),
      nama: json['Nama'] as String? ?? '',
      harga: json['Harga'] is double
          ? json['Harga'] as double
          : (json['Harga'] as num).toDouble(),
      stok: json['Stok'] is int ? json['Stok'] as int : int.parse(json['Stok'].toString()),
      aktif: json['Aktif'] == true || json['Aktif'] == 1,
      // SQL Server datetime format: "2024-11-15T14:30:00"
      dibuatPada: DateTime.parse(json['DibuatPada'] as String),
    );
  }
}

// Usage
Future<List<Produk>> ambilProduk(MssqlConnection conn) async {
  final result = await conn.getData(
    'SELECT Id, Nama, Harga, Stok, Aktif, DibuatPada FROM Produk WHERE Aktif = 1',
  );
  final list = jsonDecode(result) as List;
  return list.map((json) => Produk.dariJson(json as Map<String, dynamic>)).toList();
}

Error Handling and Connections #

import 'package:mssql_connection/mssql_connection.dart';

class DatabaseMSSQL {
  static MssqlConnection? _instance;
  static bool _terhubung = false;

  static Future<MssqlConnection> dapatkan({
    required String host,
    required String database,
    required String username,
    required String password,
  }) async {
    if (_instance != null && _terhubung) return _instance!;

    _instance = MssqlConnection.getInstance();

    try {
      _terhubung = await _instance!.connect(
        ip: host,
        port: '1433',
        databaseName: database,
        username: username,
        password: password,
        timeoutInSeconds: 15,
      );

      if (!_terhubung) {
        throw Exception('Failed to connect to SQL Server: $host/$database');
      }

      return _instance!;
    } catch (e) {
      _terhubung = false;
      rethrow;
    }
  }

  static Future<void> tutup() async {
    if (_instance != null && _terhubung) {
      await _instance!.disconnect();
      _terhubung = false;
    }
  }

  // Reconnect if the connection drops
  static Future<T> denganReconnect<T>(
    Future<T> Function(MssqlConnection) operasi,
    MssqlConnection conn,
  ) async {
    try {
      return await operasi(conn);
    } catch (e) {
      if (e.toString().contains('connection') || e.toString().contains('timeout')) {
        print('Connection lost, trying to reconnect...');
        _terhubung = false;
        // Reconnect
        final newConn = await dapatkan(
          host: 'localhost',
          database: 'TokoDB',
          username: 'sa',
          password: 'password',
        );
        return await operasi(newConn);
      }
      rethrow;
    }
  }
}

MSSQL vs MySQL in Dart #

Aspect	MySQL (`mysql_client`)	MSSQL (`mssql_connection`)
Parameter placeholder	`:namaParam`	`@namaParam`
Query result	`ResultSet` with `.rows`	JSON string, needs `jsonDecode`
INSERT + ID	`result.lastInsertID`	`OUTPUT INSERTED.Id` in the query
Rows affected	`result.affectedRows`	return from `writeData()`
Pagination	`LIMIT n OFFSET m`	`OFFSET m ROWS FETCH NEXT n ROWS ONLY`
Top N	`LIMIT n`	`TOP n`
Current date	`NOW()`	`GETDATE()`
Transactions	`conn.transactional()`	Manual `BEGIN/COMMIT/ROLLBACK TRANSACTION`
Result types	All `String?`	Native (int, double, bool, String)

Summary #

MssqlConnection.getInstance() returns a singleton — no need to create a new instance every time, but make sure to reconnect if the connection drops.
getData() for SELECT (returns a JSON string), writeData() for INSERT/UPDATE/DELETE (returns rows affected).
Always jsonDecode() the getData() result — it returns a JSON String, not objects directly.
Parameters use @namaParam (T-SQL syntax), unlike MySQL’s :namaParam.
OUTPUT INSERTED.Id in an INSERT to get the newly inserted ID — the replacement for MySQL’s lastInsertID.
T-SQL pagination: OFFSET @offset ROWS FETCH NEXT @per ROWS ONLY with a mandatory ORDER BY — unlike MySQL’s LIMIT.
Stored procedures are called with EXEC namaSP @param1 = @nilai1 — common in SQL Server enterprise environments.
Transactions must be managed manually with BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION — there’s no helper like MySQL’s transactional().
MSSQL data types can be native (int, double, bool) — unlike MySQL, which is always String. Use is checks before casting.
SQL Server datetime results are in ISO format: "2024-11-15T14:30:00" — can be parsed directly with DateTime.parse().

← Previous: MySQL Next: Oracle →