c# - 如果包含撇号，如何插入数据？

Question

实际上，我的任务是使用 C# 将 csv 文件加载到 sql server 中，所以我用逗号将其分开 我的问题是某些字段的数据包含撇号，我正在触发插入查询以将数据加载到 sql 中，所以它会给我这样的编码错误

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;

namespace tool
{
    public partial class Form1 : Form
    {
        StreamReader reader;
        SqlConnection con;
        SqlCommand cmd;
        int count = 0;
        //int id=0;
        FileStream fs;
        string file = null;
        string file_path = null;
        SqlCommand sql_del = null;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog file1 = new OpenFileDialog();
            file1.ShowDialog();
            textBox1.Text = file1.FileName.ToString();
            file = Path.GetFileName(textBox1.Text);
            file_path = textBox1.Text;
            fs = new FileStream(file_path, FileMode.Open, FileAccess.Read);

        }

        private void button2_Click(object sender, EventArgs e)
        {

               if (file != null )
                  {
                    sql_del = new SqlCommand("Delete From credit_debit1", con);
                    sql_del.ExecuteNonQuery();
                    reader = new StreamReader(file_path);
                    string line_content = null;
                    string[] items = new string[] { };
                    while ((line_content = reader.ReadLine()) != null)
                    {
                        if (count >=4680)
                        {
                            items = line_content.Split(',');
                            string region = items[0].Trim('"');
                            string station = items[1].Trim('"');
                            string ponumber = items[2].Trim('"');
                            string invoicenumber = items[3].Trim('"');
                            string invoicetype = items[4].Trim('"');
                            string filern = items[5].Trim('"');
                            string client = items[6].Trim('"');
                            string origin = items[7].Trim('"');
                            string destination = items[8].Trim('"');
                            string agingdate = items[9].Trim('"');
                            string activitydate = items[10].Trim('"');

                            if ((invoicenumber == "-") || (string.IsNullOrEmpty(invoicenumber)))
                            {
                               invoicenumber = "null";

                            }
                            else
                            {
                                invoicenumber = "'" + invoicenumber + "'";
                            }


                            if ((destination == "-") || (string.IsNullOrEmpty(destination)))
                            {
                                destination = "null";

                           }
                            else
                            {
                               destination = "'" + destination + "'";
                            }

                            string vendornumber = items[11].Trim('"');

                            string vendorname = items[12].Trim('"');

                            string vendorsite = items[13].Trim('"');

                            string vendorref = items[14].Trim('"');

                            string subaccount = items[15].Trim('"');

                            string osdaye = items[16].Trim('"');

                            string osaa = items[17].Trim('"');


                            string osda = items[18].Trim('"');

                            string our = items[19].Trim('"');


                            string squery = "INSERT INTO credit_debit1" +
                                          "([id],[Region],[Station],[PONumber],[InvoiceNumber],[InvoiceType],[FileRefNumber],[Client],[Origin],[Destination], " +
                                          "[AgingDate],[ActivityDate],[VendorNumber],[VendorName],[VendorSite],[VendorRef],[SubAccount],[OSDay],[OSAdvAmt],[OSDisbAmt], " +
                                          "[OverUnderRecovery] ) " +
                                          "VALUES " +
                                          "('" + count + "','" + region + "','" + station + "','" + ponumber + "'," + invoicenumber + ",'" + invoicetype + "','" + filern + "','" + client + "','" + origin + "'," + destination + "," +
                                          "'" + (string)agingdate.ToString() + "','" + (string)activitydate.ToString() + "','" + vendornumber + "',' " + vendorname + "',' " + vendorsite + "',' " + vendorref + "'," +
                                         "'" + subaccount + "','" + osdaye + "','" + osaa + "','" + osda + "','" + our + "') ";

                            cmd = new SqlCommand(squery, con);
                            cmd.CommandTimeout = 1500;

                            cmd.ExecuteNonQuery();

                        }
                        label2.Text = count.ToString();

                        Application.DoEvents();
                        count++;


                    }


                    MessageBox.Show("Process completed");
                }
                else
                {
                    MessageBox.Show("path select");
                }
        }









        private void button3_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            con = new SqlConnection("Data Source=192.168.50.200;User ID=EGL_TEST;Password=TEST;Initial Catalog=EGL_TEST;");
            con.Open();
        }
    }

}

供应商名称字段包含数据(MCCOLLISTER'S TRANSPORTATION)那么如何传递这些数据

Answer 1

使用prepared statements , 在这种情况下 SqlParameterCollection.AddWithValue或等效的。有多种教程可供使用。