使用C#将数据插入Oracle数据库 [英] Inserting data into Oracle database using c#

问题描述

使用按钮提交查询时，出现此错误"ora-00928缺少选择关键字".我对其他按钮有其他查询，并且select语句有效，但是由于某些原因，insert语句无效. 我看过其他有关此错误的帖子，但似乎无济于事

I get this error "ora-00928 missing select keyword" when using a button to submit the query. I have other queries on other buttons and the select statements work but for some reason the insert statement doesnt work. I've seen other posts on this error but nothing seems to help mine

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;

namespace Oracle
{
    public partial class Register : Form
    {
        string name;
        int pass;
        int repass;
        string email;
        public Register()
        {
            InitializeComponent();
        }
        OleDbConnection con = new OleDbConnection("Provider=MSDAORA;Data Source=DESKTOP-HQCK6F1:1521/CTECH;Persist Security Info=True;User ID=system;Password=G4ming404;Unicode=True");
        OleDbCommand cmd = new OleDbCommand();

        private void button1_Click(object sender, EventArgs e)
        {
            name = txtname.Text;
            pass = Convert.ToInt32(txtpass.Text);
            repass = Convert.ToInt32(txtrepass.Text);
            email = txtemail.Text;
            cmd.Connection = con;
            cmd.CommandText = "INSERT INTO SYSTEM.CUSTOMER('CUSTOMER_ID', 'CUSTOMER_NAME', 'CUSTOMER_EMAIL', 'CUSTOMER_PASSWORD')" + "VALUES('%"+ null + "%','%'" + txtname.Text + "%','%'" + txtemail.Text + "%','%'" + txtpass.Text + "%')";
            con.Open();

            if (pass == repass)
            {
                int rowsUpdated = cmd.ExecuteNonQuery();
                if (rowsUpdated == 0)
                {
                    MessageBox.Show("Record not inserted");
                }
                else {
                    MessageBox.Show("Success!");
                   }

                MessageBox.Show("User has been created");
                this.Close();
                Form1 login = new Form1();
                login.Show();

            }
            else {
                MessageBox.Show("Password mismatch");
            }
            con.Dispose();
        }

推荐答案

查询中存在一些问题.
首先，您不需要在列名两边加上单引号，只有在任何列的名称与保留关键字相同的情况下才需要双引号.

There are some problems in your query.
First you don't need single quotes around the column names, You need double quotes only if any of your columns has the same name as a reserved keyword.

第二个问题是输入框文本与查询命令的字符串连接.应该不惜一切代价避免这种情况，因为它是解析问题和sql注入hack的源头.请改用参数.

Second problem is the string concatenation of the input boxes text to the query command. This should be avoided at all cost because it is the source of parsing problems and sql injection hacks. Use parameters instead.

最后，您的OleDbConnection应该在您的方法本地，并且在using语句内，以确保即使在出现异常的情况下也能正确处置非托管资源

Finally your OleDbConnection should be local to your method and inside a using statement to ensure correct disposing of the unmanaged resources also in case of exceptions

private void button1_Click(object sender, EventArgs e)
{
    name = txtname.Text;
    pass = Convert.ToInt32(txtpass.Text);
    repass = Convert.ToInt32(txtrepass.Text);
    email = txtemail.Text;

    if (pass != repass)
    {
        MessageBox.Show("Password mismatch");
        return;
    }
    string cmdText = @"INSERT INTO SYSTEM.CUSTOMER 
                       (CUSTOMER_NAME, CUSTOMER_EMAIL, CUSTOMER_PASSWORD) 
                       VALUES(?,?,?)";
    using(OleDbConnection con = new OleDbConnection(.......))
    using(OleDbCommand cmd = new OleDbCommand(cmdText, con))
    {
         con.Open();
         cmd.Parameters.Add("p1", OleDbType.VarChar).Value = txtname.Text;
         cmd.Parameters.Add("p2", OleDbType.VarChar).Value = txtemail.Text;
         cmd.Parameters.Add("p3", OleDbType.VarChar).Value = txtpass.Text ;
         int rowsUpdated = cmd.ExecuteNonQuery();
         if (rowsUpdated == 0)
         {
             MessageBox.Show("Record not inserted");
         }
         else 
         {
            MessageBox.Show("Success!");
            MessageBox.Show("User has been created");
         }
    }
    Form1 login = new Form1();
    login.Show();
}

我还删除了CUSTOMER_ID字段的参数传递.这似乎是一个由Oracle自动计算的字段(一个序列?)，因此您无需为其提供值.

I have also removed the passing of a parameter for the CUSTOMER_ID field. This seems to be a field that is calculated automatically by Oracle (a Sequence?) and thus you don't need to provide a value for it.

最后一个建议.不要在数据库中以纯文本形式存储密码.这是非常严重的安全隐患.您应该阅读在数据库中存储密码的最佳方法

Finally an advice. Do not store password in plain text in the database. This is a security risk very seriours. You should read Best way to store passwords in a database

这篇关于使用C#将数据插入Oracle数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！