} // GetAssemblyQualifiedNoVersionName
private static async System.Threading.Tasks.Task WriteAssociativeColumnsArray(
Newtonsoft.Json.JsonTextWriter jsonWriter
, System.Data.Common.DbDataReader dr, RenderType_t renderType)
{
//await jsonWriter.WriteStartObjectAsync();
await jsonWriter.WriteStartObjectAsync();
for (int i = 0; i <= dr.FieldCount - 1; i++)
{
await jsonWriter.WritePropertyNameAsync(dr.GetName(i));
await jsonWriter.WriteStartObjectAsync();
await jsonWriter.WritePropertyNameAsync("index");
await jsonWriter.WriteValueAsync(i);
if (renderType.HasFlag(RenderType_t.WithDetail))
{
await jsonWriter.WritePropertyNameAsync("fieldType");
// await jsonWriter.WriteValueAsync(GetAssemblyQualifiedNoVersionName(dr.GetFieldType(i)));
await jsonWriter.WriteValueAsync(GetTypeName(dr.GetFieldType(i), renderType));
}
await jsonWriter.WriteEndObjectAsync();
} // Next i
await jsonWriter.WriteEndObjectAsync();
} // WriteAssociativeArray
} // WriteAssociativeArray
private static void WriteComplexArray(Newtonsoft.Json.JsonTextWriter jsonWriter, System.Data.Common.DbDataReader dr, RenderType_t renderType)
{
//jsonWriter.WriteStartObject();
jsonWriter.WriteStartArray();
for (int i = 0; i <= dr.FieldCount - 1; i++)
{
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("name");
jsonWriter.WriteValue(dr.GetName(i));
jsonWriter.WritePropertyName("index");
jsonWriter.WriteValue(i);
if (renderType.HasFlag(RenderType_t.WithDetail))
{
jsonWriter.WritePropertyName("fieldType");
//jsonWriter.WriteValue(GetAssemblyQualifiedNoVersionName(dr.GetFieldType(i)));
jsonWriter.WriteValue(GetTypeName(dr.GetFieldType(i), renderType));
}
jsonWriter.WriteEndObject();
}
// jsonWriter.WriteEndObject();
jsonWriter.WriteEndArray();
} // WriteAssociativeArray
} // End Sub WriteAssociativeArray
private static void WriteAssociativeArray(Newtonsoft.Json.JsonTextWriter jsonWriter, System.Data.Common.DbDataReader dr, bool dataType)
{
// JSON:
//{
// "column_1":{ "index":0,"fieldType":"int"}
// ,"column_2":{ "index":1,"fieldType":"int"}
//}
jsonWriter.WriteStartObject();
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WritePropertyName(dr.GetName(i));
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("index");
jsonWriter.WriteValue(i);
#if false
jsonWriter.WritePropertyName("columnName");
jsonWriter.WriteValue(dr.GetName(i));
#endif
if (dataType)
{
jsonWriter.WritePropertyName("fieldType");
jsonWriter.WriteValue(GetAssemblyQualifiedNoVersionName(dr.GetFieldType(i)));
} // End if (dataType)
jsonWriter.WriteEndObject();
} // Next i
jsonWriter.WriteEndObject();
} // End Sub WriteAssociativeArray
public DeserializerKey(int hashCode, int startBound, int length, bool returnNullIfFirstMissing, IDataReader reader, bool copyDown)
{
this.hashCode = hashCode;
this.startBound = startBound;
this.length = length;
this.returnNullIfFirstMissing = returnNullIfFirstMissing;
if (copyDown)
{
this.reader = null;
names = new string[length];
types = new Type[length];
int index = startBound;
for (int i = 0; i < length; i++)
{
names[i] = reader.GetName(index);
types[i] = reader.GetFieldType(index++);
}
}
else
{
this.reader = reader;
names = null;
types = null;
}
}
public static void MultipleDataSets(string strSQL)
{
DataSetSerialization ser = new DataSetSerialization();
using (System.Data.Common.DbDataReader dr = SQL.ExecuteReader(strSQL
, System.Data.CommandBehavior.CloseConnection
| System.Data.CommandBehavior.SequentialAccess
)
)
{
Table tbl = null;
do
{
tbl = new Table();
for (int i = 0; i < dr.FieldCount; ++i)
{
tbl.Columns.Add(
new ColumnInfo()
{
ColumnName = dr.GetName(i),
FieldType = dr.GetFieldType(i)
}
);
} // Next i
if (dr.HasRows)
{
while (dr.Read())
{
object[] thisRow = new object[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; ++i)
{
thisRow[i] = dr.GetValue(i);
} // Next i
tbl.Rows.Add(thisRow);
} // Whend
} // End if (dr.HasRows)
ser.Tables.Add(tbl);
} while (dr.NextResult());
} // End Using dr
string str = EasyJSON.JsonHelper.SerializePretty(ser);
System.Console.WriteLine(str);
DataSetSerialization ser2 = EasyJSON.JsonHelper.Deserialize <DataSetSerialization>(str);
System.Console.WriteLine(ser2);
} // End Sub MultipleDataSets
} // End Function Sql2DataTableTest
public static DataTable Sql2DataTableTest(System.Data.Common.DbCommand cmd, DataTable dt)
{
if (dt == null)
{
dt = new DataTable();
}
//else dt.Clear();
using (System.Data.Common.DbDataReader rdr = CoinBaseSharp.SQL.ExecuteReader(cmd, System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.CloseConnection))
{
int fieldCount = rdr.FieldCount;
string[] fieldNames = new string[fieldCount];
System.Type[] fieldTypes = new System.Type[fieldCount];
object[] objs = new object[fieldCount];
for (int i = 0; i < fieldCount; ++i)
{
fieldNames[i] = rdr.GetName(i);
fieldTypes[i] = rdr.GetFieldType(i);
dt.Columns.Add(fieldNames[i], fieldTypes[i]);
} // Next i
if (rdr.HasRows)
{
while (rdr.Read())
{
DataRow dr = dt.NewRow();
for (int i = 0; i < fieldCount; ++i)
{
object obj = rdr.GetValue(i);
if (obj == null)
{
dr[fieldNames[i]] = System.DBNull.Value;
}
else
{
dr[fieldNames[i]] = obj;
}
} // Next i
dt.Rows.Add(dr);
} // Whend
} // End if (rdr.HasRows)
} // End Using rdr
// string str = dt.ToHtml();
// System.Console.WriteLine(str);
// System.Console.WriteLine(dt.Rows.Count);
return(dt);
} // End Function Sql2DataTableTest
private static void GetSetProperty(IDataRecord dataRecord, ILGenerator generator, LocalBuilder result, int i, PropertyInfo propertyInfo, OpCode loadArg, LocalBuilder counter)
{
var endIfLabel = generator.DefineLabel();
if (propertyInfo != null && propertyInfo.GetSetMethod(true) != null)
{
generator.Emit(OpCodes.Ldarg_0);
generator.Emit(OpCodes.Ldc_I4, i);
generator.Emit(OpCodes.Callvirt, isDBNullMethod);
generator.Emit(OpCodes.Brtrue, endIfLabel);
generator.Emit(OpCodes.Ldloc, result);
generator.Emit(OpCodes.Ldarg_0);
generator.Emit(OpCodes.Ldc_I4, i);
generator.Emit(OpCodes.Callvirt, getValueMethod);
if (propertyInfo.PropertyType.IsGenericType() &&
propertyInfo.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable <>))
)
{
var nullableType = propertyInfo.PropertyType.GetGenericTypeDefinition().GetGenericArguments()[0];
if (!nullableType.IsEnum())
{
generator.Emit(OpCodes.Unbox_Any, propertyInfo.PropertyType);
}
else
{
generator.Emit(OpCodes.Unbox_Any, nullableType);
generator.Emit(OpCodes.Newobj, propertyInfo.PropertyType);
}
}
else
{
generator.Emit(OpCodes.Unbox_Any, dataRecord.GetFieldType(i));
}
generator.Emit(OpCodes.Callvirt, propertyInfo.GetSetMethod(true));
if (counter != null)
{
generator.Emit(OpCodes.Ldloc, counter);
generator.Emit(OpCodes.Ldc_I4_1);
generator.Emit(OpCodes.Add);
generator.Emit(OpCodes.Stloc, counter);
}
generator.MarkLabel(endIfLabel);
}
}
private void AddReaderToGrid(BasePopupViewModel viewModel, System.Data.Common.DbDataReader dataReader)
{
DataTable dtSource = new DataTable();
dtSource.TableName = "Data";
for (int i = 0; i < dataReader.FieldCount; i++)
{
DataColumn col = new DataColumn(dataReader.GetName(i), dataReader.GetFieldType(i));
dtSource.Columns.Add(col);
}
while (dataReader.Read())
{
object[] values = new object[dataReader.FieldCount];
dataReader.GetValues(values);
dtSource.LoadDataRow(values, false);
}
dataReader.Close();
AddControlsToGrid(viewModel, dtSource, "");
}
} // End Sub WriteAssociativeArray
private static void WriteArray(Newtonsoft.Json.JsonTextWriter jsonWriter, System.Data.Common.DbDataReader dr)
{
jsonWriter.WriteStartArray();
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("index");
jsonWriter.WriteValue(i);
jsonWriter.WritePropertyName("columnName");
jsonWriter.WriteValue(dr.GetName(i));
jsonWriter.WritePropertyName("fieldType");
jsonWriter.WriteValue(GetAssemblyQualifiedNoVersionName(dr.GetFieldType(i)));
jsonWriter.WriteEndObject();
} // Next i
jsonWriter.WriteEndArray();
} // End Sub WriteArray
} // End Sub MultipleLargeDataSets
public static void MultipleLargeDataSets(System.IO.Stream strm, string strSQL)
{
Newtonsoft.Json.JsonSerializer serializer = new Newtonsoft.Json.JsonSerializer();
using (System.IO.StreamWriter output = new System.IO.StreamWriter(strm))
{
using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(output))
{
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("Tables");
jsonWriter.WriteStartArray();
using (System.Data.Common.DbDataReader dr = SQL.ExecuteReader(strSQL
, System.Data.CommandBehavior.CloseConnection
| System.Data.CommandBehavior.SequentialAccess
))
{
do
{
jsonWriter.WriteStartObject(); // tbl = new Table();
jsonWriter.WritePropertyName("Columns");
jsonWriter.WriteStartArray();
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("ColumnName");
jsonWriter.WriteValue(dr.GetName(i));
jsonWriter.WritePropertyName("FieldType");
jsonWriter.WriteValue(dr.GetFieldType(i).AssemblyQualifiedName);
jsonWriter.WriteEndObject();
} // Next i
jsonWriter.WriteEndArray();
jsonWriter.WritePropertyName("Rows");
jsonWriter.WriteStartArray();
if (dr.HasRows)
{
while (dr.Read())
{
object[] thisRow = new object[dr.FieldCount];
jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteValue(dr.GetValue(i));
} // Next i
jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow);
} // Whend
} // End if (dr.HasRows)
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl);
} while (dr.NextResult());
} // End Using dr
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject();
jsonWriter.Flush();
output.Flush();
output.BaseStream.Flush();
output.Close();
// context.Response.Output.Flush();
// context.Reponse.OutputStream.Flush();
// context.Response.Flush();
} // End Using jsonWriter
} // End using output
} // End Sub MultipleLargeDataSets
public static void LinqTest()
{
System.Collections.Generic.List <string> ls = new System.Collections.Generic.List <string>();
ls.Add("foo");
ls.Add("bar");
ls.Add("foobar");
int oobj = 123;
Person someOne = new Person()
{
Name = "foo", Email = "*****@*****.**", SnailMail = "Snail"
};
// object inexistant = GetProperty(someOne, "Inexistant");
object myName = GetProperty(someOne, "Name");
string myNameString = GetProperty <Person, string>(someOne, "Name");
int?nullMe = GetProperty <Person, int?>(someOne, "NullableNumber");
object nullNumObj = GetProperty(someOne, "NullableNumber");
int? nullNum = GetProperty <Person, int?>(someOne, "NullableNumber");
System.Console.WriteLine(nullNum);
SetProperty(someOne, "NullableNumber", null);
System.Console.WriteLine(someOne);
SetProperty(someOne, "NullableNumber", -123);
System.Console.WriteLine(someOne);
SetProperty(someOne, "NullableNumber", "-123");
System.Console.WriteLine(someOne);
SetProperty(someOne, "NullableNumber", System.DBNull.Value);
System.Console.WriteLine(someOne);
// object obj = System.DBNull.Value;
// SetProperty(someOne, "NullableNumber", obj);
System.Console.WriteLine(myName);
System.Console.WriteLine(myNameString);
// SetProperty(someOne, "Anumber", oobj);
// SetProperty(someOne, "SnailMail", "Turtle Mail");
// SetProperty(someOne, "Email", "SpamMail");
T_User ben = new T_User();
int cnt = GetProperty <System.Collections.Generic.List <string>, int>(ls, "cOuNt");
object objCount = GetProperty(ls, "cOuNt");
System.Console.WriteLine(cnt);
// b15186d6-adb1-4c8a-bbfa-830b24417e8b
string SQL = @"SELECT 'B15186D6-ADB1-4C8A-BBFA-830B24417E8B' AS BE_UID, '123' AS BE_ID, 'Carbon Unit' AS BE_Name, '*****@*****.**' AS EMail, 'omg' AS SnailMail, CAST(NULL AS integer) AS NullableNumber;";
// SQL = @"SELECT CAST(NULL AS uniqueidentifier) AS BE_UID"; // Test NULLing non-null type error message...
using (System.Data.Common.DbDataReader rdr = null) // CoinBaseSharp.SQL.ExecuteReader(SQL))
{
do
{
int fieldCount = rdr.FieldCount;
System.Type[] ts = new System.Type[fieldCount];
string[] fieldNames = new string[fieldCount];
System.Action <T_User, object>[] fieldSetters = new System.Action <T_User, object> [fieldCount];
for (int i = 0; i < fieldCount; ++i)
{
ts[i] = rdr.GetFieldType(i);
fieldNames[i] = rdr.GetName(i);
fieldSetters[i] = GetSetter <T_User>(fieldNames[i]);
} // Next i
if (rdr.HasRows)
{
while (rdr.Read())
{
for (int i = 0; i < fieldCount; ++i)
{
object objValue = rdr.GetValue(i);
// if (object.ReferenceEquals(objValue, System.DBNull.Value)) objValue = null;
System.Console.WriteLine(ts[i]);
//int abc = 123;
// SetProperty(ben, fieldNames[i], abc);
// SetProperty(ben, fieldNames[i], objValue);
fieldSetters[i](ben, objValue);
System.Console.WriteLine(objValue);
} // Next i
} // Whend
} // End if (rdr.HasRows)
} while (rdr.NextResult());
} // End Using rdr
System.Console.WriteLine(ben.BE_UID);
} // End Sub LinqTest
public static void Test()
{
System.Data.SqlClient.SqlConnectionStringBuilder csb = new System.Data.SqlClient.SqlConnectionStringBuilder();
csb.DataSource = System.Environment.MachineName;
csb.DataSource = @"10.1.1.8"; // Must be IP, NETBIOS doesn't resolve on Linux
// SQL Server Configuration Manager
// SQL Server 2016 C:\Windows\SysWOW64\SQLServerManager13.msc
// SQL Server 2014 C:\Windows\SysWOW64\SQLServerManager12.msc
// SQL Server 2012 C:\Windows\SysWOW64\SQLServerManager11.msc
// SQL Server 2008 C:\Windows\SysWOW64\SQLServerManager10.msc
// in Network-Configuration: Activate TCP/IP & Restart Service
// Open firewall port for SQL-Server
// - Windows 10:
// netsh advfirewall firewall add rule name="SQL Server" dir=in action=allow protocol=TCP localport=1433
// - Windows < 10:
// netsh firewall set portopening TCP 1433 "SQLServer"
// https://support.microsoft.com/en-us/kb/968872
// https://blog.brankovucinec.com/2015/12/04/scripts-to-open-windows-firewall-ports-for-sql-server/
csb.IntegratedSecurity = false;
if (!csb.IntegratedSecurity)
{
csb.UserID = "LoggyWebServices";
csb.Password = "******";
}
csb.InitialCatalog = "TestDB";
Loggy.cDAL DAL = Loggy.cDAL.CreateInstance();
DAL.ConnectionString = csb.ConnectionString;
using (System.Data.Common.DbConnection dbConnection = DAL.GetConnection())
{
object objUser1 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_User;", dbConnection);
object objUser2 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_ID;", dbConnection);
DAL.ExecuteNonQuery("UPDATE T_Benutzer SET BE_Hash = BE_Hash;", dbConnection);
DAL.ExecuteNonQuery("UPDATE T_Benutzer SET BE_Hash = BE_Hash;", dbConnection);
#if WITH_CONNECTION
//using (System.Data.Common.DbDataReader reader = DAL.ExecuteReader("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;", dbConnection))
using (System.Data.Common.DbDataReader reader = DAL.ExecuteReader_Buggy("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;"))
#else
DAL.ExecuteReader("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;", delegate(System.Data.Common.DbDataReader reader)
#endif
{
do
{
for (int i = 0; i < reader.FieldCount; ++i)
{
string fieldName = reader.GetName(i);
System.Type fieldType = reader.GetFieldType(i);
System.Console.WriteLine("{0}:\t{1}\t{2}", i, fieldName, fieldType.ToString());
} // Next i
if (reader.HasRows)
{
int rowCount = 1;
while (reader.Read())
{
System.Console.WriteLine(@"Row {0}", rowCount);
for (int i = 0; i < reader.FieldCount; ++i)
{
string fieldName = reader.GetName(i);
object fieldValue = reader.GetValue(i);
System.Console.WriteLine(@" - {0}: {1}", fieldName, System.Convert.ToString(fieldValue));
} // Next i
++rowCount;
} // Whend
--rowCount;
} // End if (reader.HasRows)
} while (reader.NextResult());
} // End Using reader
#if !WITH_CONNECTION
);
#endif
object objUser3 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_Hash;", dbConnection);
object objUser4 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_Passwort;", dbConnection);
} // End Using dbConnection
} // End Sub Test
/// <summary>
/// Generate CSV formatted output for the given reader.
/// </summary>
public string Generate(System.Data.Common.DbDataReader reader)
{
var builder = new StringBuilder();
var schema = reader.GetSchemaTable();
var colcount = reader.FieldCount;
var nullable = new bool[colcount];
var datatype = new Type[colcount];
var typename = new string[colcount];
for (int c = 0; c < colcount; c++)
{
nullable[c] = true;
datatype[c] = reader.GetFieldType(c);
typename[c] = reader.GetDataTypeName(c);
if (c == 0)
{
if (this.Settings.AddLineNumbers)
{
if (this.Settings.QuotedStrings)
{
builder.Append(this.Settings.StringQuote);
}
builder.Append("Line");
if (this.Settings.QuotedStrings)
{
builder.Append(this.Settings.StringQuote);
}
builder.Append(this.Settings.FieldSeparator);
}
}
else
{
builder.Append(this.Settings.FieldSeparator);
}
if (this.Settings.QuotedStrings)
{
builder.Append(this.Settings.StringQuote);
}
builder.Append(reader.GetName(c));
if (this.Settings.QuotedStrings)
{
builder.Append(this.Settings.StringQuote);
}
}
builder.Append(this.Settings.LineSeparator);
var lineNumber = 0;
while (reader.Read())
{
lineNumber++;
for (int c = 0; c < colcount; c++)
{
if (c == 0)
{
if (this.Settings.AddLineNumbers)
{
builder.Append(lineNumber);
builder.Append(this.Settings.FieldSeparator);
}
}
else
{
builder.Append(this.Settings.FieldSeparator);
}
if (nullable[c] && reader.IsDBNull(c))
{
}
else
{
if (datatype[c] == typeof(String))
{
if (this.Settings.QuotedStrings)
{
builder.Append(this.Settings.StringQuote);
}
builder.Append(ToCsvableString(reader.GetString(c)));
if (this.Settings.QuotedStrings)
{
builder.Append(this.Settings.StringQuote);
}
}
else if (datatype[c] == typeof(DateTime))
{
builder.Append(reader.GetDateTime(c).ToString(this.Settings.DateTimeFormat, this.Settings.FormatProvider));
}
else if (datatype[c] == typeof(Boolean))
{
builder.Append(reader.GetBoolean(c) ? this.Settings.BooleanTrue : this.Settings.BooleanFalse);
}
else
{
builder.AppendFormat(this.Settings.FormatProvider, "{0}", reader.GetValue(c));
}
}
}
builder.Append(this.Settings.LineSeparator);
}
return(builder.ToString());
}
private void DumpTable(db mydb, string database, string table, string outdir)
{
if (excludeTables.Any(c => useRegexpTables ? Regex.IsMatch(table, c) : c == table))
{
return;
}
if (!Directory.Exists(outdir))
{
Directory.CreateDirectory(outdir);
}
string filename = Path.Combine(outdir, table + ".txt");
if (!overwrite)
{
if (File.Exists(filename))
{
return;
}
}
string tablename = db.GetTableName(dbprovider, database, table);
// Check number of rows
string sqlCount = _sql = $"select count(*) from {tablename}";
int rowcount;
object o = mydb.ExecuteScalarSQL(sqlCount);
if (o.GetType() == typeof(long))
{
rowcount = (int)(long)o;
}
else
{
rowcount = (int)o;
}
if (!exportempty && rowcount == 0)
{
return;
}
string sql = GetTableQuery(mydb, tablename, rowcount);
using (StreamWriter sw = new StreamWriter(filename))
{
using (System.Data.Common.DbDataReader reader = mydb.ExecuteReaderSQL(sql))
{
List <int> columns = new List <int>();
for (int i = 0; i < reader.FieldCount; i++)
{
string colname = reader.GetName(i);
if (excludeColumns.Any(c => useRegexpColumns ? Regex.IsMatch(colname, c) : c == colname))
{
continue;
}
if (reader.GetFieldType(i) == typeof(byte[]) && !binaryhex && !binaryfile)
{
continue;
}
columns.Add(i);
}
if (sortColumns)
{
columns = columns
.OrderBy(c => reader.GetName(c))
.Select(c => c)
.ToList();
}
// Write column names
if (header)
{
bool isFirstCol = true;
for (int i = 0; i < columns.Count; i++)
{
if (reader.GetFieldType(i) == typeof(byte[]) && !binaryhex)
{
continue;
}
if (!isFirstCol)
{
sw.Write(separator);
}
isFirstCol = false;
sw.Write(reader.GetName(columns[i]));
}
sw.WriteLine();
}
c_c += columns.Count;
if (maxrows != -1 && rowcount > maxrows)
{
sw.WriteLine($"{rowcount} rows.");
}
else
{
// Write data
int rownum = 0;
while (reader.Read())
{
bool isFirstCol = true;
for (int i = 0; i < columns.Count; i++)
{
if (reader.GetFieldType(i) != typeof(byte[]))
{
if (!isFirstCol)
{
sw.Write(separator);
}
isFirstCol = false;
if (escapecharacters)
{
sw.Write(FixValue(reader.GetValue(columns[i])));
}
else
{
sw.Write(reader.GetValue(columns[i]));
}
}
else
{
if (binaryhex)
{
if (!isFirstCol)
{
sw.Write(separator);
}
isFirstCol = false;
if (!reader.IsDBNull(i))
{
int length = (int)reader.GetBytes(i, 0, null, 0, 0);
byte[] buffer = new byte[length];
reader.GetBytes(i, 0, buffer, 0, length);
sw.Write(FixBinaryValue(buffer));
}
}
if (binaryfile)
{
if (!reader.IsDBNull(i))
{
string filename_data = Path.Combine(outdir, $"{table}_{reader.GetName(i)}_{rownum}.{extension}");
int length = (int)reader.GetBytes(i, 0, null, 0, 0);
byte[] buffer = new byte[length];
reader.GetBytes(i, 0, buffer, 0, length);
WriteBinaryfile(filename_data, buffer);
}
}
}
}
sw.WriteLine();
rownum++;
}
}
reader.Close();
}
}
}
} // End Sub SerializeLargeDataset
public void SerializeLargeTable(HttpContext context)
{
Newtonsoft.Json.JsonSerializer ser = new Newtonsoft.Json.JsonSerializer();
using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(context.Response.Output))
{
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
jsonWriter.WriteStartObject();
using (System.Data.Common.DbConnection con = SQL.CreateConnection())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT TOP 10000 * FROM T_LOG_SAP_Interface";
using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess
| System.Data.CommandBehavior.CloseConnection
))
{
jsonWriter.WritePropertyName("Columns");
jsonWriter.WriteStartArray();
for (int i = 0; i < dr.FieldCount; i++)
{
string colName = dr.GetName(i);
System.Type t = dr.GetFieldType(i);
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("ColumnName");
jsonWriter.WriteValue(colName);
jsonWriter.WritePropertyName("DataType");
jsonWriter.WriteValue(GetAssemblyQualifiedNoVersionName(t));
// jsonWriter.WritePropertyName("DateTimeMode");
// jsonWriter.WriteValue(column.DateTimeMode.ToString());
jsonWriter.WriteEndObject();
}
jsonWriter.WriteEndArray();
jsonWriter.WritePropertyName("Rows");
jsonWriter.WriteStartArray();
if (dr.HasRows)
{
int fieldCount = dr.FieldCount;
while (dr.Read())
{
jsonWriter.WriteStartArray();
for (int i = 0; i < fieldCount; ++i)
{
object obj = dr.GetValue(i);
jsonWriter.WriteValue(obj);
} // Next i
jsonWriter.WriteEndArray();
jsonWriter.Flush();
context.Response.Output.Flush();
context.Response.Flush();
} // Whend while (dr.Read())
} // End if (dr.HasRows)
dr.Close();
jsonWriter.WriteEndArray();
} // End using dr
} // End using cmd
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End using con
jsonWriter.WriteEndObject();
jsonWriter.Flush();
context.Response.Output.Flush();
context.Response.OutputStream.Flush();
context.Response.Flush();
} // End Using jsonWriter
} // End Sub SerializeLargeTable
public void SerializeLargeDataset(HttpContext context)
{
string strSQL = @"
SELECT TOP 10 * FROM T_Benutzer;
SELECT TOP 10 * FROM T_Benutzergruppen;
-- SELECT * FROM T_Benutzer LIMIT 10;
-- SELECT * FROM T_Benutzergruppen LIMIT 10;
-- SELECT * FROM T_Benutzer OFFSET 0 FETCH NEXT 10 ROWS ONLY;
-- SELECT * FROM T_Benutzergruppen OFFSET 0 FETCH NEXT 10 ROWS ONLY;
";
Newtonsoft.Json.JsonSerializer ser = new Newtonsoft.Json.JsonSerializer();
using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(context.Response.Output))
{
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("Tables");
jsonWriter.WriteStartArray();
using (System.Data.Common.DbConnection con = SQL.CreateConnection())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = strSQL;
using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess
| System.Data.CommandBehavior.CloseConnection
))
{
do
{
jsonWriter.WriteStartObject(); // tbl = new Table();
jsonWriter.WritePropertyName("Columns");
jsonWriter.WriteStartArray();
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("ColumnName");
jsonWriter.WriteValue(dr.GetName(i));
jsonWriter.WritePropertyName("FieldType");
jsonWriter.WriteValue(GetAssemblyQualifiedNoVersionName(dr.GetFieldType(i)));
jsonWriter.WriteEndObject();
} // Next i
jsonWriter.WriteEndArray();
jsonWriter.WritePropertyName("Rows");
jsonWriter.WriteStartArray();
if (dr.HasRows)
{
while (dr.Read())
{
object[] thisRow = new object[dr.FieldCount];
jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteValue(dr.GetValue(i));
} // Next i
jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow);
} // Whend
} // End if (dr.HasRows)
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl);
} while (dr.NextResult());
} // End using dr
} // End using cmd
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End using con
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject();
jsonWriter.Flush();
} // End Using jsonWriter
context.Response.Output.Flush();
context.Response.OutputStream.Flush();
context.Response.Flush();
} // End Sub SerializeLargeDataset
public override void ExecuteResult(System.Web.Mvc.ControllerContext context)
{
context.HttpContext.Response.ContentType = "application/json";
context.HttpContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(context.HttpContext.Response.Output))
{
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("Tables");
jsonWriter.WriteStartArray();
using (System.Data.Common.DbConnection con = SQL.CreateConnection())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.Common.DbCommand cmd = this.GetCommand(con))
{
using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess
| System.Data.CommandBehavior.CloseConnection
))
{
do
{
jsonWriter.WriteStartObject(); // tbl = new Table();
jsonWriter.WritePropertyName("Columns");
jsonWriter.WriteStartArray();
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("ColumnName");
jsonWriter.WriteValue(dr.GetName(i));
jsonWriter.WritePropertyName("FieldType");
jsonWriter.WriteValue(SQL.GetAssemblyQualifiedNoVersionName(dr.GetFieldType(i)));
jsonWriter.WriteEndObject();
} // Next i
jsonWriter.WriteEndArray();
jsonWriter.WritePropertyName("Rows");
jsonWriter.WriteStartArray();
if (dr.HasRows)
{
while (dr.Read())
{
object[] thisRow = new object[dr.FieldCount];
jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteValue(dr.GetValue(i));
} // Next i
jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow);
} // Whend
} // End if (dr.HasRows)
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl);
} while (dr.NextResult());
} // End using dr
} // End using cmd
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End using con
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject();
jsonWriter.Flush();
} // End Using jsonWriter
context.HttpContext.Response.Output.Flush();
context.HttpContext.Response.OutputStream.Flush();
context.HttpContext.Response.Flush();
} // End Sub SerializeLargeDataset
public static void Test()
{
System.Data.SqlClient.SqlConnectionStringBuilder csb = new System.Data.SqlClient.SqlConnectionStringBuilder();
csb.DataSource = System.Environment.MachineName;
csb.IntegratedSecurity = true;
if (!csb.IntegratedSecurity)
{
csb.UserID = "DAL_Test";
csb.Password = "******";
} // End if (!csb.IntegratedSecurity)
csb.InitialCatalog = "COR_Basic_Demo_V4";
cDAL DAL = cDAL.CreateInstance();
DAL.ConnectionString = csb.ConnectionString;
using (System.Data.Common.DbConnection dbConnection = DAL.GetConnection())
{
object objUser1 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_User;", dbConnection);
object objUser2 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_ID;", dbConnection);
DAL.ExecuteNonQuery("UPDATE T_Benutzer SET BE_Hash = BE_Hash;", dbConnection);
DAL.ExecuteNonQuery("UPDATE T_Benutzer SET BE_Hash = BE_Hash;", dbConnection);
#if WITH_CONNECTION
//using (System.Data.Common.DbDataReader reader = DAL.ExecuteReader("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;", dbConnection))
using (System.Data.Common.DbDataReader reader = DAL.ExecuteReader_Buggy("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;"))
#else
DAL.ExecuteReader("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;", delegate(System.Data.Common.DbDataReader reader)
#endif
{
do
{
for (int i = 0; i < reader.FieldCount; ++i)
{
string fieldName = reader.GetName(i);
System.Type fieldType = reader.GetFieldType(i);
System.Console.WriteLine("{0}:\t{1}\t{2}", i, fieldName, fieldType.ToString());
} // Next i
if (reader.HasRows)
{
int rowCount = 1;
while (reader.Read())
{
System.Console.WriteLine(@"Row {0}", rowCount);
for (int i = 0; i < reader.FieldCount; ++i)
{
string fieldName = reader.GetName(i);
object fieldValue = reader.GetValue(i);
System.Console.WriteLine(@" - {0}: {1}", fieldName, System.Convert.ToString(fieldValue));
} // Next i
++rowCount;
} // Whend
--rowCount;
} // End if (reader.HasRows)
} while (reader.NextResult());
} // End Using reader
#if !WITH_CONNECTION
);
#endif
object objUser3 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_Hash;", dbConnection);
object objUser4 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_Passwort;", dbConnection);
} // End Using dbConnection
} // End Sub Test
private static Build CreateBuilder(Type destinationType, IDataRecord dataRecord)
{
Build builder;
BuilderKey builderKey = new BuilderKey(destinationType, dataRecord);
if (_builderCache.TryGetValue(builderKey, out builder))
{
return(builder);
}
var method = new DynamicMethod("DynamicCreate", destinationType, new[] { typeof(IDataRecord) }, destinationType, true);
var generator = method.GetILGenerator();
var result = generator.DeclareLocal(destinationType);
generator.Emit(OpCodes.Newobj, destinationType.GetConstructor(Type.EmptyTypes));
generator.Emit(OpCodes.Stloc, result);
for (var i = 0; i < dataRecord.FieldCount; i++)
{
var propertyInfo = destinationType.GetProperty(dataRecord.GetName(i), BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance);
var endIfLabel = generator.DefineLabel();
if (propertyInfo != null && propertyInfo.GetSetMethod(true) != null)
{
generator.Emit(OpCodes.Ldarg_0);
generator.Emit(OpCodes.Ldc_I4, i);
generator.Emit(OpCodes.Callvirt, isDBNullMethod);
generator.Emit(OpCodes.Brtrue, endIfLabel);
generator.Emit(OpCodes.Ldloc, result);
generator.Emit(OpCodes.Ldarg_0);
generator.Emit(OpCodes.Ldc_I4, i);
generator.Emit(OpCodes.Callvirt, getValueMethod);
if (propertyInfo.PropertyType.IsGenericType() &&
propertyInfo.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable <>))
)
{
var nullableType = propertyInfo.PropertyType.GetGenericTypeDefinition().GetGenericArguments()[0];
if (!nullableType.IsEnum())
{
generator.Emit(OpCodes.Unbox_Any, propertyInfo.PropertyType);
}
else
{
generator.Emit(OpCodes.Unbox_Any, nullableType);
generator.Emit(OpCodes.Newobj, propertyInfo.PropertyType);
}
}
else
{
generator.Emit(OpCodes.Unbox_Any, dataRecord.GetFieldType(i));
}
generator.Emit(OpCodes.Callvirt, propertyInfo.GetSetMethod(true));
generator.MarkLabel(endIfLabel);
}
}
generator.Emit(OpCodes.Ldloc, result);
generator.Emit(OpCodes.Ret);
builder = (Build)method.CreateDelegate(typeof(Build));
_builderCache[builderKey] = builder;
return(builder);
}