PostgreSQL的应用
步骤1：安装Npgsql
通过NuGet安装Npgsql包：
Install-Package Npgsql
步骤2：编写连接字符串（三种格式都可以）
private static string str = "Host=localhost; Port=5432; Username=postgres; Password=123456; Database=testdb";
private static string str = "HOST=localhost;PORT=5432;DATABASE=testdb;USER ID=postgres;PASSWORD=123456;";
private static string str = ConfigurationManager.ConnectionStrings["PostgreSQLConStr"].ConnectionString;//需要在App.config里配置
步骤3：使用SQL命令创建数据库
注意：创建数据库需要超级用户权限（如postgres用户）或具有创建数据库权限的用户。
CREATE DATABASE newDbName
步骤4：执行CREATE TABLE命令
使用NpgsqlCommand执行创建表的SQL语句。
CREATE TABLE IF NOT EXISTS tableName (Id SERIAL PRIMARY KEY, Name VARCHAR(50) UNIQUE NOT NULL,Class VARCHAR(50) UNIQUE NOT NULL,Age INTEGER,Gender BOOLEAN DEFAULT true,CreateTime TIMESTAMP)
创建数据库示例代码：
using Npgsql;
class Program
{
static void Main()
{
// 连接到默认数据库（如postgres）
private static string connString= "Host=localhost; Port=5432; Username=postgres; Password=123456; Database=testdb";
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
// 要创建的新数据库名称
string newDbName = "my_new_database";
// 创建数据库的SQL命令
// 注意：使用参数化查询防止SQL注入，但数据库名不能参数化，所以这里直接拼接（确保名称安全）
string sql = $"CREATE DATABASE {EscapeDbName(newDbName)}";
using (var cmd = new NpgsqlCommand(sql, conn))
{
try
{
cmd.ExecuteNonQuery();
Console.WriteLine($"Database {newDbName} created successfully.");
}
catch (PostgresException ex)
{
// 如果数据库已存在，会抛出异常
if (ex.SqlState == "42P04") // 42P04是数据库已存在的错误代码
{
Console.WriteLine($"Database {newDbName} already exists.");
}
else
{
Console.WriteLine($"Error: {ex.Message}");
}
}
}
}
}
// 辅助方法：转义数据库名（防止SQL注入，仅允许安全字符）
private static string EscapeDbName(string name)
{
// 只允许字母、数字和下划线（根据实际需求调整）
if (!System.Text.RegularExpressions.Regex.IsMatch(name, @"^[a-zA-Z0-9_]+$"))
{
throw new ArgumentException("Invalid database name");
}
return name;
}
}
创建数据表示例代码：
using System;
using Npgsql;
class Program
{
static void Main()
{
// 连接字符串，根据实际情况修改
private static string connString= "Host=localhost; Port=5432; Username=postgres; Password=123456; Database=testdb";
// 创建表的SQL语句
string createTableSql = @"
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
";
using (var conn = new NpgsqlConnection(connString))
{
try
{
conn.Open();
Console.WriteLine("Connected to the database!");
// 创建命令对象
using (var cmd = new NpgsqlCommand(createTableSql, conn))
{
cmd.ExecuteNonQuery();
Console.WriteLine("Table 'users' created successfully!");
}
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
}
}
}
}
删除数据表示例代码：
using System;
using Npgsql;
class Program
{
static void Main()
{
// 数据库连接字符串
private static string connString= "Host=localhost; Port=5432; Username=postgres; Password=123456; Database=testdb";
// 要删除的表名
string tableName = "employees";
try
{
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
// 使用参数化查询避免SQL注入（表名不能参数化，所以需要验证）
// 注意：表名不能直接参数化，因此需要验证合法性
string sql = $"DROP TABLE IF EXISTS {SanitizeTableName(tableName)}";
using (var cmd = new NpgsqlCommand(sql, conn))
{
cmd.ExecuteNonQuery();
Console.WriteLine($"表 '{tableName}' 删除成功（如果存在）");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"错误: {ex.Message}");
}
}
// 简单的表名合法性验证（防止SQL注入）
static string SanitizeTableName(string tableName)
{
// 只允许字母、数字和下划线（根据实际需要调整）
if (System.Text.RegularExpressions.Regex.IsMatch(tableName, @"^[a-zA-Z0-9_]+$"))
{
return tableName;
}
else
{
throw new ArgumentException("无效的表名");
}
}
////更安全的做法（使用双引号转义）：
//// 在SanitizeTableName方法中，我们使用双引号将表名括起来，并转义其中的双引号
//static string SanitizeTableName(string tableName)
//{
// // 替换表名中的双引号为两个双引号（PostgreSQL的转义规则）
// string escapedTableName = tableName.Replace("\"", "\"\"");
// return $"\"{escapedTableName}\"";
//}
}
数据表重命名 代码示例：
using System;
using System.Text.RegularExpressions;
using Npgsql;
class TableRenamer
{
static void Main()
{
private static string connString= "Host=localhost; Port=5432; Username=postgres; Password=123456; Database=testdb";
string oldTableName = "employees_old";
string newTableName = "employees_new";
try
{
// 验证表名合法性
if (!IsValidTableName(oldTableName) || !IsValidTableName(newTableName))
{
Console.WriteLine("无效的表名");
return;
}
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
// 安全重命名表
RenameTableSafely(conn, oldTableName, newTableName);
}
}
catch (Exception ex)
{
Console.WriteLine($"错误: {ex.Message}");
}
}
static void RenameTableSafely(NpgsqlConnection conn, string oldName, string newName)
{
// 使用参数化查询防止 SQL 注入
string sql = "ALTER TABLE @oldName RENAME TO @newName";
using (var cmd = new NpgsqlCommand(sql, conn))
{
// 添加参数
cmd.Parameters.AddWithValue("oldName", oldName);
cmd.Parameters.AddWithValue("newName", newName);
cmd.ExecuteNonQuery();
Console.WriteLine($"表 '{oldName}' 已重命名为 '{newName}'");
}
}
// 验证表名是否合法（防止 SQL 注入）
static bool IsValidTableName(string name)
{
// 只允许字母、数字和下划线
return Regex.IsMatch(name, @"^[a-zA-Z_][a-zA-Z0-9_]*$");
}
}
插入数据 代码示例（单条数据插入）：
using System;
using Npgsql;
class Program
{
static void Main()
{
private static string connString= "Host=localhost; Port=5432; Username=postgres; Password=123456; Database=testdb";
try
{
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
string sql = @"
INSERT INTO users (username, email, age, created_at)
VALUES (@username, @email, @age, @createdAt)
";
using (var cmd = new NpgsqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("username", "john_doe");
cmd.Parameters.AddWithValue("email", "john@example.com");
cmd.Parameters.AddWithValue("age", 30);
cmd.Parameters.AddWithValue("createdAt", DateTime.UtcNow);
int rowsAffected = cmd.ExecuteNonQuery();
Console.WriteLine($"成功插入 {rowsAffected} 条记录");
}
}
}
catch (PostgresException ex)
{
Console.WriteLine($"PostgreSQL 错误 ({ex.SqlState}): {ex.Message}");
}
catch (Exception ex)
{
Console.WriteLine($"错误: {ex.Message}");
}
}
}
插入数据 代码示例（使用循环插入多条数据）：
static void InsertMultipleUsers(List
{
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
string sql = @"
INSERT INTO users (username, email, age)
VALUES (@username, @email, @age)
";
foreach (var user in users)
{
using (var cmd = new NpgsqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("username", user.Username);
cmd.Parameters.AddWithValue("email", user.Email);
cmd.Parameters.AddWithValue("age", user.Age);
cmd.ExecuteNonQuery();
}
}
}
}
插入数据 代码示例（使用事务批量插入（推荐））：
static void BulkInsertWithTransaction(List
{
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
using (var transaction = conn.BeginTransaction())
{
try
{
string sql = @"
INSERT INTO users (username, email, age)
VALUES (@username, @email, @age)
";
foreach (var user in users)
{
using (var cmd = new NpgsqlCommand(sql, conn, transaction))
{
cmd.Parameters.AddWithValue("username", user.Username);
cmd.Parameters.AddWithValue("email", user.Email);
cmd.Parameters.AddWithValue("age", user.Age);
cmd.ExecuteNonQuery();
}
}
transaction.Commit();
Console.WriteLine($"成功插入 {users.Count} 条记录");
}
catch
{
transaction.Rollback();
throw;
}
}
}
}
插入数据 代码示例（插入 JSON 数据）：
static void InsertWithJson()
{
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
string sql = @"
INSERT INTO user_profiles (user_id, profile_data)
VALUES (@userId, @profileData::jsonb)
";
using (var cmd = new NpgsqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("userId", 1);
// JSON 数据
string jsonData = @"
{
""preferences"": {
""theme"": ""dark"",
""notifications"": true
},
""social_links"": [
""https://twitter.com/johndoe"",
""https://github.com/johndoe""
]
}
";
cmd.Parameters.AddWithValue("profileData", jsonData);
cmd.ExecuteNonQuery();
}
}
}
插入数据 代码示例（插入数组数据）：
static void InsertWithArray()
{
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
string sql = @"
INSERT INTO products (name, tags, prices)
VALUES (@name, @tags, @prices)
";
using (var cmd = new NpgsqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("name", "Laptop");
// 文本数组
string[] tags = { "electronics", "computers", "gadgets" };
cmd.Parameters.AddWithValue("tags", tags);
// 数值数组
decimal[] prices = { 999.99m, 1099.99m, 1299.99m };
cmd.Parameters.AddWithValue("prices", prices);
cmd.ExecuteNonQuery();
}
}
}
本文来自博客园，作者：lyc6921，转载请注明原文链接：https://www.cnblogs.com/lyc6921/p/19145763
浙公网安备 33010602011771号