9/25/2023 0 Comments Ef psql commands![]() ![]() Existing MSSQL queries wont work out of the box. So we need to escape the quotes and write the query as “SELECT * FROM \”MyTable\”” or * FROM “”MyTable”””, which will make our query work, but is a very frustrating way to code or rewrite queries. So then if we want to actually make this query work and have the ability to use Pascal case naming (such as MyTable), we need to use double quotes in our SQL query string.īut then arises the problem of C# not allowing these quotes as it breaks the string. Therefore again your example query above fails. Since there is no lowercase mytable in the database your query will return an error that MyTable does not exist.Įven if we omit the double quotes when creating the MyTable (not use pgAdmin or Navicat or write the table creation script our self without quotes), MyTable will actually be created as mytable. However, your query example does not use the double quotes around MyTable, which means it will be processed by Postgres as lowercase. then MyTable is created with double quotes “MyTable” which makes it case sensitive. If they were created using the default of most database tools, example pgAdmin4, Navicat etc. One big gotcha with using Postgres, that needs to be said.īased on your example code, how was your MyTable and it’s columns created? Then again, our normal SQL Connection looks like so : using (var connection = new SqlConnection("Server=myServerAddress Database=myDataBase User Id=myUsername Password=myPassword "))Īnd our Postgres connection instead looks like so using the NpgsqlConnection class : using (var connection = new NpgsqlConnection("User ID=root Password=myPassword Host=localhost Port=5432 Database=myDataBase ")) You can probably guess how Postgres is going to go.įirst install the following nuget package : Using LIMIT in MySQL instead of TOP in MSSQL), but the actual act of talking to the database is all taken care for you and you literally don’t have to do anything else. With MySQL you would do essentially the same thing but instead you use the MySQLConnection class : using (var connection = new MySqlConnection("Server=myServerAddress Database=myDataBase Uid=myUsername Pwd=myPassword "))Īnd that’s pretty much it! Obviously the syntax for various queries may change (e.g. Normally when creating a SQL Connection you would do something like so : using (var connection = new SqlConnection("Server=myServerAddress Database=myDataBase User Id=myUsername Password=myPassword "))Ĭonnection.Query("SELECT * FROM MyTable") NET Core, you have to install the following nuget package : Install-Package MySql.Data In our case, that flexibility is handy when having to write slightly different commands across different types of SQL Databases, because Dapper itself doesn’t have to translate your LINQ to actual queries, instead that’s on you! MySQL With Dapper You have to write the queries yourself, so no Linq2SQL, but with that comes amazing control and flexibility. ![]() Just understand that Dapper is a lightweight ORM that handles querying a database and turning the rows into your plain objects with very minimal fuss and overhead. ![]() It covers a lot of the why and where we might use Dapper, including writing your first few queries with it. If you’ve never used Dapper before, I highly recommend this previous write up on getting started with Dapper. This is going to be a really short and sweet post because there really isn’t a lot to it! That also seems to get asked a lot, “OK so this can talk to MySQL but can it talk to MySQL from Linux”… errr… yes, yes it can! NET Core apps running inside Linux (Containers and VM’s) with absolutely no issue. I would also add that for both MySQL and Postgres, I’ve had. But in my experience, when using something like Dapper that allows you to really control the queries you are running, it really doesn’t make a heck of a lot of difference between any SQL Server. Maybe if you’re overlaying something like EF Core or a very heavy ORM you might have issues. NET Core’s point of view is to run simple commands. The amount of time I’ve spent on calls trying to explain it really doesn’t matter for the most part which tech choice they go with if all they are expecting from. ![]() NET Core to talk to anything non-Microsoft. The thing that gets me is often these companies are so wary about the ability for. NET Core, but don’t want to use Microsoft SQL Server – so they either want to use PostgreSQL or MYSQL. I contract/freelance out a lot to companies that are dipping their toes into. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |