ExecuteScalar() in ADO.NET
The ExecuteScalar() is method which belongs to Command object in ADO.NET. The ExecuteScalar() methods return only one value from the database. You can select or insert query by using the ExecuteScalar(), but it returns only one value from the database, that is first column of a first row. Remaining records may be omitted from the ExecuteScalar.
Below is the code to insert into database and get the ID from the database.
using System;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;
static public int AddUser(string UserName, string connStr)
{
Int32 userID = 0;
string sqlQuery ="INSERT INTO PMDB.Users (Name) VALUES (@Name); "+ "SELECT CAST(scope_identity() AS int)";
using (SqlConnection connection = new SqlConnection(connStr))
{
SqlCommand mycmd = new SqlCommand(sqlQuery, connection);
mycmd.Parameters.Add("@Name", SqlDbType.VarChar);
mycmd.Parameters["@name"].Value = UserName;
try
{
connection.Open();
userID = (Int32)mycmd.ExecuteScalar();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
return (int)userID;
}
Below is the code to select the MAX salary from the database and return to C# code.
using System;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string connStr = null;
SqlConnection yourCnn;
SqlCommand cmd;
string sqlQuery = null;
connStr = "Data Source=ServerName;Initial Catalog=DBName;User ID=UserName;Password=Password";
sqlQuery = "select MAX(salary) from employee";
yourCnn = new SqlConnection(connStr);
try
{
yourCnn.Open();
cmd = new SqlCommand(sqlQuery, yourCnn);
Int32 result = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Dispose();
yourCnn.Close();
MessageBox.Show("Max salary " + result);
}
catch (Exception ex)
{
MessageBox.Show("Can not open connection ! ");
}
}
}
}