C# 如何在 Microsoft Visual Studio 2012 中使用本地数据库文件添加/编辑/检索数据

How to add/edit/retrieve data using Local Database file in Microsoft Visual Studio 2012

提问by MrVimes

I want to get into developing applications that use databases. I am fairly experienced (as an amateur) at web based database utilization (mysql, pdo, mssql with php and old style asp) so my SQL knowledge is fairly good.

我想开发使用数据库的应用程序。我在基于 Web 的数据库使用（mysql、pdo、mssql 与 php 和旧式 asp）方面相当有经验（作为业余爱好者），所以我的 SQL 知识相当不错。

Things I have done already..

我已经做过的事情..

• Create forms application
• Add four text boxes (first name, last name, email, phone)
• Added a datagrid control
• Created a database connection using 'Microsoft SQL Server Database File (SqlClient)'
• Created a table with fields corresponding to the four text boxes.

• 创建表单应用程序
• 添加四个文本框（名字、姓氏、电子邮件、电话）
• 添加了数据网格控件
• 使用“Microsoft SQL Server 数据库文件（SqlClient）”创建数据库连接
• 创建了一个表格，其中包含与四个文本框对应的字段。

What I want to be able to do now is, when a button is clicked, the contents of the four edit boxes are inserted using SQL. I don't want to use any 'wrapper' code that hides the SQL from me. I want to use my experience with SQL as much as possible.

我现在想要做的是，当单击一个按钮时，使用 SQL 插入四个编辑框的内容。我不想使用任何对我隐藏 SQL 的“包装器”代码。我想尽可能多地利用我在 SQL 方面的经验。

So I guess what I am asking is how do I now write the necessary code to run an SQL query to insert that data. I don't need to know the SQL code obviously, just the c# code to use the 'local database file' connection to run the SQL query.

所以我想我要问的是我现在如何编写必要的代码来运行 SQL 查询以插入该数据。我显然不需要知道 SQL 代码，只需要使用“本地数据库文件”连接来运行 SQL 查询的 c# 代码。

An aside question might be - is there a better/simpler way of doing this than using the 'Microsoft SQL Server Database File' connection type (I have used it because it looks like it's a way to do it without having to set up an entire sql server)

另一个问题可能是 - 是否有比使用“Microsoft SQL Server 数据库文件”连接类型更好/更简单的方法（我使用它是因为它看起来是一种无需设置整个sql服务器）

回答by Dave

This first example is an over view based upon how I think it will be easier to understand but this is not a recommended approach due to vulnerability to SQL injection (a better approach further down). However, I feel it is easier to understand.

第一个示例是基于我认为更容易理解的概述，但由于 SQL 注入的漏洞，这不是推荐的方法（更进一步的更好方法）。不过，我觉得这样更容易理解。

private void InsertToSql(string wordToInsert)
  {
        string connectionString = Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI; User ID=myDomain\myUsername;Password=myPassword;

        string queryString = "INSERT INTO table_name (column1) VALUES (" + wordToInsert + ")"; //update as you feel fit of course for insert/update etc

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open()
        SqlDataAdapter adapter = new SqlDataAdapter();
        SqlCommand command = new SqlCommand(queryString, connection);        

        command.ExecuteNonQuery();
        connection.Close();
    }
}

I would also suggest wrapping it in a try/catch block to ensure the connection closes if it errors.

我还建议将它包装在 try/catch 块中，以确保连接在出错时关闭。

I am not able to test this but I think it is OK!

我无法对此进行测试，但我认为还可以！

Again don't do the above in live as it allows SQL injection - use parameters instead. However, it may be argued it is easier to do the above if you come from PHP background (just to get comfortable).

再次不要在实时执行上述操作，因为它允许 SQL 注入 - 请改用参数。但是，如果您来自 PHP 背景（只是为了适应），则可能会认为执行上述操作会更容易。

This uses parameters:

这使用参数：

public void Insert(string customerName)
{
try
   {
    string connectionString = Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI; User ID=myDomain\myUsername;Password=myPassword;

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
    connection.Open();
    connection.Open() SqlCommand command = new SqlCommand( "INSERT INTO Customers (CustomerName" + "VALUES (@Name)", connection);

    command.Parameters.Add("@Name", SqlDbType.NChar, 50, " + customerName +");
    command.ExecuteNonQuery();
    connection.Close();
    }
 catch()
 {
     //Logic in here
 }
 finally()
 {
    if(con.State == ConnectionState.Open)
    {
        connection.Close();
    }
 }

}

}

And then you just change the SQL string to select or add!

然后您只需更改 SQL 字符串以选择或添加！

回答by 03Usr

The below is inserting data using parameters which I believe is a better approach:

下面是使用参数插入数据，我认为这是一种更好的方法：

            var insertSQL = "INSERT INTO yourTable (firstName, lastName, email, phone) VALUES (firstName, lastName, email, phone)";

            string connectionString = "Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI; User ID=userid;Password=pwd;"

            using (var cn = new SqlCeConnection(connectionString))
            using (var cmd = new SqlCeCommand(insertSQL, cn))
            {
                cn.Open();

                cmd.Parameters.Add("firstName", SqlDbType.NVarChar);
                cmd.Parameters.Add("lastName", SqlDbType.NVarChar);
                cmd.Parameters.Add("email", SqlDbType.NVarChar);
                cmd.Parameters.Add("phone", SqlDbType.NVarChar);

                cmd.Parameters["firstName"].Value = firstName;
                cmd.Parameters["lastName"].Value = lastName;
                cmd.Parameters["email"].Value = email;
                cmd.Parameters["phone"].Value = phone;
                cmd.ExecuteNonQuery();

            }

This is selecting data from database and populating datagridview:

这是从数据库中选择数据并填充 datagridview：

            var dt = new DataTable();

            string connectionString = "Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI; User ID=userid;Password=pwd;"

            using (var cn = new SqlCeConnection(connectionString )
            using (var cmd = new SqlCeCommand("Select * From yourTable", cn))
            {
                cn.Open();

                using (var reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);

                    //resize the DataGridView columns to fit the newly loaded content.
                    yourDataGridView.AutoSize = true;                                       yourDataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);

                    //bind the data to the grid
                    yourDataGridView.DataSource = dt;
                }
            }