public QueryBuilder InsertAllColumns(DataTable queryResult, ObjectMetadata tableMeta)
{
sb.Append("INSERT INTO " + tableMeta.ToFullString() + " (");
GetColumnList(queryResult);
sb.AppendLine(")");
return(this);
}
public static string GenerateInsertFor(DataTable table, ObjectMetadata meta, bool newLineBetweenColumns = true, bool newLineBetweenValues = false)
{
if (table.Rows.Count == 0)
{
AddRowWithDefaultValuesTo(table);
}
var sb = new StringBuilder();
sb.Append("INSERT INTO " + meta.ToFullString() + " (");
sb.AppendColumnNameList(table, newLineBetweenColumns);
sb.AppendLine(")");
sb.Append("VALUES");
sb.AppendListOfRows(table, newLineBetweenValues);
sb.Append(";");
return(sb.ToString());
}
public DataTable GetTableSchema(ObjectMetadata meta, CancellationToken token)
{
DataSet ds = new DataSet();
string query = string.Format(@"
SET FMTONLY ON;
select * from {0};
SET FMTONLY OFF;", meta.ToFullString());
QueryManager.Run(connectionString, token, (qm) =>
{
qm.Fill(query, ds);
if (ds.Tables.Count == 0)
{
throw new InvalidOperationException("Trying to get table schema, got no result.");
}
});
var table = ds.Tables[0];
return(table);
}
public QueryBuilder CreateTempTableFor(DataTable queryResult, ObjectMetadata tableMeta)
{
sb.AppendFormat("CREATE TABLE {0} (", tableMeta.ToFullString());
sb.AppendLine();
int columnCount = 1;
foreach (DataColumn col in queryResult.Columns)
{
sb.AppendFormat("\t[{0}] {1}", col.ColumnName, DbTypeConverter.TranslateToSqlType(col.DataType).ToUpper());
if (columnCount++ < queryResult.Columns.Count)
{
sb.Append(",");
}
sb.AppendLine();
}
sb.Append(");");
sb.AppendLine();
return(this);
}
public DataTable SelectTopNFrom(ObjectMetadata table, CancellationToken token, int limit = 1)
{
DataTable dt = QueryManager.Run <DataTable>(connectionString, token, (qm) =>
{
string query = String.Format(@"set rowcount {1}; select * from {0}; set rowcount 0;", table.ToFullString(), limit);
DataSet ds = new DataSet();
qm.Fill(query, ds);
if (ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
return(null);
});
if (dt == null)
{
throw new ArgumentException("table does not exist");
}
return(dt);
}
public QueryBuilder Into(ObjectMetadata targetTable)
{
sb.AppendLine("INTO " + targetTable.ToFullString());
return(this);
}
public QueryBuilder From(ObjectMetadata tableMeta)
{
sb.AppendLine("FROM " + tableMeta.ToFullString());
return(this);
}