lundi 25 mars 2013

Excecute SQL batch from C#

Two ways, extending two objects. For a SSMS like use, batchSeparator should be equal to go.

    public static class SomeExtensionsClass {
        public static void ExecuteBatchFromFile(this DataContext dc, String fileName, String batchSeparator) {
            StringBuilder sb = new StringBuilder();
            foreach (String sqlLine in File.ReadAllLines(fileName)) {
                if (sqlLine == batchSeparator) {
                    if (sb.Length != 0) {
                        dc.ExecuteCommand(sb.ToString());
                        sb.Remove(0, sb.Length); //On attend de passer en .Net 4 pour le .Clear
                    }
                } else {
                    sb.AppendLine(sqlLine);
                }
            }
            if (sb.Length != 0)
                dc.ExecuteCommand(sb.ToString());
        }

        public static void ExecuteBatchFromFile(this ObjectContext oc, String fileName, String batchSeparator) {
            StringBuilder sb = new StringBuilder();
            foreach (String sqlLine in File.ReadAllLines(fileName)) {
                if (sqlLine == batchSeparator) {
                    if (sb.Length != 0) {
                        oc.ExecuteStoreCommand(sb.ToString());
                        sb.Remove(0, sb.Length); //On attend de passer en .Net 4 pour le .Clear
                    }
                } else {
                    sb.AppendLine(sqlLine);
                }
            }
            if (sb.Length != 0)
                oc.ExecuteStoreCommand(sb.ToString());
        }
    }