Showing posts with label Database. Show all posts
Showing posts with label Database. Show all posts

Saturday, 13 March 2021

How to extend MySQL query in C# application

 In C# application, when select a pretty large table using MySQL library, you may get an exception "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding". This is simply because the select operation take more than 30s, then it will give a timeout exception. 30s is the default setting for MySqlCommand.

To extend the query command is very simple: just set the CommandTimeout property in MySqlCommand to a larger value. Below is the complete code for a query session where you can see that the timeout for query command is extended to the maximum of integer, and then load data into a DataTable object.


string conStr = "Database=10.10.10.10;Data Source=testdb;User Id=testuser;Password=testpw";
MySqlConnection connection = new MySqlConnection(conStr);
connection.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM my_table", connection);
cmd.CommandTimeout = int.MaxValue; //Here execution timeout is set to maximum
DataTable table = new DataTable();
table.load(cmd.ExecuteReader());
connection.Close();





Thursday, 2 January 2020

Common useful SQL commands in MySQL

Safe Update Mode:

If Safe Update Mode is enabled in MySQL, then you may encounter an error "You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column." when you use UPDATE or DELETE statements without a WHERE keyword (Sometimes my statements contain WHERE, but still got this error, does anyone know why?). When you see this error, you can solve it by turning off safe update mode. There are two ways to do so:
  1. You can put "SET SQL_SAFE_UPDATES=0;" in the beginning of your scripts;
  2. You can turn off safe update mode from workbench, by doing so, you don't need to put "SET SQL_SAFE_UPDATES=0;" in your scripts every time. Edit -> Preferences -> SQL Editor. Scroll down, you will find" Safe Updates(rejects UPDATEs and DELETEs with no restrictions)" checkbox. Uncheck the checkbox, you won't have the troubles anymore.


Delete table from database:

DELETE FROM Database.Table;


Find rows with duplicated keys:

SELECT
    Name,                                                     -- 
    COUNT(Name)                                      -- Here give the count of duplicate number
FROM
    Database.Table
GROUP BY Name                                     -- Group rows with HAVING condition
HAVING COUNT(Name) > 1;

Dump table from database:

mysqldump -u usernane -p password database_name table1 table2 table3 > tables.sql




Chapter 3 — The Molt

(This story is written by me and I will be posting it on Royal Road. Profile: https://www.royalroad.com/profile/1129550/fictions) Six fifty ...