Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

The method of C # connecting to Mysql Database

2025-01-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

How to connect to Mysql database in C #? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

This article is about C # connecting to Mysql database. A detailed case is attached below. MySqlConnection will return a MySqlException when there is a connection error.

It includes two variables: Message and Number.

Download mysql-connector-net-8.0.12 and install it, and add Mysql.Data to the reference.

The sentence using MySql.Data.MySqlClient; should be written down. As shown in the figure

Established on the premise that the MySQL database has been installed, it is installed in C:\ Program Files (x86)\ MySQL by default. It is recommended to select the installation of Connector.NET 8.0.12, which contains dynamic link libraries connected to MySQL and C#.

The help document C:\ Program Files (x86)\ MySQL\ Connector.NET 8.0.12\ Documentation\ ConnectorNET.chm is my main basis for writing this article. Under Users Guide, Programming is the introduction of 8 classes of dynamic link library, and Tutorial is the case code.

The essence of connecting and operating the database is to use the dynamic link library MySql.Data.dll provided by the database to operate. MySql.Data.dll provides the following eight classes:

MySqlConnection: connect to the MySQL server database.

MySqlCommand: executes a sql statement.

MySqlDataReader: contains the result of the execution of the sql statement and provides a way to read a line from the result.

MySqlTransaction: represents a SQL transaction in an MySQL database.

MySqlException: the Exception returned when MySQL reports an error.

MySqlCommandBuilder: Automatically generates single-table commands used to reconcile changes made to a DataSet with the associated MySQL database.

MySqlDataAdapter: Represents a set of data commands and a database connection that are used to fill a data set and update a MySQL database.

MySqlHelper: Helper class that makes it easier to work with the provider.

1. Add a dynamic link library file

Method 1: Visual Studio, in the project (right)-manage the NuGet package (N) and then search for MySql.Data in the browser and install it.

Method 2: select the installation of Connector.NET 6.9 when installing the database MySQL, and add the MySql.Data.dll in v4.0 or v4.5 in C:\ Program Files (x86)\ MySQL\ Connector.NET 8.0.12\ Assemblies to the reference of the project. V4.0 and v4.5, corresponding to the version number of the. NET Framework in the Visual Studio specific project properties-application-target framework.

two。 Establish a connection (MySqlConnection class) = = 3. Catch exceptions (MySqlException class)

When a connection error occurs, MySqlConnection returns a MySqlException, which includes two variables:

Message: A message that describes the current exception.

Number: The MySQL error number. (0: Cannot connect to server. 1045: Invalid user name and/or password.)

Catch (MySqlException ex) {switch (ex.Number) {case 0: Console.WriteLine ("Cannot connect to server. Contact administrator"); break; case 1045: Console.WriteLine ("Invalid username/password, please try again"); break;}} 4. Add, delete, query and modify the code (MySqlCommand class, MySqlDataReader class)

ExecuteReader-- is used to query the database. The result of the query returns a MySqlDataReader object, MySqlDataReader contains the result of the execution of the sql statement, and provides a way to read a row from the result.

ExecuteNonQuery-- is used to insert, update, and delete data.

When ExecuteScalar-- is used to query data, it returns the value of the first column of the first row in the query result set, that is, only one value is returned.

(1) Enquiry

a. Query conditions are fixed

String sql= "select * from user"; MySqlCommand cmd = new MySqlCommand (sql,conn); MySqlDataReader reader = cmd.ExecuteReader (); / / execute ExecuteReader () to return a MySqlDataReader object while (reader.Read ()) / / the initial index is-1. If you read the next row of data, the return value is bool {/ / Console.WriteLine (reader [0] .ToString () + reader [1] .ToString () + reader [2] .ToString ()). / / Console.WriteLine (reader.GetInt32 (0) + reader.GetString (1) + reader.GetString (2)); Console.WriteLine (reader.GetInt32 ("userid") + reader.GetString ("username") + reader.GetString ("password")); / / "userid" is the corresponding column name of the database. This method is recommended.

b. The query condition is not fixed.

/ / string sql = "select * from user where username='" + username+ "'and password='" + password+ "'"; / / We assemble string sql = "select * from user where username=@para1 and password=@para2" according to the query conditions; / / define parameter in the sql statement, and then assign parameter the value MySqlCommand cmd = new MySqlCommand (sql, conn); cmd.Parameters.AddWithValue ("para1", username); cmd.Parameters.AddWithValue ("para2", password); MySqlDataReader reader = cmd.ExecuteReader () If (reader.Read ()) / / if the user name and password are correct, you can query a statement that reads the next line and returns true {return true;}.

c. A query is required to return a value

String sql = "select count (*) from user"; MySqlCommand cmd = new MySqlCommand (sql, conn); Object result=cmd.ExecuteScalar (); / / executes the query and returns the first column of the first row in the query result set. All other columns and rows will be ignored. When the select statement returns no record, ExecuteScalar () returns the null value if (result! = null) {int count = int.Parse (result.ToString ());}

(2) insert, delete, change

String sql = "insert into user (username,password,registerdate) values ('Ah Kuan', '123 DateTime.Now+"); / / string sql = "delete from user where userid='9'"; / / string sql = "update user set username=' AHA', password='123' where userid='8'"; MySqlCommand cmd = new MySqlCommand (sql,conn); int result = cmd.ExecuteNonQuery (); / / 3. Execute insert, delete, change statements. Successful execution returns the number of rows of affected data, and returns 1 to make a true judgment. Execution failure does not return any data, error, the following code does not execute 5. Transaction (MySqlTransaction class) String connetStr = "server=127.0.0.1;user=root;password=root;database=minecraftdb;"; MySqlConnection conn = new MySqlConnection (connetStr); conn.Open (); / / you must open the channel before you can start the transaction MySqlTransaction transaction = conn.BeginTransaction (); / / the transaction must be assigned outside the try or the transaction in the catch will report an error: unassigned Console.WriteLine ("connection established") Try {string date = DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day; string sql1= "insert into user (username,password,registerdate) values ('Ah Kuan', '123 MySqlCommand cmd1 sql1,conn'" + date + "')"; MySqlCommand cmd1 = new MySqlCommand (sql1,conn); cmd1.ExecuteNonQuery (); string sql2 = "insert into user (username,password,registerdate) values ('Ah Kuan', '123 Kuan Leng'" + date + ")" MySqlCommand cmd2 = new MySqlCommand (sql2, conn); cmd2.ExecuteNonQuery ();} catch (MySqlException ex) {Console.WriteLine (ex.Message); transaction.Rollback (); / / transaction ExecuteNonQuery () failed to report error, username is set to unique conn.Close ();} finally {if (conn.State! = ConnectionState.Closed) {transaction.Commit () / / transactions are either rolled back or committed, that is, Rollback () and Commit () can only execute one conn.Close ();}} Thank you for reading! After reading the above, do you have a general idea of how C # connects to Mysql databases? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report