/// <summary> /// 构造函数,接收一个OLEDB数据库连接对象OleDbConnection /// </summary> /// <param name="_conn"></param> public OleDbOperHandler(System.Data.OleDb.OleDbConnection _conn) { conn = _conn; dbType = DatabaseType.OleDb; conn.Open(); cmd = conn.CreateCommand(); da = new System.Data.OleDb.OleDbDataAdapter(); }
/// <summary> /// 构造函数,接收一个mdb文件 /// </summary> /// <param name="path"></param> public OleDbOperHandler(string path) { conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path); dbType = DatabaseType.OleDb; conn.Open(); cmd = conn.CreateCommand(); da = new System.Data.OleDb.OleDbDataAdapter(); }
public void BuildAxisOrderBitArray() { var ba = new BitArray(32768); using (var cn = new System.Data.OleDb.OleDbConnection( //$"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={EpsgAccessDatabase};" $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={EpsgAccessDatabase};")) { cn.Open(); var cmd = cn.CreateCommand(); cmd.CommandText = Sql; using (var dr = cmd.ExecuteReader()) { if (dr != null) { while (dr.Read()) { var code = dr.GetInt32(0); if (code > 32767) { continue; } ba[code] = true; } } } } string enc; var buffer = new byte[4096]; ba.CopyTo(buffer, 0); using (var bufferStream = new MemoryStream(buffer)) { using (var compressedStream = new MemoryStream()) { using (var s = new DeflateStream(compressedStream, CompressionMode.Compress)) { bufferStream.CopyTo(s); compressedStream.Flush(); } enc = Convert.ToBase64String(compressedStream.ToArray()); Console.WriteLine("Compressed"); WriteBlocks(enc); } } Console.WriteLine("\nByte array"); WriteBytes(buffer, 20); enc = Convert.ToBase64String(buffer); Console.WriteLine("\nUncompressed"); WriteBlocks(enc); Console.WriteLine("\nByte array"); WriteBytes(buffer, 20); }
public void UpdateExistingRecordWorksWithOleDb() { var obj = CreatePersister(GetMethodSpecificWorkingDirectory()); System.Data.DataRow row = null; AssertDelegateSuccess(() => { row = obj.GetRow(6); }, "Retrieval of a specific record in the access database should not throw an exception"); Assert.AreEqual("Sales Representative", row["Job Title"], "The expected value was not read from the sample access database"); // change their job title row["Job Title"] = "Sales Supervisor"; row["Business Phone"] = "(123)555-6119"; // send the row to the access database to be updated AssertDelegateSuccess(() => { obj.UpdateRow(row); }, "Updating a row should not throw an exception"); // retrieve the row in question using a raw OleDbConnection System.Data.DataTable dt = new System.Data.DataTable(); using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(Util.GetAccessConnectionString(obj.accessFile))) { connection.Open(); using (var cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT * FROM [tblEmployees] WHERE [ID] = @ID"; // create the single parameter that selects the specific row we are interested in var par = cmd.CreateParameter(); par.ParameterName = "@ID"; par.DbType = DbType.Int32; par.Value = 6; cmd.Parameters.Add(par); using (var dr = cmd.ExecuteReader()) { dt.Load(dr); } } } Assert.AreEqual(1, dt.Rows.Count, "The sample data should only have returned a single result"); Assert.AreEqual(row.Table.Columns.Count, dt.Columns.Count, "The column count from the two different retrieval methods did not match exactly"); // iterate over all of the columns owned by each of the data sources var OleDbMethodRow = dt.Rows[0]; foreach (System.Data.DataColumn column in row.Table.Columns) { Assert.AreEqual(row[column.ColumnName], OleDbMethodRow[column.ColumnName], "The column value updated did not match the value read from the access database for column: " + column.ColumnName); } }
/// <summary> /// Returns the value of the "SELECT @@IDENTITY" query. /// </summary> public static long?GetLastGeneratedIdentity(this System.Data.OleDb.OleDbConnection connection) { // Assume MS-Access: using (var cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT @@IDENTITY"; return((long?)cmd.ExecuteScalar()); } }
public void TestAxisOrder() { if (!File.Exists(EpsgAccessDatabase)) { throw new IgnoreException("Epsg Access Database not found"); } var unusual = new HashSet <int>(); using (var cn = new System.Data.OleDb.OleDbConnection( //$"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={EpsgAccessDatabase};" $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={EpsgAccessDatabase};")) { cn.Open(); var cmd = cn.CreateCommand(); cmd.CommandText = Sql; using (var dr = cmd.ExecuteReader()) { if (dr != null) { while (dr.Read()) { var code = dr.GetInt32(0); if (code > 32767) { continue; } unusual.Add(code); } } } } var crsAxisOrderRegistry = new CrsAxisOrderRegistry(); /* * foreach (var code in unusual) * { * CrsIdentifier crs; * if (CrsIdentifier.TryParse("urn:ogc:def:crs:EPSG::" + code, out crs)) * Assert.AreEqual(1, crsAOR[crs][0]); * }*/ for (var code = 1; code < 32768; code++) { CrsIdentifier crs; if (CrsIdentifier.TryParse("urn:ogc:def:crs:EPSG::" + code, out crs)) { var expected = unusual.Contains(code) ? 1 : 0; Assert.AreEqual(expected, crsAxisOrderRegistry[crs][0]); } } }
}// close of method GemerateExceLData public static DataTable GetExcelCSVFileData(FileInfo file, string sheetname) { DataTable dt = new DataTable("dtData"); try { // need to pass relative path after deploying on server string path = file.FullName, connectionString = ""; /* connection string to work with excel file. HDR=Yes - indicates * that the first row contains columnnames, not data. HDR=No - indicates * the opposite. "IMEX=1;" tells the driver to always read "intermixed" * (numbers, dates, strings etc) data columns as text. * Note that this option might affect excel sheet write access negative. */ switch (Path.GetExtension(path).ToUpper()) { case ".XLS": { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + path + "; Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } break; case ".XLSX": { connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + path + "; Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"; } break; case ".CSV": { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + file.DirectoryName + "\"; Extended Properties='text;HDR=Yes;FMT=Delimited(,)';"; sheetname = file.Name; } break; } using (var oledbConn = new System.Data.OleDb.OleDbConnection(connectionString)) { using (var cmd = oledbConn.CreateCommand()) { cmd.CommandText = string.Format("SELECT * FROM [{0}]", sheetname); var adapter = new System.Data.OleDb.OleDbDataAdapter(cmd); adapter.Fill(dt); } } } // need to catch possible exceptions catch (Exception ex) { throw ex; // MessageBox.Show(ex.ToString(), "GSI-Error in sheet reading"); } return(dt); }
public void BuildAxisOrderBitArray() { if (!File.Exists(EpsgAccessDatabase)) throw new IgnoreException("Epsg Access Database not found"); var ba = new BitArray(32768); using (var cn = new System.Data.OleDb.OleDbConnection( string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};", EpsgAccessDatabase))) { cn.Open(); var cmd = cn.CreateCommand(); cmd.CommandText = Sql; using (var dr = cmd.ExecuteReader()) { if (dr != null) while (dr.Read()) { var code = dr.GetInt32(0); if (code > 32767) continue; ba[code] = true; } } } string enc; var buffer = new byte[4096]; ba.CopyTo(buffer, 0); #if NET45 using (var bufferStream = new MemoryStream(buffer)) { using (var compressedStream = new MemoryStream()) { using (var s = new DeflateStream(compressedStream, CompressionMode.Compress)) { bufferStream.CopyTo(s); compressedStream.Flush(); } enc = Convert.ToBase64String(compressedStream.ToArray()); Console.WriteLine("Compressed"); WriteBlocks(enc); } } #endif enc = Convert.ToBase64String(buffer); Console.WriteLine("\nUncompressed"); WriteBlocks(enc); Console.WriteLine("\nByte array"); WriteBytes(buffer, 20); }
public void DownLoadBaseData() { System.Data.OleDb.OleDbCommand cmd = accessConnection.CreateCommand( ); cmd.CommandType = CommandType.Text; DownLoad_Doctor(cmd); DownLoad_MZFP_ITEM(cmd); DownLoad_StatItem(cmd); DownLoad_Base_PatientType(cmd); DownLoad_BASE_PATIENTTYPE_COST(cmd); DownLoad_BASE_ITEM_FAVORABLE(cmd); DownLoad_BASE_ITEMMX_FAVORABLE(cmd); DownLoad_User(cmd); DownLoad_Base_Dept_Property(cmd); DownLoad_Base_Employee_Property(cmd); DownLoad_ShowCard(cmd); DownLoad_TemplateDetail(cmd); DownLoad_MZ_Invoice(cmd); DownLoad_Base_Work_Unit(cmd); cmd.Dispose( ); }
public void InsertIntoExcel(string filename, string tablename, string word) { System.Data.OleDb.OleDbConnection CN = new System.Data.OleDb.OleDbConnection(); CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=Excel 8.0"; CN.Open(); System.Data.OleDb.OleDbCommand CM = CN.CreateCommand(); string Command; //Insert to Table Command = string.Format("INSERT INTO [{0}] VALUES ({1})", tablename, word); CM.CommandText = Command; CM.ExecuteNonQuery(); CN.Close(); }
//OLEDB Method - Write public void CreateExcelWithIndex(string filename, string tablename, string index) { System.Data.OleDb.OleDbConnection CN = new System.Data.OleDb.OleDbConnection(); CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=Excel 8.0"; CN.Open(); System.Data.OleDb.OleDbCommand CM = CN.CreateCommand(); string Command; //Create Table Command = string.Format("CREATE TABLE {0} ({1})", tablename, index); CM.CommandText = Command; CM.ExecuteNonQuery(); Command = ""; }
public static void DBLogOut(UpdateLog[] logDatas) { if (System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"] == null) { return; } string connString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].Trim(); connString = BenQGuru.eMES.Common.Helper.EncryptionHelper.DESDecryption(connString); System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connString); System.Data.OleDb.OleDbTransaction trans = null; try { conn.Open(); System.Data.OleDb.OleDbCommand comm = conn.CreateCommand(); trans = conn.BeginTransaction(); comm.Transaction = trans; foreach (UpdateLog log in logDatas) { string updateSQL = "INSERT INTO TBLUPDATELOG " + " (UPDATELOGID,FILENAME,VERSION,MACHINENAME,MACHINEIP,UPDATETIME,RESULT)" + " values ('" + log.PKID + "'" + " ,'" + log.FileName + "'" + " ,'" + log.Version + "'" + " ,'" + log.MachineName + "'" + " ,'" + log.MachineIP + "'" + " ,'" + log.UpdateTime + "'" + " ,'" + log.Result + "')"; comm.CommandText = updateSQL; comm.CommandType = System.Data.CommandType.Text; comm.ExecuteNonQuery(); } trans.Commit(); } catch (Exception ex) { if (trans != null) { trans.Rollback(); } } finally { conn.Close(); } }
public void TestEpsgCodeUom() { if (!File.Exists(EpsgAccessDatabase)) { throw new IgnoreException("Epsg Access Database not found"); } var connectionString = $"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={EpsgAccessDatabase};"; using (var connection = new System.Data.OleDb.OleDbConnection(connectionString)) { connection.Open(); var cmd = connection.CreateCommand(); cmd.CommandText = SqlEpsgToUom; var uomr = new CrsUnitOfMeasureRegistry(); using (var dr = cmd.ExecuteReader()) { if (dr != null) { while (dr.Read()) { if ((int)dr[0] > 32768) { break; } CrsIdentifier crs; if (CrsIdentifier.TryParse(string.Format("urn:ogc:def:crs:EPSG::{0}", dr.GetInt32(0)), out crs)) { var uom = new UnitOfMeasure(); Assert.DoesNotThrow(() => uom = uomr[crs], "Getting unit of measure failed for {0}", crs); var uomCode = dr.GetInt32(1); if (uomCode == 9001 || uomCode == 1024) { Assert.AreEqual(1d, uom.ToMeter, "Unit of measure ToMeter is not 1d: {0}", crs); } else { Assert.AreNotEqual(1d, uom.ToMeter, "Unit of measure ToMeter should not be 1d: {0}", crs); } } } } } } }
public void TestCreateEpsgToUom() { if (!File.Exists(EpsgAccessDatabase)) throw new IgnoreException("Epsg Access Database not found"); using (var cn = new System.Data.OleDb.OleDbConnection( string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};", EpsgAccessDatabase))) { cn.Open(); var cmd = cn.CreateCommand(); cmd.CommandText = SqlEpsgToUom; var ms = new MemoryStream(); var bw = new BinaryWriter(ms); using (var dr = cmd.ExecuteReader()) { if (dr != null) while (dr.Read()) { if ((int)dr[0] > 32768) break; //Console.WriteLine("{0} -> {1}", dr[0], dr[1]); var uom = Convert.ToInt16(dr[1]); if (uom == 9001) continue; if (Convert.ToInt16(dr[2]) == 9102) uom = 9102; var epsg = Convert.ToUInt16(dr[0]); bw.Write(epsg); bw.Write(uom); } } ms.Seek(0, SeekOrigin.Begin); var br = new BinaryReader(ms); var count = 0; while (ms.Position < ms.Length) { var byteValue = br.ReadByte(); Console.Write("{0}, ", byteValue); count++; if (count % 16 == 0) Console.Write("\n"); } } }
/// <summary> /// Retrieves a row from the access database using the specific ID /// </summary> /// <param name="id">The ID of the row to retrieve</param> /// <returns>A DataRow representing the retrieved data, or null (Nothing) it is not found</returns> public DataRow GetRow(int id) { if (IsDisposed) { throw new ObjectDisposedException("Persister"); } if (noDatabase) { return(null); } DataTable dt = new DataTable("tblEmployees"); using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(Util.GetAccessConnectionString(accessFile))) { connection.Open(); using (var cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT * FROM [tblEmployees] where [ID] = @ID"; // create the single parameter object var par = cmd.CreateParameter(); par.ParameterName = "@ID"; par.DbType = DbType.Int32; par.Value = id; cmd.Parameters.Add(par); using (var dr = cmd.ExecuteReader()) { dt.Load(dr); } } } System.Data.DataRow row = null; // the single row that was returned if exactly one match was found if (dt.Rows.Count == 1) { row = dt.Rows[0]; } return(row); }
public void TestCreateRegistryEntries() { if (!File.Exists(EpsgAccessDatabase)) { Assert.Pass("Epsg Access Database not found"); } using (var cn = new System.Data.OleDb.OleDbConnection( string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};", EpsgAccessDatabase))) { cn.Open(); var cmd = cn.CreateCommand(); cmd.CommandText = SqlLength; using (var dr = cmd.ExecuteReader()) { if (dr != null) { while (dr.Read()) { Console.WriteLine("_registry.Add({0}, new UnitOfMeasure(\"{1}\", {2}d/{3}d));", dr.GetInt32(0), dr.GetString(1), dr.GetDouble(2).ToString(NumberFormatInfo.InvariantInfo), dr.GetDouble(3).ToString(NumberFormatInfo.InvariantInfo)); } } } cmd.CommandText = SqlAngle; using (var dr = cmd.ExecuteReader()) { if (dr != null) { while (dr.Read()) { Console.WriteLine( "_registry.Add({0}, new UnitOfMeasure(\"{1}\", 0.5 * EarthCircumference * {2}d/{3}d));", dr.GetInt32(0), dr.GetString(1), dr.GetDouble(2).ToString(NumberFormatInfo.InvariantInfo), dr.GetDouble(3).ToString(NumberFormatInfo.InvariantInfo)); } } } } }
protected void ReadRPatternExceptions(CalendarRecurrencePattern pPattern, System.Data.OleDb.OleDbConnection pOleDbConn) { if (pPattern == null || pOleDbConn == null) { System.Diagnostics.Debug.Assert(false); return; } System.Data.OleDb.OleDbDataReader oleReader = null; try { int nPatternID = pPattern.Id; String strSQL = "SELECT * FROM CalendarEvents WHERE \r\n"; strSQL += " RecurrenceState = " + Convert.ToString((int)CalendarEventRecurrenceState.xtpCalendarRecurrenceException); strSQL += " AND RecurrencePatternID = " + Convert.ToString(nPatternID); System.Data.OleDb.OleDbCommand oleDbCommand = pOleDbConn.CreateCommand(); oleDbCommand.CommandText = strSQL; oleReader = oleDbCommand.ExecuteReader(); while (oleReader.Read()) { CalendarEvent axEvent = _CreateEventFromData(oleReader, true); if (axEvent != null) { pPattern.SetException(axEvent); } } } catch (System.Exception ex) { System.Diagnostics.Debug.WriteLine("EXCEPTION! SQLDataHelper.ReadRPatternExceptions: " + ex.Message); } //================================================ if (oleReader != null && !oleReader.IsClosed) { oleReader.Close(); } }
/// <summary> /// Persists data to and from the specified excel file /// </summary> /// <param name="excelFilepath">The excel file that should be read into memory</param> public ExcelPersister(string excelFilepath) { if (!System.IO.File.Exists(excelFilepath)) { throw new System.IO.FileNotFoundException(excelFilepath); } // keep a pointer to the excel file _ExcelFile = excelFilepath; var table = new System.Data.DataTable("Sheet1"); try { using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(Util.GetExcelConnectionString(excelFilepath, true))) { using (System.Data.IDbCommand cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT * FROM [Sheet1$]"; connection.Open(); using (System.Data.IDataReader dr = cmd.ExecuteReader()) { table.Load(dr); } } } } catch (Exception ex) { if ((ex.Message.IndexOf("Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine", StringComparison.OrdinalIgnoreCase) >= 0)) { logger.Error("Please install the Microsoft Access Database Engine 2016 Redistributable: https://www.microsoft.com/en-us/download/details.aspx?id=54920", ex); throw new System.Exception("Please install the Microsoft Access Database Engine 2016 Redistributable: https://www.microsoft.com/en-us/download/details.aspx?id=54920"); } logger.Error("Problem reading excel file: " + excelFilepath, ex); throw; } // assign the data table before exiting the constructor this.Data = table; }
private static void CreateTable() { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection( ); conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbPath + ";user id=;password=;"; conn.Open( ); System.Data.OleDb.OleDbCommand cmd = conn.CreateCommand( ); try { Create_BASEDOCTOR(cmd); Create_BASE_MZFP_ITEM(cmd); Create_BASE_PATIENTTYPE(cmd); Create_BASE_STAT_ITEM(cmd); Create_BASE_TEMPLATE_DETAIL(cmd); Create_BASE_EMPLOYEE_PROPERTY(cmd); Create_BASE_DEPT_PROPERTY(cmd); Create_BASE_USER(cmd); Create_BASE_PATIENTTYPE_COST(cmd); Create_BASE_ITEM_FAVORABLE(cmd); Create_BASE_ITEMMX_FAVORABLE(cmd); Create_BASE_WORK_UNIT(cmd); Create_MZ_INVOICE(cmd); Create_VI_MZ_SHOWCARD(cmd); Create_MZ_PRESMASTER(cmd); Create_MZ_PRESORDER(cmd); Create_MZ_COSTMASTER(cmd); Create_MZ_COSTORDER(cmd); Create_MZ_PATLIST(cmd); Create_MZ_ACCOUNT(cmd); } catch (Exception err) { throw err; } finally { conn.Close( ); } cmd.Dispose( ); conn.Dispose( ); }
public void InsertIntoExcel(string filename, string tablename, ArrayList Name, ArrayList Value) { System.Data.OleDb.OleDbConnection CN = new System.Data.OleDb.OleDbConnection(); CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=Excel 8.0"; CN.Open(); System.Data.OleDb.OleDbCommand CM = CN.CreateCommand(); string Command; //Insert to Table int i, length = 0; length = Name.Count; for (i = 0; i < length; i++) { Command = string.Format("INSERT INTO [{0}] VALUES ('{1}', '{2}')", tablename, Name[i].ToString(), Value[i].ToString()); CM.CommandText = Command; CM.ExecuteNonQuery(); } CN.Close(); }
public System.Data.OleDb.OleDbCommand CreateStatement(System.Data.OleDb.OleDbConnection connection) { System.Data.OleDb.OleDbCommand command = connection.CreateCommand(); System.Data.OleDb.OleDbTransaction transaction; if (this[connection] != null) { ConnectionProperties Properties = ((ConnectionProperties)this[connection]); transaction = Properties.Transaction; command.Transaction = transaction; } else { ConnectionProperties TempProp = new ConnectionProperties(); TempProp.AutoCommit = false; TempProp.TransactionLevel = 0; command.Transaction = TempProp.Transaction; Add(connection, TempProp); } return(command); }
private void cboDatabase_DropDown(object sender, System.EventArgs e) { if (TestConnection(out string sMessage)) { try { var command = connectionTest.CreateCommand(); command.CommandText = "select * from master.sys.databases"; var reader = command.ExecuteReader(); Cursor = Cursors.WaitCursor; while (reader.Read()) { try { this.cboDatabase.Items.Add(reader.GetValue(0).ToString()); } catch { } } this.cboDatabase.Sorted = true; if (this.cboDatabase.Items.Count > 0) { this.cboDatabase.SelectedIndex = 0; this.cboDatabase.Enabled = true; } else { this.cboDatabase.Enabled = false; this.cboDatabase.Text = "<No databases found>"; } this.Cursor = Cursors.Default; } catch { Cursor = Cursors.Default; } } }
/// <summary> /// Counts the number of rows present in the access database /// </summary> /// <returns>The number of rows present in the database</returns> public int CountRows() { if (IsDisposed) { throw new ObjectDisposedException("Persister"); } if (noDatabase) { return(0); } using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(Util.GetAccessConnectionString(accessFile))) { connection.Open(); using (var cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT count(*) FROM [tblEmployees]"; return(int.Parse(cmd.ExecuteScalar().ToString())); } } }
//================================================================== private void m_axCalendar_DoRetrieveDayEvents(object sender, AxXtremeCalendarControl._DCalendarControlEvents_DoRetrieveDayEventsEvent e) { if (!IsOpen()) { return; } System.Data.OleDb.OleDbDataReader oleReader = null; try { System.Data.OleDb.OleDbCommand oleDbCommand = m_oleDbConnection.CreateCommand(); oleDbCommand.CommandText = m_DataHelper.MakeRetrieveDayEventsSQL(e.dtDay); oleReader = oleDbCommand.ExecuteReader(); while (oleReader.Read()) { CalendarEvent axEvent = m_DataHelper.CreateEventFromData(oleReader); if (axEvent != null) { e.events.Add(axEvent); } } } catch (System.Exception ex) { System.Diagnostics.Debug.WriteLine("EXCEPTION! providerSQLServer.m_axCalendar_DoRetrieveDayEvents: " + ex.Message); } //================================================ if (oleReader != null && !oleReader.IsClosed) { oleReader.Close(); } }
public void AccessOleDb12ProviderIsRegistereOnLocalMachine() { var directory = GetMethodSpecificWorkingDirectory(); var tmpAccessDatabase = System.IO.Path.Combine(directory, "sample-data.accdb"); CopyEmbeddedResourceBaseToDirectory("PFW.CSIST203.Project3.Tests.Resources.Data", directory); Assert.IsTrue(System.IO.File.Exists(tmpAccessDatabase), "Unable to extract testing access database file from the embedded assembly"); using (var table = new System.Data.DataTable("tblEmployees")) { try { using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(Util.GetAccessConnectionString(tmpAccessDatabase))) { using (System.Data.IDbCommand cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT * FROM [tblEmployees]"; connection.Open(); using (System.Data.IDataReader dr = cmd.ExecuteReader()) { table.Load(dr); } } } } catch (Exception ex) { if ((ex.Message.IndexOf("Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine", StringComparison.OrdinalIgnoreCase) >= 0)) { logger.Error("Please install the Microsoft Access Database Engine 2016 Redistributable: https://www.microsoft.com/en-us/download/details.aspx?id=54920", ex); throw new System.Exception("Please install the Microsoft Access Database Engine 2016 Redistributable: https://www.microsoft.com/en-us/download/details.aspx?id=54920"); } logger.Error("Problem reading access database file: " + tmpAccessDatabase, ex); throw; } } }
private void button1_Click(object sender, EventArgs e) { var cn = new System.Data.OleDb.OleDbConnection( "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=C:\\C#2018\\SampleDB.accdb;"); //DBに接続してトランザクションを開始する cn.Open(); var cmd = cn.CreateCommand(); var tr = cn.BeginTransaction(); //トランザクションを適用し、レコードを追加する cmd.Connection = cn; cmd.Transaction = tr; cmd.CommandText = "INSERT INTO 社員 VALUES(100101, '井ノ上真央', 'B-001')"; cmd.ExecuteNonQuery(); //レコードを追加 //トランザクションを確定して接続を閉じる tr.Commit(); MessageBox.Show("処理を終了しました。", "通知"); cn.Close(); }
private void DosyaKaydet() { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(); try { // Excelden okunan dersin bilgilerine göre dosyanın adının ve yolunun belirlenmesi string pathOfFileToCreate = "./" + ders.DersKodu + "_" + ders.DersAdi + ".xls"; // Aynı isimde dosya varsa silinmesi File.Delete(pathOfFileToCreate); // Office Access Database Engine yardımıyla XLS dosyasının bağlanması conn.ConnectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathOfFileToCreate + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=0\""); conn.Open(); // Bağlantının sağlanması ve işlemler için bağlantıda komut nesnesinin çağırılması var cmd = conn.CreateCommand(); // Formda tutulan her bir sınıf için XLS dosyasında tablo oluşturulması for (int i = 0; i < siniflar.Length - 1; i++) { cmd.CommandText = "CREATE TABLE " + siniflar[i] + " ('Sıra No' INTEGER,'Ögrenci Numarası' NVARCHAR(15),'Öğrenci Adı Soyadı' NVARCHAR(100),'İmza' NVARCHAR(1))"; cmd.ExecuteNonQuery(); } // Formda tutulan Dağıtılan Kişiler Listesinin her bir tabloya Eklenmesi foreach (Tuple <string, int, string, string> tuple in liste) { cmd.CommandText = String.Format("INSERT INTO " + tuple.Item1 + " ('Sıra No','Ögrenci Numarası','Öğrenci Adı Soyadı') VALUES({0},'{1}','{2}')", tuple.Item2, tuple.Item3, tuple.Item4); cmd.ExecuteNonQuery(); // Ekleme işleminin çalıştırılması } } finally { // Bağlantının sonlandırılması if (conn.State == ConnectionState.Open) { conn.Close(); } } }
static void Main(string[] args) { // set up variables with default values string dbName = ConfigurationManager.AppSettings["DefaultDbName"]; string folderName = ConfigurationManager.AppSettings["DefaultFolderName"]; // set up capitalized word list List<string> capitalizedWords = new List<string>(); foreach (var word in ConfigurationManager.AppSettings["CapitalizedWordList"].Split(',')) capitalizedWords.Add(word.Trim()); // prepare for songs List<Song> songs = new List<Song>(); // get argument list if (args.Length > 0) dbName = args[0]; if (args.Length > 1) folderName = args[1]; // ensure database exists if (!File.Exists(dbName)) throw new FileNotFoundException("Could not find database file!", dbName); // ensure folder exists (or create it) if (!Directory.Exists(folderName)) Directory.CreateDirectory(folderName); // build the connection string DbConnectionStringBuilder builder = new DbConnectionStringBuilder(); builder.ConnectionString = ConfigurationManager.ConnectionStrings["Access"].ConnectionString; builder.Add("Data Source", dbName); // get a new connection using (IDbConnection conn = new System.Data.OleDb.OleDbConnection(builder.ConnectionString)) { conn.Open(); IDbCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT SongTitle, Song, Chorus2, Chorus3, Chorus4, Verse1, Verse2, Verse3, Verse4 FROM Songs"; using(IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { Song currSong; while (reader.Read()) { currSong = new Song( System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(Regex.Replace(reader.GetString(0).ToLower(), @"\s+", " ")) ); for (int i = 1; i < reader.FieldCount; i++) if ((!reader.IsDBNull(i)) && (!string.IsNullOrWhiteSpace(reader.GetString(i)))) currSong.Parts.Add(x[reader.GetName(i)], Normalize(reader.GetString(i), capitalizedWords)); songs.Add(currSong); } } } foreach (var song in songs) song.Serialize(File.Open(Path.Combine(folderName, MakeValidFileName(song.Name + ".txt")), FileMode.Truncate, FileAccess.Write, FileShare.Write)); }
// Aufruf mit übergebener Termin-ID --> Vorhandenen Termin bearbeiten public Terminbearbeitung(int termID) { InitializeComponent(); if(termID == 0) { MessageBox.Show("Fehlerhafte Termin-ID wurde übergeben!"); } else { terminID = termID; // Kontaktliste füllen FillListKontakte(); string verbindungsstring = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\Terminverwaltung.accdb"; System.Data.OleDb.OleDbConnection dBVerbindung = null; System.Data.OleDb.OleDbCommand befehl = null; System.Data.OleDb.OleDbDataReader datenleser = null; bool offen = false; try { dBVerbindung = new System.Data.OleDb.OleDbConnection(verbindungsstring); dBVerbindung.Open(); offen = true; befehl = dBVerbindung.CreateCommand(); befehl.CommandText = "select Termin_ID, Titel, Datum, Uhrzeit, Beschreibung, Kontakt \n" + "from Termine \n" + "where Termin_ID = " + terminID; datenleser = befehl.ExecuteReader(); // Liefert folgende Spalten zurück: // 0 - Termin_ID; 1 - Titel; 2 - Datum; 3 - Uhrzeit; 4 - Beschreibung // 5 - Kontakt_ID while (datenleser.Read()) { textBoxTitel.Text = datenleser.GetString(1); textBoxDatum.Text = datenleser.GetDateTime(2).ToString("dd.MM.yyyy"); textBoxUhrzeit.Text = datenleser.GetDateTime(3).ToString("HH:mm"); textBoxBeschreibung.Text = datenleser.GetString(4); if (datenleser.GetValue(5).ToString() == "") // Auf letztes Item = kein Kontakt stellen { listBoxKontakt.SelectedIndex = listBoxKontakt.Items.Count - 1; } else { MessageBox.Show(datenleser.GetInt32(5).ToString()); listBoxKontakt.SelectedValue = datenleser.GetInt32(5); } } } catch (Exception ausnahme) { MessageBox.Show("Datenbankfehler: " + ausnahme.Message); } finally { if (offen == true) { dBVerbindung.Close(); } } } }
private static void Spike_OleDbConnection_With_ConnectionString(string[] args) { var cs = System.Configuration.ConfigurationManager.ConnectionStrings["NespeDataContext"]; using (var cnn = new System.Data.OleDb.OleDbConnection(cs.ConnectionString)) { using (var cmd = cnn.CreateCommand()) { cmd.CommandText = "select 1+1"; cnn.Open(); var v = cmd.ExecuteScalar(); Console.WriteLine(v); } } }
public void TestCreateRegistryEntries() { if (!File.Exists(EpsgAccessDatabase)) throw new IgnoreException("Epsg Access Database not found"); using (var cn = new System.Data.OleDb.OleDbConnection( string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};", EpsgAccessDatabase))) { cn.Open(); var cmd = cn.CreateCommand(); cmd.CommandText = SqlLength; using (var dr = cmd.ExecuteReader()) { if (dr != null) while (dr.Read()) { Console.WriteLine("_registry.Add({0}, new UnitOfMeasure(\"{1}\", {2}d/{3}d));", dr.GetInt32(0), dr.GetString(1), dr.GetDouble(2).ToString(NumberFormatInfo.InvariantInfo), dr.GetDouble(3).ToString(NumberFormatInfo.InvariantInfo)); } } cmd.CommandText = SqlAngle; using (var dr = cmd.ExecuteReader()) { if (dr != null) while (dr.Read()) { Console.WriteLine( "_registry.Add({0}, new UnitOfMeasure(\"{1}\", 0.5 * EarthCircumference * {2}d/{3}d));", dr.GetInt32(0), dr.GetString(1), dr.GetDouble(2).ToString(NumberFormatInfo.InvariantInfo), dr.GetDouble(3).ToString(NumberFormatInfo.InvariantInfo)); } } } }
public void TestEpsgCodeUom() { if (!File.Exists(EpsgAccessDatabase)) throw new IgnoreException("Epsg Access Database not found"); using (var cn = new System.Data.OleDb.OleDbConnection( string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};", EpsgAccessDatabase))) { cn.Open(); var cmd = cn.CreateCommand(); cmd.CommandText = SqlEpsgToUom; var uomr = new CrsUnitOfMeasureRegistry(); using (var dr = cmd.ExecuteReader()) { if (dr != null) while (dr.Read()) { if ((int)dr[0] > 32768) break; CrsIdentifier crs; if (CrsIdentifier.TryParse(string.Format("urn:ogc:def:crs:EPSG::{0}", dr.GetInt32(0)), out crs)) { var uom = new UnitOfMeasure(); Assert.DoesNotThrow(() => uom = uomr[crs], "Getting unit of measure failed for {0}", crs); var uomCode = dr.GetInt32(1); if (uomCode == 9001 || uomCode == 1024) Assert.AreEqual(1d, uom.ToMeter, "Unit of measure ToMeter is not 1d: {0}", crs); else Assert.AreNotEqual(1d, uom.ToMeter, "Unit of measure ToMeter should not be 1d: {0}", crs); } } } } }
public static void ExportDataTableToExcel(string path, params DataTable[] dts) { path = Path.ChangeExtension(path, ".xls"); if (File.Exists(path)) { Console.WriteLine("文件已存在"); return; } var connectionString = ConnectionString(path, "HDR=NO;IMEX=2"); if (string.IsNullOrEmpty(connectionString)) { return; } //实例化一个Oledbconnection类(实现了IDisposable,要using) using (var connection = new System.Data.OleDb.OleDbConnection(connectionString)) { Console.WriteLine(path); connection.Open(); using (var sql = connection.CreateCommand()) { foreach (DataTable dt in dts) { var tableName = dts.Length <= 1 ? "Sheet1" : dt.TableName; //写入标题 var header = new List <string>(); for (int i = 0; i < dt.Columns.Count; i++) { header.Add(string.Format("[{0}] VarChar", dt.Columns[i].ColumnName)); } sql.CommandText = string.Format("CREATE TABLE {0} ({1})", tableName, string.Join(",", header.ToArray())); sql.ExecuteNonQuery(); //数据 header.Clear(); for (int i = 0; i < dt.Columns.Count; i++) { header.Add(dt.Columns[i].ColumnName); } for (int i = 0; i < dt.Rows.Count; i++) { var values = new List <string>(); for (int j = 0; j < dt.Columns.Count; j++) { values.Add(string.Format("'{0}'", dt.Rows[i][j])); } sql.CommandText = string.Format("insert into {0} ({1}) values ({2})", tableName, string.Join(",", header.ToArray()), string.Join(",", values.ToArray())); sql.ExecuteNonQuery(); } } //sql.CommandText = "CREATE TABLE CustomerInfo ([CustomerID] VarChar,[Customer] VarChar)"; //sql.ExecuteNonQuery(); //sql.CommandText = "insert into CustomerInfo(CustomerID,Customer)values('DJ001','点击科技')"; //sql.ExecuteNonQuery(); } } }
// Selektiert die Terminbeschreibung zu einer übergebenen // Termin-ID private string SelectTermBeschreibung(int termID) { string verbindungsstring = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\Terminverwaltung.accdb"; System.Data.OleDb.OleDbConnection dBVerbindung = null; System.Data.OleDb.OleDbCommand befehl = null; System.Data.OleDb.OleDbDataReader datenleser = null; bool offen = false; try { dBVerbindung = new System.Data.OleDb.OleDbConnection(verbindungsstring); dBVerbindung.Open(); offen = true; befehl = dBVerbindung.CreateCommand(); befehl.CommandText = "select Beschreibung from Termine where Termin_ID = " + termID; datenleser = befehl.ExecuteReader(); // Liefert folgende Spalten zurück: // 0 - Beschreibung while (datenleser.Read()) { return datenleser.GetString(0); } } catch (Exception ausnahme) { MessageBox.Show("Datenbankfehler: " + ausnahme.Message); } finally { if (offen == true) dBVerbindung.Close(); } return ""; }
public void TermineAbfragen() { string verbindungsstring = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\Terminverwaltung.accdb"; System.Data.OleDb.OleDbConnection dBVerbindung = null; System.Data.OleDb.OleDbCommand befehl = null; System.Data.OleDb.OleDbDataReader datenleser = null; bool offen = false; try { dBVerbindung = new System.Data.OleDb.OleDbConnection(verbindungsstring); dBVerbindung.Open(); offen = true; befehl = dBVerbindung.CreateCommand(); befehl.CommandText = "select * from Termine"; datenleser = befehl.ExecuteReader(); // Liefert folgende Spalten zurück: // 0 - Termin_ID; 1 - Titel; 2 - Datum; 3 - Uhrzeit; 4 - Beschreibung // 5 - Kontakt while (datenleser.Read()) { // Feststellen, ob der Termin in der aktuellen Auswahl angezeigt // werden muss if (datenleser.GetDateTime(2) >= StartOfWeek(WeekPicker.Value, DayOfWeek.Monday) && datenleser.GetDateTime(2) <= EndOfWeek(WeekPicker.Value, DayOfWeek.Sunday)) { // Reihe = Stundenzahl - 1 (weil Index bei 0 beginnt) - 5 (weil die ersten 5 // Stunden des Tages nicht angezeiggt werden int row = Int32.Parse(datenleser.GetDateTime(3).ToString("HH")) - 6; int col = (int)datenleser.GetDateTime(2).DayOfWeek; TerminGridView.Rows[row].Cells[col].Value = datenleser.GetString(1); // Im Hintergrund wird ein zweidimensionales Int-Array mit aufgebaut, // um die IDs der Termine passend zur Zeile und Spalte der TerminGridView // zu verwalten termIDs[row, col] = datenleser.GetInt32(0); } } } catch (Exception ausnahme) { MessageBox.Show("Datenbankfehler: " + ausnahme.Message); } finally { if (offen == true) dBVerbindung.Close(); } }
private string SelKontaktName(int kontakt_id) { if (kontakt_id == 0) { return ""; } else { string verbindungsstring = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\Terminverwaltung.accdb"; System.Data.OleDb.OleDbConnection dBVerbindung = null; System.Data.OleDb.OleDbCommand befehl = null; System.Data.OleDb.OleDbDataReader datenleser = null; bool offen = false; try { dBVerbindung = new System.Data.OleDb.OleDbConnection(verbindungsstring); dBVerbindung.Open(); offen = true; befehl = dBVerbindung.CreateCommand(); befehl.CommandText = "select Name & ', ' & Vorname as Kname \n" + "from Kontakte \n" + "where Kontakt_ID = " + kontakt_id; datenleser = befehl.ExecuteReader(); // Liefert folgende Spalten zurück: // 0 - Kontaktname while (datenleser.Read()) { if (datenleser.GetValue(0) == null) return "null"; else { MessageBox.Show(datenleser.GetString(0)); return datenleser.GetString(0).ToString(); } } } catch (Exception ausnahme) { MessageBox.Show("Datenbankfehler: " + ausnahme.Message); return ""; } finally { if (offen == true) { dBVerbindung.Close(); } } MessageBox.Show("4"); return ""; } }
public void insertall(string path) { try { System.Data.OleDb.OleDbConnection yaalconnection; System.Data.DataSet DtSet; System.Data.OleDb.OleDbDataAdapter MyCommand; // public static string path = @"C:\src\RedirectApplication\RedirectApplication\301s.xlsx"; // public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;"; //yaalconnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='Z:\\33BRKPR0174G1ZS_032019_R2A.xlsx';Extended Properties=Excel 12.0;"); yaalconnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;"); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [B2B$]", yaalconnection); MyCommand.TableMappings.Add("Table", "TestTable"); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); dataGridView1.DataSource = DtSet.Tables[0]; yaalconnection.Close(); System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(" Provider = Microsoft.ACE.OLEDB.12.0; Data Source =D:\\json\\test.accdb"); System.Data.OleDb.OleDbCommand comm = con.CreateCommand(); con.Open(); String gstno; string mystring; mystring = ""; string res; res = ""; for (int i = 0; i < dataGridView1.RowCount; i++) { for (int j = 0; j < dataGridView1.ColumnCount; j++) { if (dataGridView1.Rows[i].Cells[j].Value == DBNull.Value) { dataGridView1.Rows[i].Cells[j].Value = 0; } } } dataGridView1.Update(); int strlen; for (int i = 0; i < dataGridView1.Rows.Count; i++) { if (dataGridView1.Rows[i].Cells[2].Value != null && dataGridView1.Rows[i].Cells[2].ToString() != String.Empty) { res = ""; string value = (string)dataGridView1.Rows[i].Cells[2].Value; mystring = value; strlen = mystring.Length; if (strlen > 5) { res = mystring.Substring(strlen - 5, 5); } } string path1 = ""; string StrQuery; if (res == "Total") { double amount = (double)(dataGridView1.Rows[i].Cells[5].Value); if (amount > 0) { //33BKMPP8726K1ZJ_012019_R2A.xlsx path1 = path.Substring(path.Length - 15, 6); StrQuery = @"INSERT INTO test(gstno,monthname,invno,invdt,amount,TaxAmt,camount,samount,name) VALUES('" + dataGridView1.Rows[i].Cells[0].Value + "', '" + path1 + "','" + dataGridView1.Rows[i].Cells[2].Value + "','" + dataGridView1.Rows[i].Cells[4].Value + "'," + dataGridView1.Rows[i].Cells[5].Value + "," + dataGridView1.Rows[i].Cells[9].Value + ", " + dataGridView1.Rows[i].Cells[11].Value + ", " + dataGridView1.Rows[i].Cells[12].Value + ", '" + dataGridView1.Rows[i].Cells[1].Value + "'); "; //string StrQuery = "INSERT INTO tableName VALUES ('" + dataGridView1.Rows[i].Cells[0].Value + "',' " + dataGridView1.Rows[i].Cells[1].Value + "', '" + dataGridView1.Rows[i].Cells[2].Value + "', '" + dataGridView1.Rows[i].Cells[3].Value + "',' " + dataGridView1.Rows[i].Cells[4].Value + "')"; // 11,12,1 // StrQuery = @"INSERT INTO test VALUES (" + dataGridView1.Rows[i].Cells["ColumnName"].Text + ", " // + dataGridView1.Rows[i].Cells["ColumnName"].Text + ");"; //VALUES ('" + dataGridView1.Rows[i].Cells[0].Value +"'," + dataGridView1.Rows[i].Cells[5].Value +",'" + dataGridView1.Rows[i].Cells[1].Value +"');"; comm.Connection = con; comm.CommandText = StrQuery; comm.ExecuteNonQuery(); } } } // cmd.CommandText = "Insert into test(test)Values(1)"; //comm.Connection = con; //comm.ExecuteNonQuery(); MessageBox.Show("Record Submitted", "Congrats"); con.Close(); MessageBox.Show("Completed"); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void Button4_Click(object sender, EventArgs e) { try { System.Data.OleDb.OleDbConnection yaalconnection; System.Data.DataSet DtSet; System.Data.OleDb.OleDbDataAdapter MyCommand; // public static string path = @"C:\src\RedirectApplication\RedirectApplication\301s.xlsx"; // public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;"; yaalconnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='Z:\\33BRKPR0174G1ZS_032019_R2A.xlsx';Extended Properties=Excel 12.0;"); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [B2B$]", yaalconnection); // Provider = Microsoft.Jet.OLEDB.4.0; Data Source = E:\33BRKPR0174G1ZS_032019_R2A.xlsx //Provider = Microsoft.ACE.OLEDB.12.0; Data Source = D:\json\test.accdb MyCommand.TableMappings.Add("Table", "TestTable"); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); dataGridView1.DataSource = DtSet.Tables[0]; yaalconnection.Close(); System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(" Provider = Microsoft.ACE.OLEDB.12.0; Data Source =D:\\json\\test.accdb"); string StrQuery; System.Data.OleDb.OleDbCommand comm = con.CreateCommand(); con.Open(); String gstno; string mystring; mystring = ""; string res; res = ""; for (int i = 0; i < dataGridView1.RowCount; i++) { for (int j = 0; j < dataGridView1.ColumnCount; j++) { if (dataGridView1.Rows[i].Cells[j].Value == DBNull.Value) { dataGridView1.Rows[i].Cells[j].Value = 0; } } } dataGridView1.Update(); for (int i = 0; i < dataGridView1.Rows.Count; i++) { if (dataGridView1.Rows[i].Cells[2].Value != null && dataGridView1.Rows[i].Cells[2].ToString() != String.Empty) { string value = (string)dataGridView1.Rows[i].Cells[2].Value; mystring = value; res = mystring.GetLast(5); } if (res == "Total") { StrQuery = @"INSERT INTO test(gstno,amount) VALUES ('" + dataGridView1.Rows[i].Cells[0].Value + "'," + dataGridView1.Rows[i].Cells[5].Value + ");"; //string StrQuery = "INSERT INTO tableName VALUES ('" + dataGridView1.Rows[i].Cells[0].Value + "',' " + dataGridView1.Rows[i].Cells[1].Value + "', '" + dataGridView1.Rows[i].Cells[2].Value + "', '" + dataGridView1.Rows[i].Cells[3].Value + "',' " + dataGridView1.Rows[i].Cells[4].Value + "')"; // StrQuery = @"INSERT INTO test VALUES (" + dataGridView1.Rows[i].Cells["ColumnName"].Text + ", " // + dataGridView1.Rows[i].Cells["ColumnName"].Text + ");"; comm.CommandText = StrQuery; comm.ExecuteNonQuery(); } } // cmd.CommandText = "Insert into test(test)Values(1)"; comm.Connection = con; comm.ExecuteNonQuery(); MessageBox.Show("Record Submitted", "Congrats"); con.Close(); MessageBox.Show("Completed"); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public void PicksUpExternalDatabaseChanges() { var obj = CreatePersister(GetMethodSpecificWorkingDirectory()); DataRow row = null; AssertDelegateSuccess(() => { row = obj.GetRow(2); }, "Retrieval of a specific record in the access database should not throw an exception"); // ensure the state of the database Assert.AreEqual(2, row["ID"], "The database value in the sample file did not match the expected result"); Assert.AreEqual("Northwind Traders", row["Company"], "The database value in the sample file did not match the expected result"); Assert.AreEqual("Cencini", row["Last Name"], "The database value in the sample file did not match the expected result"); Assert.AreEqual("Andrew", row["First Name"], "The database value in the sample file did not match the expected result"); Assert.AreEqual("*****@*****.**", row["E-mail Address"], "The database value in the sample file did not match the expected result"); Assert.AreEqual("Vice President, Sales", row["Job Title"], "The database value in the sample file did not match the expected result"); Assert.AreEqual("(123)555-0102", row["Business Phone"], "The database value in the sample file did not match the expected result"); // change a few values in the DataRow before persisting these changes back to the database row["Company"] = "Acme Products, LLC"; row["Business Phone"] = "(260)123-4567"; // perform a manual modification to the record above that does not utilize the UpdateRow() method using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(Util.GetAccessConnectionString(obj.accessFile))) { connection.Open(); using (var cmd = connection.CreateCommand()) { cmd.CommandText = "UPDATE [tblEmployees] SET [Company] = @Company,[Last Name] = @LastName,[First Name] = @FirstName,[E-mail Address] = @EmailAddress,[Job Title] = @JobTitle,[Business Phone] = @BusinessPhone WHERE [ID] = @ID"; // company parameter var par = cmd.CreateParameter(); par.ParameterName = "@Company"; par.DbType = DbType.String; par.Value = row["Company"]; cmd.Parameters.Add(par); // last name par = cmd.CreateParameter(); par.ParameterName = "@LastName"; par.DbType = DbType.String; par.Value = row["Last Name"]; cmd.Parameters.Add(par); // first name par = cmd.CreateParameter(); par.ParameterName = "@FirstName"; par.DbType = DbType.String; par.Value = row["First Name"]; cmd.Parameters.Add(par); // e-mail address par = cmd.CreateParameter(); par.ParameterName = "@EmailAddress"; par.DbType = DbType.String; par.Value = row["E-mail Address"]; cmd.Parameters.Add(par); // job title par = cmd.CreateParameter(); par.ParameterName = "@JobTitle"; par.DbType = DbType.String; par.Value = row["Job Title"]; cmd.Parameters.Add(par); // business phone par = cmd.CreateParameter(); par.ParameterName = "@BusinessPhone"; par.DbType = DbType.String; par.Value = row["Business Phone"]; cmd.Parameters.Add(par); par = cmd.CreateParameter(); par.ParameterName = "@ID"; par.DbType = DbType.Int32; par.Value = row["ID"]; cmd.Parameters.Add(par); var results = cmd.ExecuteNonQuery(); Assert.AreEqual(1, results, "The number of expected results from the database update was not returned"); } } // Read the modified row using the persister AssertDelegateSuccess(() => { row = obj.GetRow(2); }, "Retrieval of a specific record in the access database should not throw an exception"); Assert.AreEqual("Acme Products, LLC", row["Company"], "The database value in the sample file did not match the expected result after the external modification"); Assert.AreEqual("(260)123-4567", row["Business Phone"], "The database value in the sample file did not match the expected result after the external modification"); }
private void buttonSpeichern_Click(object sender, EventArgs e) { // Über die Termin-ID kann geprüft werden, ob ein bestehender Datensatz // bearbeitet werden soll oder ein neuer eingefügt werden soll if (terminID == 0) { if (textBoxTitel.Text != null && textBoxDatum != null && textBoxUhrzeit != null) { // Neuen Termin anlegen string verbindungsstring = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\Terminverwaltung.accdb"; System.Data.OleDb.OleDbConnection dBVerbindung = null; System.Data.OleDb.OleDbCommand befehl = null; bool offen = false; try { dBVerbindung = new System.Data.OleDb.OleDbConnection(verbindungsstring); dBVerbindung.Open(); offen = true; // Je nach Kontaktauswahl "null" oder den Wert beim Insert angeben string kontakt; if (listBoxKontakt.SelectedValue.ToString() == "") kontakt = "null"; else kontakt = listBoxKontakt.SelectedValue.ToString(); befehl = dBVerbindung.CreateCommand(); befehl.CommandText = "insert into Termine (Titel, Datum, Uhrzeit, Beschreibung, Kontakt) values ('" + textBoxTitel.Text + "', '" + textBoxDatum.Text + "', '" + textBoxUhrzeit.Text + "', '" + textBoxBeschreibung.Text + "', " + kontakt + ")"; int anzahl = befehl.ExecuteNonQuery(); } catch (Exception ausnahme) { MessageBox.Show("Datenbankfehler: " + ausnahme.Message); } finally { if (offen == true) dBVerbindung.Close(); } } else { MessageBox.Show("Bitte geben Sie mindestens einen Titel, das Datum und die Uhrzeit an!"); } } else { // Bestehenden Termin updaten if (textBoxTitel.Text != null && textBoxDatum != null && textBoxUhrzeit != null) { // Neuen Termin anlegen string verbindungsstring = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\Terminverwaltung.accdb"; System.Data.OleDb.OleDbConnection dBVerbindung = null; System.Data.OleDb.OleDbCommand befehl = null; bool offen = false; try { dBVerbindung = new System.Data.OleDb.OleDbConnection(verbindungsstring); dBVerbindung.Open(); offen = true; // Je nach Kontaktauswahl "null" oder den Wert beim Insert angeben string kontakt; if (listBoxKontakt.SelectedValue.ToString() == "") kontakt = "null"; else kontakt = listBoxKontakt.SelectedValue.ToString(); befehl = dBVerbindung.CreateCommand(); befehl.CommandText = "update Termine \n" + "set Titel = '" + textBoxTitel.Text + "', \n" + "Datum = '" + textBoxDatum.Text + "', \n" + "Uhrzeit = '" + textBoxUhrzeit.Text + "', \n" + "Beschreibung = '" + textBoxBeschreibung.Text + "', \n" + "Kontakt = " + kontakt + " \n" + "where Termin_ID = " + terminID; int anzahl = befehl.ExecuteNonQuery(); } catch (Exception ausnahme) { MessageBox.Show("Datenbankfehler: " + ausnahme.Message); } finally { if (offen == true) dBVerbindung.Close(); } } else { MessageBox.Show("Bitte geben Sie mindestens einen Titel, das Datum und die Uhrzeit an!"); } } }
static void Main(string[] args) { // set up variables with default values string dbName = ConfigurationManager.AppSettings["DefaultDbName"]; string folderName = ConfigurationManager.AppSettings["DefaultFolderName"]; // set up capitalized word list List <string> capitalizedWords = new List <string>(); foreach (var word in ConfigurationManager.AppSettings["CapitalizedWordList"].Split(',')) { capitalizedWords.Add(word.Trim()); } // prepare for songs List <Song> songs = new List <Song>(); // get argument list if (args.Length > 0) { dbName = args[0]; } if (args.Length > 1) { folderName = args[1]; } // ensure database exists if (!File.Exists(dbName)) { throw new FileNotFoundException("Could not find database file!", dbName); } // ensure folder exists (or create it) if (!Directory.Exists(folderName)) { Directory.CreateDirectory(folderName); } // build the connection string DbConnectionStringBuilder builder = new DbConnectionStringBuilder(); builder.ConnectionString = ConfigurationManager.ConnectionStrings["Access"].ConnectionString; builder.Add("Data Source", dbName); // get a new connection using (IDbConnection conn = new System.Data.OleDb.OleDbConnection(builder.ConnectionString)) { conn.Open(); IDbCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT SongTitle, Song, Chorus2, Chorus3, Chorus4, Verse1, Verse2, Verse3, Verse4 FROM Songs"; using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { Song currSong; while (reader.Read()) { currSong = new Song( System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(Regex.Replace(reader.GetString(0).ToLower(), @"\s+", " ")) ); for (int i = 1; i < reader.FieldCount; i++) { if ((!reader.IsDBNull(i)) && (!string.IsNullOrWhiteSpace(reader.GetString(i)))) { currSong.Parts.Add(x[reader.GetName(i)], Normalize(reader.GetString(i), capitalizedWords)); } } songs.Add(currSong); } } } foreach (var song in songs) { song.Serialize(File.Open(Path.Combine(folderName, MakeValidFileName(song.Name + ".txt")), FileMode.Truncate, FileAccess.Write, FileShare.Write)); } }
public void TestAxisOrder() { if (!File.Exists(EpsgAccessDatabase)) throw new IgnoreException("Epsg Access Database not found"); var unusual = new HashSet<int>(); using (var cn = new System.Data.OleDb.OleDbConnection( string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};", EpsgAccessDatabase))) { cn.Open(); var cmd = cn.CreateCommand(); cmd.CommandText = Sql; using (var dr = cmd.ExecuteReader()) { if (dr != null) while (dr.Read()) { var code = dr.GetInt32(0); if (code > 32767) continue; unusual.Add(code); } } } var crsAOR = new CrsAxisOrderRegistry(); /* foreach (var code in unusual) { CrsIdentifier crs; if (CrsIdentifier.TryParse("urn:ogc:def:crs:EPSG::" + code, out crs)) Assert.AreEqual(1, crsAOR[crs][0]); }*/ for (var code = 1; code < 32768; code++) { CrsIdentifier crs; if (CrsIdentifier.TryParse("urn:ogc:def:crs:EPSG::" + code, out crs)) { var expected = unusual.Contains(code) ? 1 : 0; Assert.AreEqual(expected, crsAOR[crs][0]); } } }
// List-Item füllen private void FillListKontakte() { // Füllen der Listbox string verbindungsstring = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\Terminverwaltung.accdb"; System.Data.OleDb.OleDbConnection dBVerbindung = null; System.Data.OleDb.OleDbCommand befehl = null; System.Data.OleDb.OleDbDataReader datenleser = null; bool offen = false; try { dBVerbindung = new System.Data.OleDb.OleDbConnection(verbindungsstring); dBVerbindung.Open(); offen = true; befehl = dBVerbindung.CreateCommand(); befehl.CommandText = "select ko.Kontakt_ID, ko.Name & ', ' & ko.Vorname as KName \n" + "from Kontakte ko \n" + "order by ko.Name, ko.Vorname"; datenleser = befehl.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(datenleser); dt.Rows.Add(); listBoxKontakt.DataSource = dt; listBoxKontakt.DisplayMember = "KName"; listBoxKontakt.ValueMember = "Kontakt_ID"; } catch (Exception ausnahme) { MessageBox.Show("Datenbankfehler: " + ausnahme.Message); } finally { if (offen == true) dBVerbindung.Close(); } }