Windows Store Apps and SQL Server Database Part 2 (C#) |
Windows Store Apps and SQL Server Database Part 2 (C#) หัวข้อนี้จะเป็น Part ต่อจากบทความก่อนหน้านี้ เกี่ยวกับ Windows Store Apps กับ SQL Server Database โดยเราจะมาประยุกต์การเขียน Windows Store Apps เพื่อทำการ Insert / Update/ Delete ข้อมูลที่อยู่บน SQL Server ผ่านช่องทาง Web Services และสำหรับวิธีการนั้น บน Web Services จะต้องทำการออกแบบ Method สำหรับรับค่า Parameters ที่จะส่งมาจาก Windows Store Apps จากนั้นเมื่อได้ค่า Paramters ต่าง ๆ แล้ว Web Services จะคอยทำหน้าที่นำค่าที่ได้ไป Insert ลงใน SQL Server
Windows Store Apps and SQL Server Database
Windows Store Apps and SQL Server Database Part 1 (C#)
ขั้นตอนที่ 1 การออกแบบ Web Services กับ SQL Server Database
CREATE TABLE [dbo].[customer](
[CustomerID] [varchar](4) NOT NULL,
[Name] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[CountryCode] [varchar](2) NULL,
[Budget] [float] NULL,
[Used] [float] NULL,
CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
))
INSERT INTO [customer] VALUES ('C001', 'Win Weerachai', '[email protected]', 'TH', 1000000, 600000)
INSERT INTO [customer] VALUES ('C002', 'John Smith', '[email protected]', 'UK', 2000000, 800000)
INSERT INTO [customer] VALUES ('C003', 'Jame Born', '[email protected]', 'US', 3000000, 600000)
INSERT INTO [customer] VALUES ('C004', 'Chalee Angel', '[email protected]', 'US', 4000000, 100000)
โครงสร้าง Table บน SQL Server
ออกแบบ Method บน Web Services ที่ทำหน้าที่รับค่า Parameters
public string AddData(string sCustomerID,
string sName,
string sEmail,
string sCountryCode,
string sBudget,
string sUsed)
{
}
การ Insert ข้อมูลลงใน Table ของ SQL Server
strSQL = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) VALUES " +
" ('" + sCustomerID + "' " +
" ,'" + sName + "' " +
" ,'" + sEmail + "' " +
" ,'" + sCountryCode + "' " +
" ,'" + sBudget + "' " +
" ,'" + sUsed + "') ";
Code ทั้งหมด
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using Newtonsoft.Json;
using System.Data.SqlClient;
namespace myAppWeb
{
/// <summary>
/// Summary description for myWSV
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class myWSV : System.Web.Services.WebService
{
[WebMethod]
public string AddData(string sCustomerID,
string sName,
string sEmail,
string sCountryCode,
string sBudget,
string sUsed)
{
try
{
SqlConnection objConn = new SqlConnection();
SqlCommand objCmd = new SqlCommand();
String strConnString, strSQL;
strConnString = "Server=localhost;UID=sa;PASSWORD=mypassword;Database=mydatabase;Max Pool Size=400;Connect Timeout=600;";
objConn.ConnectionString = strConnString;
objConn.Open();
strSQL = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) VALUES " +
" ('" + sCustomerID + "' " +
" ,'" + sName + "' " +
" ,'" + sEmail + "' " +
" ,'" + sCountryCode + "' " +
" ,'" + sBudget + "' " +
" ,'" + sUsed + "') ";
objCmd = new SqlCommand();
objCmd.Connection = objConn;
objCmd.CommandText = strSQL;
objCmd.CommandType = CommandType.Text;
objCmd.ExecuteNonQuery();
return "1"; // return sccess.
}
catch (Exception ex)
{
return "0"; // return failed.
}
}
}
}
ขั้นตอนที่ 2 บน Windows Store เรียกใช้งาน Web Services และส่งค่า Parameters ไปที่ Web Services
var client = new myWebServices.myWSVSoapClient();
var result = await client.AddDataAsync(this.txtCustomerID.Text,
this.txtName.Text,
this.txtEmail.Text,
this.txtCountryCode.Text,
this.txtBudget.Text,
this.txtUsed.Text);
กรณีที่ทำการ Add Services จาก Web Services อยู่แล้ว สามารถคลิก Update เพื่อทำการ Update Method ใหม่ ๆ
MainPage.xaml
ออกแบบ Form สำหรับ Input ข้อมูล
<Page
x:Class="WindowsStoreApps.MainPage"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:local="using:WindowsStoreApps"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="d">
<Grid Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">
<TextBlock HorizontalAlignment="Left" Margin="524,57,0,0" TextWrapping="Wrap" Text="Add Data" VerticalAlignment="Top" FontSize="25"/>
<TextBlock HorizontalAlignment="Left" Margin="364,115,0,0" TextWrapping="Wrap" Text="CustomerID :" VerticalAlignment="Top" FontSize="20"/>
<TextBox x:Name="txtCustomerID" HorizontalAlignment="Left" Height="36" Margin="669,116,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="326" FontSize="20" FontFamily="Global User Interface"/>
<TextBlock HorizontalAlignment="Left" Margin="362,182,0,0" TextWrapping="Wrap" Text="Name : " VerticalAlignment="Top" FontSize="20"/>
<TextBox x:Name="txtName" HorizontalAlignment="Left" Height="36" Margin="667,176,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="319" FontSize="20" FontFamily="Global User Interface"/>
<TextBlock HorizontalAlignment="Left" Margin="362,237,0,0" TextWrapping="Wrap" Text="Email : " VerticalAlignment="Top" FontSize="20"/>
<TextBox x:Name="txtEmail" HorizontalAlignment="Left" Height="36" Margin="667,235,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="326" FontSize="20" FontFamily="Global User Interface"/>
<TextBlock HorizontalAlignment="Left" Margin="362,303,0,0" TextWrapping="Wrap" Text="Country : " VerticalAlignment="Top" FontSize="20"/>
<TextBox x:Name="txtCountryCode" HorizontalAlignment="Left" Height="36" Margin="667,299,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="326" FontSize="20" FontFamily="Global User Interface"/>
<TextBlock HorizontalAlignment="Left" Margin="362,369,0,0" TextWrapping="Wrap" Text="Budget :" VerticalAlignment="Top" FontSize="20"/>
<TextBox x:Name="txtBudget" HorizontalAlignment="Left" Height="36" Margin="667,365,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="326" FontSize="20" FontFamily="Global User Interface"/>
<TextBlock HorizontalAlignment="Left" Margin="364,433,0,0" TextWrapping="Wrap" Text="Used : " VerticalAlignment="Top" FontSize="20"/>
<TextBox x:Name="txtUsed" HorizontalAlignment="Left" Height="36" Margin="669,434,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="326" FontSize="20" FontFamily="Global User Interface"/>
<Button x:Name="btnSave" Content="Save" HorizontalAlignment="Left" Margin="554,522,0,0" VerticalAlignment="Top" FontSize="20" Width="120" FontFamily="Global User Interface" Click="btnSave_Click"/>
</Grid>
</Page>
MainPage.xaml.cs
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using Windows.Devices.Geolocation;
using Windows.Foundation;
using Windows.Foundation.Collections;
using Windows.UI.Core;
using Windows.UI.Xaml;
using Windows.UI.Xaml.Controls;
using Windows.UI.Xaml.Controls.Primitives;
using Windows.UI.Xaml.Data;
using Windows.UI.Xaml.Input;
using Windows.UI.Xaml.Media;
using Windows.UI.Xaml.Navigation;
using WindowsStoreApps.myWebServices;
using Windows.UI.Popups;
// The Blank Page item template is documented at http://go.microsoft.com/fwlink/?LinkId=234238
namespace WindowsStoreApps
{
/// <summary>
/// An empty page that can be used on its own or navigated to within a Frame.
/// </summary>
///
public sealed partial class MainPage : Page
{
public MainPage()
{
this.InitializeComponent();
}
private async void btnSave_Click(object sender, RoutedEventArgs e)
{
var client = new myWebServices.myWSVSoapClient();
var result = await client.AddDataAsync(this.txtCustomerID.Text,
this.txtName.Text,
this.txtEmail.Text,
this.txtCountryCode.Text,
this.txtBudget.Text,
this.txtUsed.Text);
string jsonData = result.Body.AddDataResult;
if (jsonData == "0")
{
MessageDialog msgDialog = new MessageDialog("Add Data Failed", "Error");
await msgDialog.ShowAsync();
}
else
{
MessageDialog msgDialog = new MessageDialog("Add Data Success.", "Success");
await msgDialog.ShowAsync();
}
}
}
}
Result
ทดสอบการทำงาน
ทดสอบการเพิ่มข้อมูล
แสดง Message ในกรณีที่ทำการเพิ่มข้อมูลเรียบร้อยแล้ว
เมื่อกลับไปดูที่ Table บน SQL Server จะเห็นว่ามีข้อมูลถูก Insert ลงใน Record
ตัวอย่างการ Update ข้อมูล
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using Newtonsoft.Json;
using System.Data.SqlClient;
namespace myAppWeb
{
/// <summary>
/// Summary description for myWSV
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class myWSV : System.Web.Services.WebService
{
[WebMethod]
public void UpdateData(string sCustomerID,
string sName,
string sEmail,
string sCountryCode,
string sBudget,
string sUsed)
{
SqlConnection objConn = new SqlConnection();
SqlCommand objCmd = new SqlCommand();
String strConnString, strSQL;
strConnString = "Server=localhost;UID=sa;PASSWORD=mypassword;Database=mydatabase;Max Pool Size=400;Connect Timeout=600;";
objConn.ConnectionString = strConnString;
objConn.Open();
strSQL = "UPDATE customer SET " +
" Name = '" + sName + "' " +
" ,Email = '" + sEmail + "' " +
" ,CountryCode = '" + sCountryCode + "' " +
" ,Budget = '" + sBudget + "' " +
" ,Used = '" + sUsed + "' " +
" WHERE CustomerID = '" + sCustomerID + "' ";
objCmd = new SqlCommand();
objCmd.Connection = objConn;
objCmd.CommandText = strSQL;
objCmd.CommandType = CommandType.Text;
objCmd.ExecuteNonQuery();
}
}
}
private async void btnSave_Click(object sender, RoutedEventArgs e)
{
var client = new myWebServices.myWSVSoapClient();
var result = await client.UpdateDataAsync(this.lblCustomerID.Text,
this.txtName.Text,
this.txtEmail.Text,
this.txtCountryCode.Text,
this.txtBudget.Text,
this.txtUsed.Text);
this.Frame.Navigate(typeof(MainPage));
}
ตัวอย่างการ Delete ข้อมูล
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using Newtonsoft.Json;
using System.Data.SqlClient;
namespace myAppWeb
{
/// <summary>
/// Summary description for myWSV
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class myWSV : System.Web.Services.WebService
{
[WebMethod]
public void DeleteData(string sCustomerID)
{
SqlConnection objConn = new SqlConnection();
SqlCommand objCmd = new SqlCommand();
String strConnString, strSQL;
strConnString = "Server=localhost;UID=sa;PASSWORD=mypassword;Database=mydatabase;Max Pool Size=400;Connect Timeout=600;";
strSQL = "DELETE FROM customer WHERE CustomerID ='" + sCustomerID + "'";
objConn.ConnectionString = strConnString;
objConn.Open();
objCmd = new SqlCommand();
objCmd.Connection = objConn;
objCmd.CommandText = strSQL;
objCmd.CommandType = CommandType.Text;
objCmd.ExecuteNonQuery();
}
}
}
private async void btnDelete_Click(object sender, RoutedEventArgs e)
{
string CustomerID = "C001";
var client = new myWebServices.myWSVSoapClient();
var result = await client.DeleteDataAsync(CustomerID);
}
.
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน 
Load balance : Server 05
