C#学习教程:使用SQLCommand插入C#分享


使用SQLCommand插入C#

将数据插入数据库的最佳方法是什么?

这就是我所拥有的,但这是错的..

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)); 

该函数将数据添加到listBox中

http://www.pictourl.com/viewer/37e4edcf (link is dead)

但不进入数据库..

http://www.pictourl.com/viewer/4d5721fc (link is dead)

全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(); } 

尝试确认数据库中每个参数的数据类型( 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)"; 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(); } 

你可以使用隐式转换AddWithValue

 cmd.Parameters.AddWithValue("@param1", klantId); cmd.Parameters.AddWithValue("@param2", klantNaam); cmd.Parameters.AddWithValue("@param3", klantVoornaam); 

示例代码,

 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"); } } } 

 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(); } } 

你可以使用dapper库:

 conn2.Execute(@"INSERT INTO klant(klant_id,naam,voornaam) VALUES (@p1,@p2,@p3)", new { p1 = klantId, p2 = klantNaam, p3 = klantVoornaam }); 

BTW Dapper是一个Stack Overflow项目:)

更新:我相信如果没有像EF这样的东西你就不能做得更简单。 在使用数据库连接时,还要尝试使用using语句。 这将自动关闭连接,即使在exception情况下也是如此。 连接将返回到连接池。

 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 }); } } 

您应该避免在应用程序中硬编码SQL语句。 如果您不使用ADO或EntityFramework,我建议您将存储过程添加到数据库并从您的c#应用程序中调用它。 可以在此处找到示例代码: 如何在C#程序中执行存储过程 ,此处http://msdn.microsoft.com/en-us/library/ms171921%28v=vs.80%29.aspx 。

使用AddWithValue() ,但be aware of the possibility of the wrong implicit type conversion
像这样:

上述就是C#学习教程:使用SQLCommand插入C#分享的全部内容,如果对大家有所用处且需要了解更多关于C#学习教程,希望大家多多关注—猴子技术宅(www.ssfiction.com)

 cmd.Parameters.AddWithValue("@param1", klantId); cmd.Parameters.AddWithValue("@param2", klantNaam); cmd.Parameters.AddWithValue("@param3", klantVoornaam); 

 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(); } } } 

本文来自网络收集,不代表猴子技术宅立场,如涉及侵权请点击右边联系管理员删除。

如若转载,请注明出处:https://www.ssfiction.com/ckf/959892.html

发表评论

邮箱地址不会被公开。 必填项已用*标注