none
Updating DataTable: Two ways: with/without ConnectionString RRS feed

  • Question

  • The code below doesn't show the runtime updated dataTable. How can I make the below code to show the updated DataTable. 

                    PressureVessels.Database.UserDefinedDataSet userDefinedDataSet = (PressureVessels.Database.UserDefinedDataSet)                   (this.FindResource("userDefinedDataSet"));
                    PressureVessels.Database.UserDefinedDataSetTableAdapters.MaterialTableAdapter userDefinedDataAdapter = new                         Database.UserDefinedDataSetTableAdapters.MaterialTableAdapter();
                    userDefinedDataAdapter.Fill(userDefinedDataSet.Material);
                    CollectionViewSource userDefinedView = (CollectionViewSource)(this.FindResource("material"));
                    userDefinedView.View.MoveCurrentToFirst();
                    DataTablesCollectionView = userDefinedView.View;

    However, the code below works as I am connecting directly to the DataBase. 
                    //string path = (new System.Uri(System.Reflection.Assembly.GetExecutingAssembly().CodeBase)).AbsolutePath;
                    //string drive = System.IO.Path.GetPathRoot(path);
                    //string conString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=" + drive + @"Pressure                                                 Vessels\PressureVessels\Database\UserDefined.mdf;Integrated Security=True;";
                    ////string conString = Properties.Settings.Default.UserDefinedConnectionString;
                    //using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(conString))
                    //{
                    //    con.Open();
                    //    string query = "Select * from Material";
                    //    DataTable dt = new DataTable();
                    //    SqlDataAdapter adapter = new SqlDataAdapter();
                    //    adapter.SelectCommand = new SqlCommand(query, con);
                    //    SqlCommandBuilder com = new SqlCommandBuilder(adapter);
                    //    adapter.Fill(dt);
                    //    materialGrid.DataContext = dt;
                    //    //materialWindowDG.ItemsSource = dt.DefaultView;
                    //    materialWindowDG.UpdateLayout();
                    //}

               
    Wednesday, November 12, 2014 1:16 PM

