For postgres, you also need to replace "\"
public string escape (string s)
{
s = s.Replace("\\", "\\\\"); // Replace \ with \\
s = s.Replace("\'", "\'\'"); // Replace ' with '' (SQL Standard)
//s = s.Replace("\"", "\\\""); not necessary if enclosed in single quotes.
return s;
}
here's mine, which is part of a postgre util I am using.. :) probably the most usefull would be the ConvertTo.Sql(string) and ConvertTo.Sql(Guid), it should be pretty usable as-is, I'm using BYTEA to store System.Guid ... was a bit of a pita to get the encoding right, but it works smoothly, the main purpose of the ConvertTo.Sql() method overloads, is for simple queries, especially (for me) queries with a guid as a key field, which runs pretty smooth from what I've done so far. Thinking on doing an article focused on using .Net with Npgsql + Postgre 8 ...
The only real gotchas I've come accross so far are dealing with the guid encoding, and haven't worked with some of the array values coming out of postgre (haven't needed them)...
So far I haven't really tested it all, only bits and pieces, it's a conversion from a mysql util I was working on.. getting ready to start using it in the AM for a new project.
If someone has a better method of escaping the binary strings, let me know,
(yes, I know about the conversion methods under the Microsoft.VisualBasic namespace, but I wanted to avoid using it)
-- Michael J. Ryan - tracker1(at)theroughnecks(dot)com - www.theroughnecks.net icq: 4935386 - AIM/AOL: azTracker1 - Y!: azTracker1 - MSN/Win: (email)
/******************************************************************************* Some DB Utility Functions for Postgre by Michael J. Ryan (tracker1 - at - theroughnecks.com) *******************************************************************************/ using System; using System.Configuration; using System.Data; using System.Text; using Npgsql; using NpgsqlTypes;
namespace Roughneck.PostgreUtility {
public class DB {
private DB() { /* Operates as a static singleton */ }
public static int Exec(string query) {
NpgsqlConnection cn = new
NpgsqlConnection(ConnectionString);
cn.Open();
int ret = Exec(query, cn);
cn.Close();
cn = null;
return ret;
}
public static int Exec(string query, NpgsqlConnection cn) {
NpgsqlCommand cmd = new NpgsqlCommand(query, cn);
return cmd.ExecuteNonQuery();
}
public static int Exec(string query, NpgsqlConnection cn,
NpgsqlTransaction tx) {
NpgsqlCommand cmd = new NpgsqlCommand(query, cn, tx);
return cmd.ExecuteNonQuery();
}
public static DataTable GetTable(string query) {
DataTable ret = new DataTable();
NpgsqlConnection cn = new
NpgsqlConnection(ConnectionString);
NpgsqlDataAdapter dad = new NpgsqlDataAdapter(query,
cn);
cn.Open();
dad.Fill(ret);
cn.Close();
return ret;
}
public static DataTable GetTable(string query, NpgsqlConnection
cn) {
DataTable ret = new DataTable();
NpgsqlDataAdapter dad = new NpgsqlDataAdapter(query,
cn);
dad.Fill(ret);
return ret;
}
public static DataTable GetTable(string query, NpgsqlConnection
cn, NpgsqlTransaction tx) {
DataTable ret = new DataTable();
NpgsqlCommand cmd = new
NpgsqlCommand(query, cn, tx);
NpgsqlDataAdapter dad = new
NpgsqlDataAdapter(cmd);
dad.Fill(ret);
return ret;
}
public static string GetSelect(string tablename, string[]
fields, string whereClause) {
return GetSelect(tablename, fields) + "\r\n " +
whereClause;
}
public static string GetSelect(string tablename, string[]
fields) {
StringBuilder query = new StringBuilder(1000);
query.Append("SELECT ");
for (int i=0; i< fields.Length; i++)
query.Append(string.Format("\r\n\t\"{0}\"{1}",fields[i],((fields.Length ==
i+1)?"":",")));
query.Append(string.Format("\r\nFROM \"{0}\"",
tablename));
return query.ToString();
}
public static string GetInsertQuery(string tableName, string[]
fields) {
//INSERT
StringBuilder query = new StringBuilder(1000);
query.Append(string.Format("INSERT INTO \"{0}\"
(",tableName));
for (int i=0; i<fields.Length; i++)
query.Append(string.Format("\r\n\t\"{0}\"{1}",fields[i],((fields.Length ==
i+1)?"":",")));
query.Append("\r\n) VALUES (");
for (int i=0; i<fields.Length; i++)
query.Append(string.Format("\r\n\t @{0}
{1}",fields[i].Replace("-",""),((fields.Length == i+1)?"":",")));
query.Append("\r\n)");
return query.ToString();
}
public static string GetUpdateQuery(string tableName, string[]
fields, string keyFieldName) {
StringBuilder query = new StringBuilder(1000);
query.Append(string.Format("UPDATE \"{0}\"
SET",tableName));
for (int i=0; i<fields.Length; i++)
if (fields[i].ToLower() !=
keyFieldName.ToLower())
query.Append(string.Format("\r\n\t\"{0}\"[EMAIL
PROTECTED]",fields[i],fields[i].Replace("-",""),((fields.Length ==
i+1)?"":",")));
query.Append(string.Format(
"\r\nWHERE \"{0}\"[EMAIL PROTECTED]",
keyFieldName
));
return query.ToString();
}
public static string GetUpdateQuery(string tableName, string[]
fields, string keyFieldName, Guid keyValue) {
return GetUpdateQuery(tableName, fields, keyFieldName,
(object)ConvertTo.Sql(keyValue), true)
}
public static string GetUpdateQuery(string tableName, string[]
fields, string keyFieldName, object keyValue, bool keyIsNumeric) {
StringBuilder query = new StringBuilder(1000);
query.Append(string.Format("UPDATE \"{0}\"
SET",tableName));
for (int i=0; i<fields.Length; i++)
query.Append(string.Format("\r\n\t\"{0}\"[EMAIL
PROTECTED]",fields[i],fields[i].Replace("-",""),((fields.Length ==
i+1)?"":",")));
query.Append(string.Format(
"\r\nWHERE \"{0}\"={2}{1}{2}\r\n",
keyFieldName,
((keyIsNumeric)?keyValue:keyValue.ToString().Replace("'","''")),
((keyIsNumeric)?"":"'")
));
return query.ToString();
}
public static String ConnectionString {
get { return
ConfigurationSettings.AppSettings["ConnectionString"]; }
}
public class ConvertTo {
private static string Escape(byte input) {
int x = (int)input;
return string.Format("\\\\{0}{1}{2}",((x>>6) &
7),((x>>3) & 7),(x & 7));
}
private static string Escape(char input) {
if (input == '\\')
return "\\134"; //special
character escape
else if (input == '\'')
return "\\047"; //special
character escape
else if (input >= (char)32 && input <=
(char)127)
return input.ToString();
//us-ascii - leave alone
else if (input <= (char)byte.MaxValue)
return Escape((byte)input);
//upper/lower ascii char, escape
else
return input.ToString();
//unicode - leave alone
}
public static Guid Guid(object input) {
if (DBNull.Value.Equals(input))
return new Guid();
else
return new Guid((byte[])input);
}
public static bool Boolean(object input) {
return Boolean(input, false); //default false
}
public static bool Boolean(object input, bool
defaultValue) {
if (DBNull.Value.Equals(input))
return defaultValue; //null defaults to
false
switch ((input.ToString().ToLower() +
((defaultValue)?"1":"0"))[0]) {
case '0':
return false;
case 'f':
return false;
case 'n':
return false;
default:
return true;
}
}
public static string Sql(Guid input) {
return Sql(input.ToByteArray());
}
public static string Sql(byte[] input) {
StringBuilder ret = new
StringBuilder(input.Length * 5);
int x;
for (int i=0; i<input.Length; i++) {
x = (int)input[i];
ret.AppendFormat("\\\\{0}{1}{2}",((x>>6) & 7),((x>>3) & 7),(x & 7));
}
return "'" + ret.ToString() + "'::\"bytea\"";
}
public static string Sql(bool input) {
return (input)?"true":"false";
}
public static string Sql(string input) {
StringBuilder ret = new
StringBuilder(input.Length * 2);
for (int i=0; i<input.Length; i++) {
ret.Append(Escape((char)input[i]));
}
return "'" + ret.ToString() + "'";
}
public static string Sql(int input) {
return input.ToString();
}
public static string Sql(long input) {
return input.ToString();
}
public static string Sql(float input) {
return input.ToString();
}
public static string Sql(double input) {
return input.ToString();
}
public static string Sql(decimal input) {
return input.ToString();
}
}
}
}
