public static string GenerateConnectionString(ExcelFile excel)
{
if (excel == null)
{
throw new ExcelFileException($"Ссылна на объект {nameof(excel)} равна NULL ");
}
string ext = excel.Extension.ToLower();
OleDbConnectionStringBuilder strcon = new OleDbConnectionStringBuilder();
switch (ext)
{
case ".xlsx":
{
strcon.Add("Provider", $"{OleDbProvider}");
strcon.Add("Extended Properties", "Excel 12.0;HDR=YES;IMEX=1");
break;
}
case ".xls":
{
strcon.Add("Provider", $"{OleDbProvider}");
strcon.Add("Extended Properties", "Excel 8.0;HDR=YES;IMEX=1");
break;
}
default:
throw new Exception("Неверный формат файла.");
}
strcon.Add("Data Source", excel.FullPath);
ConnectionStringGenerated?.Invoke(null, strcon.ToString());
return(strcon.ToString());
}
private void Button_Click(object sender, RoutedEventArgs e)
{
// Create OpenFileDialog
Microsoft.Win32.OpenFileDialog openFileDlg = new Microsoft.Win32.OpenFileDialog();
openFileDlg.Filter = "Access Database Files (*.mdb)|*.mdb";
Nullable <bool> result = openFileDlg.ShowDialog();
if (result == true)
{
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
builder.Provider = "Microsoft.Jet.OLEDB.4.0";
builder.DataSource = openFileDlg.FileName;
builder["User Id"] = "admin";
builder["Password"] = "";
string conStr = builder.ToString();
OleDbConnection con = new OleDbConnection();
con.ConnectionString = builder.ToString();
con.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "select ML_Name from [ML]";
cmd.Connection = con;
OleDbDataReader rd = cmd.ExecuteReader();
listBox.ItemsSource = rd;
}
}
public void Connect(string password)
{
_sb["Jet OLEDB:Database Password"] = password;
_connection.ConnectionString = _sb.ToString();
_connection.Open();
}
protected override string GetConnectionString()
{
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(FConnectionString);
builder.DataSource = tbDatabase.Text;
if (!String.IsNullOrEmpty(tbUserName.Text))
{
builder.Add(MsAccessDataConnection.strUserID, tbUserName.Text);
}
else
{
builder.Remove(MsAccessDataConnection.strUserID);
}
if (!String.IsNullOrEmpty(tbPassword.Text))
{
builder.Add(MsAccessDataConnection.strPassword, tbPassword.Text);
}
else
{
builder.Remove(MsAccessDataConnection.strPassword);
}
return(builder.ToString());
}
private void Form1_Load(object sender, EventArgs e)
{
textBox1.KeyDown += textBox1_KeyDown;
DataGridView1.KeyDown += DataGridView1_KeyDown;
DataTable dt = new DataTable();
OleDbConnectionStringBuilder Builder = new OleDbConnectionStringBuilder();
Builder.Provider = "Microsoft.ACE.OLEDB.12.0";
Builder.DataSource = Path.Combine(Application.StartupPath, "Database1.accdb");
using (OleDbConnection cn = new OleDbConnection {
ConnectionString = Builder.ToString()
})
{
using (OleDbCommand cmd = new OleDbCommand {
CommandText = "SELECT Identifier, UserName, UserRole FROM Users", Connection = cn
})
{
cn.Open();
OleDbDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
}
}
bs.DataSource = dt;
DataGridView1.DataSource = bs;
ActiveControl = textBox1;
}
public List <string> ListSheetInExcel(string filePath)
{
OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
String strExtendedProperties = String.Empty;
sbConnection.DataSource = filePath;
if (Path.GetExtension(filePath).Equals(".xls"))//for 97-03 Excel file
{
sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1"; //HDR=ColumnHeader,IMEX=InterMixed
}
else if (Path.GetExtension(filePath).Equals(".xlsx")) //for 2007 Excel file
{
sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
}
sbConnection.Add("Extended Properties", strExtendedProperties);
List <string> listSheet = new List <string>();
using (OleDbConnection conn = new OleDbConnection(sbConnection.ToString()))
{
conn.Open();
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (DataRow drSheet in dtSheet.Rows)
{
if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
{
listSheet.Add(drSheet["TABLE_NAME"].ToString());
}
}
}
return(listSheet);
}
private OleDbConnection CreateNewConnection()
{
var conn = new OleDbConnection();
var csb = new OleDbConnectionStringBuilder();
if (!string.IsNullOrEmpty(dbConfigName))
{
csb.ConnectionString = this.connectionStrings;
if (csb.DataSource.StartsWith("~/"))
{
csb.DataSource = HttpContext.Current.Server.MapPath(csb.DataSource);
}
}
else
{
if (dbFile.Extension == ".xls")
{
csb.ConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Extended Properties='Excel 8.0;HDR=YES;IMEX={0}'", this.ExcelOpenMode.ToString("d"));
}
else if (dbFile.Extension == ".xlsx")
{
csb.ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES;IMEX={0}'", this.ExcelOpenMode.ToString("d"));
}
else
{
csb.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;";
}
csb.DataSource = dbFile.FullName;
}
conn.ConnectionString = csb.ToString();
conn.Open();
return(conn);
}
/// <summary>
/// 解析処理
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public async Task <DataTable> ReadCsvTableAsync(FileInfo file)
{
// 接続文字列
var build = new OleDbConnectionStringBuilder();
// バージョン
build.Provider = "Microsoft.Jet.OLEDB.4.0";
//build.Provider = "Microsoft.ACE.OLEDB.12";
// フォルダー
build.DataSource = file.DirectoryName;
// プロパティ
build["Extended Properties"] = ""
+ $"Text;"
+ $"FMT=Delimited(,);"
+ $"CharacterSet={this.Charset.CodePage};"
//+ $"IMEX=1;"
+ $"HDR={(HeaderMode == HeaderMode.None ? "No" : "Yes")};"
;
// 接続
using (var conn = new OleDbConnection(build.ToString())) {
conn.Open();
var commond = conn.CreateCommand();
commond.CommandText = $"SELECT * FROM {file.Name}";
var reader = await commond.ExecuteReaderAsync();
var table = new DataTable();
table.Load(reader);
return(table);
}
}
private static string ExcelConnectionString(string filePath)
{
try
{
OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
String strExtendedProperties = String.Empty;
sbConnection.DataSource = filePath;
if (Path.GetExtension(filePath).Equals(".xls"))//for 97-03 Excel file
{
sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1"; //HDR=ColumnHeader,IMEX=InterMixed
}
else if (Path.GetExtension(filePath).Equals(".xlsx")) //for 2007 Excel file
{
sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
}
sbConnection.Add("Extended Properties", strExtendedProperties);
return(sbConnection.ToString());
}
catch (Exception)
{
throw;
}
}
public static OleDbConnection getCon()
{
if (conexion == null)
{
try
{
OleDbConnectionStringBuilder b = new OleDbConnectionStringBuilder();
b.Provider = "Microsoft.ACE.OLEDB.12.0";
b.DataSource = "contacts.accdb";
conexion = new OleDbConnection(b.ToString());
conexion.Open();
}
catch (OleDbException e)
{
MessageBox.Show("Error de la base de datos: " + e.Message);
if (System.Windows.Forms.Application.MessageLoop)
{
System.Windows.Forms.Application.Exit();
}
else
{
System.Environment.Exit(1);
}
}
}
return(conexion);
}
/// <summary>
/// Gets table schema information about an OLE database
/// </summary>
/// <returns>DataTable with schema information</returns>
//This function has been fixed in parent class, It should work for all database type // zack 1/30/08
//public override DataTable GetTableSchema()
//{
//DataTable schema = base.GetSchema("Tables");
//foreach (DataRow row in schema.Rows)
//{
// // TODO: This isn't asbolute, should be replaced with search on...
// // exact system table names
// if (row[ColumnNames.SCHEMA_TABLE_NAME].ToString().StartsWith("MSys", true, CultureInfo.InvariantCulture))
// {
// row.Delete();
// }
//}
//schema.AcceptChanges();
// return schema;
//}
public static string BuildConnectionString(string filePath, string password)
{
StringBuilder builder = new StringBuilder();
if (filePath.EndsWith(".accdb", true, System.Globalization.CultureInfo.InvariantCulture))
{
builder.Append("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=");
}
builder.Append(EncodeOleDbConnectionStringValue(filePath));
builder.Append(";");
builder.Append("User Id=admin;Password=;");
if (!string.IsNullOrEmpty(password))
{
builder.Append("Jet OLEDB:Database Password="******";");
}
// sanity check
OleDbConnectionStringBuilder connectionBuilder = new OleDbConnectionStringBuilder(builder.ToString());
return(connectionBuilder.ToString());
}
/// <inheritdoc/>
protected override void SetConnectionString(string value)
{
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(value);
builder.Provider = "Microsoft.Jet.OLEDB.4.0";
base.SetConnectionString(builder.ToString());
}
/// <summary>
/// Creates a <see cref="OleDbConnection"/> based on the specified Microsoft Excel workbook.
/// </summary>
/// <param name="fileName">Full path and file name of the Microsoft Excel workbook to use as the data source.</param>
/// <returns>Returns a <see cref="OleDbConnection"/>.</returns>
private OleDbConnection GetWorkbookConnection(string fileName)
{
// http://stackoverflow.com/a/9274455/2386774
if (string.IsNullOrEmpty(fileName))
{
throw new ArgumentNullException("fileName", "The filename must not be empty or null.");
}
OleDbConnectionStringBuilder connectionString = new OleDbConnectionStringBuilder();
connectionString.DataSource = fileName;
string extendedProperties = String.Empty;
if (Path.GetExtension(fileName).Equals(".xls")) // for 97-03 Excel file
{
connectionString.Provider = "Microsoft.Jet.OLEDB.4.0";
extendedProperties = "Excel 8.0";
}
else if (Path.GetExtension(fileName).Equals(".xlsx")) // for 2007 Excel file
{
// Install 2007 Office System Driver: Data Connectivity Components
// https://www.microsoft.com/en-us/download/details.aspx?id=23734
connectionString.Provider = "Microsoft.ACE.OLEDB.12.0";
extendedProperties = "Excel 12.0";
}
else
{
throw new ArgumentException("The file must be of type .xls or .xlsx.");
}
connectionString.Add("Extended Properties", extendedProperties + ";HDR=Yes;IMEX=1"); // HDR=ColumnHeader,IMEX=InterMixed
return(new OleDbConnection(connectionString.ToString()));
}
/// <summary>
/// Load Vendor contact data from Excel file
/// </summary>
public DataSet LoadVendorContacts(string ShortCode)
{
DataSet dsVendorContactData = new DataSet();
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
builder["Provider"] = "Microsoft.Jet.OLEDB.4.0";
builder["Data Source"] = @"\\PFCDEV\Lib\RTS\VendorContactsList.xls";
builder["User Id"] = "Admin";
builder["Extended Properties"] = "Excel 8.0;HDR=YES;";
OleDbConnection cno = new OleDbConnection();
cno.ConnectionString = builder.ToString();
cno.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [Sheet1$] where [Search Name] = '" + ShortCode + "'", cno);
adapter.Fill(dsVendorContactData, "VendorContacts");
//OleDbCommand commando = new OleDbCommand();
//commando.Connection = cno;
////commando.CommandTimeout = TimeOut;
//commando.CommandText = "select * from [Sheet1$]";
//OleDbDataReader dsVendorContactData = commando.ExecuteReader();
cno.Close();
return(dsVendorContactData);
}
public MainWindow()
{
InitializeComponent();
// Define the application's look-and-feel.
// (Don't forget to add a corresponding assembly to the project).
ThemeManager.ApplicationThemeName = "Office1020blue";
// Create a new string builder.
var builder = new OleDbConnectionStringBuilder()
{
Provider = "Microsoft.Jet.OLEDB.4.0",
DataSource = "nwind.mdb"
};
// Register the string builder in the current data provider repository.
DataProviderRepository.Current.Register("nwind", "Northwind", builder.ToString());
// Create a new client factory and view model for the Designer.
var factory = new LocalReportDesignerClientFactory();
var model = new ReportDesignerViewModel()
{
ReportName = "WpfReportDesigner_local.SampleReport",
ServiceClientFactory = factory
};
// Assign the model to the Report Designer,
// and start a design session.
reportDesigner.Model = model;
reportDesigner.Model.StartDesign();
LocalReportDesignerClient qw = new LocalReportDesignerClient();
}
public DataTable Consulta(string c)
{
DataTable dt = new DataTable();
//try
//{
OleDbDataAdapter da = new OleDbDataAdapter();
DataSet ds = new DataSet();
OleDbConnectionStringBuilder documentos = new OleDbConnectionStringBuilder();
documentos.DataSource = "documentos.accdb";
documentos.Provider = "Microsoft.Jet.OLEDB.4.0";
OleDbConnection conexion = new OleDbConnection(documentos.ToString());
da.SelectCommand = new OleDbCommand(c, conexion);
da.Fill(ds);
dt = ds.Tables[0];
//label5.Text =Convert.ToString(dt.Rows[0].ItemArray[6]);
//label5.Text = Convert.ToString(dt.Rows.Count);
//}
//catch(System.Data.OleDb.OleDbException)
//{
// MessageBox.Show("Por favor cierre el archivo documentos.accdb", "Advertencia Ingreso de datos", MessageBoxButtons.OK, MessageBoxIcon.Warning);
//}
return(dt);
}
private List <String> GetExcelSheetNames(string filePath)
{
OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
String strExtendedProperties = String.Empty;
sbConnection.DataSource = filePath;
sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
sbConnection.Add("Extended Properties", strExtendedProperties);
List <string> listSheet = new List <string>();
using (OleDbConnection conn = new OleDbConnection(sbConnection.ToString()))
{
conn.Open();
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (DataRow drSheet in dtSheet.Rows)
{
if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
{
listSheet.Add(drSheet["TABLE_NAME"].ToString());
}
}
}
return(listSheet);
}
// ====================================================================================================
// ====================================================================================================
#region MÉTODOS PÚBLICOS
// ====================================================================================================
// public string GetCadenaConexion(Centros centro) {
// // Si no se ha establecido el centro, devolvemos null.
// if (centro == Centros.Desconocido) return null;
// // Definimos el archivo de base de datos
// string archivo = Utils.CombinarCarpetas(Configuracion.CarpetaDatos, centro.ToString() + ".accdb");
// // Si no existe el archivo, devolvemos null
// if (!File.Exists(archivo)) return null;
// // Establecemos la cadena de conexión
// string cadenaConexion = "Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;";
// cadenaConexion += "Data Source=" + archivo + ";";
// // Devolvemos la cadena de conexión.
// return cadenaConexion;
//}
public string GetCadenaConexion(Centros centro)
{
// Si no se ha establecido el centro, devolvemos null.
if (centro == Centros.Desconocido)
{
return(null);
}
// Definimos el archivo de base de datos
string archivo = Utils.CombinarCarpetas(Configuracion.CarpetaDatos, centro.ToString() + ".accdb");
// Si no existe el archivo, devolvemos null
if (!File.Exists(archivo))
{
return(null);
}
// Establecemos la cadena de conexión
OleDbConnectionStringBuilder cadenaConexionB = new OleDbConnectionStringBuilder {
DataSource = archivo,
Provider = "Microsoft.ACE.OLEDB.12.0",
PersistSecurityInfo = false
};
string cadenaConexion = cadenaConexionB.ToString();
// Devolvemos la cadena de conexión.
return(cadenaConexion);
}
public DataTable ImportarXLSXNovo(string NomeArquivo, string NomePlanilha, string ColunasBusca = "*", int QtdTopSelect = 0)
{
string topSelect = "";
if (QtdTopSelect == 0)
{
topSelect = "";
}
else
{
topSelect = string.Format("TOP {0} ", QtdTopSelect);
}
//if (NomePlanilha.Contains("$")) NomePlanilha = NomePlanilha.Replace("$", "");
OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
sbConnection.DataSource = NomeArquivo;
string strExtendedProperties = string.Empty;
#region Verifica a extensão do arquivo e aplica a Connection String apropriada
if (Path.GetExtension(NomeArquivo).Equals(".xls")) // Excel 97-03
{
sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1";
}
else if (Path.GetExtension(NomeArquivo).Equals(".xlsx")) // Excel 2007
{
sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
}
sbConnection.Add("Extended Properties", strExtendedProperties);
#endregion
using (OleDbConnection conn = new OleDbConnection(sbConnection.ToString()))
{
//string sql = string.Format("select {0}* from [{1}]", topSelect, NomePlanilha);
string sql = string.Format("select {0}{1} from [{2}]", topSelect, ColunasBusca, NomePlanilha);
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
try
{
conn.Open();
da.Fill(ds);
dt = ds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
da.Dispose();
ds.Dispose();
}
return(dt);
}
}
protected override OleDbConnection GetNativeConnection(string connectionString)
{
OleDbConnectionStringBuilder oleDBCnnStrBuilder = new OleDbConnectionStringBuilder(connectionString);
oleDBCnnStrBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
return(new OleDbConnection(oleDBCnnStrBuilder.ToString()));
}
protected override OleDbConnection GetNativeConnection(string connectionString)
{
OleDbConnectionStringBuilder oleDBCnnStrBuilder = new OleDbConnectionStringBuilder(connectionString);
oleDBCnnStrBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
oleDBCnnStrBuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
return(new OleDbConnection(oleDBCnnStrBuilder.ToString()));
}
public static String CreateOleDbConnectionString(String DataSource, String Provider = "Microsoft.ACE.OleDB.15.0")
{
OleDbConnectionStringBuilder oleDbConnectionStringBuilder = new OleDbConnectionStringBuilder();
oleDbConnectionStringBuilder.Provider = Provider;
oleDbConnectionStringBuilder.DataSource = DataSource;
return(oleDbConnectionStringBuilder.ToString());
}
public static ICollection <Platform> GetAllPlatforms(ICollection <Platform> dealershipPlatforms, IReadOnlyCollection <ZipArchiveEntry> platformEntries)
{
foreach (var entry in platformEntries)
{
var connection = new OleDbConnection();
OleDbConnectionStringBuilder connectionString = SetUpConnectionString();
connection.ConnectionString = connectionString.ToString();
var fileName = GetFileName(entry);
using (var ms = new MemoryStream())
{
var file = File.Create(TempExcelFile);
using (file)
{
CopyStream(entry.Open(), ms);
ms.WriteTo(file);
}
}
using (connection)
{
connection.Open();
var excelSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
var sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();
var oleDbCommand = new OleDbCommand("SELECT * FROM [" + sheetName + "]", connection);
using (var adapter = new OleDbDataAdapter(oleDbCommand))
{
var dataSet = new DataSet();
adapter.Fill(dataSet);
using (var reader = dataSet.CreateDataReader())
{
while (reader.Read())
{
var platform = new Platform()
{
PlatformType = (PlatformType)Enum.Parse(typeof(PlatformType), reader["PlatformType"].ToString()),
NumberOfDoors = int.Parse(reader["NumberOfDoors"].ToString())
};
Console.WriteLine(platform.NumberOfDoors);
dealershipPlatforms.Add(platform);
}
}
}
}
}
File.Delete(TempExcelFile);
return(dealershipPlatforms);
}
public static ICollection <Engine> GetAllEngines(ICollection <Engine> dealershipEngines, IReadOnlyCollection <ZipArchiveEntry> engineEntries)
{
foreach (var entry in engineEntries)
{
var fileName = GetFileName(entry);
using (var ms = new MemoryStream())
{
var file = File.Create(TempExcelFile);
using (file)
{
CopyStream(entry.Open(), ms);
ms.WriteTo(file);
}
}
var connection = new OleDbConnection();
OleDbConnectionStringBuilder connectionString = SetUpConnectionString();
connection.ConnectionString = connectionString.ToString();
using (connection)
{
connection.Open();
var excelSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
var sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();
var oleDbCommand = new OleDbCommand("SELECT * FROM [" + sheetName + "]", connection);
using (var adapter = new OleDbDataAdapter(oleDbCommand))
{
var dataSet = new DataSet();
adapter.Fill(dataSet);
using (var reader = dataSet.CreateDataReader())
{
while (reader.Read())
{
var engine = new Engine()
{
Fuel = (FuelType)Enum.Parse(typeof(FuelType), reader["Fuel"].ToString()),
HorsePower = int.Parse(reader["HorsePower"].ToString())
};
Console.WriteLine(engine.HorsePower);
dealershipEngines.Add(engine);
}
}
}
}
}
File.Delete(TempExcelFile);
return(dealershipEngines);
}
public AccessDirectDataOperator(String databaseFileName)
{
OleDbConnectionStringBuilder connectionStringBuilder = new OleDbConnectionStringBuilder()
{
DataSource = databaseFileName
};
this.Connection = new OleDbConnection(connectionStringBuilder.ToString());
this.GeneratedCommands = new List <OleDbCommand>();
}
protected virtual string GetConnectionString()
{
var conBilder = new OleDbConnectionStringBuilder();
conBilder.Provider = "Microsoft.ACE.OLEDB.12.0";
conBilder.DataSource = this.DataSource;
conBilder.Add("Extended Properties", this.GetExtendedPropertyString());
return(conBilder.ToString());
}
private static OleDbConnection DatabaseConnection()
{
var DbStringBuild = new OleDbConnectionStringBuilder
{
Provider = DatabaseProvider,
DataSource = DatabaseSource
};
return(new OleDbConnection(DbStringBuild.ToString()));
}
public static ICollection <Town> GetAllTowns(ICollection <Town> dealershipTowns, IReadOnlyCollection <ZipArchiveEntry> townEntries)
{
foreach (var entry in townEntries)
{
var fileName = GetFileName(entry);
using (var ms = new MemoryStream())
{
var file = File.Create(TempExcelFile);
using (file)
{
CopyStream(entry.Open(), ms);
ms.WriteTo(file);
}
}
var connection = new OleDbConnection();
OleDbConnectionStringBuilder connectionString = SetUpConnectionString();
connection.ConnectionString = connectionString.ToString();
using (connection)
{
connection.Open();
var excelSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
var sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();
var oleDbCommand = new OleDbCommand("SELECT * FROM [" + sheetName + "]", connection);
using (var adapter = new OleDbDataAdapter(oleDbCommand))
{
var dataSet = new DataSet();
adapter.Fill(dataSet);
using (var reader = dataSet.CreateDataReader())
{
while (reader.Read())
{
var town = new Town()
{
Name = reader["Name"].ToString(),
};
Console.WriteLine(town.Name);
dealershipTowns.Add(town);
}
}
}
}
}
File.Delete(TempExcelFile);
return(dealershipTowns);
}
public static string GetJetConnectionString()
{
// ReSharper disable once CollectionNeverUpdated.Local
OleDbConnectionStringBuilder oleDbConnectionStringBuilder = new OleDbConnectionStringBuilder();
//oleDbConnectionStringBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
//oleDbConnectionStringBuilder.DataSource = @".\Empty.mdb";
oleDbConnectionStringBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
oleDbConnectionStringBuilder.DataSource = GetTestDirectory() + "\\Empty.accdb";
return(oleDbConnectionStringBuilder.ToString());
}
public static OleDbConnection getCon()
{
if (conexion == null)
{
OleDbConnectionStringBuilder b = new OleDbConnectionStringBuilder();
b.Provider = "Microsoft.ACE.OLEDB.12.0";
b.DataSource = "cyberman.accdb";
conexion = new OleDbConnection(b.ToString());
conexion.Open();
}
return(conexion);
}
