c#程序中想一次插入多条数据到oracle数据库中怎么实现

[zw520lb]

如题，asp.net 程序中，我需要实现一个import多行记录到数据库的功能，
所有的数据是显示在GridView中的，GridView的数据源是一个DataTable,
这个DataTable的列是根据条件动态生成的，列名对应数据库中表的字段，列数也是不确定的。


1. 我能想到的是在一个事务中，循环一条一条的进行插入，但问题是我必须把所有的列名（即数据表对应的字段名）和那条记录的所有列的值传递给oracle的存储过程，然后在存储过程中拼接insert的语句。 分2个参数，1个是所有列名用,拼接起来变成一个字符串传给oracle，另一个各个列的数据也这样拼接传过去。 想来想去这样做总归不好。

有没有什么好方法，可以传集合去实现？因为oracle不熟，只知道给存储过程传递基本的字段类型数据。

[tcmakebest]

楼主这样的功能明显像万能钥匙般的可操作任何一张表，因此楼主以为的复杂性部分其实是必须这样做的，没有更好的方法了。 但这种方式也是不安全的，表中的数据自有它的逻辑，业务上容易出问题。

[我本良人]

DataTable dt = new DataTable(); 
foreach (DataRow dr in dt.Rows) 
 { 
 ls.Add(dr["列名"]); 
}  

[江南小鱼]

撸主这个只能拼凑动态SQL了！ oracle可以一个SQL模板，批量执行一个OracleParameter数组

        /// <summary>
        /// 执行OracleCommand操作，使用批量插入
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="arrayBindCount">批量插入数</param>
        /// <param name="sql">sql语句</param>
        /// <param name="commandParameters">OracleParameter集合</param>
        /// <returns>受影响行数</returns>
        public static int ExecuteNonQuery(OracleTransaction transaction, int arrayBindCount, string sql, params OracleParameter[] commandParameters)
        {
            if (arrayBindCount == 0) return 0;
            using (OracleCommand cmd = new OracleCommand(sql, transaction.Connection))
            {
                cmd.CommandType = CommandType.Text;
                cmd.ArrayBindCount = arrayBindCount;
                cmd.Parameters.AddRange(commandParameters);
                int retval = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return retval;
            }
        }

[usabiscuit]

/// <summary>
        /// Insert data to Oracle
        /// </summary>
        /// <returns></returns>
        private int InsertResultToOracle(DataSet ds)
        {
            int status = -1;

            OracleConnection myConnection = null;

            OracleCommand myCommand = null;

            try
            {
                myConnection = new OracleConnection(ConnOracle);

                myCommand = myConnection.CreateCommand();
                
                //插入数据条数
                myCommand.ArrayBindCount = ds.Tables[0].Rows.Count;

                myCommand.CommandText = " Insert into DETECTRECORD( ";
                myCommand.CommandText += " VINID,AUTOTYPE,DETECTDATE,ENGINECHCKDT,DETECTRESULT,ENGINEJUDGE) ";
                myCommand.CommandText += " VALUES( ";
                myCommand.CommandText += " :VINID,:AUTOTYPE,:DETECTDATE,:ENGINECHCKDT,:DETECTRESULT,:ENGINEJUDGE)";

                myConnection.Open();

                //初始化参数
                string[] vin = new string[ds.Tables[0].Rows.Count];
                string[] autotype = new string[ds.Tables[0].Rows.Count];
                string[] detectdate = new string[ds.Tables[0].Rows.Count];
                string[] enginechckdt = new string[ds.Tables[0].Rows.Count];
                string[] result = new string[ds.Tables[0].Rows.Count];
                string[] enginejudge = new string[ds.Tables[0].Rows.Count];

                //VIN
                OracleParameter ParamVIN = new OracleParameter("VINID", OracleDbType.Varchar2);
                ParamVIN.Direction = ParameterDirection.Input;
                ParamVIN.Value = vin;
                myCommand.Parameters.Add(ParamVIN);

                //AUTOTYPE
                OracleParameter ParamAutotype = new OracleParameter("AUTOTYPE", OracleDbType.Varchar2);
                ParamAutotype.Direction = ParameterDirection.Input;
                ParamAutotype.Value = autotype;
                myCommand.Parameters.Add(ParamAutotype);

                //DETECTDATE
                OracleParameter ParamDETEDATE = new OracleParameter("DETECTDATE", OracleDbType.Varchar2);
                ParamDETEDATE.Direction = ParameterDirection.Input;
                ParamDETEDATE.Value = detectdate;
                myCommand.Parameters.Add(ParamDETEDATE);

                //enginechckdt
                OracleParameter ParamEngineCK = new OracleParameter("ENGINECHCKDT", OracleDbType.Varchar2);
                ParamEngineCK.Direction = ParameterDirection.Input;
                ParamEngineCK.Value = enginechckdt;
                myCommand.Parameters.Add(ParamEngineCK);

                //RESULT
                OracleParameter ParamResult = new OracleParameter("DETECTRESULT", OracleDbType.Varchar2);
                ParamResult.Direction = ParameterDirection.Input;
                ParamResult.Value = result;
                myCommand.Parameters.Add(ParamResult);

                //ENGINEJUDGE
                OracleParameter ParamEngineJudge = new OracleParameter("ENGINEJUDGE", OracleDbType.Varchar2);
                ParamEngineJudge.Direction = ParameterDirection.Input;
                ParamEngineJudge.Value = enginejudge;
                myCommand.Parameters.Add(ParamEngineJudge);

                //insert array
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    vin[i] = ds.Tables[0].Rows[i][0].ToString().Trim();
                    autotype[i] = ds.Tables[0].Rows[i][1].ToString().Trim();
                    detectdate[i] = ds.Tables[0].Rows[i][2].ToString().Trim() + " " + ds.Tables[0].Rows[i][3].ToString().Trim();
                    enginechckdt[i] = ds.Tables[0].Rows[i][2].ToString().Trim();
                    if (ds.Tables[0].Rows[i][4].ToString().Trim() == "1")
                    {
                        result[i] = "合格";
                        enginejudge[i] = "1";
                    }
                    else
                    {
                        result[i] = "不合格";
                        enginejudge[i] = "0";
                        HasDTC = true;
                    }
                    

                    WriteLog.Log("vin: " + vin[i] + "\n"
                    +"autotype: " + autotype[i] + "\n"
                    +"detectdate: " + detectdate[i] + "\n"
                    +"enginechckdt: " + enginechckdt[i] + "\n"
                    +"result: " + result[i] + "\n"
                    +"enginejudge: " + enginejudge[i] + "\n");
                }

                status = myCommand.ExecuteNonQuery(); 

                //myConnection.Close();
                //myCommand.Dispose();
            }
            catch (Exception ex)
            {
                WriteLog.Log("InsertResultToOracle:" + ex.Message.ToString());
            }
            finally
            {
                if (myConnection != null)
                {
                    myConnection.Close();
                }
                if (myCommand != null)
                {
                    myCommand.Dispose();
                }
            }
            return status;
        }