星期五, 3月 24, 2017

[SQL] MERGE 搭配 Linked Server

有個需求想要透過 Linked Server 搭配 MERGE 語法來同步資料，在 try 的時候才發現到，原來 MERGE 語法的 Target_Table 不能是遠端 Table，會出現下面這段錯誤訊息
MERGE 陳述式的目標不能是遠端資料表、遠端檢視或針對遠端資料表的檢視
在 MSDN 上也有找到相關說明，指出 Target_Table 不能是遠端資料表，但 Table_Source 可以喔
沒有留言:

星期四, 3月 16, 2017

[X.Form] 設定 Label.Text

[Xamarin.Forms] 從XAML開始 教學影片中在 xaml 內指定 Label View 的 x:Name，就可以在 cs 檔案 (Code Behind) 內，直接指定 Label

該筆記內容是在 cs 內把 Label.Text 從預設的 Welcome to Xamarin Forms! 改為 Hello World to Xamarin Forms!

xaml
<?xml version="1.0" encoding="utf-8" ?>
<ContentPage 
    xmlns="http://xamarin.com/schemas/2014/forms"
    xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
    xmlns:local="clr-namespace:App1"
    x:Class="App1.MainPage">

    <Label 
        x:Name="lbl"
        Text="Welcome to Xamarin Forms!" 
        VerticalOptions="Center" 
        HorizontalOptions="Center" />

</ContentPage>
cs 檔案
using Xamarin.Forms.Xaml;

namespace App1
{
    [XamlCompilation(XamlCompilationOptions.Compile)]
    public partial class MainPage : ContentPage
    {
        public MainPage()
        {
            InitializeComponent();

            lbl.Text = "Hello World to Xamarin Forms!";
        }
    }
}
[Xamarin] 設定 Label.Text
沒有留言:

星期三, 3月 15, 2017

[X.Form] DataBinding - View to View

參考這兩篇文章的筆記
利用 Slider 控制兩個 Label 的 Rotate 和 FontSize 屬性，來了解 DataBinding

Xaml 內容
<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             xmlns:local="clr-namespace:DataBindingSample"
             x:Class="DataBindingSample.MainPage">

    <StackLayout
        BindingContext="{x:Reference Name = slider}">

        <Label 
            Text="ROTATION"
            FontAttributes="Bold"
            FontSize="Large"
            Rotation="{Binding Path=Value}"
            HorizontalOptions="Center"
            VerticalOptions="CenterAndExpand" />

        <Label 
            Text="FontSize"
            FontAttributes="Bold"
            FontSize="{Binding Path=Value}"
            HorizontalOptions="Center"
            VerticalOptions="CenterAndExpand" />

        <Slider 
            x:Name="slider"
            Maximum="360"
            VerticalOptions="CenterAndExpand" />

        <Label 
          Text="{
            Binding Value,
            StringFormat='Slider:{0:F0}'}"
          FontAttributes="Bold"
          FontSize="Large"
          HorizontalOptions="Center"
          VerticalOptions="CenterAndExpand" />

    </StackLayout>

</ContentPage>
  • 重點1
BindingContext="{x:Reference Name = slider}" 
// 可以寫成
BindingContext="{x:Reference slider}" 
// 省略 Name
  • 重點2
可以在各別 Label 內設定 BindingContext
<Label Text="ROTATION" BindingContext="{x:Reference Name = slider}">
<Label Text="FontSize" BindingContext="{x:Reference Name = slider}">
或是直接在 StackLayout 上設定 BindingContext
<StackLayout BindingContext="{x:Reference Name = slider}">
  • 重點3
Rotation="{Binding Path=Value}"
// 可以寫成
Rotation="{Binding Value}"
// 省略 Path
  • 重點4
Text="{Binding Value,StringFormat='Slider:{0:F0}'}"
StringFormat 用單引號包起來

顯示結果
[Xamarin] DataBindng 簡易範例

沒有留言:

星期一, 3月 13, 2017

[X.Form] XamlCompilation

閱讀 [Xamarin.Forms] 從XAML開始 的筆記

