locked
Using SQL in C# - Cannot insert data into SQL table

    Question

  • I have been teaching myself C# this year and have now decided that I need to venture into using SQL
    code in my learning. I have read books and the examples are OK, but they concentrate on using
    datagridviews and binding. Well I am afraid in the real world, you often want to do database
    activities that are not related to screen displays. So although these examples are good as an
    introduction, beyond that, they are not much use.

    I am using MS Visual Studio 2008 Express edition and SQL server 2008 express edition.

    At this stage, I have just been writing some mickey mouse code to see how to do things and I cannot
    get some of it working, so I need help.

    I have created a database via the Database Explorer and created a table and populated it with some data.

    I have successfully selected data from this table using both SQL Command based code with an SQL Reader
    or using SqlDataAdapter, DataSet and DataTable components to access the data. Both have worked perfectly.

    It is when I try to update the table that I am having problems and I have tried a number of different
    ways. Below is my latest offering, using an SqlDataAdapter. I have attempted to insert rows into the table.
    The strange thing is that the inserted rows exist while the program is running but not after it has ended,
    Having said that, they are not visible via the Database Explorer while it is running. So what am I doing
    wrong and why are these inserted row not being flushed to the database table. To prove that they exist,
    I have inserted them and then executed the select code to display them and they were selected successfully.
    But if the program ends and then is restarted and try's to select them again, they are not there.

    So, what am I doing that is so fundamentally wrong and where are there good examples on the internet. I have
    waded through the MS help and find loads of info on the various bits and pieces, but nothing that helps me
    with my problem, or pulls all the pieces together in a coherent example.

    Also, the following questions apply:-

    1) Is this the best way to access an SQL database in these circumstances and if not, what is?
    2) I have used transactions with some of my code, but not with the example below. Cannot make sense of the MS
    help regarding use of transactions. Should transaction be used with adapters, or are they implied? Personally I think
    they should be used, but don't yet know enough about MS SQL and C#.
    3) Should I be using Table Adapters instead of Data Adapters?
    4) What opens the connection when you use adapters?
    4) If I am doing anything else that is fundamentally wrong, then please advise. Bear in mind, this is just
    quick code to see how things work, it is not the finished article.

    Some notes... DataAccess.GetConnectionString() uses the ConfigurationManager class to access the connection
    string stored in the app.config file. I have also used hard coded strings and it has made no difference.


        ///////////////////////////////////////////////////////////////////////////////////////////////
        // TEMPORARY GAME INSERTS
        ///////////////////////////////////////////////////////////////////////////////////////////////
        public void InsertGamesDetails() {
    
            string connectionString = DataAccess.GetConnectionString();
            SqlConnection sqlConnection = new SqlConnection( connectionString );
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter( "SELECT * FROM GamesDetails", sqlConnection );
    
            try {
                DataSet dataSet = new DataSet();
                DataRow dataRow;
    
                // Create command builder. This line automatically generates the update commands for you, so you don't 
                // have to provide or create your own.
                SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder( sqlDataAdapter );
    
                // Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary
                // key & unique key information to be retrieved unless AddWithKey is specified.
                sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    
                sqlDataAdapter.Fill( dataSet, "GamesDetails");
    
                dataRow = dataSet.Tables["GamesDetails"].NewRow();
                dataRow["GameNo"] = 2;
                dataRow["CellIndex"] = 11;
                dataRow["CellValue"] = 1;
                dataSet.Tables["GamesDetails"].Rows.Add( dataRow );
    
                sqlDataAdapter.Update( dataSet, "GamesDetails" );
            }
            catch( Exception ex ) {
                Console.WriteLine( "Insert GamesDetails failed" );
                Console.WriteLine( "    ERROR: " + ex.Message );
                Console.WriteLine( "    Conn.: " + connectionString );
            }
            finally {
                if( sqlConnection.State == ConnectionState.Open ) {
                    sqlConnection.Close();
                }
            }
        }
    
    Tuesday, December 08, 2009 2:30 PM

Answers

  • It copies the db each time for letting you debug your application with the same starting point each time.
    It copies the db in bin\debug (or bin\release) when you compile, depending on the compile settings.

    Let's say you're compiling in debug mode.
    It copies the db in the bin\debug directory.
    It will use the db in bin\debug.
    If you want to have the db with the inserted rows as a starting point, overwrite the top level db with the db from bin\debug before recompiling again.

    • Marked as answer by pso57 Monday, March 08, 2010 3:03 PM
    Monday, January 04, 2010 3:12 PM

