none
How to update the Data Base with the help of Data Set from Command Builder and Data Adapter RRS feed

  • Question

  •  private static void InsertToLocalSqlTallyData(DataSet TallyDataSet)
            {
                DataSet SqlTallyDataSet=new DataSet();
                SqlTallyDataConnection = new SqlCeConnection("Data Source=|DataDirectory|\\TallyData.sdf;Persist Security Info=False;");
                SqlTallyDataConnection.Open();
                SqlCeDataAdapter SqlTallyDataAdapter = new SqlCeDataAdapter("SELECT * FROM [Table]", SqlTallyDataConnection);
                SqlTallyDataAdapter.FillSchema(SqlTallyDataSet, SchemaType.Source, "Table");
                SqlTallyDataAdapter.Fill(SqlTallyDataSet);
                SqlCeCommandBuilder SqlTallyCommandBuilder = new SqlCeCommandBuilder(SqlTallyDataAdapter);
                SqlTallyCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
                SqlTallyDataAdapter.MissingMappingAction = MissingMappingAction.Passthrough;
                SqlTallyDataAdapter.UpdateCommand = SqlTallyCommandBuilder.GetInsertCommand();
                SqlTallyDataSet.Merge(TallyDataSet);
                SqlTallyDataAdapter.Update(SqlTallyDataSet);
                SqlTallyDataConnection.close();
       
            }

    please go through the code and Help me..

    I'm really bugged with it.. 

    I'm merging the data from the diffrent data in my data set with the schema from the Sql but the thing is I'm unable to update it in the DataBase 

    Because I don't how to use the Command builder Command to update the DataBase on the Data Set 

    Else I've to use the following code ...

    for (int i = 0; i < TallyDataSet.Tables[0].Rows.Count; i++)
                //{
                //    for(int k=0;i<ColoumnCount;i++)
                //      SqlTallyDataAdapter.InsertCommand.ExecuteNonQuery();
    
                //}

    which i really hate ...

    For Loop? man I have 1000 records...please help me out...

    Saturday, February 11, 2012 12:43 PM

