C# 訪問PostgreSQL 數據庫
sql
1、第三方類庫數據庫
Npgsql提供 .NET 操做postgreSQL數據庫的相關類。支持postgreSQL7.x 及以上版本。ide
2、自定義接口及操做類post
2.1接口類測試
- public interface IDBHelper
- {
-
-
-
- int ExecuteNonQuery(string connectionString, System.Data.CommandType cmdType, string cmdText, params System.Data.Common.DbParameter[] cmdParms);
-
-
-
-
- int ExecuteNonQuery(System.Data.Common.DbTransaction trans, System.Data.CommandType cmdType, string cmdText, params System.Data.Common.DbParameter[] cmdParms);
-
-
-
-
- DataSet ExecuteQuery(System.Data.Common.DbTransaction trans, System.Data.CommandType cmdType, string cmdText, params System.Data.Common.DbParameter[] cmdParms);
-
-
-
-
- DataSet ExecuteQuery(string connectionString, System.Data.CommandType cmdType, string cmdText, params System.Data.Common.DbParameter[] cmdParms);
-
-
-
-
- DbDataReader ExecuteReader(System.Data.Common.DbTransaction trans, System.Data.CommandType cmdType, string cmdText, params System.Data.Common.DbParameter[] cmdParms);
-
-
-
-
- DbDataReader ExecuteReader(string connectionString, System.Data.CommandType cmdType, string cmdText, params System.Data.Common.DbParameter[] cmdParms);
-
-
-
-
- object ExecuteScalar(System.Data.Common.DbTransaction trans, System.Data.CommandType cmdType, string cmdText, params System.Data.Common.DbParameter[] cmdParms);
-
-
-
-
- object ExecuteScalar(string connectionString, System.Data.CommandType cmdType, string cmdText, params System.Data.Common.DbParameter[] cmdParms);
-
-
-
-
-
-
-
- int GetCount(string connectionString, string tblName, string condition);
- }
2.2 操做類ui
-
-
-
- public class PostgreHelper : IDBHelper
- {
-
-
-
- public int GetCount(string connectionString, string tblName, string condition)
- {
- StringBuilder sql = new StringBuilder("select count(*) from " + tblName);
- if (!string.IsNullOrEmpty(condition))
- sql.Append(" where " + condition);
-
- object count = ExecuteScalar(connectionString, CommandType.Text, sql.ToString(), null);
- return int.Parse(count.ToString());
- }
-
-
-
-
- public DataSet ExecuteQuery(string connectionString, CommandType cmdType, string cmdText,
- params DbParameter[] cmdParms)
- {
- using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
- {
- using (NpgsqlCommand cmd = new NpgsqlCommand())
- {
- PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
- using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))
- {
- DataSet ds = new DataSet();
- da.Fill(ds, "ds");
- cmd.Parameters.Clear();
- return ds;
- }
- }
- }
- }
-
-
-
-
- public DataSet ExecuteQuery(DbTransaction trans, CommandType cmdType, string cmdText,
- params DbParameter[] cmdParms)
- {
- NpgsqlCommand cmd = new NpgsqlCommand();
- PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
- NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
- DataSet ds = new DataSet();
- da.Fill(ds, "ds");
- cmd.Parameters.Clear();
- return ds;
- }
-
-
-
-
- public int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText,
- params DbParameter[] cmdParms)
- {
- NpgsqlCommand cmd = new NpgsqlCommand();
-
- using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
- {
- PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
- int val = cmd.ExecuteNonQuery();
- cmd.Parameters.Clear();
- return val;
- }
- }
-
-
-
-
- public int ExecuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText,
- params DbParameter[] cmdParms)
- {
- NpgsqlCommand cmd = new NpgsqlCommand();
- PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
- int val = cmd.ExecuteNonQuery();
- cmd.Parameters.Clear();
- return val;
- }
-
-
-
-
- public DbDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText,
- params DbParameter[] cmdParms)
- {
- NpgsqlCommand cmd = new NpgsqlCommand();
- NpgsqlConnection conn = new NpgsqlConnection(connectionString);
-
- try
- {
- PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
- NpgsqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
- cmd.Parameters.Clear();
- return rdr;
- }
- catch
- {
- conn.Close();
- throw;
- }
- }
-
-
-
-
- public DbDataReader ExecuteReader(DbTransaction trans, CommandType cmdType, string cmdText,
- params DbParameter[] cmdParms)
- {
- NpgsqlCommand cmd = new NpgsqlCommand();
- PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
- NpgsqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
- cmd.Parameters.Clear();
- return rdr;
- }
-
-
-
-
- public object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText,
- params DbParameter[] cmdParms)
- {
- NpgsqlCommand cmd = new NpgsqlCommand();
-
- using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
- {
- PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms);
- object val = cmd.ExecuteScalar();
- cmd.Parameters.Clear();
- return val;
- }
- }
-
-
-
-
- public object ExecuteScalar(DbTransaction trans, CommandType cmdType, string cmdText,
- params DbParameter[] cmdParms)
- {
- NpgsqlCommand cmd = new NpgsqlCommand();
- PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
- object val = cmd.ExecuteScalar();
- cmd.Parameters.Clear();
- return val;
- }
-
-
-
-
-
- private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType,
- string cmdText, DbParameter[] cmdParms)
- {
- if (conn.State != ConnectionState.Open)
- conn.Open();
-
- cmd.Connection = conn;
- cmd.CommandText = cmdText.Replace("@", ":").Replace("?", ":").Replace("[", "\"").Replace("]", "\"");
-
- if (trans != null)
- cmd.Transaction = trans;
-
- cmd.CommandType = cmdType;
-
- if (cmdParms != null)
- {
- foreach (NpgsqlParameter parm in cmdParms)
- {
- parm.ParameterName = parm.ParameterName.Replace("@", ":").Replace("?",":");
-
- cmd.Parameters.Add(parm);
- }
- }
- }
- }
3、數據庫操做實例
寫入實例:this
- IDBHelper dbHelper = new PostgreHelper();
- string connectionString = "User ID=postgres;Password=admin;Server=192.168.0.226;Port=5432;Database=MonitorDB;";
- string sql = "insert into [RawLog]([ProjectID],[File],[Note]) values('項目編號',@file,'備註')";
-
- string file = this.textBox1.Text;
- if (!File.Exists(file)) return;
-
-
- System.IO.FileStream fs = new System.IO.FileStream(file, System.IO.FileMode.Open);
- BinaryReader br = new BinaryReader(fs);
- byte[] b = br.ReadBytes((int)fs.Length);
- br.Close();fs.Close();
-
- int r = dbHelper.ExecuteNonQuery(connectionString, CommandType.Text,sql,new Npgsql.NpgsqlParameter("@file",raw_log.File));
注意:
1.查詢語句中表名、字段名需用中括號[ ] 封起來,在操做類中會統一替換爲雙引號。spa
2.命令語句中的參數名格式爲:@或?+ 參數名,在操做類中爲統一替換爲冒號。
.net
3.對於二進制流、日期等特殊類型需採用2中的參數形式postgresql
4.經屢次測試postgreSQL數據庫只直接支持(冒號+參數名)的參數方式