All replies

  • Hi,

    The new added data is still in dataset which is in memory and will be collected when the application quit.
    We need to add appropriate command to the dataadapter.
    When you call the Update method, the DataAdapter analyzes the changes that have been made and executes the appropriate command (INSERT, UPDATE, or DELETE). When the DataAdapter encounters a change to a DataRow, it uses the InsertCommand, UpdateCommand, or DeleteCommand to process the change.

    For more info , please see:
    http://msdn.microsoft.com/en-us/library/33y2221y.aspx
    http://msdn.microsoft.com/en-us/library/system.data.common.dbdataadapter.insertcommand.aspx

    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.
    Thursday, December 10, 2009 8:30 AM
  • I'm afraid I do not understand your reply.

    I have executed the Update method on the DataAdapter and the data is not in the table when the program quits.

    I have also created the SqlCommandBuilder option and according to the write up, this creates the Insert, Update and Delete commands for you. The articles you pointed me to say that an exception will be thrown if these commands do not exist, no exception is thrown.

    Interestingly, you indicate that the data will be commited when the program quits, what happens if you want to commit data now, not when the program commits?

    Thursday, December 10, 2009 10:33 AM
  • I am disappointed that even though 92 people have viewed this, only one person has responded, with an answer that I do not understand in relationship to what I have done and consequently it does not help. Can no one else offer any suggestions or even attempt to clarify the answer I have received.

    Disappointed.
    Wednesday, December 16, 2009 4:25 PM
  • What is the ConnectionString? Does it match the database you're looking at?

    I had the same problem once and I finally found that the db I was updating was not the one in the database explorer, so I was never seeing any changes.

    Wednesday, December 16, 2009 4:52 PM
  • Put a dataSet.AcceptChanges() between your new row add and your call to Update().
    Wednesday, December 16, 2009 5:35 PM
  • Don't put a dataSet.AcceptChanges before Update, this would change the RowState from Added to Unchanged. The Update would then do nothing.
    Thursday, December 17, 2009 9:46 AM
  • Louis, Sorry I have not replied sooner, Christmas got in the way.

    I doubt very much if it is the wrong database, as I have only created one via the database explorer. Further I have tested the SELECT statements in the program and they successfully select the data I have entered in this table and that was in the same execution as the Insert was tested. Further, I have created a Class called DataAccess with a static method called GetConnectionString and as explained in my original post, this uses the ConfigurationManager class to access the connection string held in the app.config file.  This was all copied for MS examples I found along the way.

    The app.config file contains :-

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <configSections>
        </configSections>
        <connectionStrings>
            <add name="Sudoku.Properties.Settings.SudokuDBConnectionString"
                connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SudokuDB.mdf;Integrated Security=True;User Instance=True"
                providerName="System.Data.SqlClient" />
        </connectionStrings>
    </configuration>
    The GetConnectionString method executes :-

        public static string GetConnectionString( string strConnection ) {
    
            //variable to hold our connection string for returning it
            string strReturn = "";
    
            //check to see if the user provided a connection string name
            if( !string.IsNullOrEmpty( strConnection ) ) {
                //get the connection string by the name provided
                strReturn = ConfigurationManager.ConnectionStrings[strConnection].ConnectionString;
            } else {
                //get the default connection string
                strReturn = ConfigurationManager.ConnectionStrings["Sudoku.Properties.Settings.SudokuDBConnectionString"].ConnectionString;
            }
            
            //return the connection string to the calling method
            return strReturn;
        }
    
    The file in the database explorer is called SudokuDB.mdf.

    So I am 99% sure that I am accessing the correct table. I am obviously doing something very fundamentally wrong, but so far I have been unable to work out what it is.

    Thanks for trying.
    Sunday, January 03, 2010 11:52 AM
  • Michael

    This definitely does not work. Before putting your suggested code in, I can execute my insert and then a select and see the new row, but it still disappears when the program completes. With your suggested code in, the new row is NOT inserted and it is then not accessible when the select executes. Thanks anyway.
    Sunday, January 03, 2010 12:02 PM
  • Not sure about this but it looks like the Insert Command on the DataAdapter needs to be set. You can do this like this:

    SQLDataAdapter.InsertCommand = "INSERT INTO GameDetails (GameNo, GameIndex, GameValue) VALUES (?, ?, ?)

    Something like this anyway. You need to put this right before your try block. Here are some links that could help you.

    SqlDataAdapter. . :: . InsertCommand Property
    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.insertcommand.aspx

    How To Update a SQL Server Database by Using the SqlDataAdapter Object in Visual C# .NET
    http://support.microsoft.com/default.aspx/kb/308507


    Dewayne Dodd - Landshark Software "Please make sure to 'Mark As Answer' if this answer solves your question"
    Sunday, January 03, 2010 3:08 PM
  • i don't understand your problem.
    Please explain what you want to do?
    Sunday, January 03, 2010 3:14 PM
  • pso, you're saying that the rows exist while the application runs and then disappear when the application quits. I have never worked with databases created with the database explorer but I suspect that maybe your database is being re-created every time you compile your application? That would explain your observations. Try looking at the database through the SQL Management Studio Express you'll get a much better idea of what your database looks like.
    Sunday, January 03, 2010 3:27 PM
  • pso, you're saying that the rows exist while the application runs and then disappear when the application quits. I have never worked with databases created with the database explorer but I suspect that maybe your database is being re-created every time you compile your application? That would explain your observations. Try looking at the database through the SQL Management Studio Express you'll get a much better idea of what your database looks like.

    It doesn't matter re-created database or not. Because  when he clicked his button and  database is re-creating after this(re-create database) he insert data in his table, and this data appear in database, but when he closed his application daia is disappeared. Here re-creating  is not a problem.
    • Edited by _Dmitriy_ Monday, January 04, 2010 7:05 PM
    Sunday, January 03, 2010 9:24 PM
  • Dear pso
    this is a code to creating databse
    if db_id('SudokuDB')is not null
    drop database SudokuDB
    create database SudokuDB
    on primary(
    name=SudokuDB_data,
    filename='C:\game.mdf',
    size=10mb)
    log on(
    name=SudokuDB_log,
    filename='C:\game.ldf',
    size=5mb)

    This is a code to creating a table
    use SudokuDB
    if ('GamesDetails')is not null
    drop table GamesDetails
    create table GamesDetails
    (GameNo smallint,
    CellIndex smallint,
    CellValue smallint)

    This is a code to insert data to the table
    SqlConnection con = new SqlConnection("data source=MICROSOF-5E02E6;initial catalog=SudokuDB;integrated security=true;");
    DataSet ds = new DataSet();
     SqlDataAdapter sda = new SqlDataAdapter("insert GamesDetails values(10,15,20)", con);
     sda.Fill(ds);

    This must work
    Sunday, January 03, 2010 9:28 PM
  • When I had that problem, the program was working with a copy of the database in the bin\Debug directory. A select within the program would report the inserted row but the explorer would not because it was set on the original db. Each time I recompiled, the original db was copied again and the previous changes disappeared.

    Check if you have a copy of the db inside the bin\Debug directory.

    Monday, January 04, 2010 1:45 AM
  • I have to say I am confused by all these answer attempts.

    LANDSHARKDADDY - In my original post, I mentioned that I have used the SqlCommandBuilder class, which according to what I have read automatically generates the Insert, Update and Delete commands for you, so why do you think I am missing the Insert command. I have to admit I am suspicious of SqlCommandBuilder and I may well remove it and put an Insert command in to see what happens.

    _Dimitry_ - You asked what I am doing. I would have thought this obvious. I am trying to work out how to do an Insert to an MS SQL table inside C#. I have tried all sorts of options and none have worked. I have used SQL on other platforms for many years and have over 30 years experience of this industry. I have embedded SQL in Cobol and C, but this stuff takes the cake for being obtuse. You also offer code fro dropping and creating databases and tables, but is this C# code or what?

    Michal Burger - You suggest that I should use SQL Management Studio Express, but I have install Visual Studio 2008 Express which comes with SQL express and there is no Management Studio option available, so perhaps you can explain where this is.

    Louis - Yes there is a copy in bin\debug and in bin\release and in the dir above these two. So can you please explain why it makes a copy and how on earth I am supposed to know which version it is actually using. If I compile it and run it without debug, the updated table is in bin\release and the new row is there, so yes I suspect the next time I compile it, that row is lost.

    So, when it compiles, what is the source DB (the top level) and I assume it copies it to debug or release, depending on whether you compile with or without debug. If I create new rows and say they go into the release version, how on earth do I get these into the top level database, so that it is used as the starting point for all future compiles?

    Thanks everybody.
    Monday, January 04, 2010 2:50 PM
  • It copies the db each time for letting you debug your application with the same starting point each time.
    It copies the db in bin\debug (or bin\release) when you compile, depending on the compile settings.

    Let's say you're compiling in debug mode.
    It copies the db in the bin\debug directory.
    It will use the db in bin\debug.
    If you want to have the db with the inserted rows as a starting point, overwrite the top level db with the db from bin\debug before recompiling again.

    • Marked as answer by pso57 Monday, March 08, 2010 3:03 PM
    Monday, January 04, 2010 3:12 PM
  • Thanks Louis. No wonder I could not see the new row, I had no idea it would do this. Nobody ever commented on my original questions as to whether I was doing things the right way, are there better ways and also the use of transactions. Care to comment?
    Monday, January 04, 2010 3:32 PM
  • I see no problem with your code.

    The documentation says that SqlDataAdapter opens and closes the connection if it was not already opened.

    About the use of table adapters, there is not really a TableAdapter object, only objects called xxxTableAdapter generated by the Designer when you use typed datasets. When you just create your dataset in your code like you did, you can forget about table adapters. They are just strongly typed proxies to call DataAdapter methods.
    Monday, January 04, 2010 5:18 PM


  • _Dimitry_ - You asked what I am doing. I would have thought this obvious. I am trying to work out how to do an Insert to an MS SQL table inside C#. I have tried all sorts of options and none have worked. I have used SQL on other platforms for many years and have over 30 years experience of this industry. I have embedded SQL in Cobol and C, but this stuff takes the cake for being obtuse. You also offer code fro dropping and creating databases and tables, but is this C# code or what?



    SQL Management Studio- is a spesial product that allows you to do all things whis your database

    For Example you have database with name SudokuDB and 1 table with name GamesDetails and 3 columns with names GameNo,CellIndex,CellValue


    the first example in C# code to insert data

                SqlConnection con = new SqlConnection("data source=MICROSOF-5E02E6;initial catalog=SudokuDB;integrated security=true;");
                DataSet ds = new DataSet();
                SqlDataAdapter sda = new SqlDataAdapter("insert GamesDetails values(10,15,20)", con);
                sda.Fill(ds);


    The second example in C# code to insert data

                SqlConnection con = new SqlConnection("data source=MICROSOF-5E02E6;initial catalog=SudokuDB;integrated security=true;");
                SqlCommand cmd = new SqlCommand("insert GamesDetails values(@1,@1,@3)", con);
                cmd.Parameters.Add("@1", "20");
                cmd.Parameters.Add("@2", "25");
                cmd.Parameters.Add("@3", "30");
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();

    They both work correctly  
    Monday, January 04, 2010 7:04 PM
  • _Dimitry_  I Know what Management Studio is, I have used it in SQL Server 2005, but it does not appear to be available in the SQL Express version.
    Wednesday, January 06, 2010 1:44 PM
  • You have to download it separately from the MS SQL Server Express webpage. The Management Studio Express version is free.

    EDIT: Look here: http://www.microsoft.com/express/sql/download/
    Wednesday, January 06, 2010 2:03 PM
  • Thank you

    Wednesday, January 06, 2010 3:51 PM
  • Dear pso
    If toy have any questions about ado.net you can ask me
    here my icq number 574354624
    Thursday, January 07, 2010 11:18 AM
  • Check this article HOW TO: SQL & C# for details on how to connect to SQL Server database from C#.NET database applications as well as Java database applications. It also describes how to pass embedded SQL queries (SELECT, INSERT, UPDATE, DELETE), calling stored procedures, pass parameters etc.
    Friday, June 04, 2010 11:26 AM
  • Sometimes we want to do temporary operations on our data on the page and finally store it into database for this we can use following procedure :
     
    1) Create a temporary table treating function 
    2) Create a session variable on page load :
     
               Session["dtAccessorialCharges"] = CreateTemptable();
     
    3) Fill default values into session variable :
    4) Now if you want you can do operations on this datatable 
    5) And finally you can save it like :
    This code is very useful when you are doing insert and delete operations on a grid frequently and at the end you want to save only active rows into your database

    hope this did answer the question.

    Cheers, Eliza
    Tuesday, June 15, 2010 8:55 AM