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

Using System

Using System.Collections.Generic

Using System.Linq

Using System.Text

Using MySql.Data.MySqlClient

Using System.Data

Namespace www.xinduofen.com

{

Class MySqlOperateTool

{

Private const string serverIP = "localhost"; / / IP address of the mysql server

Private const string serverPort = "3306"; / / Port of the mysql server

Private const string userName = "LGQ"; / / user name of the mysql server

Private const string userPassword = "lgq"; / / user password of the mysql server

/ / /

/ / Test method of MySqlOperateTool tool class (for reference by people who use MySqlOperateTool tool class)

/ / /

Public static void mySqlOperateToolTest () {

MySqlConnection dbConnection = MySqlOperateTool.getConnection ("test")

If (dbConnection! = null)

{

/ / create a transaction

MySqlTransaction transaction = MySqlOperateTool.beginTransaction (dbConnection)

Try

{

String sql = "insert into testTable values (1001m 'Zhang San', 'male')"

MySqlOperateTool.executeNonQuery (sql, dbConnection)

Sql = "insert into testTable values (1002 ~ Li Si', 'female')"

MySqlOperateTool.executeNonQuery (sql, dbConnection)

/ simulated exception

/ / int aa = 0

/ / int bb = 10 / aa

/ / commit transaction

MySqlOperateTool.transactionCommit (transaction)

}

Catch (Exception)

{

/ / rollback the transaction

MySqlOperateTool.transactionRollback (transaction)

}

MySqlOperateTool.closeConnection (dbConnection)

}

/ / MySqlConnection dbConnection = MySqlOperateTool.getConnection ("test")

/ / if (dbConnection! = null)

/ / {

/ / string sql = "select count (id) from testTable"

/ / object resultSetCnt = MySqlOperateTool.getSingleObject (sql, dbConnection)

/ / if (resultSetCnt! = null)

/ / {

/ / System.Console.WriteLine ("resultSetCnt:" + (long) resultSetCnt)

/ /}

/ / sql = "select * from testTable"

/ / MySqlDataReader dataReader = MySqlOperateTool.getDataReader (sql, dbConnection)

/ / if (dataReaderinvalid null)

/ / {

/ / while (dataReader.Read ())

/ / {

/ / int id = (int) dataReader ["id"]

/ / string name = (string) dataReader ["name"]

/ / string sex = (string) dataReader ["sex"]

/ / System.Console.WriteLine ("id:" + id+ "name:" + name+ "sex:" + sex)

/ /}

/ /}

/ / MySqlOperateTool.closeConnection (dbConnection)

/ /}

/ / MySqlConnection dbConnection = MySqlOperateTool.getConnection ("test")

/ / if (dbConnection! = null)

/ / {

/ / DataSet dataset = new DataSet (); / / define a DataSet

/ / string sql = "select * from testTable"

/ / bool resultFlag = MySqlOperateTool.dataAdappterDataSet (sql,dataset, "persons", dbConnection)

/ / if (resultFlag)

/ / {

/ / foreach (DataRow therow in dataset.Tables ["persons"] .Rows)

/ / {

/ / int id = (int) therow ["id"]

/ / string name = (string) therow ["name"]

/ / string sex = (string) therow ["sex"]

/ / System.Console.WriteLine ("id:" + id + "name:" + name + "sex:" + sex)

/ /}

/ /}

/ / MySqlOperateTool.closeConnection (dbConnection)

/ /}

/ / MySqlConnection dbConnection = MySqlOperateTool.getConnection ("test")

/ / if (dbConnection! = null)

/ / {

/ / DataSet dataset = new DataSet (); / / define a DataSet

/ / string sql = "select * from testTable"

/ get an empty class object for update

/ / MySqlCommandBuilder commandBuilder = MySqlOperateTool.dataAdappterDataSetUpdate (sql, dataset, "persons", dbConnection)

/ / if (commandBuilderbuilder commands null)

/ / {

/ update the contents of the database (assuming there are more than two lines of personnel information in the database)

/ / dataset.Tables ["persons"] .Rows [0] ["name"] = "Tianji Xiao Hong"

/ / dataset.Tables ["persons"] .Rows [0] ["sex"] = "female"

/ / dataset.Tables ["persons"] .Rows [1] ["name"] = "Xiao Li split knife"

/ / dataset.Tables ["persons"] .Rows [1] ["sex"] = "male"

/ / commandBuilder.DataAdapter.Update (dataset, "persons")

/ /}

/ / MySqlOperateTool.closeConnection (dbConnection)

/ /}

/ / MySqlConnection dbConnection = MySqlOperateTool.getConnection ("test")

/ / if (dbConnection! = null)

/ / {

/ / DataSet dataset = new DataSet (); / / define a DataSet

/ / string sql = "select * from testTable"

/ get an empty class object for update

/ / MySqlCommandBuilder commandBuilder = MySqlOperateTool.dataAdappterDataSetUpdate (sql, dataset, "persons", dbConnection)

/ / if (commandBuilder! = null)

/ / {

/ add content to the database

/ / DataRow datarow = dataset.Tables ["persons"] .NewRow (); / / define a new row of data with the fields of this table

/ / datarow ["id"] = 1003

/ / datarow ["name"] = "Zhang San"

/ / datarow ["sex"] = "male"

/ / dataset.Tables ["persons"] .Rows.Add (datarow)

/ / datarow = dataset.Tables ["persons"] .NewRow (); / / define a new row of data with the fields of this table

/ / datarow ["id"] = 1004

/ / datarow ["name"] = "Li Si"

/ / datarow ["sex"] = "female"

/ / dataset.Tables ["persons"] .Rows.Add (datarow)

/ / commandBuilder.DataAdapter.Update (dataset, "persons")

/ /}

/ / MySqlOperateTool.closeConnection (dbConnection)

/ /}

/ / MySqlConnection dbConnection = MySqlOperateTool.getConnection ("test")

/ / if (dbConnection! = null)

/ / {

/ / DataSet dataset = new DataSet (); / / define a DataSet

/ / string sql = "select * from testTable"

/ get an empty class object for update

/ / MySqlCommandBuilder commandBuilder = MySqlOperateTool.dataAdappterDataSetUpdate (sql, dataset, "persons", dbConnection)

/ / if (commandBuilder! = null)

/ / {

/ / delete content from the database

/ / DataRow datarow = dataset.Tables ["persons"] .Rows [2]

/ / datarow.Delete ()

/ / datarow = dataset.Tables ["persons"] .Rows [3]

/ / datarow.Delete ()

/ / commandBuilder.DataAdapter.Update (dataset, "persons")

/ /}

/ / MySqlOperateTool.closeConnection (dbConnection)

/ /}

}

/ / /

/ / get the database connection object

/ / /

/ / the database name passed in

/ / the returned value is null, which means failed to get the connection

Public static MySqlConnection getConnection (string dbName)

{

MySqlConnection dbConnection = null

If (string.IsNullOrEmpty (dbName)) / / if the passed parameter is abnormal

{

Return null

}

String connectStr = "Data Source=" + serverIP + "; Port=" + serverPort + "; User ID=" + userName + "; Password=" + userPassword + "; Database=" + dbName + "; CharSet=utf8;"

Try {

MySqlConnection connection = new MySqlConnection (connectStr)

Connection.Open ()

/ / the mysql database connection object was obtained successfully

DbConnection = connection

} catch (Exception) {

System.Console.WriteLine ("an exception occurred when C# got the mysql database connection object!")

}

Return dbConnection

}

/ / /

/ / create a transaction and start executing the transaction

/ / /

/ / Database connection object

/ / return failed to create transaction for null representative

Public static MySqlTransaction beginTransaction (MySqlConnection dbConnection)

{

MySqlTransaction transaction = null

If (dbConnection = = null)

{

Return null

}

Try

{

Transaction = dbConnection.BeginTransaction (); / / create the transaction and start executing the transaction

}

Catch (Exception) {

Console.WriteLine ("an exception occurred while creating the transaction!")

}

Return transaction

}

/ / /

/ / commit transaction

/ / /

/ / objects related to a transaction

Public static void transactionCommit (MySqlTransaction transaction)

{

Try

{

/ / commit transaction

Transaction.Commit ()

}

Catch (Exception)

{

Console.WriteLine ("exception occurred while committing transaction!")

}

}

/ / /

/ rollback the transaction

/ / /

/ / objects related to a transaction

Public static void transactionRollback (MySqlTransaction transaction)

{

Try

{

/ / rollback the transaction

Transaction.Rollback ()

}

Catch (Exception)

{

Console.WriteLine ("exception occurred while rolling back the transaction!")

}

}

/ / /

/ close the connection to the database

/ / /

/ / Database connection object

Public static void closeConnection (MySqlConnection dbConnection)

{

/ / if the connection is not empty

If (dbConnection! = null)

{

Try {

DbConnection.Close ()

} catch (Exception) {

System.Console.WriteLine ("an exception occurred when C# closed the mysql database connection object!")

}

}

}

/ / /

/ / query individual information

/ / /

/ sql query statement

/ / Database connection object

/ / /

/ / if the returned value is null, the query failed

/ / this method can only return one value, which is mainly used to check the number of rows, and other purposes (none of the parameters passed in can be empty)

/ / returns data of type Object, executes the query, and returns the first column of the first row in the result set returned by the query. Ignore other columns or rows

/ / /

Public static Object getSingleObject (string sql, MySqlConnection dbConnection)

{

Object result = null

If (string.IsNullOrEmpty (sql) | | dbConnection = = null) / / if the passed parameter is invalid

{

Return null

}

Try

{

MySqlCommand command = new MySqlCommand (sql, dbConnection); / / create database command statement object

Result = command.ExecuteScalar (); / / this command returns only one value

}

Catch (Exception) / / if the sql command fails

{

Console.WriteLine ("illegal sql query statement or abnormal database connection object!")

}

Return result;// returns the value read

}

/ / /

/ / execute sql statement

/ / /

/ sql execute statement

/ / Database connection object

/ / /

/ / A return value of 0 means that the sql command has no effect on the database after execution. If the return value is greater than 0, it means that the database is affected after the sql command is executed.

/ / this method can only return one value (the number of rows affected by the instruction in the database). It is mainly used to perform insert, delete and alter operations.

/ / /

Public static int executeNonQuery (string sql, MySqlConnection dbConnection)

{

/ / the number of rows affected after the execution of the sql statement, initialized to 0

Int resultCnt = 0

If (string.IsNullOrEmpty (sql) | | dbConnection = = null) / / if the passed parameter is invalid

{

Return 0

}

Try

{

MySqlCommand command = dbConnection.CreateCommand (); / / create database command statement object

Command.CommandText = sql;// executes related sql statements

ResultCnt = command.ExecuteNonQuery (); / / this command has no query function

}

Catch (Exception) / / if the sql command fails

{

Console.WriteLine ("illegal sql query statement or abnormal database connection object!")

}

Return resultCnt;// returns the number of rows affected

}

/ / /

/ / query multiple lines of information

/ / /

/ sql query statement

/ / Database connection object

/ / /

/ / returned as null, indicating that the query failed and returned data of type MySqlDataReader, which is mainly used to retrieve multiple rows of values.

/ / this method can only return data of type MySqlDataReader and can only read data one by one.

/ / /

Public static MySqlDataReader getDataReader (string sql, MySqlConnection dbConnection)

{

MySqlDataReader data_read = null

If (string.IsNullOrEmpty (sql) | | dbConnection = = null) / / if the passed parameter is invalid

{

Return null

}

Try

{

MySqlCommand command = dbConnection.CreateCommand (); / / create database command statement object

Command.CommandText = sql;// executes related sql statements

Data_read = command.ExecuteReader (); / / read data in the database

}

Catch (Exception) / / if the sql command fails

{

Console.WriteLine ("illegal sql query statement or abnormal database connection object!")

}

Return data_read;// returns the data read.

}

/ / /

/ / this method fills data into the incoming dataset and "updates are not supported"

/ / /

/ sql query statement

/ / the dataset cache used to store query information

/ is the name of the DataTble that dataset wants to cache query information

/ / Database connection object

/ / true is returned for a successful query, and false is returned for a failed query.

Public static bool dataAdappterDataSet (string sql, DataSet dataset, string table_name, MySqlConnection dbConnection)

{

/ / failed to initialize query

Bool result = false

If (string.IsNullOrEmpty (sql) | | dataset = = null | | string.IsNullOrEmpty (table_name) | | dbConnection==null)

{

Return false

}

Try

{

/ / define an adapter object that matches data from the database to DataSet

MySqlDataAdapter data_adappter = new MySqlDataAdapter (sql, dbConnection)

Data_adappter.Fill (dataset, table_name)

Result = true;// query succeeded

}

Catch (Exception) / / if the sql command fails

{

Console.WriteLine ("illegal sql query statement or abnormal database connection object!")

}

Return result;// returns the query result

}

/ / /

/ / this method fills in data into the incoming dataset, "supports update function", and cannot disconnect from the database before performing the update.

/ / /

/ sql query statement

/ / the dataset cache used to store query information

/ is the name of the DataTble that dataset wants to cache query information

/ / Database connection object

/ / return a MySqlCommandBuilder object that can be used for update operations to synchronize the changed data in the memory dataset to the database

Public static MySqlCommandBuilder dataAdappterDataSetUpdate (string sql, DataSet dataset, string table_name, MySqlConnection dbConnection)

{

MySqlCommandBuilder commandBuilder = null

If (string.IsNullOrEmpty (sql) | | dataset = = null | | string.IsNullOrEmpty (table_name) | | dbConnection = = null)

{

Return null

}

Try

{

/ / define an adapter object that matches data from the database to DataSet

MySqlDataAdapter data_adappter = new MySqlDataAdapter (sql, dbConnection)

MySqlCommandBuilder builder = new MySqlCommandBuilder (data_adappter)

Data_adappter.Fill (dataset, table_name)

CommandBuilder = builder;// query succeeded

}

Catch (Exception) / / if the sql command fails

{

Console.WriteLine ("illegal sql query statement or abnormal database connection object!")

}

Return commandBuilder;// query ends and commandBuilder is returned

}

}

}

Content from: Yuekang Sports

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.