public SqlServerResultWriter(string connectionString, SqliteDb db = null, TextWriter verbose = null, TextWriter e = null, TextWriter warning = null)
{
//var connstr = "Data Source=.;Initial Catalog=Test;Integrated Security=SSPI;Provider=SQLOLEDB;";
dbCanConnect = false;
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
dbCanConnect = true;
dbConnectionString = connectionString;
connection.Close();
}
VerboseOut = verbose ?? TextWriter.Null;
ErrorOut = e ?? TextWriter.Null;
WarningOut = warning ?? TextWriter.Null;
Key = "";
ResultMode = "SQLSVR";
ContextStack = new Stack <ResultContext>();
CurrentContextName = "";
TableIsCreated = false;
SqliteDb = db;
CreateLookup();
}
public IResultWriter WriteResult(IReadOnlyList <IResultSetValue> result, Globals.ResultWriterDestination dest)
{
if (!TableIsCreated)
{
string sql = "CREATE TABLE [" + CurrentContextName + "] ( {0} );";
List <string> cols = new List <string>();
foreach (var r in result)
{
var colname = r.ColumnInfo.Name;
bool isQuoted = false;
string mssqlDatatype = "";
if (r.ColumnInfo.TableName != null)
{
var query = "SELECT ColumnType FROM TableMetadata WHERE Tablename = " +
r.ColumnInfo.TableName.SingleQuote() + " AND ColumnName = " + colname.SingleQuote() + ";";
var enumerator = SqliteDb.GetEnumeratorForQuery(query);
if (enumerator.MoveNext())
{
var sqliteDatatype = enumerator.Current[0].ToString();
var tuple = SqliteToMsSqlLookup
.Where(t => (t.Item1.ToString().ToLower() == sqliteDatatype.ToLower()))
.Select(t => t).ElementAt(0);
var etlDatatype = tuple.Item1;
mssqlDatatype = (tuple.Item2 != null) ? tuple.Item2 : "varchar(max)";
isQuoted = (tuple.Item3.ToLower() == "quoted");
if (etlDatatype == DataType.@decimal || etlDatatype == DataType.@double || etlDatatype == DataType.@float)
{
string precscale = "18";
var precisionSql = "with cte as (" +
"select distinct length(" + colname + ")-1 as precision, length(" + colname + ") - instr(" + colname + ", '.') as scale, " +
"length(" + colname + ")-1 - (length(" + colname + ") - instr(" + colname + ", '.')) as beforeDec " +
"from " + r.ColumnInfo.TableName +
") " +
"select case when max(precision)+max(beforeDec)-1 - min(scale) >= 38 then 38 " +
"else max(precision)+max(beforeDec)-1 " +
"end as precision, max(scale) as scale " +
"from cte;";
enumerator = SqliteDb.GetEnumeratorForQuery(precisionSql);
if (enumerator.MoveNext())
{
int precnum, scalenum = 0;
int.TryParse(enumerator.Current[0].ToString(), out precnum);
int.TryParse(enumerator.Current[1].ToString(), out scalenum);
var prec = (string.IsNullOrWhiteSpace(enumerator.Current[0].ToString()) || precnum <= 0) ? "18," : enumerator.Current[0].ToString() + ",";
var scale = (string.IsNullOrWhiteSpace(enumerator.Current[1].ToString()) || scalenum <= 0) ? "2" : enumerator.Current[1].ToString();
precscale = prec + scale;
}
mssqlDatatype = string.Format(mssqlDatatype, precscale);
}
else if (etlDatatype == DataType.@sbyte || etlDatatype == DataType.@uint || etlDatatype == DataType.@ulong || etlDatatype == DataType.@ushort)
{
mssqlDatatype = string.Format(mssqlDatatype, colname);
}
}
else
{
mssqlDatatype = string.Format("varchar(max)", colname);
isQuoted = true;
}
}
else
{
// NPS_TODO try to ascertain datatype
mssqlDatatype = "nvarchar(max)";
ContextColumnIsQuoted.Add("null_" + colname, true);
}
cols.Add(colname + " " + mssqlDatatype);
ContextColumnIsQuoted.Add(r.ColumnInfo.TableName + "_" + colname, isQuoted);
}
sql = string.Format(sql, string.Join(",", cols));
Console.WriteLine(sql);
Write(sql, dest);
TableIsCreated = true;
}
Write(GetResultAsSqlInsertString(result), dest);
return(this);
}