Пример #1
0
 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();
 }
Пример #2
0
        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);
        }