84035

How to read certain data from mysql results in C#

Question:

I want to make certain query to mysql from C# so I can get back for example name and lastname.

I don't know how to handle this in c# and I want to minimize sqlinjection.

Here is my code:

con = new MySqlConnection("server=localhost;database=Customers;uid=root;pwd=******"); con.Open(); cmd = new MySqlCommand("SELECT name,lastname FROM customer WHERE ID_customer= ?Parname;", con); cmd.Parameters.Add("?Parname", MySqlDbType.Float).Value = customer_card; cmd.ExecuteNonQuery(); con.Close();

I want to be able for example to read results and it will be only one result, in the 2 string variables. How to get trough the data and save it?

Thank you

Answer1:

A cheating but quick way of getting several strings out of a query returning one row would be as follows:

con = new MySqlConnection("server=localhost;database=Customers;uid=root;pwd=******"); try { con.Open(); cmd = new MySqlCommand("SELECT concat(name,'|',lastname) FROM customer WHERE ID_customer= ?Parname;", con); cmd.Parameters.Add("?Parname", MySqlDbType.Float).Value = customer_card; // Are you sure that the ID is float? That's the first time I see anything like that! var tokens = ((String)cmd.ExecuteScalar()).Split('|'); var firstName = tokens[0]; var lastName = tokens[1]; Console.Writeln("First={0}, Last={1}", firstName, lastName); } finally { con.Close(); }

The idea is to combine both strings into one on the MySql side, and split them back on the C# side. This trick lets you avoid looping through a reader, closing it, and so on. It assumes that the names do not contain '|' characters.

Answer2:

You need a DataReader to iterate trough the records, instead of using cmd.ExecuteNonQuery(); use cmd.ExecuteReader();

try { MySqlDataReader myReader = cmd.ExecuteReader(); // Always call Read before accessing data. while (myReader.Read()) { //This will get the value of the column "name" Console.WriteLine(myReader.GetString(myReader.GetOrdinal("name"))); } // always call Close when done reading. myReader.Close(); // Close the connection when done with it. } finally { con.Close(); }

Recommend

  • Django 1.8 Migrations - “NoneType” object has no attribute “_meta”
  • How to close a JFrame and open it consequently, without exit the JVM/Application?
  • Using prepared statement in Groovy
  • No routes matches “missing required keys: [:id, :_id]”
  • 404 on http://localhost/wp-json/wp/v2/posts in wordpress using REST API V2
  • Report of oozie jobs
  • Increment counter with Cloud Functions for Firebase
  • Unable to create managed bean UserBean - JSF
  • Guava: How does Cacheloader.load() work
  • compile error in recursively go down folder structure and to delete specific files
  • Why can't I use CapturePhotoToStreamAsync from a System.Threading.Timer call back in WPF?
  • Creating an alias for emacs in read-only mode on Linux in my .tcshrc file
  • How to calculate the shift parameters of glTranslatef function in opengl
  • Load external JSON file
  • extract matched line from text file
  • Excel VBA connecting to remote MySQL Database
  • Confluent kafka connect elasticsearch document ID creation
  • How to encrypt and decrypt files with bouncy castle (DES) in netbeans java?
  • Javascript matrix inversion
  • How to retrieve data from Firebase Database with join if key only return true?
  • Open default mail app from within Qt with some html
  • How to use CoreFoundation in QuickTime SDK for Windows?
  • In metro, get all inherited classes of an (abstract) class?
  • Bundling python(“.py”)files along with java class files for a web application
  • Signed Java web start application with Glassfish 4.1 and Java7
  • Showing image on a acro text field position
  • Extracting a small subset of data from XMLs
  • How to control xtics in gnuplot
  • Splitting ReportLab table across PDF page (side by side)?
  • How to change the host IP sent in emails to new GitLab users to a publicly visible IP, not the local
  • Add checkbox dynamically using angular 2
  • How to use array in autohotkey?
  • How to include associated objects using gon in Rails/jQuery
  • How can I ssh into a server that requires 2 password authentication using python's paramiko mod
  • Stacked bar chart with continuous time-axis as x-axis
  • Comma decimal separator is ignored by ASP.NET MVC model binder
  • Background transfer download task failed when app was closed
  • XEP-0166: Jingle protocol implementation for voice/video chat in iOS
  • Firebase: How to read from external DB?
  • Running R's aov() mixed effects model from Python using rpy2