c# mysql数据源_C# 使用MySql数据库（带事务）

这里分享一些C#中使用MySql的例子

依赖库

可以使用NuGet一键安装，搜索MySql.Data

注意 .NET4.0 支持版本是：v6.9.12

添加需要增加以下命名空间

using MySql.Data.MySqlClient;

连接字符串

private string connString = "data source=192.168.6.161;database=tts;user id=swack;password=xxx;pooling=false;charset=utf8";

读取数据值

using (MySqlConnection connection = new MySqlConnection(connString))

{

MySqlCommand command = new MySqlCommand();

// Set the Connection to the new MySqlConnection.

command.Connection = connection;

// Open the connection.

try

{

connection.Open();

command.Connection = connection;

// Execute the commands.

command.CommandText =

"select nsGetdatabyhand from tts";

MySqlDataReader reader = command.ExecuteReader();

int Savetime = Environment.TickCount;

if (reader.HasRows)

{

reader.Read();

Debug.WriteLine(reader[0].ToString());

}

reader.Close();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

// The connection is automatically closed when the

// code exits the using block.

}

读取数据行

using (MySqlConnection connection = new MySqlConnection(connString))

{

TTSData006 nData = new TTSData006();

MySqlCommand command = new MySqlCommand();

// Set the Connection to the new MySqlConnection.

command.Connection = connection;

// Open the connection.

try

{

connection.Open();

command.Connection = connection;

// Execute the commands.

command.CommandText =

"select * from tts";

MySqlDataReader reader = command.ExecuteReader();

if (reader.HasRows)

{

reader.Read();

nData.nsGetdatabyhand = Convert.ToInt16(reader.GetProviderSpecificValue(1));

nData.nsUploadinterval = Convert.ToInt16(reader.GetProviderSpecificValue(2));

nData.nsUploadbyspan = Convert.ToInt16(reader.GetProviderSpecificValue(3));

nData.nsUploadbyfloorlevel = Convert.ToInt16(reader.GetProviderSpecificValue(4));

nData.nsMeasurerange = Convert.ToInt16(reader.GetProviderSpecificValue(5));

nData.nInterval = Convert.ToInt32(reader.GetProviderSpecificValue(6));

nData.nSpan = Convert.ToInt32(reader.GetProviderSpecificValue(7));

nData.nFloorlevel = Convert.ToInt32(reader.GetProviderSpecificValue(8));

nData.nMeasurerange = Convert.ToInt32(reader.GetProviderSpecificValue(9));

nData.nChannel = Convert.ToString(reader.GetProviderSpecificValue(10));

nData.nInnercode = Convert.ToString(reader.GetProviderSpecificValue(11));

nData.nTtsid = Convert.ToString(reader.GetProviderSpecificValue(12));

}

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

}

插入数据

using (MySqlConnection connection = new MySqlConnection(connString))

{

MySqlCommand command = new MySqlCommand();

MySqlTransaction transaction = null;

// Set the Connection to the new MySqlConnection.

command.Connection = connection;

// Open the connection and execute the transaction.

try

{

connection.Open();

// Start a local transaction

transaction = connection.BeginTransaction();

// Assign transaction object for a pending local transaction.

command.Transaction = transaction;

// Execute the commands.

command.CommandText =

"insert into i_realtime_data(tts_id,inner_code,C2,getdatetimestamp,sendtimestamp)values('" + nRec.ID + "','1'," + nRec.Data + ",TO_DATE('" + nRec.Getdatetimestamp + "','YYYY-MM-DD HH24:MI:SS'),TO_DATE('" + nRec.Sendtimestamp + "','YYYY-MM-DD HH24:MI:SS'))";

command.ExecuteNonQuery();

command.CommandText =

"update tts set nsGetdatabyhand=" + Convert.ToString(nbyStatus);

command.ExecuteNonQuery();

// Commit the transaction.

transaction.Commit();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

try

{

// Attempt to roll back the transaction.

transaction.Rollback();

}

catch

{

// Do nothing here; transaction is not active.

}

}

// The connection is automatically closed when the

// code exits the using block.

}

更新数据

using (MySqlConnection connection = new MySqlConnection(connString))

{

MySqlCommand command = new MySqlCommand();

MySqlTransaction transaction = null;

// Set the Connection to the new MySqlConnection.

command.Connection = connection;

// Open the connection and execute the transaction.

try

{

connection.Open();

// Start a local transaction

transaction = connection.BeginTransaction();

// Assign transaction object for a pending local transaction.

command.Transaction = transaction;

// Execute the commands.

command.CommandText =

"update tts set nttsid='" + tts_id + "',ninnercode='" + inner_code + "',nchannel='" + channel + "',nsGetdatabyhand=5";

command.ExecuteNonQuery();

// Commit the transaction.

transaction.Commit();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

try

{

// Attempt to roll back the transaction.

transaction.Rollback();

}

catch

{

// Do nothing here; transaction is not active.

}

}

// The connection is automatically closed when the

// code exits the using block.

}