public async Task <string> Create(string messageString)
{
using (var db = new PostgresDB())
{
try
{
var result = await db.InsertAsync(new Message { MessageString = messageString });
return("Added: " + messageString);
}
catch (Exception ex)
{
return($"[ERROR]: Can't connect to Postgres [{ex.Message}]");
}
}
}
public static void Execute()
{
DataConnection.DefaultSettings = new MySettings();
var db = new PostgresDB();
var query = from album in db.Albums
from artist in db.Artists
where album.ArtistId == artist.ArtistId && artist.ArtistId > 5
select album;
Console.WriteLine("SUBQUERY-----");
foreach (var album in query)
{
Console.WriteLine(album.Title);
}
}
public static void Execute()
{
DataConnection.DefaultSettings = new MySettings();
using var db = new PostgresDB();
var query = from album in db.Albums
where album.Title.StartsWith("B")
select album.Title;
Console.WriteLine("SELECT-----");
foreach (var product in query)
{
Console.WriteLine(product);
}
}
public static void Execute()
{
DataConnection.DefaultSettings = new MySettings();
using var db = new PostgresDB();
var query = from album in db.Albums
orderby album.Title
select album;
Console.WriteLine("ORDERBY-----");
foreach (var album in query)
{
Console.WriteLine(album.Title);
}
}
public static User roleFromLogin(string user, string password)
{
//get the role type from the user table
DataConnection.DefaultSettings = new PostgreSQLDbSettings("Server = localhost; Port = 5432; Database = postgres; User Id = read_users; Password = jeff; Pooling = true; MinPoolSize = 10; MaxPoolSize = 100;");
User completeUser = new User();
using (var db = new PostgresDB())
{
var q =
from u in db.Users
where u.UserColumn == user && u.Password == password
select u;
bool userFound = false;
bool bDuplicateUser = false;
foreach (var u in q)
{
if (userFound)
{
bDuplicateUser = true;
break;
}
userFound = true;
completeUser = u;
Console.WriteLine(completeUser.Id + ", " + completeUser.UserColumn + ", " + completeUser.Password + ", " + completeUser.Role);
}
if (bDuplicateUser)
{
//raise warning
}
}
return(completeUser);
//return the role back to be cookied and used by the client.
//set the settings to be that role as given per resource acquisition by client.
//DataConnection.DefaultSettings = new PostgreSQLDbSettings("Server = localhost; Port = 5432; Database = postgres; User Id = read_users; Password = jeff; Pooling = true; MinPoolSize = 10; MaxPoolSize = 100;");
}
public static void Execute()
{
DataConnection.DefaultSettings = new MySettings();
using var db = new PostgresDB();
var query = from album in db.Albums
group album by album.ArtistId into albumGroup
select new {
ArtistID = albumGroup.Key,
Count = albumGroup.LongCount()
};
Console.WriteLine("GROUPBY-----");
foreach (var queryObject in query)
{
Console.WriteLine("{0} {1}", queryObject.Count, queryObject.ArtistID);
}
}
public static void Execute()
{
DataConnection.DefaultSettings = new MySettings();
using var db = new PostgresDB();
var query = from album in db.Albums
join artist in db.Artists on album.ArtistId equals artist.ArtistId
where album.Title.Equals("Let There Be Rock")
select new
{
album,
artist
};
Console.WriteLine("JOIN-----");
foreach (var queryObject in query)
{
//Print first three columns
Console.WriteLine("{0}", queryObject.artist.Name);
}
}
public async Task <string> GetById(int id)
{
using (var db = new PostgresDB())
{
try
{
var message = await db.Message.FirstOrDefaultAsync(m => m.Id == id);
if (message != null)
{
return($"{message.Id} : {message.MessageString}");
}
else
{
return("Not found");
}
}
catch (Exception ex)
{
return($"[ERROR]: Can't connect to Postgres [{ex.Message}]");
}
}
}
public async Task <string> Get(string messageString)
{
using (var db = new PostgresDB())
{
try
{
var message = await db.Message.Where(m => m.MessageString == messageString).ToListAsync();
if (message.Any())
{
return($"{message.First().Id} : {message.First().MessageString}");
}
else
{
return("Not found");
}
}
catch (Exception ex)
{
return($"[ERROR]: Can't connect to Postgres [{ex.Message}]");
}
}
}
public object Get(int id)
{
int i = 0;
string sp = "call transfer7(:a,:pmsg)", jsonStr = "", j = "a";
var db = new PostgresDB();
//bool rtn = true;
//var list = db.GetRefCursorData(sp, null, out rtn); //Successfully Data Return
//var ds = db.ExecuteStoredProcedure(new List<NpgsqlParameter>()
//{
// new NpgsqlParameter("a", NpgsqlTypes.NpgsqlDbType.Integer) { Value = 0 },
// //new NpgsqlParameter("b", NpgsqlTypes.NpgsqlDbType.Refcursor){ Value=j},
//}, sp);
var ob = new CLASSNAME();
//foreach(DataTable tab in ds.Tables)
//foreach (DataRow dr in tab.Rows)
//{
// ob.USER_ID = (dr["USER_ID"] == DBNull.Value) ? 0 : Convert.ToInt64(dr["USER_ID"]);
// ob.USERNAME = (dr["USERNAME"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["USERNAME"]);
// ob.PASSWORD = (dr["password"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["password"]);
// ob.EMAIL = (dr["email"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["email"]);
// ob.CREATED_ON = (dr["created_on"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["created_on"]);
// ob.LAST_LOGIN = (dr["last_login"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["last_login"]);
//}
var ds = db.ExecuteFunction(new List <NpgsqlParameter>()
{
//new NpgsqlParameter("a", NpgsqlTypes.NpgsqlDbType.Integer){ Value=0,Direction=ParameterDirection.InputOutput},
//new NpgsqlParameter<string>("pmsg", NpgsqlTypes.NpgsqlDbType.Varchar){ Value=j,Direction=ParameterDirection.InputOutput},
}, "show_cities_multiple2");
ds = db.ExecuteStoredProcedure(new List <NpgsqlParameter>()
{
new NpgsqlParameter("a", NpgsqlTypes.NpgsqlDbType.Integer)
{
Value = 0, Direction = ParameterDirection.InputOutput
},
new NpgsqlParameter <string>("pmsg", NpgsqlTypes.NpgsqlDbType.Varchar)
{
Value = j, Direction = ParameterDirection.InputOutput
},
new NpgsqlParameter("ref1", NpgsqlTypes.NpgsqlDbType.Refcursor)
{
Value = j, Direction = ParameterDirection.InputOutput
},
new NpgsqlParameter("ref2", NpgsqlTypes.NpgsqlDbType.Refcursor)
{
Value = j, Direction = ParameterDirection.InputOutput
},
}, sp);
foreach (DataRow dr in ds.Tables["OUTPARAM"].Rows)
{
jsonStr += Convert.ToString('"') + dr["KEY"].ToString() + Convert.ToString('"') + ":" + Convert.ToString('"') + (dr["VALUE"].ToString().Replace(@"""", @"\""")) + Convert.ToString('"');
if (i < ds.Tables["OUTPARAM"].Rows.Count)
{
jsonStr += ",";
}
else
{
jsonStr += "}";
}
i++;
}
var json = Newtonsoft.Json.JsonConvert.SerializeObject(ob);
return("value " + jsonStr + json);
}
