index > Visual C# Express Edition > SQL Server Compact Edition, TableAdapters and the Update, Fill and Insert methods

SQL Server Compact Edition, TableAdapters and the Update, Fill and Insert methods

Hi, all.

I am trying to populate a SQL Server Compact Edition database with data I've retrieved from an Oracle database. I've captured the Oracle data using an OracleDataReader object, but I'm having trouble getting it into SSCE. After running this code, I have a form that pops up with a datagrid that remains unpopulated. I don't know much about using tableAdapters, but here's what I've cobbled together so far:

                string sqlStatement = "[some SQL statement]";
                OracleCommand cmd = new OracleCommand(sqlStatement, conn);
                cmd.CommandType = CommandType.Text;
                OracleDataReader dr = cmd.ExecuteReader();

                randomDataSet1.tableDataTable dt = new RandomDataSet1.tableDataTable();
                dt.Load(dr);
                dr.Close();
                tableTableAdapter.Update(dt);
                tableTableAdapter.Fill(dt);

I had a "while (dr.Read())" loop prior to this that inserted each item in dr one at a time into the database. However, with many thousands of records, it was taking forever, so I am trying to optimize the data entry. Despite the fact it was incredibly slow, the "while (dr.Read())" loop worked just fine.

Anyway, is there something I'm missing here? I'd appreciate any help in figuring out a faster method for entering this data into my database. Thanks in advance!

--Raj

Changed TypeHarry ZhuMSFT, ModeratorTuesday, October 13, 2009 5:00 AM

RajAgainstTheMachine Tuesday, October 06, 2009 1:06 AM

Hi,

It seems the data schema is not compatible.

Please try to use
DataTable dt=new DataTable();
instead of :randomDataSet1.tableDataTable

I have not idea OracleDataReader would work properly as other datareader.
If it does not.

You might have to use while (read) to handler the data.
Could you also upload code relating to it?

Harry

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Harry Zhu Wednesday, October 07, 2009 8:17 AM

Thanks for the response. Here is my code using the Insert statement:

                conn.Open();

                string sqlStatement = "[some SQL statement]";
                OracleCommand cmd = new OracleCommand(sqlStatement, conn);
                cmd.CommandType = CommandType.Text;
                OracleDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    dwAssetsTableAdapter.Insert(dr["STUDIO"].ToString().Trim(), DateTime.Parse(dr["VIEW_DT"].ToString().Trim()), dr["SERIES"].ToString().Trim(), dr["CLIP_OR_EPISODE"].ToString().Trim(), long.Parse(dr["IMPS_ESTIMATED"].ToString().Trim()), dr["VIDEO_TITLE"].ToString().Trim(), long.Parse(dr["ASSET_ID"].ToString().Trim()), dr["SERIES_CAT"].ToString().Trim(), int.Parse(dr["CONTENT_START_CNT"].ToString().Trim()));
                }

                dwAssetsTableAdapter.Update(tVcomPartnerToolDataSet1.dwAssets);
                dwAssetsTableAdapter.Fill(tVcomPartnerToolDataSet1.dwAssets);

                dr.Close();
                conn.Close();
                dr.Dispose();
                conn.Dispose();

The problem with using

DataTable dt=new DataTable();

instead of

RandomDataSet1.tableDataTable dt = new RandomDataSet1.tableDataTable();

is that the Update and Fill methods only take datatables of type RandomDataSet1.tableDataTable (at least that is what Intellisense is telling me). Any other suggestions or tips on how I can get this to work faster? Thanks!

--Raj

RajAgainstTheMachine Thursday, October 08, 2009 4:50 PM

Hi,

What is the value of dt (DataTable)? If we can get the data of Oracle database, we can try to update sql database in other means.

Harry

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Harry Zhu Friday, October 09, 2009 1:48 AM

We are changing the issue type to “General Discussion” because you have not followed up with the necessary information. If you have more time to look at the issue and provide more information, please feel free to change the issue type back to “Question” by opening the Options list at the top of the post window, and changing the type. If the issue is resolved, we will appreciate it if you can share the solution so that the answer can be found and used by other community members having similar questions.

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Harry Zhu Tuesday, October 13, 2009 1:54 AM

You can use google to search for other answers

Custom Search

More Threads

• Web Browser

• OnServerClick using a void in codebehind

• Help with xml storing of data

• I just edited the code.

• sql template missing

• clear DataGridView

• how can i recieve e-mail from a stmp server (by c# program)

• C# switch statement and goto.

• Can I pass a value from InitializeComponent to the rest of my form code?

• How to check my file is opened or not?