///<summary>/// 实体 company///</summary>
[Description("Primary:id")]
[Serializable]
publicclass Company
{
#region 私有变量private Int32 _id = Int32.MinValue;
privatestring _name = null;
private Int32 _age = Int32.MinValue;
privatestring _address = null;
privatefloat _salary = float.MinValue;
private DateTime _join_date = DateTime.MinValue;
#endregion#region 公共属性///<summary>/// 主键 id(NOT NULL)///</summary>public Int32 id
{
set { _id = value; }
get { return _id; }
}
///<summary>/// name(NOT NULL)///</summary>publicstring name
{
set { _name = value; }
get { return _name; }
}
///<summary>/// age(NOT NULL)///</summary>public Int32 age
{
set { _age = value; }
get { return _age; }
}
///<summary>/// address///</summary>publicstring address
{
set { _address = value; }
get { return _address; }
}
///<summary>/// salary///</summary>publicfloat salary
{
set { _salary = value; }
get { return _salary; }
}
///<summary>/// join_date///</summary>public DateTime join_date
{
set { _join_date = value; }
get { return _join_date; }
}
#endregion
}
Program.cs代码:
publicclass Program
{
staticvoid Main(string[] args)
{
using (var dc = new NpgSqlDataContext("Host=localhost;Username=king;Password=wu12345;Database=dellstore").MapComposite<Company>("company"))
{
var r0 = dc.Query(@"SELECT * FROM company");
PrintTable(r0);
//-------------------------------------------------------------------------------------------------var r1 = dc.Query(@"SELECT * FROM company where id=@id", new NpgsqlParameter("id", 3));
PrintTable(r1);
//-------------------------------------------------------------------------------------------------// 使用表值参数// Postgres 没有tvp - 它们由正则或复合类型的数组var r3 = dc.Query(@" SELECT c.* FROM company c INNER JOIN UNNEST(@ageval_tvp) tvp ON c.age = tvp", new NpgsqlParameter("ageval_tvp", newint[] { 1,3, 4}));
PrintTable(r3);
//-------------------------------------------------------------------------------------------------// 复合型tvp
dc.MapComposite<Company>("company");
var r4 = dc.Query(@" SELECT c.* FROM company c INNER JOIN UNNEST(@x_company) x ON c.age = x.age AND c.name = x.name",
new NpgsqlParameter(
"x_company",
new Company[] {
new Company() { name = "Paul", age = 32 },
new Company() { name = "Allen", age = 25 }
}
)
);
PrintTable(r4);
//-------------------------------------------------------------------------------------------------
dc.MapComposite<order>("orders");
var r6 = dc.Query(@"INSERT INTO orders (order_id,customer_id, item,shipping_address) SELECT order_id,customer_id, item,shipping_address from UNNEST(@x_orders) returning order_id",
new NpgsqlParameter(
"x_orders",
new order[] {
new order() {order_id=1, customer_id = 22, item = "cc",shipping_address="加利福尼亚硅谷1号" },
new order() {order_id=2,customer_id = 23, item = "dd",shipping_address="中国上海外滩18号" }
})
);
PrintTable(r6);
//-------------------------------------------------------------------------------------------------//Console.ReadKey();var r7 = dc.Execute(@"WITH customer as (insert into customers(name, age) values ('Ghan', 55) returning customer_id)INSERT INTO orders(customer_id, item)SELECT c.customer_id, x.item FROM customer c CROSS JOIN UNNEST(@x_orders) x
",
new NpgsqlParameter(
"x_orders",
new order[] {
new order() { item = "gg" },
new order() { item = "hh" }
}
)
);
Console.WriteLine("Inserted {0} rows", r7);
//-------------------------------------------------------------------------------------------------//区分大小写的名称var query = @"
WITH Customer AS (INSERT INTO ""Test"" (""Name"", ""Age"")
SELECT ""Name"", ""Age""
FROM UNNEST(@TestItems) returning ""TestID"")
INSERT INTO ""TestLink"" (""TestID"")
SELECT c.""TestID"" FROM Customer c
";
var query1 = @"
WITH Customer AS (INSERT INTO ""Test"" (""Name"", ""Age"")
SELECT ""Name"", ""Age""
FROM UNNEST(@TestItems) returning ""TestID"")
INSERT INTO ""TestLink"" (""TestID"", ""LinkName"")
SELECT c.""TestID"", x.""LinkName"" FROM Customer c CROSS JOIN UNNEST (@TestLinkItems) x
";
var x = dc.Execute(query,new NpgsqlParameter("TestItems", new Test[] { new Test() { Name = "Sam", Age = 25 } }));
Console.WriteLine("已插入行 {0}", x);
//-------------------------------------------------------------------------------------------------var y = dc.Execute(query1,
new NpgsqlParameter(
"TestItems",
new Test[] { new Test() { Name = "Sam", Age = 25 } }),
new NpgsqlParameter(
"TestLinkItems",
new TestLink[]
{
new TestLink() {LinkName = "xxx"},
new TestLink() {LinkName = "yyy"}
}));
Console.WriteLine("已插入行 {0}", y);
}
Console.ReadKey();
}
staticvoid PrintTable(DataTable t)
{
foreach (var c in t.Columns.Cast<DataColumn>().Select(r => r.ColumnName))
Console.Write("{0,12}", c);
Console.WriteLine();
foreach (var r in t.Rows.Cast<DataRow>())
{
foreach (var c in r.ItemArray)
{
int length=c.ToString().Length;
Console.Write("{0,12}", length > 10 ? c.ToString().Substring(0, 10) : c.ToString());
}
Console.WriteLine();
}
Console.WriteLine("\n");
Console.WriteLine("-----------------------------------------------------------------------------");
}
}
CaseSensitiveTranslator.cs代码:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;
namespace NpgSqlUtils
{
publicclass CaseSensitiveTranslator : INpgsqlNameTranslator
{
publicstringTranslateMemberName(string clrName)
{
return clrName;
}
publicstringTranslateTypeName(string clrName)
{
return clrName;
}
}
}
INpgSqlDataContext.cs代码:
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;
namespace NpgSqlUtils
{
publicinterface INpgSqlDataContext: IDisposable
{
INpgsqlNameTranslator Translator { get; }
DataTable Query(string query, params NpgsqlParameter[] parameters);
int Execute(string query, params NpgsqlParameter[] parameters);
///<summary>//////</summary>
INpgSqlDataContext MapComposite<T>(string name) where T : new();
}
}
NpgSqlDataContext.cs代码:
using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace NpgSqlUtils
{
publicclass NpgSqlDataContext : INpgSqlDataContext
{
private NpgsqlConnection _connection;
public INpgsqlNameTranslator Translator { get; set; }
publicNpgSqlDataContext(string connectionString, INpgsqlNameTranslator translator = null)
{
if (translator == null)
translator = new CaseSensitiveTranslator();
Translator = translator;
_connection = new NpgsqlConnection(connectionString);
_connection.Open();
}
public DataTable Query(string query, params NpgsqlParameter[] parameters)
{
DataTable result;
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = _connection;
cmd.CommandText = query;
cmd.Parameters.AddRange(parameters);
using (var reader = cmd.ExecuteReader())
{
result = new DataTable();
result.Load(reader);
}
}
return result;
}
publicintExecute(string query, params NpgsqlParameter[] parameters)
{
int result = -1;
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = _connection;
cmd.CommandText = query;
cmd.Parameters.AddRange(parameters);
result = cmd.ExecuteNonQuery();
}
return result;
}
publicvoidDispose()
{
_connection.Dispose();
}
public INpgSqlDataContext MapComposite<T>(string name) where T : new()
{
_connection.MapComposite<T>(name, Translator);
returnthis;
}
}
}
SQL脚本:
-- Table: public.company-- DROP TABLE public.company;CREATETABLEpublic.company
(
id integerNOTNULL,
name text COLLATE pg_catalog."default"NOTNULL,
age integerNOTNULL,
address character(50) COLLATE pg_catalog."default",
salary real,
join_date date,
CONSTRAINT company_pkey PRIMARYKEY (id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;ALTERTABLEpublic.company
OWNER to postgres;
-- Table: public.orders-- DROP TABLE public.orders;CREATETABLEpublic.orders
(
order_id integerNOTNULL,
shipping_address text COLLATE pg_catalog."default",
item text COLLATE pg_catalog."default",
customer_id integer,
CONSTRAINT orders_pkey PRIMARYKEY (order_id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;ALTERTABLEpublic.orders
OWNER to postgres;