重點：
  • 設定 XamlCompilation 可以讓 xaml 在編譯時期進行偵錯，而不是執行時才發現錯誤
  • XamlCompilation 是 attribute，可以放在 class、namespace 或 assembly 上
  • 要引用 using Xamarin.Forms.Xaml　才能使用
把 XamlCompilation 設定在 class、namespcae 或 assembly 上

[Xamarin] XamlCompilation-1

[Xamarin] XamlCompilation-2

在 xaml 內把 Text Property 故意改錯，執行時期才在模擬器上顯示錯誤

[Xamarin] XamlCompilation-3

在 class 上指定 XamlCompilation，編譯時期在錯誤清單內就會顯示異常

[Xamarin] XamlCompilation-4

沒有留言:

星期五, 3月 03, 2017

Dapper

該篇單純紀錄 Dapper 使用範例，下面這三篇大神文章，有較多理論介紹
MS SQL 環境建置
USE [Demo]
GO

DROP TABLE IF EXISTS [dbo].[Employee]

CREATE TABLE [dbo].[Employee](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [EmpName] [nchar](10) NOT NULL,
    [HireDate] [date] NOT NULL,
    [Salary] [money] NOT NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([ID] ASC))
GO

INSERT INTO Employee (EmpName , HireDate , Salary)
SELECT '張三' , '2010-02-01' , 50000
UNION ALL
SELECT '李四' , '2013-07-15' , 30000
UNION ALL
SELECT '王五' , '2012-12-03' , 45000

DROP PROCEDURE IF EXISTS uspEmmployeeSalarySearch
GO

CREATE PROCEDURE uspEmmployeeSalarySearch (@Salary money)
AS
BEGIN

    SET NOCOUNT ON;

    SELECT 
        EmpName ,
        HireDate ,
        Salary
    FROM [dbo].[Employee]
    WHERE Salary > @Salary
END
GO

DROP PROCEDURE IF EXISTS uspDapperSP
GO

CREATE PROCEDURE uspDapperSP (@HireDate date , @count nchar(10) output)
AS
BEGIN

    SET NOCOUNT ON;

    SELECT
        ID ,
        EmpName , 
        HireDate ,
        Salary
    FROM Employee
    WHERE HireDate >= @HireDate

    SELECT 
        @count = COUNT(*) 
    FROM Employee
    WHERE HireDate >= @HireDate

    return 0;
END
GO

----------------------

DROP TABLE IF EXISTS [dbo].[Product]
DROP TABLE IF EXISTS [dbo].[ProductDetail]

CREATE TABLE [dbo].[Product](
    [ProdID] [int] NULL,
    [ProdName] [nchar](50) NULL,
    [ProdURL] [char](100) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ProductDetail](
     [ID] [int] IDENTITY(1,1) NOT NULL,
     [ProdID] [int] NULL,
     [DetailName] [nchar](50) NULL
) ON [PRIMARY]
GO

INSERT INTO Product (ProdID , ProdName , ProdURL)
SELECT 1 , 'SQL Server   ' , 'https://www.microsoft.com/en-us/cloud-platform/sql-server'   UNION ALL                                        
SELECT 2 , 'Wndows Server' , 'https://www.microsoft.com/en-us/cloud-platform/windows-server-2016'UNION ALL    
SELECT 3 , 'Visual Studio' , 'https://www.visualstudio.com/'

INSERT INTO ProductDetail (ProdID , DetailName)
SELECT 1 ,'SQL Server 2016' UNION ALL                                
SELECT 1 ,'SQL Server 2014' UNION ALL                                  
SELECT 1 ,'SQL Server 2012' UNION ALL                                  
SELECT 1 ,'SQL Server 2008' UNION ALL                                  
SELECT 1 ,'SQL Server 2005' UNION ALL                                  
SELECT 2 ,'Windows Server 2016' UNION ALL                               
SELECT 2 ,'Windows Server 2012' UNION ALL                               
SELECT 2 ,'Windows Server 2008' UNION ALL                               
SELECT 2 ,'Windows Server 2003' UNION ALL                               
SELECT 2 ,'Windows Server 2000' UNION ALL    
SELECT 3 ,'Visual Studio 2017' UNION ALL                              
SELECT 3 ,'Visual Studio 2015' UNION ALL                                
SELECT 3 ,'Visual Studio 2013' UNION ALL                                
SELECT 3 ,'Visual Studio 2012' UNION ALL                                
SELECT 3 ,'Visual Studio 2010' UNION ALL                                
SELECT 3 ,'Visual Studio 2008'
C# 環境建置
// App.Config 連線字串
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
    </startup>
 <connectionStrings>
  <add name="default" connectionString="Data Source=.\SQL2016;Initial Catalog=Demo;Integrated Security=True;" providerName="System.Data.SqlClient"/>
 </connectionStrings>
</configuration>

// Entity
public class Employee
{
    public int ID { get; set; }
    public string EmpName { get; set; }
    public DateTime HireDate { get; set; }
    public decimal Salary { get; set; }
}

public class Product
{
    public string ProdID { get; set; }
    public string ProdName { get; set; }
    public string ProdURL { get; set; }
    public ProductDetail PD { get; set; }
}

public class ProductDetail
{
    public int ID { get; set; }
    public string ProdID { get; set; }
    public string DetailName { get; set; }
}

// 顯示 Employee 資料
private static void ShowData(IEnumerable<Employee> data)
{
    foreach (Employee e in data)
    {
        Console.WriteLine(e.ID + " - " + e.EmpName.Trim() + " - " + e.HireDate.ToString("yyyy/MM/dd") + " - " + e.Salary);
    }
}

範例 1：簡單查詢
namespace DapperSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                string TSQL = "SELECT ID , EmpName , HireDate , Salary FROM Employee";
                IEnumerable<Employee> data = conn.Query<Employee>(TSQL);
                ShowData(data);
            }
        }
    }
}

範例 2：參數化 - 以 BETWEEN 為例
namespace DapperSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                string TSQL = "SELECT ID , EmpName , HireDate , Salary FROM Employee WHERE HireDate BETWEEN @StartDate AND @EndDate";
                // 匿名型別
                object para = new { StartDate = "2010-1-1", EndDate = "2011-12-31" };
                IEnumerable<Employee> data = conn.Query<Employee>(TSQL, para);
                ShowData(data);
            }
        }
    }
}

範例 3：參數化 - 以 LIKE 為例
namespace DapperSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                string TSQL = "SELECT ID , EmpName , HireDate , Salary FROM Employee WHERE EmpName LIKE @EmpName";
                // 匿名型別
                object para = new { EmpName = "王%" };
                IEnumerable<Employee> data = conn.Query<Employee>(TSQL, para);
                ShowData(data);
            }
        }
    }
}

範例 4：參數化 - 以 WHERE IN 為例
namespace DapperSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                string TSQL = "SELECT ID , EmpName , HireDate , Salary FROM Employee WHERE ID IN @ID";
                object paras = new { ID = new int[] { 2, 3 } };
                IEnumerable<Employee> data = conn.Query<Employee>(TSQL, paras);
                ShowData(data);
            }
        }
    }
}

範例 5：Store Procedure 基礎使用
namespace DapperSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                DynamicParameters spPara = new DynamicParameters();
                spPara.Add("Salary", "30000", DbType.Decimal, ParameterDirection.Input);

                // 沒有這一個關鍵字的話 commandType: 會被認為是第三個參數，也就是 Transaction 截圖來表示一下
                IEnumerable<Employee> data = conn.Query<Employee>("uspEmmployeeSalarySearch", spPara, commandType: CommandType.StoredProcedure);
                ShowData(data);
            }
        }
    }
}

範例 6：Stroe Produre - input、output 和 return 
namespace DapperSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                DynamicParameters spParams = new DynamicParameters();
                spParams.Add("HireDate", "2013-01-01", DbType.Date, ParameterDirection.Input);
                spParams.Add("count", dbType: DbType.Int32, direction: ParameterDirection.Output);
                spParams.Add("RetValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

                IEnumerable<Employee> data = conn.Query<Employee>("uspDapperSP", spParams, commandType: CommandType.StoredProcedure);
                ShowData(data);
                Console.WriteLine($"@count：{spParams.Get<int>("@count")} 位");
                Console.WriteLine($"@RetValue：{spParams.Get<int>("@RetValue")}");
            }
        }
    }
}

範例 7：INSERT 基礎
namespace DapperSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                string TSQL = "INSERT INTO Employee (EmpName , HireDate , Salary) ";
                TSQL += "VALUES(@EmpName , @HireDate , @Salary)";
                Employee e = new Employee()
                {
                    EmpName = "蔡六",
                    HireDate = new DateTime(2015, 11, 11),
                    Salary = 28000
                };
                int RowCount = conn.Execute(TSQL, e);
                Console.WriteLine(RowCount);
            }
        }
    }
}

範例 8：INSERT - 以陣列為參數
namespace DapperSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                string TSQL = "INSERT INTO Employee (EmpName , HireDate , Salary) ";
                TSQL += "VALUES(@EmpName , @HireDate , @Salary)";

                object[] paras = new[]
                {
                    new { EmpName = "蔡六" , HireDate = "2015-11-11" , Salary = 35000} ,
                    new { EmpName = "鄭七", HireDate = "2013-10-10", Salary = 97870 }
                };
            }
        }
    }
}

範例 9：INSERT - 以 List<T> 為參數
namespace DapperSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                string TSQL = "INSERT INTO Employee (EmpName , HireDate , Salary) ";
                TSQL += "VALUES(@EmpName , @HireDate , @Salary)";

                List<Employee> datalist = new List<Employee>();
                datalist.Add(new Employee() { EmpName = "鍾九", HireDate = new DateTime(2007, 3, 3), Salary = 45000 });
                datalist.Add(new Employee() { EmpName = "韋十", HireDate = new DateTime(2011, 11, 11), Salary = 22000 });

                int RowCount = conn.Execute(TSQL, datalist);
                Console.WriteLine(RowCount);
            }
        }
    }
}

範例 10：UPDATE
namespace DapperSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                string TSQL = "UPDATE Employee ";
                TSQL += "SET Salary = @Salary ";
                TSQL += "WHERE ID = @ID";
                object para = new { Salary = 28999, ID = 4 };
                int RowCount = conn.Execute(TSQL, para);
                Console.WriteLine(RowCount);
            }
        }
    }
}

範例 11：DELETE
namespace DapperSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                string TSQL = "DELETE FROM Employee WHERE ID = @ID   ";
                object para = new { ID = 5 };
                int RowCount = conn.Execute(TSQL, para);
                Console.WriteLine(RowCount);
            }
        }
    }
}

範例 12：Dynamic
namespace DapperSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                string TSQL = "SELECT ID , EmpName , HireDate , Salary FROM Employee";
                var data = conn.Query(TSQL);
                foreach (var e in data)
                {
                    Console.WriteLine(e.ID + " - " + e.EmpName.Trim() + " - " + e.HireDate.ToString("yyyy/MM/dd") + " - " + e.Salary);
                }
            }
        }
    }
}

範例 13：Multi Read
namespace DapperSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();


                string TSQL = "SELECT * FROM Product WHERE ProdID = @ProdID ; SELECT * FROM ProductDetail WHERE ProdID = @ProdID";
                var MultiResult = conn.QueryMultiple(TSQL, new { ProdID = 1 });
                Product master = MultiResult.Read<Product>().Single();
                IEnumerable<ProductDetail> detail = MultiResult.Read<ProductDetail>();

                Console.WriteLine($"產品：{master.ProdName}");
                foreach (ProductDetail pd in detail)
                {
                    Console.WriteLine("     " + pd.DetailName);
                }
            }
        }
    }
}

範例 14：Multi Mapping
namespace DapperSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                string TSQL = "SELECT* FROM Product AS P JOIN ProductDetail AS PD ON P.ProdID = PD.ProdID ORDER BY P.ProdID";
                var data = conn.Query<Product, ProductDetail, Product>(TSQL, (Product, ProductDetail) => { Product.PD = ProductDetail; return Product; });

                foreach (Product p in data)
                {
                    Console.WriteLine(p.ProdName.Trim() + " - " + p.PD.DetailName.Trim());
                }
            }
        }
    }
}

沒有留言: