How to return sql query result messages with c# ASP.net

Question

I tried following code but I couldn't find success messages from it.Here commandText can be insert,update,delete or select query and I need dataset if it is a select query else success messages like in sql result output("12 row(s) inserted successfully").I can't use ExecuteScalar or ExecuteNonQuery methods since I need dataset output when select query executed.

public static  DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        using (SqlConnection cn = new SqlConnection(connectionString))
        {
            cn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
            {
                MessageInfo += "\n" + e.Message;
            };
            cn.FireInfoMessageEventOnUserErrors = true;

            cn.Open();
            using (SqlCommand command = new SqlCommand(commandText, cn))
            {

                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    DataSet dt = new DataSet();
                    adapter.Fill(dt); // Do something with DataTable
                    return dt;
                }
            }
        }
    }

I have seen a lot of questions about this but it seems the only real option is to implement the messages yourself. Problem being you wont have the actual message generated from the database. — CSharpened
– CSharpened, Commented Jul 6, 2012 at 11:40
yep.i checked that eventhandler didn't call except at first time.And MessageInfo is null. — LittleOne
– LittleOne, Commented Jul 6, 2012 at 11:45

Tom · Accepted Answer · 2015-12-31 18:07:22Z

1

I am using System.Data version 4.0.0.0 and the following works for me with UPDATE, INSERT, DELETE and SELECT statements.

using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
    var rowsAffected = -1;

    adapter.SelectCommand.StatementCompleted += delegate (object sender, StatementCompletedEventArgs e)
    {
        rowsAffected = e.RecordCount;
    };

    DataSet dt = new DataSet();
    adapter.Fill(dt); // Do something with DataTable
    return dt;
}

answered Dec 31, 2015 at 18:07

Tom

513 bronze badges

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

Comments

LittleOne · Accepted Answer · 2012-07-11 06:46:10Z

I managed to solve it by following way but hope there should be a better way.

 string type = Query.Trim().Split(' ')[0].ToLower();
    if ( type == "select")
    {
        DataSet result = SqlHelper.ExecuteDataset(CommandType.Text, Query);                

    }
    else
    {                
        if(type == "insert") type="inserted."; else type=type+"d.";
        int a=(int)SqlHelper.ExecuteNonQuery(CommandType.Text, Query);
        if (a > 0)
        {
            lblInfo.Text = "Query executed successfully. " + a.ToString() + " row(s) " + type;                

        }
    }

I found another one.That's better than above one.

query = resultQuery + "  SELECT '('+CONVERT(VARCHAR(10),@@ROWCOUNT)+' row(s) affected)' AS RESULT";
DataSet result = DataSet result = SqlHelper.ExecuteDataset(CommandType.Text, query);

        if (result.DefaultViewManager.DataViewSettingCollectionString.Contains("Table") == true)
        {
            sqlGrid.Visible = true;
            sqlGrid.DataSource = result;
            sqlGrid.DataBind();
        }
        if (sqlGrid.Rows.Count==1 && sqlGrid.Rows[0].Cells.Count==1)
        {
            string text = (sqlGrid.Rows[0].Cells[0]).Text;
            if (text.Contains("row(s) affected"))
            {
                lblInfo.Text=text;
                lblInfo.Visible=true;
                sqlGrid.DataSource = null;
                sqlGrid.DataBind();
            }
        }

Collectives™ on Stack Overflow

How to return sql query result messages with c# ASP.net

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related