如何从SQL查询中获取值?
问题描述:
conn.Open();
string query = "INSERT INTO Film (Film, Jaartal, Cijfer) VALUES ('" + filmnaam + "','" + jaartal + "','" + cijfer + "')";
string LastID = "SELECT TOP 1 Id FROM Film ORDER BY Id DESC";
SqlCommand cmd = new SqlCommand(query, conn);
SqlCommand cmd2 = new SqlCommand(LastID, conn);
cmd.ExecuteNonQuery();
using (SqlDataReader dr = cmd2.ExecuteReader())
{
while (dr.Read())
{
string ID = dr["Id"].ToString();
string add= "INSERT INTO GenreFilm (FilmId) VALUES ('" + ID + "')";
SqlCommand cmd3 = new SqlCommand(add, conn);
cmd3.ExecuteNonQuery();
}
}
我想将我的(LastID)查询的值(ID)添加到我的数据库表中。但我似乎无法做到这一点。以上是我现有的代码。任何帮助/提示将不胜感激!如何从SQL查询中获取值?
答
这并不直接提供解决方案而是跟随牢记什么@ADyson表示有关参数(SQL注入),什么@Dimitry表示关于允许数据库获取的价值为你流。希望这会有所帮助。
该代码有评论洒在里面。第一类DemoForOperations展示了如何在第二类操作中调用该方法。当然你可以在任何你想要的操作中调用insert方法。
using System;
using System.Data.SqlClient;
namespace *Sample
{
public class DemoForOperations
{
public void TheDemo()
{
var ops = new Operations();
var firstName = "Karen";
var lastName = "Payne";
var returningNewId = 0;
if (ops.SampleInsert(firstName,lastName,ref returningNewId))
{
// success, returningNewId has the new key value which can be
// used for whatever you want e.g. as a value for another query.
}
else
{
// failed, you can use the following the
// figure out the issue
var exceptionMessage = ops.Exception.Message;
}
}
}
public class Operations
{
private Exception exception;
public Exception Exception { get { return exception; } }
/// <summary>
/// Insert a record
/// </summary>
/// <param name="FirstName"></param>
/// <param name="LastName"></param>
/// <param name="NewIdentifier">
/// pass in a valid int by ref
/// </param>
/// <returns>
/// true if successful, false otherwise and will set the property
/// Exception so that the caller can see what went wrong
/// </returns>
public bool SampleInsert(string FirstName, string LastName, ref int NewIdentifier)
{
// here we create the connection new but of course a connection can be created
// outside of the method that is in-scope of this method
using (SqlConnection cn = new SqlConnection() { ConnectionString = "TODO" })
{
// setup for insert using parameters
// along with a secondary query to return the new primary key value
var statement = "INSERT INTO Contacts (FirstName,LastName) " +
"VALUES (@FirstName,@LastName); " +
"SELECT CAST(scope_identity() AS int);";
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = cn;
cmd.CommandText = statement;
try
{
// setup our parameters
cmd.Parameters.AddWithValue("@FirstName", FirstName);
cmd.Parameters.AddWithValue("@LastName", LastName);
cn.Open();
// get new primary key
NewIdentifier = Convert.ToInt32(cmd.ExecuteScalar());
return true;
}
catch (Exception ex)
{
exception = ex;
return false;
}
}
}
}
}
}
请添加语言标记 – Jens
您不应该通过重新查询来检索新ID,由于多种原因这是不安全的。数据库提供了一种机制让你获得价值,告诉我们你的数据库,我们会告诉你它是什么。 –
https://*.com/questions/7917695/sql-server-return-value-after-insert –