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); }