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