使用 SQLCommand 插入 C#
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12939501/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert into C# with SQLCommand
提问by KevinDW
What's the best way to INSERT data into a database?
将数据插入数据库的最佳方法是什么？
This is what I have but it's wrong..
这是我所拥有的，但它是错误的..
cmd.CommandText = "INSERT INTO klant(klant_id,naam,voornaam) VALUES(@param1,@param2,@param3)";
cmd.Parameters.Add(new SqlParameter("@param1", klantId));
cmd.Parameters.Add(new SqlParameter("@param2", klantNaam));
cmd.Parameters.Add(new SqlParameter("@param3", klantVoornaam));
The function add data into the listBox
函数向listBox中添加数据
http://www.pictourl.com/viewer/37e4edcf(link is dead)
http://www.pictourl.com/viewer/37e4edcf(link is dead)
but not into the database..
但没有进入数据库..
http://www.pictourl.com/viewer/4d5721fc(link is dead)
http://www.pictourl.com/viewer/4d5721fc(link is dead)
The full function:
全功能：
private void Form1_Load(object sender, EventArgs e)
{
conn2 = new SqlConnection();
conn2.ConnectionString = ConfigurationManager.ConnectionStrings["connSpionshopString"].ConnectionString;
}
private void button2_Click(object sender, EventArgs e)
{
string sqlCmd = "SELECT naam,voornaam,klant_id FROM klant;";
SqlCommand cmd = new SqlCommand(sqlCmd, conn2);
conn2.Open();
using(SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
listBox2.Items.Add(reader.GetString(0) + " " + reader.GetString(1) + " (" + reader.GetInt16(2) + ")");
}
}
conn2.Close();
}
private void button4_Click(object sender, EventArgs e)
{
int klantId = Convert.ToInt32(textBox1.Text);
string klantNaam = textBox2.Text;
string klantVoornaam = textBox3.Text;
conn2.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn2;
cmd.CommandText = "INSERT INTO klant(klant_id, naam, voornaam) VALUES(@param1,@param2,@param3)";
cmd.Parameters.AddWithValue("@param1", klantId);
cmd.Parameters.AddWithValue("@param2", klantNaam);
cmd.Parameters.AddWithValue("@param3", klantVoornaam);
cmd.ExecuteNonQuery();
conn2.Close();
}
回答by John Woo
you can use implicit casting AddWithValue
您可以使用隐式转换 AddWithValue
cmd.Parameters.AddWithValue("@param1", klantId);
cmd.Parameters.AddWithValue("@param2", klantNaam);
cmd.Parameters.AddWithValue("@param3", klantVoornaam);
sample code,
示例代码，
using (SqlConnection conn = new SqlConnection("connectionString"))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"INSERT INTO klant(klant_id,naam,voornaam)
VALUES(@param1,@param2,@param3)";
cmd.Parameters.AddWithValue("@param1", klantId);
cmd.Parameters.AddWithValue("@param2", klantNaam);
cmd.Parameters.AddWithValue("@param3", klantVoornaam);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch(SqlException e)
{
MessgeBox.Show(e.Message.ToString(), "Error Message");
}
}
}
回答by Lukasz M
You should avoid hardcoding SQL statements in your application. If you don't use ADO nor EntityFramework, I would suggest you to ad a stored procedure to the database and call it from your c# application. A sample code can be found here: How to execute a stored procedure within C# programand here http://msdn.microsoft.com/en-us/library/ms171921%28v=vs.80%29.aspx.
您应该避免在应用程序中硬编码 SQL 语句。如果您不使用 ADO 或 EntityFramework，我建议您向数据库添加一个存储过程并从您的 c# 应用程序中调用它。可以在此处找到示例代码：如何在 C# 程序中执行存储过程以及此处http://msdn.microsoft.com/en-us/library/ms171921%28v=vs.80%29.aspx。
回答by Ravindra Bagale
Use AddWithValue(), but be aware of the possibility of the wrong implicit type conversion.
like this:
使用AddWithValue()，但是be aware of the possibility of the wrong implicit type conversion。
像这样：
cmd.Parameters.AddWithValue("@param1", klantId);
cmd.Parameters.AddWithValue("@param2", klantNaam);
cmd.Parameters.AddWithValue("@param3", klantVoornaam);
回答by Gene S
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO klant(klant_id,naam,voornaam) VALUES(@param1,@param2,@param3)";
command.Parameters.AddWithValue("@param1", klantId));
command.Parameters.AddWithValue("@param2", klantNaam));
command.Parameters.AddWithValue("@param3", klantVoornaam));
command.ExecuteNonQuery();
}
}
回答by Sergey Berezovskiy
You can use dapperlibrary:
您可以使用dapper库：
conn2.Execute(@"INSERT INTO klant(klant_id,naam,voornaam) VALUES (@p1,@p2,@p3)",
new { p1 = klantId, p2 = klantNaam, p3 = klantVoornaam });
BTW Dapper is a Stack Overflow project :)
顺便说一句，Dapper 是一个 Stack Overflow 项目 :)
UPDATE: I believe you can't do it simpler without something like EF. Also try to use usingstatements when you are working with database connections. This will close connection automatically, even in case of exception. And connection will be returned to connections pool.
更新：我相信如果没有像 EF 这样的东西，你就不能做得更简单。using在处理数据库连接时也尝试使用语句。这将自动关闭连接，即使在异常情况下也是如此。并且连接将返回到连接池。
private readonly string _spionshopConnectionString;
private void Form1_Load(object sender, EventArgs e)
{
_spionshopConnectionString = ConfigurationManager
.ConnectionStrings["connSpionshopString"].ConnectionString;
}
private void button4_Click(object sender, EventArgs e)
{
using(var connection = new SqlConnection(_spionshopConnectionString))
{
connection.Execute(@"INSERT INTO klant(klant_id,naam,voornaam)
VALUES (@klantId,@klantNaam,@klantVoornaam)",
new {
klantId = Convert.ToInt32(textBox1.Text),
klantNaam = textBox2.Text,
klantVoornaam = textBox3.Text
});
}
}
回答by Oluwafemi
Try confirm the data type (SqlDbType) for each parameter in the database and do it this way;
尝试确认数据库中每个参数的数据类型（SqlDbType）并这样做；
using(SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connSpionshopString"].ConnectionString))
{
connection.Open();
string sql = "INSERT INTO klant(klant_id,naam,voornaam) VALUES(@param1,@param2,@param3)";
using(SqlCommand cmd = new SqlCommand(sql,connection))
{
cmd.Parameters.Add("@param1", SqlDbType.Int).value = klantId;
cmd.Parameters.Add("@param2", SqlDbType.Varchar, 50).value = klantNaam;
cmd.Parameters.Add("@param3", SqlDbType.Varchar, 50).value = klantVoornaam;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
回答by Danush Mohotti
public class customer
{
public void InsertCustomer(string name,int age,string address)
{
// create and open a connection object
using(SqlConnection Con=DbConnection.GetDbConnection())
{
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("spInsertCustomerData",Con);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paramName = new SqlParameter();
paramName.ParameterName = "@nvcname";
paramName.Value = name;
cmd.Parameters.Add(paramName);
SqlParameter paramAge = new SqlParameter();
paramAge.ParameterName = "@inage";
paramAge.Value = age;
cmd.Parameters.Add(paramAge);
SqlParameter paramAddress = new SqlParameter();
paramAddress.ParameterName = "@nvcaddress";
paramAddress.Value = address;
cmd.Parameters.Add(paramAddress);
cmd.ExecuteNonQuery();
}
}
}
