ISB VIETNAM CO.,LTD.

TECH

November 28, 2024

Calling REST API From SQL Server Stored Procedure

Besides the usual way of calling API from Website or Application, we can call API from SQL Server Stored Process. In this post, I would like to introduce how to call an API from a SQL Server stored procedure by a few steps.

SQL Server doesn't have built-in functionality to directly make HTTP requests, so you'll typically use SQL Server's sp_OACreate and related procedures to interact with COM objects for HTTP requests.

Example using sp_OACreate
Here's a simplified example of how you might use sp_OACreate to call an API from a stored procedure. Please note that this approach relies on the SQL Server's ability to interact with COM objects and may be limited or require additional configuration.

Steps:

1. Enable OLE Automation Procedures:

Before using sp_OACreate, you need to make sure that OLE Automation Procedures are enabled on your SQL Server instance.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;EXEC sp_configure 'ole automation procedures', 1;
RECONFIGURE;

2. Create the Stored Procedure

Here's an example stored procedure that performs a simple HTTP GET request to an API endpoint.

CREATE PROCEDURE CallApiExample

AS

BEGIN

DECLARE @object INT;

DECLARE @responseText VARCHAR(5000); -- Shoudn't use VARCHAR(MAX)

DECLARE @url VARCHAR(255) = 'https://northwind.vercel.app/api/categories'; -- Replace with your API URL

DECLARE @status INT;

-- Create the XMLHTTP object

EXEC sp_OACreate 'MSXML2.XMLHTTP', @object OUTPUT;

-- Open the HTTP connection

EXEC sp_OAMethod @object, 'open', NULL, 'GET', @url, 'false';

-- Send the request

EXEC sp_OAMethod @object, 'send';

-- Get the response text

EXEC sp_OAMethod @object, 'responseText', @responseText OUTPUT;

-- Check the status

EXEC sp_OAMethod @object, 'status', @status OUTPUT;

-- Get the response text

IF((SELECT @ResponseText) <> '')

BEGIN

DECLARE @json NVARCHAR(MAX) = (Select @ResponseText)

PRINT 'Response: ' + @json;

SELECT *

FROM OPENJSON(@json)

WITH (

id INTEGER '$.id',

description NVARCHAR(MAX) '$.description',

name NVARCHAR(MAX) '$.name'

);

END

ELSE

BEGIN

DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';

PRINT @ErroMsg;

END

-- Clean up

EXEC sp_OADestroy @object;

END;

3. Execute the Stored Procedure

Run the stored procedure to see the output:

EXEC CallApiExample;

Result from API:

Result after executing the stored procedure:

Detailed Explanation:
sp_OACreate: This procedure creates an instance of a COM object. Here, 'MSXML2.XMLHTTP' is used to create an object that can make HTTP requests.

sp_OAMethod: This procedure calls methods on the COM object. In this example:

'open' sets up the request method and URL.

'send' sends the HTTP request.

'responseText' retrieves the response body.

'status' retrieves the HTTP status code.

sp_OADestroy: This procedure cleans up and releases the COM object.

Considerations:

- Security: Using OLE Automation Procedures can pose security risks. Ensure your SQL Server instance is properly secured and consider using more secure methods if available.

- Error Handling: The example doesn't include detailed error handling. In production code, you should handle potential errors from HTTP requests and COM operations.

- Performance: Making HTTP requests synchronously from SQL Server can impact performance and scalability.

- SQL Server Versions: OLE Automation Procedures are supported in many versions of SQL Server but may be deprecated or not available in future versions. So, please check your version's documentation for specifics.

References:

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/ole-automation-stored-procedures-transact-sql?view=sql-server-ver16
https://stackoverflow.com/questions/22067593/calling-an-api-from-sql-server-stored-procedure
https://blog.dreamfactory.com/stored-procedures-data-integration-resty-performance/
https://mssqlserver.dev/making-rest-api-call-from-sql-server
Image source: https://www.freepik.com/free-photo/application-programming-interface-hologram_18098426.htm

