将xml中的数据导入到数据库

最简单的读取，把xml的一级子元素当做一条记录插入数据库

 

代码

static void InputDict()
{
var conn = new Connection(SharedMembers.localConnectString);

XmlReaderSettings settings = new XmlReaderSettings();
settings.IgnoreWhitespace = true ;

using (XmlReader reader = XmlReader.Create( " 简明英汉词典.xml " )) {

reader.MoveToContent();
reader.Read();
var i = 0 ;
while (reader.IsStartElement()) {
i ++ ;
var cmd = new Command( " INSERT INTO [dict].[dbo].[simplec]([ck])VALUES (@ck) " );
cmd.AddParameter( " @ck " ,System.Data.SqlDbType.Xml, false ,reader.ReadOuterXml());
conn.Execute(cmd);
Console.WriteLine( " 插入第{0}行数据 " ,i);
};
}

}

 

 

将上一例中插入的数据记录读取出来，经分析后拆分成dc,js保存到另一个表中。

 

代码

static void parsedict()
{
using (var connection = new SqlConnection(SharedMembers.localConnectString))
{
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = " SELECT [ck] FROM [dict].[dbo].[simplec] " ;
connection.Open();
var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
var ck = ( string )rdr[ " ck " ];

var doc = new XmlDocument();
doc.LoadXml(ck);

var Navigator = doc.CreateNavigator();
var dc = Navigator.SelectSingleNode( " /CK/DC " ).Value;

Navigator.MoveToNext();
string js = Navigator.SelectSingleNode( " /CK/JS " ).OuterXml;

using (var connection2 = new SqlConnection(SharedMembers.localConnectString))
{
using (var inscmd = connection2.CreateCommand())
{
inscmd.CommandType = CommandType.Text;
inscmd.CommandText = " INSERT INTO [dict].[dbo].[jmyh2]([dc],[js])VALUES(@DC,@JS) " ;

var pdc = inscmd.CreateParameter();
pdc.ParameterName = " @DC " ;
pdc.SqlDbType = SqlDbType.NVarChar;
pdc.Value = dc;
inscmd.Parameters.Add(pdc);

var pjs = inscmd.CreateParameter();
pjs.ParameterName = " @JS " ;
pjs.SqlDbType = SqlDbType.Xml ;
pjs.Value = js;
inscmd.Parameters.Add(pjs);

connection2.Open();
inscmd.ExecuteNonQuery();
Console.WriteLine(dc);
}

}
}
}
}
}

 

 

事实上，可以直接的解析xml，再插入到数据库表。这里使用了xpath技术。

 

代码

static void InputDict2()
{
var conn = new Connection(SharedMembers.localConnectString);

XmlReaderSettings settings = new XmlReaderSettings();
settings.IgnoreWhitespace = true ;

const string dictname = " OxfordCollocations " ;
using (XmlReader reader = XmlReader.Create(dictname + " .xml " ))
{

reader.MoveToContent();
reader.Read();
long i = 0 ;
while (reader.IsStartElement())
{
var ck = new XmlDocument();
ck.LoadXml(reader.ReadOuterXml());

var Navigator = ck.CreateNavigator();
var dc = Navigator.SelectSingleNode( " /CK/DC " ).Value;

Navigator.MoveToNext();
var js = Navigator.SelectSingleNode( " /CK/JS " ).OuterXml;

i ++ ;
var cmd = new Command( " INSERT INTO dict.dbo. " + dictname + " (id, dc,js)VALUES(@id, @dc,@js) " );
cmd.AddParameter( " @id " , System.Data.SqlDbType.BigInt, false , i);
cmd.AddParameter( " @dc " , System.Data.SqlDbType.NVarChar, false , dc);
cmd.AddParameter( " @js " , System.Data.SqlDbType.Xml, false , js);

conn.Execute(cmd);
if (i % 1000 == 0 ) Console.WriteLine( " 插入第{0:#,##0,000}行数据 " , i);
};
}

}

 

 

 

 

 

 

转载于:https://www.cnblogs.com/cuishengli/archive/2010/12/17/1909564.html