how to insert data if it contain apostrophe ?

Question

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 field contain data (MCCOLLISTER'S TRANSPORTATION) so how to pass this data

Answer 1

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

Answer 2

You are very naughty for building your sql statements that way, Santa Claus is definitely not going to visit you this year. Doing queries the way you are is opening yourself to sql injection attacks, intentional and unintentional as you've discovered with the '.

You should use parameterized query strings or stored procedures.

const string connString = "Data Source=localhost;Initial Catalog=OnlineQuiz;Integrated Security=True";

static void Main(string[] args)
{
    string query = string.Format("SELECT * FROM [User] WHERE name like @name");

    using (SqlConnection conn = new SqlConnection(connString))
    {
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            cmd.Parameters.AddWithValue("@name", "F%");

            conn.Open();
            using (SqlDataReader reader = cmd.ExecuteReader())
            {

                while (reader.Read())
                {
                    Console.WriteLine(reader.GetValue(1));
                }
            }
        }
    }
}

Answer 3

You need to escape the apostrophe by adding a second apostrophe:

vendorname = vendorname.Replace("'", "''");

Disclaimer: Writing a raw SQL statement without using parameters is dangerous. Ideally, you should write a full SQL insert statement with assumed parameters, and instead of concatenating the value directly into the string, pass it in as a parameter:

string parameterizedSQL = "insert into credit_debit1 (id,region,station) values (@count, @region,@station)";

SqlCommand cmd = new SqlCommand(parameterizedSQL, con);
cmd.Parameters.Add("@count", SqlDbType.Int).Value = count;
cmd.Parameters.Add("@region", SqlDbType.VarChar).Value = region;
cmd.Parameters.Add("@station", SqlDbType.VarChar).Value = station;
cmd.ExecuteNonQuery();