public static string GetDataTable(ref DataTable _ConnectionDt, ref DataTable ReturnDt, string SelectCommand)
{
OdbcConnection odbc_Conn = new OdbcConnection();
OdbcDataAdapter odbcAdap = new OdbcDataAdapter();
OdbcCommand odbcCmd = new OdbcCommand();
string ErrMsg = string.Empty;
try
{
ErrMsg = MakeInformixConnection(ref _ConnectionDt, ref odbc_Conn);
if (ErrMsg == string.Empty)
{
odbcCmd.Connection = odbc_Conn;
odbcCmd.CommandText = SelectCommand;
odbcAdap.SelectCommand = odbcCmd;
odbcAdap.Fill(ReturnDt);
odbc_Conn.Close();
}
return ErrMsg;
}
catch (Exception err)
{
return err.Message;
}
finally
{ odbcAdap.Dispose(); odbcCmd.Dispose(); odbc_Conn.Dispose(); }
}
static void Main ()
{
if (Environment.GetEnvironmentVariable ("MONO_TESTS_ODBC") == null)
return;
OdbcConnection conn = new OdbcConnection (CreateOdbcConnectionString ());
conn.Open ();
string dbName = Environment.GetEnvironmentVariable ("MONO_TESTS_SQL_DB");
Assert.AreEqual (dbName, conn.Database, "#1");
OdbcCommand cmd = new OdbcCommand ("CREATE DATABASE aфbиc", conn);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
try {
Assert.AreEqual (dbName, conn.Database, "#2");
conn.ChangeDatabase ("aфbиc");
Assert.AreEqual ("aфbиc", conn.Database, "#3");
conn.ChangeDatabase (dbName);
Assert.AreEqual (dbName, conn.Database, "#4");
} finally {
conn.Dispose ();
conn = new OdbcConnection (CreateOdbcConnectionString ());
conn.Open ();
cmd = new OdbcCommand ("DROP DATABASE aфbиc", conn);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
}
}
/// <summary>
/// Insert one datarow into database table
/// </summary>
/// <param name="thisEntrySameSeqEntriesTable"></param>
public void InsertDataIntoDb(DbConnect dbConnect, DataRow dRow)
{
if (!dbConnect.IsConnected())
{
dbConnect.ConnectToDatabase();
}
InsertionSqlString insertSqlStr = new InsertionSqlString(dRow.Table.TableName);
System.Data.Odbc.OdbcCommand insertCommand = dbConnect.CreateCommand();
try
{
for (int colI = 0; colI < dRow.Table.Columns.Count; colI++)
{
string colName = dRow.Table.Columns[colI].ColumnName;
insertSqlStr.AddKeyValuePair(colName, dRow[colName]);
}
insertCommand.CommandText = insertSqlStr.ToString();
insertCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(string.Format("{0} Insertion Errors: {1}", dRow.Table.TableName, ex.Message));
}
finally
{
insertSqlStr.ClearSqlInsertString();
insertCommand.Dispose();
}
}
public DataTable Execute(string query)
{
DataTable dt = new DataTable("Results");
using (OdbcConnection conn = new OdbcConnection(_connectionString))
{
conn.Open();
OdbcCommand command = new OdbcCommand(query.Replace("\n", " "), conn);
OdbcDataReader dr = command.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
dt.Columns.Add("");
}
while (dr.Read())
{
DataRow row = dt.NewRow();
for (int i = 0; i < dr.FieldCount; i++)
{
string s = dr[i].ToString();
row[i] = dr[i].ToString();
}
_nbRecords++;
dt.Rows.Add(row);
}
dr.Close();
dr.Dispose();
command.Dispose();
conn.Close();
}
return dt;
}
public static DataTable GetData(string strConn, string strSql, int timeout)
{
DataTable dt = new DataTable("td");
using (OdbcConnection conn = new OdbcConnection(strConn))
{
conn.Open();
OdbcCommand cmd = null;
OdbcDataAdapter da = null;
try
{
cmd = new OdbcCommand(strSql, conn) { CommandTimeout = timeout };
da = new OdbcDataAdapter { SelectCommand = cmd };
da.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw new Exception("error getting data " + ex.Message);
}
finally
{
if (da != null) { da.Dispose(); }
if (cmd != null) { cmd.Dispose(); }
conn.Close();
}
}
}
private static DataTable GetColumnsCollection(string?[]?restrictions, OdbcConnection connection)
{
OdbcCommand? command = null;
OdbcDataReader?dataReader = null;
DataTable? resultTable = null;
const int columnsRestrictionsCount = 4;
try
{
command = GetCommand(connection);
string[] allRestrictions = new string[columnsRestrictionsCount];
FillOutRestrictions(columnsRestrictionsCount, restrictions, allRestrictions, OdbcMetaDataCollectionNames.Columns);
dataReader = command.ExecuteReaderFromSQLMethod(allRestrictions, ODBC32.SQL_API.SQLCOLUMNS);
resultTable = DataTableFromDataReader(dataReader, OdbcMetaDataCollectionNames.Columns);
}
finally
{
dataReader?.Dispose();;
command?.Dispose();;
}
return(resultTable);
}
private void btnSignIn_Click(object sender, EventArgs e)
{
OdbcConnection Conn = new OdbcConnection("DSN=ora10glocal;uid=TTCS;PWD=Pass1234");
OdbcCommand cmd = new OdbcCommand("SELECT EMPLOYEEPASSWORD FROM EMPLOYEE WHERE LASTNAME = '" + txtUserName.Text + "'AND EMPLOYEETYPE = 'Foreman';", Conn);
Conn.Open();
Object EmpPass = cmd.ExecuteScalar();
cmd.Dispose();
Conn.Close();
if (txtUserName.Text == "")
{
MessageBox.Show("User name is empty!");
}
else if (Convert.ToString(EmpPass) == "")
{
MessageBox.Show("Password is empty!");
}
else if (Convert.ToString(EmpPass) != txtPassword.Text)
{
MessageBox.Show("Wrong password! please type again!");
}
else
{
string Emp = Convert.ToString(EmpPass);
var frm = new Form1(Emp);
frm.ShowDialog();
//this.Visible = false
}
}
public static System.Data.Odbc.OdbcDataReader ExecuteReaderQueryOnDB(string sSQL, string s_ConnectionString)
{
System.Data.Odbc.OdbcConnection QConnection = null;
System.Data.Odbc.OdbcCommand QCommand = null;
try
{
QConnection = new System.Data.Odbc.OdbcConnection(s_ConnectionString);
QCommand = new System.Data.Odbc.OdbcCommand(sSQL, QConnection);
QConnection.Open();
return(QCommand.ExecuteReader());
}
finally
{
if (QCommand != null)
{
QCommand.Dispose();
}
QCommand = null;
if (QConnection != null && QConnection.State != System.Data.ConnectionState.Closed)
{
QConnection.Close();
}
if (QConnection != null)
{
QConnection.Dispose();
}
QConnection = null;
}
}
/// <summary>
/// insert data into corresponding database tables
/// </summary>
public void InsertDataIntoDBtables(DbConnect dbConnect, DataTable[] dataTables)
{
#if DEBUG
logWriter = new StreamWriter("dbInsertErrorLog.txt", true);
#endif
try
{
if (!dbConnect.IsConnected())
{
dbConnect.ConnectToDatabase();
}
System.Data.Odbc.OdbcCommand insertCommand = dbConnect.CreateCommand();
foreach (DataTable dataTable in dataTables)
{
InsertionSqlString insertSqlStr = new InsertionSqlString(dataTable.TableName);
foreach (DataRow dRow in dataTable.Rows)
{
try
{
for (int colI = 0; colI < dataTable.Columns.Count; colI++)
{
string colName = dataTable.Columns[colI].ColumnName;
insertSqlStr.AddKeyValuePair(colName, dRow[colName]);
}
insertCommand.CommandText = insertSqlStr.ToString();
insertCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
// throw new Exception (string.Format ("{0} Insertion Errors: {1}", dataTable.TableName, ex.Message));
#if DEBUG
logWriter.WriteLine(string.Format("{0} Insertion Errors: {1}", dataTable.TableName, ex.Message));
logWriter.WriteLine(insertCommand.CommandText);
#endif
}
finally
{
insertSqlStr.ClearSqlInsertString();
}
}
// commit the insertion
insertCommand.CommandText = "Commit";
insertCommand.ExecuteNonQuery();
}
insertCommand.Dispose();
}
catch (Exception ex)
{
throw ex;
}
finally
{
#if DEBUG
logWriter.Close();
#endif
}
}
static void Main(string[] args)
{
OdbcConnection dbcon = new OdbcConnection();
// connection string to a Microsoft SQL Server 2000 database
// that does not use a DSN
//dbcon.ConnectionString =
// "DRIVER={SQL Server};" +
// "SERVER=(local);" +
// "Trusted_connection=true;" +
// "DATABASE=pubs;";
// connection string that uses a DSN.
dbcon.ConnectionString =
"DSN=LocalServer;UID=sa;PWD=";
dbcon.Open();
OdbcCommand dbcmd = new OdbcCommand();
dbcmd.Connection = dbcon;
dbcmd.CommandType = CommandType.Text;
dbcmd.CommandText = "SELECT lname FROM employee";
OdbcDataReader reader;
reader = (OdbcDataReader) dbcmd.ExecuteReader();
while(reader.Read()) {
Console.WriteLine("Last Name: " + reader[0].ToString());
}
reader.Close();
dbcmd.Dispose();
dbcon.Close();
}
public static string FetchTable(string SelectCommand, string XMLconnectionPhysicalFilePath, ref DataTable Dt)
{
OdbcConnection OdbcConn = new OdbcConnection();
OdbcCommand OdbcCmd = new OdbcCommand();
OdbcDataAdapter OdbcAdap = new OdbcDataAdapter();
try
{
string DbErr = InitializeODBCConnection(ref OdbcConn, XMLconnectionPhysicalFilePath);
if (DbErr == string.Empty)
{
OdbcCmd.Connection = OdbcConn;
OdbcCmd.CommandText = SelectCommand;
OdbcAdap.SelectCommand = OdbcCmd;
OdbcAdap.Fill(Dt);
return string.Empty;
}
else
return DbErr;
}
catch (Exception err)
{
return err.Message;
}
finally
{ OdbcAdap.Dispose(); OdbcCmd.Dispose(); OdbcConn.Close(); OdbcConn.Dispose(); }
}
public List<DataImportColumn> GetTableDataColumnList(string request)
{
List<DataImportColumn> lstDc = new List<DataImportColumn>();
using (OdbcConnection conn = new OdbcConnection(_connectionString))
{
OdbcCommand command = new OdbcCommand(request, conn);
conn.Open();
OdbcDataReader dr = command.ExecuteReader();
while (dr.Read())
{
lstDc.Add(new DataImportColumn()
{
Name = dr[0].ToString()
,
DataType = dr[3].ToString()
,
Size = dr[2].ToString()
});
}
dr.Close();
dr.Dispose();
command.Dispose();
conn.Close();
}
return lstDc;
}
private void InsertNotes()
{
Notes = Interaction.InputBox("Enter Note:", "TeleRad - Client", " ");
string strSQL = "";
string centre = " ";
Myconnectionclass CN2 = new Myconnectionclass();
OdbcCommand Cmd2;
OdbcDataReader Rs2;
CN2.OpenConnection();
Cmd2 = new System.Data.Odbc.OdbcCommand("SELECT * from UserMaster WHERE UserName='******'", CN2.DBConnection);
Rs2 = Cmd2.ExecuteReader();
if (Rs2.HasRows)
{
centre = Rs2["Centre"].ToString();
}
Rs2.Close();
Cmd2.Dispose();
CN2.closeconnection();
CN.OpenConnection();
Cmd = new System.Data.Odbc.OdbcCommand("SELECT * from Reports WHERE StudyUID='" + StID + "'", CN.DBConnection);
Rs1 = Cmd.ExecuteReader();
if (Rs1.HasRows)
{
if (Notes.Length <= 0)
{
strSQL = "UPDATE Reports SET IsNotes=0, Centre='" + centre + "' WHERE StudyUID='" + StID + "'";
}
else
{
strSQL = "UPDATE Reports SET Notes='" + Notes + "', IsNotes=1, Centre='" + centre + "' WHERE StudyUID='" + StID + "'";
}
}
else
{
if (Notes.Length <= 0)
{
strSQL = "INSERT INTO Reports (StudyUID, IsNotes,Centre) VALUES ('" + StID + "',1,'" + centre + "')";
}
else
{
strSQL = "INSERT INTO Reports (StudyUID, Notes, IsNotes,Centre) VALUES ('" + StID + "', '" + Notes + "', 1,'" + centre + "')";
}
}
Rs1.Close();
Cmd.Dispose();
CN.closeconnection();
CN.OpenConnection();
Cmd = new System.Data.Odbc.OdbcCommand(strSQL, CN.DBConnection);
Rs1 = Cmd.ExecuteReader();
Rs1.Close();
Cmd.Dispose();
CN.closeconnection();
MessageBox.Show("Successfully Sent Study", "TeleRad - Client", MessageBoxButtons.OK);
}
private void CompletedMovesDGV(string emp)
{
// Create table for the request information.
DataTable DeliveryLocation = new DataTable();
DeliveryLocation.Columns.Add("Request No.", typeof(Int32));
DeliveryLocation.Columns.Add("Status", typeof(string));
DeliveryLocation.Columns.Add("Start Location", typeof(string));
DeliveryLocation.Columns.Add("Required By", typeof(DateTime));
DeliveryLocation.Columns.Add("End Location", typeof(string));
DeliveryLocation.Columns.Add("Trailer No.", typeof(string));
DeliveryLocation.Columns.Add("Seal No", typeof(string));
DeliveryLocation.Columns.Add("Notes", typeof(string));
// Select all the request information.
// Select portion of the statement.
string select = "SELECT REQUESTS.REQUEST_NO,REQUESTS.Status,REQUESTS.LOCATION_ID,REQUESTS.SCHEDULED_DATE,REQUESTS.END_LOCATION,REQUESTS.TRAILER_NO,REQUESTS.SEALNO, REQUESTS.NOTES";
// Where clause
string where = " WHERE REQUESTS.STATUS <> 'Needed' AND REQUESTS.STATUS <> 'In Progress' AND REQUESTS.EMPLOYEE_ID ='" + emp + "' ";
// Connection string
OdbcConnection Conn = new OdbcConnection("DSN=ora10glocal;uid=TTCS;PWD=Pass1234");
// Command for the select statement.
OdbcCommand cmd = new OdbcCommand(select + " FROM REQUESTS" + where, Conn);
// Open connection
Conn.Open();
// This reads the information
OdbcDataReader rdr = cmd.ExecuteReader();
// While there is stuff ot be read.
while (rdr.Read())
{
// Add the information to the data table and columns listed.
DeliveryLocation.Rows.Add(
rdr.GetInt32(0),
rdr.GetString(1),
rdr.GetString(2),
rdr.GetDateTime(3),
rdr.GetString(4),
rdr.GetString(5),
rdr.GetString(6),
rdr.GetString(7)
);
}
// Destroy the information in the datagrid view.
dgvCompletedMoves.DataSource = null;
// Update it to make sure nothing is in it.
dgvCompletedMoves.Update();
// Fill it with proper information.
dgvCompletedMoves.DataSource = DeliveryLocation;
// kill the odbc command. If this is not done you'll have problems re using it.
dgvCompletedMoves.Columns[0].Visible = false;
cmd.Dispose();
// Close the connnection.
Conn.Close();
}
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
// dispose managed resources
Cmdr.Dispose();
Cmdw.Dispose();
}
}
/// <summary>
///
/// </summary>
/// <param name="dbConnect"></param>
/// <param name="insertString"></param>
public void InsertDataIntoDb(DbConnect dbConnect, string insertString)
{
if (!dbConnect.IsConnected())
{
dbConnect.ConnectToDatabase();
}
System.Data.Odbc.OdbcCommand insertCommand = dbConnect.CreateCommand();
insertCommand.CommandText = insertString;
insertCommand.ExecuteNonQuery();
insertCommand.Dispose();
}
/// <summary>
/// 利用SQL语句来对记录执行增删改操作
/// </summary>
/// <param name="cmdText">要执行的增删改的SQL语句</param>
/// <returns>返回受影响的行数</returns>
public int ExecuteNonQuery(string cmdText)
{
int num = -1;
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
OdbcCommand cmd = new OdbcCommand();
PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, null);
num = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Dispose();
}
return num;
}
public UInt64 GetCount(string tableName)
{
UInt64 count;
using (OdbcConnection connCount = new OdbcConnection(_connectionString))
{
OdbcCommand commandCount = new OdbcCommand(String.Format("SELECT count(*) FROM {0}", tableName), connCount);
commandCount.CommandTimeout = 300;
connCount.Open();
count = UInt64.Parse(commandCount.ExecuteScalar().ToString());
commandCount.Dispose();
connCount.Close();
}
return count;
}
private void AllRequestsForDay()
{
// Datatable.
DataTable AllDriverRequestsFD = new DataTable();
AllDriverRequestsFD.Columns.Add("Request No", typeof(Int32));
AllDriverRequestsFD.Columns.Add("Component", typeof(string));
AllDriverRequestsFD.Columns.Add("Supplier", typeof(string));
AllDriverRequestsFD.Columns.Add("Quantity", typeof(Int32));
AllDriverRequestsFD.Columns.Add("Status", typeof(string));
AllDriverRequestsFD.Columns.Add("Time Due", typeof(DateTime));
AllDriverRequestsFD.Columns.Add("First Name", typeof(string));
AllDriverRequestsFD.Columns.Add("Last Name", typeof(string));
// Connections string.
OdbcConnection Conn = new OdbcConnection("DSN=ora10glocal;uid=TTCS;PWD=Pass1234");
// Query
string AllDriverRequestsQuery = "Select Request_No, Component,SUPPLIER,Quantity,Status,SCHEDULED_DATE,EMPLOYEE.FIRSTNAME,EMPLOYEE.LASTNAME FROM REQUESTS,EMPLOYEE WHERE REQUESTS.EMPLOYEE_ID = EMPLOYEE.EMPLOYEEPASSWORD AND STATUS = 'Finished' ORDER BY STATUS ASC";
// this is the command for the query.
OdbcCommand cmdAllDriverRequests = new OdbcCommand(AllDriverRequestsQuery, Conn);
// opens connection.
Conn.Open();
// This reads the information from the database.
OdbcDataReader rdrADR = cmdAllDriverRequests.ExecuteReader();
// read in the information from the database.
while (rdrADR.Read())
{
AllDriverRequestsFD.Rows.Add(
rdrADR.GetInt32(0),
rdrADR.GetString(1),
rdrADR.GetString(2),
rdrADR.GetInt32(3),
rdrADR.GetString(4),
rdrADR.GetDateTime(5),
rdrADR.GetString(6),
rdrADR.GetString(7)
);
}
// set the table to null.
dgvAllDriverRequests.DataSource = null;
// update datagrid view.
dgvAllDriverRequests.Update();
// set the datatable to the datasource.
dgvAllDriverRequests.DataSource = AllDriverRequestsFD;
dgvAllDriverRequests.Columns[0].Visible = false;
// dispose of command.
cmdAllDriverRequests.Dispose();
// close connection.
Conn.Close();
}
static int Main ()
{
if (Environment.GetEnvironmentVariable ("MONO_TESTS_ODBC") == null)
return 0;
OdbcConnection conn = new OdbcConnection (CreateOdbcConnectionString ());
conn.Open ();
OdbcCommand cmd = new OdbcCommand (drop_table, conn);
cmd.ExecuteNonQuery ();
OdbcDataReader reader = null;
try {
cmd = new OdbcCommand (create_table, conn);
cmd.ExecuteNonQuery ();
cmd = new OdbcCommand (insert_data, conn);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
cmd = new OdbcCommand (select_data, conn);
reader = cmd.ExecuteReader ();
Assert.IsTrue (reader.Read (), "#A1");
Assert.AreEqual (2, reader.FieldCount, "#A2");
Assert.AreEqual ("nvarchar", reader.GetDataTypeName (0), "#B1");
Assert.AreEqual ("text1", reader.GetName (0), "#B2");
Assert.AreEqual (new string ('ф', 270), reader.GetValue (0), "#B3");
Assert.AreEqual ("ntext", reader.GetDataTypeName (1), "#C1");
Assert.AreEqual ("text2", reader.GetName (1), "#C2");
Assert.AreEqual (new string ('ф', 270), reader.GetValue (1), "#C3");
reader.Close ();
cmd.Dispose ();
} finally {
if (reader != null)
reader.Close ();
cmd = new OdbcCommand (drop_table, conn);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
conn.Dispose ();
}
return 0;
}
private static DataTable GetIndexCollection(string?[]?restrictions, OdbcConnection connection)
{
OdbcCommand? command = null;
OdbcDataReader?dataReader = null;
DataTable? resultTable = null;
const int nativeRestrictionsCount = 5;
const int indexRestrictionsCount = 4;
const int indexOfTableName = 2;
const int indexOfIndexName = 3;
try
{
command = GetCommand(connection);
object[] allRestrictions = new object[nativeRestrictionsCount];
FillOutRestrictions(indexRestrictionsCount, restrictions, allRestrictions, OdbcMetaDataCollectionNames.Indexes);
if (allRestrictions[indexOfTableName] == null)
{
throw ODBC.GetSchemaRestrictionRequired();
}
allRestrictions[3] = (short)ODBC32.SQL_INDEX.ALL;
allRestrictions[4] = (short)ODBC32.SQL_STATISTICS_RESERVED.ENSURE;
dataReader = command.ExecuteReaderFromSQLMethod(allRestrictions, ODBC32.SQL_API.SQLSTATISTICS);
string?indexName = null;
if (restrictions != null)
{
if (restrictions.Length >= indexOfIndexName + 1)
{
indexName = restrictions[indexOfIndexName];
}
}
resultTable = DataTableFromDataReaderIndex(dataReader,
OdbcMetaDataCollectionNames.Indexes,
indexName);
}
finally
{
dataReader?.Dispose();;
command?.Dispose();;
}
return(resultTable);
}
private void AcceptTrailer(String Lot)
{
string ChangeStatus = "UPDATE LOCATION SET LOCATIONSTATUS = 'Occupied', TRAILER_NO = '" + lblTrNo.Text + "' WHERE LOCATION_ID = '" + Convert.ToString(Lot) + "';";
OdbcConnection Conn = new OdbcConnection("DSN=ora10glocal;uid=TTCS;PWD=Pass1234");
OdbcCommand cmdStatus = new OdbcCommand(ChangeStatus, Conn);
Conn.Open();
// This is the result from the query executing.
OdbcDataReader RDRStatus = cmdStatus.ExecuteReader();
// Cleans up SQL.
cmdStatus.Dispose();
// Closes the connection.
Conn.Close();
LotStatus();
MessageBox.Show("Trailer has been added!");
Close();
}
public void cleanOnlineStatus()
{
try
{
itsCommand = new OdbcCommand("",itsConnection);
if(itsCommand.Connection.State != 0)
{
itsCommand.CommandText = "UPDATE users SET status=\'Z\'";
itsCommand.ExecuteNonQuery();
itsCommand.Dispose();
}
}
catch(OdbcException MyOdbcException) //Catch any ODBC exception ..
{
this.handleException(MyOdbcException);
}
}
private void AllRequests()
{
//shows all the requests in progress
DataTable AllRequests = new DataTable();
AllRequests.Columns.Add("Request No", typeof(Int32));
AllRequests.Columns.Add("Component", typeof(string));
AllRequests.Columns.Add("Quantity", typeof(Int32));
AllRequests.Columns.Add("Status", typeof(string));
AllRequests.Columns.Add("Trailer No", typeof(string));
AllRequests.Columns.Add("PO No", typeof(string));
AllRequests.Columns.Add("Bay", typeof(string));
OdbcConnection Conn = new OdbcConnection("DSN=ora10glocal;uid=TTCS;PWD=Pass1234");
string AllRequestsQuery = "Select Request_No, Component, Quantity, Status, Trailer_NO, PO_NO, END_LOCATION FROM REQUESTS WHERE STATUS = 'Needed' OR STATUS = 'Finished' OR STATUS = 'Attention' OR STATUS = 'Broken Seal' OR STATUS = 'Mechanical Problems' ORDER BY STATUS ASC";
OdbcCommand cmdAllRequests = new OdbcCommand(AllRequestsQuery, Conn);
OdbcDataAdapter ODABay = new OdbcDataAdapter(cmdAllRequests);
Conn.Open();
OdbcDataReader rdrAR = cmdAllRequests.ExecuteReader();
while (rdrAR.Read())
{
AllRequests.Rows.Add(
rdrAR.GetInt32(0),
rdrAR.GetString(1),
rdrAR.GetInt32(2),
rdrAR.GetString(3),
rdrAR.GetString(4),
rdrAR.GetString(5),
rdrAR.GetString(6)
);
}
DGVRequests.DataSource = null;
DGVRequests.Update();
DGVRequests.DataSource = AllRequests;
cmdAllRequests.Dispose();
Conn.Close();
}
static private void ExecuteMultiQueries(OdbcConnection conn, string[] multiQueries)
{
OdbcCommand command = new OdbcCommand();
command.Connection = conn;
foreach (string query in multiQueries)
{
try
{
command.CommandText = query;
command.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
command.Dispose();
}
private DataTable GetDataTypesCollection(string?[]?restrictions, OdbcConnection connection)
{
if (ADP.IsEmptyArray(restrictions) == false)
{
throw ADP.TooManyRestrictions(DbMetaDataCollectionNames.DataTypes);
}
// verify the existence of the table in the data set
DataTable?dataTypesTable = CollectionDataSet.Tables[DbMetaDataCollectionNames.DataTypes];
if (dataTypesTable == null)
{
throw ADP.UnableToBuildCollection(DbMetaDataCollectionNames.DataTypes);
}
// copy the data table it
dataTypesTable = CloneAndFilterCollection(DbMetaDataCollectionNames.DataTypes, null);
OdbcCommand? command = null;
OdbcDataReader?dataReader = null;
object[] allArguments = new object[1];
allArguments[0] = ODBC32.SQL_ALL_TYPES;
try
{
command = GetCommand(connection);
dataReader = command.ExecuteReaderFromSQLMethod(allArguments, ODBC32.SQL_API.SQLGETTYPEINFO);
DataTableFromDataReaderDataTypes(dataTypesTable, dataReader, connection);
}
finally
{
dataReader?.Dispose();;
command?.Dispose();;
}
dataTypesTable.AcceptChanges();
return(dataTypesTable);
}
public System.Data.DataTable LoadExcel(string pPath)
{
string connString = "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;SafeTransactions=0;ReadOnly=1;MaxScanRows=16;Threads=3;MaxBufferSize=2024;UserCommitSync=Yes;FIL=excel 8.0;PageTimeout=5;"; //连接字符串
//简单解释下这个连续字符串，Driver={Driver do Microsoft Excel(*.xls)} 这种连接写法不需要创建一个数据源DSN，DRIVERID表示驱动ID，Excel2003后都使用790，
//FIL表示Excel文件类型，Excel2007用excel 8.0，MaxBufferSize表示缓存大小， 如果你的文件是2010版本的，也许会报错，所以要找到合适版本的参数设置。
connString += "DBQ=" + pPath; //DBQ表示读取Excel的文件名（全路径）
OdbcConnection conn = new OdbcConnection(connString);
OdbcCommand cmd = new OdbcCommand();
cmd.Connection = conn;
//获取Excel中第一个Sheet名称，作为查询时的表名
string sheetName = this.GetExcelSheetName(pPath);
string sql = "select * from [" + sheetName.Replace('.', '#') + "$]";
cmd.CommandText = sql;
OdbcDataAdapter da = new OdbcDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
return ds.Tables[0]; //返回Excel数据中的内容，保存在DataTable中
}
catch (Exception x)
{
ds = null;
throw new Exception("从Excel文件中获取数据时发生错误！可能是Excel版本问题，可以考虑降低版本或者修改连接字符串值");
}
finally
{
cmd.Dispose();
cmd = null;
da.Dispose();
da = null;
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn = null;
}
}
private DataTable GetTablesCollection(string[] restrictions, OdbcConnection connection, bool isTables)
{
OdbcDataReader reader = null;
OdbcCommand command = null;
DataTable table = null;
try
{
string tables;
string str2;
command = this.GetCommand(connection);
string[] allRestrictions = new string[4];
if (isTables)
{
str2 = "TABLE,SYSTEM TABLE";
tables = OdbcMetaDataCollectionNames.Tables;
}
else
{
str2 = "VIEW";
tables = OdbcMetaDataCollectionNames.Views;
}
this.FillOutRestrictions(3, restrictions, allRestrictions, tables);
allRestrictions[3] = str2;
reader = command.ExecuteReaderFromSQLMethod(allRestrictions, ODBC32.SQL_API.SQLTABLES);
table = this.DataTableFromDataReader(reader, tables);
}
finally
{
if (reader != null)
{
reader.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
return(table);
}
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 DataTable GetIndexCollection(string[] restrictions, OdbcConnection connection)
{
OdbcDataReader reader = null;
OdbcCommand command = null;
DataTable table = null;
try
{
command = this.GetCommand(connection);
object[] allRestrictions = new object[5];
this.FillOutRestrictions(4, restrictions, allRestrictions, OdbcMetaDataCollectionNames.Indexes);
if (allRestrictions[2] == null)
{
throw ODBC.GetSchemaRestrictionRequired();
}
allRestrictions[3] = (short)1;
allRestrictions[4] = (short)1;
reader = command.ExecuteReaderFromSQLMethod(allRestrictions, ODBC32.SQL_API.SQLSTATISTICS);
string restrictionIndexName = null;
if ((restrictions != null) && (restrictions.Length >= 4))
{
restrictionIndexName = restrictions[3];
}
table = this.DataTableFromDataReaderIndex(reader, OdbcMetaDataCollectionNames.Indexes, restrictionIndexName);
}
finally
{
if (reader != null)
{
reader.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
return(table);
}
static int Main ()
{
if (Environment.GetEnvironmentVariable ("MONO_TESTS_ODBC") == null)
return 0;
OdbcConnection conn = new OdbcConnection (CreateOdbcConnectionString ());
conn.Open ();
OdbcCommand cmd = new OdbcCommand (drop_table, conn);
Assert.AreEqual (-1, cmd.ExecuteNonQuery (), "#1");
try {
cmd = new OdbcCommand (create_table, conn);
Assert.AreEqual (-1, cmd.ExecuteNonQuery (), "#2");
cmd = new OdbcCommand (insert_data, conn);
Assert.AreEqual (2, cmd.ExecuteNonQuery (), "#3");
using (OdbcDataReader dr = cmd.ExecuteReader ()) {
Assert.IsFalse (dr.NextResult (), "#4");
Assert.AreEqual (2, dr.RecordsAffected, "#5");
}
cmd = new OdbcCommand (modify_data, conn);
using (OdbcDataReader dr = cmd.ExecuteReader ()) {
Assert.AreEqual (6, dr.RecordsAffected, "#6");
}
} finally {
cmd = new OdbcCommand (drop_table, conn);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
conn.Dispose ();
}
return 0;
}
private void btnViewNotes_Click(object sender, EventArgs e)
{
if (DataGridView1.SelectedCells[13].Value.ToString() == false.ToString())
{
MessageBox.Show("No Notes to view", "TeleRad - Client", MessageBoxButtons.OK);
return;
}
Myconnectionclass CN = new Myconnectionclass();
OdbcCommand Cmd;
OdbcDataReader Rs1;
CN.OpenConnection();
Cmd = new System.Data.Odbc.OdbcCommand("SELECT * FROM Reports WHERE StudyUID='" + DataGridView1.SelectedCells[5].Value.ToString() + "'", CN.DBConnection);
Rs1 = Cmd.ExecuteReader();
Rs1.Read();
if (Convert.ToBoolean(Rs1["IsNotes"]) == true)
{
MessageBox.Show("Notes sent: " + Rs1["Notes"].ToString(), "TeleRad - Client", MessageBoxButtons.OK);
}
Rs1.Close();
Cmd.Dispose();
CN.closeconnection();
}
private static DataTable GetProcedureColumnsCollection(string?[]?restrictions, OdbcConnection connection, bool isColumns)
{
OdbcCommand? command = null;
OdbcDataReader?dataReader = null;
DataTable? resultTable = null;
const int procedureColumnsRestrictionsCount = 4;
try
{
command = GetCommand(connection);
string[] allRestrictions = new string[procedureColumnsRestrictionsCount];
FillOutRestrictions(procedureColumnsRestrictionsCount, restrictions, allRestrictions, OdbcMetaDataCollectionNames.Columns);
dataReader = command.ExecuteReaderFromSQLMethod(allRestrictions, ODBC32.SQL_API.SQLPROCEDURECOLUMNS);
string collectionName;
if (isColumns)
{
collectionName = OdbcMetaDataCollectionNames.ProcedureColumns;
}
else
{
collectionName = OdbcMetaDataCollectionNames.ProcedureParameters;
}
resultTable = DataTableFromDataReaderProcedureColumns(dataReader,
collectionName,
isColumns);
}
finally
{
dataReader?.Dispose();;
command?.Dispose();;
}
return(resultTable);
}
private DataTable GetProcedureColumnsCollection(string[] restrictions, OdbcConnection connection, bool isColumns)
{
OdbcDataReader reader = null;
OdbcCommand command = null;
DataTable table = null;
try
{
string procedureColumns;
command = this.GetCommand(connection);
string[] allRestrictions = new string[4];
this.FillOutRestrictions(4, restrictions, allRestrictions, OdbcMetaDataCollectionNames.Columns);
reader = command.ExecuteReaderFromSQLMethod(allRestrictions, ODBC32.SQL_API.SQLPROCEDURECOLUMNS);
if (isColumns)
{
procedureColumns = OdbcMetaDataCollectionNames.ProcedureColumns;
}
else
{
procedureColumns = OdbcMetaDataCollectionNames.ProcedureParameters;
}
table = this.DataTableFromDataReaderProcedureColumns(reader, procedureColumns, isColumns);
}
finally
{
if (reader != null)
{
reader.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
return(table);
}
/// <summary>
/// OdbcDb驱动 执行查询操作 x86平台
/// </summary>
/// <param name="p_strSql">查询sql语句</param>
/// <param name="p_dicDictionary">字典参数</param>
/// <param name="p_strTablename">产生的datatable名称</param>
/// <param name="cmd">cmd</param>
/// <returns>返回表</returns>
static public DataTable OdbcExecuteBySQL(string p_strSql, Dictionary<string, string> p_dicDictionary, string p_strTablename)
{
//SetConnectionString();
if ("" == m_strConnectionString)
{
MessageBox.Show("未设置数据库连接字符串！");
return null;
}
DataTable _dtTable = new DataTable(p_strTablename);
m_odbcConn = new OdbcConnection(m_strConnectionString);
m_odbcCmd = m_odbcConn.CreateCommand();
m_odbcConn.Open();
OdbcChangeSelectCommand(p_strSql, p_dicDictionary, ref m_odbcCmd);
try
{
using (OdbcDataAdapter adapter = new OdbcDataAdapter(m_odbcCmd))
{
adapter.Fill(_dtTable);
}
}
catch (Exception exp)
{
WriteLog(exp, p_strSql);
}
finally
{
m_odbcConn.Dispose();
m_odbcCmd.Dispose();
}
return _dtTable;
}
/// <summary>
/// 采用Odbc方式驱动 执行增，删，改操作 x86平台
/// </summary>
/// <param name="p_strSql">操作的sql</param>
/// <param name="p_dictParam">字典参数</param>
/// <param name="cmd">cmd</param>
/// <returns>返回结果</returns>
static public int OdbcExecuteNonQuery(string p_strSql, Dictionary<string, string> p_dictParam)
{
//SetConnectionString();
int _iExeCount = 0;
m_odbcConn = new OdbcConnection(m_strConnectionString);
m_odbcCmd = m_odbcConn.CreateCommand();
m_odbcConn.Open();
OdbcChangeSelectCommand(p_strSql, p_dictParam, ref m_odbcCmd);
try
{
_iExeCount = m_odbcCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
WriteLog(exp, p_strSql);
_iExeCount = -1;
}
finally
{
m_odbcConn.Dispose();
m_odbcCmd.Dispose();
}
return _iExeCount;
}
static int Main ()
{
if (Environment.GetEnvironmentVariable ("MONO_TESTS_ODBC") == null)
return 0;
OdbcConnection conn = new OdbcConnection (CreateOdbcConnectionString ());
conn.Open ();
OdbcCommand cmd = null;
try {
cmd = new OdbcCommand ("SELECT * FROM nфme", conn);
cmd.ExecuteNonQuery ();
return 1;
} catch (OdbcException ex) {
Assert.AreEqual (typeof (OdbcException), ex.GetType (), "#A1");
Assert.IsNull (ex.InnerException, "#A2");
Assert.AreEqual ("ERROR [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'nфme'.", ex.Message, "#A3");
Assert.AreEqual ("SQLSRV32.DLL", ex.Source, "#A4");
Assert.AreEqual (1, ex.Errors.Count, "#A5");
Assert.AreEqual ("[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'nфme'.", ex.Errors [0].Message, "#B1");
Assert.AreEqual (208, ex.Errors [0].NativeError, "#B2");
Assert.AreEqual ("SQLSRV32.DLL", ex.Errors [0].Source, "#B3");
Assert.AreEqual ("42S02", ex.Errors [0].SQLState, "#B4");
} finally {
if (cmd != null)
cmd.Dispose ();
conn.Dispose ();
}
conn = new OdbcConnection (CreateOdbcConnectionString ());
conn.Open ();
try {
Assert.AreEqual ("SQLSRV32.DLL", conn.Driver, "#C1");
conn.Close ();
Assert.AreEqual (string.Empty, conn.Driver, "#C2");
} finally {
conn.Close ();
}
conn = new OdbcConnection (CreateOdbcConnectionString ());
Assert.AreEqual (string.Empty, conn.Driver, "#D");
conn = new OdbcConnection (CreateOdbcConnectionString ("dфtabasé"));
try {
conn.Open ();
return 2;
} catch (OdbcException ex) {
Assert.AreEqual (typeof (OdbcException), ex.GetType (), "#E1");
Assert.IsNull (ex.InnerException, "#E2");
#if MONO
Assert.AreEqual ("ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database \"dфtabasé\" requested by the login. The login failed.", ex.Message, "#E3");
#else
Assert.AreEqual (string.Format ("{0}{1}{0}", "ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database \"dфtabasé\" requested by the login. The login failed.", Environment.NewLine), ex.Message, "#E3");
#endif
Assert.AreEqual (string.Empty, ex.Source, "#E4");
#if MONO
Assert.AreEqual (1, ex.Errors.Count, "#E5");
#else
Assert.AreEqual (2, ex.Errors.Count, "#E5");
#endif
Assert.AreEqual ("[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database \"dфtabasé\" requested by the login. The login failed.", ex.Errors [0].Message, "#F1");
Assert.AreEqual (4060, ex.Errors [0].NativeError, "#F2");
Assert.AreEqual (string.Empty, ex.Errors [0].Source, "#F3");
Assert.AreEqual ("42000", ex.Errors [0].SQLState, "#F4");
#if !MONO
Assert.AreEqual ("[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database \"dфtabasé\" requested by the login. The login failed.", ex.Errors [1].Message, "#G1");
Assert.AreEqual (4060, ex.Errors [1].NativeError, "#G2");
Assert.AreEqual (string.Empty, ex.Errors [1].Source, "#G3");
Assert.AreEqual ("42000", ex.Errors [1].SQLState, "#G4");
#endif
} finally {
if (cmd != null)
cmd.Dispose ();
conn.Dispose ();
}
return 0;
}
static int Main ()
{
if (Environment.GetEnvironmentVariable ("MONO_TESTS_ODBC") == null)
return 0;
OdbcConnection conn = new OdbcConnection (CreateOdbcConnectionString ());
conn.Open ();
OdbcCommand cmd = new OdbcCommand (drop_table, conn);
cmd.ExecuteNonQuery ();
OdbcDataReader reader = null;
try {
cmd = new OdbcCommand (create_table, conn);
cmd.ExecuteNonQuery ();
cmd = new OdbcCommand (insert_data, conn);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
cmd = new OdbcCommand (select_data, conn);
reader = cmd.ExecuteReader ();
Assert.IsTrue (reader.Read (), "#A1");
Assert.AreEqual (7, reader.FieldCount, "#A2");
byte [] buffer;
long read;
buffer = new byte [8];
read = reader.GetBytes (6, 3, buffer, 2, 4);
Assert.AreEqual (4L, read, "#B1");
Assert.AreEqual ((byte) 0, buffer [0], "#B2");
Assert.AreEqual ((byte) 0, buffer [1], "#B3");
Assert.AreEqual ((byte) 101, buffer [2], "#B4");
Assert.AreEqual ((byte) 32, buffer [3], "#B5");
Assert.AreEqual ((byte) 116, buffer [4], "#B6");
Assert.AreEqual ((byte) 101, buffer [5], "#B7");
Assert.AreEqual ((byte) 0, buffer [6], "#B8");
Assert.AreEqual ((byte) 0, buffer [7], "#B9");
buffer = new byte [8];
buffer [1] = (byte) 5;
buffer [7] = (byte) 4;
read = reader.GetBytes (6, 3, buffer, 2, 4);
Assert.AreEqual (4L, read, "#C1");
Assert.AreEqual ((byte) 0, buffer [0], "#C2");
Assert.AreEqual ((byte) 5, buffer [1], "#C3");
Assert.AreEqual ((byte) 101, buffer [2], "#C4");
Assert.AreEqual ((byte) 32, buffer [3], "#C5");
Assert.AreEqual ((byte) 116, buffer [4], "#C6");
Assert.AreEqual ((byte) 101, buffer [5], "#C7");
Assert.AreEqual ((byte) 0, buffer [6], "#C8");
Assert.AreEqual ((byte) 4, buffer [7], "#C9");
buffer = new byte [8];
read = reader.GetBytes (6, 8, buffer, 2, 6);
Assert.AreEqual (6L, read, "#D1");
Assert.AreEqual ((byte) 0, buffer [0], "#D2");
Assert.AreEqual ((byte) 0, buffer [1], "#D3");
Assert.AreEqual ((byte) 116, buffer [2], "#D4");
Assert.AreEqual ((byte) 32, buffer [3], "#D5");
Assert.AreEqual ((byte) 119, buffer [4], "#D6");
Assert.AreEqual ((byte) 105, buffer [5], "#D7");
Assert.AreEqual ((byte) 116, buffer [6], "#D8");
Assert.AreEqual ((byte) 104, buffer [7], "#D9");
buffer = new byte [8];
read = reader.GetBytes (6, 27, buffer, 1, 26);
Assert.AreEqual (2L, read, "#E1");
Assert.AreEqual ((byte) 0, buffer [0], "#E2");
Assert.AreEqual ((byte) 110, buffer [1], "#E3");
Assert.AreEqual ((byte) 103, buffer [2], "#E4");
Assert.AreEqual ((byte) 0, buffer [3], "#E5");
Assert.AreEqual ((byte) 0, buffer [4], "#E6");
Assert.AreEqual ((byte) 0, buffer [5], "#E7");
Assert.AreEqual ((byte) 0, buffer [6], "#E8");
Assert.AreEqual ((byte) 0, buffer [7], "#E9");
buffer = new byte [0];
read = reader.GetBytes (6, 27, buffer, 0, 0);
Assert.AreEqual (0L, read, "#F");
buffer = new byte [2];
try {
read = reader.GetBytes (6, 20, buffer, 0, 3);
Assert.Fail ("#G1");
} catch (ArgumentException ex) {
// Destination array was not long enough.
// Check destIndex and length, and the array's
// lower bounds
Assert.AreEqual (typeof (ArgumentException), ex.GetType (), "#G2");
Assert.IsNull (ex.InnerException, "#G3");
Assert.IsNotNull (ex.Message, "#G4");
Assert.IsNull (ex.ParamName, "#G5");
}
reader.Close ();
cmd.Dispose ();
} finally {
if (reader != null)
reader.Close ();
cmd = new OdbcCommand (drop_table, conn);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
conn.Dispose ();
}
return 0;
}
public static void DisposeAll(OdbcCommand sqlCommand, OdbcDataReader dbReader)
{
sqlCommand.Dispose();
dbReader.Close();
}
bool isDbPrime(double in_num)
{
bool v_ret=false;
//string strMdbFile="G:\\mathchallenge\\vbs\\Primes.mdb";
//OleDbConnection oleconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strMdbFile);
string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
"SERVER=localhost;" +
"DATABASE=Primes;" +
"UID=primes;" +
"OPTION=3";
OdbcConnection odbcconn = new OdbcConnection(MyConString);
OdbcCommand odbccom;
OdbcDataReader odbcdr;
string strsql="";
strsql = "select PrimeNum from Primes where PrimeNum=" + in_num.ToString();
odbcconn.Open();
odbccom=new OdbcCommand(strsql,odbcconn);
odbcdr = odbccom.ExecuteReader();
v_ret=odbcdr.Read();
odbcdr.Close();
odbccom.Dispose();
odbcconn.Close();
odbcconn.Dispose();
return v_ret;
}
void loadDivisors()
{
string MyConString = "dsn=sqlBulk;uid=bulkuser;";
OdbcConnection odbcconn = new OdbcConnection(MyConString);
OdbcCommand odbccom;
string strsql = "select distinct Number from tblDivisors";
odbcconn.Open();
odbccom=new OdbcCommand(strsql,odbcconn);
OdbcDataReader odbcdr= odbccom.ExecuteReader();
int v_val=0;
while(odbcdr.Read())
{
v_val=(int)odbcdr.GetInt32(0);
if(!htctrl.ContainsKey(v_val))
{
htctrl.Add(v_val,v_val);
}
}
odbcdr.Close();
odbccom.Dispose();
odbcconn.Close();
odbcconn.Dispose();
}
void loadPrimes(int v_min,int v_max)
{
b_PrimesLoaded=true;
//Hashtable v_ret=new Hashtable();
//string strMdbFile="G:\\mathchallenge\\vbs\\Primes.mde";
//OleDbConnection oleconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strMdbFile);
string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
"SERVER=localhost;" +
"DATABASE=Primes;" +
"UID=primes;" +
"OPTION=3";
OdbcConnection odbcconn = new OdbcConnection(MyConString);
OdbcCommand odbccom;
string strsql = "select distinct PrimeNum from Primes where PrimeNum between " + v_min.ToString() + " and " + v_max.ToString() + " order by PrimeNum";
odbcconn.Open();
odbccom=new OdbcCommand(strsql,odbcconn);
OdbcDataReader odbcdr= odbccom.ExecuteReader();
int v_val=0;
while(odbcdr.Read())
{
v_val=(int)odbcdr.GetInt32(0);
if(!htctrl.ContainsKey(v_val))
{
htctrl.Add(v_val,v_val);
}
}
odbcdr.Close();
odbccom.Dispose();
odbcconn.Close();
odbcconn.Dispose();
//return v_ret;
}
void addToPrimeDb(string in_num)
{
//if(!isDbPrime(double.Parse(in_num)))
//{
//string strMdbFile="G:\\mathchallenge\\vbs\\Primes.mde";
//OleDbConnection oleconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strMdbFile);
//OleDbConnection oleconn = new OleDbConnection("dsn=Primes;uid=primes;");
/*string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
"SERVER=localhost;" +
"DATABASE=Primes;" +
"UID=primes;" +
"OPTION=3";
*/
string MyConString = "dsn=sqlBulk;uid=bulkuser;";
OdbcConnection odbcconn = new OdbcConnection(MyConString);
string strsql = "insert into tblPrimes (PrimeNum) values(" + in_num + ")";
odbcconn.Open();
OdbcCommand odbccom=new OdbcCommand(strsql,odbcconn);
odbccom.ExecuteNonQuery();
/*try
{
odbccom.ExecuteNonQuery();
}
catch
{
//writetofileAllPrimes(in_num);
}*/
odbccom.Dispose();
odbcconn.Close();
odbcconn.Dispose();
//}
}
public string getStartDiv()
{
string v_ret="0";
string MyConString = "dsn=sqlBulk;uid=bulkuser;";
OdbcConnection odbcconn = new OdbcConnection(MyConString);
string strsql = "select max(Number) from tblDivisors where number<5000000";
odbcconn.Open();
OdbcCommand odbccom=new OdbcCommand(strsql,odbcconn);
try
{
OdbcDataReader odbcdr = odbccom.ExecuteReader();
if(odbcdr.Read() && !odbcdr.IsDBNull(0))
{
v_ret = odbcdr.GetValue(0).ToString();
}
}
catch
{
}
odbccom.Dispose();
odbcconn.Close();
odbcconn.Dispose();
return v_ret;
}
/// <summary>
/// insert data into corresponding database tables
/// </summary>
public void BatchInsertDataIntoDBtables(DbConnect dbConnect, DataTable dataTable)
{
if (dataTable == null || dataTable.Rows.Count == 0)
{
return;
}
#if DEBUG
logWriter = new StreamWriter("dbInsertErrorLog.txt", true);
#endif
string executeBlock = "";
int rowCount = 1;
try
{
if (!dbConnect.IsConnected())
{
dbConnect.ConnectToDatabase();
}
System.Data.Odbc.OdbcCommand insertCommand = dbConnect.CreateCommand();
InsertionSqlString insertSqlStr = new InsertionSqlString(dataTable.TableName);
executeBlock += "EXECUTE BLOCK AS BEGIN ";
foreach (DataRow dRow in dataTable.Rows)
{
try
{
for (int colI = 0; colI < dataTable.Columns.Count; colI++)
{
string colName = dataTable.Columns[colI].ColumnName;
insertSqlStr.AddKeyValuePair(colName, dRow[colName]);
}
if (rowCount > numOfRowsAllowed ||
executeBlock.Length + insertSqlStr.ToString().Length > queryLength)
{
executeBlock += "END";
insertCommand.CommandText = executeBlock;
insertCommand.ExecuteNonQuery();
// commit the insertion
// insertCommand.CommandText = "Commit";
// insertCommand.ExecuteNonQuery();
executeBlock = "EXECUTE BLOCK AS BEGIN ";
rowCount = 1;
}
executeBlock += (insertSqlStr.ToString() + " ");
rowCount++;
}
catch (Exception ex)
{
// throw new Exception (string.Format ("{0} Insertion Errors: {1}", dataTable.TableName, ex.Message));
#if DEBUG
logWriter.WriteLine(string.Format("{0} Insertion Errors: {1}", dataTable.TableName, ex.Message));
logWriter.WriteLine(insertCommand.CommandText);
#endif
executeBlock = "EXECUTE BLOCK AS BEGIN ";
rowCount = 1;
}
finally
{
insertSqlStr.ClearSqlInsertString();
// executeBlock += "END";
}
}
executeBlock += "END";
insertCommand.CommandText = executeBlock;
insertCommand.ExecuteNonQuery();
// commit the insertion
insertCommand.CommandText = "Commit";
insertCommand.ExecuteNonQuery();
insertCommand.Dispose();
}
catch (Exception ex)
{
Exception thisException = new Exception(executeBlock + " eror: " + ex.Message);
throw thisException;
}
finally
{
#if DEBUG
logWriter.Close();
#endif
}
}
public bool ExportToTxt()
{
bool flag;
try
{
string str = string.Concat("SELECT COUNT(*) from ", this.TableName);
System.Data.Odbc.OdbcCommand sqlCommand = new System.Data.Odbc.OdbcCommand(str, this.conn);
OdbcDataReader sqlDataReader = sqlCommand.ExecuteReader();
sqlDataReader.Read();
int num = Convert.ToInt32(sqlDataReader[0].ToString());
string str1 = sqlDataReader[0].ToString();
sqlDataReader.Close();
if (!(this.SqlCommand != ""))
{
sqlCommand.CommandText = string.Concat("SELECT * from ", this.TableName);
}
else
{
sqlCommand.CommandText = this.SqlCommand;
}
sqlDataReader = sqlCommand.ExecuteReader();
int fieldCount = sqlDataReader.FieldCount;
decimal num1 = new decimal(0);
StreamWriter streamWriter = new StreamWriter(string.Concat(this.filename, ".Txt"));
StringBuilder stringBuilder = new StringBuilder();
while (sqlDataReader.Read())
{
decimal num2 = num1;
num1 = num2++;
this.updateDisplay(num2, num, str1);
for (int i = 0; i < fieldCount; i++)
{
if (!(sqlDataReader[i].GetType().ToString() == "System.Byte[]"))
{
stringBuilder.Append(sqlDataReader[i].ToString());
}
else
{
byte[] item = (byte[])sqlDataReader[i];
stringBuilder.Append(DataWriter2.BytesToHex(item));
}
if ((i >= fieldCount - 1 ? false : i >= 0))
{
stringBuilder.Append("|");
}
}
stringBuilder.Replace(Environment.NewLine, string.Empty);
streamWriter.WriteLine(stringBuilder.ToString());
stringBuilder.Length = 0;
}
streamWriter.Close();
streamWriter.Dispose();
stringBuilder = null;
sqlCommand.Dispose();
sqlDataReader.Dispose();
flag = true;
}
catch (OdbcException odbcException1)
{
OdbcException odbcException = odbcException1;
this.SimpleError = "OdbcError";
this.Error = odbcException.Message;
flag = false;
}
catch (IOException oException1)
{
IOException oException = oException1;
this.SimpleError = "FileError";
this.Error = oException.Message;
flag = false;
}
catch (UnauthorizedAccessException unauthorizedAccessException1)
{
UnauthorizedAccessException unauthorizedAccessException = unauthorizedAccessException1;
this.SimpleError = "PermissionsError";
this.Error = unauthorizedAccessException.Message;
flag = false;
}
return(flag);
}
