troubleshooting Question

Handling apostrophes when generating SQL inserts

Avatar of raymurphy
raymurphy asked on
C#Microsoft SQL Server 2005
8 Comments1 Solution807 ViewsLast Modified:
Developing some SQL-generator code to generate a file of INSERT statements from an SQL Server db (code snippet shown below).
 
 This code works fine, and we end up with a generated file of INSERT statements, but have just encountered a problem  in trying to run the generated SQL file against a test database.
 
 
         using (SqlConnection connection = new SqlConnection(connectionStrin
         {
           using (SqlCommand command = new SqlCommand())
           {
                 // Initialize the SQL Connection
                 command.Connection = connection;
                 command.CommandText = "SELECT * FROM " + tableName;
                   connection.Open();
                   
                   using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        // Format the first part of the INSERT statement. This part remains
                        // constant for every row that is generated.
                        string sInsert = string.Format("INSERT INTO [{0}] ( ", tableName);
                       
                        // Loop through all the column names
                        for (int iCol = 0; iCol < reader.FieldCount; iCol++)
                        {
                            sInsert += string.Format("[{0}]", reader.GetName(iCol));
                            if (iCol < reader.FieldCount - 1)
                                sInsert += ",";
                        }

                        // Start outputting the data values for those columns
                        sInsert += ") VALUES ({0})";

                        // Read each row of the table
                        object[] objs = new object[reader.FieldCount];
                        int nLines = 1;

                        while (reader.Read())
                        {
                            int n = reader.GetValues(objs);
                            string sValues = string.Empty;

                            // Go through each column of the row, and generate a string
                   
                            for (int i = 0; i < n; i++)
                            {
                                try
                                {
                                    string sVal = (reader.IsDBNull(i)) ? "null" : ObjectToSQLString(objs[i]);
                                    sValues += sVal;
                                    if (i < n - 1)
                                        sValues += ",";
                                }
                                catch (DataException)
                                {
                                    Console.WriteLine(string.Format("Conversion error in Record {0}, Column {1}", nLines, reader.GetName(i) ));
                                    return;
                                }
                            }

                            // Dump the INSERT statement to the file
                            outputStream.WriteLine(string.Format(sInsert, sValues));
                            nLines++;
                        }
                    }
         
             }
         }  

        static string ObjectToSQLString(object o)
        {
            if (o == null || o == DBNull.Value)
                return "null";
         
            Type t = o.GetType();
 
            if (t == typeof (string))
                return string.Format("'{0}'", ((string) o).Trim());
            if (t == typeof (int))
                return ((int) o).ToString();
           
            if (t == typeof (Int16))
                return ((Int16) o).ToString();
            if (t == typeof (long))
                return ((long) o).ToString();
            if (t == typeof (float))
                return ((float) o).ToString();
            if (t == typeof (double))
                return ((double) o).ToString();
            if (t == typeof (bool))
                return ((bool) o).ToString();
            if (t == typeof (DateTime))
                return string.Format("'{0}'", ((DateTime) o));

            throw new DataException();
        }
       
 This code works fine, and we end up with a generated file of INSERT statements, but have just encountered a problem
 in trying to run the generated SQL file against a test database. The cutdown generated SQL file below illustrates
 the problem :
 
 INSERT INTO [customercopy] ( [customer_no],[customer_name]) VALUES (4217,'REGINALD COLIN O'REILLY')
 
 and the problem is where the customer_name value contains an apostrophe (O'REILLY in tyhis case) - as that INSERT statement would fail.
 
 Is there any way of amending my SQL-generator code to address this issue where name fields on the sourtce database might contain apostrophes ?
 
Thanks

Ray
 

 
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros