CRUD in Metro Style using WCF

• Download source - 209.2 KB

Introduction

CRUD means Create, Retrieve (Read), Update and Delete. These are the four basic functions of persistent storage. Sometimes CRUD is also known as CRMD (Create, Retrieve, Modify and Destroy). Mostly these functions are the important parts of an LOB (Line of Business applications). If you are going to make a student marks MIS, the UI may contain: names of students, subjects, their max marks, obtained marks, and maybe a percentage. For this you have to use the CRUD for retrieving the record, entering a new record, deleting a record for modifying a record.

Background

This application will enable beginner programmers or others who have started making a Windows Metro style applications, which does not have a system.data reference. We are going to use WCF services to communicate with the SQL server, especially in the LOB type.

Tools required:

1. Visual studio 2012 or 2010 with metro style enabled.
2. Microsoft SQL Server Management Studio or use Visual Studio server explorer for database part.
3. Knowledge of C# and XAML.
4. A cup of tea, coffee or whatever you like.
5. The most important, a mind.

P.S.: We are using Microsoft SQL server in this project.

We start the project by creating the database part.

The Database Design

Start Visual Studio, click on the view tab then click on the SQL Server object explorer. The Server object explorer will be opened. After that, click on Add New Database and give it a name, then the db will be created.

After the db is created, the next part is to create the Table. Follow the steps in the below project to create the Table.

Click on add new table and then give it a name. The table will be created. Then click on the table and click on the view designer in which we will design the database. The below is the example of the designer.

Design the database like the above and click on the update and follow the next step. After you created the database, then follow the below steps to create the UI of Metro style.

Creating the UI

For creating the Metro UI, click on the new project, give it a name and then click ok, like in the below picture:

After the project will be created, you will have a blank, black screen. Here, you can design the UI.

The below picture shows my created UI.

This is my UI. I'm sure you're a better designer than I am. :)

Your UI must contain:

1. 3 Labels (Main label, Id, Name)
2. 2 Textboxes
3. 3 Buttons
4. 2 List boxes

After designing, give them names.

My XAML is below:

 <Page
    x:Class="wcfexamcrud.MainPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:local="using:wcfexamcrud"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    mc:Ignorable="d">
    <Page.Resources>
        <DataTemplate x:Key="uid">
            <Grid/>
        </DataTemplate>
    </Page.Resources>

    <Grid Background="{StaticResource ApplicationPageBackgroundThemeBrush}">
        <TextBox x:Name="uidtb" HorizontalAlignment="Left" 
        Margin="134,200,0,0" TextWrapping="Wrap" VerticalAlignment="
        Top" Width="169" Height="40" FontSize="20"/>
        <TextBox x:Name="nametb" HorizontalAlignment="Left" 
        Margin="134,269,0,0" TextWrapping="Wrap" VerticalAlignment="
        Top" Width="169" Height="40" FontSize="20"/>
        <TextBlock HorizontalAlignment="Left" Height="40" 
        Margin="10,200,0,0" TextWrapping="Wrap" 
        Text="UID" VerticalAlignment="Top" Width="55" 
        FontSize="30"/>
        <TextBlock HorizontalAlignment="Left" Height="40" 
        Margin="10,269,0,0" TextWrapping="Wrap" Text="Name" 
        VerticalAlignment="Top" Width="119" FontSize="30" 
        RenderTransformOrigin="0.442,2.612"/>
        <Button Content="Insert" HorizontalAlignment="Left" 
        Margin="25,383,0,0" VerticalAlignment="Top" 
        Width="177" Click="Button_Click_1"/>
        <Button Content="Update" HorizontalAlignment="Left" 
        Margin="253,383,0,0" VerticalAlignment="Top" 
        Width="177" Click="Button_Click_2"/>
        <Button Content="Delete" HorizontalAlignment="Left" 
        Margin="471,383,0,0" VerticalAlignment="Top" 
        Width="177" Click="Button_Click_3"/>
        <TextBlock HorizontalAlignment="Left" Margin="383.426,56.423,0,0" 
        TextWrapping="Wrap" Text="CRUD " VerticalAlignment="Top" 
        Width="510.708" RenderTransformOrigin="0.5,0.5" 
        UseLayoutRounding="False" d:LayoutRounding="Auto" 
        Height="60.461" FontSize="60" Foreground="#FF3CF712">
            <TextBlock.RenderTransform>
                <CompositeTransform Rotation="-0.045"/>
            </TextBlock.RenderTransform>
        </TextBlock>
        <ListBox x:Name="gv1" HorizontalAlignment="Left" 
        Height="100" Margin="38,470,0,0" VerticalAlignment="
        Top" Width="482"/>
        <ListBox x:Name="gv2" HorizontalAlignment="Left" 
        Height="100" Margin="576,470,0,0" 
        VerticalAlignment="Top" Width="482"/>

    </Grid>
</Page>

After the Design, click on the solution and click add on the add new solution and add the new project of WCF Service Application. See the picture below:

Click OK. It will add a new WCF project and open the two CS files, one is an interface and the second is a service which inherits the service, rename Iservice1 to Iservice. The C# codes of Iservice and service are as follows:

