How to Connect an SQL database with TypeScript

How to Connect an SQL database with TypeScript

In this post, I will explain how to connect to a SQL database with TypeScript in a web application and how to get data from the database in TypeScript.

Introduction

In this post, I will explain how to connect to a SQL database with TypeScript in a web application and how to get data from the database in TypeScript. First I created a database "EmpDetail". Then I created a table in the database.

Query Code

CREATE TABLE[dbo]. [emp]([id][int] NULL, [name][varchar](50) NULL, [salary][int] NULL) ON[PRIMARY]  

Now Insert some Data in the emp table.

Complete Program

aap.ts

class DataConnectivity  
{  
 LoadDB()  
 {  
  var connection = new ActiveXObject("ADODB.Connection");  
  var connectionstring = "Data Source=.;Initial Catalog=EmpDetail;Persist Security Info=True;User ID=sa;Password=****;Provider=SQLOLEDB";  
  connection.Open(connectionstring);  
  var rs = new ActiveXObject("ADODB.Recordset");  
  rs.Open("select * from emp", connection);  
  rs.MoveFirst();  
  var span = document.createElement("span");  
  span.style.color = "Blue";  
  span.innerText = "  ID " + "  Name " + "   Salary";  
  document.body.appendChild(span);  
  while (!rs.eof)  
  {  
   var span = document.createElement("span");  
   span.style.color = "green";  
   span.innerText = "\n " + rs.fields(0) + " |  " + rs.fields(1) + " |  " + rs.fields(2);  
   document.body.appendChild(span);  
   rs.MoveNext();  
  }  
  rs.close();  
  connection.close();  
 }  
}  
window.onload = () =>  
 {  
  var obj = new DataConnectivity();  
  var bttn = < HTMLButtonElement > document.getElementById("ShowData");  
  bttn.onclick = function()  
  {  
   obj.LoadDB();  
  }  
 };  

DataConnectivity_Demo.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DataConnectivity_Demo.aspx.cs" Inherits="Data_Connectivity.DataConnectivity_Demo" %>  
 <  
 !DOCTYPE html >  
 <  
 html xmlns = "http://www.w3.org/1999/xhtml" >    
 <  
 head runat = "server" >   
 <  
 title > < /title>  
 <  
 script src = "app.js" > < /script>  
 <  
 /head>  
 <  
 body >  
 <  
 form id = "form1"  
runat = "server" >  
 <  
 div style = "font-size: large; font-weight: bold" >  
 Click on button  
for show data < br / >  
 <  
 br / >  
 <  
 input id = "ShowData"  
type = "button"  
value = "Show Data"  
style = "font-weight: bold" / >  
 <  
 /div>  
 <  
 /form>  
 <  
 /body>  
 <  
 /html>  

app.js

var DataConnectivity = (function() {  
 function DataConnectivity() {}  
 DataConnectivity.prototype.LoadDB = function() {  
  var connection = new ActiveXObject("ADODB.Connection");  
  var connectionstring = "Data Source=.;Initial Catalog=EmpDetail;Persist Security Info=True;User ID=sa;Password=*****;Provider=SQLOLEDB";  
  connection.Open(connectionstring);  
  var rs = new ActiveXObject("ADODB.Recordset");  
  rs.Open("select * from emp", connection);  
  rs.MoveFirst();  
  var span = document.createElement("span");  
  span.style.color = "Blue";  
  span.innerText = "  ID " + "  Name " + "   Salary";  
  document.body.appendChild(span);  
  while (!rs.eof) {  
   var span = document.createElement("span");  
   span.style.color = "green";  
   span.innerText = "\n " + rs.fields(0) + " |  " + rs.fields(1) + " |  " + rs.fields(2);  
   document.body.appendChild(span);  
   rs.MoveNext();  
  }  
  rs.close();  
  connection.close();  
 };  
 return DataConnectivity;  
})();  
window.onload = function() {  
 var obj = new DataConnectivity();  
 var bttn = document.getElementById("ShowData");  
 bttn.onclick = function() {  
  obj.LoadDB();  
 };  
};  
//@ sourceMappingURL=app.js.map  

Output

Thank you for reading!

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

What’s new in HTML6

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

Learn to Build SQL Query| Ultimate SQL and DataBase Concepts|Simpliv

Learn to Build SQL Query| Ultimate SQL and DataBase Concepts|Simpliv

Learn to Build SQL Query| Ultimate SQL and DataBase Concepts

Description
SQL developers are earning higher salary in IT industry, but, its not about writing queries its about understanding and applying the right query at right time and this course will let you understand complex SQL Statements in an easy way .

