Example 4 : Windows Store App and Update Data (Web Services) - C#

CREATE TABLE `customer` (
  `CustomerID` varchar(4) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Email` varchar(50) NOT NULL,
  `CountryCode` varchar(2) NOT NULL,
  `Budget` double NOT NULL,
  `Used` double NOT NULL,
  PRIMARY KEY  (`CustomerID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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);

using System.Data;
using Newtonsoft.Json;
using MySql.Data.MySqlClient;

        [WebMethod]
        public string getCustomerData(string sName)
        {
            MySqlConnection objConn = new MySqlConnection();
            MySqlCommand objCmd = new MySqlCommand();
            MySqlDataAdapter dtAdapter = new MySqlDataAdapter();

            DataSet ds = new DataSet();
            DataTable dt;
            String strConnString, strSQL;


            strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false";
            strSQL = "SELECT * FROM customer WHERE Name LIKE '%" + sName + "%'";

            objConn.ConnectionString = strConnString;

            objCmd.Connection = objConn;
            objCmd.CommandText = strSQL;
            objCmd.CommandType = CommandType.Text;

            dtAdapter.SelectCommand = objCmd;

            dtAdapter.Fill(ds);
            dt = ds.Tables[0];

            dtAdapter = null;
            objConn.Close();
            objConn = null;

            string json = JsonConvert.SerializeObject(dt, Formatting.Indented);
            return json;
        }

        [WebMethod]
        public string getCustomerByID(string sCustomerID)
        {
            MySqlConnection objConn = new MySqlConnection();
            MySqlCommand objCmd = new MySqlCommand();
            MySqlDataAdapter dtAdapter = new MySqlDataAdapter();

            DataSet ds = new DataSet();
            DataTable dt;
            String strConnString, strSQL;


            strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false";
            strSQL = "SELECT * FROM customer WHERE CustomerID ='" + sCustomerID + "'";

            objConn.ConnectionString = strConnString;

            objCmd.Connection = objConn;
            objCmd.CommandText = strSQL;
            objCmd.CommandType = CommandType.Text;

            dtAdapter.SelectCommand = objCmd;

            dtAdapter.Fill(ds);
            dt = ds.Tables[0];

            dtAdapter = null;
            objConn.Close();
            objConn = null;

            string json = JsonConvert.SerializeObject(dt, Formatting.Indented);
            return json;
        }

        [WebMethod]
        public void UpdateData(string sCustomerID,
            string sName,
            string sEmail,
            string sCountryCode,
            string sBudget,
            string sUsed)
        {

            MySqlConnection objConn = new MySqlConnection();
            MySqlCommand objCmd = new MySqlCommand();

            String strConnString, strSQL;

            strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false";

            objConn.ConnectionString = strConnString;
            objConn.Open();

            strSQL = "UPDATE customer SET " +
            " Name = '" + sName + "' " +
            " ,Email = '" + sEmail + "' " +
            " ,CountryCode = '" + sCountryCode + "' " +
            " ,Budget = '" + sBudget + "' " +
            " ,Used = '" + sUsed + "' " +
            " WHERE CustomerID = '" + sCustomerID + "' ";

            objCmd = new MySqlCommand();
            objCmd.Connection = objConn;
            objCmd.CommandText = strSQL;
            objCmd.CommandType = CommandType.Text;
            objCmd.ExecuteNonQuery();
        }

            var client = new myWebServices.myWSVSoapClient();
            var result = await client.getCustomerDataAsync(this.txtName.Text);

            string jsonData = result.Body.getCustomerDataResult;

            MemoryStream ms = new MemoryStream(Encoding.UTF8.GetBytes(jsonData));
            ObservableCollection<myCustomer> list = new ObservableCollection<myCustomer>();
            DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(ObservableCollection<myCustomer>));
            list = (ObservableCollection<myCustomer>)serializer.ReadObject(ms);

            var client = new myWebServices.myWSVSoapClient();
            var result = await client.getCustomerByIDAsync(sCustomerID);

            string jsonData = result.Body.getCustomerByIDResult;

            MemoryStream ms = new MemoryStream(Encoding.UTF8.GetBytes(jsonData));
            ObservableCollection<myCustomer> list = new ObservableCollection<myCustomer>();
            DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(ObservableCollection<myCustomer>));
            list = (ObservableCollection<myCustomer>)serializer.ReadObject(ms);

            myCustomer customer = list.First();

            this.lblCustomerID.Text = customer.CustomerID;
            this.txtName.Text = customer.Name;
            this.txtEmail.Text = customer.Email;
            this.txtCountryCode.Text = customer.CountryCode;
            this.txtBudget.Text = customer.Budget;
            this.txtUsed.Text = customer.Used;

            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);

<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="142,78,0,0" TextWrapping="Wrap" Text="Search (Name)" VerticalAlignment="Top" FontSize="25"/>
        <TextBox x:Name="txtName" HorizontalAlignment="Left" Margin="334,78,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="242" FontSize="20"/>
        <Button x:Name="btnSearch" Content="Search" HorizontalAlignment="Left" Margin="609,74,0,0" VerticalAlignment="Top" FontSize="20" Width="120" Click="btnSearch_Click" FontFamily="Global User Interface"/>

        <ListView x:Name="myListView" HorizontalAlignment="Left" Height="386" Margin="140,165,0,0" VerticalAlignment="Top" Width="907" SelectionChanged="myListView_SelectionChanged">
            <ListView.ItemTemplate>
                <DataTemplate>
                    <StackPanel Orientation="Horizontal" Margin="0,0,0,17">
                        <StackPanel Width="80">
                            <TextBlock Text="{Binding CustomerID}" TextWrapping="Wrap" FontSize="20" Foreground="#FFBFB9B9" Margin="5,0,0,0" FontFamily="Global User Interface"/>
                        </StackPanel>
                        <StackPanel Width="150">
                            <TextBlock Text="{Binding Name}" TextWrapping="Wrap" FontSize="20" Foreground="#FFBFB9B9" Margin="5,0,0,0"/>
                        </StackPanel>
                        <StackPanel Width="300">
                            <TextBlock Text="{Binding Email}" TextWrapping="Wrap" FontSize="20" Foreground="#FFBFB9B9" Margin="5,0,0,0"/>
                        </StackPanel>
                        <StackPanel Width="50">
                            <TextBlock Text="{Binding CountryCode}" TextWrapping="Wrap" FontSize="20" Foreground="#FFBFB9B9" Margin="5,0,0,0"/>
                        </StackPanel>
                        <StackPanel Width="100">
                            <TextBlock Text="{Binding Budget}" TextWrapping="Wrap" FontSize="20" Foreground="#FFBFB9B9" Margin="5,0,0,0"/>
                        </StackPanel>
                        <StackPanel Width="100">
                            <TextBlock Text="{Binding Used}" TextWrapping="Wrap" FontSize="20" Foreground="#FFBFB9B9" Margin="5,0,0,0"/>
                        </StackPanel>
                    </StackPanel>
                </DataTemplate>
            </ListView.ItemTemplate>
        </ListView>

    </Grid>

</Page>