RE: [Firebird-net-provider] Importing data from XML

[Peter M. <P.m...@de...>]

Hello:

> Is there any good application for importing data into Firebird from XML 
> or do you have to write one yourself?


Huummmmm .... i'm not sure if it will match your needs but you can try 
using a DataSet ( using the DataSet.ReadXml method ) and the 
FbDataAdapter class for execute updates, inserts and deletes.


I did something like that:
- fill a dataset from XML
- creating the SQL for creating the tables in the fb database. 
- execute the sql on my database
- fill a new dataAdapter and dataset for the new table 
- filling the new dataset with the old one
- do an update

and then: crash at record 16000. 
I don't know what happened. I tried to commit every 1000 records but that
didn't help.
I rewrote the same code for MS-SQl server and that worked fine.

If anybody's interested, here is the code at the bottom.
If somebody can get this to work, I could switch over to Firebird again.

regards, Peter Martens.



		public SqlDataAdapter CreateTable(DataSet ds)  // maak een
database tabel van de ds.Tables[0]
		{
			string TableName = ds.Tables[0].TableName.ToUpper();
			if( TableName == "TABLE" ) 
			{
				TableName = ds.DataSetName.ToUpper();  //
wordt soms niet goed overgenomen
				ds.Tables[0].TableName = TableName;
			}
			int[] lengths = new int[ds.Tables[0].Columns.Count];
			foreach(DataRow dr in ds.Tables[0].Rows)
			{  // zet de maximum lengte goed voor de strings
				for(int i=0; i < ds.Tables[0].Columns.Count;
i++)
				{
					DataColumn dc =
ds.Tables[0].Columns[i];
					if(
dc.DataType.Equals(typeof(string)) )
					{
						string s = dr[i].ToString();
						if( s.Length > lengths[i] )
lengths[i] = s.Length ;
					} 
					else if (dc.MaxLength > lengths[i] )
lengths[i] = dc.MaxLength;
				}
			}

			ExecuteSQL("drop table " + TableName + ";");

			bool first1 = true;
			bool HasID = false;
			string cmd = "create table " + TableName + "(";
			for(int i=0; i < ds.Tables[0].Columns.Count; i++)
			{
				DataColumn dc = ds.Tables[0].Columns[i];
				if( first1 ) first1 = false;
				else 
				{
					cmd += ",";

				}
				System.Text.StringBuilder fc = new
System.Text.StringBuilder(dc.ColumnName.ToUpper());
				if( fc.Length < 1 ) 
				{
					Message = "Kolom zonder naam
gevonden";
					return null;
				}
				if( !System.Char.IsLetter(fc[0]) ) fc = new
System.Text.StringBuilder("N"+ fc) ;
				for( int k =0 ; k < fc.Length;k++)
				{
					if(
System.Char.IsLetterOrDigit(fc[k]) ) continue;
					fc[k] = '_';   // maak van de rest
een underscore
				}
				if( fc.ToString() == "ID" ) 
				{
					HasID = true;
					dc.AllowDBNull = false;
				}
				fc.Append(" ");
				if( dc.DataType == typeof(System.DateTime))
fc.Append( "TIMESTAMP");
				else if( dc.DataType ==
typeof(System.Int16))  fc.Append("INTEGER");
				else if( dc.DataType ==
typeof(System.Int32))  fc.Append(  "INTEGER"); 
				else if( dc.DataType ==
typeof(System.Double))  fc.Append("DOUBLE PRECISION");
				else if( dc.DataType ==
typeof(System.Object))   fc.Append( "BLOB");
				else 
				{  // if( dc.DataType ==
typeof(System.Char[]))  if( dc.DataType == typeof(System.String))
					int len = lengths[i];
					if( len <= 0 ) 
					{  // todo: maak hier een goede
schatting
						len = 10;
					}
					fc.Append("VARCHAR(" +
len.ToString() + ")");
				}
				if( ! dc.AllowDBNull ) fc.Append( " not
null");
				cmd += fc;
			}
			if( !HasID )
			{
				cmd += ",ID INTEGER NOT NULL";
			}
			cmd += ");";
			ExecuteSQL(cmd);
			
			//  create primary keys: todo gebruik key van tabel
			cmd = @"alter table " + TableName + " add constraint
PK1 primary key (ID);";
			ExecuteSQL(cmd);
	

			txn = myConnection.BeginTransaction("Populate");
			System.Data.SqlClient.SqlDataAdapter mDataAdapter
= new System.Data.SqlClient.SqlDataAdapter();
			mDataAdapter.SelectCommand = new
System.Data.SqlClient.SqlCommand("SELECT * FROM " + TableName + ";",
myConnection,txn);	
			System.Data.SqlClient.SqlCommandBuilder cb = new
System.Data.SqlClient.SqlCommandBuilder(mDataAdapter);  // genereer de
insert, etc

			DataSet nds = new DataSet("ds" + TableName);
			mDataAdapter.Fill(nds, TableName);
			nds.Tables[0].TableName = TableName;
			
			int id = 1;
			int c = 0;
			foreach(DataRow dr in ds.Tables[0].Rows)
			{
				DataRow ndr = nds.Tables[0].NewRow();
				for(int i=0; i <
nds.Tables[0].Columns.Count; i++)
				{
					DataColumn dc =
nds.Tables[0].Columns[i];
					if(i >= ds.Tables[0].Columns.Count )
					{
						if( dc.ColumnName == "ID" )
ndr["ID"] = id++; // zelf nummeren

					}
					else
					{
						if(
dc.DataType.Equals(typeof(string)) )
						{
							string s =
dr[i].ToString();
							if( s.Length >
dc.MaxLength && dc.MaxLength > 0) s = s.Substring(0,dc.MaxLength);
							if( s.Length > 0 )
ndr[i] = s;
							else ndr[i] =
String.Empty;
						} 
						else 
						{
							ndr[i] = dr[i];  //
copy. todo wat als de objecttype niet helemaal overeenkomt
						}
					}
				}
				nds.Tables[0].Rows.Add(ndr);
				if( c++ > 1000 )
				{  // attempt to fix for Firebird
					c = 0;
					mDataAdapter.Update(nds,TableName); 
					//txn.Commit();
					//txn =
myConnection.BeginTransaction("PopulateTable");
				}
			}
			mDataAdapter.Update(nds,TableName);  // do it
			//txn.Commit();
			//txn = myConnection.BeginTransaction();  // nieuwe
transactie achterlaten
			return mDataAdapter;
		}