Just a small addition to the Inserting into SQL Server with parameterized query.
You might have noticed that the products table has a autonumber field, id, and often it's very useful to get that value of the just inserted record.
Well, lets just use some other trick we learned - multiple queries in a single command
So we call it like this:
long lId = InsertProduct("Our new product", 1, 1, "kilo", 23.45, 11, 2,20, false);
and the lId will contain the new records identity value.
private long InsertProduct(string ProductName, long SupplierID, long CategoryID,
string QuantityPerUnit, double UnitPrice, int UnitsInStock, int UnitsOnOrder, int ReorderLevel, bool Discontinued)
{
string sConn = "Data Source=(local);Network Library=DBMSSOCN;Initial Catalog=Northwind;User ID=sa;Password=stefan;";
string sSQL = "insert into products (ProductName, SupplierID, CategoryID, " +
"QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, " +
" ReorderLevel, Discontinued) values( @ProductName, @SupplierID, " +
" @CategoryID, @QuantityPerUnit, @UnitPrice, @UnitsInStock, " +
" @UnitsOnOrder, @ReorderLevel, @Discontinued); select @@identity";
System.Data.SqlClient.SqlParameter[] oParamColl =
new System.Data.SqlClient.SqlParameter[9];
oParamColl[0] = new System.Data.SqlClient.SqlParameter("@ProductName", SqlDbType.NVarChar, 40);
oParamColl[0].Value = ProductName;
oParamColl[1] = new System.Data.SqlClient.SqlParameter("@SupplierID", SqlDbType.Int);
oParamColl[1].Value = SupplierID;
oParamColl[2] = new System.Data.SqlClient.SqlParameter("@CategoryID", SqlDbType.Int);
oParamColl[2].Value = CategoryID;
oParamColl[3] = new System.Data.SqlClient.SqlParameter("@QuantityPerUnit", SqlDbType.NVarChar,20);
oParamColl[3].Value = QuantityPerUnit;
oParamColl[4] = new System.Data.SqlClient.SqlParameter("@UnitPrice", SqlDbType.Float);
oParamColl[4].Value = UnitPrice;
oParamColl[5] = new System.Data.SqlClient.SqlParameter("@UnitsInStock", SqlDbType.SmallInt);
oParamColl[5].Value = UnitsInStock;
oParamColl[6] = new System.Data.SqlClient.SqlParameter("@UnitsOnOrder", SqlDbType.SmallInt);
oParamColl[6].Value = UnitsOnOrder;
oParamColl[7] = new System.Data.SqlClient.SqlParameter("@ReorderLevel", SqlDbType.SmallInt);
oParamColl[7].Value = ReorderLevel;
oParamColl[8] = new System.Data.SqlClient.SqlParameter("@Discontinued", SqlDbType.Bit);
oParamColl[8].Value = Discontinued;
return Convert.ToInt32(Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteScalar(sConn,
CommandType.Text, sSQL, oParamColl));
}
Now, to note is of course the ; select @@identity addition in the end of the statement. Also for maximum performance we use the ExecuteScalar function since we know it's just a single value we are interested in.
