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.

Answer 1

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!!