Ado.net DataSet Refresh

  • Thread starter Thread starter Guest
  • Start date Start date
Hello Manjree,

Yes, this is nessary. Because DataSet.Reset method not only clears all the
rows in each table, but also deletes each table in current dataset. It is
necessary for us to set up the schema again for your new dataset (primary
keys and relationships).

By the way, for your informatioin, there is anyother approach by using
TableMappings.
TableMappings could specify which table DBDataAdatper will fill in. We
could define which table in dataset we want to fill data in. Therefor, we
doesn't delete all datatables, but fill the new datas in related table.
Look at the following code snippet.

String^ allSuppliers = L"SELECT * FROM Supplier";
String^ allSamples = L"SELECT * FROM Sample";
String^ select = String::Format(L"{0};{1}",allSuppliers,allSamples);
commandBuilder = gcnew SqlCommandBuilder(adapter);
dataset = gcnew DataSet();
adapter = gcnew SqlDataAdapter(select,conn);
adapter->Fill(dataset);
DataTableCollection^ tables = dataset->Tables;
tables->default[0]->TableName = L"AllSuppliers";
tables->default[1]->TableName = L"AllSamples";

*adapter->TableMappings->Add("Table"," AllSuppliers");
//this line of code means the first table(supplier) need to fill in the
table named ALLsuppliers.
*adapter->TableMappings->Add("Table1"," AllSamples");
//this line of code means the second table(Samples) need to fill the table
named ALLSamples.
*dataset->Clear();
//This time, we need clear the dataset rather than reset all tables. This
means we still reserve the primary key in current tables. Thus we need not
set up schema again.

adapter->Fill(dataset);
DataTableCollection^ tables = dataset->Tables;
nRows = tables->default[L"AllSamples"]->Rows->Count;
str.Format("Records = %d ", nRows);
AfxMessageBox(str);

Hope this helps. Let me know if you have anything unclear. We are glad to
assist.
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Thankyou so much Wen. It's great. Table mapping saved a lot of work. I really
appreciate your help.

I'll be posting you in future whenever I'll have some doubts.

Manjree
 
You are welcome, Manjree.
I'm standing by. Feel free to update here again.:)
We are glad to assist you.

Have a great weekend.
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hello Wen

Is there any way of linking text file to a table's field. I have a table
Experiment that contains all the information about Experiment (e.g. Raman
spectra) like date, type, sample etc. I need to find some way to access that
experiment's data (which is lots of text files) from VC++ user interface.

Perhaps I did not make it very clear.

Thanks.

Manjree
 
Hello Manjree,

I'm not sure I have understood your issue very clear.
It seems you need to store a text file into Table, isn't it?

If this is the case, we usually have two options.
1) Save the file path in the field of table. If you need to read the file,
we can locate the file by file path which is stored in table.
2) Save the text file as Binary in the field of table. If necessary, we can
read binary stream from the table and print it for display.

Hope this helps. Please feel free to correct me if I misunderstood anything
here. We are glad to assist.
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Thanks Wen. You got my problem very clear. The text file contains 2 columns
(xVal and yVal) that I'ld like to store into 2 fields of the 'ExpData' table
as binary.

To save the text files into ExpData table using VC++ interface, is there any
way of browsing the directory and getting the file name/path rather then
typing the full pathname for each file in an edit control of the dialog box?
Means, How to add the functionality of browsing the filename in the
directories?

Thanks again.

Manjree
 
Hello Wen

I found it out. I have to use CFileDialog fopendialog().
Now the problem is that when I click on 'Browse' button of 'AddData' dialog
box it does not open the file open dialog box. Though I have added function
on BnClicked event of 'Browse' button in which I am using CFileDialog
fopendialog(). The problem is that it does not go to the BnClicked function.

I need to know is there any way of executing a BnClicked function of a
button of a dialog box?

Thanks

Manjree
 
Hello Wen

I sorted it out using CFileDialog fopendialog(). If there is any other way
please let me know.

Another thing is reading two columns of a text files into two seperate
fields of a table (xVal and yVal). When we use Byte Stream it takes whole
text file as a stream.

Thanks.

Manjree
 
Hello Manjree,
Thanks for your reply.
It seems your objective is to open a File Dialog (CFileDialog) when
clicking "Browse" button in "AddData" Dialog.
I'm not sure what the "AddData" Dialog is. Is it CFileDialog? Actually, I
cannot found "Browse" button in CFileDialog. There are "Open" button and
"Cancel" button in CFileDialog.
Am I missing something here?

Have a great day,
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hello Wen

This issue is resolved now. I am able to read a text file and store as
binary data. But the next issue is to store two columns (xVal and yVal) of
the text file into two different fields (xValue and yValue) of the 'ExpData'
table.
I guess there is StreamReader class that can be used to read a line of
the stream but again that line contains two double values that need to be
stored (appended) in xValue and yValue fields of the table.

Thanks for your time.

Manjree
 
Hello Manjree,
Thanks for your reply.

Yes, as you seen, StreamReader class provides a feature to read string from
Txt file.

http://msdn2.microsoft.com/en-us/library/system.io.streamreader.readline.asp
x
[StreamReader.ReadLine Method]

In my opinion, we could read one line from txt file and split it into
xValue and yValue. Covent each Vaule(xValue and yValue) to binary by
Encoding.GetBytes method and store them into "ExpData" table. Does this
work in your scenario?
http://msdn2.microsoft.com/en-us/library/ds4kkd55.aspx
[Encoding.GetBytes Method (String)]

Hope this helps,
Best regards,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hello Wen

Thanks for the reply. I guess the best thing will be to store the file as
binary in one field and when reading it back for display or any other
processing then split it into xVal and yVal.

