1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
4
<meta http-equiv="Content-Type" content="text/html; charset=US-ASCII">
5
<title>psqlODBC HOWTO - C#</title>
8
<body bgcolor="#ffffff" text="#000000" link="#ff0000" vlink="#a00000" alink="#0000ff">
10
<h1>psqlODBC HOWTO - C#</h1>
15
Author: Dave Page (dpage@postgresql.org)<br>
16
Release Date: 12 April 2002<br>
17
Description: Example based Mini-Howto on Accessing PostgreSQL from C#
20
This document provides some sample code to get you started with C# & PostgreSQL.
22
Requirements to get the code to work:
26
<li>A C# Compiler.</li>
27
<li>The Microsoft .NET Framework.</li>
28
<li>The Microsoft ODBC .NET Data Provider.</li>
29
<li>A PostgreSQL datasource.</li>
32
The example code shown below may need some modification to make it actually work in your environment.
33
There is one table used in the example:
39
id serial,
40
data text,
41
accessed timestamp
43
INSERT INTO csharptest(data, accessed) VALUES('Rows: 1', now());
44
INSERT INTO csharptest(data, accessed) VALUES('Rows: 2', now());
45
INSERT INTO csharptest(data, accessed) VALUES('Rows: 3', now());
56
using Microsoft.Data.Odbc;
63
static void Main(string[] args)
66
// Setup a connection string
67
string szConnect = "DSN=dsnname;" +
71
// Attempt to open a connection
72
OdbcConnection cnDB = new OdbcConnection(szConnect);
74
// The following code demonstrates how to catch & report an ODBC exception.
75
// To keep things simple, this is the only exception handling in this example.
76
// Note: The ODBC data provider requests ODBC3 from the driver. At the time of
77
// writing, the psqlODBC driver only supports ODBC2.5 - this will cause
78
// an additional error, but will *not* throw an exception.
83
catch (OdbcException ex)
85
Console.WriteLine (ex.Message + "\n\n" + "StackTrace: \n\n" + ex.StackTrace);
86
// Pause for the user to read the screen.
87
Console.WriteLine("\nPress <RETURN> to continue...");
93
DataSet dsDB = new DataSet();
94
OdbcDataAdapter adDB = new OdbcDataAdapter();
95
OdbcCommandBuilder cbDB = new OdbcCommandBuilder(adDB);
96
adDB.SelectCommand = new OdbcCommand(
97
"SELECT id, data, accessed FROM csharptest",
101
// Display the record count
102
Console.WriteLine("Table 'csharptest' contains {0} rows.\n",
103
dsDB.Tables[0].Rows.Count);
105
// List the columns (using a foreach loop)
106
Console.WriteLine("Columns\n=======\n");
108
foreach(DataColumn dcDB in dsDB.Tables[0].Columns)
109
Console.WriteLine("{0} ({1})", dcDB.ColumnName, dcDB.DataType);
110
Console.WriteLine("\n");
112
// Iterate through the rows and display the data in the table (using a for loop).
113
// Display the data column last for readability.
114
Console.WriteLine("Data\n====\n");
115
for(int i=0;i<dsDB.Tables[0].Rows.Count;i++){
116
Console.WriteLine("id: {0}, accessed: {1}, data: {2}",
117
dsDB.Tables[0].Rows[i]["id"],
118
dsDB.Tables[0].Rows[i]["accessed"],
119
dsDB.Tables[0].Rows[i]["data"]);
122
// Add a new row to the table using the dataset
123
// Create a new row on the existing dataset, then set the values and add the row
124
Console.WriteLine("\nInserting a new row...");
125
DataRow rwDB = dsDB.Tables[0].NewRow();
126
int iRows = dsDB.Tables[0].Rows.Count + 1;
127
rwDB["data"] = "Rows: " + iRows.ToString();
128
rwDB["accessed"] = System.DateTime.Now;
129
dsDB.Tables[0].Rows.Add(rwDB);
132
// Delete a row from the table using a direct SQL query.
133
// This method can also be used for direct INSERTs UPDATEs, CREATEs DROPs and more.
134
Console.WriteLine("\nDeleting the row with the lowest ID...");
135
OdbcCommand cmDB = new OdbcCommand(
136
"DELETE FROM csharptest WHERE id = (SELECT min(id) FROM csharptest)",
138
cmDB.ExecuteNonQuery();
140
// Execute a scalar query
141
cmDB = new OdbcCommand("SELECT max(id) FROM csharptest", cnDB);
142
string szMax = cmDB.ExecuteScalar().ToString();
143
Console.WriteLine("\nThe maximum value in the id column is now: {0}", szMax);
145
// Pause for the user to read the screen.
146
Console.WriteLine("\nPress <RETURN> to continue...");
b'\\ No newline at end of file'