[TOC]

T-SQL : API in UWP

1. Background Library

At least we need two namespace in UWP if a Sql API is used:

using System.Data.SqlClient;
using System.Diagnostics;

Besides, to archive data binding, sometimes System.Collections.ObjectModel; is needed, too.

SqlClient provides almost every API we will need then , and Diagnostics provides a debug class we will use to show error info. It will transport errors in Sql Server back to us.

At the last but not the least , we need the user id and password and the name of sql server we are going to access:

 private string connectionString =
            @"Data Source=MYCOMPUTER;Initial Catalog=Note; User Id=note; Password=note";
           //@"SERVER = ;DATABASE = Note; USER ID = note; PASSWORD = note";
        public string ConnectionString { get => connectionString; set => connectionString = value; }

Here we use note ,note,as our user name and password to access table Note in MYCOMPUTER .

2. Run a Sql Query

To run any query commend , just follow this schema:

                try
                {
                    using (SqlConnection conn = new SqlConnection(ConnectionString))
                    {
                        conn.Open();
                        if (conn.State == System.Data.ConnectionState.Open)
                        {
                            using (SqlCommand cmd = conn.CreateCommand())
                            {
                                cmd.CommandText = Query;
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
                catch (Exception eSql)
                {
                    Debug.WriteLine("Exception:" + eSql.Message);
                }

Two class are used : SqlConnection andSqlCommand, the former create a connection , and the latter set a buff ready to run its query. (Saved in SqlCommand.CommandText)

As for string Query, just assembly it as the query in T-SQL, for example:

 string Query = "insert into users values(" + "'"+RegNameText.Text+"'" + "," + "'"+ RegPassword.Password+"'" + ")";

3. Run a Sql Query and get its returned SELECT

Assuming we have a custom class User,which has 2 member: password and username ,and we want our query fetch every password and username :

class User
    {
        public string UserID { get; set; }
        public string Password { get; set; }

    }

This schema is provided from microsoft :

this.InitializeComponent();
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    conn.Open();
                    if(conn.State==System.Data.ConnectionState.Open)
                    {
                        using (SqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = GetUsersQuery;
                            using (SqlDataReader reader = cmd.ExecuteReader())
                            {
                                while(reader.Read())
                                {
                                    var user = new User();
                                    user.UserID = reader.GetString(0).TrimEnd();
                                    user.Password = reader.GetString(1).TrimEnd();
                                    Users.Add(user);
                                    //Users can be a List (List<User>) ,or an observeablecollection typ.
                                }
                            }
                        }
                    }
                }
            }
            catch(Exception eSql)
            {
                Debug.WriteLine("Exception:" + eSql.Message);
            }

UWP: Data Binding

Now we have a list of custom class filled with our query result , how do we make our Component to show each member’s attributes or values?

We need to use an observeablecollection and a ListView component or any other components that support binding. Assuming the list’s name is TestCollection

A ListView should have such structure:

 <ListViewItemsSource="{x:Bind TestCollection}" Name="MyListView" SelectionChanged="MyListView_SelectionChanged">               <!--Put list name here-->
            <ListView.ItemTemplate>
                <DataTemplate x:DataType="assets:Note" ><!--Put your class here-->
                    <StackPanel><!--Put every attributes of class in sub-components-->
                        <TextBlock xml:space="preserve" Height="auto"
                                   Text="{x:Bind Content}"
                                   ScrollViewer.VerticalScrollBarVisibility="Auto"/>
                        <TextBlock Text="{x:Bind Date}"/>
                        <TextBlock Text="{x:Bind Writter}"/>
                    </StackPanel>
                </DataTemplate>
            </ListView.ItemTemplate>
        </ListView>

Then, back to .xmal.cs file, we will create the list we mentioned at first:

private ObservableCollection<Note> TestCollection;//this should be in the Page class

And fill it’s value in current page’s init function, and everything is done.