Re: [Firebird-net-provider] insert many rows

[<car...@te...>]

Hello:

Sorry for the delay.

> This results in:
> 
> An unhandled exception of type 'FirebirdSql.Data.Firebird.FbException'
> occurred in system.data.dll

I got the exception when using the Command Builder with the Data 
Adapter, i have done some little changes in your sample code and now 
runs fine, i have done the test with 1.6 CVS sources and i have done 
changes in the FbCommand.Clone method for a correct implementation.

I will try to review why this fails when using the FbCommandBuilder, 
thanks for the sample code,
very useful :)



The new code:

FbConnection connection = new FbConnection(connectionString);
connection.Open();

string		tableName	= "TEST1";
string		message		= String.Empty;
DataSet		ds			= new DataSet("ds" + tableName);
DataTable	dt			= new DataTable(tableName);
string		sql			= String.Empty;

dt.Columns.Add("TSTAMP"	, typeof(String));
dt.Columns.Add("ID"		, typeof(Int32));
dt.Columns.Add("MSG"	, typeof(String));
ds.Tables.Add(dt);

try
{
	sql = String.Format("drop table {0}", tableName);
	FbCommand dropTable = new FbCommand(sql, connection);
	dropTable.ExecuteNonQuery();
}
catch
{
}

bool	first1	= true;
bool	hasID	= false;
string	cmd		= String.Format("create table {0} (", 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 += ",";
	}

	StringBuilder fc = new StringBuilder(dc.ColumnName.ToUpper());

	if (fc.Length < 1)
	{
		message = "Kolom zonder naam gevonden";
		return;
	}

	if (!System.Char.IsLetter(fc[0]))
	{
		fc = new 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
	{
		fc.Append("VARCHAR(30)");
	}

	if (!dc.AllowDBNull)
	{
		fc.Append( " not null");
	}
	cmd += fc;
}
if (!hasID)
{
	cmd += ",ID INTEGER NOT NULL";
}
cmd += ");";

FbCommand createTable = new FbCommand(cmd, connection);
createTable.ExecuteNonQuery();

cmd = @"alter table " + tableName + " add constraint PK1 primary key (ID);";

FbCommand alterTable = new FbCommand(cmd, connection);
alterTable.ExecuteNonQuery();

FbTransaction transaction = connection.BeginTransaction();

sql = "SELECT * FROM " + tableName + ";";

FbDataAdapter		mDataAdapter	= new FbDataAdapter(new FbCommand(sql, 
connection, transaction));
FbCommandBuilder	cb				= new FbCommandBuilder(mDataAdapter);  // 
genereer de insert, etc

mDataAdapter.InsertCommand = 
(FbCommand)((ICloneable)cb.GetInsertCommand()).Clone();
mDataAdapter.UpdateCommand = 
(FbCommand)((ICloneable)cb.GetUpdateCommand()).Clone();
mDataAdapter.DeleteCommand = 
(FbCommand)((ICloneable)cb.GetDeleteCommand()).Clone();

cb.Dispose();

DataSet nds = new DataSet("ds" + tableName);
mDataAdapter.Fill(nds, tableName);
nds.Tables[0].TableName = tableName;

int id	= 1;

ds.Tables[0].BeginLoadData();

for (int j=0; j < 50000; j++)
{
	DataRow dr = ds.Tables[0].NewRow();

	dr["ID"]		= id++;
	dr["TSTAMP"]	= System.DateTime.Now.ToShortTimeString();
	dr["MSG"]		= System.DateTime.Now.ToShortDateString();

	ds.Tables[0].Rows.Add(dr);
}

ds.Tables[0].EndLoadData();

mDataAdapter.Update(ds, tableName);  // do it

transaction.Commit();

connection.Close();




--
Best regards

Carlos Guzmán Álvarez
Vigo-Spain