Iservice.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data;
namespace wsqlcon
{
    [ServiceContract]
    public interface IService
    {
        // TODO: Add your service operations here 

        [OperationContract]
        List<string> getusers();
        [OperationContract]
        List<string> getids();
        [OperationContract]
        int opi(usersinfo insert);
        [OperationContract]
        int opu(usersinfo update);
        [OperationContract]
        int opd(usersinfo id);      
    }

    // Use a data contract as illustrated in the sample below to 
    // add composite types to service operations.
    [DataContract]
    public class usersinfo
    {
        [DataMember]
        public int id { get; set; }

        [DataMember]
        public string name { get; set; } 
    }
}

In the interface, we have 5 operation contracts, the operation contract must have the operations which will be performed. In the class of usersinfo, we have the data member of id and the name with getter and setter. Basically, getters and setters are just means of helping encapsulation.

service1.svc.cs

The service1.svc.cs contains the main code to be performed, the sqlcommands, the List to grab all the data from the Dbs. Our service1.svc.cs contains the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace wsqlcon
{
    public class Service1 : IService
    {
        //Your connection string here.
        SqlConnection con = new SqlConnection
        (@"Data Source=BISMILLAH-PC\SQLEXPRESS;
        Initial Catalog=datagr;Integrated Security=True");


        public List<string> getids()
        {
            List<string> idslisr = new List<string>();
            con.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM wcff", con))
            {
                using (SqlDataReader idsreader = cmd.ExecuteReader())
                {
                    while (idsreader.Read())
                    {
                        idslisr.Add(idsreader[0].ToString());
                    }
                }
            }

            return idslisr;
        }

        public List<string> getusers()
        {
            List<string> userslist = new List<string>();
            con.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM wcff", con))
            {
                using (SqlDataReader reader = cmd.ExecuteReader())
              
                {
                    while (reader.Read())
                    {
                        userslist.Add(reader[1].ToString());
                    }
                }
            }

            return userslist;

        }
        public int opi (usersinfo insert)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand
            ("INSERT INTO wcff (id,name) VALUES (@id,@name)", con);
            cmd.Parameters.AddWithValue("@id", insert.id);
            cmd.Parameters.AddWithValue("@name", insert.name);
           int a= cmd.ExecuteNonQuery();
            con.Close();
            return a;
        }

        public int opu(usersinfo update)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand
            ("UPDATE wcff SET name = @name  WHERE id = @id", con);
            cmd.Parameters.AddWithValue("@id", update.id);
            cmd.Parameters.AddWithValue("@name", update.name);
            int a =cmd.ExecuteNonQuery();
            con.Close();
            return a;
        }

        public int opd(usersinfo id)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("DELETE FROM wcff WHERE id = @id", con);
            cmd.Parameters.AddWithValue("@id", id.id);

           int a = cmd.ExecuteNonQuery();
            con.Close();
            return a; 
        }
    }
}

The connection strings are available here.

• The list getids will get all the ids and the list getusers will get all the user names.
• The int opi method has the insert SQL command.
• The int opu method has the update SQL command.
• The int opd has the Delete SQL command.

When you are done with the coding part in the WCF project, click on that project then click on build wait for some time, then you will see the Visual Studio saying build succeeded. Then click on view in browser. After that, a page will open in the browser. Click on service1.svc. When you click it, you will be prompted to a page that says you have created a service. Copy the link. Now in your Metro style project, click on the references and then click on add service reference. Paste the link in the Address bar and then click Go. It will look something as shown in the picture below.

MainPage.xaml.cs

Here is how your XAML C# code will perform the operations from the UI.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using Windows.Foundation;
using Windows.Foundation.Collections;
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 wcfexamcrud.ServiceReference1;

namespace wcfexamcrud
{   
    public sealed partial class MainPage : Page
    {
        //object of service ref
        ServiceReference1.ServiceClient objserv = new ServiceReference1.ServiceClient();
        public MainPage()
        {
            this.InitializeComponent();
            this.get();            
        }

        protected  override void OnNavigatedTo(NavigationEventArgs e)
        {            
           //get() <-- Can be here                     
        }
        // To get ids and users
        public async void get()
        {
            gv1.ItemsSource = await objserv.getusersAsync();
            gv2.ItemsSource = await objserv.getidsAsync();                       
        }
        
        // To insert ids and users
        private async void Button_Click_1(object sender, RoutedEventArgs e)
        {
            usersinfo insert = new usersinfo();
            insert.id = int.Parse(uidtb.Text);
            insert.name = nametb.Text;
            await objserv.opiAsync(insert);
            get();                 
        }
        // To update ids and users
        private async void Button_Click_2(object sender, RoutedEventArgs e)
        {
            usersinfo update = new usersinfo();
            update.id = int.Parse(uidtb.Text);
            update.name = nametb.Text;
            await objserv.opuAsync(update);
            get();
        }

        // To del the users
        private async void Button_Click_3(object sender, RoutedEventArgs e)
        {
            usersinfo delete = new usersinfo();
            delete.id = int.Parse(uidtb.Text);
            await objserv.opdAsync(delete);
            get();
        }
    }
}

After coding in the MainPage C# file, click on the metro project and set it as startup project.

Then debug the application and check whether the application is working.

The working as in the below picture: