private void button1_Click(object sender, EventArgs e)
{
string _conStr = ConfigurationManager.AppSettings["DB_SM"];
EDBConnection connection = new EDBConnection(_conStr);
connection.Open();
string guid = "b3bd85e7280e446eb97d5d0447e148d5";
EDBCommand cmd = new EDBCommand("PCK_OFFERT_KONTROLL.HAMTA_MAX_SEKVENSNR", connection);
cmd.CommandType = CommandType.StoredProcedure;
EDBParameter p = cmd.Parameters.Add("p_inGuid", EDBTypes.EDBDbType.Varchar);
p.Value = guid;
/*p = new EDBParameter();
p.Direction = ParameterDirection.ReturnValue;
p.EDBDbType = EDBTypes.EDBDbType.Numeric;
//p = cmd.Parameters.Add("ret", EDBTypes.EDBDbType.Numeric);
cmd.Parameters.Add(p);
*/
object o = cmd.ExecuteScalar();
int sekvensnr = Convert.ToInt32(o);
connection.Close();
}
public EDBProcedureCall(string commandName, EDBConnection connection)
{
procedureParams = new List<string>();
command = new EDBCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = commandName;
command.Connection = connection;
}
private void button2_Click(object sender, EventArgs e)
{
string _conStr = ConfigurationManager.AppSettings["DB_SK"];
EDBConnection connection = new EDBConnection(_conStr);
connection.Open();
EDBTransaction et = connection.BeginTransaction();
//EDBProcedureCall pcall = new EDBProcedureCall("SK_TASKS.MESSAGE_SENT", connection, et);
EDBProcedureCall pcall = new EDBProcedureCall("nolstedt01", connection, et);
pcall.addVarcharInput("p_personnr", "196402230319");
pcall.addVarcharInput("p_contactchannel_id", "48AF6816C15B49A9956079BD6D8DE561");
pcall.prepare();
int i = pcall.executeNonQuery();
// pcall.executeReaderRefCursorOut();
et.Save("apa");
et.Commit();
connection.Close();
}
public EdbDataTableCommand(string command, EDBConnection connection) : base(command, connection)
{
}
private static void DoDemo(EDBConnection connection)
{
Console.Write("Inserting row 1... ");
using (var cmd = new EdbAffectedRowsCommand("INSERT INTO demo (value_unicode) VALUES (:param)", connection))
{
cmd.Prepare();
cmd.Parameters.Add(
new EDBParameter("param", "Lithuanian chars: ĖĮĘĮŲŠŲŪŠŲčęėūįčųū, russian chars: ваниа маша"));
// Bit overprotective probably, but using 3rd party libs its usual to be overprotective
if (cmd.Execute() != 1)
{
throw new InvalidOperationException();
}
//
Console.WriteLine("DONE");
}
Console.Write("Inserting row 2... ");
using (
var cmd = new EdbAffectedRowsCommand(
"INSERT INTO demo (value_unicode, value_decimal) VALUES (:param1, :param2)",
connection))
{
cmd.Prepare();
cmd.Parameters.Add(new EDBParameter("param1", DBNull.Value));
cmd.Parameters.Add(
new EDBParameter(
"param2",
3.1415926535897932384626433832795028841971693993751058209749445923078164062862089986280348253421170679m));
// Bit overprotective probably, but using 3rd party libs its usual to be overprotective
if (cmd.Execute() != 1)
{
throw new InvalidOperationException();
}
//
Console.WriteLine("DONE");
}
Console.Write("Inserting row 3, and get its id... ");
using (
var cmd =
new EdbAffectedRowsCommand(
"INSERT INTO demo (value_unicode, value_decimal, value_datetime, value_bool) VALUES (:param1, :param2, :param3, :param4)",
connection))
{
cmd.Prepare();
cmd.Parameters.Add(new EDBParameter("param1", DBNull.Value));
cmd.Parameters.Add(new EDBParameter("param2", DBNull.Value));
cmd.Parameters.Add(new EDBParameter("param3", DateTime.UtcNow));
cmd.Parameters.Add(new EDBParameter("param4", true));
// Bit overprotective probably, but using 3rd party libs its usual to be overprotective
if (cmd.Execute() != 1)
{
throw new InvalidOperationException();
}
//
using (var cmd2 = new EdbScalarCommand <int>("SELECT LASTIDENTITY('demo', 'id')", connection))
{
Console.WriteLine("DONE, with id - {0}", cmd2.Execute());
}
}
Console.Write("Getting two rows... ");
using (var cmd = new EdbDataTableCommand("SELECT * FROM demo RANGE 2 TO 3", connection))
{
var dt = cmd.Execute();
Console.WriteLine("DONE");
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(
"Got row with id: {0}, timestamp: {1}, values: {2},{3},{4}",
row["id"],
row["value_datetime"],
row["value_unicode"],
row["value_decimal"],
row["value_bool"]);
}
}
Console.Write("Updating timestamps where null... ");
using (
var cmd = new EdbAffectedRowsCommand(
"UPDATE demo SET value_datetime = :param WHERE value_datetime IS NULL",
connection))
{
cmd.Prepare();
cmd.Parameters.Add(new EDBParameter("param", DateTime.MinValue));
var cnt = cmd.Execute();
Console.WriteLine("DONE, {0} rows updated", cnt);
}
Console.Write("Getting all rows... ");
using (var cmd = new EdbDataTableCommand("SELECT * FROM demo", connection))
{
var dt = cmd.Execute();
Console.WriteLine("DONE");
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(
"Got row with id: {0}, timestamp: {1}, values: {2},{3},{4}",
row["id"],
row["value_datetime"],
row["value_unicode"],
row["value_decimal"],
row["value_bool"]);
}
}
}
private static EDBConnection CreateDemoConnectionInteractive(string db, bool doLocal)
{
var connectionString = new EDBConnectionStringBuilder
{
Database = "Configuration",
CharSet = "UNICODE",
ConfigMemory = false
};
if (doLocal)
{
connectionString.Type = "LOCAL";
Console.Write("Local DB folder (Hit <Enter> to switch to inmemory): ");
var localDbFolder = Console.ReadLine();
connectionString.ConfigMemory = false;
if (!string.IsNullOrEmpty(localDbFolder))
{
connectionString.ConfigPath = localDbFolder;
}
else
{
connectionString.ConfigMemory = true;
}
}
else
{
connectionString.Type = "REMOTE";
Console.Write("Host (Hit <Enter> to switch to localhost): ");
var host = Console.ReadLine();
connectionString.Host = string.IsNullOrEmpty(host) ? "localhost" : host;
Console.Write("Port (Hit <Enter> to switch to 12010): ");
var port = Console.ReadLine();
connectionString.Port = string.IsNullOrEmpty(port) ? 12010 : int.Parse(port);
}
Console.Write("Username (Hit <Enter> to Administrator): ");
var userName = Console.ReadLine();
connectionString.UID = string.IsNullOrEmpty(userName) ? "Administrator" : userName;
Console.Write("Password (Hit <Enter> to EDBDefault): ");
var password = Console.ReadLine();
connectionString.PWD = string.IsNullOrEmpty(password) ? "EDBDefault" : password;
Console.Write("Initiating connection... ");
using (var connection = new EDBConnection(connectionString.ToString()))
{
connection.Open();
Console.WriteLine("SUCCESS");
Console.Write("Opening database... ");
connectionString.Database = db;
using (var connection2 = new EDBConnection(connectionString.ToString()))
{
try
{
connection2.Open();
Console.WriteLine("SUCCESS");
}
catch (Exception)
{
Console.Write("FAILED... Creating inmemory database for demo purposes... ");
using (
var cmd =
new EDBCommand(
string.Format("CREATE DATABASE \"{0}\" IN MEMORY", db.Replace("\"", "\"\"")),
// DDL escaping, no standard way to do this
connection))
{
cmd.ExecuteNonQuery();
Console.WriteLine("SUCCESS");
}
}
Console.Write("Opening database... ");
connection2.Open();
Console.WriteLine("SUCCESS");
Console.Write("Creating demo table... ");
using (
var cmd =
new EDBCommand(
"DROP TABLE \"demo\"",
connection2))
{
try
{
cmd.ExecuteNonQuery();
}
catch // Let delete fail, it may be ok
{
}
}
using (
var cmd =
new EDBCommand(
@"
CREATE TABLE ""demo""
(
id INT GENERATED BY DEFAULT AS IDENTITY
(START WITH 0, INCREMENT BY 1) PRIMARY KEY,
value_unicode VARCHAR(255) COLLATE ""UNI"",
value_decimal DECIMAL(14,4), -- 4 is macimum scale!!! WTF?
value_dateTime TIMESTAMP,
value_bool BOOL
)",
connection2))
{
cmd.ExecuteNonQuery();
Console.WriteLine("SUCCESS");
return(new EDBConnection(connectionString.ToString()));
}
}
}
}
public EdbScalarCommand(string command, EDBConnection connection) : base(command, connection)
{
}
private static void DoDemo(EDBConnection connection)
{
Console.Write("Inserting row 1... ");
using (var cmd = new EdbAffectedRowsCommand("INSERT INTO demo (value_unicode) VALUES (:param)", connection))
{
cmd.Prepare();
cmd.Parameters.Add(
new EDBParameter("param", "Lithuanian chars: ĖĮĘĮŲŠŲŪŠŲčęėūįčųū, russian chars: ваниа маша"));
// Bit overprotective probably, but using 3rd party libs its usual to be overprotective
if (cmd.Execute() != 1)
throw new InvalidOperationException();
//
Console.WriteLine("DONE");
}
Console.Write("Inserting row 2... ");
using (
var cmd = new EdbAffectedRowsCommand(
"INSERT INTO demo (value_unicode, value_decimal) VALUES (:param1, :param2)",
connection))
{
cmd.Prepare();
cmd.Parameters.Add(new EDBParameter("param1", DBNull.Value));
cmd.Parameters.Add(
new EDBParameter(
"param2",
3.1415926535897932384626433832795028841971693993751058209749445923078164062862089986280348253421170679m));
// Bit overprotective probably, but using 3rd party libs its usual to be overprotective
if (cmd.Execute() != 1)
throw new InvalidOperationException();
//
Console.WriteLine("DONE");
}
Console.Write("Inserting row 3, and get its id... ");
using (
var cmd =
new EdbAffectedRowsCommand(
"INSERT INTO demo (value_unicode, value_decimal, value_datetime, value_bool) VALUES (:param1, :param2, :param3, :param4)",
connection))
{
cmd.Prepare();
cmd.Parameters.Add(new EDBParameter("param1", DBNull.Value));
cmd.Parameters.Add(new EDBParameter("param2", DBNull.Value));
cmd.Parameters.Add(new EDBParameter("param3", DateTime.UtcNow));
cmd.Parameters.Add(new EDBParameter("param4", true));
// Bit overprotective probably, but using 3rd party libs its usual to be overprotective
if (cmd.Execute() != 1)
throw new InvalidOperationException();
//
using (var cmd2 = new EdbScalarCommand<int>("SELECT LASTIDENTITY('demo', 'id')", connection))
{
Console.WriteLine("DONE, with id - {0}", cmd2.Execute());
}
}
Console.Write("Getting two rows... ");
using (var cmd = new EdbDataTableCommand("SELECT * FROM demo RANGE 2 TO 3", connection))
{
var dt = cmd.Execute();
Console.WriteLine("DONE");
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(
"Got row with id: {0}, timestamp: {1}, values: {2},{3},{4}",
row["id"],
row["value_datetime"],
row["value_unicode"],
row["value_decimal"],
row["value_bool"]);
}
}
Console.Write("Updating timestamps where null... ");
using (
var cmd = new EdbAffectedRowsCommand(
"UPDATE demo SET value_datetime = :param WHERE value_datetime IS NULL",
connection))
{
cmd.Prepare();
cmd.Parameters.Add(new EDBParameter("param", DateTime.MinValue));
var cnt = cmd.Execute();
Console.WriteLine("DONE, {0} rows updated", cnt);
}
Console.Write("Getting all rows... ");
using (var cmd = new EdbDataTableCommand("SELECT * FROM demo", connection))
{
var dt = cmd.Execute();
Console.WriteLine("DONE");
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(
"Got row with id: {0}, timestamp: {1}, values: {2},{3},{4}",
row["id"],
row["value_datetime"],
row["value_unicode"],
row["value_decimal"],
row["value_bool"]);
}
}
}
private static EDBConnection CreateDemoConnectionInteractive(string db, bool doLocal)
{
var connectionString = new EDBConnectionStringBuilder
{
Database = "Configuration",
CharSet = "UNICODE",
ConfigMemory = false
};
if (doLocal)
{
connectionString.Type = "LOCAL";
Console.Write("Local DB folder (Hit <Enter> to switch to inmemory): ");
var localDbFolder = Console.ReadLine();
connectionString.ConfigMemory = false;
if (!string.IsNullOrEmpty(localDbFolder))
{
connectionString.ConfigPath = localDbFolder;
}
else
{
connectionString.ConfigMemory = true;
}
}
else
{
connectionString.Type = "REMOTE";
Console.Write("Host (Hit <Enter> to switch to localhost): ");
var host = Console.ReadLine();
connectionString.Host = string.IsNullOrEmpty(host) ? "localhost" : host;
Console.Write("Port (Hit <Enter> to switch to 12010): ");
var port = Console.ReadLine();
connectionString.Port = string.IsNullOrEmpty(port) ? 12010 : int.Parse(port);
}
Console.Write("Username (Hit <Enter> to Administrator): ");
var userName = Console.ReadLine();
connectionString.UID = string.IsNullOrEmpty(userName) ? "Administrator" : userName;
Console.Write("Password (Hit <Enter> to EDBDefault): ");
var password = Console.ReadLine();
connectionString.PWD = string.IsNullOrEmpty(password) ? "EDBDefault" : password;
Console.Write("Initiating connection... ");
using (var connection = new EDBConnection(connectionString.ToString()))
{
connection.Open();
Console.WriteLine("SUCCESS");
Console.Write("Opening database... ");
connectionString.Database = db;
using (var connection2 = new EDBConnection(connectionString.ToString()))
{
try
{
connection2.Open();
Console.WriteLine("SUCCESS");
}
catch (Exception)
{
Console.Write("FAILED... Creating inmemory database for demo purposes... ");
using (
var cmd =
new EDBCommand(
string.Format("CREATE DATABASE \"{0}\" IN MEMORY", db.Replace("\"", "\"\"")),
// DDL escaping, no standard way to do this
connection))
{
cmd.ExecuteNonQuery();
Console.WriteLine("SUCCESS");
}
}
Console.Write("Opening database... ");
connection2.Open();
Console.WriteLine("SUCCESS");
Console.Write("Creating demo table... ");
using (
var cmd =
new EDBCommand(
"DROP TABLE \"demo\"",
connection2))
{
try
{
cmd.ExecuteNonQuery();
}
catch // Let delete fail, it may be ok
{
}
}
using (
var cmd =
new EDBCommand(
@"
CREATE TABLE ""demo""
(
id INT GENERATED BY DEFAULT AS IDENTITY
(START WITH 0, INCREMENT BY 1) PRIMARY KEY,
value_unicode VARCHAR(255) COLLATE ""UNI"",
value_decimal DECIMAL(14,4), -- 4 is macimum scale!!! WTF?
value_dateTime TIMESTAMP,
value_bool BOOL
)",
connection2))
{
cmd.ExecuteNonQuery();
Console.WriteLine("SUCCESS");
return new EDBConnection(connectionString.ToString());
}
}
}
}
private void button4_Click(object sender, EventArgs e)
{
string _conStr = ConfigurationManager.AppSettings["DB_SK"];
_conStr += ";Pooling=False";
EDBConnection connection = new EDBConnection(_conStr);
connection.Open();
EDBTransaction tra = connection.BeginTransaction();
EDBCommand cmd = new EDBCommand("nolstedt01(:p_personnr,:p_contactchannel_id)", connection, tra);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("p_personnr", "196402230319");
cmd.Parameters.AddWithValue("p_contactchannel_id", "48AF6816C15B49A9956079BD6D8DE561");
cmd.Prepare();
cmd.ExecuteNonQuery();
tra.Save("apa");
tra.Commit();
connection.Close();
}
public EdbAffectedRowsCommand(string command, EDBConnection connection) : base(command, connection)
{
}
public EDBProcedureCall(string commandName, EDBConnection connection, EDBTransaction transaction)
: this(commandName, connection)
{
command.Transaction = transaction;
}