TECH

November 28, 2024

Exploring TypeScript: The Future Language for JavaScript Programming

In the dynamic world of web development, JavaScript has long been the go-to language for building interactive and dynamic web applications. However, as applications grow in complexity, managing large codebases and ensuring code quality with plain JavaScript can become challenging. Enter TypeScript, a powerful superset of JavaScript that addresses these challenges by adding static type-checking and other robust features.

What is TypeScript?

TypeScript is an open-source programming language developed by Microsoft. It builds on JavaScript by introducing static typing, classes, and interfaces, among other features, making it easier to write and maintain large-scale applications. Essentially, TypeScript is JavaScript with additional tools to catch errors early and enhance the development process.

Why Use TypeScript?

Early Error Detection: TypeScript's static type system allows developers to catch errors at compile time rather than at runtime. This means you can identify and fix issues before your code even runs, significantly reducing the number of bugs.
Enhanced Maintainability: As projects grow, maintaining code can become cumbersome. TypeScript's type annotations and interfaces make the code more readable and self-documenting, which simplifies maintenance and collaboration.
Improved Tooling: TypeScript provides powerful tools such as IntelliSense, which offers intelligent code completion, parameter info, and documentation on the fly. This improves developer productivity and reduces the likelihood of errors.
Interoperability with JavaScript: TypeScript is designed to be fully compatible with existing JavaScript codebases. You can gradually introduce TypeScript into your project, converting files one at a time without disrupting the entire codebase.

Basic Structure of TypeScript

TypeScript syntax is very similar to JavaScript, with additional features for static typing and more. Here are some key elements:

Type Annotations: Define variable types to catch errors early.

let isDone: boolean = false;

let total: number = 10;

let name: string = "TypeScript";

Interfaces: Define complex types and enforce structure.

interface Person {

    name: string;
  age: number;

let user: Person = {

name: "John", age: 25

};

Classes: Support object-oriented programming with features like inheritance and encapsulation.

class Greeter {

    greeting: string;

