static public void case_GetDataTypeName()
{
// Open Connection
string strConn = connString;
OdbcConnection connCubrid = conn;
// Create a test table with 4 columns in various data type
string testTable = "table_data_type";
string strDropTable = string.Format("DROP TABLE {0}", testTable);
string strCreateTable = string.Format(@"CREATE TABLE {0}(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,col_1 INT, col_2 VARCHAR, col_3 CHAR,col_4 bit);", testTable);
string strSqlInsert = string.Format("INSERT INTO {0}(col_1, col_2, col_3) VALUE(123, 'varchar contents', 'a')", testTable);
// Execute multiple queries
ExecuteMultiQueries(connCubrid, new string[] { strDropTable, strCreateTable });
string strSqlSelect = string.Format("SELECT * FROM {0} ORDER BY id DESC;", testTable);
OdbcDataReader odbcReader = CreateReader(connCubrid, strSqlSelect);
// Check for GetDataTypeName returned values
// Assert.AreEqual("INT", odbcReader.GetDataTypeName(0));
// Assert.AreEqual("INT", odbcReader.GetDataTypeName(1));
// Assert.AreEqual("VARCHAR", odbcReader.GetDataTypeName(2));
// Assert.AreEqual("CHAR", odbcReader.GetDataTypeName(3));
Console.WriteLine(odbcReader.GetDataTypeName(0));
Console.WriteLine(odbcReader.GetDataTypeName(1));
Console.WriteLine(odbcReader.GetDataTypeName(2));
Console.WriteLine(odbcReader.GetDataTypeName(3));
}
private static void PreencheGrid(OdbcDataReader reader, DataGridView dgv)
{
dgv.Rows.Clear();
dgv.Columns.Clear();
bool fazer = true;
int colBool = -1;
while (reader.Read())
{
#region - Nomes Colunas -
if (fazer)
{
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.GetDataTypeName(i).Contains("bool"))
{
DataGridViewCheckBoxColumn colch = new DataGridViewCheckBoxColumn();
colch.Name = reader.GetName(i);
colch.HeaderText = reader.GetName(i);
colch.ReadOnly = true;
dgv.Columns.Add(colch);
colBool = i;
}
else
{
dgv.Columns.Add(reader.GetName(i), reader.GetName(i));
dgv.Columns[i].ReadOnly = true;
}
}
fazer = false;
}
#endregion
#region - Valores Colunas -
object[] linha = new object[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
/*reader.GetFieldType(i).ToString()*/
//MessageBox.Show("[" + reader.GetName(i) + "]: " + reader[i].ToString() + " (" + reader.GetDataTypeName(i) + ")");
linha[i] = (colBool == i) ? ((reader[i].ToString().Equals("0")) ? false : true) : reader[i];
}
dgv.Rows.Add(linha);
#endregion
}
dgv.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
}
public void data_backup(string tablename, OdbcDataReader dr)
{
if (data_type == null)
{
data_type = new Hashtable();
}
if (data_columnsize == null)
{
data_columnsize = new Hashtable();
}
for (int i = 0; i < dr.FieldCount; i++)
{
string key = tablename + "." + dr.GetName(i);
if (!data_type.ContainsKey(key))
{
data_type.Add(key, dr.GetDataTypeName(i));
}
if (!data_columnsize.ContainsKey(key))
{
DataRow[] rows = dr.GetSchemaTable().Select(string.Format("ColumnName='{0}'", dr.GetName(i)));
data_columnsize.Add(key, rows[0]["ColumnSize"].ToString());
}
if (data_type[key].ToString() == "date")
{
if (dr.HasRows)
{
data_value.Add(key, string.Format("{0:yyyy/MM/dd}", dr[i]));
}
else
{
data_value.Add(key, "");
}
}
else
{
if (dr.HasRows)
{
data_value.Add(key, dr[i].ToString());
}
else
{
data_value.Add(key, "");
}
}
}
}
public List <OdbcData> Get(string queryString)
{
List <OdbcData> _data = new List <OdbcData>();
using (OdbcConnection _conn = new OdbcConnection(connectionString))
{
try
{
_conn.Open();
OdbcTransaction _myTrans = _conn.BeginTransaction();
try
{
OdbcCommand _command = new OdbcCommand(queryString, _conn, _myTrans);
OdbcDataReader _OdbcReader = _command.ExecuteReader();
int i = 0;
while (_OdbcReader.Read())
{
_data.Add(new OdbcData {
DataTypeName = _OdbcReader.GetDataTypeName(i), DataTypeValue = _OdbcReader.GetValue(i).ToString()
});
i++;
}
_myTrans.Commit();
}
catch (Exception ex)
{
Console.WriteLine("Error! " + ex.Message);
_myTrans.Rollback();
_data = null;
}
finally
{
_conn.Close();
}
}
catch (Exception ex)
{
Console.WriteLine("Can not open connection ! " + ex.Message);
}
}
return(_data);
}
static int Main(string[] args)
{
int
Result = -1;
StreamWriter
fstr_out = null;
string
tmpString = "log.log",
TableName;
#if TEST_DB_BY_ODBC
OdbcConnection
odbc_conn = null;
OdbcCommand
odbc_cmd = null;
OdbcDataReader
odbc_rdr = null;
OdbcDataAdapter
odbc_da = null;
#endif
OleDbConnection
conn = null;
OleDbCommand
cmd = null;
OleDbDataReader
rdr = null;
OleDbDataAdapter
da = null;
DataTable
tmpDataTable;
int
tmpInt;
object[]
tmpObjects;
FileStream
fs;
byte[]
Blob;
try
{
try
{
fstr_out = new StreamWriter(tmpString, false, System.Text.Encoding.GetEncoding(1251));
fstr_out.AutoFlush = true;
string
PathToDb = "E:\\Soft.src\\CBuilder\\Tests\\Paradox\\Test.#1\\db",
CommonDbTableName = "Common",
CommonDbTableSQLCreate = @"
create table " + CommonDbTableName + @"(
FInt integer,
FChar char(254)
)";
#if TEST_DB_BY_ODBC
if (!PathToDb.EndsWith(Path.DirectorySeparatorChar.ToString()))
{
PathToDb += Path.DirectorySeparatorChar;
}
tmpString = "Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Paradox 5.X;DefaultDir=" + PathToDb + ";Dbq=" + PathToDb + ";CollatingSequence=ASCII";
odbc_conn = new OdbcConnection(tmpString);
odbc_conn.Open();
fstr_out.WriteLine("ConnectionString: " + odbc_conn.ConnectionString);
fstr_out.WriteLine("ConnectionTimeout: " + odbc_conn.ConnectionTimeout.ToString());
fstr_out.WriteLine("Database: " + odbc_conn.Database);
fstr_out.WriteLine("DataSource: " + odbc_conn.DataSource);
fstr_out.WriteLine("Driver: " + odbc_conn.Driver);
fstr_out.WriteLine("ServerVersion: " + odbc_conn.ServerVersion);
fstr_out.WriteLine("State: " + odbc_conn.State.ToString());
fstr_out.WriteLine();
tmpString = PathToDb + CommonDbTableName + ".db";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
odbc_cmd = odbc_conn.CreateCommand();
odbc_cmd.CommandType = CommandType.Text;
odbc_cmd.CommandText = CommonDbTableSQLCreate;
odbc_cmd.ExecuteNonQuery();
odbc_cmd.CommandText = "insert into " + CommonDbTableName + " values (1,'FChar (‘„ар)')";
odbc_cmd.ExecuteNonQuery();
#if TEST_BLOB
if (odbc_cmd == null)
{
odbc_cmd = odbc_conn.CreateCommand();
}
odbc_cmd.CommandType = CommandType.Text;
odbc_cmd.CommandText = "select * from TestTypes";
odbc_cmd.Parameters.Clear();
odbc_rdr = odbc_cmd.ExecuteReader();
do
{
if (odbc_rdr.HasRows)
{
for (int i = 0; i < odbc_rdr.FieldCount; ++i)
{
fstr_out.WriteLine(odbc_rdr.GetName(i) + " GetDataTypeName(): \"" + odbc_rdr.GetDataTypeName(i) + "\" GetFieldType(): \"" + odbc_rdr.GetFieldType(i) + "\"");
}
tmpInt = odbc_rdr.GetOrdinal("FGraphic");
while (odbc_rdr.Read())
{
tmpString = "FromBlob.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = (byte[])odbc_rdr["FGraphic"];
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
tmpString = "FromBlob_1.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = new byte[odbc_rdr.GetBytes(tmpInt, 0, null, 0, int.MaxValue)];
rdr.GetBytes(tmpInt, 0, Blob, 0, Blob.Length);
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
}
}
}while(rdr.NextResult());
odbc_rdr.Close();
#endif
odbc_conn.Close();
#endif
if (PathToDb.EndsWith(Path.DirectorySeparatorChar.ToString()))
{
PathToDb = PathToDb.Remove(PathToDb.Length - 1, 1);
}
tmpString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathToDb + ";Extended Properties=Paradox 5.x";
conn = new OleDbConnection(tmpString);
conn.Open();
fstr_out.WriteLine("ConnectionString: " + conn.ConnectionString);
fstr_out.WriteLine("ConnectionTimeout: " + conn.ConnectionTimeout.ToString());
fstr_out.WriteLine("Database: " + conn.Database);
fstr_out.WriteLine("DataSource: " + conn.DataSource);
fstr_out.WriteLine("Provider: " + conn.Provider);
fstr_out.WriteLine("ServerVersion: " + conn.ServerVersion);
fstr_out.WriteLine("State: " + conn.State.ToString());
fstr_out.WriteLine();
tmpString = PathToDb + Path.DirectorySeparatorChar + CommonDbTableName + ".db";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = CommonDbTableSQLCreate;
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into " + CommonDbTableName + " values (1,'FChar (‘„ар)')";
cmd.ExecuteNonQuery();
#if TEST_BLOB
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.CommandType = CommandType.Text;
TableName = "TestTypes";
tmpObjects = new object[] { null, null, TableName, null };
fstr_out.WriteLine("OleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns)");
tmpDataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, tmpObjects);
fstr_out.WriteLine("Columns in " + TableName + " table:");
foreach (DataRow row in tmpDataTable.Rows)
{
fstr_out.WriteLine("\t" + row["TABLE_CATALOG"] + " " + row["TABLE_NAME"] + " " + row["COLUMN_NAME"].ToString() + " " + row["DATA_TYPE"] + " " + row["TABLE_SCHEMA"]);
}
fstr_out.WriteLine();
#if TEST_BLOB_SAVE
cmd.CommandText = "update TestTypes set FGraphic = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add("FGraphic", OleDbType.LongVarBinary);
fs = new FileStream("welcome.bmp", FileMode.Open, FileAccess.Read);
Blob = new byte[fs.Length];
fs.Read(Blob, 0, Blob.Length);
cmd.Parameters["FGraphic"].Value = Blob;
tmpInt = cmd.ExecuteNonQuery();
#endif
cmd.CommandText = "select * from TestTypes";
cmd.Parameters.Clear();
rdr = cmd.ExecuteReader();
do
{
if (rdr.HasRows)
{
for (int i = 0; i < rdr.FieldCount; ++i)
{
fstr_out.WriteLine(rdr.GetName(i) + " GetDataTypeName(): \"" + rdr.GetDataTypeName(i) + "\" GetFieldType(): \"" + rdr.GetFieldType(i) + "\"");
}
tmpInt = rdr.GetOrdinal("FGraphic");
while (rdr.Read())
{
tmpString = "FromBlob.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = (byte[])rdr["FGraphic"];
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
tmpString = "FromBlob_1.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = new byte[rdr.GetBytes(tmpInt, 0, null, 0, int.MaxValue)];
rdr.GetBytes(tmpInt, 0, Blob, 0, Blob.Length);
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
}
}
}while(rdr.NextResult());
rdr.Close();
#endif
Result = 0;
}
catch (Exception eException)
{
Console.WriteLine(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace);
}
}
finally
{
#if TEST_DB_BY_ODBC
if (odbc_rdr != null && !odbc_rdr.IsClosed)
{
odbc_rdr.Close();
}
if (odbc_conn != null && odbc_conn.State == System.Data.ConnectionState.Open)
{
odbc_conn.Close();
}
#endif
if (rdr != null && !rdr.IsClosed)
{
rdr.Close();
}
if (conn != null && conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
if (fstr_out != null)
{
fstr_out.Close();
}
}
return(Result);
}
public static int Main(string[] args)
{
int
Result = -1,
_ID_;
StreamWriter
fstr_out = null,
fstr_out_866 = null;
string
currentDirectory = System.Reflection.Assembly.GetExecutingAssembly().Location,
tmpString = "log.log",
DbfFileName,
asciiString,
cp866String;
if (currentDirectory.IndexOf("bin") != -1)
{
currentDirectory = currentDirectory.Substring(0, currentDirectory.LastIndexOf("bin", currentDirectory.Length - 1));
}
byte[]
asciiBytes,
cp866Bytes;
char[]
cp866Chars;
#if TEST_DBF_BY_ODBC
OdbcConnection
odbc_conn = null;
OdbcCommand
odbc_cmd = null;
OdbcDataReader
odbc_rdr = null;
OdbcDataAdapter
odbc_da = null;
#endif
OleDbConnection
conn = null;
OleDbCommand
cmd = null;
OleDbDataReader
rdr = null;
OleDbDataAdapter
da = null;
DataTable
tmpDataTable = null;
object
tmpObject;
try
{
try
{
fstr_out = new StreamWriter(tmpString, false, System.Text.Encoding.GetEncoding(1251));
fstr_out.AutoFlush = true;
fstr_out_866 = new StreamWriter(tmpString + "_866", false, System.Text.Encoding.GetEncoding(866));
fstr_out_866.AutoFlush = true;
#if TEST_DBF_BY_ODBC || TEST_DBF || TEST_VFP
string
PathToDbf = Path.Combine(currentDirectory, "dbf"),
CommonDbfTableName = "Common",
CommonDbfTableSQLCreate = @"
create table " + CommonDbfTableName + @"(
FInt integer,
FChar char(254),
FDate1 date,
FDate2 date
)";
#endif
#if TEST_DBF_BY_ODBC
if (PathToDbf.EndsWith(Path.DirectorySeparatorChar.ToString()))
{
PathToDbf = PathToDbf.Remove(PathToDbf.Length - 1, 1);
}
tmpString = "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" + PathToDbf + ";";
odbc_conn = new OdbcConnection(tmpString);
odbc_conn.Open();
fstr_out.WriteLine("ConnectionString: " + odbc_conn.ConnectionString);
fstr_out.WriteLine("ConnectionTimeout: " + odbc_conn.ConnectionTimeout.ToString());
fstr_out.WriteLine("Database: " + odbc_conn.Database);
fstr_out.WriteLine("DataSource: " + odbc_conn.DataSource);
fstr_out.WriteLine("Driver: " + odbc_conn.Driver);
fstr_out.WriteLine("ServerVersion: " + odbc_conn.ServerVersion);
fstr_out.WriteLine("State: " + odbc_conn.State.ToString());
fstr_out.WriteLine();
tmpString = PathToDbf + Path.DirectorySeparatorChar + CommonDbfTableName + ".dbf";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
odbc_cmd = odbc_conn.CreateCommand();
odbc_cmd.CommandType = CommandType.Text;
odbc_cmd.CommandText = CommonDbfTableSQLCreate;
odbc_cmd.ExecuteNonQuery();
odbc_cmd.CommandText = "insert into " + CommonDbfTableName + " (FInt, FChar, FDate1, FDate2) values (1, 'Line# 1 Линия № 1 Лінія № 1', {d'2008-12-31'}, {d'2008-12-31'})";
odbc_cmd.ExecuteNonQuery();
odbc_cmd.CommandText = "insert into " + CommonDbfTableName + " (FInt, FChar) values (?, ?)";
odbc_cmd.Parameters.Clear();
odbc_cmd.Parameters.Add("FInt", OdbcType.Int).Value = 2;
odbc_cmd.Parameters.Add("FChar", OdbcType.VarChar).Value = "Line# 2 Линия № 2 Лінія № 2";
odbc_cmd.ExecuteNonQuery();
tmpString = "select * from " + CommonDbfTableName;
odbc_cmd = new OdbcCommand(tmpString, odbc_conn);
odbc_rdr = odbc_cmd.ExecuteReader();
while (odbc_rdr.Read())
{
for (int i = 0; i < odbc_rdr.FieldCount; ++i)
{
fstr_out.Write(odbc_rdr[odbc_rdr.GetName(i)]);
if (i < odbc_rdr.FieldCount)
{
fstr_out.Write("\t");
}
}
fstr_out.WriteLine();
}
odbc_rdr.Close();
odbc_da = new OdbcDataAdapter(tmpString, odbc_conn);
if (tmpDataTable == null)
{
tmpDataTable = new DataTable();
}
odbc_da.Fill(tmpDataTable);
ShowStru(tmpDataTable, fstr_out);
tmpDataTable.Reset();
odbc_cmd.CommandText = "select * from testtype_1";
fstr_out.WriteLine();
fstr_out.WriteLine("OdbcDataAdapter.FillSchema()");
odbc_da.SelectCommand = odbc_cmd;
odbc_da.FillSchema(tmpDataTable, SchemaType.Source);
foreach (DataColumn c in tmpDataTable.Columns)
{
fstr_out.WriteLine(c.ColumnName + ": \"" + c.DataType.ToString() + "\"");
}
tmpDataTable.Reset();
odbc_rdr = odbc_cmd.ExecuteReader(CommandBehavior.SchemaOnly);
fstr_out.WriteLine(Environment.NewLine + "OdbcDataReader.HasRows=" + odbc_rdr.HasRows.ToString());
for (int i = 0; i < odbc_rdr.FieldCount; ++i)
{
fstr_out.WriteLine(odbc_rdr.GetName(i) + " GetDataTypeName(): \"" + odbc_rdr.GetDataTypeName(i) + "\" GetFieldType(): \"" + odbc_rdr.GetFieldType(i) + "\"");
}
odbc_rdr.Close();
/*
* odbc_cmd.CommandText="insert into testtype_1 (FLogical) values (true)";
* odbc_cmd.ExecuteNonQuery();
*
* odbc_cmd.CommandText="update testtype_1 set FLogical=false";
* odbc_cmd.ExecuteNonQuery();
*/
try
{
tmpString = "select * from t"; // t is FoxPro (with cdx) table
odbc_cmd.CommandText = tmpString;
odbc_rdr = odbc_cmd.ExecuteReader();
odbc_rdr.Close();
}
catch (OdbcException eException)
{
tmpString = string.Empty;
for (int i = 0; i < eException.Errors.Count; ++i)
{
if (tmpString != string.Empty)
{
tmpString += Environment.NewLine;
}
tmpString += "Index #" + i + "\t" +
"Message: \"" + eException.Errors[i].Message + "\"\t" +
"Native: \"" + eException.Errors[i].NativeError.ToString() + "\"\t" +
"Source: \"" + eException.Errors[i].Source + "\"\t" +
"SQL: \"" + eException.Errors[i].SQLState + "\"";
}
fstr_out.WriteLine(Environment.NewLine + "OdbcException:" + Environment.NewLine + tmpString + Environment.NewLine);
}
odbc_conn.Close();
#endif
#if TEST_DBF
if (PathToDbf.EndsWith(Path.DirectorySeparatorChar.ToString()))
{
PathToDbf = PathToDbf.Remove(PathToDbf.Length - 1, 1);
}
tmpString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathToDbf + ";Extended Properties=dBASE IV;User ID=;Password="******"ConnectionString: " + conn.ConnectionString);
fstr_out.WriteLine("ConnectionTimeout: " + conn.ConnectionTimeout.ToString());
fstr_out.WriteLine("Database: " + conn.Database);
fstr_out.WriteLine("DataSource: " + conn.DataSource);
fstr_out.WriteLine("Provider: " + conn.Provider);
fstr_out.WriteLine("ServerVersion: " + conn.ServerVersion);
fstr_out.WriteLine("State: " + conn.State.ToString());
fstr_out.WriteLine();
tmpString = PathToDbf + Path.DirectorySeparatorChar + CommonDbfTableName + ".dbf";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = CommonDbfTableSQLCreate;
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into " + CommonDbfTableName + " (FInt, FChar, FDate1, FDate2) values (1, 'Line# 1 Линия № 1 Лінія № 1', #01/01/2008#, #01/01/2008#)";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into " + CommonDbfTableName + " (FInt, FChar) values (?, ?)";
cmd.Parameters.Clear();
cmd.Parameters.Add("FInt", OleDbType.Integer).Value = 2;
cmd.Parameters.Add("FChar", OleDbType.VarChar).Value = "Line# 2 Линия № 2 Лінія № 2";
cmd.ExecuteNonQuery();
tmpString = "select * from " + CommonDbfTableName;
cmd = new OleDbCommand(tmpString, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; ++i)
{
fstr_out.Write(rdr[rdr.GetName(i)]);
if (i < rdr.FieldCount)
{
fstr_out.Write("\t");
}
}
fstr_out.WriteLine();
}
rdr.Close();
tmpString = "select * from dbffile";
cmd = new OleDbCommand(tmpString, conn);
rdr = cmd.ExecuteReader();
fstr_out.WriteLine();
while (rdr.Read())
{
fstr_out.WriteLine(rdr.GetString(rdr.GetOrdinal("BANK")));
}
rdr.Close();
if (tmpDataTable == null)
{
tmpDataTable = new DataTable();
}
else
{
tmpDataTable.Reset();
}
if (da == null)
{
da = new OleDbDataAdapter(cmd);
}
da.Fill(tmpDataTable);
ShowStru(tmpDataTable, fstr_out);
tmpDataTable.Reset();
fstr_out.WriteLine();
cmd.CommandText = "select * from testtype_1";
fstr_out.WriteLine(cmd.CommandText);
fstr_out.WriteLine(new string('-', cmd.CommandText.Length));
fstr_out.WriteLine();
fstr_out.WriteLine("OleDbDataAdapter.FillSchema()");
da.SelectCommand = cmd;
da.FillSchema(tmpDataTable, SchemaType.Source);
foreach (DataColumn c in tmpDataTable.Columns)
{
fstr_out.WriteLine(c.ColumnName + ": \"" + c.DataType.ToString() + "\"");
}
tmpDataTable.Reset();
fstr_out.WriteLine();
fstr_out.WriteLine("OleDbCommand.ExecuteReader(CommandBehavior.SchemaOnly)");
rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
fstr_out.WriteLine("OleDbDataReader.HasRows=" + rdr.HasRows.ToString());
for (int i = 0; i < rdr.FieldCount; ++i)
{
fstr_out.WriteLine(rdr.GetName(i) + " " + rdr.GetDataTypeName(i) + " " + rdr.GetFieldType(i));
}
rdr.Close();
fstr_out.WriteLine();
cmd.CommandText = "select * from testtype_2";
fstr_out.WriteLine(cmd.CommandText);
fstr_out.WriteLine(new string('-', cmd.CommandText.Length));
fstr_out.WriteLine();
fstr_out.WriteLine("OleDbDataAdapter.FillSchema()");
da.SelectCommand = cmd;
da.FillSchema(tmpDataTable, SchemaType.Source);
foreach (DataColumn c in tmpDataTable.Columns)
{
fstr_out.WriteLine(c.ColumnName + ": \"" + c.DataType.ToString() + "\"");
}
tmpDataTable.Reset();
fstr_out.WriteLine();
fstr_out.WriteLine("OleDbCommand.ExecuteReader(CommandBehavior.SchemaOnly)");
rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
fstr_out.WriteLine("OleDbDataReader.HasRows=" + rdr.HasRows.ToString());
for (int i = 0; i < rdr.FieldCount; ++i)
{
fstr_out.WriteLine(rdr.GetName(i) + " " + rdr.GetDataTypeName(i) + " " + rdr.GetFieldType(i));
}
rdr.Close();
fstr_out.WriteLine();
try
{
tmpString = "select * from t"; // t is FoxPro (with cdx) table
cmd.CommandText = tmpString;
rdr = cmd.ExecuteReader();
rdr.Close();
}
catch (OleDbException eException)
{
tmpString = string.Empty;
for (int i = 0; i < eException.Errors.Count; ++i)
{
if (tmpString != string.Empty)
{
tmpString += Environment.NewLine;
}
tmpString += "Index #" + i + "\t" +
"Message: \"" + eException.Errors[i].Message + "\"\t" +
"Native: \"" + eException.Errors[i].NativeError.ToString() + "\"\t" +
"Source: \"" + eException.Errors[i].Source + "\"\t" +
"SQL: \"" + eException.Errors[i].SQLState + "\"";
}
fstr_out.WriteLine(Environment.NewLine + "OleDbException:" + Environment.NewLine + tmpString + Environment.NewLine);
}
cmd.CommandType = CommandType.Text;
DbfFileName = "test_ins";
cmd.CommandText = "delete from " + DbfFileName;
cmd.ExecuteNonQuery();
cmd.CommandText = "select max(ID) from " + DbfFileName;
tmpObject = cmd.ExecuteScalar();
_ID_ = !Convert.IsDBNull(tmpObject) ? Convert.ToInt32(tmpObject) + 1 : 1;
cmd.CommandText = "insert into " + DbfFileName + " (ID, FCHAR, DDATE, FNUMERIC) values (?, ?, ?, ?)";
cmd.Parameters.Clear();
cmd.Parameters.Add("ID", OleDbType.Numeric).Value = _ID_;
cmd.Parameters.Add("FCHAR", OleDbType.VarChar).Value = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
cmd.Parameters.Add("DDATE", OleDbType.Date).Value = DateTime.Now;
cmd.Parameters.Add("FNUMERIC", OleDbType.Numeric).Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "abcdefghijklmnopqrstuvwxyz";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "АБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "абвгдежзийклмнопрстуфхцчшщъыьэюя";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "ЁёҐґІіЇїЄє№";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "АБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "абвгдежзийклмнопрстуфхцчшщъыьэюя";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "ЁёҐґІіЇїЄє№";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
asciiBytes[2] = 0x0c3; // 0x0a5 (165) -> 0x0c3 (195) Ґ -> Г
asciiBytes[3] = 0x0e3; // 0x0b4 (180) -> 0x0e3 (227) ґ -> г
asciiBytes[4] = 0x0a1; // 0x0b2 (178) -> 0x0a1 (161) І -> Ў
asciiBytes[5] = 0x0a2; // 0x0b3 (179) -> 0x0a2 (162) і -> ў
asciiBytes[6] = 0x0b0; // 0x0af (175) -> 0x0b0 (176) Ї -> °
asciiBytes[7] = 0x0b7; // 0x0bf (191) -> 0x0b7 (183) ї -> ·
asciiBytes[8] = 0x0af; // 0x0aa (170) -> 0x0af (175) Є -> Ї
asciiBytes[9] = 0x0bf; // 0x0ba (186) -> 0x0bf (191) є -> ї
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866Chars[7] = (char)0x02219;
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = CP1251ToCP866("Line# 2 Линия № 2 Лінія № 2");
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
if (!PathToDbf.EndsWith(Path.DirectorySeparatorChar.ToString()))
{
PathToDbf += Path.DirectorySeparatorChar;
}
DbfFileName = "test_ADO.dbf";
if (File.Exists(PathToDbf + DbfFileName))
{
File.Delete(PathToDbf + DbfFileName);
}
DbfFileName = Path.GetFileNameWithoutExtension(DbfFileName);
cmd.CommandText = "create table " + DbfFileName + "(ID numeric(18,0), FCHAR varchar(254), DDATE date, FNUMERIC numeric(10,4))";
cmd.ExecuteNonQuery();
cmd.CommandText = "select max(ID) from " + DbfFileName;
tmpObject = cmd.ExecuteScalar();
_ID_ = !Convert.IsDBNull(tmpObject) ? Convert.ToInt32(tmpObject) + 1 : 1;
cmd.CommandText = "insert into " + DbfFileName + " (ID, FCHAR, DDATE, FNUMERIC) values (?, ?, ?, ?)";
cmd.Parameters.Clear();
cmd.Parameters.Add("ID", OleDbType.Numeric).Value = _ID_;
cmd.Parameters.Add("FCHAR", OleDbType.VarChar).Value = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
cmd.Parameters.Add("DDATE", OleDbType.Date).Value = DateTime.Now;
cmd.Parameters.Add("FNUMERIC", OleDbType.Numeric).Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "abcdefghijklmnopqrstuvwxyz";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "АБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "абвгдежзийклмнопрстуфхцчшщъыьэюя";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "ЁёҐґІіЇїЄє№";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "АБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "абвгдежзийклмнопрстуфхцчшщъыьэюя";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "ЁёҐґІіЇїЄє№";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
asciiBytes[2] = 0x0c3; // 0x0a5 (165) -> 0x0c3 (195) Ґ -> Г
asciiBytes[3] = 0x0e3; // 0x0b4 (180) -> 0x0e3 (227) ґ -> г
asciiBytes[4] = 0x0a1; // 0x0b2 (178) -> 0x0a1 (161) І -> Ў
asciiBytes[5] = 0x0a2; // 0x0b3 (179) -> 0x0a2 (162) і -> ў
asciiBytes[6] = 0x0b0; // 0x0af (175) -> 0x0b0 (176) Ї -> °
asciiBytes[7] = 0x0b7; // 0x0bf (191) -> 0x0b7 (183) ї -> ·
asciiBytes[8] = 0x0af; // 0x0aa (170) -> 0x0af (175) Є -> Ї
asciiBytes[9] = 0x0bf; // 0x0ba (186) -> 0x0bf (191) є -> ї
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866Chars[7] = (char)0x02219; // •
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = tmpString = CP1251ToCP866("Line# 2 Линия № 2 Лінія № 2");
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
fstr_out_866.WriteLine(tmpString);
#if TEST_DBF_JOIN
cmd.CommandType = CommandType.Text;
/*
* cmd.CommandText=@"
* select
* m.*,
* d_l_I.*,
* d_l_II.*
* from
* master m
* left outer join det_l_I d_l_I on (d_l_I.Master_ID=m.ID)
* left outer join det_l_II d_l_II on (d_l_II.Master_ID=m.ID) and (d_l_II.det_l_I_ID=d_l_I.ID)
* order by m.ID, d_l_I.ID, d_l_II.ID
* ";
*/
/*
* cmd.CommandText=@"
* select
* m.*,
* d_l_I.*,
* d_l_II.*
* from
* master m
* left outer join det_l_I d_l_I on (d_l_I.Master_ID=m.ID)
* left outer join det_l_II d_l_II on (d_l_II.Master_ID=d_l_I.Master_ID) and (d_l_II.det_l_I_ID=d_l_I.ID)
* order by m.ID, d_l_I.ID, d_l_II.ID
* ";
*/
cmd.CommandText = @"
select
m.*,
d_l_I.*,
d_l_II.*
from
master m
join det_l_I d_l_I on (d_l_I.Master_ID=m.ID)
join det_l_II d_l_II on (d_l_II.Master_ID=m.ID) and (d_l_II.det_l_I_ID=d_l_I.ID)
order by m.ID, d_l_I.ID, d_l_II.ID
";
/*
* cmd.CommandText=@"
* select
* m.*,
* d_l_I.*,
* d_l_II.*
* from
* master m
* join det_l_I d_l_I on (d_l_I.Master_ID=m.ID)
* join det_l_II d_l_II on (d_l_II.Master_ID=d_l_I.Master_ID) and (d_l_II.det_l_I_ID=d_l_I.ID)
* order by m.ID, d_l_I.ID, d_l_II.ID
* ";
*/
if (da == null)
{
da = new OleDbDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
if (tmpDataTable == null)
{
tmpDataTable = new DataTable();
}
else
{
tmpDataTable.Reset();
}
da.Fill(tmpDataTable);
tmpDataTable.Reset();
#endif
conn.Close();
#endif
#if TEST_VFP
if (!PathToDbf.EndsWith(Path.DirectorySeparatorChar.ToString()))
{
PathToDbf += Path.DirectorySeparatorChar;
}
tmpString = "Provider=vfpoledb.1;Data Source=" + PathToDbf; //Collating Sequence=general";
conn = new OleDbConnection(tmpString);
conn.Open();
fstr_out.WriteLine("ConnectionString: " + conn.ConnectionString);
fstr_out.WriteLine("ConnectionTimeout: " + conn.ConnectionTimeout.ToString());
fstr_out.WriteLine("Database: " + conn.Database);
fstr_out.WriteLine("DataSource: " + conn.DataSource);
fstr_out.WriteLine("Provider: " + conn.Provider);
fstr_out.WriteLine("ServerVersion: " + conn.ServerVersion);
fstr_out.WriteLine("State: " + conn.State.ToString());
fstr_out.WriteLine();
tmpString = PathToDbf + CommonDbfTableName + ".dbf";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = CommonDbfTableSQLCreate;
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into " + CommonDbfTableName + " (FInt, FChar) values (1, 'Line# 1 Линия № 1 Лінія № 1')";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into " + CommonDbfTableName + " (FInt, FChar, FDate1, FDate2) values (?, ?, ?, ?)";
cmd.Parameters.Clear();
cmd.Parameters.Add("FInt", OleDbType.Integer).Value = 2;
cmd.Parameters.Add("FChar", OleDbType.VarChar).Value = "Line# 2 Линия № 2 Лінія № 2";
cmd.Parameters.Add("FDate1", OleDbType.DBDate).Value = DateTime.Now;
cmd.Parameters.Add("FDate2", OleDbType.Date).Value = DateTime.Now;
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into test_t3 (FDate1, FDate2, FChar1) values (?, ?, ?)";
cmd.Parameters.Clear();
cmd.Parameters.Add("FDate1", OleDbType.Date).Value = DateTime.Now;
cmd.Parameters.Add("FDate2", OleDbType.DBDate).Value = DateTime.Now;
cmd.Parameters.Add("FChar1", OleDbType.Char).Value = CP1251ToCP866("Line# 2 Линия № 2 Лінія № 2");
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into test_t3 (FDate1, FDate2, FChar1) values ({12/31/2008},{12/31/2008},'" + CP1251ToCP866("Line# 2 Линия № 2 Лінія № 2") + "')";
cmd.ExecuteNonQuery();
DbfFileName = "test_ins";
cmd.CommandText = "delete from " + DbfFileName;
cmd.ExecuteNonQuery();
cmd.CommandText = "select max(ID) from " + DbfFileName;
tmpObject = cmd.ExecuteScalar();
_ID_ = !Convert.IsDBNull(tmpObject) ? Convert.ToInt32(tmpObject) + 1 : 1;
cmd.CommandText = "insert into " + DbfFileName + " (ID, FCHAR, DDATE, FNUMERIC) values (?, ?, ?, ?)";
cmd.Parameters.Clear();
cmd.Parameters.Add("ID", OleDbType.Numeric).Value = _ID_;
cmd.Parameters.Add("FCHAR", OleDbType.VarChar).Value = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
cmd.Parameters.Add("DDATE", OleDbType.Date).Value = DateTime.Now;
cmd.Parameters.Add("FNUMERIC", OleDbType.Numeric).Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "abcdefghijklmnopqrstuvwxyz";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "АБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "абвгдежзийклмнопрстуфхцчшщъыьэюя";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "ЁёҐґІіЇїЄє№";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "АБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "абвгдежзийклмнопрстуфхцчшщъыьэюя";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "ЁёҐґІіЇїЄє№";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
asciiBytes[2] = 0x0c3; // 0x0a5 (165) -> 0x0c3 (195) Ґ -> Г
asciiBytes[3] = 0x0e3; // 0x0b4 (180) -> 0x0e3 (227) ґ -> г
asciiBytes[4] = 0x0a1; // 0x0b2 (178) -> 0x0a1 (161) І -> Ў
asciiBytes[5] = 0x0a2; // 0x0b3 (179) -> 0x0a2 (162) і -> ў
asciiBytes[6] = 0x0b0; // 0x0af (175) -> 0x0b0 (176) Ї -> °
asciiBytes[7] = 0x0b7; // 0x0bf (191) -> 0x0b7 (183) ї -> ·
asciiBytes[8] = 0x0af; // 0x0aa (170) -> 0x0af (175) Є -> Ї
asciiBytes[9] = 0x0bf; // 0x0ba (186) -> 0x0bf (191) є -> ї
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866Chars[7] = (char)0x02219;
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = CP1251ToCP866("Line# 2 Линия № 2 Лінія № 2");
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
if (tmpDataTable == null)
{
tmpDataTable = new DataTable();
}
else
{
tmpDataTable.Reset();
}
cmd.CommandText = "select * from " + CommonDbfTableName;
if (da == null)
{
da = new OleDbDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
da.Fill(tmpDataTable);
tmpString = "select * from baza";
cmd = new OleDbCommand(tmpString, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; ++i)
{
fstr_out.Write(rdr[rdr.GetName(i)]);
if (i < rdr.FieldCount)
{
fstr_out.Write("\t");
}
}
fstr_out.WriteLine();
}
rdr.Close();
fstr_out.WriteLine();
if (tmpDataTable == null)
{
tmpDataTable = new DataTable();
}
else
{
tmpDataTable.Reset();
}
if (da == null)
{
da = new OleDbDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
da.Fill(tmpDataTable);
foreach (DataRow _r_ in tmpDataTable.Rows)
{
foreach (DataColumn _c_ in tmpDataTable.Columns)
{
fstr_out.Write(_r_[_c_.ColumnName] + "\t");
}
fstr_out.WriteLine();
}
fstr_out.WriteLine();
cmd.CommandText = "select * from test_ins";
da.SelectCommand = cmd;
tmpDataTable.Reset();
da.Fill(tmpDataTable);
foreach (DataRow _r_ in tmpDataTable.Rows)
{
foreach (DataColumn _c_ in tmpDataTable.Columns)
{
fstr_out.Write(_r_[_c_.ColumnName] + "\t");
}
fstr_out.WriteLine();
}
fstr_out.WriteLine();
tmpString = "select * from t";
cmd = new OleDbCommand(tmpString, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; ++i)
{
fstr_out.Write(rdr[rdr.GetName(i)]);
if (i < rdr.FieldCount)
{
fstr_out.Write("\t");
}
}
fstr_out.WriteLine();
}
rdr.Close();
fstr_out.WriteLine();
cmd.CommandType = CommandType.Text;
/*
* cmd.CommandText=@"
* select
* m.*,
* d_l_I.*,
* d_l_II.*
* from
* master m
* left outer join det_l_I d_l_I on (d_l_I.Master_ID=m.ID)
* left outer join det_l_II d_l_II on (d_l_II.Master_ID=m.ID) and (d_l_II.det_l_I_ID=d_l_I.ID)
* order by m.ID, d_l_I.ID, d_l_II.ID
* ";
*/
/*
* cmd.CommandText=@"
* select
* m.*,
* d_l_I.*,
* d_l_II.*
* from
* master m
* left outer join det_l_I d_l_I on (d_l_I.Master_ID=m.ID)
* left outer join det_l_II d_l_II on (d_l_II.Master_ID=d_l_I.Master_ID) and (d_l_II.det_l_I_ID=d_l_I.ID)
* order by m.ID, d_l_I.ID, d_l_II.ID
* ";
*/
cmd.CommandText = @"
select
m.*,
d_l_I.*,
d_l_II.*
from
master m
join det_l_I d_l_I on (d_l_I.Master_ID=m.ID)
join det_l_II d_l_II on (d_l_II.Master_ID=m.ID) and (d_l_II.det_l_I_ID=d_l_I.ID)
order by m.ID, d_l_I.ID, d_l_II.ID
";
/*
* cmd.CommandText=@"
* select
* m.*,
* d_l_I.*,
* d_l_II.*
* from
* master m
* join det_l_I d_l_I on (d_l_I.Master_ID=m.ID)
* join det_l_II d_l_II on (d_l_II.Master_ID=d_l_I.Master_ID) and (d_l_II.det_l_I_ID=d_l_I.ID)
* order by m.ID, d_l_I.ID, d_l_II.ID
* ";
*/
if (da == null)
{
da = new OleDbDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
if (tmpDataTable == null)
{
tmpDataTable = new DataTable();
}
else
{
tmpDataTable.Reset();
}
da.Fill(tmpDataTable);
tmpDataTable.Reset();
conn.Close();
#endif
Result = 0;
}
catch (Exception eException)
{
Console.WriteLine(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace);
}
}
finally
{
#if TEST_DBF_BY_ODBC
if (odbc_rdr != null && !odbc_rdr.IsClosed)
{
odbc_rdr.Close();
}
if (odbc_conn != null && odbc_conn.State == System.Data.ConnectionState.Open)
{
odbc_conn.Close();
}
#endif
if (rdr != null && !rdr.IsClosed)
{
rdr.Close();
}
if (conn != null && conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
if (fstr_out != null)
{
fstr_out.Close();
}
if (fstr_out_866 != null)
{
fstr_out_866.Close();
}
}
return(Result);
}
private string syncTables(string tableName, List <string> columnNames)
{
Console.WriteLine("Syncing {0}", tableName);
OdbcCommand deletealld3f = new OdbcCommand("DELETE FROM " + tableName, d3fConnection);
OdbcCommand getAllRowsInVetEddieTable = new OdbcCommand("SELECT * FROM " + tableName, vetEddieConnection);
OdbcCommand getRowsCountInVetEddieTable = new OdbcCommand("SELECT COUNT(*) FROM " + tableName, vetEddieConnection);
int totalRowsinVetEddie = Convert.ToInt32(getRowsCountInVetEddieTable.ExecuteScalar());
if (totalRowsinVetEddie == 0)
{
return("");
}
int totalRowsDeleted = deletealld3f.ExecuteNonQuery();
Console.WriteLine("Cleaned {0} - Total Rows Affected {1} ", tableName, totalRowsDeleted);
// queries that return result set are executed by ExecuteReader()
// If you are to run queries like insert, update, delete then
// you would invoke them by using ExecuteNonQuery()
OdbcDataReader rowsInVetEddieTable = getAllRowsInVetEddieTable.ExecuteReader();
StringBuilder insertionQuery = new StringBuilder();
int progressCount = 0;
string[] skip = { "AMH", "ORO", "SOM", "TIG" };
StringBuilder insertIntoD3fStringBuilder = new StringBuilder("INSERT INTO " + tableName);
insertIntoD3fStringBuilder.Append(" (");
bool allSkipsHaveBeenRemoved = false;
// ignore language columns
foreach (string sk in skip)
{
allSkipsHaveBeenRemoved = columnNames.Remove(sk);
}
foreach (String name in columnNames)
{
insertIntoD3fStringBuilder.Append(name);
if (columnNames.IndexOf(name) < columnNames.Count - 1)
{
insertIntoD3fStringBuilder.Append(",");
}
}
insertIntoD3fStringBuilder.Append(") ");
insertIntoD3fStringBuilder.Append("VALUES");
int rowIndex = 1;
while (rowsInVetEddieTable.Read())
{
insertIntoD3fStringBuilder.Append("(");
int columnCount = rowsInVetEddieTable.FieldCount;
if (allSkipsHaveBeenRemoved)
{
columnCount = columnCount - skip.Length;//adjust the length of columns if we have removed the language ones
}
for (int i = 0; i < columnCount; i++)
{
if (rowsInVetEddieTable.GetName(i).Equals("AMH") ||
rowsInVetEddieTable.GetName(i).Equals("ORO") ||
rowsInVetEddieTable.GetName(i).Equals("SOM") ||
rowsInVetEddieTable.GetName(i).Equals("TIG"))
{
continue;
}
string val = rowsInVetEddieTable.GetValue(i).ToString();
String type = rowsInVetEddieTable.GetDataTypeName(i);
// Console.Write(" " + type + " ");
if (type.Equals("varchar") || type.Equals("date"))
{
insertIntoD3fStringBuilder.Append("'");
if (type.Equals("date") && !val.Equals("0000-00-00") && !val.Equals(""))
{
val = val.Split(' ')[0];
// Console.WriteLine(val);
try
{
DateTime temp = DateTime.ParseExact(val, "dd/MM/yyyy", null);
val = temp.ToString("yyyy-MM-dd");
}
catch (Exception e)
{
Console.WriteLine("Problem with date {0} ", val);
}
}
insertIntoD3fStringBuilder.Append(val);
insertIntoD3fStringBuilder.Append("'");
}
else
{
insertIntoD3fStringBuilder.Append(val);
}
if (i < columnCount - 1)
{
insertIntoD3fStringBuilder.Append(",");
}
}
insertIntoD3fStringBuilder.Append(")");
//Console.WriteLine(insertIntoD3fStringBuilder.ToString());
if (rowIndex < totalRowsinVetEddie)
{
insertIntoD3fStringBuilder.Append(",");
}
insertIntoD3fStringBuilder.AppendLine();
rowIndex++;
// Console.WriteLine(rowIndex);
progressCount++;
double progress = ((float)progressCount / (float)totalRowsinVetEddie) * 100.0f;
Console.WriteLine("Syncing Progress {0}% ", progress);
Console.SetCursorPosition(0, Console.CursorTop - 1);
if (progress < 99)
{
ClearCurrentConsoleLine();
}
// break;//DELETE LATER
}
rowsInVetEddieTable.Close();
Console.WriteLine(insertIntoD3fStringBuilder.ToString());
return(insertIntoD3fStringBuilder.ToString());
}
