C # operations on PostgreSQL databases

1. Third-party class library Npgsql provides. NET-related classes for operating postgreSQL databases. PostgreSQL7.x and later versions are supported. Ii. User-Defined interface and operation Class 2.1: publicinterfaceIDBHelper {summary: Execute the Transact-SQL statement and return the affected number of rows. SummaryintExecuteNo

1. Third-party class library Npgsql provides. NET-related classes for operating postgreSQL databases. PostgreSQL7.x and later versions are supported. Ii. Custom interface and operation Class 2.1 public interface IDBHelper {// summary // execute the Transact-SQL statement and return the affected number of rows. /// Summary int ExecuteNo

I. Third-party class libraries

Npgsql provides related classes for. NET operations on postgreSQL databases. PostgreSQL7.x and later versions are supported.

Ii. custom interfaces and operations

2.1 Interface Class

Public interface IDBHelper

{

///

/// Execute the Transact-SQL statement and return the affected number of rows.

///

Int ExecuteNonQuery (string connectionString, System. Data. CommandType cmdType, string cmdText, params System. Data. Common. DbParameter [] cmdParms );

///

/// Execute the Transact-SQL statement in the transaction and return the affected number of rows.

///

Int ExecuteNonQuery (System. Data. Common. DbTransaction trans, System. Data. CommandType primitive type, string plain text, params System. Data. Common. DbParameter [] partial parms );

///

/// Execute the query in the transaction and return the DataSet

///

DataSet ExecuteQuery (System. Data. Common. DbTransaction trans, System. Data. CommandType primitive type, string plain text, params System. Data. Common. DbParameter [] parallel parms );

///

/// Execute the query and return DataSet

///

DataSet ExecuteQuery (string connectionString, System. Data. CommandType cmdType, string cmdText, params System. Data. Common. DbParameter [] cmdParms );

///

/// Execute the query in the transaction and return the DataReader

///

DbDataReader ExecuteReader (System. Data. Common. DbTransaction trans, System. Data. CommandType primitive type, string plain text, params System. Data. Common. DbParameter [] partition parms );

///

/// Execute the query and return DataReader

///

DbDataReader ExecuteReader (string connectionString, System. Data. CommandType cmdType, string text, params System. Data. Common. DbParameter [] cmdParms );

///

/// Execute the query in the transaction and return the first column in the first row of the returned result set. Ignore other columns or rows.

///

Object ExecuteScalar (System. Data. Common. DbTransaction trans, System. Data. CommandType primitive type, string plain text, params System. Data. Common. DbParameter [] parallel parms );

///

/// Execute the query and return the first column in the first row of the returned result set. Ignore other columns or rows.

///

Object ExecuteScalar (string connectionString, System. Data. CommandType parameter type, string cmdText, params System. Data. Common. DbParameter [] callback parms );

///

/// Obtain the number of data entries

///

///Table Name

///Condition (where is not required)

/// Number of data entries

Int GetCount (string connectionString, string tblName, string condition );

}

2.2 operation

///

/// Database operation base class (for PostgreSQL)

///

Public class extends helper: PageHelper, IDBHelper

{

///

/// Retrieve data by PAGE

///

///Connection string

///Table Name

///Field name

///Page size

///Page

///Sorting Field

///Ascending {False}/descending (True)

///Condition (where is not required)

Public DbDataReader GetPageList (string connectionString, string tblName, int pageSize,

Int pageIndex, string fldSort, bool sort, string condition)

{

String SQL = GetPagerSQL (condition, pageSize, pageIndex, fldSort, tblName, sort );

Return ExecuteReader (connectionString, CommandType. Text, SQL, null );

}

///

/// Obtain the number of data entries

///

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 ());

}

///

/// Execute the query and return DataSet

///

Public DataSet ExecuteQuery (string connectionString, CommandType cmdType, string cmdText,

Params DbParameter [] partition parms)

{

Using (NpgsqlConnection conn = new NpgsqlConnection (connectionString ))

{

Using (NpgsqlCommand cmd = new NpgsqlCommand ())

{

PrepareCommand (cmd, conn, null, partition type, plain text, plain parms );

Using (NpgsqlDataAdapter da = new NpgsqlDataAdapter (cmd ))

{

DataSet ds = new DataSet ();

Da. Fill (ds, "ds ");

Cmd. Parameters. Clear ();

Return ds;

}

}

}

}

///

/// Execute the query in the transaction and return the DataSet

///

Public DataSet ExecuteQuery (DbTransaction trans, CommandType primitive type, string plain text,

Params DbParameter [] partition parms)

{

NpgsqlCommand cmd = new NpgsqlCommand ();

PrepareCommand (cmd, trans. Connection, trans, argument type, plain text, plain parms );

NpgsqlDataAdapter da = new NpgsqlDataAdapter (cmd );

DataSet ds = new DataSet ();

Da. Fill (ds, "ds ");

Cmd. Parameters. Clear ();

Return ds;

}

///

/// Execute the Transact-SQL statement and return the affected number of rows.

///

Public int ExecuteNonQuery (string connectionString, CommandType text type, string cmdText,

Params DbParameter [] partition parms)

{

NpgsqlCommand cmd = new NpgsqlCommand ();

Using (NpgsqlConnection conn = new NpgsqlConnection (connectionString ))

{

PrepareCommand (cmd, conn, null, partition type, plain text, plain parms );

Int val = cmd. ExecuteNonQuery ();

Cmd. Parameters. Clear ();

Return val;

}

}

///

/// Execute the Transact-SQL statement in the transaction and return the affected number of rows.

///

Public int ExecuteNonQuery (DbTransaction trans, CommandType primitive type, string plain text,

Params DbParameter [] partition parms)

{

NpgsqlCommand cmd = new NpgsqlCommand ();

PrepareCommand (cmd, trans. Connection, trans, argument type, plain text, plain parms );

Int val = cmd. ExecuteNonQuery ();

Cmd. Parameters. Clear ();

Return val;

}

///

/// Execute the query and return DataReader

///

Public DbDataReader ExecuteReader (string connectionString, CommandType text type, string cmdText,

Params DbParameter [] partition parms)

{

NpgsqlCommand cmd = new NpgsqlCommand ();

NpgsqlConnection conn = new NpgsqlConnection (connectionString );

Try

{

PrepareCommand (cmd, conn, null, partition type, plain text, plain parms );

NpgsqlDataReader rdr = cmd. ExecuteReader (CommandBehavior. CloseConnection );

Cmd. Parameters. Clear ();

Return rdr;

}

Catch

{

Conn. Close ();

Throw;

}

}

///

/// Execute the query in the transaction and return the DataReader

///

Public DbDataReader ExecuteReader (DbTransaction trans, CommandType primitive type, string plain text,

Params DbParameter [] partition parms)

{

NpgsqlCommand cmd = new NpgsqlCommand ();

PrepareCommand (cmd, trans. Connection, trans, argument type, plain text, plain parms );

NpgsqlDataReader rdr = cmd. ExecuteReader (CommandBehavior. CloseConnection );

Cmd. Parameters. Clear ();

Return rdr;

}

///

/// Execute the query and return the first column in the first row of the returned result set. Ignore other columns or rows.

///

Public object ExecuteScalar (string connectionString, CommandType cmdType, string cmdText,

Params DbParameter [] partition parms)

{

NpgsqlCommand cmd = new NpgsqlCommand ();

Using (NpgsqlConnection connection = new NpgsqlConnection (connectionString ))

{

PrepareCommand (cmd, connection, null, partition type, plain text, plain parms );

Object val = cmd. ExecuteScalar ();

Cmd. Parameters. Clear ();

Return val;

}

}

///

/// Execute the query in the transaction and return the first column in the first row of the returned result set. Ignore other columns or rows.

///

Public object ExecuteScalar (DbTransaction trans, CommandType primitive type, string plain text,

Params DbParameter [] partition parms)

{

NpgsqlCommand cmd = new NpgsqlCommand ();

PrepareCommand (cmd, trans. Connection, trans, argument type, plain text, plain parms );

Object val = cmd. ExecuteScalar ();

Cmd. Parameters. Clear ();

Return val;

}

///

/// Generate the command to be executed

///

/// Parameter format: Colon + parameter name

Private static void PrepareCommand (DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType primitive type,

String parameter text, DbParameter [] parameter parms)

{

If (conn. State! = ConnectionState. Open)

Conn. Open ();

Cmd. Connection = conn;

Cmd. CommandText = plain text. Replace ("@", ":"). Replace ("? ",": "). Replace (" ["," \ ""). Replace ("]", "\" ");

If (trans! = Null)

Cmd. Transaction = trans;

Cmd. CommandType = primitive type;

If (partition parms! = Null)

{

Foreach (NpgsqlParameter parm in parallel parms)

{

Parm. ParameterName = parm. ParameterName. Replace ("@", ":"). Replace ("? ",":");

Cmd. Parameters. Add (parm );

}

}

}

}

Iii. Database Operation instances

Write instance:

IDBHelper dbHelper = new helper ();

String connectionString = "User ID = s; Password = admin; Server = 192.168.0.226; Port = 5432; Database = MonitorDB ;";

String SQL = "insert into [RawLog] ([ProjectID], [File], [Note]) values ('Project number', @ file, 'note ')";

String file = this. textBox1.Text;

If (! File. Exists (file) return;

// Obtain the binary stream of a file

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 );

Fs. Close ();

Int r = dbHelper. ExecuteNonQuery (connectionString, CommandType. Text, SQL, new Npgsql. NpgsqlParameter ("@ file", raw_log.File ));

Note:

1. In the query statement, the table name and field name must be enclosed by brackets [], which will be replaced with double quotation marks in the operation class.

2. The format of the parameter name in the command statement is @ or? + Parameter name, which is replaced by colons in the operation class.

3. For binary streams, dates, and other special types, the parameter format must be 2.

4. After multiple tests, the postgreSQL database only supports the (colon + parameter name) parameter method.