SQL C#, command(query) is executing twice

Question

I have simple app to build sql query (for educational purposes). I created textarea where user can write his command to sql, then program has to execute it or catch Sqlexeption. I know about safety etc. but its ok- user can delete everything :)

ok. here is the code:

query = text from textarea(its SQL command)

if (!String.IsNullOrEmpty(query) || !String.IsNullOrWhiteSpace(query))
{
    string conString = ConfigurationManager.ConnectionStrings["StudentDataBase"].ConnectionString;

    try
    { 
        using (SqlConnection SqlCon = new SqlConnection(conString))
        {                
            try
            {
                SqlCommand command = new SqlCommand(query, SqlCon);
                SqlCon.Open();

                command.ExecuteScalar();

                int numOfRows = 0;

                SqlDataAdapter adpt = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                DataSet dset = new DataSet();
                adpt.Fill(dset);
                dt = dset.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    numOfRows = dt.Rows.Count;
                    gridview_results.DataSource = dt;
                    gridview_results.DataBind();

                    Sql_error = "Done. Results: " + numOfRows + " rows.";
                    container_sql_error.Style.Add("background-color", "#b9ffcb");
                }
                else
                {
                    Sql_error = "0 rows to show.";
                }                           

                SqlCon.Close();
            }
             catch (SqlException ex)
            {
               Sql_error = "Error: " + ex.Message;
               container_sql_error.Style.Add("background-color", "#ff9600");
            }
        }
    }
    catch (SqlException ex)
    {
        Sql_error = "Error... " + ex.Message;
        container_sql_error.Style.Add("background-color", "#ff9600");
    }
}

And now, when im trying:

SELECT * FROM test its OK. GridView showing data.

slleeeccct * from testsste its OK - showing an error.

INSERT INTO test (col1) VALUES ('aaa') its NOT OK- program throws error System.IndexOutOfRangeException: cannot find table 0 BUT command was excecuted properly BUT TWICE.

Now i have a questions: why command is excecuting TWICE(2x same data in DB) and why is there an Error about finding table 0 (is it about GridView maybe- cant fill GV with insert into)?

Well you're calling SqlCommand.ExecuteScalar and then you're using the same command with a SqlDataAdapter and asking that adapter to fill a dataset... which it's going to do be executing the command again. Why are you calling ExecuteScalar at all? — Jon Skeet
– Jon Skeet, Commented Nov 14, 2016 at 22:59
@Kafus, I think Jon was implying that the call to ExecuteScalar is probably spurious and may be safely removed. (as he stated, the SqlDataAdapter will execute the command itself) — Kirk Woll
– Kirk Woll, Commented Nov 14, 2016 at 23:11
On option is to have INSERT/UPDATE/DELETE and SELECT on the fronted as separate functions. Because how can you SELECT from an INSERT statement ? Not possible. So when the perform any of C U D you will need to run a separate SELECT and fill your datagrid. — Searching
– Searching, Commented Nov 14, 2016 at 23:33
This looks like you're building a method that will force you write code that is horribly vulnerable to sql injection attacks. It is not okay to use string concatenation to put data into SQL commands. You must include some mechanism for accepting parameters in your SQL. When you only deal with a query variable in the SqlCommand, and never look at the command's .Parameters collection, you're missing that mechanism. — Joel Coehoorn
– Joel Coehoorn, Commented Nov 14, 2016 at 23:46

theK · Accepted Answer · 2016-11-14 23:41:39Z

1

First of all, you are executing the code twice

-> one time you are using ExecuteScalar and the other you are using the SQLAdapter to fill the dataset with returned results, you can just use it like the below:

1- dataset ds=new dataset();

2- adapter.fill(ds);

3- return ds;

and that's it :)

Regarding the insert query error, that's normal as well because the insert statement using Execute Scalar will Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

so when you use Insert statement, you are having an error because either

1- the command wasn't executed successfully and returned an error "Check if databsae has the inserted row you just typed"

2- dataset tables has no data, you can make an IF Statement check before you try to read from it like

"If(ds.tables.count>0) {do something}"

answered Nov 14, 2016 at 23:41

theK

942 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kafus Over a year ago

Thanks. This is exactly what i need.

Collectives™ on Stack Overflow

SQL C#, command(query) is executing twice

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related