如何插入数据是否包含撇号？ [英] how to insert data if it contain apostrophe?

问题描述

Actally我的任务是用C＃加载CSV文件到SQL Server，所以我必须用逗号我的问题分裂是一些字段的数据包含apostrop和IM射击插入查询将数据加载到SQL因此它给予错误我的编码像

Actally my task is load csv file into sql server using c# so i have split it by comma my problem is that some field's data contain apostrop and i m firing insert query to load data into sql so its give error my coding like that

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();
        }
    }

}

vendername场包含数据（MCCOLLISTER的交通运输），所以如何通过这个数据

vendername field contain data (MCCOLLISTER'S TRANSPORTATION) so how to pass this data

推荐答案

使用的预处理语句的，在这种情况下的 SqlParameterCollection.AddWithValue 或同等学历。有多种可用于本教程。

Use prepared statements, in this case SqlParameterCollection.AddWithValue or equivalent. There are a variety of tutorials available for this.

这篇关于如何插入数据是否包含撇号？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！