| public static OnSQL ( string name, string sql ) : void | ||
| name | string | |
| sql | string | |
| return | void | |
public override List <List <string> > Select(string sql, List <Database.Bind> bind_var = null)
{
if (!this.IsConnected)
{
throw new WmibException("The database is not connected");
}
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, this.connection);
if (bind_var != null)
{
BindVars(sql, bind_var, command);
}
SystemHooks.OnSQL(LocalName, sql);
Npgsql.NpgsqlDataReader dr = command.ExecuteReader();
List <List <string> > results = new List <List <string> >();
while (dr.Read())
{
List <string> line = new List <string>();
results.Add(line);
for (int i = 0; i < dr.FieldCount; i++)
{
line.Add(dr[i].ToString());
}
}
return(results);
}
public override int Delete(string table, string query)
{
int result = 0;
string sql = "DELETE FROM " + table;
if (!String.IsNullOrEmpty(query))
{
sql += " " + query;
}
SystemHooks.OnSQL(LocalName, sql);
lock (DatabaseLock)
{
try
{
Npgsql.NpgsqlCommand SqlCommand = new Npgsql.NpgsqlCommand(sql, this.connection);
result = SqlCommand.ExecuteNonQuery();
}
catch (Npgsql.NpgsqlException me)
{
ErrorBuffer = me.Message;
HandleError(sql);
}
}
return(result);
}
public override void ExecuteNonQuery(string sql, List <Bind> bind_var = null)
{
if (!this.IsConnected)
{
throw new WmibException("The database is not connected");
}
try
{
Npgsql.NpgsqlCommand c = new Npgsql.NpgsqlCommand(sql, this.connection);
if (bind_var != null)
{
BindVars(sql, bind_var, c);
}
SystemHooks.OnSQL(LocalName, sql);
c.ExecuteNonQuery();
}
catch (Npgsql.NpgsqlException me)
{
ErrorBuffer = me.Message;
HandleError(sql, bind_var);
}
}
public override void Commit()
{
SystemHooks.OnSQL(LocalName, "commit;");
Npgsql.NpgsqlCommand c = new Npgsql.NpgsqlCommand("commit;", this.connection);
c.ExecuteNonQuery();
}
public override bool InsertRow(string table, Row row)
{
StringBuilder sql = new StringBuilder();
lock (DatabaseLock)
{
try
{
if (!IsConnected)
{
Syslog.DebugLog("Postponing request to insert a row into database which is not connected");
//lock(unwritten.PendingRows)
//{
// unwritten.PendingRows.Add(new SerializedRow(table, row));
//}
//FlushRows();
return(false);
}
Npgsql.NpgsqlCommand s = new Npgsql.NpgsqlCommand();
s.Connection = this.connection;
sql.Append("INSERT INTO ");
sql.Append(table);
// let's check if there are headers
bool headers = true;
string header = "";
foreach (Row.Value value in row.Values)
{
if (value.Column == null)
{
headers = false;
break;
}
header += value.Column + ", ";
}
if (header.EndsWith(", "))
{
header = header.Substring(0, header.Length - 2);
}
if (headers)
{
sql.Append(" (" + header + ")");
}
sql.Append(" VALUES (");
int cv = 0;
foreach (Row.Value value in row.Values)
{
sql.Append(":v" + cv.ToString() + ", ");
switch (value.Type)
{
case DataType.Boolean:
s.Parameters.Add(new Npgsql.NpgsqlParameter("v" + cv.ToString(), NpgsqlTypes.NpgsqlDbType.Boolean));
s.Parameters[cv].Value = bool.Parse(value.Data);
break;
case DataType.Integer:
s.Parameters.Add(new Npgsql.NpgsqlParameter("v" + cv.ToString(), NpgsqlTypes.NpgsqlDbType.Integer));
s.Parameters[cv].Value = int.Parse(value.Data);
break;
case DataType.Varchar:
s.Parameters.Add(new Npgsql.NpgsqlParameter("v" + cv.ToString(), NpgsqlTypes.NpgsqlDbType.Varchar));
s.Parameters[cv].Value = value.Data;
break;
case DataType.Text:
s.Parameters.Add(new Npgsql.NpgsqlParameter("v" + cv.ToString(), NpgsqlTypes.NpgsqlDbType.Text));
s.Parameters[cv].Value = value.Data;
break;
case DataType.Date:
s.Parameters.Add(new Npgsql.NpgsqlParameter("v" + cv.ToString(), NpgsqlTypes.NpgsqlDbType.Timestamp));
s.Parameters[cv].Value = DateTime.Parse(value.Data);
break;
}
cv++;
}
if (sql.ToString().EndsWith(", "))
{
sql.Remove(sql.Length - 2, 2);
}
sql.Append(");");
s.CommandText = sql.ToString();
SystemHooks.OnSQL(LocalName, sql.ToString());
s.ExecuteNonQuery();
return(true);
}
catch (Npgsql.NpgsqlException me)
{
ErrorBuffer = me.Message;
Syslog.Log("Error while storing a row to DB " + me, true);
Syslog.DebugLog("SQL: " + sql.ToString());
HandleError(sql.ToString());
/*lock(unwritten.PendingRows)
* {
* unwritten.PendingRows.Add(new SerializedRow(table, row));
* }
* FlushRows();
*
*
*/
return(false);
}
}
}