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