All replies

  • I already know how to do the same task using SqlConnection and SqlCommandBuilder. 

    But I would like to doing something like this:

       Database.UserDefinedDataSet userdefinedDataSet = new Database.UserDefinedDataSet();
                        Database.UserDefinedDataSet.MaterialDataTable materialTable = new Database.UserDefinedDataSet.MaterialDataTable();
                        Database.UserDefinedDataSet.MaterialRow newMaterialRow = userdefinedDataSet.Material.NewMaterialRow();
                        newMaterialRow.Rp1 = 2.44M;
                        newMaterialRow.Material_name = "this way";
                        newMaterialRow.Id = 2;
                        userdefinedDataSet.Material.Rows.Add(newMaterialRow);                 
                        Database.UserDefinedDataSetTableAdapters.MaterialTableAdapter materialTableAdapter = new Database.UserDefinedDataSetTableAdapters.MaterialTableAdapter();
                        materialTableAdapter.Fill(userdefinedDataSet.Material);           
                        materialTableAdapter.Update(userdefinedDataSet.Material);

                        materialGrid.DataContext = userdefinedDataSet.Material;                   

                        materialWindowDG.UpdateLayout();

    When I comment out the adapter then the dataGrid displays the values that I have entered. Otherwise, it shows the values that are in dataBase. 

    Friday, November 7, 2014 1:13 PM
  • I'm not sure I understand your question. TableAdapers *only* work with connections to databases. Their only purpose is to get data from the database and save data to the database.

    You can work directly with the DataSet/DataTable, as you have already done. When you manually put values into the DataTable to which your grid is databound, you'll see those values in the grid, which you have seen.

    So, what's your question? If you want to manually add data to a DataTable, without getting the data from a database using a TableAdapter, then you can do that. There's nothing wrong with that scenario.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, November 10, 2014 12:46 AM
  • The problem is that the DataAdapter doesn't update the database with the above code? 

    However, it works when I use the code below.

                        string path = (new System.Uri(System.Reflection.Assembly.GetExecutingAssembly().CodeBase)).AbsolutePath;
                        string drive = System.IO.Path.GetPathRoot(path);
                        string conString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename="+drive+@"Pressure Vessels\PressureVessels\Database\UserDefined.mdf;Integrated Security=True;";
                        //string conString = Properties.Settings.Default.UserDefinedConnectionString;
                        using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(conString))
                        {
                            con.Open();
                            string query = "Select * from Material";
                            DataTable dt = new DataTable();
                            SqlDataAdapter adapter = new SqlDataAdapter();
                            adapter.SelectCommand = new SqlCommand(query, con);
                            SqlCommandBuilder com = new SqlCommandBuilder(adapter);
                            adapter.Fill(dt);          
                            for (int i = 0; i < cols.Count; i++)
                            {
                                if (dt.Rows.Count > rows[i])
                                    dt.Rows[rows[i]][cols[i]] = cellValues[i];
                                else
                                {
                                    while (dt.Rows.Count <= rows[i])
                                    {
                                        dt.Rows.Add(dt.NewRow());
                                    }
                                    dt.Rows[rows[i]][cols[i]] = cellValues[i];
                                }
                            }

                            adapter.UpdateCommand = com.GetUpdateCommand();
                            adapter.InsertCommand = com.GetInsertCommand();
                            adapter.Update(dt);
                            materialGrid.DataContext = dt;
                            materialWindowDG.UpdateLayout();

                            cols.Clear();
                            rows.Clear();
                            cellValues.Clear();
                        }

    But there I have used DataConnectionString. 


    Tuesday, November 11, 2014 11:50 AM
  • Hello Bopy,

    >> The problem is that the DataAdapter doesn't update the database with the above code?

    Does it throw any exception or run it without any exception and just not update database?

    And I am a bit confused about the “Database.UserDefinedDataSet”, what is the type? Could you please tell how you create it, so that we can clear know what exactly happens in your project.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 12, 2014 7:37 AM
    Moderator
  • It's type is Microsoft SQL Server. 

    Provider: .Net FrameWork data provider for microsoft sql server.

    When I set the Identity specification to true. Then something strange happens. If I wouldn't assign newMaterialRow.Id = 2; , the compiler gives an error for not assigning the Id. 

    Could it be because of Copy to Output Directory : Copy always. 

    Hence instead after altering the actual database it changes the copy one?

    When I change it to Do not copy then I get the following error:

    An attempt to attach an auto-named database for file G:\Pressure Vessels\PressureVessels\bin\Debug\Database\UserDefined.mdf failed

    A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

    It seems the dataAdapter is trying to change the database inside the bin folder rather than the actual database/Table. How can I make the DataAdapter to understand the actual location of the Table (G:\Pressure Vessels\PressureVessels\Database\UserDefined.mdf)?

    Wednesday, November 12, 2014 11:13 AM
  • Hi Bopy,

    What project are you working with, console application, windows form or others? And where does the DataAdapter come from, from the typed DataSet item or something else? Please provide some description about your project so that we can understand clearly.

    Thursday, November 13, 2014 7:20 AM
  • It's a WPF application for Windows. I created the DataBase inside the visual studio as the server based database using datasets for the same project. It's microsoft sql database. The dataAdapter must be from within the project itself. However, I am not sure, it could be from bin folder as well. As i described it in my previous reply. 
    Thursday, November 13, 2014 8:49 AM
  • Hello Bopy,

    A database project is also based on a database, so we still need to connect to the database firstly by using connection string.

    Regards.

    Friday, November 21, 2014 8:16 AM
  • I thought that the DataAdapter does the job of the connection string! It seems that the DataAdapter only connects to the data that are temporary saved in the bin folder whereas the SqlDataAdapter connects directly to the source data. 

    Even why I change the DataBase using Sql Server. I have to go the DataSource => configue data source with wizard, then untick => finish, then again tick, in order to the updated databases.  

    Friday, November 21, 2014 1:10 PM
  • Hello Bopy,

    As Bonnie describes, TableAdpater *only* work with connections to databases. Their only purpose is to get data from the database and save data to the database. There is another object which is used to perform the connection, for example, in ADO.NET, it is the DbConnection. In your project, I think inside it, it actually still uses this object and it should do the connection automatically.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 24, 2014 9:07 AM
    Moderator
  • " In your project, I think inside it, it actually still uses this object and it should do the connection automatically." I still don't understand if DataAdapter should do it automatically why then it's not connecting to the actual source but instead to the files that are in the bin folder?

    I have got two files that is .mdf and .xsd for each database as shown below

    The property of .xsd is

    The property of .mdf is

    Are there something in the properties that could cause the effect of not connecting to the actual source. The database is a folder inside the project. 

    Monday, November 24, 2014 9:24 AM
  • Hello,

    >> I still don't understand if DataAdapter should do it automatically why then it's not connecting to the actual source but instead to the files that are in the bin folder

    Do you have check the connection string(I am not sure about your current project, usually it is stored in app.config or web.config) to see which database it connects?

    >> Are there something in the properties that could cause the effect of not connecting to the actual source.

    I see that in the properties windows of the .mdf, the Copy to Output Directory is set to be Copy always which means the database file is copied from the project directory to the /bin directory every time you build your application. Therefore, if you build your application and save changes to the file in the /bin directory, those changes are overwritten the next time that the original file is copied to the /bin directory.

    Changing it to Do not copy and rebuild your application to see if your original database would be connected.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 25, 2014 9:27 AM
    Moderator
  • I changed the connectionstring to the actual path of the DataBase in application.properties as

    After doing so I am getting the following error:

                Database.UserDefinedDataSet userdefinedDataSet = new Database.UserDefinedDataSet();
                Database.UserDefinedDataSet.MaterialDataTable materialTable = new Database.UserDefinedDataSet.MaterialDataTable();
                Database.UserDefinedDataSet.MaterialRow newMaterialRow = userdefinedDataSet.Material.NewMaterialRow();
                newMaterialRow.Rp1 = 2.44M;
                newMaterialRow.Material_name = "this should appear";            
                userdefinedDataSet.Material.Rows.Add(newMaterialRow);
                Database.UserDefinedDataSetTableAdapters.MaterialTableAdapter materialTableAdapter = new Database.UserDefinedDataSetTableAdapters.MaterialTableAdapter();
                //materialTableAdapter.Connection.ConnectionString = builder.ConnectionString;
                materialTableAdapter.Fill(userdefinedDataSet.Material); //Erro occurs here
                materialTableAdapter.Update(userdefinedDataSet.Material);
                materialGrid.DataContext = userdefinedDataSet.Material;
                materialWindowDG.UpdateLayout();

    Tuesday, November 25, 2014 10:02 AM
  • Hello Bopy,  

    For this error, is may be related with usercontrol, please check this discussion:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/94b646a1-0e01-4fd4-9c0d-e81c40fd73d4/an-attempt-to-attach-an-autonamed-database-for-file?forum=sqlexpress

    Please also try to use the specific database name as Database = XXX to see if it works.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 28, 2014 8:00 AM
    Moderator
  • Thanks Fred. But I don't think my problem is the same because I don't have a UserControl that's bind to the database. "You experience this problem if the user control contains a data-binding object that uses an attached local database file"

    It could be related to user instance. I have given the direct connection string in my app config. 

    Sorry I didn't understand what you meant by specific database name as Database = XXX?

    Saturday, November 29, 2014 8:27 PM
  • Hello ,

    >>Sorry I didn't understand what you meant by specific database name as Database = XXX?

    I mean try to specify the database name rather than provide the file path as:

    Server=myServerAddress;Database=myDataBase;User Id=myUsername;
    Password=myPassword;

    After reading this issue, I realize that you would have a copied database, do you have a check the copied one to see if it is changed after you run these code?

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 1, 2014 10:04 AM
    Moderator
  • I'm not sure I understand your question. TableAdapers *only* work with connections to databases. Their only purpose is to get data from the database and save data to the database.


    Bonny, 

    I know what you intending to write, but the first Data team made it also to use for converting in the beginning recordsets to datatables.

    :-)


    Success
    Cor

    Friday, December 5, 2014 3:40 PM
  • Bopy,

    DataTables are meant to be used in a multi user environment. Therefore the most simple (and safe) way is to Fill again new the datatable (after that it is cleared because the Fill adds at the end).

    Don't worry about the time it takes, because probably that dynamically created SQL statement is still in the Server cache and therefore the the time it takes is mostly ignorable.


    Success
    Cor

    Friday, December 5, 2014 3:43 PM
  • Bonny, 

    I know what you intending to write, but the first Data team made it also to use for converting in the beginning recordsets to datatables.

    :-)


    I didn't know that Cor. But you know I don't like TableAdapters anyway, so I never paid much attention to them!  ;-)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, December 5, 2014 4:18 PM
  • Bonnie, 

    In this you are right, but was it me or you with that mind twist. The OP is talking about the DataAdapter, and of course the Table adapter does work only with strongly typed datasets. And I know you don't like them but I've very good experiences with those.


    Success
    Cor

    Friday, December 5, 2014 6:06 PM