The following example creates a SqlCommand and runs it using ExecuteScalar. Two strings are passed to this example. One String indicates the new value to be inserted into the table, and the other string is used to connect to the data source. If a new row has been inserted, this function returns the value of the new "Identity" column. If it fails, 0 is returned.
01
static
public
int
AddProductCategory(
string
newName,
string
connString)
02
{
03
Int32 newProdID = 0;
04
string
sql =
05
"INSERT INTO Production.ProductCategory (Name) VALUES (@Name); "
06
+
"SELECT CAST(scope_identity() AS int)"
;
07
using
(SqlConnection conn =
new
SqlConnection(connString))
08
{
09
SqlCommand cmd =
new
SqlCommand(sql, conn);
10
cmd.Parameters.Add(
"@Name"
, SqlDbType.VarChar);
11
cmd.Parameters[
"@name"
].Value = newName;
12
try
13
{
14
conn.Open();
15
newProdID = (Int32)cmd.ExecuteScalar();
16
}
17
catch
(Exception ex)
18
{
19
Console.WriteLine(ex.Message);
20
}
21
}
22
return
(
int
)newProdID;
23
}