How to show value printed by sql query in message box
I want to print a value that is returned by SQL Server.
If NOT Exists(SELECT * FROM ItemList WHERE ItemName='txtItemNama')
BEGIN
INSERT INTO ItemList开发者_开发问答 (ItemName) VALUES('txtItemNamea')
END
ELSE
BEGIN
Print 'Duplicate'
END
This query will either return me either number of rows affected or Duplicate
I want to use this Duplicate in C# in MessageBox.Show()
string query1 = "If NOT Exists(SELECT * FROM ItemList WHERE ItemName='txtItemName') BEGIN INSERT INTO ItemList (ItemName) VALUES('txtItemName') END ELSE BEGIN Print 'Duplicate' END";
SqlCommand cmd = new SqlCommand(query1, conn);
SqlDataReader dr;
conn.Open();
dr=cmd.ExecuteReader();
conn.Close();
MessageBox.Show(dr);
I don't know how to use dr
to do this. Please help me out to print Duplicate here
MessageBox.Show(dr);
What do I need to do here?
a few years late but you should be able to retrieve print/info text (like originally asked) by attaching an eventhandler to the InfoMessage event on the SqlConnection object -
But only do this if you can't (for some reason) use the alternatives mentioned in this thread.
static void Main(string[] args)
{
using (SqlConnection connection = new SqlConnection(@"someconnectionstring"))
{
connection.Open();
using(SqlCommand command = new SqlCommand("test", connection))
{
connection.InfoMessage += new SqlInfoMessageEventHandler(connection_InfoMessage);
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
DataTable dt = new DataTable();
adapter.Fill(dt); // Do something with DataTable
}
}
}
}
static void connection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
// e contains info message etc
}
Using ADO.NET there are four options for returning information from an SQL query:
- Use a DataSet object to gather the returned rows and to work with these rows in addition to the return values and the return parameters.
- Use a DataReader object to gather the returned rows, to move through these rows, and to gather return values and return parameters.
- Use the ExecuteScalar method to return the value from the first column of the results' first row with the return values and the return parameters. This is most useful with aggregate functions.
- Use the ExecuteNonQuery method to return only the return parameters and the return values. Any returned rows are discarded. This is most useful for executing action queries.
These are all methods called from your command object.
There are lots of different ways to skin a cat, you can use output parameters, you can use ExecuteScalar, you can use return values or you can use dummy recordsets.
You should be able to something like the following in your C# to get the return value from a query
// add a new parameter, with any name we want - its for our own use only
SqlParameter sqlParam = com.Parameters.Add("@ReturnValue", SqlDbType.Int);
// set the direction flag so that it will be filled with the return value
myParm.Direction = ParameterDirection.ReturnValue;
The code above the captures the return value that you can set as you need, perhaps with a 0 for exists and a 1 for not exists.
If NOT Exists(SELECT * FROM ItemList WHERE ItemName='txtItemNama')
BEGIN
INSERT INTO ItemList (ItemName) VALUES('txtItemNamea')
Return 0;
END
ELSE
BEGIN
Return 1;
END
There is a bit caveat with the above advice however - I usually either use an ORM like Linq-to-SQL or NHibernate, or I use Stored Procedures. I find inline SQL quite cumbersome. So, while my answer should be sound in general, you will probably need to work through some details to get it working exactly as you need.
精彩评论