Another thing is that the 'Console' doesn't work in my VC++ application,
e.g. the following code does not display anything on console:

Console::WriteLine( "<none>" );

any idea why?

Thanks again.

Manjree
 
Hello Manjree
Thanks for your reply.

Console::WriteLine Writes the specified data to the standard output stream.
Console::Out could get the standard output stream.

If you face an issue that Console::WriteLine doesn't display anything on
console, I would like to suggest you check current standard output stream
(Console::Out).

http://msdn2.microsoft.com/en-us/library/system.console.out.aspx
[Console.Out Property]

Would you please let me know what does Console::out return? (is it null?)

I'm looking forward to your reply.
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hello Wen

Thanks for the reply.

My VC++ application is an MFC application. I guess Console::Write works in
'CLR Console' application.

Is there some way of declaring the size of stored procedures parameters of
type VarChar (MAX) or VarBinary(MAX) in VC++ ADO.NET code, like I need to
declare an array of 'Byte' type that will store the binary data from the
'ExpData' table's 'data' column which has been declared of datatype
VarBinary(MAX) . I am doing some thing like following as the max size limit
of this type is (2^31-1).

array<Byte>^ data = gcnew array<Byte>(2^31-1);

Is there any way of knowing the exact size instead of giving the maximum
limits.
The same problem is with VarChar(MAX). How do we declare procedure's
parameter's size in ADO.NET.


Thanks for your time.

Manjree
 
Hello Wen

In continuation to my earlier reply I am trying to read a binary value from
a DataRow object and then output that data to a file. I am doing the
following:

array<Object^>^ search = gcnew array<Object^>(1);
search[0] = dataID;
DataRow^ row = expDataTable->Rows->Find(search);
array<Byte>^ data = gcnew array<Byte>(2^31-1);
data = (Byte[]) (row->default[L"data"]);

to read data from the VarBinary(MAX) field 'data' of 'expDataTable'.

its giving following compilation error in the last line.

error C2440: 'type cast' : cannot convert from 'System::Object ^' to
'unsigned char []'
1> There are no conversions to array types, although there are
conversions to references or pointers to arrays
1>.\DatabaseConn.cpp(106) : error C2440: '=' : cannot convert from 'unsigned
char []' to 'cli::array<Type> ^'

any suggestion will be appreciated.

Manjree
 
Hello Manjree,

I have repro this issue on my side.
In C# world, we use following line to retrieve Byte array from DataTable.
Byte[] b = (Byte[])ds.Tables[0].Rows[0][1];
However, it seems there is something different in C++.net world.

I need to perform more research on this issue and reply here as soon as
possible.
If you have any more concerns on it, please feel free to post here.

Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hello Manjree,
Thanks for your wait.

I searched on internet and found out a KB article about how to retrieve
BLOB by ADO.net in C++.net.

http://support.microsoft.com/kb/317044/EN-US/
[HOW TO: Read and Write a File to and from a BLOB Column by Using ADO.NET
and Visual C++ .NET]

Would you please check if this method works for you?
If you have any issue on this, please feel free to update here. I'm glad to
assist you.

Best regards,
Wen Yuan
Microsoft Online Community Support
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Dear Wen

I tried the method but its giving compilation error in the following
GetBytes() method when used first time to determine the size of the data
array.

int size = (Convert::ToInt32(dr->GetBytes(dataCol, 0, 0, 0,
Int32::MaxValue)));
array<Byte>^ data = gcnew array<Byte>(size);
dr->GetBytes(dataCol, 0, data, 0, data->Length);

Where dr is the DataReader. The error is:

error C2664: '__int64
System::Data::Common::DbDataReader::GetBytes(int,__int64,cli::array<Type,dimension>
^,int,int)' : cannot convert parameter 3 from 'int' to
'cli::array<Type,dimension> ^'


Is there any way of using DataSet instead of DataReader?

Thanks for your time.

Manjree
 
Hello Manjree,
Thanks for your reply.

It seems you'd like to retrieve byte array from Dataset, rather than
DataReader. However, I searched on MSDN website. There is No article
related to this so far.

For DataSet, I have tried the following method on my side.
array<Byte>^ b=(array<Byte>^)ds->Tables[0]->Rows[0]->default[2];

Would you please try this method and let me know if this is what you need.
I'm glad to assist you.
System::Data::SqlClient::SqlConnection^ cn = gcnew
System::Data::SqlClient::SqlConnection("Data Source=.;Initial
Catalog=ImageDB;Integrated Security=True");
System::Data::SqlClient::SqlCommand^ cmd = gcnew
System::Data::SqlClient::SqlCommand("SELECT * from Nodes", cn);
System::Data::SqlClient::SqlDataAdapter^ sda=gcnew
System::Data::SqlClient::SqlDataAdapter(cmd);
System::Data::DataSet^ ds=gcnew System::Data::DataSet();
sda->Fill(ds);

array<Byte>^ b=(array<Byte>^)ds->Tables[0]->Rows[0]->default[2];

Hope this helps,
Best regards,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hello Wen

I tried the method. My code is:

DataRow^ row = expDataTable->Rows->Find(search);
array<Byte>^ data = gcnew array<Byte>(2^31-1);
data = (array<Byte>^)(row->default[L"data"]);
int size = data->GetUpperBound(0);


But it is throwing following exception in line 3:

"Object reference is not set to an instance of an object."

I guess we are using an array declaration as casting operator which is not
working.

If I use a DataReader that was giving the compilation error as I mentioned
in my earlier reply.

Is there any way out of it?

Thanks for any information provided.

Manjree
 
Back
Top