1599127080
Geo-Distributed SQL Databases: 9 Techniques to Reduce Cross-Region Latency
Here’s a webinar playback on how to stretch database clusters across 3 or more regions while at the same time reducing cross-region latency.
Delivered by my colleague Sid Choudhury, SVP of Product at Yugabyte, you can also check out the companion blog here:
blog.yugabyte.com/9-techniques-to-build-cloud-native-geo-distributed-sql-apps-with-low-latency/
New to distributed SQL or YugabyteDB? Read on.
What is Distributed SQL?
Distributed SQL databases are becoming popular with organizations interested in moving data infrastructure to the cloud or cloud native environments. This is often motivated by the desire to reduce TCO or move away from the horizontal scaling limitations of monolithic RDBMS like Oracle, PostgreSQL, MySQL, and SQL Server. The basic characteristics of Distributed SQL are:
They must have a SQL API for querying and modeling data, with support for traditional RDBMS features like foreign keys, partial indexes, stored procedures, and triggers.
Automatic distributed query execution so that no single node becomes a bottleneck.
Should support automatic and transparent distributed data storage. This includes indexes, which should be sharded across multiple nodes of the cluster so that no single node becomes a bottleneck. Data distribution ensures high performance and high availability.
Distributed SQL systems should also provide for strongly consistent replication and distributed ACID transactions.
For a deeper discussion about what Distributed SQL is, check out, “What is Distributed SQL?”
#postgresql #cloud native #postgres #distributed sql databases #yugabyte #cloud
What is GEEK
Buddha Community
1594369800
Introduction to Structured Query Language SQL pdf
SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.
Models for SQL exist. In any case, the SQL that can be utilized on every last one of the major RDBMS today is in various flavors. This is because of two reasons:
1. The SQL order standard is genuinely intricate, and it isn’t handy to actualize the whole standard.
2. Every database seller needs an approach to separate its item from others.
Right now, contrasts are noted where fitting.
#programming books #beginning sql pdf #commands sql #download free sql full book pdf #introduction to sql pdf #introduction to sql ppt #introduction to sql #practical sql pdf #sql commands pdf with examples free download #sql commands #sql free bool download #sql guide #sql language #sql pdf #sql ppt #sql programming language #sql tutorial for beginners #sql tutorial pdf #sql #structured query language pdf #structured query language ppt #structured query language
1574307124
How to add CRUD Operations to Angular using ASP.NET Web API and MongoDB
MongoDB is a NoSQL, free, open source, high-performance, and cross-platform document-oriented database. MongoDB was developed by the 10gen company that is now called MongoDB Inc. MongoDB is written in C++ and stores data in a flexible, JSON-like format with a dynamic schema.
Prerequisites
- We should have the basic knowledge of Angular, MongoDB, and Web API.
- The Visual Studio Code IDE should be installed.
- Robo 3T or Studio 3T should be installed.
Create a Database and Collection in MongoDB
MongoDB Environment Setup
Check how to set up the MongoDB environment and Robo 3T, from here.
Step 1
Now, open Robo 3T and connect to the local server.
Step 2
Create a database with the name “employee” using Robo 3T (Check Link).
Create a Web API Project
Step 1
Open Visual Studio and create a new project.
Change the name to CrudWithMongoDB.
Choose the WEB API template.
Step 2
Now, add the MongoDB Driver for C# using NuGet Package Manager.
Go to Tools>>NuGet Package Manager >> Manage NuGet package for Solution.
Step 3
Right-click the Models folder and add two classes, Employee and Status. Now, paste the following codes in these classes.
Add the required namespaces in the Employee class.
using MongoDB.Bson;
using MongoDB.Bson.Serialization.Attributes;
Employee class
public class Employee
{
[BsonRepresentation(BsonType.ObjectId)]
public String Id { get; set; }
public string Name { get; set; }
public string { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string Country { get; set; }
}
Status class
public class Status
{
public string Result { set; get; }
public string Message { set; get; }
}
Step 4
Now, add a connection string in the web.config file and add the following line in the App Settings section of that file.
<add key="connectionString" value="mongodb://localhost"/>
Step 5
Right-click on the Controllers folder and add a new controller. Name it “Emp controller.”
Add the following namespaces in the Emp controller.
using MongoDB.Driver;
using MongoDB.Bson;
using CrudWithMongoDB.Models;
Now, add a method to insert data into the database for inserting employee details.
[Route("InsertEmployee")]
[HttpPost]
public object Addemployee(Employee objVM)
{
try
{ ///Insert Emoloyeee
#region InsertDetails
if (objVM.Id == null)
{
string constr = ConfigurationManager.AppSettings["connectionString"];
var Client = new MongoClient(constr);
var DB = Client.GetDatabase("Employee");
var collection = DB.GetCollection<Employee>("EmployeeDetails");
collection.InsertOne(objVM);
return new Status
{ Result = "Success", Message = "Employee Details Insert Successfully" };
}
#endregion
///Update Emoloyeee
#region updateDetails
else
{
string constr = ConfigurationManager.AppSettings["connectionString"];
var Client = new MongoClient(constr);
var Db = Client.GetDatabase("Employee");
var collection = Db.GetCollection<Employee>("EmployeeDetails");
var update = collection.FindOneAndUpdateAsync(Builders<Employee>.Filter.Eq("Id", objVM.Id), Builders<Employee>.Update.Set("Name", objVM.Name).Set("Department", objVM.Department).Set("Address", objVM.Address).Set("City", objVM.City).Set("Country", objVM.Country));
return new Status
{ Result = "Success", Message = "Employee Details Update Successfully" };
}
#endregion
}
catch (Exception ex)
{
return new Status
{ Result = "Error", Message = ex.Message.ToString() };
}
}
Add a new method to delete employee details.
#region DeleteEmployee
[Route("Delete")]
[HttpGet]
public object Delete(string id)
{
try
{
string constr = ConfigurationManager.AppSettings["connectionString"];
var Client = new MongoClient(constr);
var DB = Client.GetDatabase("Employee");
var collection = DB.GetCollection<Employee>("EmployeeDetails");
var DeleteRecored = collection.DeleteOneAsync(
Builders<Employee>.Filter.Eq("Id", id));
return new Status
{ Result = "Success", Message = "Employee Details Delete Successfully" };
}
catch (Exception ex)
{
return new Status
{ Result = "Error", Message = ex.Message.ToString() };
}
}
#endregion
Add a method to get Employee details.
#region Getemployeedetails
[Route("GetAllEmployee")]
[HttpGet]
public object GetAllEmployee()
{
string constr = ConfigurationManager.AppSettings["connectionString"];
var Client = new MongoClient(constr);
var db = Client.GetDatabase("Employee");
var collection = db.GetCollection<Employee>("EmployeeDetails").Find(new BsonDocument()).ToList();
return Json(collection);
}
#endregion
Add a method to get Employee details by Id.
#region EmpdetaisById
[Route("GetEmployeeById")]
[HttpGet]
public object GetEmployeeById(string id)
{
string constr = ConfigurationManager.AppSettings["connectionString"];
var Client = new MongoClient(constr);
var DB = Client.GetDatabase("Employee");
var collection = DB.GetCollection<Employee>("EmployeeDetails");
var plant = collection.Find(Builders<Employee>.Filter.Where(s => s.Id == id)).FirstOrDefault();
return Json(plant);
}
#endregion
Here is the complete Emp controller code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using MongoDB.Driver;
using MongoDB.Bson;
using CrudWithMongoDB.Models;
using System.Configuration;
namespace CrudWithMongoDB.Controllers
{
[RoutePrefix("Api/Employee")]
public class EmpController : ApiController
{
[Route("InsertEmployee")]
[HttpPost]
public object Addemployee(Employee objVM)
{
try
{ ///Insert Emoloyeee
#region InsertDetails
if (objVM.Id == null)
{
string constr = ConfigurationManager.AppSettings["connectionString"];
var Client = new MongoClient(constr);
var DB = Client.GetDatabase("Employee");
var collection = DB.GetCollection<Employee>("EmployeeDetails");
collection.InsertOne(objVM);
return new Status
{ Result = "Success", Message = "Employee Details Insert Successfully" };
}
#endregion
///Update Emoloyeee
#region updateDetails
else
{
string constr = ConfigurationManager.AppSettings["connectionString"];
var Client = new MongoClient(constr);
var Db = Client.GetDatabase("Employee");
var collection = Db.GetCollection<Employee>("EmployeeDetails");
var update = collection.FindOneAndUpdateAsync(Builders<Employee>.Filter.Eq("Id", objVM.Id), Builders<Employee>.Update.Set("Name", objVM.Name).Set("Department", objVM.Department).Set("Address", objVM.Address).Set("City", objVM.City).Set("Country", objVM.Country));
return new Status
{ Result = "Success", Message = "Employee Details Update Successfully" };
}
#endregion
}
catch (Exception ex)
{
return new Status
{ Result = "Error", Message = ex.Message.ToString() };
}
}
#region Getemployeedetails
[Route("GetAllEmployee")]
[HttpGet]
public object GetAllEmployee()
{
string constr = ConfigurationManager.AppSettings["connectionString"];
var Client = new MongoClient(constr);
var db = Client.GetDatabase("Employee");
var collection = db.GetCollection<Employee>("EmployeeDetails").Find(new BsonDocument()).ToList();
return Json(collection);
}
#endregion
#region EmpdetaisById
[Route("GetEmployeeById")]
[HttpGet]
public object GetEmployeeById(string id)
{
string constr = ConfigurationManager.AppSettings["connectionString"];
var Client = new MongoClient(constr);
var DB = Client.GetDatabase("Employee");
var collection = DB.GetCollection<Employee>("EmployeeDetails");
var plant = collection.Find(Builders<Employee>.Filter.Where(s => s.Id == id)).FirstOrDefault();
return Json(plant);
}
#endregion
#region DeleteEmployee
[Route("Delete")]
[HttpGet]
public object Delete(string id)
{
try
{
string constr = ConfigurationManager.AppSettings["connectionString"];
var Client = new MongoClient(constr);
var DB = Client.GetDatabase("Employee");
var collection = DB.GetCollection<Employee>("EmployeeDetails");
var DeleteRecored = collection.DeleteOneAsync(
Builders<Employee>.Filter.Eq("Id", id));
return new Status
{ Result = "Success", Message = "Employee Details Delete Successfully" };
}
catch (Exception ex)
{
return new Status
{ Result = "Error", Message = ex.Message.ToString() };
}
}
#endregion
}
}
Step 6
Now, let’s enable CORS. Go to Tools, open NuGet Package Manager, search for Cors, and install the Microsoft.Asp.Net.WebApi.Cors package.
Open Webapiconfig.cs and add the following lines.
EnableCorsAttribute cors = new EnableCorsAttribute("*", "*", "*");
config.EnableCors(cors);
Create an Project
Step 1
Create an Angular 7 project with the name “CrudwithMongoDB” by using the following command.
ng new CrudwithMongoDB
Step 2
Open Visual Studio Code, open the newly created project, and add bootstrap to this project.
npm install bootstrap --save
Step 3
Now, create two components for displaying the employee list page and adding a new employee page. To create the components, open terminal and use the following commands.
ng g c employee
ng g c addemployee
Step 4
Create a class named “employee” by using the following command.
ng g class employee
Add the required properties in the class.
export class Employee {
Id: string;
Name: string;
Department: string;
Address: string;
City: string;
Country: string;
}
Step 5
Create a service to call the Web API.
ng g s emprecord
Step 6
Open the emprecord service and import the required packages and classes. Add the following lines of code in the emprecord.service.ts file.
import { Injectable } from '@angular/core';
import { Observable } from "rxjs";
import {HttpHeaders, HttpClient } from "@angular/common/http";
import { Employee } from "../app/employee";
@Injectable({
providedIn: 'root'
})
export class EmprecordService {
Url="http://localhost:14026/Api/Employee/";
constructor(private http:HttpClient) { }
InsertEmployee(employee:Employee)
{
debugger;
const httpOptions = { headers: new HttpHeaders({ 'Content-Type': 'application/json' }) };
return this.http.post<Employee[]>(this.Url+'/InsertEmployee/', employee,httpOptions)
}
GetEmployeeRecord():Observable<Employee[]>
{
debugger;
return this.http.get<Employee[]>(this.Url+"/GetAllEmployee")
}
DeleteEmployee(id:string):Observable<number>
{
debugger;
return this.http.get<number>(this.Url + '/Delete/?id='+id);
}
GetEmployeeById(id:string)
{
return this.http.get<Employee>(this.Url + '/GetEmployeeById/?id=' + id);
}
UpdatEmplouee(employee:Employee)
{
debugger;
const httpOptions = { headers: new HttpHeaders({ 'Content-Type': 'application/json' }) };
return this.http.post<Employee[]>(this.Url+'/UpdateEmployee/', employee,httpOptions)
}
}
Step 7 - Add Paging and Searching
To add paging and searching, install the following library in the project. For pagination:
npm install --save ngx-pagination
For searching:
npm i ng2-search-filter --save
Export and import both these directives in the app.module.ts file.
import {NgxPaginationModule} from 'ngx-pagination';
import { Ng2SearchPipeModule } from 'ng2-search-filter';
Step 8
Now, open addemployee.component.html and add the following HTML.
<div class="container" style="padding-top:40px;">
<div class="row">
<div class="col-md-10 mx-auto">
<div class="card mx-4">
<div class="card-head p-4">
<div class="col-sm-12 btn btn-success">
Employee's Information
</div>
</div>
<div class="card-body p-4">
<form [formGroup]="Addemployee" (ngSubmit)="onFormSubmit(Addemployee.value)">
<div class="col-sm-12">
<div class="card-body">
<!-- <div class="row"> -->
<div class="form-group ">
<label class="col-sm-2 control-label" for="Name">Name</label>
<div class="col-sm-10">
<input type="text" class="form-control" placeholder="Enter name" formControlName="Name">
</div>
</div>
<div class="form-group ">
<label class="col-sm-2 control-label" for="Department">Department</label>
<div class="col-sm-10">
<input type="text" class="form-control" placeholder="Enter Department" formControlName="Department">
</div>
</div>
<div class="form-group ">
<label class="col-sm-2 control-label" for="Address">Address</label>
<div class="col-sm-10">
<input type="text" class="form-control" placeholder="Enter Address" formControlName="Address">
</div>
</div>
<div class="form-group ">
<label class="col-sm-2 control-label" for="City">City</label>
<div class="col-sm-10">
<input type="text" class="form-control" placeholder="Enter City" formControlName="City">
</div>
</div>
<div class="form-group ">
<label class="col-sm-2 control-label" for="Country">Country</label>
<div class="col-sm-10">
<input type="text" class="form-control" placeholder="Enter Country" formControlName="Country">
</div>
</div>
</div>
</div>
<div class="col-6 text-right">
<button class="btn btn-primary px-10" type="submit">Add </button>
</div>
</form>
</div>
</div>
</div>
</div>
</div>
Step 9
Open the addemployee.componet.ts file and add the following lines.
import { Component, OnInit } from '@angular/core';
import { HttpClient } from "@angular/common/http";
import { FormGroup, FormControl } from '@angular/forms';
import { EmprecordService } from "../../emprecord.service";
import { Employee } from "../../employee";
import { Observable } from "rxjs";
import { identifierModuleUrl } from '@angular/compiler';
import { Router } from '@angular/router';
@Component({
selector: 'app-addemployee',
templateUrl: './addemployee.component.html',
styleUrls: ['./addemployee.component.css']
})
export class AddemployeeComponent implements OnInit {
massage: string;
dataSaved = false;
Addemployee:FormGroup;
EmployeeIdUpdate = "0";
constructor(private router: Router,private emprecordService:EmprecordService) { }
InsertEmployee(employee:Employee)
{
debugger;
if (this.EmployeeIdUpdate != "0") employee.Id=this.EmployeeIdUpdate;
this.emprecordService.InsertEmployee(employee).subscribe(
()=>
{
if (this.EmployeeIdUpdate == "0") {
this.massage = 'Saved Successfully';
}
else
{
this.massage = 'Update Successfully';
}
this.dataSaved = true;
this.router.navigate(['/employee']);
})
}
onFormSubmit() {
const Emp = this.Addemployee.value;
this.InsertEmployee(Emp);
}
EmployeeEdit(id: string) {
debugger;
this.emprecordService.GetEmployeeById(id).subscribe(emp => {
this.massage = null;
this.dataSaved = false;
debugger;
this.EmployeeIdUpdate=id;
this.Addemployee.controls['Name'].setValue(emp.Name);
this.Addemployee.controls['Department'].setValue(emp.Department);
this.Addemployee.controls['City'].setValue(emp.City);
this.Addemployee.controls['Country'].setValue(emp.Country);
this.Addemployee.controls['Address'].setValue(emp.Address);
});
debugger;
}
clearform() {
debugger;
this.Addemployee.controls['Name'].setValue("");
this.Addemployee.controls['Department'].setValue("");
this.Addemployee.controls['Address'].setValue("");
this.Addemployee.controls['City'].setValue("");
this.Addemployee.controls['Country'].setValue("");
}
ngOnInit() {
this.Addemployee = new FormGroup({
Name: new FormControl(),
Department:new FormControl(),
Address:new FormControl(),
City:new FormControl(),
Country:new FormControl(),
});
let Id = localStorage.getItem("id");
if(Id!=null)
{
this.EmployeeEdit(Id) ;
}}
}
Step 10
Open employee.componet.html and add this HTML.
<div class="container" style="margin-bottom:20px;padding-top:20px;">
<div class="row">
<div class="col-sm-12 btn btn-success">
Employee's Information
</div>
</div>
<div class="col-sm-12" style="margin-bottom:20px;padding-top:20px;">
<div class="row">
<div class="col-sm-6">
<button type="button" class="btn btn-primary" data-toggle="modal" routerLink="/addemployee">
Add New Employee
</button>
</div>
<div class="col-sm-6">
<input class="form-control" type="text" name="search" [(ngModel)]="filter" placeholder="Search">
</div>
</div>
</div>
</div>
<div class="container" style="padding-top:20px;">
<table class="table table-striped">
<thead class="thead-dark">
<th>Name</th>
<th>Department</th>
<th>Address</th>
<th>City</th>
<th>Country</th>
<th>Action</th>
</thead>
<tbody>
<tr *ngFor="let e of emp | async|filter:filter| paginate: { itemsPerPage: 5, currentPage: p } ; let i=index">
<td>{{e.Name}}</td>
<td>{{e.Department}}</td>
<td>{{e.Address}}</td>
<td>{{e.City}}</td>
<td>{{e.Country}}</td>
<td>
<div class="btn-group">
<button type="button" class="btn btn-primary mr-1" (click)="EmployeeEdit(e.Id)">Edit</button>
<button type="button" class="btn btn-danger mr-1" (click)="Deleteemployee(e.Id)">Delete</button>
</div>
</td>
</tr>
</tbody>
</table>
<ul class="pagination">
<pagination-controls (pageChange)="p = $event"></pagination-controls>
</ul>
</div>
Step 11
Open employee.componet.ts file and add the following lines.
import { Component, OnInit } from '@angular/core';
import { Employee } from "../employee";
import { EmprecordService } from "../emprecord.service";
import { Observable } from "rxjs";
import { Router } from '@angular/router';
@Component({
selector: 'app-employee',
templateUrl: './employee.component.html',
styleUrls: ['./employee.component.css']
})
export class EmployeeComponent implements OnInit {
private emp: Observable<Employee[]>;
massage:String;
dataSaved=false;
constructor(private router: Router,private emprecordService:EmprecordService) { }
Loademployee()
{
debugger;
this.emp = this.emprecordService.GetEmployeeRecord();
console.log(this.emp);
debugger;
}
EmployeeEdit(id: string) {
debugger;
localStorage.removeItem("id");
localStorage.setItem("id",id.toString());
this.router.navigate(['/addemployee'], { queryParams: { Id: id } });
debugger;
}
Deleteemployee(id: string) {
if (confirm("Are You Sure To Delete this Informations")) {
this.emprecordService.DeleteEmployee(id).subscribe(
() => {
this.dataSaved = true;
this.massage = "Deleted Successfully";
}
);
}
}
ngOnInit() {
localStorage.clear();
this.Loademployee();
}
}
Step 12
Now, open app-routing.module.ts file and add the following lines to create routing.
import { NgModule } from '@angular/core';
import { Routes, RouterModule } from '@angular/router';
import { EmployeeComponent } from "./employee/employee.component";
import { AddemployeeComponent } from "./employee/addemployee/addemployee.component";
const routes: Routes = [
{path:"employee",component:EmployeeComponent},
{path:"addemployee",component:AddemployeeComponent},
];
@NgModule({
imports: [RouterModule.forRoot(routes)],
exports: [RouterModule]
})
export class AppRoutingModule { }
Step 13
Now, open app.module.ts file and add the following lines.
import { BrowserModule } from '@angular/platform-browser';
import { NgModule } from '@angular/core';
import { FormsModule } from '@angular/forms';
import { AppRoutingModule } from './app-routing.module';
import { AppComponent } from './app.component';
import { HttpClientModule,HttpClient} from '@angular/common/http';
import { EmployeeComponent } from './employee/employee.component';
import { ReactiveFormsModule } from "@angular/forms";
import { EmprecordService } from "../app/emprecord.service";
import { AddemployeeComponent } from './employee/addemployee/addemployee.component';
import {NgxPaginationModule} from 'ngx-pagination';
import { Ng2SearchPipeModule } from 'ng2-search-filter';
@NgModule({
declarations: [
AppComponent,
EmployeeComponent,
AddemployeeComponent,
],
imports: [
BrowserModule,FormsModule,
AppRoutingModule,HttpClientModule,ReactiveFormsModule,Ng2SearchPipeModule,NgxPaginationModule
],
providers: [EmprecordService],
bootstrap: [AppComponent]
})
export class AppModule { }
Step 14
Now, let us run the project and redirect the URL to the Addemployee page.
Step 15
Enter the details and click on the Add button.
Summary
In this article, we discussed how to perform CRUD operations using MongoDB, Angular 8, and Asp.net Web API.
#Angular #MongoDB #dotnet #webdev #api
1633367280
単純な挿入MYSQLデータベースを使用したASP.NETでの更新と削除の選択
この記事では、ASP.NET WebアプリケーションからMySQLデータベースにデータの選択、更新、および削除を挿入する方法について説明します。
それでは、次の手順に進みましょう。
- ASP.NETWebページ
- グリッドビューデータコントロールとMySQLデータベース
次に、MySQLAdminページを開き、[Create A New Table]-> [View]-> [Table Structure for Table`student`]を選択します。
CREATE TABLE IF NOT EXISTS `student` (
`SID` int(100) NOT NULL AUTO_INCREMENT,
`Name` varchar(100) NOT NULL,
`Address` varchar(500) NOT NULL,
`Email` varchar(100) NOT NULL,
`Mobile` varchar(25) NOT NULL,
PRIMARY KEY (`SID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=31 ; 
Visual Studio 2012のインスタンスを開き、新しいASP.NETWebアプリケーションを作成します。次の図に示すように、プロジェクトに「MYSQLCRUDApplication」という名前を付け
ます。コードビハインドファイル（Student.aspx.cs）に、次のようにコードを記述します
Student.aspx
<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true"
CodeBehind="Student.aspx.cs" Inherits="MYSQLCRUDApplication.Student" %>
<asp:Content ID="Content1" ContentPlaceHolderID="titleContent" runat="server">
Simple Insert Select Update and Delete in ASP.NET using MySQL Database
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="MainContent" runat="server">
<table>
<tr>
<td class="td">Name:</td>
<td>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
<td>
<asp:Label ID="lblSID" runat="server" Visible="false"></asp:Label> </td>
</tr>
<tr>
<td class="td">Address:</td>
<td>
<asp:TextBox ID="txtAddress" runat="server"></asp:TextBox></td>
<td> </td>
</tr>
<tr>
<td class="td">Mobile:</td>
<td>
<asp:TextBox ID="txtMobile" runat="server"></asp:TextBox></td>
<td> </td>
</tr>
<tr>
<td class="td">Email ID:</td>
<td>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></td>
<td> </td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" />
<asp:Button ID="btnUpdate" runat="server" Text="Update" Visible="false"
OnClick="btnUpdate_Click" />
<asp:Button ID="btnCancel" runat="server" Text="Cancel" OnClick="btnCancel_Click" /></td>
<td></td>
</tr>
</table>
<div style="padding: 10px; margin: 0px; width: 100%;">
<p>
Total Student:<asp:Label ID="lbltotalcount" runat="server" Font-Bold="true"></asp:Label>
</p>
<asp:GridView ID="GridViewStudent" runat="server" DataKeyNames="SID"
OnSelectedIndexChanged="GridViewStudent_SelectedIndexChanged"
OnRowDeleting="GridViewStudent_RowDeleting">
<Columns>
<asp:CommandField HeaderText="Update" ShowSelectButton="True" />
<asp:CommandField HeaderText="Delete" ShowDeleteButton="True" />
</Columns>
</asp:GridView>
</div>
</asp:Content> Web.configファイルで、次のように接続文字列を作成します。
Web.config
<connectionStrings>
<add name="ConnectionString"
connectionString="Server=localhost;userid=root;password=;Database=Testdb"
providerName="MySql.Data.MySqlClient"/>
</connectionStrings> ここで、コードビハインドファイル「Student.aspx.cs」で次のコードを使用します。
Student.aspx.cs
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
namespace MYSQLCRUDApplication
{
public partial class Student : System.Web.UI.Page
{
#region MySqlConnection Connection and Page Lode
MySqlConnection conn = new
MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
Try
{
if (!Page.IsPostBack)
{
BindGridView();
}
}
catch (Exception ex)
{
ShowMessage(ex.Message);
}
}
#endregion
#region show message
/// <summary>
/// This function is used for show message.
/// </summary>
/// <param name="msg"></param>
void ShowMessage(string msg)
{
ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script
language='javascript'>alert('" + msg + "');</script>");
}
/// <summary>
/// This Function is used TextBox Empty.
/// </summary>
void clear()
{
txtName.Text = string.Empty; txtAddress.Text = string.Empty; txtMobile.Text = string.Empty;
txtEmail.Text = string.Empty;
txtName.Focus();
}
#endregion
#region bind data to GridViewStudent
private void BindGridView()
{
Try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
MySqlCommand cmd = new MySqlCommand("Select * from Student ORDER BY SID DESC;",
conn);
MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);
GridViewStudent.DataSource = ds;
GridViewStudent.DataBind();
lbltotalcount.Text = GridViewStudent.Rows.Count.ToString();
}
catch (MySqlException ex)
{
ShowMessage(ex.Message);
}
Finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
#endregion
#region Insert Data
/// <summary>
/// this code used to Student Data insert in MYSQL Database
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSubmit_Click(object sender, EventArgs e)
{
Try
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("Insert into student (Name,Address,Mobile,Email )
values (@Name,@Address,@Mobile,@Email)", conn);
cmd.Parameters.AddWithValue("@Name",txtName.Text);
cmd.Parameters.AddWithValue("@Address", txtAddress.Text);
cmd.Parameters.AddWithValue("@Mobile",txtMobile.Text);
cmd.Parameters.AddWithValue("@Email",txtEmail.Text);
cmd.ExecuteNonQuery();
cmd.Dispose();
ShowMessage("Registered successfully......!");
clear();
BindGridView();
}
catch (MySqlException ex)
{
ShowMessage(ex.Message);
}
Finally
{
conn.Close();
}
}
#endregion
#region SelectedIndexChanged
/// <summary>
/// this code used to GridViewRow SelectedIndexChanged value show textbox
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void GridViewStudent_SelectedIndexChanged(object sender, EventArgs e)
{
GridViewRow row = GridViewStudent.SelectedRow;
lblSID.Text = row.Cells[2].Text;
txtName.Text = row.Cells[3].Text;
txtAddress.Text = row.Cells[4].Text;
txtEmail.Text = row.Cells[5].Text;
txtMobile.Text = row.Cells[6].Text;
btnSubmit.Visible = false;
btnUpdate.Visible = true;
}
#endregion
#region Delete Student Data
/// <summary>
/// This code used to GridViewStudent_RowDeleting Student Data Delete
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void GridViewStudent_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
Try
{
conn.Open();
int SID = Convert.ToInt32(GridViewStudent.DataKeys[e.RowIndex].Value);
MySqlCommand cmd = new MySqlCommand("Delete From student where SID='" + SID + "'",
conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
ShowMessage("Student Data Delete Successfully......!");
GridViewStudent.EditIndex = -1;
BindGridView();
}
catch (MySqlException ex)
{
ShowMessage(ex.Message);
}
Finally
{
conn.Close();
}
}
#endregion
#region student data update
/// <summary>
/// This code used to student data update
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnUpdate_Click(object sender, EventArgs e)
{
Try
{
conn.Open();
string SID = lblSID.Text;
MySqlCommand cmd = new MySqlCommand("update student Set
Name=@Name,Address=@Address,Mobile=@Mobile,Email=@Email where SID=@SID", conn);
cmd.Parameters.AddWithValue("@Name", txtName.Text);
cmd.Parameters.AddWithValue("@Address", txtAddress.Text);
cmd.Parameters.AddWithValue("@Mobile", txtMobile.Text);
cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
cmd.Parameters.AddWithValue("SID",SID);
cmd.ExecuteNonQuery();
cmd.Dispose();
ShowMessage("Student Data update Successfully......!");
GridViewStudent.EditIndex = -1;
BindGridView(); btnUpdate.Visible = false;
}
catch (MySqlException ex)
{
ShowMessage(ex.Message);
}
Finally
{
conn.Close();
}
}
#endregion
#region textbox clear
protected void btnCancel_Click(object sender, EventArgs e)
{
clear();
}
#endregion
}
} ページを実行すると、次のようになります。
次に、学生データの挿入とグリッドビューのデータの表示を入力します。メッセージボックス「正常に登録されました」。
次に、Studentを選択し、データTextBoxを表示して、メッセージボックス「StudentDataupdatesuccessfully」に表示されているデータを更新します。
ここで、メッセージボックス「StudentDataDeleteSuccessfully」に表示されているStudentデータを削除します。
この記事がお役に立てば幸いです。他にご不明な点がございましたら、以下にコメントをお寄せください。
1621850444
List of Available Database for Current User In SQL Server
Introduction
When working in the SQL Server, we may have to check some other databases other than the current one which we are working. In that scenario we may not be sure that does we have access to those Databases?. In this article we discuss the list of databases that are available for the current logged user in SQL Server
Get the list of database
Conclusion
#sql server #available databases for current user #check database has access #list of available database #sql #sql query #sql server database #sql tips #sql tips and tricks #tips
1620633584
System Databases in SQL Server
Introduction
In SSMS, we many of may noticed System Databases under the Database Folder. But how many of us knows its purpose?. In this article lets discuss about the System Databases in SQL Server.
System Database
Fig. 1 System Databases
There are five system databases, these databases are created while installing SQL Server.
- Master
- Model
- MSDB
- Tempdb
- Resource
Master
- This database contains all the System level Information in SQL Server. The Information in form of Meta data.
- Because of this master database, we are able to access the SQL Server (On premise SQL Server)
Model
- This database is used as a template for new databases.
- Whenever a new database is created, initially a copy of model database is what created as new database.
MSDB
- This database is where a service called SQL Server Agent stores its data.
- SQL server Agent is in charge of automation, which includes entities such as jobs, schedules, and alerts.
TempDB
- The Tempdb is where SQL Server stores temporary data such as work tables, sort space, row versioning information and etc.
- User can create their own version of temporary tables and those are stored in Tempdb.
- But this database is destroyed and recreated every time when we restart the instance of SQL Server.
Resource
- The resource database is a hidden, read only database that holds the definitions of all system objects.
- When we query system object in a database, they appear to reside in the sys schema of the local database, but in actually their definitions reside in the resource db.
#sql server #master system database #model system database #msdb system database #sql server system databases #ssms #system database #system databases in sql server #tempdb system database