Moreover, This Course will teach you how to extract Data from Database and write complex queries to a database This course will focus on a wider scale by Covering Structure Query Language SQL concepts as a whole, whether Students work with MySQL, Microsoft SQL Server, Oracle Server, etc.

This course have 5 Chapters in which you will learn

Chapter 1 Fundamentals

Fundamentals
Building Blocks
Selecting Records from DB
Working with Arithmetic Expressions
Chapter 2 Conditioning Sorting and Operators

Logical Operators
Comparison Operators
Operator Precedence
Sorting Results
Chapter 3 Functions

Character Functions
Number Functions
Date Functions
Conversions
General Purpose Functions
Nesting Functions
Chapter 4 Grouping

Multiple Row Functions on a single Table
Multiple Row Functions on Many Table
Chapter 5 Joins

Understanding Primary Key
Understanding Foreign Key
Understanding Need of Joins
Cartesian Product
Equie Join Simple Join Self Join
Non Equie Join
Outer Join
Self Join
Course is Designed for College and University Students who want Solid SQL and Data Base Concepts in a short period of time.

Who this course is for:

Beginners
University or College students
Anyone who wants Solid SQL Concepts
Basic knowledge
No prior knowledge is required
PC or MAC
What will you learn
SQL Fundamentals
Understand Complex SQL Concepts in Easy way using daily life examples
Construct SQL Statements
Use SQL to retrieve data from database
Selecting Data From Database
Restricting and Sorting Data from DB
Grouping Data From DB
Construct SQL statements that will let them work with more than two tables
Use SQL Functions
Work with SQL Operators and find out precedence
Nesting in SQL
Joins
To continue:

Migrate Entity Framework Core to SQL Database on Startup

Migrate Entity Framework Core to SQL Database on Startup

This ASP.NET Core tutorial explains how to migrate Entity Framework Core to SQL Database on Startup. How to automatically migrate database changes from code in ASP.NET Core using Entity Framework Core from the Startup.cs file. Use the EF Core DB Context Service to automatically migrate database changes.

Example code tested with ASP.NET Core 3.1

This is a super quick example of how to automatically migrate database changes from code in ASP.NET Core using Entity Framework Core from the Startup.cs file.

Solution

Register the EF Core DB Context as an ASP.NET Core Service

The Entity Framework Core DB Context is registered as a service with the ASP.NET Core Dependency Injection (DI) system from the ConfigureServices() method of the Startup.cs file.

// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<DataContext>(x => x.UseSqlite("Data Source=LocalDatabase.db"));

    ...
}

Use the EF Core DB Context Service to automatically migrate database changes

An instance of the EF Core DB Context service is injected as a parameter into the Configure() method of the Startup.cs file, the DB Context instance is then used to apply any pending migrations to the database by calling the Database.Migrate() method.

// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env, DataContext dataContext)
{
    // migrate any database changes on startup (includes initial db creation)
    dataContext.Database.Migrate();

    ...
}
Extra Info

While updating the tutorial from an EF Core InMemory database to SQLite I ran into some difficulties trying to automatically run database migrations from the Startup.cs. At first I was following a tutorial on the MS Docs website that called services.BuildServiceProvider().GetService<MyDatabaseContext>().Database.Migrate(); from within the ConfigureServices() method, but this resulted in the following warning in the console when I ran the application:

Startup.cs(39,13): warning ASP0000: Calling 'BuildServiceProvider' from application code results in an additional copy of singleton services being created.
Consider alternatives such as dependency injecting services as parameters to 'Configure'.
[/Users/jwatmore/Projects/aspnet-core-3-registration-login-api/WebApi.csproj]

A Practical Guide to Database Design and Access SQL|Simpliv

A Practical Guide to Database Design and Access SQL|Simpliv

Understanding the Concepts of Database design, develop normalized tables, analyse table designs to avoid redundancy and anomalies, write SQL statements to extract and manipulate requests and resolve many inherent problems associated with poor database design.

Description
In this course you will design and develop normalized tables, analyse table designs to avoid redundancy and anomalies, write SQL statements to extract and manipulate requests and resolve many inherent problems associated with poor database design. As you develop these skills, you will be exposed to LucidArt for diagramming ERD's; querySQL and QBE programming, to execute query statements; MS Access Workbench, for implementing business solutions.

Who this course is for:

IT staff and business employees who would like an understanding of how to organize, access and report data effectively and efficiently
Basic knowledge
An interest in computer information systems. Copy of MS Access 2016 software is optional

What will you learn
Build relational database solutions for home and business, including lots of practical examples, "best practice" guidelines, Access SQL design and implementation, templates that work
To continue: