Friday, August 29, 2014

[SQL] Schema

利用下述的範例來說明使用者預設 Schema 和撰寫 T-SQL 時,明確指定 Schema 的重要性

基礎建設
-- 在 Person 和 dbo 兩個 Schema 內,建立 SchemaDemo Table
-- HumanResources Schema 內,則是故意不建立 SchemaDemo Table
USE [AdventureWorks2012]
GO
 
IF OBJECT_ID('Person.SchemaDemo') IS NOT NULL
    DROP TABLE [Person].[SchemaDemo]
 
CREATE TABLE [Person].[SchemaDemo] (descript nchar(100))
INSERT INTO [Person].[SchemaDemo] (descript)
    VALUES(N'Schema 為 Person 的 SchemaDemo Table')
GO
 
IF OBJECT_ID('dbo.SchemaDemo') IS NOT NULL
    DROP TABLE [dbo].[SchemaDemo]
 
CREATE TABLE [dbo].[SchemaDemo] (descript nchar(100))
INSERT INTO [dbo].[SchemaDemo] (descript)
    VALUES(N'Schema 為 dbo 的 SchemaDemo Table')
GO
 
-- 建立測試 UserPerson 和 UserHumanResources,故意建立跟預設 Schema 同名的 User,方便辨識
USE [master]
GO
 
IF EXISTS (SELECT 1 FROM sys.sql_logins WHERE name = 'UserPerson')
    DROP LOGIN UserPerson
IF EXISTS (SELECT 1 FROM sys.sql_logins WHERE name = 'UserHumanResources')
    DROP LOGIN UserHumanResources
 
CREATE LOGIN [UserPerson]
    WITH PASSWORD = N'P@ssw0rd' , DEFAULT_DATABASE = [AdventureWorks2012]
 
CREATE LOGIN [UserHumanResources]
    WITH PASSWORD = N'P@ssw0rd' , DEFAULT_DATABASE = [AdventureWorks2012]
 
USE [AdventureWorks2012]
GO
 
IF EXISTS(SELECT 1 FROM sys.database_principals WHERE name = 'UserPerson')
    DROP USER UserPerson
 
CREATE USER [UserPerson] FOR LOGIN [UserPerson] WITH DEFAULT_SCHEMA = Person;
GO
 
IF EXISTS(SELECT 1 FROM sys.database_principals WHERE name = 'UserHumanResources')
    DROP USER UserHumanResources
 
CREATE USER [UserHumanResources] FOR LOGIN [UserHumanResources] WITH DEFAULT_SCHEMA = HumanResources;
GO
 
-- 授予存取 Table 權限
GRANT SELECT ON [Person].[SchemaDemo] TO [UserPerson]
GRANT SELECT ON [dbo].[SchemaDemo] TO [UserPerson]
GRANT SELECT ON [dbo].[SchemaDemo] TO [UserHumanResources]
GO
 
-- 確認使用者
SELECT
    Name,
    type_desc,
    default_database_name
FROM sys.sql_logins
WHERE name IN ('UserPerson','UserHumanResources')

-- 確認 User 的 預設 Schema
SELECT
    name,
    type_desc,
    default_schema_name
FROM sys.database_principals
WHERE default_schema_name IS NOT NULL
[SQL] Schema-1

Thursday, August 28, 2014

[C#] 根據條件變化顏色

論壇問題
根據資料指定其背景顏色
  • 1 => 藍色
  • 2 => 紅色
  • 3 => 黃色
  • 4 => 綠色
using System.Data;
using System.Drawing;

namespace ConditionColor
{
    public partial class ConditionColor : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                DataTable dt = new DataTable("Demo");
                dt.Columns.Add("DataSeq", typeof(int));
                dt.Columns.Add("Kind", typeof(int));
                dt.Rows.Add(1, 4);
                dt.Rows.Add(2, 3);
                dt.Rows.Add(3, 1);
                dt.Rows.Add(4, 2);
                dt.Rows.Add(5, 3);
                dt.Rows.Add(6, 2);
                dt.Rows.Add(7, 1);
                dt.Rows.Add(8, 4);

                gvData.DataSource = dt;
                gvData.DataBind();
            }
        }
        // 在 RowDataBound 事件內設定顏色變化
        protected void gvData_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            // 判斷 RowType 是不是 DataRow
            if (e.Row.RowType != DataControlRowType.DataRow) return;
   
            // 抓出該資料並設定其背景顏色
            string kind = e.Row.Cells[1].Text;
            switch (kind)
            {
                case "1":
                     e.Row.Cells[1].BackColor = Color.Blue;
                     break;
                case "2":
                     e.Row.Cells[1].BackColor = Color.Red;
                     break;
                case "3":
                     e.Row.Cells[1].BackColor = Color.Yellow;
                     break;
                case "4":
                     e.Row.Cells[1].BackColor = Color.Green;
                     break;
                default:
                     break;
            }
        }
    }
}
[ASP.NET] 根據條件變化顏色

Wednesday, August 27, 2014

[C#] foreach 中的 index

MVA Twenty C# Questions Explained - [12 ​How do I get the index of the current iteration of a foreach loop?]

簡易範例:利用 for 迴圈取代 foreach
namespace MVATwentyQuestions
{
    class Program
    {
        static void Main(string[] args)
        {
            int curIndex = 0;
            // List
            List<string> stringList = new List<string>();
            stringList.Add("One");
            stringList.Add("Two");
            stringList.Add("Three");
            stringList.Add("Four");
            stringList.Add("Five");
            stringList.Add("Six");
            stringList.Add("Seven");

            for (int i = 0; i < stringList.Count; i++)
            {
                Console.WriteLine(i);
            }
        }
    }
}

Tuesday, August 26, 2014

[C#] 奇數列、偶數列背景顏色

不論任何語言,Grid 奇偶數列變色,好像是 Grid 變色的入門考題了,來練習一下
namespace OddEvenRowBG
{
    public partial class OddEvenRowBG : Form
    {
        public OddEvenRowBG()
        {
            InitializeComponent();
        }

        private void OddEvenRowBG_Load(object sender, EventArgs e)
        {
            // 建立 DataGridView 資料來源
            DataTable dt = new DataTable();

            dt.Columns.Add("ColEmpNO", typeof(int));
            dt.Columns.Add("ColEmpName", typeof(string));
            dt.Columns.Add("ColHireDate", typeof(DateTime));

            dt.Rows.Add(1, "趙一", new DateTime(1963, 7, 7));
            dt.Rows.Add(2, "洪二", new DateTime(1965, 5, 10));
            dt.Rows.Add(3, "張三", new DateTime(1981, 12, 11));
            dt.Rows.Add(4, "李四", new DateTime(1980, 1, 9));
            dt.Rows.Add(5, "王五", new DateTime(1973, 9, 9));

            if (dt.PrimaryKey.Length == 0) dt.Constraints.Add("PK", dt.Columns["colEmpNO"], true);
            dgvData.DataSource = dt;

            dgvData.AllowUserToAddRows = false;

            // 下列方法二擇一
            // 方法一:利用 DataGridView 本身屬性來完成
            dgvData.DefaultCellStyle.BackColor = Color.DarkGray;
            dgvData.AlternatingRowsDefaultCellStyle.BackColor = Color.DarkOliveGreen;

            // 方法二:自訂函數來完成
            OddEvenColor(dgvData);
        }

        public void OddEvenColor(DataGridView dgv)
        {
            if (dgv.Rows.Count == 0) return;
   
            // 下列方法二擇一
            // foreach 作法
            foreach (DataGridViewRow row in dgv.Rows)
            { 
                int index = dgv.Rows.IndexOf(row);
                if (index % 2 == 0)
                {
                    row.DefaultCellStyle.BackColor = Color.DarkGray;
                }
                else
                {
                    row.DefaultCellStyle.BackColor =  Color.DarkOliveGreen;
                }
            }

            // for 作法
            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                if (i % 2 == 0)
                {
                    dgv.Rows[i].DefaultCellStyle.BackColor = Color.DarkGray;
                }
                else
                {
                    dgv.Rows[i].DefaultCellStyle.BackColor = Color.DarkOliveGreen;
                }
            }
        }
    }
}
[C#] 奇數列、偶數列背景顏色

Monday, August 25, 2014

[LINQ] SUM

網路問題
要利用 LINQ 找出整個 Table 的總合,延伸練習計算每個群組總合
using System.Data;

namespace LINQSum
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable("demo");
            dt.Columns.Add("Pro", typeof(string));
            dt.Columns.Add("cha", typeof(int));
            dt.Rows.Add("A", 12);
            dt.Rows.Add("B", 6);
            dt.Rows.Add("A", 14);
            dt.Rows.Add("C", 4);
            dt.Rows.Add("B", 8);

            Console.WriteLine("論壇問題:把 Table 內資料加總");

            var TotalSum = dt.AsEnumerable().Sum(datarow => datarow.Field<int>("cha"));

            Console.WriteLine("整個 Table 總合:{0}",TotalSum);

            Console.WriteLine("-------------------------------");

            Console.WriteLine("問題延伸:計算每個 Pro 群組總合");

            // Linq 寫法
            var query = from datarow in dt.AsEnumerable()
                        group datarow by datarow.Field<string>("Pro") into g
                        select new
                        {
                            Pro = g.Key,
                            TotalSum = g.Sum(r => r.Field<int>("cha"))
                        };

            // Lambda 寫法
            var query = dt.AsEnumerable().GroupBy(datarow => datarow.Field<string>("Pro")).Select(g => new { Pro = g.Key, TotalSum = g.Sum(r => r.Field<int>("cha")) });

            foreach (var item in query)
            {
                Console.WriteLine("{0}-{1}", item.Pro, item.TotalSum);
            }
        }
    }
}
[LINQ] SUM