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'
// 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());
}
}
}
}
}
沒有留言:
張貼留言