c# 创建mysql存储过程_C#创建mysql存储过程

using Dapper;

using LabCenterDataAcquisitionSystem.Util;

using MySql.Data.MySqlClient;

using System;

using System.Collections.Generic;

namespace LabCenterDataAcquisitionSystem.Mysql

{

///

///

///

public class AddDeviceTrans

{

public static bool AddDeviceViaTrans(Util.Device device, List controllerList, List collectorList)

{

#region 存储过程操作

using (MySqlConnection connection = new MySqlConnection(Db.mysqlConnString))

{

connection.Open();

var trans = connection.BeginTransaction();

//Device

string sql0 = $"insert into device (device_no,device_name,device_type) values ( '{device.deviceName}','{device.deviceGuid}',{(int)device.deviceType} ) ";

var result0 = connection.Execute(sql0);

if(result0 <= 0)

{

trans.Rollback();

return false;

}

//Controlelr

var result1 = true;

for(var i = 0; i < controllerList.Count; i ++ )

{

var c = controllerList[i];

string sql1 = $"insert into controller (name,ip,port,status,create_time) values ( '{c.exControllerName}','{c.exControllerName}',{c.exControllerPort},0,{Tools.ConvertDateTimeToInt(DateTime.Now)} ) ";

result1 = result1 && connection.Execute(sql1) > 0;

}

if (!result1)

{

trans.Rollback();

return false;

}

//Collector

var result2 = true;

for (var i = 0; i < collectorList.Count; i++)

{

var c = collectorList[i];

string sql2 = $"insert into collector (name,ip,port,status,create_time) values ( '{c.exCollectorName}','{c.exCollectorIp}',{c.exCollectorPort},0,{Tools.ConvertDateTimeToInt(DateTime.Now)} ) ";

result2 = result2 && connection.Execute(sql2) > 0;

}

if (!result2)

{

trans.Rollback();

return false;

}

trans.Commit();

connection.Close();

return true;

}

#endregion

}

}

}