C#--Access一次插入多条数据
网上搜索的方法：
参考：https://www.cnblogs.com/FLWL/p/3900791.html
OleDbConnection inconn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=DB.mdb");
inconn.Open();
OleDbTransaction myTrans = inconn.BeginTransaction();
OleDbCommand incmd = inconn.CreateCommand();
incmd.Transaction = myTrans;
string time = "软件";
string sqlstr = "insert into Faultlibrarytable (EquipmentTableID,FaultNumber,LineOn,EquipmentName) Values({0},{1},'{2}','{3}')";
for (int i = 0; i < 32; i++)
{
incmd.CommandText = string.Format(sqlstr,i, i, time, time);
incmd.ExecuteNonQuery();
}
myTrans.Commit();
1，Access帮助类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
namespace DAL.Helper
{
public class Access
{
public OleDbConnection Conn;
public string ConnString;
/// <summary>
/// 连接数据库的方法
/// </summary>
public void AccessDbClass()
{
this.ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;";
this.ConnString = this.ConnString + "Data Source=toolsdata.accdb;Persist Security Info=False;Jet OLEDB:Database Password=atlas";
this.Conn = new OleDbConnection(this.ConnString);
this.Conn.Open();
}
public void Close()
{
this.Conn.Close();
}
public OleDbConnection DbConn()
{
this.Conn.Open();
return this.Conn;
}
/// <summary>
/// 判断能否执行SQL语句
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public bool ExecuteSQLNonquery(string SQL)
{
this.AccessDbClass();
OleDbCommand command = new OleDbCommand(SQL, this.Conn);
try
{
command.ExecuteNonQuery();
this.Close();
return true;
}
catch
{
this.Close();
return false;
}
}
/// <summary>
/// 查询数据，返回DataSet
/// </summary>
/// <param name="SQL"></param>
/// <param name="subtableName"></param>
/// <returns></returns>
public DataSet SelectToDataSet(string SQL, string subtableName)
{
this.AccessDbClass();
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(SQL, this.Conn);
adapter.SelectCommand = command;
DataSet dataSet = new DataSet();//数据集,表的集合，可以存储很多的表
dataSet.Tables.Add(subtableName);//添加表，这个代码屏蔽也没有关系，下面的这个代码会自动添加的
adapter.Fill(dataSet, subtableName);//参数2：是表名，自定义的名字，不需要和查询的表名一致，随便取名
return dataSet;
}
public DataSet SelectToDataSet(string SQL, string subtableName, DataSet DataSetName)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(SQL, this.Conn);
adapter.SelectCommand = command;
DataTable table = new DataTable();
DataSet set = new DataSet();
set = DataSetName;
adapter.Fill(DataSetName, subtableName);
return set;
}
/// <summary>
/// 查询数据
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public DataTable SelectToDataTable(string SQL)
{
this.AccessDbClass();
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(SQL, this.Conn);
adapter.SelectCommand = command;
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
this.Close();
return dataTable;
}
public OleDbDataAdapter SelectToOleDbDataAdapter(string SQL)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(SQL, this.Conn);
adapter.SelectCommand = command;
return adapter;
}
}
}
2，实际项目应用
private void SaveResultToMyaccess(PMOpenProtocol.TighteningResultData data)
{
try
{
myAccess.AccessDbClass();
OleDbTransaction myTrans = myAccess.Conn.BeginTransaction();
OleDbCommand incmd = myAccess.Conn.CreateCommand();
incmd.Transaction = myTrans;
//【1】编写SQL语句
StringBuilder sqlBuilder = new StringBuilder();//如果字符串比较长，可以用StringBuilder
sqlBuilder.Append("insert into mydata (TighteningID,ProductSN,PsetName,StationName,BoltNumber,TighteningStatus,ResultDateTime,FinalTorque,FinalAngle,OperateDateTime,OperateFlag,ErrorInfo)");
sqlBuilder.Append(" values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',{10},'{11}')");
//【2】解析对象
string sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_1, data.t_D_TIGHTENING_STATUS_1, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_1, data.t_D_ANGLE_1, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
if (data.t_D_Number_of_Bolts >= 2)
{
sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_2, data.t_D_TIGHTENING_STATUS_2, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_2, data.t_D_ANGLE_2, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
}
if (data.t_D_Number_of_Bolts >= 3)
{
sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_3, data.t_D_TIGHTENING_STATUS_3, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_3, data.t_D_ANGLE_3, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
}
if (data.t_D_Number_of_Bolts >= 4)
{
sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_4, data.t_D_TIGHTENING_STATUS_4, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_4, data.t_D_ANGLE_4, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
}
if (data.t_D_Number_of_Bolts >= 5)
{
sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_5, data.t_D_TIGHTENING_STATUS_5, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_5, data.t_D_ANGLE_5, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
}
if (data.t_D_Number_of_Bolts >= 6)
{
sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_6, data.t_D_TIGHTENING_STATUS_6, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_6, data.t_D_ANGLE_6, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
}
if (data.t_D_Number_of_Bolts >= 7)
{
sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_7, data.t_D_TIGHTENING_STATUS_7, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_7, data.t_D_ANGLE_7, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
}
if (data.t_D_Number_of_Bolts >= 8)
{
sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_8, data.t_D_TIGHTENING_STATUS_8, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_8, data.t_D_ANGLE_8, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
}
if (data.t_D_Number_of_Bolts >= 9)
{
sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_9, data.t_D_TIGHTENING_STATUS_9, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_9, data.t_D_ANGLE_9, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
}
if (data.t_D_Number_of_Bolts >= 10)
{
sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_10, data.t_D_TIGHTENING_STATUS_10, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_10, data.t_D_ANGLE_10, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
}
if (data.t_D_Number_of_Bolts >= 11)
{
sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_11, data.t_D_TIGHTENING_STATUS_11, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_11, data.t_D_ANGLE_11, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
}
if (data.t_D_Number_of_Bolts >= 12)
{
sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_12, data.t_D_TIGHTENING_STATUS_12, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_12, data.t_D_ANGLE_12, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
}
if (data.t_D_Number_of_Bolts >= 13)
{
sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_13, data.t_D_TIGHTENING_STATUS_13, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_13, data.t_D_ANGLE_13, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
}
if (data.t_D_Number_of_Bolts >= 14)
{
sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_14, data.t_D_TIGHTENING_STATUS_14, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_14, data.t_D_ANGLE_14, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
}
if (data.t_D_Number_of_Bolts >= 15)
{
sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_15, data.t_D_TIGHTENING_STATUS_15, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_15, data.t_D_ANGLE_15, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
}
if (data.t_D_Number_of_Bolts >= 16)
{
sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_16, data.t_D_TIGHTENING_STATUS_16, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_16, data.t_D_ANGLE_16, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
}
if (data.t_D_Number_of_Bolts >= 17)
{
sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_17, data.t_D_TIGHTENING_STATUS_17, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_17, data.t_D_ANGLE_17, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
}
if (data.t_D_Number_of_Bolts >= 18)
{
sql = string.Format(sqlBuilder.ToString(),
data.TighteningID, data.IDRes, data.ModeName, data.StationName, data.OrdinalBoltNumber_18, data.t_D_TIGHTENING_STATUS_18, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_18, data.t_D_ANGLE_18, "", 0, "");
incmd.CommandText = sql;
incmd.ExecuteNonQuery();
}
try
{
myTrans.Commit();
myAccess.Close();
}
catch
{
myAccess.Close();
}
}
catch (Exception e)
{
MessageBox.Show("写入数据库失败，请检查数据库：" + e.ToString());
}
}
浙公网安备 33010602011771号