FillSchema problem/question

[bri189a]

1. Using FillSchema works great if I use SQL in my SelectCommand, but if my SelectCommand is a procedure (which is the same SQL text - SELECT * FROM someTable) it doesn't work at all... why is this? And is this by design?

 

2. Since I have several tables, each with their own 1-Many relationships with other tables will FillSchema put in all the relationships as I add the tables?

 

Thanks...

[Nerseus]

1. Not sure why it didn't work. What properties did you set on your adapter - can we see the relevant code?

 

2. It's generally faster if you put in the tables and relationships yourself though you can use FillSchema to get the table structure. I don't think FillSchema builds the relationships, but it might - you'd have to test.

 

-nerseus

[bri189a]

here's the code:

 

private void Init_COMP_GEN()
	{
		COMP_GEN = new OleDbDataAdapter();
		//Select Query - has to be SQL otherwise won't work for FillSchema...find out why!
		COMP_GEN.SelectCommand = new OleDbCommand("SELECT * FROM COMP_GEN", dbConn);
		//COMP_GEN.SelectCommand.CommandType = CommandType.StoredProcedure;

		//Add Query
		COMP_GEN.InsertCommand = new OleDbCommand("ADD_COMPUTER", dbConn);
		COMP_GEN.InsertCommand.CommandType = CommandType.StoredProcedure;
		COMP_GEN.InsertCommand.Parameters.Add("ComputerName", OleDbType.Char, 50, "ComputerName");
		COMP_GEN.InsertCommand.Parameters.Add("Icon", OleDbType.SmallInt, 0, "Icon");

		//Update Query (copy parameter from Add Query, then add the WHERE Parameter
		COMP_GEN.UpdateCommand = new OleDbCommand("UPDATE_COMPUTER", dbConn);
		COMP_GEN.UpdateCommand.CommandType = CommandType.StoredProcedure;
		COMP_GEN.UpdateCommand.Parameters.Add("newComputerName", OleDbType.Char, 50, "ComputerName");
		COMP_GEN.UpdateCommand.Parameters.Add("newIcon", OleDbType.SmallInt, 0, "Icon");
		COMP_GEN.UpdateCommand.Parameters.Add("Q", OleDbType.Integer, 0, "ID");

		//Delete Query
		COMP_GEN.DeleteCommand = new OleDbCommand("DELETE_COMPUTER", dbConn);
		COMP_GEN.DeleteCommand.CommandType = CommandType.StoredProcedure;
		COMP_GEN.DeleteCommand.Parameters.Add("Q", OleDbType.Integer, 0, "ID");

		//Fill Schema
		COMP_GEN.FillSchema(ds, SchemaType.Mapped, "COMP_GEN");
	}

 

Yeah I read that it's 20% faster to do it yourself, but speed isn't in an issue for this procedure as it runs in the background on a seperate thread while other things are going on in the main program... it's a rarely used procedure and I'm just trying to keep the code simple (plus learn new methods)... though if I have to make the relationships between the tables myself then it really doesn't matter right? I'll end up doing it anyway. :)

 

Also, while I'm here... using Fill()... if someone removed a row from the database using a seperate instance of the program from a differant location, wouldn't the Fill() method detect this change and remove that row from it's instance? In other words, a user on computer 1 and 2 are looking at the same table. User on computer 2 deletes a row and calls update. User on computer 1 later does something that calls the Fill() method to be called again... wouldn't he now see that change? It seems I can only get this to work if I do Rows.Clear() on that table before calling the Fill() method which seems to take the whole point of the Fill() method out. (The table has the schema information already)... it's almost like because that row was there previously in the DataTable it will hold on to it, even if it isn't in the database itself - I don't understand why it's doing this since the Fill() method is suppose to refresh the DataTable to match what is in the Database, and what's really weird, is if I undo the schema information so that there in no Primary Key, it will add the row in the database as you would expect, so that there is a double of every row except the one that was deleted.

 

Thanks.

Edited February 23, 2004 by bri189a