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());
            }
        }