      constructor(message: string) {

            this.greeting = message;

greet() {

            return "Hello, " + this.greeting;

let greeter = new Greeter("world");

console.log(greeter.greet()); // => Hello world

Generics: Write reusable and flexible components.

function identity<T>(arg: T): T {

      return arg;

let output = identity<string>("myString");

let numberOutput = identity<number>(100);

Getting Started with TypeScript

To start using TypeScript, you need to install the TypeScript compiler (tsc) via npm (Node Package Manager). Open your terminal and run the following command:

npm install -g typescript

Once installed, you can compile TypeScript files into JavaScript using the tsc command:

tsc file.ts

This will generate a corresponding file.js that you can run in any browser or Node.js environment.

Key Features of TypeScript

Static Typing: TypeScript allows you to define types for variables, function parameters, and return values. This helps prevent type-related errors and improves code clarity.
Type Inference: Even without explicit type annotations, TypeScript can often infer the type of a variable based on its value or how it is used.
Type Declarations: TypeScript allows you to create type definitions for libraries or frameworks that are not written in TypeScript, enabling better integration and development experience.
ES6 and Beyond: TypeScript supports many modern JavaScript features, such as async/await, destructuring, and template literals, even if they are not yet available in the target JavaScript environment.

Conclusion

TypeScript not only improves code quality and maintainability but also enhances developer productivity through better tooling and early error detection. Its compatibility with JavaScript allows for a smooth transition and incremental adoption. As web applications continue to grow in complexity, TypeScript emerges as a powerful ally for developers aiming to write clean, reliable, and scalable code.

References:
https://www.typescriptlang.org/docs
https://smachstack.com/how-to-work-ts ( Image source )

TECH

November 28, 2024

Some tips to improve performance of LINQ in C#

Improving performance with LINQ in C# is essential, especially when working with large datasets. LINQ provides a powerful and expressive way to query data, but it can introduce performance overhead if not used efficiently. Below are some tips and tricks to improve LINQ performance, along with sample code:

1. Avoid repeated Enumeration

When you execute a LINQ query, it can be enumerated multiple times, leading to unnecessary performance hits.

You can improve performance by materializing the result of a query (e.g., using ToList(), ToArray(), or ToDictionary()).

var data = GetData(); // Some large collection

// Not good: Repeatedly enumerating the sequence
var count = data.Where(x => x.IsActive).Count();
var sum = data.Where(x => x.IsActive).Sum(x => x.Value);

// Good: Materializing the result to avoid repeated enumeration
var activeData = data.Where(x => x.IsActive).ToList();
var count = activeData.Count;
var sum = activeData.Sum(x => x.Value);

2. Use Any() instead of Count() > 0

If you're only checking whether a collection contains any elements, using Any() is faster than Count() > 0.

Any() stops as soon as it finds the first matching element, whereas Count() counts all elements before returning a result.

// Not good: Counting all elements
if (data.Where(x => x.IsActive).Count() > 0) { ... }

// Good: Checking for any element
if (data.Where(x => x.IsActive).Any()) { ... }

3. Use FirstOrDefault() and SingleOrDefault()

When you expect only one element or none, use FirstOrDefault() or SingleOrDefault() instead of Where() combined with First() or Single().

These methods are optimized for single element retrieval.

// Not good: Using Where with First
var item = data.Where(x => x.Id == 1).FirstOrDefault();

// Good: Using FirstOrDefault directly
var item = data.FirstOrDefault(x => x.Id == 1);

4. Use OrderBy and ThenBy efficiently

If you need to sort data, make sure that you're sorting only what is necessary, as sorting can be an expensive operation. Additionally, try to minimize the number of sorting operations.

// Not good: Multiple OrderBy statements
var sortedData = data.OrderBy(x => x.Age).OrderBy(x => x.Name);

// Good: Using OrderBy and ThenBy together
var sortedData = data.OrderBy(x => x.Age).ThenBy(x => x.Name);

5. Optimize GroupBy

The GroupBy operator can be expensive, especially if you're grouping large collections. If you need to perform a GroupBy but only need to count or get the First/Last element in each group, avoid creating the entire group and just perform a more efficient aggregation.

// Not good: GroupBy followed by a complex operation
var grouped = data.GroupBy(x => x.Category)
.Select(g => new { Category = g.Key, Count = g.Count() })
.ToList();

// Good: Perform aggregation more directly
var counts = data.GroupBy(x => x.Category)
.Select(g => new { Category = g.Key, Count = g.Count() })
.ToDictionary(g => g.Category, g => g.Count);

6. Prefer IEnumerable<T> over List<T> when possible

LINQ queries work best with IEnumerable<T> because it represents a lazy sequence.

Converting it to a List<T> immediately could result in unnecessary memory usage if not required.

// Not good: Convert to List too early
var result = data.Where(x => x.IsActive).ToList();

// Good: Keep it as IEnumerable until it's really needed
IEnumerable<int> result = data.Where(x => x.IsActive);

Hoping with these tips, you can significantly improve the performance of your LINQ queries in C#.

References:

https://www.bytehide.com/blog/linq-performance-optimization-csharp

Image source: https://www.freepik.com/free-photo/top-view-laptop-table-glowing-screen-dark_160644251.htm

TECH

November 28, 2024

Flexbox in CSS: A Flexible Layout Solution

In modern web design, organizing and aligning elements on a page is crucial. One of the powerful tools that helps achieve this is Flexbox (Flexible Box Layout). Flexbox allows you to create flexible and easily adjustable layouts, providing an optimal user experience.

TECH

November 28, 2024

Next.js: A Comprehensive Security Solution

In the era of modern web applications, security is one of the most critical factors, especially when handling and storing sensitive data. Next.js - a powerful framework based on React - not only optimizes performance but also provides enhanced security features through Server-Side Rendering (SSR) and API Routes.

TECH

November 28, 2024

Understanding Temporary Tables in SQL

When working with databases, efficiency and performance are critical. One powerful feature that SQL provides to enhance these aspects is the use of temporary tables. In this blog, we will explore what temporary tables are, their benefits, and how to effectively utilize them in your SQL queries.

What is a Temporary Table?

Temporary tables are special types of database tables that are created and used to store data temporarily during the execution of a SQL script or session. Unlike regular tables, which persist in the database until explicitly removed, temporary tables exist only for the duration of the session or connection that created them.

Types of Temporary Tables

SQL databases generally support two types of temporary tables:

1. Local Temporary Tables (#temp):

Prefixed with a single # (e.g., #ProductOrders).
Visible only to the session that created it.
Automatically dropped when the session ends.

2. Global Temporary Tables (##temp):

Prefixed with double ## (e.g., ##ProductOrders).
Visible to all sessions after creation.
Dropped only when the last session using it closes.

Advantages of Using Temporary Tables

Improved Performance: Temporary tables can improve the performance of your SQL queries by reducing complexity. Instead of executing complex joins or subqueries repeatedly, you can store intermediate results in a temporary table and reference that table multiple times.
Session-specific Data: Temporary tables allow you to store data that is specific to a particular session. This reduces the risk of naming conflicts and allows for cleaner code since other sessions cannot access your temp tables.
Ease of Use: Temporary tables can simplify your SQL code. For large and complex queries, breaking down the process into multiple steps with temporary tables can enhance readability and maintainability.
Data Manipulation: You can perform operations on temporary tables just like you would with permanent tables, including DML (INSERT, UPDATE, DELETE) operations, making them versatile for various use cases.
Rollback Capabilities: Changes made to temporary tables can be rolled back within the same transaction, allowing for easier error handling during extensive data manipulation.

How to Create and Use Temporary Tables

1. Creating a Local Temporary Table

Creating a local temporary table is straightforward. Here's the syntax:

CREATE TABLE #ProductOrder (
    ProductOrderId INT,
    ProductName VARCHAR(50),
    Quantity INT,
    Price DECIMAL(10, 2)
);

In the example above, we’ve created a temporary table named #ProductOrder with four columns. This table is only visible to the session that created it.

2. Inserting Data into the Temporary Table

Once created, you can insert data into it like any regular table:

INSERT INTO #ProductOrder (ProductOrderId , ProductName, Quantity, Price)
VALUES 
    (1, 'Laptop', 1, 1200.00), 
    (2, 'Monitor', 2, 300.00),
    (3, 'Keyboard', 3, 20.00);

In the example above, we’ve created a temporary table named #ProductOrder with four columns. This table is only visible to the session that created it.

3. Querying the Temporary Table

Once data has been inserted, you can perform standard SQL operations:

SELECT * FROM #ProductOrder;

Output:

ProductOrderId	ProductName	Quantity	Price
1	Laptop	1	1200.00
2	Monitor	2	300.00
3	Keyboard	3	20.00

4. Dropping a Temporary Table

Although temporary tables are automatically dropped when the session ends, you can explicitly drop them if you no longer need them:

DROP TABLE #ProductOrder;

In the example above, we’ve created a temporary table named #ProductOrder with four columns. This table is only visible to the session that created it.

Use Cases for Temporary Tables

Data Staging: When performing ETL (Extract, Transform, Load) processes, temporary tables can serve as a landing area for data that needs to be cleaned or transformed before being inserted into permanent tables.
Complex Reporting: In scenarios where complex reporting queries involve multiple aggregations or calculations, temporary tables can simplify the process by storing intermediate results.
Batch Processing: During batch processing tasks, you can use temporary tables to store results for subsequent updates or inserts.
Managing Intermediate State: In transaction management, temporary tables can be utilized to store intermediate results, reducing the overhead of processing data multiple times.

Conclusion

Temporary tables are indispensable tools for database administrators and developers looking to optimize their SQL workflows. By mastering their use, you can improve query performance, simplify complex data transformations, and streamline your reporting processes.

Cover image from freepik.com

TECH

November 27, 2024

Tailwind CSS - An open-source utility-first CSS framework

Tailwind CSS is an open-source utility-first CSS framework designed to help developers build modern websites quickly and efficiently. Unlike traditional CSS frameworks like Bootstrap, Tailwind CSS does not come with predefined components. Instead, it provides a set of utility classes that you can use to style your HTML elements directly.

TECH

November 27, 2024

Solving race condition problem with locking mechanism in C# programming

A race condition occurs in programming when multiple threads access a shared resource concurrently and the final result depends on the unpredictable order of their execution. This can lead to inconsistent and non-deterministic program behavior.

TECH

November 27, 2024

Responsive of Bootstrap - Creating Perfect Websites for All Devices

First of all, let's find out what responsive design is. Responsive Web Design (RWD) is an approach to web design that enables websites to automatically adjust and optimize the user experience across a variety of devices, including desktops, tablets, and mobile phones. In today’s digital age, having a responsive website is crucial.

TECH

November 26, 2024

Introducing Pinia: A Simple State Management Tool for Vue.js

In the world of front-end development, managing state is important for creating scalable and easy-to-maintain applications. As Vue.js grows in popularity, developers look for tools that make their work easier. That’s where Pinia comes in—a modern state management library made for Vue 3. In this blog post, we’ll explain Pinia, its main features, how it compares to Vuex, and how it can help you in your Vue.js projects.

What is Pinia?

Pinia is a state management library that serves as a lightweight and easy-to-use alternative to Vuex, the main state management tool for Vue.js. Pinia is designed for Vue 3 and uses the Composition API, making it a more modern choice for developers. It offers many features that meet the needs of today’s web development.

Key Features of Pinia

Simplicity and Ease of Use
Pinia stands out because it is simple to use. The API is designed to be user-friendly so that developers can create their stores with very little code. This makes it easy for both new and experienced developers to get started quickly.
Vue 3 Compatibility
Pinia is built specifically for Vue 3, meaning it works perfectly with the Composition API. This gives developers more flexibility in how they structure their applications and makes state management fit better within Vue.
TypeScript Support
As TypeScript becomes more common in projects, Pinia offers great support for it. This ensures that developers can catch errors before the code runs, which leads to stronger applications.
Global State Management
With Pinia, managing the global state is simple. You can create stores that hold your application’s state and access them from any component, making it easier to manage data without passing it through many layers.
Plugin Support
Pinia allows the use of plugins, which means developers can add more features as needed. This makes Pinia adaptable for various projects.

Comparing Pinia and Vuex

While Vuex has been the main choice for state management in Vue.js, Pinia offers some advantages that make it a good alternative:

API Design
- Vuex: Uses a more complex approach with mutations, actions, and getters, which can require more code.
- Pinia: Simplifies state management by combining state, actions, and getters in one store definition.
Integration with Vue 3
- Vuex: Has a version for Vue 3 but was originally made for Vue 2, which can complicate things.
- Pinia: Built from the start for Vue 3, ensuring easy integration and better use of the Composition API.
TypeScript Support
- Vuex: Provides TypeScript support but can be tricky to set up.
- Pinia: Offers excellent TypeScript support right away, making it easier to use in TypeScript projects.
Performance
- Vuex: Can be heavier and slower due to its structure.
- Pinia: Lightweight and optimized for performance, leading to faster state management.

Getting Started with Pinia

To start using Pinia, you’ll first need to install it in your Vue 3 project:

npm install pinia

Once installed, you can create a store like this:

import { defineStore } from 'pinia';

export const useMainStore = defineStore('main', {

state: () => ({

count: 0,

}),

actions: {

increment() {

this.count++;

},

});

You can then use this store in your components:

<template>

<div>

<p>{{ count }}</p>

<button @click="increment">Increment</button>

</div>

</template>

<script setup>

import { useMainStore } from '@/stores/main';

const store = useMainStore();

const { count, increment } = store;

</script>

Conclusion

Pinia is a big step forward in state management for Vue.js applications. With its easy-to-use API, strong TypeScript support, and smooth integration with Vue 3, it’s a great choice for developers who want to simplify their work.

While Vuex has been helpful for many years, Pinia’s simplicity and performance make it a strong alternative. Whether you’re working on a small project or a large one, Pinia can help you manage state more effectively.

If you haven’t tried Pinia yet, now is a great time to explore what it can do for your Vue.js projects!

References

« 1 … 12 13 14 15 16 … 25 »

Blog

Calling REST API From SQL Server Stored Procedure

References:

Exploring TypeScript: The Future Language for JavaScript Programming

What is TypeScript?

Why Use TypeScript?

Basic Structure of TypeScript

Getting Started with TypeScript

Key Features of TypeScript

Conclusion

References:
https://www.typescriptlang.org/docs
https://smachstack.com/how-to-work-ts ( Image source )

Some tips to improve performance of LINQ in C#

References:

Flexbox in CSS: A Flexible Layout Solution

Next.js: A Comprehensive Security Solution

Understanding Temporary Tables in SQL

What is a Temporary Table?

Types of Temporary Tables

1. Local Temporary Tables (#temp):

2. Global Temporary Tables (##temp):

Advantages of Using Temporary Tables

How to Create and Use Temporary Tables

1. Creating a Local Temporary Table

2. Inserting Data into the Temporary Table

3. Querying the Temporary Table

4. Dropping a Temporary Table

Use Cases for Temporary Tables

Conclusion

Tailwind CSS - An open-source utility-first CSS framework

Solving race condition problem with locking mechanism in C# programming

Responsive of Bootstrap - Creating Perfect Websites for All Devices

Introducing Pinia: A Simple State Management Tool for Vue.js

What is Pinia?

Key Features of Pinia

Comparing Pinia and Vuex

Getting Started with Pinia

Conclusion

References

CONTACT US

Blog

Calling REST API From SQL Server Stored Procedure

References:

Exploring TypeScript: The Future Language for JavaScript Programming

What is TypeScript?

Why Use TypeScript?

Basic Structure of TypeScript

Getting Started with TypeScript

Key Features of TypeScript

Conclusion

References: https://www.typescriptlang.org/docs https://smachstack.com/how-to-work-ts ( Image source )

Some tips to improve performance of LINQ in C#

References:

Flexbox in CSS: A Flexible Layout Solution

Next.js: A Comprehensive Security Solution

Understanding Temporary Tables in SQL

What is a Temporary Table?

Types of Temporary Tables

1. Local Temporary Tables (#temp):

2. Global Temporary Tables (##temp):

Advantages of Using Temporary Tables

How to Create and Use Temporary Tables

1. Creating a Local Temporary Table

2. Inserting Data into the Temporary Table

3. Querying the Temporary Table

4. Dropping a Temporary Table

Use Cases for Temporary Tables

Conclusion

Tailwind CSS - An open-source utility-first CSS framework

Solving race condition problem with locking mechanism in C# programming

Responsive of Bootstrap - Creating Perfect Websites for All Devices

Introducing Pinia: A Simple State Management Tool for Vue.js

What is Pinia?

Key Features of Pinia

Comparing Pinia and Vuex

Getting Started with Pinia

Conclusion

References

CONTACT US

References:
https://www.typescriptlang.org/docs
https://smachstack.com/how-to-work-ts ( Image source )