|
I want to return a larg set of records from database server
to the client tier -win forms-
so the question
1-what is the effective way to do this?
2- is it differ if they connected via Wan or via the Internet
thanks in advace
|
|
|
|
|
Two very basic advices:
1. return ONLY the data that you really need (do not use SELECT * ...),
2. return all data using ONE batch to minimize the network traffic; don't do something like:
do
[your query]
while (...)
modified on Monday, August 30, 2010 6:42 AM
|
|
|
|
|
thanks chopeen for your reply
of course i will return the data that i realy need
so the question at the case that i really need many records
when you work with disconected data you may have situation like this
you want to create dataset which have many records or if you implement your business objects
so the question again ho to do this effectively
thanks again
i need your advices gurus
|
|
|
|
|
You can use a DataSet object. It works perfect on disconected enviroments. Also look information on the DataAdapter object.
The DataAdapter, will translate the modifications done to your DataSet to the DataBase.
Free your mind...
|
|
|
|
|
Thanks Guillermo Rivero
it seems that i did not explain what i need exactly
i know that i can use dataset on disconected enviroments but Is it will work fine if
the returned records = 1000000 ? for example.
in situation i need this data
|
|
|
|
|
Well, I once did load a DataSet with 2 million records and it worked fine.
Each record had 5 fields.
Free your mind...
|
|
|
|
|
Thanks Guillermo Rivero for your help
thats ok for me but i want to ask you another question
do you know good resources (books - web sites...) about
Designing distrebuted application in .net that have real
world examples? i have some books about remoting and webservice but they are teaching technology with out real world examples .
thanks in advance
|
|
|
|
|
Ok, I read somewhenre in msdn.microsoft.com[^] something about Application Blocks. And I'm almost sure that there is Distributed Application Block. There you can have guidelines on how to design Distributed Applications.
The other way is design, your own. I prefer the second one...
Free your mind...
|
|
|
|
|
thanks again Guillermo Rivero i really appreciate your interact with my quetsion i will post two qestion about
paging May you take a look about them?
|
|
|
|
|
To: Mohamad Al Husseiny
Hi there. Have you got a solution to your problem already?
Because im encountering that same problem. if you already have the solution please it would help me a lot thanks.
|
|
|
|
|
Guys, perhaps you can help me speed up the process here.
1. sp_msx_enlist
2. sp_msx_defect
Do you run this stored proc on master server or on target server? Or both?
Thanks
norm
|
|
|
|
|
HELLO! I've been developing a C# .NET application with mySQL database using ODBC driver (myodbc 3.51) to connect to the database. I've been trying to implement database transaction but encountered an error which I've no idea on how to go about in solving it.
Here's the exception that I got:
**********************************************************
Microsoft.Data.Odbc.OdbcException: ERROR [HYC00] [MySQL][ODBC 3.51 Driver]Transactions are not enabled
at Microsoft.Data.Odbc.OdbcConnection.HandleError(IntPtr hHandle, SQL_HANDLE hType, RETCODE retcode)
at Microsoft.Data.Odbc.OdbcTransaction.set_AutoCommit(Boolean value)
at Microsoft.Data.Odbc.OdbcTransaction.BeginTransaction()
at Microsoft.Data.Odbc.OdbcConnection.BeginTransactionObject(IsolationLevel isolevel)
at Microsoft.Data.Odbc.OdbcConnection.BeginTransaction(IsolationLevel isolevel)
at Solution1.Model.DBAccess.pubic() in c:\documents and settings\nat\desktop\equibs 2004\source\solution1\model\dbaccess.cs:line 478
The thread '<no name="">' (0x1f00) has exited with code 0 (0x0).
The program '[7908] Solution1.exe' has exited with code 0 (0x0).
**********************************************************
Here's what I did:
**********************************************************
OdbcConnection myConnect = new OdbcConnection(myConnString);
myConnect.Open();
OdbcCommand myCommand = myConnect.CreateCommand();
OdbcTransaction myTrans;
try
{
myTrans = myConnect.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
myCommand.Transaction = myTrans;
try
{
myCommand.CommandText = "INSERT INTO `alvin` ( `id` , `value` ) " + "VALUES ('0', '12');";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "INSERT INTO `alvin` ( `id` , `value` ) " + "VALUES ('0', '24');";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Console.WriteLine("Both records are written to database.");
myConnect.Close();
}
catch(Exception e)
{
try
{
myTrans.Rollback();
}
catch (OdbcException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() + " was encountered while attempting to roll back the transaction.");
}
}
Console.WriteLine("An exception of type " + e.GetType() + "was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
}
finally
{
myConnect.Close();
}
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
}
**********************************************************
Please HELP!!! Thanksss
Love my shoes
|
|
|
|
|
When I last looked, a couple of years back, MySQL did not support transactions (although there were plans in the pipe-line to support them).
You should look at your version of MySQL to find out if it supports transactions - if it does then you may have to change some configuration settings.
Hope this helps.
Andy
|
|
|
|
|
Thanks a bunch! Yeah, reckon that's the problem too! Thanks again!
Love my shoes
|
|
|
|
|
Hi all,
I have an admin sproc that I've used for a long time; but apparently I never noticed that sp_depends doesn't return a failure value. I suppose over time I have just never checked a sproc that had no dependencies which is how I discovered this.
sp_depends raises an error if there are no dependencies unfortunately the return Value is 0 for all cases. Therefore using checking the return value is of no use. Since I use a return Value and a retCode in my sproc I was wondering if there is some system variable I can use to get the last error raised when exec a sproc from a sproc??
Currently I am checking for the table count and if it is 0 then I display the same message sp_depends would. I would rather the error number be returned in my return code so I can look up the mesage or have a custom message of my own.
Thanks
Pamela Reinskou
VersusLaw Inc.
|
|
|
|
|
The @@ERROR variable holds the last error number.
|
|
|
|
|
Yes, this is true; but in this case it is overwritten as soon as the next statement in the sp_depends sproc is executed. Therefore by the time my sproc receives the output the @@Error is 0 again.
I was thinking maybe there was some undocumented system function(s) that handles scope much like the identity functions, Scope_Identity and @@Ident_current.
You can check this for yourself by comparing the results of sp_depends on an object that has dependencies and one that doesn't, set a local var to return the returnValue and also try to grab the @@Error and you will see in all cases the returnValue is 0 and @@Error is 0. For now I am trapping it and using table count that seems to be the only way to figure it out.
Thanks.
Pamela Reinskou
VersusLaw Inc.
|
|
|
|
|
When I try to access "MS Access" .mdb database using "native" Microsoft.Jet.OLEDB.4.0 provider I'm experiencing a problem with deleting of multiple records from previously retrieved recordset.
Here is an example:
// first, I open database in very usual way using ATL-based classes (mostly generated by OLEDB consumer-template wizard in VS)
CDataSource db;
CDBPropSet dbinit(DBPROPSET_DBINIT);
...................................
dbinit.AddProperty(DBPROP_AUTH_PASSWORD, OLESTR(""));
dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("Admin"));
dbinit.AddProperty(DBPROP_INIT_DATASOURCE, (LPOLESTR)szDBName);
dbinit.AddProperty(DBPROP_INIT_MODE, (long)16);
hr = db.Open(_T("Microsoft.Jet.OLEDB.4.0"), &dbinit);
if( SUCCEEDED(hr) )
{
hr = m_session.Open( db );
return hr;
}
// then I use generated accessor class
(something like class CProducts : public CCommand<caccessor<cproductsaccessor> >)
to open a rowset as a result of some SQL statement:
CProducts rs;
rs.Open();
// When I iterate through received recordset, processing data and deleting records using some criteria (or just all of them) like:
while( S_OK == rs.MoveNext() )
{
..................... // do something here
rs.Delete();
}
I receive "DB_S_ENDOFROWSET" just right after the second deletion, even though there is a lot more records in the recordset - without such deletion inside a loop I can successfully traverse entire recordset (all records) with no problems at all.
When I open DataSource using "MDASQL" as a provider instead of "Microsoft.Jet.OLEDB.4.0" everything works just fine (as it is supposed to work).
Initially blamed msjetoledb40.dll module, but installing the latest MSJETOLEDB40 Service Pack SP8 did not solve the problem.
Did I do something wrong, or somebody else had such problem and knew good workaround?
Best regards,
Andrey
|
|
|
|
|
I haven't seen this problem, but that's probably because I'm fairly new to this. Generally I've had good luck with a slightly different structure, though. Try
while (!rs.EOF)
{
.............//do stuff
rs.Delete();
rs.MoveNext();
}
and see what happens. According to MSDN,
"After a successful deletion, the recordset's field data members are set to a Null value, and you must explicitly call one of the Move functions in order to move off the deleted record. "
I know that your existing while loop is supposed to do this, but it may be that the Jet Database Engine is looking for something more explicit.
"Another day done - All targets met; all systems fully operational; all customers satisfied; all staff keen and well motivated; all pigs fed and ready to fly" - Jennie A.
|
|
|
|
|
I want to rename fields in the existing data table.
I am totally confused with ADO Fields deletions process I am using.
I delete all records from table - using SQL DELETE. The table may be empty already.
Than I close the current empty recordset - fields->delete wont't work on an open recordset (per documentation-I have not tried it with open recordset!)
The code ptrFields->Delete(_variant_t(Index)) appears to delete the field not "mark it" for deletion as the documentation states. If I advance the Index in the loop it skips every other field!
Before I attempt to do update I re-open the recordset.
When I do prtFields->Update() to actually delete the field I am getting "Not implemented " error. What does that mean?
When I do "recordset->Update" I get an info that I do not have a current record. ( Do I need a dummy record to get rid of this error?)
Can anybody point me to the right way to do this?
Thanks for your help. Vaclav
|
|
|
|
|
I need to update a database(B) with values from a stored procedure in another database(A). Both these databases reside on the same machine (apps). The closest example I found was using linked server, but that did not work for me, as I probably set it up wrong. The table I want to insert values to in database B is called tracking.
How is it possible to do this?
|
|
|
|
|
|
Hi
I'm trying to access some data from a table that has fields defined as 200 character long strings. Problem is that it appears as if the 200 characters are being used as a byte array with either 0xFF or 0x00 stored as the values. The presence of the 0x00 values effectively terminates the string if you try and read the field using a string variable so you only ever get the first part up to the first 0x00 character.
How can I read this field as an array of unsigned chars instead?
I am at present coding in C++, using MFC with an ODBC connection to the database which is a Btrieve database.
Many thanks for any help that you are able to provide.
Andrew
|
|
|
|
|
Sorry if this is the wrong place to post. I know this is more of a sql admin question then a programming question, but I figured the giant intellect that is code project should have a solution. We have a ms-sql server which is currently
getting an import of 2+ million records a day. the import takes any where from 1 to 2 hours. Which btw
is very impressive, but this requireds table locks
for that speed. Problem is that the data comes in
at a time early in the day, but is required for the
days processing. So users are getting locked out
of the db. Is this the best performance I can expect to get or are there other solutions. Also the
table has indexes, Ive heard this could be slowing it down a bit for the inserts. The machine has 2 gigs of ram and dual cpu's.
Thank you in advance
Take Care
Clay
|
|
|
|
|
There must be a better way, 1-2 hours is everything but impressive. Dont know much about SQLServer, but a small import like that is a matter of minutes on a properly configured Oracle.
First, turn off all indexes or drop them before starting the import, they definitely slow down record insertion and updates.
Second, try another interface / tool, but that strongly depends on the form of data you import...
Hope it helps,
dirk
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
