I'm new to C# so I apologize if this is an obvious question but I can't seem to find an answer to it. I'm writing a client application that takes a raw SQL command and passes it directly to the database. I don't know if it will be an select, update, insert, or delete. If it's a select, I want to get the results into a DataTable. If it's anything else, I want to know if it was successful or if there was an error in execution (for example: no rows were affected).
I am current doing the below code before I found out it doesn't return any errors for update or delete affects no rows. I formatted the code but tried to leave enough to understand what I am trying to do. Since this is my first question on here, if you have any tips on how better to contextualise my questions, please share.
class Client
{
static void Main(string[] args)
{
Config.LoadConfig();
Db.Init();
var testQuery1 = "select * from test where 1=1";
var testQuery2 = "update test set value='value3' where name = 'name3'";
var testQuery3 = "insert into test (id, name, value, date) values (4,'name4','value4',current_timestamp)";
Db.Query(testQuery1);
Db.PrintResults();
Db.Query(testQuery2);
Db.PrintResults();
Db.Query(testQuery3);
Db.PrintResults();
}
}
/// <summary>
/// Universal database class.
/// </summary>
public static partial class Db
{
private static IUniversalDatabase _dbUniConn;
/* Database Results */
public static string ErrorCode { get; private set; }
public static string ErrorMessage { get; private set; }
public static int RowCount { get; private set; }
public static double ExecutionSeconds { get; private set; }
public static DataTable Data { get; private set; } = new DataTable();
public static void Init(string connString, Type dbType)
{
try
{
_connString = connString;
_connType = dbType;
switch (_connType)
{
case Db.Type.PostgreSQL:
_dbUniConn = new PostgresDatabase(_connString);
break;
...
}
}
}
public static void Open()
{ ... _dbUniConn.Open(); ... }
public static void Close()
{ ... _dbUniConn.Close(); ... }
public static void Query(string cmdString)
{
var start = DateTime.Now;
Open();
/* Reset Data */
Data.Reset();
RowCount = 0;
ErrorMessage = "";
ErrorCode = "";
/* Execute Query */
_dbUniConn.Query(cmdString);
/* Execution Time */
ExecutionSeconds = (DateTime.Now - start).TotalSeconds;
Debug.Write("Database: Execution Time: " + ExecutionSeconds.ToString() + " seconds", Debug.TraceLevel.Info);
/* RowCount and Error Message */
RowCount = Data.Rows.Count;
Debug.Write("Database: Returning " + RowCount.ToString() + " rows", Debug.TraceLevel.Info);
Debug.Write("Database: Error (" + ErrorCode + "): " + ErrorMessage, Debug.TraceLevel.Info);
Close();
}
public class PostgresDatabase : IUniversalDatabase
{
private NpgsqlConnection _dbConn;
public PostgresDatabase(string connString)
{ _dbConn = new NpgsqlConnection(connString); }
public void Open()
{ _dbConn.Open(); }
public void Close()
{ _dbConn.Close(); }
public void Dispose()
{ _dbConn.Dispose(); }
public void Query(string cmdString)
{
NpgsqlCommand cmd = new NpgsqlCommand(cmdString, _dbConn);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
cmd.CommandType = System.Data.CommandType.Text;
try
{
da.Fill(Data);
}
catch (Exception e)
{
MatchCollection configVar = Regex.Matches(e.Message.ToString(), @"^(?<code>.*?): (?<msg>.*?)$");
foreach (Match match in configVar)
{
ErrorCode = match.Groups["code"].Value.ToString();
ErrorMessage = match.Groups["msg"].Value.ToString();
}
}
da.Dispose();
cmd.Dispose();
}
}
}
I solved this by adding RETURNING 1 to my query when it started with anything but select:
public void Query()
{
Match m = Regex.Match(QueryInput.queryString, @"^\s*(?i:select).*$");
if (!m.Success)
{
QueryInput.queryString += " RETURNING 1";
}
NpgsqlCommand cmd = new NpgsqlCommand(QueryInput.queryString, _dbConn);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
cmd.CommandType = System.Data.CommandType.Text;
try
{
da.Fill(Data);
}
catch (Exception e)
{
m = Regex.Match(e.Message.ToString(), @"^(?<code>.*?): (?<msg>.*?)$");
if (m.Success)
{
ErrorCode = m.Groups["code"].Value.ToString();
ErrorMessage = m.Groups["msg"].Value.ToString();
}
else
throw e;
}
da.Dispose();
cmd.Dispose();
}
}
I tested this with the following:
Db.Query("update test set value = 'value1' where name in (\nselect name from test where name = 'name1')", Db.Type.PostgreSQL);
Db.PrintResults();
Db.Query("\nSELECT * from test limit 10", Db.Type.PostgreSQL);
Db.PrintResults();
Db.Query("update test set value='new value' where name='name1'", Db.Type.PostgreSQL);
Db.PrintResults();
Db.Query("insert into test (id,name,value,date) values (3,'name3',value3,current_timestamp)", Db.Type.PostgreSQL);
Db.PrintResults();
Db.Query("delete from test where id = 3", Db.Type.PostgreSQL);
Db.PrintResults();
This returned the following results:
13:43:57:014 - Info - Database: Received [PostgreSQL] command (update test set value = 'value1' where name in (
select name from test where name = 'name1'))
13:43:57:015 - Debug - Database: Connection is now opened.
13:43:57:070 - Info - Database: Execution Time: 0.055539 seconds
13:43:57:071 - Info - Database: Returning 1 rows
13:43:57:071 - Info - Database: Error (0): Success.
13:43:57:071 - Debug - Database: Connection is now closed.
13:43:57:072 - Debug - Database: Result ?column?
13:43:57:072 - Debug - Database: Result 1: 1
13:43:57:072 - Info - Database: Received [PostgreSQL] command (
SELECT * from test limit 10)
13:43:57:072 - Debug - Database: Connection is now opened.
13:43:57:088 - Info - Database: Execution Time: 0.0160114 seconds
13:43:57:088 - Info - Database: Returning 2 rows
13:43:57:088 - Info - Database: Error (0): Success.
13:43:57:089 - Debug - Database: Connection is now closed.
13:43:57:089 - Debug - Database: Result id name value date
13:43:57:089 - Debug - Database: Result 1: 1 name1 value1 6/14/2017 8:37:48 PM
13:43:57:089 - Debug - Database: Result 2: 2 name2 value2 6/14/2017 8:37:48 PM
13:43:57:089 - Info - Database: Received [PostgreSQL] command (update test set value='new value' where name='name1')
13:43:57:089 - Debug - Database: Connection is now opened.
13:43:57:090 - Info - Database: Execution Time: 0.0010007 seconds
13:43:57:090 - Info - Database: Returning 1 rows
13:43:57:091 - Info - Database: Error (0): Success.
13:43:57:092 - Debug - Database: Connection is now closed.
13:43:57:093 - Debug - Database: Result ?column?
13:43:57:093 - Debug - Database: Result 1: 1
13:43:57:094 - Info - Database: Received [PostgreSQL] command (insert into test (id,name,value,date) values (3,'name3',value3,current_timestamp))
13:43:57:096 - Debug - Database: Connection is now opened.
13:43:57:186 - Info - Database: Execution Time: 0.0920653 seconds
13:43:57:187 - Info - Database: Returning 0 rows
13:43:57:191 - Info - Database: Error (42703): column "value3" does not exist
13:43:57:192 - Debug - Database: Connection is now closed.
13:43:57:199 - Debug - Database: Result
13:43:57:200 - Info - Database: Received [PostgreSQL] command (delete from test where id = 3)
13:43:57:201 - Debug - Database: Connection is now opened.
13:43:57:204 - Info - Database: Execution Time: 0.0040029 seconds
13:43:57:204 - Info - Database: Returning 0 rows
13:43:57:205 - Info - Database: Error (-1403): No rows found.
13:43:57:205 - Debug - Database: Connection is now closed.
13:43:57:206 - Debug - Database: Result ?column?