SQL 如何在面向ADO.Net连接的模式下在SQL数据库中插入行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14090914/
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
how to insert row in SQL database in ADO.Net Connection oriented mode
提问by Sometimes Code
I have a database in which a table has name Registrationwhich is used to register the user.
我有一个数据库，其中一个表的名称Registration用于注册用户。
It has only two column one is Usernameand one is password.
它只有两列，一是Username和一是password。
A page named Register.aspxis used for registering the member which have two textbox one is for taking Username(textbox1)and one is for taking password(textbox2)and one button for insert these value in database.
一个名为的页面Register.aspx用于注册具有两个文本框的成员，一个用于获取Username(textbox1)，一个用于获取password(textbox2)，一个用于将这些值插入数据库的按钮。
The Main problem is that we cannot write statement like this :
主要问题是我们不能写这样的语句：
Insert into Registration (Username, password)
values ('TextBox1.text','TextBox2.text')
I am using ADO.net Connection oriented mode, I googled but I didn't find any way to insert row in SQL database in connected mode. Please provide me a idea for inserting this row?
我正在使用 ADO.net 面向连接的模式，我用谷歌搜索但我没有找到任何方法在连接模式下在 SQL 数据库中插入行。请给我一个插入这一行的想法？
回答by syed mohsin
ADO.NET has DataReader which supports Connected mode. All else are disconnected.
ADO.NET 具有支持连接模式的 DataReader。其他都断开了。
DataReader is connected architecture since it keeps conneection open untill all records are fetched
DataReader 是连接架构，因为它保持连接打开，直到获取所有记录
If you want to insert in ADO.NET then you should perform the following steps:
如果要在 ADO.NET 中插入，则应执行以下步骤：
private void btnadd_Click(object sender, EventArgs e)
{
try
{
//create object of Connection Class..................
SqlConnection con = new SqlConnection();
// Set Connection String property of Connection object..................
con.ConnectionString = "Data Source=KUSH-PC;Initial Catalog=test;Integrated Security=True";
// Open Connection..................
con.Open();
//Create object of Command Class................
SqlCommand cmd = new SqlCommand();
//set Connection Property of Command object.............
cmd.Connection = con;
//Set Command type of command object
//1.StoredProcedure
//2.TableDirect
//3.Text (By Default)
cmd.CommandType = CommandType.Text;
//Set Command text Property of command object.........
cmd.CommandText = "Insert into Registration (Username, password) values ('@user','@pass')";
//Assign values as `parameter`. It avoids `SQL Injection`
cmd.Parameters.AddWithValue("user", TextBox1.text);
cmd.Parameters.AddWithValue("pass", TextBox2.text);
Execute command by calling following method................
1.ExecuteNonQuery()
This is used for insert,delete,update command...........
2.ExecuteScalar()
This returns a single value .........(used only for select command)
3.ExecuteReader()
Return one or more than one record.
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Data Saved");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
con.Close();
}
}
回答by Artem G
try
{
using (var connection = new SqlConnection(yourConnectionString))
{
string queryString = "Select id, name, age from Table where name = @name";
using (var command = new SqlCommand(queryString, Connection))
{
command.Parameters.AddWithValue("@name", name);
Connection.Open();
SqlDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
item = new Item(long.Parse(dataReader[0].ToString()),
dataReader[1].ToString(),
int.Parse(dataReader[2].ToString()));
}
dataReader.Close();
}
}
}
catch (Exception ex)
{
// if exception will happen in constructor of SqlConnection or Command, the // resource can leak but Dispose method will never be called, couse the object was not created yet.
// Trace and handle here
throw;
}
finally
{
}
But ADO.net is useless for enterprice development. You have to have and abstraction from Data Acess Layer and go to entities, not table records Use the ORM, Luke!
但是ADO.net 对企业开发毫无用处。您必须从数据访问层中抽象出来并转到实体，而不是表记录 使用 ORM，Luke！
回答by Mahdi Tahsildari
using System.Data.SqlClient;
string cnstr = "server=.;database=dbname;user=username;password=password;";
SqlConnection con = new SqlConnection(cnstr);
SqlCommand cmd = new SqlCommand { Connection = con };
cmd.CommandText = "Insert into Registration (Username, password) values ('@user','@pass')";
cmd.Parameters.AddWithValue("user", TextBox1.text);
cmd.Parameters.AddWithValue("pass", TextBox2.text);
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (Exception)
{
//something;
}
finally
{
if (con != null)
con.Close();
}
