Example 7 : Windows Store App and Login Form (Web Services) - C#

CREATE TABLE `member` (
  `MemberID` int(2) NOT NULL auto_increment,
  `Username` varchar(50) NOT NULL,
  `Password` varchar(50) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Tel` varchar(50) NOT NULL,
  `Email` varchar(150) NOT NULL,
  PRIMARY KEY  (`MemberID`),
  UNIQUE KEY `Username` (`Username`),
  UNIQUE KEY `Email` (`Email`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;


INSERT INTO `member` VALUES (1, 'weerachai', 'weerachai@1', 'Weerachai Nukitram', '0819876107', '[email protected]');
INSERT INTO `member` VALUES (2, 'adisorn', 'adisorn@2', 'Adisorn Bunsong', '021978032', '[email protected]');
INSERT INTO `member` VALUES (3, 'surachai', 'surachai@3', 'Surachai Sirisart', '0876543210', '[email protected]');

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

        [WebMethod]
        public string CheckUsernamePassword(string sUsername,string sPassword)
        {
            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 member WHERE Username = '" + sUsername + "' AND Password = '" + sPassword + "' ";

            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;


            if (dt.Rows.Count == 0)
            {
                return "0"; // Login Failed
            }
            else
            {
                return "1"; // Login Complete
            }
        }

        [WebMethod]
        public string getMemberByUsername(string sUsername)
        {
            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 member WHERE Username ='" + sUsername + "'";

            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;

        }

            var client = new myWebServices.myWSVSoapClient();
            var result = await client.CheckUsernamePasswordAsync(this.txtUsername.Text, this.txtPassword.Password.ToString());

            string jsonData = result.Body.CheckUsernamePasswordResult;

            if (jsonData == "0")
            {
                MessageDialog msgDialog = new MessageDialog("Username or Password Incorrect!", "Error");
                await msgDialog.ShowAsync();
            }
            else
            {
                this.Frame.Navigate(typeof(InfoPage), this.txtUsername.Text);
            }

            var client = new myWebServices.myWSVSoapClient();
            var result = await client.getMemberByUsernameAsync(sUsername);

            string jsonData = result.Body.getMemberByUsernameResult;

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

            myMember customer = list.First();

            this.lblMemberID.Text = customer.MemberID;
            this.lblUsername.Text = customer.Username;
            this.lblName.Text = customer.Name;
            this.lblTel.Text = customer.Tel;
            this.lblEmail.Text = customer.Email;

<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,98,0,0" TextWrapping="Wrap" Text="Login Username / Password" VerticalAlignment="Top" FontSize="25"/>

        <TextBlock HorizontalAlignment="Left" Margin="454,214,0,0" TextWrapping="Wrap" Text="Username" VerticalAlignment="Top" FontSize="25"/>

        <TextBox x:Name="txtUsername" HorizontalAlignment="Left" Margin="724,206,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="198" FontSize="20" FontFamily="Global User Interface"/>

        <TextBlock HorizontalAlignment="Left" Margin="454,292,0,0" TextWrapping="Wrap" Text="Password" VerticalAlignment="Top" FontSize="25"/>

        <PasswordBox x:Name="txtPassword" HorizontalAlignment="Left" Margin="722,290,0,0" VerticalAlignment="Top" Width="202" FontFamily="Global User Interface" FontSize="20"/>

        

        <Button x:Name="btnLogin" Content="Login" HorizontalAlignment="Left" Margin="614,393,0,0" VerticalAlignment="Top" FontSize="20" Width="120" FontFamily="Global User Interface" Click="btnLogin_Click"/>



    </Grid>

</Page>