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());
}
static void Main(string[] args)
{
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
builder.ConnectionString = @"Data Source=C:\Sample.mdb";
// Call the Add method to explicitly add key/value
// pairs to the internal collection.
builder.Add("Provider", "Microsoft.Jet.Oledb.4.0");
builder.Add("Jet OLEDB:Database Password", "MyPassword!");
builder.Add("Jet OLEDB:System Database", @"C:\Workgroup.mdb");
// Set up row-level locking.
builder.Add("Jet OLEDB:Database Locking Mode", 1);
Console.WriteLine(builder.ConnectionString);
Console.WriteLine();
// Clear current values and reset known keys to their
// default values.
builder.Clear();
// Pass the OleDbConnectionStringBuilder an existing
// connection string, and you can retrieve and
// modify any of the elements.
builder.ConnectionString =
"Provider=DB2OLEDB;Network Transport Library=TCPIP;" +
"Network Address=192.168.0.12;Initial Catalog=DbAdventures;" +
"Package Collection=SamplePackage;Default Schema=SampleSchema;";
Console.WriteLine("Network Address = " + builder["Network Address"].ToString());
Console.WriteLine();
// Modify existing items.
builder["Package Collection"] = "NewPackage";
builder["Default Schema"] = "NewSchema";
// Call the Remove method to remove items from
// the collection of key/value pairs.
builder.Remove("User ID");
// Note that calling Remove on a nonexistent item does not
// throw an exception.
builder.Remove("BadItem");
Console.WriteLine(builder.ConnectionString);
Console.WriteLine();
// Setting the indexer adds the value, if
// necessary.
builder["User ID"] = "SampleUser";
builder["Password"] = "******";
Console.WriteLine(builder.ConnectionString);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
/// <inheritdoc/>
protected override string GetConnectionStringWithLoginInfo(string userName, string password)
{
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(ConnectionString);
builder.Remove("User ID");
builder.Add("User ID", userName);
builder.Remove("Password");
builder.Add("Password", password);
return(builder.ToString());
}
public static string ForSqlConnection(this string oleDbConnectionString)
{
var builder = new OleDbConnectionStringBuilder(oleDbConnectionString);
builder.Remove("provider");
return(builder.ConnectionString);
}
static void TryRemove(OleDbConnectionStringBuilder builder,
string itemToRemove)
{
if (builder.Remove(itemToRemove))
{
Console.WriteLine("Removed '{0}'", itemToRemove);
}
else
{
Console.WriteLine("Unable to remove '{0}'", itemToRemove);
}
Console.WriteLine(builder.ConnectionString);
}
private void EnumerateCatalogs()
{
// Perform the enumeration
DataTable dataTable = null;
OleDbConnection connection = null;
try
{
// Create a connection string without initial catalog
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(Properties.ToFullString());
builder.Remove("Initial Catalog");
// Create a connection
connection = new OleDbConnection(builder.ConnectionString);
// Open the connection
connection.Open();
// Try to get the DBSCHEMA_CATALOGS schema rowset
dataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Catalogs, null);
}
catch
{
dataTable = new DataTable();
dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
// Create the object array of catalog names
_catalogs = new object[dataTable.Rows.Count];
for (int i = 0; i < _catalogs.Length; i++)
{
_catalogs[i] = dataTable.Rows[i]["CATALOG_NAME"];
}
// Populate the initial catalog combo box items (must occur on the UI thread)
if (Thread.CurrentThread == _uiThread)
{
PopulateInitialCatalogComboBox();
}
else if (this.IsHandleCreated)
{
BeginInvoke(new ThreadStart(PopulateInitialCatalogComboBox));
}
}
private void EnumerateCatalogs()
{
// Perform the enumeration
DataTable dataTable = null;
OleDbConnection connection = null;
try
{
// Create a connection string without initial catalog
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(_connectionProperties.ToFullString());
builder.Remove("Initial Catalog");
// Create a connection
connection = new OleDbConnection(builder.ConnectionString);
// Open the connection
connection.Open();
// Try to get the DBSCHEMA_CATALOGS schema rowset
dataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Catalogs, null);
}
catch
{
dataTable = new DataTable
{
Locale = System.Globalization.CultureInfo.InvariantCulture
};
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
// Create the object array of catalog names
_databases = new List <string>();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
_databases.Add(dataTable.Rows[i]["CATALOG_NAME"] as string);
}
databaseCombobox.GetBindingExpression(ComboBox.ItemsSourceProperty).UpdateTarget();
}
public BulkCopy(OleDbConnection conn)
{
OleDbConnectionStringBuilder oleDbStringBuilder = new OleDbConnectionStringBuilder(conn.ConnectionString);
oleDbStringBuilder.Remove("provider");
IBulkCopyWrapper bulkCopyWrapper = null;
switch (conn.Provider)
{
case "sqloledb":
bulkCopyWrapper = new SqlBulkCopyWrapper(new SqlConnection(oleDbStringBuilder.ConnectionString));
break;
case "oraoledb":
bulkCopyWrapper = new OracleBulkCopyWrapper(new OracleConnection(oleDbStringBuilder.ConnectionString));
break;
default: throw new Exception("Not Support OleDbConnection");
}
}
public void OleDbConnectionStringBuilder_Success()
{
var connectionStringBuilder = (OleDbConnectionStringBuilder)OleDbFactory.Instance.CreateConnectionStringBuilder();
Assert.Empty(connectionStringBuilder.Provider);
Assert.True(connectionStringBuilder.ContainsKey("Provider"));
Assert.Empty((string)connectionStringBuilder["Provider"]);
Assert.False(connectionStringBuilder.ContainsKey("MissingKey"));
Assert.False(connectionStringBuilder.TryGetValue("MissingKey", out var value));
// Default values
Assert.Equal(-13, connectionStringBuilder.OleDbServices);
Assert.False(connectionStringBuilder.PersistSecurityInfo);
connectionStringBuilder = new OleDbConnectionStringBuilder(ConnectionString);
Assert.Equal(Helpers.ProviderName, connectionStringBuilder.Provider);
Assert.Equal(TestDirectory, connectionStringBuilder.DataSource);
string udlFile = GetTestFilePath() + ".udl";
connectionStringBuilder = new OleDbConnectionStringBuilder(@"file name = " + udlFile);
Assert.Equal(udlFile, connectionStringBuilder.FileName);
connectionStringBuilder = new OleDbConnectionStringBuilder();
connectionStringBuilder.Provider = "myProvider";
connectionStringBuilder.DataSource = "myServer";
connectionStringBuilder.FileName = "myAccessFile.mdb";
connectionStringBuilder.PersistSecurityInfo = true;
connectionStringBuilder.OleDbServices = 0;
Assert.Equal(connectionStringBuilder.Provider, connectionStringBuilder["Provider"]);
Assert.Equal(connectionStringBuilder.DataSource, connectionStringBuilder["Data Source"]);
Assert.Equal(connectionStringBuilder.FileName, connectionStringBuilder["File Name"]);
Assert.Equal(connectionStringBuilder.OleDbServices, connectionStringBuilder["OLE DB Services"]);
Assert.Equal(connectionStringBuilder.PersistSecurityInfo, connectionStringBuilder["Persist Security Info"]);
connectionStringBuilder["CustomKey"] = "CustomValue";
string connectionString = connectionStringBuilder.ToString();
Assert.Contains(@"Provider=" + connectionStringBuilder.Provider, connectionString);
Assert.Contains(@"Data Source=" + connectionStringBuilder.DataSource, connectionString);
Assert.Contains(@"File Name=" + connectionStringBuilder.FileName, connectionString);
Assert.Contains(@"OLE DB Services=" + connectionStringBuilder.OleDbServices, connectionString);
Assert.Contains(@"Persist Security Info=" + connectionStringBuilder.PersistSecurityInfo, connectionString);
Assert.Contains(@"CustomKey=" + connectionStringBuilder["CustomKey"], connectionString);
connectionStringBuilder["OLE DB Services"] = 3;
Assert.Contains(@"OLE DB Services=" + 3, connectionStringBuilder.ToString());
connectionStringBuilder["Persist Security Info"] = false;
Assert.Contains(@"Persist Security Info=" + false, connectionStringBuilder.ToString());
connectionStringBuilder["Provider"] = string.Empty;
Assert.Contains(@"Provider=;", connectionStringBuilder.ToString());
Assert.Equal(string.Empty, connectionStringBuilder["Provider"]);
Assert.Throws <ArgumentNullException>(() => connectionStringBuilder.Remove(null));
Assert.False(connectionStringBuilder.Remove("NonExistentKey"));
Assert.True(connectionStringBuilder.Remove("File Name"));
Assert.Empty(connectionStringBuilder.FileName);
Assert.True(connectionStringBuilder.Remove("Provider"));
Assert.Empty(connectionStringBuilder.Provider);
}
private void ParseTSqlStatement(string statement, int connectionID, int reportID)
{
SqlStatement toBeParsed = new SqlStatement();
String existingDBName = String.Empty;
object existing;
String connectionString = repository.RetrieveConnectionString(connectionID);
OleDbConnectionStringBuilder csBuilder = (OleDbConnectionStringBuilder)repository.GetConnectionStringBuilder(connectionString);
if (csBuilder != null)
{
if (csBuilder.TryGetValue(Repository.ConnectionStringProperties.InitialCatalog, out existing))
{
existingDBName = (String)existing;
}
else if (csBuilder.TryGetValue(Repository.ConnectionStringProperties.Database, out existing))
{
existingDBName = (String)existing;
}
}
else
{
csBuilder = (OleDbConnectionStringBuilder)repository.GetConnectionStringBuilder(String.Empty);
}
try
{
toBeParsed.quotedIdentifiers = true;
if (toBeParsed.ParseString(statement))
{
foreach (string tableName in toBeParsed.getTableNames(true))
{
int tableID = -1;
string[] tableParts = tableName.Split('.');
switch (tableParts.Length)
{
case 3:
String dbName = tableParts[0].Replace("[", "").Replace("]", "");
if (csBuilder.ContainsKey(Repository.ConnectionStringProperties.InitialCatalog))
{
csBuilder.Remove(Repository.ConnectionStringProperties.InitialCatalog);
csBuilder.Add(Repository.ConnectionStringProperties.InitialCatalog, dbName);
connectionString = csBuilder.ConnectionString;
}
else if (csBuilder.ContainsKey(Repository.ConnectionStringProperties.Database))
{
csBuilder.Remove(Repository.ConnectionStringProperties.Database);
csBuilder.Add(Repository.ConnectionStringProperties.Database, dbName);
connectionString = csBuilder.ConnectionString;
}
int objectConnectionId = repository.GetConnection(connectionString);
if (objectConnectionId == -1)
{
// Need to add a new connectionID.
objectConnectionId = repository.AddObject("CMD " + dbName, string.Empty, Repository.OLEDBGuid, repository.RootRepositoryObjectID);
repository.AddAttribute(objectConnectionId, Repository.Attributes.ConnectionString, connectionString);
repository.AddAttribute(objectConnectionId, Repository.Attributes.ConnectionServer, csBuilder.DataSource);
repository.AddAttribute(objectConnectionId, Repository.Attributes.ConnectionDatabase, dbName);
}
if (threePartNames)
{
tableID = repository.GetTable(objectConnectionId, String.Format("{0}.{1}.{2}", tableParts[0], tableParts[1], tableParts[2]));
}
else
{
tableID = repository.GetTable(objectConnectionId, String.Format("{0}.{1}", tableParts[1], tableParts[2]));
}
break;
default:
if (threePartNames)
{
tableID = repository.GetTable(connectionID, String.Format("[{0}].{1}", existingDBName, tableName));
}
else
{
tableID = repository.GetTable(connectionID, tableName);
}
break;
}
if (!repository.DoesMappingExist(tableID, reportID))
{
repository.AddMapping(tableID, reportID);
}
}
foreach (string procedureName in toBeParsed.getProcedureNames(true))
{
int procID = -1;
string[] procedureParts = procedureName.Split('.');
switch (procedureParts.Length)
{
case 3:
String dbName = procedureParts[0].Replace("[", "").Replace("]", "");
if (csBuilder.ContainsKey(Repository.ConnectionStringProperties.InitialCatalog))
{
csBuilder.Remove(Repository.ConnectionStringProperties.InitialCatalog);
csBuilder.Add(Repository.ConnectionStringProperties.InitialCatalog, dbName);
connectionString = csBuilder.ConnectionString;
}
else if (csBuilder.ContainsKey(Repository.ConnectionStringProperties.Database))
{
csBuilder.Remove(Repository.ConnectionStringProperties.Database);
csBuilder.Add(Repository.ConnectionStringProperties.Database, dbName);
connectionString = csBuilder.ConnectionString;
}
int objectConnectionId = repository.GetConnection(connectionString);
if (objectConnectionId == -1)
{
// Need to add a new connectionID.
objectConnectionId = repository.AddObject("CMD " + dbName, string.Empty, Repository.OLEDBGuid, repository.RootRepositoryObjectID);
repository.AddAttribute(objectConnectionId, Repository.Attributes.ConnectionString, connectionString);
repository.AddAttribute(objectConnectionId, Repository.Attributes.ConnectionServer, csBuilder.DataSource);
repository.AddAttribute(objectConnectionId, Repository.Attributes.ConnectionDatabase, dbName);
}
if (threePartNames)
{
procID = repository.GetProcedure(objectConnectionId, String.Format("{0}.{1}.{2}", procedureParts[0], procedureParts[1], procedureParts[2]));
}
else
{
procID = repository.GetProcedure(objectConnectionId, String.Format("{0}.{1}", procedureParts[1], procedureParts[2]));
}
break;
default:
if (threePartNames)
{
procID = repository.GetProcedure(connectionID, String.Format("[{0}].{1}", existingDBName, procedureName));
}
else
{
procID = repository.GetProcedure(connectionID, procedureName);
}
break;
}
if (!repository.DoesMappingExist(procID, reportID))
{
repository.AddMapping(procID, reportID);
}
}
foreach (string funcName in toBeParsed.getFunctionNames(true))
{
int funcID = -1;
string[] functionParts = funcName.Split('.');
switch (functionParts.Length)
{
case 3:
String dbName = functionParts[0].Replace("[", "").Replace("]", "");
if (csBuilder.ContainsKey(Repository.ConnectionStringProperties.InitialCatalog))
{
csBuilder.Remove(Repository.ConnectionStringProperties.InitialCatalog);
csBuilder.Add(Repository.ConnectionStringProperties.InitialCatalog, dbName);
connectionString = csBuilder.ConnectionString;
}
else if (csBuilder.ContainsKey(Repository.ConnectionStringProperties.Database))
{
csBuilder.Remove(Repository.ConnectionStringProperties.Database);
csBuilder.Add(Repository.ConnectionStringProperties.Database, dbName);
connectionString = csBuilder.ConnectionString;
}
int objectConnectionId = repository.GetConnection(connectionString);
if (objectConnectionId == -1)
{
// Need to add a new connectionID.
objectConnectionId = repository.AddObject("CMD " + dbName, string.Empty, Repository.OLEDBGuid, repository.RootRepositoryObjectID);
repository.AddAttribute(objectConnectionId, Repository.Attributes.ConnectionString, connectionString);
repository.AddAttribute(objectConnectionId, Repository.Attributes.ConnectionServer, csBuilder.DataSource);
repository.AddAttribute(objectConnectionId, Repository.Attributes.ConnectionDatabase, dbName);
}
if (threePartNames)
{
funcID = repository.GetFunction(objectConnectionId, String.Format("{0}.{1}.{2}", functionParts[0], functionParts[1], functionParts[2]));
}
else
{
funcID = repository.GetFunction(objectConnectionId, String.Format("{0}.{1}", functionParts[1], functionParts[2]));
}
break;
default:
if (threePartNames)
{
funcID = repository.GetFunction(connectionID, String.Format("[{0}].{1}", existingDBName, funcName));
}
else
{
funcID = repository.GetFunction(connectionID, funcName);
}
break;
}
if (!repository.DoesMappingExist(funcID, reportID))
{
repository.AddMapping(funcID, reportID);
}
}
}
else
{
string errorMessage = "The following messages where generated whilst parsing the sql statement\r\n" + statement + "\r\n";
foreach (string error in toBeParsed.parseErrors)
{
errorMessage += error + "\r\n";
}
Console.WriteLine(errorMessage);
}
}
catch (Exception ex)
{
Console.WriteLine(string.Format("SQL Statement Failed with Exception {0}\r\nStatement Was:{1}\r\nPlease report to SSISMeta.codeplex.com\r\n", ex.Message, statement));
}
}
