代码之家 › 专栏 › 技术社区 › James Piper

添加到数据库oledb c#

oledb ms-access database sql c#

-1

James Piper · 技术社区 · 7 年前

        InitializeComponent();
        System.Data.OleDb.OleDbConnection conn = new
        System.Data.OleDb.OleDbConnection();
        // TODO: Modify the connection string and include any
        // additional required properties for your database.
        conn.ConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = \\crd-a555-015.occ.local\c$\Users\james.piper\Documents\Visual Studio 2015\Projects\Project V1\Project Database.accdb";
        try
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO Work_Done (employee,client,project,task,hours)" + " VALUES (@employee,@client,@project,@task,@hours)";
            cmd.Parameters.AddWithValue("@employee", user.employee);
            cmd.Parameters.AddWithValue("@client", listBox1.SelectedItem.ToString());
            cmd.Parameters.AddWithValue("@project", listBox2.SelectedItem.ToString());
            cmd.Parameters.AddWithValue("@task", listBox3.SelectedItem.ToString());
            cmd.Parameters.AddWithValue("@hours", listBox4.SelectedItem.ToString());



            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("sql insert fail");
        }

1 回复 | 直到 7 年前

Zohar Peled 7 年前

我会这样写代码:

var connectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = \\crd-a555-015.occ.local\c$\Users\james.piper\Documents\Visual Studio 2015\Projects\Project V1\Project Database.accdb";
var query = "INSERT INTO Work_Done (employee,client,project,task,hours) VALUES (@employee,@client,@project,@task,@hours)";
using (var conn = new OleDbConnection(connectionString))
{             
    using(var cmd = new OleDbCommand(query, conn))
    {
        // No need to specifiy command type, since CommandType.Text is the default

        // I'm assuming, of course, your parameter data types. You should change them if my assumptions are wrong.
        cmd.Parameters.Add("@employee", OleDbType.Integer).Value = user.employee;
        cmd.Parameters.Add("@client", OleDbType.Integer).Value = Convert.ToInt32(listBox1.SelectedItem);
        cmd.Parameters.Add("@project", OleDbType.Integer).Value =  Convert.ToInt32(listBox2.SelectedItem);
        cmd.Parameters.Add("@task", OleDbType.Integer).Value = Convert.ToInt32(listBox3.SelectedItem);
        cmd.Parameters.Add("@hours", OleDbType.Integer).Value = Convert.ToInt32(listBox4.SelectedItem);

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show($"sql insert fail: {ex}");
        }
    }                
}

主要变化如下:

Using 实现的类的每个实例的 IDisposable 界面
注意,OleDbCommand的构造函数也有OleDbConnection对象。在代码中,您没有指定与命令的活动连接。
Add 而不是 AddWithValue this blog post 找出原因。