public comboMaker(String sqlCmd, String sqlCmdID, Dictionary <String, String> dictionary, ObservableCollection <string> comboClass)
{
object[] obj = new object[10];
object[] objID = new object[10];
String cxnString = "Driver={SQL Server};Server=HC-sql7;Database=REVINT;Trusted_Connection=yes;";
OdbcConnection connection = new OdbcConnection(cxnString);
using (OdbcConnection connectionID = new OdbcConnection(cxnString))
{
OdbcCommand command = new OdbcCommand(sqlCmd, connection);
OdbcCommand commandID = new OdbcCommand(sqlCmdID, connectionID);
connection.Open();
connectionID.Open();
OdbcDataReader reader = command.ExecuteReader();
OdbcDataReader reader2 = commandID.ExecuteReader();
while (reader.Read() && reader2.Read())
{
int numCols = reader.GetValues(obj);
numCols = reader2.GetValues(objID);
for (int i = 0; i < numCols; i++)
{
dictionary.Add(obj[i].ToString(), objID[i].ToString());
comboClass.Add(obj[i].ToString());
}
}
}
}
public static void SaveResultsTable(string MyConString, string sql, string Filename)
{
OdbcConnection conn = new OdbcConnection(MyConString);
conn.Open();
OdbcCommand comm = new OdbcCommand(sql, conn);
OdbcDataReader dr = comm.ExecuteReader();
using (System.IO.StreamWriter file = new System.IO.StreamWriter(Filename, false))
{
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
object[] fields = new object[dr.FieldCount];
sw.Start();
while (dr.Read())
{
dr.GetValues(fields);
for (int i = 0; i < fields.Length; i++)
{
file.Write(fields.ToString() + ",");
}
file.WriteLine("0");
}
}
conn.Close();
dr.Close();
comm.Dispose();
conn.Dispose();
}
public static void Main()
{
using (OdbcConnection connection =
new OdbcConnection("Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\\Northwind.mdb")) {
object[] meta = new object[10];
bool read;
OdbcCommand command = new OdbcCommand("select * from Shippers", connection);
connection.Open();
OdbcDataReader reader = command.ExecuteReader();
if (reader.Read() == true)
{
do
{
int NumberOfColums = reader.GetValues(meta);
for (int i = 0; i < NumberOfColums; i++)
{
Console.Write("{0} ", meta[i].ToString());
}
Console.WriteLine();
read = reader.Read();
} while (read == true);
}
reader.Close();
}
}
public idMaker(String sqlCmdID, List <object> ids)
{
object[] objID = new object[40];
String cxnString = "Driver={SQL Server};Server=HC-sql7;Database=REVINT;Trusted_Connection=yes;";
using (OdbcConnection connectionID = new OdbcConnection(cxnString))
{
OdbcCommand commandID = new OdbcCommand(sqlCmdID, connectionID);
connectionID.Open();
OdbcDataReader reader = commandID.ExecuteReader();
while (reader.Read())
{
int numCols = reader.GetValues(objID);
for (int i = 0; i < numCols; i++)
{
ids.Add((object)objID[i].ToString().ToUpper());
}
}
}
}
// execute SQL statement
public int ExecSQL(cDB obj, string sql, bool is_quary)
{
dbCmd = new OdbcCommand(sql, dbCon);
dbCon.Open();
if (is_quary)
{
// select
using (OdbcDataReader dbr = dbCmd.ExecuteReader())
{
int count;
object[] row;
while (dbr.Read())
{
// read each row at a time
count = dbr.FieldCount;
row = new object[count];
dbr.GetValues(row);
obj.ReadData(row);
}
}
}
else
{
// delete, create, update, insert
dbCmd.ExecuteNonQuery();
}
return(0);
}
/// <summary>
/// Executes the statement against Crate
/// </summary>
/// <param name="statement">the sql statement</param>
/// <returns>the response of the execution</returns>
public OdbcResponse Exec(string statement)
{
using (OdbcConnection conn = new OdbcConnection(connectionString))
{
try
{
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = statement;
OdbcDataReader dbReader = cmd.ExecuteReader();
List <string> cols = Enumerable.Range(0, dbReader.FieldCount).Select(dbReader.GetName).ToList();
List <Type> types = Enumerable.Range(0, dbReader.FieldCount).Select(dbReader.GetFieldType).ToList();
object[] row = new object[dbReader.FieldCount];
List <object[]> response = new List <object[]>();
while (dbReader.Read())
{
dbReader.GetValues(row);
response.Add(row);
}
return(new OdbcResponse(cols.ToArray(), response.ToArray(), types));
}
catch (Exception ex)
{
Console.WriteLine("Error during statement execution: " + ex.Message);
return(new OdbcResponse(null, null, null));
}
}
}
public static DataTable ToDataTable(this OdbcDataReader reader)
{
var schema = reader.GetSchemaTable();
var table = SchemaTableToDataTable(schema);
object[] row = new object[reader.FieldCount];
while (true)
{
var rc = reader.Read();
if (rc == false)
{
break;
}
var cx = reader.GetValues(row);
var dataRow = table.NewRow();
for (int ix = 0; ix < reader.FieldCount; ++ix)
{
dataRow[ix] = reader[ix];
}
table.Rows.Add(dataRow);
}
return(table);
}
private void frmReportView_Load(object sender, EventArgs e)
{
string databasePath = AppDomain.CurrentDomain.BaseDirectory + "\\EquipmentDatabase.accdb";
con = new OdbcConnection("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + databasePath + ";Uid=Admin;PWD=;");
cmd = con.CreateCommand();
cmd.CommandText = queryString;
con.Open();
reader = cmd.ExecuteReader();
if (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
dgvData.Columns.Add(reader.GetName(i), reader.GetName(i));
}
do
{
object[] rowData = new object[reader.FieldCount];
reader.GetValues(rowData);
dgvData.Rows.Add(rowData);
} while((reader.Read()));
}
else
{
MessageBox.Show("There was no data to retireve");
}
con.Close();
}
public static object[,] GetAllData_mySQL(string MyConString, string sql)
{
OdbcConnection conn = new OdbcConnection(MyConString);
conn.Open();
OdbcCommand comm = new OdbcCommand(sql, conn);
OdbcDataReader dr = comm.ExecuteReader();
object[,] table = new object[dr.RecordsAffected + 1, dr.FieldCount];
object[] fields = new object[dr.FieldCount];
int cc = 1;
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
while (dr.Read())
{
dr.GetValues(fields);
for (int i = 0; i < fields.Length; i++)
{
table[0, i] = dr.GetName(i);
var t = fields[i].GetType();
if (t == typeof(double))
{
table[cc, i] = (double)fields[i];
}
else if (t == typeof(int))
{
table[cc, i] = (double)(int)fields[i];
}
else if (t == typeof(Int64))
{
table[cc, i] = (double)(Int64)fields[i];
}
else if (t == typeof(float))
{
table[cc, i] = (double)(float)fields[i];
}
else
{
table[cc, i] = fields[i].ToString();
}
}
cc = cc + 1;
}
System.Diagnostics.Debug.Print(sw.ElapsedMilliseconds.ToString());
conn.Close();
dr.Close();
comm.Dispose();
conn.Dispose();
return(table);
}
private void Update(string field, int ix = 0, bool isFiltered = false) // обновление списка с записями field поле по которому сортируем
// ix индекс элемента, который хотим выбрать
// isFiltered флаг надо ли фильровать
{
OdbcCommand command = dbConnection.CreateCommand(); // создание ODBC комманды
if (!isFiltered)
{
// записываем команду
// {string.Join(", ", dbFields)} - соеденияет все элементы коллекции dbFields в строку через ", "
// и результат будет вписан внутрь строки, т.е. получится "SELECT ID, Name, VisitorsAmount, ... FROM [events] ...
// {field} - просто подставит значение переменной в строку, т.е. получится что-то наподобие ... ORDER BY ID ... (если field равен строке "ID")
// {sortTypeComboBox.Text.ToUpper()} - берёт ыбранное значение из списка возвможных соритровок (это такая выпдающая менюшка, с значениями Asc и Desc) и приводит его к виду ASC или DESC
command.CommandText = $"SELECT {string.Join(", ", dbFields)} FROM [events] ORDER BY {field} {sortTypeComboBox.Text.ToUpper()}";
}
else
{
// {filterCommand} - если выбрана опция фильтрации вставляет значение переменной в строку
// значение этой переменной задётся в другой функции ниже может быть таким например:
// "WHERE VisitorsAmount>10 AND Name=John AND ..."
command.CommandText = $"SELECT {string.Join(", ", dbFields)} FROM [events] {filterCommand} ORDER BY {field} {sortTypeComboBox.Text.ToUpper()}";
}
OdbcDataReader reader = command.ExecuteReader(); // выполняем команду
eventsListBox.Items.Clear(); // очищаем список элементов
object[] fieldValues = new object[8]; // массив куда будем записывать значения полученные из Access
int i = 0;
while (reader.Read()) // считывает 1 строку табилцы
{
reader.GetValues(fieldValues); // запоминаем значения в массив
eventsListBox.Items.Add(string.Join(" ",
fieldValues.Select(v => // для каждого значения из fieldValues проверяем тип данных
// и если дата то конвертирем в строку с применением формата даты (записанного выше)
// иначе просто преобразовываем в строку
{
if (v.GetType() == typeof(DateTime))
{
return(((DateTime)v).ToString(dateTimeFormat));
}
return(v.ToString());
}).ToArray())); // все значения преобразовываем в массив и массив соедениям в строку через пробел (строка 119: string.Join(" ", ...)
if (i++ == 0)
{
setFields(fieldValues); // вызывается только для первой записи в таблице (чтобы заполнить подробную информцию элемента)
}
}
eventsListBox.SetSelected(ix, true); // выделяем элемент из списка записей
reader.Close(); // закрываем объект для чтения данных из таблицы Access
}
public void InsertOne(OdbcDataReader reader, string tableName)
{
DataTable dt = reader.GetSchemaTable();
if (dt != null)
{
//string colstr = helper.CreateColumnStringBySqlReaderSchema(資料表名稱[no], dt);
string colstr = CreateColumnStringBySqlReaderSchema(dt);
CreateTable(tableName, colstr);
}
bool first = true;
using (var cn = new SQLiteConnection(ConnectionString))
{
cn.Open();
using (var transaction = cn.BeginTransaction())
{
using (var cmd = cn.CreateCommand())
{
while (reader.Read())
{
Object[] values = new Object[reader.FieldCount];
int fieldCounts = reader.GetValues(values);
if (first)
{
cmd.CommandText = GetInsertString(tableName);
for (int i = 0; i < fieldCounts; i++)
{
cmd.Parameters.AddWithValue(ParameterList[i], values[i]);
}
first = false;
}
else
{
for (int i = 0; i < fieldCounts; i++)
{
cmd.Parameters[i].Value = values[i];
}
}
cmd.ExecuteNonQuery();
values = null;
}
}
transaction.Commit();
}
}
}
public static List <FraudulentClaim> GetInsuranceFraudData()
{
string filePath = Path.Combine(Directory.GetCurrentDirectory(), "Files", "ABI_DRIVER_FRAUD1.accdb");
string connectionString = @"Driver={Microsoft Access Driver (*.mdb, *.accdb)}; Dbq=" + filePath + ";";
string queryString = "SELECT * FROM fraudulent_claim_data;";
object[] columns = new object[7];
FraudulentClaim fraudulentClaim;
List <FraudulentClaim> listOfFraudulentClaims = new List <FraudulentClaim>();
try
{
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
OdbcCommand command = new OdbcCommand(queryString, connection);
connection.Open();
OdbcDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int numberOfColumns = reader.GetValues(columns);
fraudulentClaim = new FraudulentClaim();
fraudulentClaim.ID = (int)columns[0];
fraudulentClaim.FamilyName = (string)columns[1];
fraudulentClaim.Forenames = (string)columns[2];
fraudulentClaim.DateOfBirth = (DateTime)columns[3];
fraudulentClaim.AddressOfClaim = (string)columns[4];
fraudulentClaim.DateOfClaim = (DateTime)columns[5];
fraudulentClaim.InsurerCode = (int)columns[6];
listOfFraudulentClaims.Add(fraudulentClaim);
}
reader.Close();
}
return(listOfFraudulentClaims);
}
catch (Exception e)
{
return(null);
}
}
public void ShowData(OdbcConnection connection)
{
int selectedID = -1;
//определяем ID файла
if (GetSelectedNode().Contains("File_") == true)
{
selectedID = Convert.ToInt32(AskNodeFileID());
}
else
{
return;
}
string CommandText =
$"SELECT * " +
$"FROM public.{quote}Files{quote}" +
$"WHERE {quote}FileID{quote} = '{(int)selectedID}'" +
$"ORDER BY {quote}TypeID{quote} ASC ";
OdbcCommand TypeReaderCommand = new OdbcCommand(CommandText, connection);
//Вывод информации о типе в текстовое поле
OdbcDataReader reader = TypeReaderCommand.ExecuteReader();
while (reader.Read())
{
FileStream fstream;
try
{
//считываем данные
object[] variables = new object[6];
reader.GetValues(variables);
byte[] FileArray = ((byte[])reader.GetValue(5)); //Content
//отображаем данные
textBox2.Text = Encoding.Default.GetString(FileArray);
}
catch (Exception ex)
{
}
}
reader.Close();
}
private void selectedIndexChenged(object sender, EventArgs e) // вызывается при изменении выбранного элемента в списке
// обновляет значения в полях (обновляет подробную информцию)
{
try
{
string id = eventsListBox.SelectedItem.ToString().Split(' ')[0];
OdbcCommand command = dbConnection.CreateCommand();
command.CommandText = $"SELECT {string.Join(", ", dbFields)} FROM [events] WHERE [ID]={id}";
OdbcDataReader reader = command.ExecuteReader();
reader.Read();
object[] values = new object[8];
reader.GetValues(values);
setFields(values);
}
catch {}
}
public ActionResult Download()
{
Response.Clear();
Response.ContentType = "octet/stream";
Response.AppendHeader("content-disposition", "attachment; filename=spells_us.txt");
using (OdbcDataReader _data = DB.OpenDataStream("SELECT * FROM spells_new ORDER BY id;"))
{
if ((_data != null) && (_data.HasRows))
{
Object[] _row = new Object[_data.FieldCount];
while (_data.Read())
{
bool _firstField = true;
_data.GetValues(_row);
foreach (Object _value in _row)
{
if (_firstField)
{
_firstField = false;
}
else
{
Response.Write('^');
}
Response.Write(_value == null ? "" : _value.ToString());
}
Response.Write("\r\n");
Response.Flush();
}
}
}
Response.End();
return(null);
}
public comboMaker(String sqlCmd, ObservableCollection <string> comboClass)
{
object[] obj = new object[10];
String cxnString = "Driver={SQL Server};Server=HC-sql7;Database=REVINT;Trusted_Connection=yes;";
using (OdbcConnection connection = new OdbcConnection(cxnString))
{
OdbcCommand command = new OdbcCommand(sqlCmd, connection);
connection.Open();
OdbcDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int numCols = reader.GetValues(obj);
if (numCols > 0)
{
comboClass.Add(obj[0].ToString());
}
}
}
}
// execute special select SQL statement
public int ExecSQL_RowCount(cDB obj, string sql, string tbl_name)
{
int rc = 0;
dbCmd = new OdbcCommand(sql, dbCon);
dbCon.Open();
using (OdbcDataReader dbr = dbCmd.ExecuteReader()) {
int count;
object[] row;
if (dbr.Read())
{
count = dbr.FieldCount;
row = new object[count];
dbr.GetValues(row);
rc = Convert.ToInt32(row[0]);
}
}
return(rc);
}
private static string GetReaderInfo(OdbcDataReader reader)
{
string str = string.Empty;
try
{
object[] fields = new object[reader.VisibleFieldCount];
reader.GetValues(fields);
str = string.Empty;
int index = 0;
foreach (var field in fields)
{
str += reader.GetName(index) + ": " + field.ToString() + ",";
}
}
catch (OdbcException e)
{
str = $"GetReaderInfo failed with message: {e.Message}";
}
return(str);
}
/// <summary>
/// executes a sql select statement in the database
/// </summary>
/// <param name="sqlStatement">sql select statement</param>
/// <returns></returns>
protected List <object[]> executeSelect(string sqlStatement)
{
List <object[]> rows = new List <object[]>();
try {
if (_conn.State != ConnectionState.Open)
{
_conn.Open();
}
_cmd = _conn.CreateCommand();
_cmd.CommandText = sqlStatement;
_reader = _cmd.ExecuteReader();
int columns = _reader.FieldCount;
while (_reader.Read())
{
object[] temp = new object[columns];
_reader.GetValues(temp);
rows.Add(temp);
}
return(rows);
} catch (OdbcException exc) {
throw exc;
}
}
/// <summary>
/// Search method
/// </summary>
/// <param name="directory">Search settings<seealso cref="DirectoryType"/></param>
/// <returns>A dataset conform to the FieldFormatters specified<seealso cref="FieldFormatter"/></returns>
public static DataSet Search(DirectoryType directory)
{
DataSet results = new DataSet();
DataTable dt = results.Tables.Add();
OdbcConnection odbc = new OdbcConnection();
OdbcDataReader reader = null;
OdbcCommand command = null;
try
{
string dsn = "DSN=";
dsn += ((SqlDatasourceType)directory.Item).dsn;
dsn += ";Uid=";
dsn += ((SqlDatasourceType)directory.Item).uid;
dsn += ";Pwd=";
dsn += ((SqlDatasourceType)directory.Item).pwd;
odbc.ConnectionString = dsn;
log.Debug("Opening ODBC connection...");
odbc.Open();
string sql = ((SqlDatasourceType)directory.Item).command + " " + ((SqlDatasourceType)directory.Item).sqlFilter;
log.Debug("Initializing ODBC command: " + sql);
command = odbc.CreateCommand();
command.CommandText = sql;
log.Debug("Loading data in memory...");
reader = command.ExecuteReader();
DataTable schema = reader.GetSchemaTable();
foreach (DataRow dr in schema.Rows)
{
dt.Columns.Add((string)dr[0], System.Type.GetType(((Type)dr[5]).FullName));
}
object[] values = new object[dt.Columns.Count];
while (reader.Read())
{
reader.GetValues(values);
dt.Rows.Add(values);
}
return(results);
}
catch (Exception e)
{
throw new Exception("Request failed!", e);
}
finally
{
if (reader != null)
{
reader.Close();
reader.Dispose();
}
if (command != null)
{
command.Dispose();
}
if (odbc != null)
{
odbc.Close();
odbc.Dispose();
}
}
}
public static double[,] GetData_mySQL(string MyConString, string sql, int identityColumn, string[] analyteName, int[] analyteIndex)
{
//string MyConString = "DSN=recognition;" +
// "UID=root;" +
// "PASSWORD=Shawntel75;";
OdbcConnection conn = new OdbcConnection(MyConString);
conn.Open();
identityColumn = identityColumn - 1;
// string sql = "SELECT * FROM Peaks ;";
Dictionary <string, int> analyteMappings = new Dictionary <string, int>();
for (int i = 0; i < analyteName.Length; i++)
{
analyteMappings.Add(analyteName[i].ToLower(), analyteIndex[i]);
}
OdbcCommand comm = new OdbcCommand(sql, conn);
OdbcDataReader dr = comm.ExecuteReader();
double[,] table = new double[dr.RecordsAffected, dr.FieldCount];
object[] fields = new object[dr.FieldCount];
int cc = 0;
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
while (dr.Read())
{
dr.GetValues(fields);
for (int i = 0; i < fields.Length; i++)
{
if (i == identityColumn)
{
try
{
table[cc, i] = (double)analyteMappings[((string)fields[i]).ToLower()];
}
catch (Exception ex)
{
table[cc, i] = -1;
//throw new Exception(((string)fields[i]).ToLower() + " not found");
}
}
else
{
var t = fields[i].GetType();
if (t == typeof(double))
{
table[cc, i] = (double)fields[i];
}
else if (t == typeof(int))
{
table[cc, i] = (double)(int)fields[i];
}
else if (t == typeof(Int64))
{
table[cc, i] = (double)(Int64)fields[i];
}
else if (t == typeof(float))
{
table[cc, i] = (double)(float)fields[i];
}
}
}
//for (int i = 1; i < 5; i++)
// table[cc, i] = (double)(int)fields[i];
//for (int i = 5; i < 7; i++)
// table[cc, i] = (double)(Int64)fields[i];
//for (int i = 7; i < fields.Length; i++)
// table[cc, i] = (double)fields[i];
cc = cc + 1;
// Application.DoEvents();
}
System.Diagnostics.Debug.Print(sw.ElapsedMilliseconds.ToString());
conn.Close();
dr.Close();
comm.Dispose();
conn.Dispose();
return(table);
}
private static String sqlODBC()
{
try
{
Console.WriteLine("Test avec connexion ODBC.");
Regex regex = new Regex("[ ]{2,}", RegexOptions.None);
String tempFileName = System.IO.Path.GetTempFileName();
String sqlServer = @"VirtuoTest";
Int32 sqlTableCount = 0;
Int32 sqlLines = 0;
System.IO.StreamWriter file = new System.IO.StreamWriter(tempFileName);
Console.WriteLine(@"Nom de l'ordinateur: " + System.Environment.MachineName);
OdbcConnection connection = new OdbcConnection("DSN=" + sqlServer);
connection.Open();
Console.WriteLine(@"Connecté à " + sqlServer);
Console.WriteLine("Obtention de la liste des tables à exporter");
OdbcCommand command = new OdbcCommand(@"SELECT TABLE_NAME FROM virtuo.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'", connection);
OdbcDataReader dataReader = command.ExecuteReader();
List <String> tableNames = new List <String>();
while (dataReader.Read())
{
tableNames.Add(dataReader.GetValue(0).ToString());
sqlTableCount++;
}
dataReader.Close();
Console.WriteLine(sqlTableCount + @" tables à exporter");
Console.WriteLine(@"Exportation vers le fichier " + tempFileName);
var watch = System.Diagnostics.Stopwatch.StartNew();
foreach (String tableName in tableNames)
{
file.WriteLine(@"Parsing table : " + tableName);
command = new OdbcCommand(@"SELECT * FROM virtuo.dbo." + tableName, connection);
dataReader = command.ExecuteReader();
Object[] obj = new Object[dataReader.FieldCount];
for (int i = 0; i < dataReader.FieldCount; i++)
{
obj[i] = dataReader.GetName(i);
}
file.WriteLine(String.Join(", ", obj));
while (dataReader.Read())
{
dataReader.GetValues(obj);
file.WriteLine(regex.Replace(String.Join(", ", obj), " "));
sqlLines++;
}
dataReader.Close();
file.WriteLine();
}
watch.Stop();
file.Close();
Console.WriteLine(@"Durée de l'exportation " + watch.Elapsed + ", " + sqlLines + " Lignes de données écrites dans le fichier.");
command.Dispose();
connection.Close();
return(tempFileName);
}
catch (Exception ex)
{
Console.WriteLine("Can not open connection ! ");
Console.WriteLine(ex.ToString());
Console.ReadLine();
return(null);
}
}
private void ProcessRun(string folder_str)
{
if (string.IsNullOrWhiteSpace(folder_str))
{
MessageBox.Show("Path to folder should be specified!", "Folder path", MessageBoxButton.OK,
MessageBoxImage.Error);
return;
}
if (!Directory.Exists(folder_str))
{
Directory.CreateDirectory(folder_str);
}
var connStr = Settings.Default.ConnectionString;
if (string.IsNullOrWhiteSpace(connStr))
{
connStr = $"DSN=MonetDB;Host=scalpelhost;Port=50000;Database=msinvent;Uid=user;Pwd=password;";
}
var resultSpectrum = new List <object[]>();
var querySpectrum = $@"select distinct rtrim(t.label) || '-' || sm.label || '-' || s.id,
'Extraction',
'Melted samples in ' || sol.name,
'none',
'N/A',
'Chromatography',
'Mass spectrometry',
m.name,
r.min || '-' || r.max,
dev.name,
'MS:electrospray ionization',
sol.name,
res.name,
s.id,
p.sex,
p.yob,
d.name,
s.filename
from tissue t
inner join spectrum s on t.id = s.sampletumorid
INNER join patient p on p.id = t.patientid
inner join smpl sm on sm.id = s.sampleid
inner join device dev on dev.id = s.device
inner join mode m on m.id = s.mode
inner join mzrange r on r.id = s.mzrange
inner join solvent sol on sol.id = s.solvent
inner join resolution res on res.id = s.resolutionid
inner join diagnosis d on d.id = t.diagnosis
where s.ionsource < 5 and not t.label like ('%unspecified%');";
using (OdbcConnection con = new OdbcConnection(connStr))
{
con.Open();
using (OdbcCommand com = new OdbcCommand(querySpectrum, con))
{
using (OdbcDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
var result = new object[17];
var obj = reader.GetValues(result);
if (result[9].Equals("LTQ_Leninsky"))
{
result[9] = "MS:LTQ FT Ultra";
if (result[12].Equals("FT"))
{
result[11] = "MS:fourier transform ion cyclotron resonance mass spectrometer";
}
else
{
result[11] = "MS:linear ion trap";
}
}
else
{
result[9] = "MS:LTQ Orbitrap XL ETD";
if (result[12].Equals("FT"))
{
result[11] = "MS:orbitrap";
}
else
{
result[11] = "MS:linear ion trap";
}
}
resultSpectrum.Add(result);
}
}
foreach (object[] objects in resultSpectrum)
{
var spectrum = new MsSpectrum {
Id = (int)objects[13], RawFilePath = (string)objects[17]
};
var loggerStr = $"Processing spectrum, id = {spectrum.Id}";
logger.Info($"Processing spectrum, id = {spectrum.Id}");
Dispatcher.BeginInvoke(new Action(() =>
{
statusLabel.Content = loggerStr;
}), DispatcherPriority.Background);
Thread piThread = new Thread(() =>
{
try
{
SaveDataToCdf(spectrum, folder_str);
}
finally
{
spectrum = null;
}
})
{
IsBackground = true
};
piThread.Start();
}
}
}
var fstr = new StreamWriter(Path.Combine(folder_str, $"list.txt"));
foreach (var spectrum in resultSpectrum)
{
var line = new StringBuilder(String.Join(";", spectrum));
line.Append($";{Path.Combine(folder_str, $"{spectrum[13]}.cdf")}");
fstr.WriteLine(line.ToString());
}
fstr.Flush();
fstr.Close();
}
public static void dumpOdbctoCsv(string vOdbcName, string vSqlCommand, string vCsvFile)
{
try
{
int i = 0;
string DsnConn = In2SqlSvcODBC.getODBCProperties(vOdbcName, "DSNStr");
if (DsnConn == null | DsnConn == "")
{
MessageBox.Show("Please make the connection by expand list on the left pane ", "sql run event",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
using (OdbcConnection conn = new System.Data.Odbc.OdbcConnection())
{
using (OdbcCommand cmnd = new OdbcCommand(vSqlCommand, conn))
{ conn.ConnectionString = DsnConn;
conn.ConnectionTimeout = 5;
conn.Open();
In2SqlSvcTool.addSqlLog(vOdbcName, vSqlCommand);
OdbcDataReader rd = cmnd.ExecuteReader();
object[] output = new object[rd.FieldCount];
using (var textWriter = new StreamWriter(@vCsvFile))
{
var writer = new CsvWriter(textWriter, CultureInfo.InvariantCulture);
writer.Configuration.Delimiter = ",";
writer.Configuration.ShouldQuote = (field, context) => true;
for (int j = 0; j < rd.FieldCount; j++)
{
output[j] = rd.GetName(j);
writer.WriteField(rd.GetName(j));
}
writer.NextRecord();
while (rd.Read())
{
rd.GetValues(output);
writer.WriteField(output);
writer.NextRecord();
i++;
}
conn.Close();
conn.Dispose();
} }
}
MessageBox.Show("Export completed. \n\r File name is " + vCsvFile + " \n\r Row count:" + i, "csv export",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception e)
{
if (e.HResult != -2147024809)
{
In2SqlSvcTool.ExpHandler(e, "dumpOdbctoCsv");
}
}
}