public static TableMetaData[] getTableData(String connStr, String table) { List <TableMetaData> td = new List <TableMetaData>(); DbConnection c = null; DbCommand cmd = null; DbDataReader r = null; try { c = DBConnectionFactory.GetSqlConnection(connStr); cmd = DBConnectionFactory.GetSqlCommand("desc " + table, c); r = cmd.ExecuteReader(); while (r.Read()) { TableMetaData md = new TableMetaData(); md.Name = DBTools.GetDBStringValue(r, "Field"); String[] dataType = DBTools.GetDBStringValue(r, "Type").Split(new char[] { '(', ')' }); if (dataType.Length > 1) { md.MaxLen = int.Parse(dataType[1]); } else { md.MaxLen = 0; } md.Type = dataType[0]; md.IsNullable = DBTools.GetDBStringValue(r, "Null").Equals("YES") ? true : false; md.IsKey = DBTools.GetDBStringValue(r, "Key").Equals("") ? false : true; if (md.IsKey) { md.IsPrimary = DBTools.GetDBStringValue(r, "Key").Equals("PRI") ? true : false; } td.Add(md); } return(td.ToArray()); } catch (Exception e) { log.Error("GetTableData feilet", e); return(null); } finally { if (r != null) { r.Close(); } if (cmd != null) { cmd.Dispose(); } if (c != null) { c.Close(); } } }
public static int loadData(String conStr, String data, String table, String format) { DbConnection con = DBConnectionFactory.GetSqlConnection(conStr); if (con == null) { return(0); } DbTransaction trans = con.BeginTransaction(); TableMetaData[] meta = getTableData(conStr, table); if (meta == null) { return(0); } if (format.ToLower().Equals("xml")) { XmlDocument doc = new XmlDocument(); doc.LoadXml(data); XmlNodeList nodes = doc.SelectNodes(table.Trim() + "/data"); if (nodes.Count == 0) { return(0); } // Build the prepared statement String sql = "replace into " + table + " set "; XmlNode node = nodes.Item(0); XmlNodeList nl = node.ChildNodes; System.Collections.IEnumerator en = nl.GetEnumerator(); while (en.MoveNext()) { node = (XmlNode)en.Current; sql += node.Name + " = @" + node.Name.Trim() + ","; } sql = sql.TrimEnd(','); try { for (int n = 0; n < nodes.Count; n++) { // Load the data DbCommand cmd = DBConnectionFactory.GetSqlCommand(sql, con); XmlNode xmlnode = nodes.Item(n); XmlNodeList param = xmlnode.ChildNodes; for (int p = 0; p < param.Count; p++) { xmlnode = param.Item(p); cmd.Parameters.Add("@" + xmlnode.Name.Trim()); cmd.Parameters["@" + xmlnode.Name.Trim()].Value = xmlnode.InnerText; } cmd.ExecuteNonQuery(); cmd.Dispose(); } trans.Commit(); return(nodes.Count); } catch (Exception e) { trans.Rollback(); throw e; } } return(0); }