把System.byte[]类型数据写入Oracle的blob类型字段中

最新推荐文章于 2021-04-12 13:37:59 发布

飞瀑

最新推荐文章于 2021-04-12 13:37:59 发布

阅读量2.2k

点赞数

分类专栏： C#

本文链接：https://blog.csdn.net/feifei2211/article/details/8315639

版权

C# 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

基本形式为：sql="insert into 你要插入的表名 (field1,field2,..fieldi..fieldn)values(‘value1’,‘value2’,...,：fieldi,....‘valuen’)";
其中“:fieldi”为你要插入blob对应的字段名。字段名和参数名一致（ParameterName)

OracleCommand cmd = new OracleCommand(sql, connection);
OracleParameter a = new OracleParameter();
a.Direction = ParameterDirection.Input;
a.ParameterName = valuei.ToString();
a.Value =System.byte[]类型的值;
a.OracleType = OracleType.Blob;
cmd.Parameters.Add(a);

connection.Open();
cmd.ExecuteNonQuery();

如：

ArrayList fieldNames = getInsertFields();
            string content = "";
            //content = "ID,";
            connection.Open();
            int numberOfAdd =(int)fieldNames[fieldNames.Count - 1];
            for (int k = 0; k < fieldNames.Count - 2; k++)
            {
                content = content + fieldNames[k].ToString() + ",";
            }
            content = content + fieldNames[fieldNames.Count - 1].ToString();
            WriteToLog(content, logPath);
            content = "导入的记录条数：" + dataTable.Rows.Count.ToString();
            WriteToLog(content, logPath);
            string sql = "";

            if (numberOfAdd == 0)
            {
                for (int j = 0; j < dataTable.Rows.Count; j++)
                {
                    ArrayList blobArray = new ArrayList();
                    ArrayList blobName = new ArrayList();
                    //int m = 0;
                    sql =@"INSERT INTO " + tableName + @" (";
                    for (int i = 0; i < fieldNames.Count - 2; i++)
                    {
                        //if(blobF.Contains(fieldNames[i].ToString()))
                        //{
                        //    sql = sql + ":"+fieldNames[i].ToString() + ",";
                        //}else
                        //{
                        sql = sql + fieldNames[i].ToString() + ",";
                        //}
                    }
                    sql = sql + fieldNames[fieldNames.Count - 2] + ") values (";
                    for (int i = 0; i < dataTable.Columns.Count - 1; i++)
                    {

                        if (dateOrNot(dataTable.Rows[j][i].ToString()))
                        {
                            sql = sql + "to_date('" + toFormatShortDate(dataTable.Rows[j][i].ToString()) + "','yyyy-mm-dd'),";
                        }
                        else if (dataTable.Rows[j][i].GetType().ToString() == "System.Byte[]")
                        {
                            blobArray.Add(dataTable.Rows[j][i]);
                            blobName.Add(fieldNames[i]);
                            sql = sql + ":" + fieldNames[i] + ",";
                        }
                        else
                        {
                            sql = sql + "'" + dataTable.Rows[j][i].ToString() + "',";
                        }

                    }
                    if (dateOrNot(dataTable.Rows[j][dataTable.Columns.Count - 1].ToString()))
                    {
                        sql = sql + "to_date('" + toFormatShortDate(dataTable.Rows[j][dataTable.Columns.Count - 1].ToString()) + "','yyyy-mm-dd'))";
                    }
                    else if (dataTable.Rows[j][dataTable.Columns.Count - 1].GetType().ToString() == "System.Byte[]")
                    {
                        blobArray.Add(dataTable.Rows[j][dataTable.Columns.Count - 1]);
                        blobName.Add(fieldNames[dataTable.Columns.Count - 1]);
                        sql = sql + ":" + fieldNames[dataTable.Columns.Count - 1] + ")";
                    }
                    else
                    {
                        sql = sql + "'" + dataTable.Rows[j][dataTable.Columns.Count - 1].ToString() + "')";
                    }
                    OracleCommand cmd = new OracleCommand(sql, connection);
                    if (blobName.Count > 0)
                    {
                        for (int i = 0; i < blobArray.Count; i++)
                        {
                            OracleParameter a = new OracleParameter();
                            a.Direction = ParameterDirection.Input;
                            a.ParameterName = blobName[i].ToString();
                            a.Value = blobArray[i];
                            a.OracleType = OracleType.Blob;
                            cmd.Parameters.Add(a);
                        }
                    }
                    connection.Close();
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
            }