How can I see the data of a SQL server database in c#, and where it's store?

Question

Hello,
I have a program written in c# visual studio 2008 with SQL server 2005 (.mdf) database.
Here is part of the code:

        ...   
        SqlCommandBuilder cb;
        cb = new SqlCommandBuilder(dataAdapter);
        String[] dataList =new String [8];
        ...
        DataTable resultTable = new DataTable();           
        FillDataList(data);
        dataAdapter = new SqlDataAdapter("insert into ProcessData values('" + dataList[0] + "','" + dataList[1] + "','" + dataList[2] + "','" + dataList[3] + "','" + dataList[4] + "','" + dataList[5] + "','" + dataList[6] + "','" + dataList[7]+"')", con);
        dataAdapter.Fill(resultTable); 
        ...

My questions are:
1) Where is the data I added in those lines stored?
2) Why is it that when I Right-Click with the mouse in the Server Explorer->Data Connections->Tables->ProcessData (my table's name)->"show Table Data", I don't see the data but just NULL in the columns, and how can i see the data there?
3) Why when I present this data in a DataGridView sometimes it shows the data and sometimes it doesn't?
Many Thanks!

Answer 1

Without seeing more info on the datalist object and its construction, it's impossible to say. However, you're using the Fill and SQLDataAdapter incorrectly. The FILL relies on the SQLDataAdapter having a SelectCommand property set, which is what your code is bunging the INSERT statement into (that's what the SQLDataAdapter's constructor does). So...your Fill returns nothing as there's no SELECT where there should be.

Your INSERT should be part of the dataadapter's InsertCommand, and you'll need to write a separate SELECT statement to get anything into your resultTable.

Answer 2

Try doing it like this (you need to fill in the correct connection string though):

// Get the db connection
SqlConnection dbCon = new SqlConnection("connection string");

// Select the data from the database table into a DataSet (even if it's empty)
DataSet myData = new DataSet();
SqlDataAdapter dbAdapter = new SqlDataAdapter("select * from ProcessData", dbCon);
dbAdapter.Fill(myData);
myData.Tables[0].TableName = "ProcessData"; // Keeps the table name consistent for the DataMember property

// Use the command builder to add insert, delete, update commands to your adapter
// You must have a primary key on the table for these to work, though
SqlCommandBuilder dbComBuilder = new SqlCommandBuilder(dbAdapter);

dbAdapter.InsertCommand = dbComBuilder.GetInsertCommand();
dbAdapter.DeleteCommand = dbComBuilder.GetDeleteCommand();
dbAdapter.UpdateCommand = dbComBuilder.GetUpdateCommand();

// Bind the data set to the GridView for viewing / editing
yourGridControl.AutoGenerateColumns = true; // Optional, if you haven't manually added the columns
yourGridControl.DataSource = myData;
yourGridControl.DataMember = "ProcessData";

// Use the db adapter to update the database (by calling those commands) with 
// changes made to the DataSet through the grid.  This would go in a different
// form event, like a Save Button click.
dbAdapter.Update();

If you don't have a primary key in the ProcessData table, you can directly insert values using this command:

// Insert the data directly with a command
SqlCommand dbInsCommand = new SqlCommand("insert into ProcessData values (" + val1 + "," + val2 + ")", dbCon);
dbInsCommand.ExecuteNonQuery();

Ron

Answer 3

I think you need to read some basic documentation on ADO.NET before you write any more code. You're passing an INSERT statement to the SqlDataAdapter constructor, which takes a SELECT statement. You're using the Fill method of SqlDataAdapter where you should be using the Update method. You're building a SQL string with hard-coded values in it where you should be using SqlParameters with references to the DataTable Columns. And is the fact that your DataTable is called "data" but that SQL string is using indexed properties from "dataList" just a typo? Because if it is, DataTable doesn't have an indexed property.