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); }