ExecuteNonQuery, ExecuteReader and SqlDataAdapter insert query 3 times

Question

public static class db
{
    public static string databaseName = "blue_pumpkin";

    public static Temp query(string qr)
    {
        var s = new Temp();
        s.query(qr);
        return s;
    }
}

public class Con
{
    static SqlConnection sc = null;

    public static SqlConnection connect()
    {
        if (sc == null)
        {
            sc = new SqlConnection("server=localhost\\SQLEXPRESS;database=" + db.databaseName + ";Integrated Security=True;");
        }

        return sc;
    }
}

public class Temp
{
    private SqlConnection con;
    private string sqlQuery = "";
    private DataTable dataset = new DataTable();

    public long last_insert_id = -1;
    public int rows_affected = -1;
    public int num_rows = 0;
    public int num_columns = 0;
    public Boolean hasRows = false;

    public void query(string qr)
    {
        this.sqlQuery = qr;
        this.con = Con.connect();
        this.con.Open();

        SqlCommand cd = new SqlCommand(this.sqlQuery, this.con);

        try {
            this.rows_affected = cd.ExecuteNonQuery();
        } catch (Exception e) {
            this.rows_affected = -1;
        }

        SqlDataReader reader = cd.ExecuteReader();
        this.hasRows = reader.HasRows;
        reader.Close();

        SqlDataAdapter sda = new SqlDataAdapter(cd);
        sda.Fill(this.dataset);

        this.num_rows = this.dataset.Rows.Count;
        this.num_columns = this.dataset.Columns.Count;

        string sql = "SELECT SCOPE_IDENTITY();";
        SqlCommand cmd = new SqlCommand(sql, this.con);

        try {
            this.last_insert_id = Convert.ToInt64(cmd.ExecuteScalar());
        } catch (Exception e) {
            this.last_insert_id = -1;
        }

        this.con.Close();
    }

    public DataTable getDataTable()
    {
        return this.dataset;
    }
}

This is how I use it in every page where needed.

var b = db.query("SELECT * FROM [" + db.databaseName + "].[dbo].[registration] ");
Response.Write("Last id : " + b.last_insert_id.ToString() + "<br>");
Response.Write("hasRows : " + b.hasRows.ToString() + "<br>");
Response.Write("num_columns : " + b.num_columns.ToString() + "<br>");
Response.Write("num_rows : " + b.num_rows.ToString() + "<br>");
Response.Write("rows_affected : " + b.rows_affected.ToString() + "<br>");

var c = db.query("UPDATE  [" + db.databaseName + "].[dbo].[registration] SET reg_password = 'Pune'" +
"WHERE reg_id = 1; ");
Response.Write("Last id : " + c.last_insert_id.ToString() + "<br>");
Response.Write("hasRows : " + c.hasRows.ToString() + "<br>");
Response.Write("num_columns : " + c.num_columns.ToString() + "<br>");
Response.Write("num_rows : " + c.num_rows.ToString() + "<br>");
Response.Write("rows_affected : " + c.rows_affected.ToString() + "<br>");

var queryString = "INSERT INTO [" + db.databaseName + "].[dbo].[registration] ([reg_name], [reg_age], [reg_gender], " +
   " [reg_creation_date], [reg_email], [reg_password], [reg_is_approved]) VALUES" +
   " ('"+ reg_name + "', '" + reg_age + "', '" + reg_gender + "', GETDATE(), '" + reg_email + "',"+
   " '" + reg_password + "', 0)";
db.query(queryString);

//OR 

DataTable dt = db.query("SELECT * FROM [" + db.databaseName + "].[dbo].[registration] WHERE [reg_id]=1").getDataTable();

Everything is working fine except when I run insert query it insert 3 times because of ExecuteNonQuery, ExecuteReader and SqlDataAdapter.

If I ran them separately, it works fine, but If I run them together, they insert it 3 times. Because I want to run all type of query in same function, is there any way possible to make it work all 3 simultaneously and insert query insert only one time and doesn't effect update or delete or different pattern query.

One thing I can think of is to create insertQuery() function separately just for insert data. But if anyone can give me a better Idea than I'm more than welcome.

Yes a better idea is to forget the idea to have one single method to execute all the possible interaction with a database. — Steve, Feb 05 '17 at 14:03
By the way, you have a lot of problems here. Sql Injection is the worse one, correct parsing is another one. FInally failing to dispose correctly a connection is another big one. Please look at how to write a parameterized query and the basic concept of _disposable_ objects like the connection — Steve, Feb 05 '17 at 14:06
can you provide me any link where I can learn and correct these mistakes? I'm from php and javascript background that's y I use a lot of var because I like it. — Faizan Anwer Ali Rupani, Feb 05 '17 at 14:11
http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i https://msdn.microsoft.com/en-us/library/ms254953(v=vs.110).aspx http://stackoverflow.com/questions/3079098/the-c-sharp-using-statement-sql-and-sqlconnection — Steve, Feb 05 '17 at 14:19
You might also have a look at how to use stored procedures, rather than trying to build this up in C#. You've got a database engine there, not just a repository - use it. — David T. Macknet, Feb 05 '17 at 20:03

malandres · Answer 1 · 2018-10-11T18:54:22.687

The problem is that inside your query method, these three lines are executing the insert statement.

    this.rows_affected = cd.ExecuteNonQuery();

    SqlDataReader reader = cd.ExecuteReader();

    sda.Fill(this.dataset);

You need to separate these executions depending what you want to do, for example, if the statement isn't a select, only execute the SqlCommand.ExecuteNonQuery method, otherwise execute SqlDataAdapter.Fill method.

If you need to know if there is rows or the number of rows for the select statement, you can use the following code without using SQLDataReader

    SqlCommand cd = new SqlCommand(sqlQuery, con);
    DataTable dataTable = new DataTable();
    SqlDataAdapter sda = new SqlDataAdapter(cd);
    sda.Fill(dataTable); 
    Boolean hasRows = (dataTable != null && dataTable.Rows.Count > 0);
    Int32 noRows = (hasRows ? dataTable.Rows.Count : 0);

Hope it was usefull you!!

ExecuteNonQuery, ExecuteReader and SqlDataAdapter insert query 3 times

1 Answers1