Answers

  • Of course I cannot test it, the commandbuilder is dynamic component which adds its statements to the dataadapter,

    I think you can try it like this. (Be aware that in the passed dataset the rowstates should be changed, inserted or deleted and not set to unchanged (for instance by using the acceptchanges)

            private static void InsertToLocalSqlTallyData(DataSet TallyDataSet)
            {
                using (SqlCeConnection SqlTallyDataConnection = new SqlCeConnection("Data Source=|DataDirectory|\\TallyData.sdf;Persist Security Info=False;"))
                {
                    DataSet SqlTallyDataSet = new DataSet();
                    SqlTallyDataConnection.Open();
                    SqlCeDataAdapter SqlTallyDataAdapter = new SqlCeDataAdapter("SELECT * FROM [Table]", SqlTallyDataConnection);
                    SqlTallyDataAdapter.FillSchema(SqlTallyDataSet, SchemaType.Source, "TallyDataSet");
                    SqlTallyDataSet.Merge(TallyDataSet);// be aware that the table should also be set with its tablename property like that
                    SqlCeCommandBuilder SqlTallyCommandBuilder = new SqlCeCommandBuilder(SqlTallyDataAdapter);
                    SqlTallyDataAdapter.Update(SqlTallyDataSet);
                }
            }


    Success
    Cor



    • Edited by Cor Ligthert Sunday, February 12, 2012 12:50 PM
    • Marked as answer by SKracker Tuesday, February 14, 2012 6:11 AM
    Sunday, February 12, 2012 12:39 PM
  • For bulk copy in SqlCe I'm using ErickEj.SqlCe
    • Marked as answer by SKracker Tuesday, February 14, 2012 5:03 AM
    Monday, February 13, 2012 5:26 AM

All replies

  • Of course I cannot test it, the commandbuilder is dynamic component which adds its statements to the dataadapter,

    I think you can try it like this. (Be aware that in the passed dataset the rowstates should be changed, inserted or deleted and not set to unchanged (for instance by using the acceptchanges)

            private static void InsertToLocalSqlTallyData(DataSet TallyDataSet)
            {
                using (SqlCeConnection SqlTallyDataConnection = new SqlCeConnection("Data Source=|DataDirectory|\\TallyData.sdf;Persist Security Info=False;"))
                {
                    DataSet SqlTallyDataSet = new DataSet();
                    SqlTallyDataConnection.Open();
                    SqlCeDataAdapter SqlTallyDataAdapter = new SqlCeDataAdapter("SELECT * FROM [Table]", SqlTallyDataConnection);
                    SqlTallyDataAdapter.FillSchema(SqlTallyDataSet, SchemaType.Source, "TallyDataSet");
                    SqlTallyDataSet.Merge(TallyDataSet);// be aware that the table should also be set with its tablename property like that
                    SqlCeCommandBuilder SqlTallyCommandBuilder = new SqlCeCommandBuilder(SqlTallyDataAdapter);
                    SqlTallyDataAdapter.Update(SqlTallyDataSet);
                }
            }


    Success
    Cor



    • Edited by Cor Ligthert Sunday, February 12, 2012 12:50 PM
    • Marked as answer by SKracker Tuesday, February 14, 2012 6:11 AM
    Sunday, February 12, 2012 12:39 PM
  • nope dosen't work....

    no error.. nothing..... :(

    you already explained that Sql Data base requires the ExecuteNonQuery();

    in one of your posts.

    DataAdapter.Update(); is not working...

    and don't worry about the merging DataSet1.merge(DataSet2); because I made sure no error occur in merging....


    Monday, February 13, 2012 4:21 AM
  • anyhow I'm using SqlBulkCopy now if you got new thing on this please suggest me...
    Monday, February 13, 2012 4:48 AM
  • For bulk copy in SqlCe I'm using ErickEj.SqlCe
    • Marked as answer by SKracker Tuesday, February 14, 2012 5:03 AM
    Monday, February 13, 2012 5:26 AM
  • And you are sure all rows are marked to have changes before the update starts, otherwise the Update simple updates zero rows, so no error, nothing. 

    If you created a new approach than mark one reply as answer (even your own) and start a new question.


    Success
    Cor

    Monday, February 13, 2012 8:32 AM
  • And you are sure all rows are marked to have changes before the update starts, otherwise the Update simple updates zero rows, so no error, nothing. 

    If you created a new approach than mark one reply as answer (even your own) and start a new question.


    Success
    Cor


    Hello Cor Lighthert,

    Thanks for suggesting me I surely did some corrections in my posts,The DataSet has the Data but it isn't updating in the Database,you have explianed in one of the posts the reason why we can't update in sqlserver I can't search the post but you stated the reason why Update won't work.

    yet I tried giving the absolute path for the SqlTallyDataConnection(Connection String) Data Source=D:\\sreekanth\\project\\TBTest\\TallyDataTest\\TallyData.sdf;Persist Security Info=False;

    and then used Update() it succefully worked out....

    Thankfully,

    Sreekanth B
    • Edited by SKracker Tuesday, February 14, 2012 6:11 AM Spelling Mistake
    • Proposed as answer by XuYongSG Wednesday, March 27, 2013 6:28 AM
    Tuesday, February 14, 2012 6:10 AM
  • what you need is only to put the following to your source dataset:

    daLoc.AcceptChangesDuringFill = False

    I faced the same problem and read a lot via google, until i found this article.

    [Now, many people are aware of the Merge method of the DataSet, DataTable, DataRow and they
    mistakenly believe that Merging two datasets will cause the merged datasets rows
    to have a RowState of Added.  Then they try to use a DataAdapter to add these
    rows and nothing happens.  Let me stop here for a second.  If you test your
    DataSet with the HasChanges method and it returns false, you can call Update
    against it until the cows come home and nothing is going to happen to the
    database.  Only if there are changes registered and only if there is a valid
    Insert/Delete/Update command is anything on the back end going to happen.  There
    are no exceptions.]

    details, refer to    http://www.knowdotnet.com/articles/datasetmerge-printable.html

    Wednesday, March 27, 2013 6:33 AM