public string GetCommandString(string connectionString, string tableName) { StringBuilder sb = new StringBuilder(); sb.Append("select "); Dictionary <String, OleDbColumnDefinition> schema = new OleDbSchemaProvider().GetSchema(connectionString, tableName); String comma = String.Empty; foreach (KeyValuePair <String, OleDbColumnDefinition> kvp in schema) { sb.Append(comma + kvp.Value.Name); comma = ", "; // When I had the IIF(EMPTY(msClDate),null,msClDate) as msClDate // VFP blew chow with Error # 1890 // And you get the same thing in the VFP Dev Edition if you issue // SELECT IIF(EMPTY(mscldate),null,mscldate) FROM in_msg using // the Laptop //if (kvp.Value.IsDate) //{ // String s = String.Format("IIF(EMPTY({0}),null,{0}) as {0}", kvp.Value.Name); // sb.Append(s); //} //else //{ // sb.Append(kvp.Value.Name); //} } /* * The saga of deleted().... 1st I tried the following * * sb.Append(comma + "IIF(DELETED(),1,0) AS " + Constants.DILayer.DeletedColumnName); * * A problem with the above is the the column was preceived as having a Decimal type - which * makes sense. While the values where either 0 or 1 nothing coerced the type to boolean * * So I tried the following .... * * sb.Append(comma + " DELETED() AS " + Constants.DILayer.DeletedColumnName); * * This comand resulted in a "correct" DataTable in that it had an SqlDeleted column * of type boolean * * However, it still didn't work. The following is the bulk insert command and it knows nothing about SqlDeleted c * * insert bulk IN_MSG ([MSNUMB] Char(6) COLLATE Latin1_General_BIN, [MSTYPE] Char(1) COLLATE Latin1_General_BIN, [MSGNUM] Char(6) COLLATE Latin1_General_BIN, * [MSRNUM] Char(6) COLLATE Latin1_General_BIN, [MSDATE] Date, [MSTIME] Char(5) COLLATE Latin1_General_BIN, [MSUSER] Char(2) COLLATE Latin1_General_BIN, [MSCLDATE] Date, * [MSCLTIME] Char(5) COLLATE Latin1_General_BIN, [MSCLUSER] Char(2) COLLATE Latin1_General_BIN, [MSPRINT] Char(1) COLLATE Latin1_General_BIN, * [MSTO] Char(3) COLLATE Latin1_General_BIN, [MSFLAG] Char(1) COLLATE Latin1_General_BIN, [MSNOTTYP] Char(1) COLLATE Latin1_General_BIN, [MSBEGIN] Date, * [MSDAY] Decimal(2,0), [MSBEGTIME] Char(5) COLLATE Latin1_General_BIN, [MSENDTIME] Char(5) COLLATE Latin1_General_BIN, * [MSTXT] Text COLLATE SQL_Latin1_General_CP1_CI_AS, [MSPRI] Decimal(2,0), [MSSTAT] Char(1) COLLATE Latin1_General_BIN, [MSIPADDR] Char(15) COLLATE Latin1_General_BIN, [MSAUTOCLS] Bit) * */ sb.Append(" from " + tableName); return(sb.ToString()); }
public void TestSchemaBuilder() { //ITableProcessor tp = new NumericScrubProcessor(); //tp.Process(LaptopHostConnectionString, "RS_POLCY",null,null); OleDbSchemaProvider sp = new OleDbSchemaProvider(); Dictionary <String, OleDbColumnDefinition> schema = sp.GetSchema(LaptopHostConnectionString, "RS_POLCY"); foreach (KeyValuePair <String, OleDbColumnDefinition> kvp in schema) { TestContext.WriteLine(String.Format("{0} - {1}", kvp.Key, kvp.Value.ToString())); } TestContext.WriteLine("Here boss"); }
public void TestBasicCommandStringProvider() { const string tableName = "IN_MSG"; ICommandStringProvider csp = new SelectCommandStringProvider(); String actual = csp.GetCommandString(VfpConnectionString, tableName); String upperActual = actual.ToUpper(); Assert.IsTrue(upperActual.StartsWith("SELECT ")); Assert.IsTrue(upperActual.Replace(" ", String.Empty).EndsWith("FROM" + tableName)); Dictionary <String, OleDbColumnDefinition> schema = new OleDbSchemaProvider().GetSchema(VfpConnectionString, tableName); foreach (KeyValuePair <String, OleDbColumnDefinition> kvp in schema) { Assert.IsTrue(upperActual.Contains(kvp.Value.Name + ",") || upperActual.Contains(kvp.Value.Name + " ")); } // Can we run it? Turns out - we can't. VFP raises Error # 1890 // if you issued SELECT IIF(EMPTY(mscldate),null,mscldate) FROM in_msg // on the Laptop DataTable dt = Helper.GetOleDbDataTable(VfpConnectionString, actual); TestContext.WriteLine(actual); }
public void Process(string sourceConnectionString, string sourceTableName, string destinationConnectionString, string destinationTableName) { OleDbSchemaProvider schemaProvider = new OleDbSchemaProvider(); Dictionary <String, OleDbColumnDefinition> schema = schemaProvider.GetSchema(sourceConnectionString, sourceTableName); IEnumerable <OleDbColumnDefinition> numericColDefs = schema.Values.Where(colDef => colDef.Type == System.Data.OleDb.OleDbType.Numeric).ToList(); if (numericColDefs.Count() == 0) { return; } VfpConnectionStringBuilder vfpConnStrBldr = new VfpConnectionStringBuilder(sourceConnectionString); sourceConnectionString = vfpConnStrBldr.ConnectionString; int batchSize = BatchSizeProvider.GetBatchSize(sourceTableName); int recordCount = Convert.ToInt32(Helper.GetOleDbScaler(sourceConnectionString, "SELECT COUNT(*) FROM " + sourceTableName)); using (OleDbConnection sourceConnection = new OleDbConnection(sourceConnectionString)) { sourceConnection.Open(); int minRecno, maxRecno, recsProcessed; recsProcessed = 0; String comma = String.Empty; String columnList = String.Empty; StringBuilder sb = new StringBuilder().Append("SELECT "); foreach (OleDbColumnDefinition colDef in numericColDefs) { sb.Append(comma + colDef.Name); comma = ","; } sb.Append(" FROM " + sourceTableName + " WHERE "); String selectAllColsCmdStr = sb.ToString(); while (true) { minRecno = recsProcessed; maxRecno = minRecno + batchSize; // SELECT <all numeric cols> FROM <> RECNO() > 0 and RECNO() <= 25000 if (!TryRead(sourceConnectionString, String.Format(selectAllColsCmdStr + GetRecNoWhereClause(minRecno, maxRecno)))) { // if we cant read all the numerics we're good-to-go. Otherwise we'll go column-by-column foreach (OleDbColumnDefinition colDef in numericColDefs) { String recnoWhereClause = GetRecNoWhereClause(minRecno, maxRecno); String cmdStr = String.Format("SELECT {0} FROM {1} WHERE {2}", colDef.Name, sourceTableName, recnoWhereClause); if (!TryRead(sourceConnectionString, cmdStr)) { String maxVal; if (colDef.NumericScale > 0) { // 4,2 - -9.99 - 99.99 maxVal = new String('9', (int)(colDef.NumericPrecision - colDef.NumericScale)) + "." + new String('9', (int)colDef.NumericScale); } else { maxVal = new String('9', (int)(colDef.NumericPrecision)); } cmdStr = String.Format("UPDATE {0} SET {1} = 0 WHERE NOT BETWEEN({1},-{2},{2}) AND {3}", sourceTableName, colDef.Name, maxVal, recnoWhereClause); CommandStrings.Add(cmdStr); Helper.ExecuteOleDbNonQuery(sourceConnectionString, cmdStr); } } } recsProcessed = recsProcessed + batchSize; if (recsProcessed >= recordCount) { break; } } sourceConnection.Close(); } return; }