Saturday 7 June 2008

Split SQL statements in C#.NET

Have you ever wanted to split a series a SQL statements? Probably not, but here is a code sample anyway! It works by looping through the string and splits the statements on semi-colons (only if they aren't part of some text). I used this on a website that executed SQL code where I wanted to show feedback for each query.



using System;
using System.Collections.Generic;

public class StringUtility
{
public static string SplitSQLStatements( string sql )
{
List<string> sqlStatements = new List<string>();

char separator = ';';

bool isQuoted = false;

int substringStartIndex = 0;

if ( sql.Contains( ";" ) )
{
for ( int characterIndex = 0; characterIndex < sql.Length; characterIndex++ )
{
if ( sql[characterIndex] == '\'' )
{
isQuoted = !isQuoted;
}
else if ( sql[characterIndex] == separator && !isQuoted )
{
sqlStatements.Add( sql.Substring( substringStartIndex, characterIndex + 1 - substringStartIndex ).Trim() );

substringStartIndex = characterIndex + 1;

if ( sql.IndexOf( separator, substringStartIndex, sql.Length - substringStartIndex ) == -1 )
{
break;
}
}
}
}

if ( !sql.EndsWith( separator.ToString() ) )
{
sql = string.Format( "{0};", sql );
}

if ( substringStartIndex < sql.Length - 1 )
{
sqlStatements.Add( sql.Substring( substringStartIndex, sql.Length - substringStartIndex ).Trim() );
}

return sqlStatements.ToArray();
}
}

No comments: