public static DbConnection DbConnectionFromConnectionString(ConnectionType connectionType, string connectionString)
{
DbConnection connection;
if (connectionType == ConnectionType.MSSQLServer)
{
connection = new SqlConnection(connectionString);
}
else if (connectionType == ConnectionType.Odbc)
{
connection = new OdbcConnection(connectionString);
}
else
{
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(connectionString);
string provider = builder["Provider"].ToString();
if (provider.StartsWith("MSDASQL"))
{
//Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=mysql2;SERVER=localhost;UID=root;DATABASE=sakila;PORT=3306";Initial Catalog=sakila
//Provider=MSDASQL.1;Persist Security Info=True;Data Source=mysql;Initial Catalog=sakila
//Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=brCRM;DBQ=C:\tem\adb.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"
//Extract the real ODBC connection string...to be able to use the OdbcConnection
string odbcConnectionString = "";
if (builder.ContainsKey("Extended Properties"))
{
odbcConnectionString = builder["Extended Properties"].ToString();
}
else if (builder.ContainsKey("Data Source") && !string.IsNullOrEmpty(builder["Data Source"].ToString()))
{
odbcConnectionString = "DSN=" + builder["Data Source"].ToString();
}
if (odbcConnectionString != "" && builder.ContainsKey("Initial Catalog"))
{
odbcConnectionString += ";DATABASE=" + builder["Initial Catalog"].ToString();
}
if (odbcConnectionString != "" && builder.ContainsKey("User ID"))
{
odbcConnectionString += ";UID=" + builder["User ID"].ToString();
}
if (odbcConnectionString != "" && builder.ContainsKey("Password"))
{
odbcConnectionString += ";PWD=" + builder["Password"].ToString();
}
connection = new OdbcConnection(odbcConnectionString);
}
else
{
connection = new OleDbConnection(connectionString);
}
}
return(connection);
}
public static List <Line> GetSegments()
{
string query = @"select Link, Lane, SegStX, SegStY, SegEndX, SegEndY from dbo.Olaine_LINK_EVAL";
var list = new List <Line>();
var sb = new OleDbConnectionStringBuilder(vissim.Instance.Evaluation.Wrap().GetConnectionString());
if (sb.ContainsKey("Password"))
{
using (var conn = new OleDbConnection(sb.ConnectionString))
{
conn.Open();
using (var reader = new OleDbCommand(query, conn).ExecuteReader())
{
while (reader.Read())
{
list.Add(new Line()
{
X1 = reader.GetDouble(2),
Y1 = reader.GetDouble(3),
X2 = reader.GetDouble(4),
Y2 = reader.GetDouble(5),
Stroke = Brushes.Red
});
}
}
}
}
return(list);
}
public static List <Ellipse> GetPoints(int size)
{
string query = @"select Link, Lane, SegStX, SegStY, SegEndX, SegEndY from dbo.Olaine_LINK_EVAL";
var list = new List <Ellipse>();
var sb = new OleDbConnectionStringBuilder(vissim.Instance.Evaluation.Wrap().GetConnectionString());
if (sb.ContainsKey("Password"))
{
using (var conn = new OleDbConnection(sb.ConnectionString))
{
conn.Open();
using (var reader = new OleDbCommand(query, conn).ExecuteReader())
{
while (reader.Read())
{
list.Add(new Ellipse()
{
Height = size,
Width = size,
Fill = Brushes.Red,
Stroke = Brushes.Red,
Tag = new Point(reader.GetDouble(2), reader.GetDouble(3))
});
}
}
}
}
return(list);
}
public static List <AvgSpeedReportItem> GetReportData()
{
string query =
@"WITH RankedLink AS (
SELECT ROW_NUMBER() OVER (ORDER BY Link, SegEndC) [Number], *
FROM dbo.Olaine_LINK_EVAL),
MiddleLink AS (
SELECT AVG(Number) [MiddleLink]
FROM RankedLink
GROUP BY Link),
AvgSpeed AS (
SELECT AVG(v__0_) [speed], Link
FROM dbo.Olaine_LINK_EVAL
GROUP BY Link)
SELECT RankedLink.Link, 0 [Lane], (SegStX + SegEndX)/2 [center.x], (SegStY + SegEndY)/2 [center.y], AvgSpeed.speed
FROM RankedLink
JOIN MiddleLink ON RankedLink.Number=MiddleLink.MiddleLink
JOIN AvgSpeed ON RankedLink.Link=AvgSpeed.Link" ;
var list = new List <AvgSpeedReportItem>();
try
{
var sb = new OleDbConnectionStringBuilder(vissim.Instance.Evaluation.Wrap().GetConnectionString());
if (sb.ContainsKey("Password"))
{
using (var conn = new OleDbConnection(sb.ConnectionString))
{
conn.Open();
try
{
using (var reader = new OleDbCommand(query, conn).ExecuteReader())
{
while (reader.Read())
{
list.Add(new AvgSpeedReportItem()
{
Link = reader.GetInt32(0),
Lane = reader.GetInt32(1),
Center = new Point(reader.GetDouble(2), reader.GetDouble(3)),
AvgSpeed = reader.GetDouble(4)
});
}
}
}
finally
{
conn.Close();
}
}
}
}
catch { }
return(list);
}
private void TakeBackup(Guid id)
{
#if (!DEBUG)
var sb = new OleDbConnectionStringBuilder(vissim.Instance.Evaluation.Wrap().GetConnectionString());
if (sb.ContainsKey("Password"))
{
SQLAdmin.MakeRestore(null, id,
sb.DataSource,
sb["Initial Catalog"].ToString(),
@"\\toshiba\English.Cafe.2008.MP3.64kbps\",
sb["User ID"].ToString(),
sb["Password"].ToString());
}
#endif
}
private void btnUseSqlCmd_Click(object sender, EventArgs e)
{
string argument = "";
OleDbConnectionStringBuilder osb = new OleDbConnectionStringBuilder(this.ConnStr);
string server = osb["Data Source"].ToString();
string catalog = osb["Initial Catalog"].ToString();
if (osb.ContainsKey("integrated security"))
{
string argumentFormat = "sqlcmd -S \"{0}\" -d \"{1}\" -E -i \"{2}\"";
argument = string.Format(argumentFormat, server, catalog, txtSQLFileName.Text);
}
else
{
string argumentFormat = "sqlcmd -S \"{0}\" -U \"{2}\" -P \"{3}\" -d \"{1}\" -i \"{4}\"";
string user = osb["User ID"].ToString();
string password = osb["Password"].ToString();
argument = string.Format(argumentFormat, server, catalog, user, password, txtSQLFileName.Text);
}
this.ShowMessage("[{0}]", argument);
this.ShowMessage("命令执行中。。。。（请等待完成提示。。。。）");
Process p = new Process();
p.EnableRaisingEvents = true;
p.OutputDataReceived += new DataReceivedEventHandler(p_OutputDataReceived);
p.ErrorDataReceived += new DataReceivedEventHandler(p_ErrorDataReceived);
p.Exited += new EventHandler(p_Exited);
p.StartInfo.FileName = "cmd.exe";
p.StartInfo.Arguments = "/c " + argument;
p.StartInfo.UseShellExecute = false;
p.StartInfo.RedirectStandardInput = true;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.RedirectStandardError = true;
p.StartInfo.CreateNoWindow = true;
start = DateTime.Now;
p.Start();
p.BeginOutputReadLine();
p.BeginErrorReadLine();
}
//private void CreateExperiment(string projectDir, Guid id)
//{
// this.experiment = new Experiment(id);
// experiment.Save(Path.Combine(projectDir, project.Files.ExperimentFileName));
//}
//public void LoadExperiment(Guid id)
//{
// experiment = Experiment.Load(ExperimentFileName, id);
//}
//public void CreateNewProject(string projectName, string projectDir, string modelName)
//{
//vissim.Instance.New();
//vissim.Instance.Net.Name = modelName;
//CreateProject(projectName, projectDir);
//string modelDir = Path.Combine(projectDir, project.Files.ModelDirectory);
//if (!Directory.Exists(modelDir)) Directory.CreateDirectory(modelDir);
//vissim.Instance.SaveNetAs(string.Format("{0}\\{1}{2}", modelDir, modelName,
// modelName.EndsWith(".inp") ? string.Empty : ".inp"));
//vissim.Instance.SaveLayout(string.Format("{0}\\vissim.ini", modelDir));
//tree = new ExperimentsTree(Path.Combine(projectDir, project.Files.SnapshotTreeFileName));
//DirectoryPacker.Pack(
// modelDir,
// Path.Combine(projectDir, project.Files.SnapshotDataFileName),
// Tree.root.Id);
//CreateExperiment(projectDir, Tree.root.Id);
//project.CurrentExperimentId = Tree.root.Id;
//project.Save(projectDir);
//SaveProjectToSettings(project, projectDir);
//OnProjectLoaded(string.Format("{0}\\{1}{2}", projectDir, projectName, project.FileExtension));
//}
//public void CloneProject(string projectName, string projectDir, string modelFileName)
//{
//CreateProject(projectName, projectDir);
//string modelName = string.IsNullOrEmpty(modelFileName) ? vissim.Instance.GetInputFileName() : Path.GetFileName(modelFileName);
//string modelDir = string.IsNullOrEmpty(modelFileName) ? vissim.Instance.GetWorkingDirectory() : Path.GetDirectoryName(modelFileName);
//string snapshotDataFileName = string.Format("{0}\\{1}", projectDir, project.Files.SnapshotDataFileName);
//tree = new ExperimentsTree(string.Format("{0}\\{1}", projectDir, project.Files.SnapshotTreeFileName));
//DirectoryPacker.Pack(modelDir, snapshotDataFileName, Tree.root.Id);
//modelDir = string.Format("{0}\\{1}", projectDir, project.Files.ModelDirectory);
//if (!Directory.Exists(modelDir)) Directory.CreateDirectory(modelDir);
//DirectoryPacker.UnPack(modelDir, snapshotDataFileName, Tree.root.Id);
//vissim.Instance.LoadNet(string.Format("{0}\\{1}{2}", modelDir, modelName, modelName.EndsWith(".inp") ? string.Empty : ".inp"));
//CreateExperiment(projectDir, Tree.root.Id);
//project.CurrentExperimentId = Tree.root.Id;
//project.Save(projectDir);
//SaveProjectToSettings(project, projectDir);
//OnProjectLoaded(string.Format("{0}\\{1}{2}", projectDir, project.Name, project.FileExtension));
//}
//public void LoadProject(string projectFileName)
//{
// if (string.IsNullOrWhiteSpace(projectFileName)) return;
// if (!File.Exists(projectFileName))
// {
// if (LoadProjectFailed != null) LoadProjectFailed(this, new ProjectEventArgs(projectFileName));
// return;
// }
// projectDir = Path.GetDirectoryName(projectFileName);
// Project = Project.Load(projectFileName);
// experiment = Experiment.Load(Path.Combine(projectDir, project.Files.ExperimentFileName), project.CurrentExperimentId);
// string modelDir = Path.Combine(projectDir, project.Files.ModelDirectory);
// //TODO LASTPOINT
// if (vissim.Instance.GetWorkingDirectory() != string.Format("{0}\\", modelDir))
// {
// var filesList = Directory.GetFiles(modelDir, "*.inp");
// if (filesList.Length == 0) throw new FileNotFoundException("There are no input file in the model directory");
// string modelName = Path.GetFileName(filesList[0]);
// vissim.Instance.LoadNet(string.Format("{0}\\{1}{2}", modelDir, modelName,
// modelName.EndsWith(".inp") ? string.Empty : ".inp"));
// string layoutFileName = string.Format("{0}\\vissim.ini", modelDir);
// if (File.Exists(layoutFileName)) vissim.Instance.LoadLayout(layoutFileName);
// }
// SaveProjectToSettings(projectFileName);
// OnProjectLoaded(projectFileName);
//}
private bool MakeBackup(Guid id)
{
#if (!DEBUG)
var sb = new OleDbConnectionStringBuilder(vissim.Instance.Evaluation.Wrap().GetConnectionString());
if (sb.ContainsKey("Password"))
{
return(SQLAdmin.MakeBackup(null, id, ExperimentNumber,
sb.DataSource,
sb["Initial Catalog"].ToString(),
@"\\toshiba\English.Cafe.2008.MP3.64kbps\",
sb["User ID"].ToString(),
sb["Password"].ToString()));
}
else
{
return(false);
}
#else
return(false);
#endif
}
public void ExecuteMSSQLScripts(string scriptsDirectory, bool useAllConnections = false, bool stopOnError = true, int errorClassLevel = 11, string fileNameFilter = "")
{
_mssqlError = "";
_mssqlErrorClassLevel = errorClassLevel;
var files = Directory.GetFiles(scriptsDirectory, "*.sql");
foreach (var file in files.OrderBy(i => i))
{
if (!string.IsNullOrEmpty(fileNameFilter) && !Path.GetFileNameWithoutExtension(file).ToLower().Contains(fileNameFilter.ToLower()))
{
continue;
}
LogMessage("Processing file '{0}'", file);
foreach (var connection in _task.Source.Connections.Where(i => useAllConnections || i.GUID == _task.Connection.GUID))
{
if (_task.CancelReport)
{
break;
}
string connectionString = connection.FullConnectionString;
if (connection.ConnectionType == ConnectionType.OleDb)
{
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(connection.FullConnectionString);
connectionString = string.Format("Server={0};Database={1};", builder["Data Source"], builder["Initial Catalog"]);
connectionString += (builder.ContainsKey("User ID") ? string.Format("User Id={0};Password={1};", builder["User ID"], builder["Password"]) : "Trusted_Connection=True;");
}
else if (connection.ConnectionType == ConnectionType.Odbc)
{
throw new Exception("Odbc connection type not supported");
}
SqlConnection conn = new SqlConnection(connectionString);
try
{
conn.FireInfoMessageEventOnUserErrors = true;
conn.InfoMessage += MSSQLConnection_InfoMessage;
conn.Open();
string script = File.ReadAllText(file);
// split script on GO command
IEnumerable <string> commandStrings = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
List <string> commands = new List <string>();
string startCmd = "";
bool inComment = false;
foreach (string commandString in commandStrings)
{
if (!string.IsNullOrEmpty(commandString.Trim()))
{
if (!inComment)
{
if (hasStartCommentAtTheEnd(commandString))
{
//start of comment
inComment = true;
startCmd = commandString + "GO";
}
else
{
//normal case
commands.Add(startCmd + commandString);
inComment = false;
startCmd = "";
}
}
else
{
//in comment
if (!hasEndCommentAtTheBeginning(commandString))
{
//no end of comment
startCmd += commandString + "GO";
}
else if (hasEndCommentAtTheBeginning(commandString) && hasStartCommentAtTheEnd(commandString))
{
//end of comment, but start again
startCmd += commandString + "GO";
}
else
{
//end of comment
commands.Add(startCmd + commandString);
inComment = false;
startCmd = "";
}
}
}
}
foreach (string commandString in commands)
{
if (!string.IsNullOrEmpty(commandString.Trim()))
{
DateTime startCommand = DateTime.Now;
using (var command = new SqlCommand("", conn))
{
command.CommandTimeout = 0;
command.CommandText = commandString;
command.ExecuteNonQuery();
}
Thread.Sleep(200);
if (!string.IsNullOrEmpty(_mssqlError) && stopOnError)
{
throw new Exception(_mssqlError);
}
}
}
Thread.Sleep(500);
}
finally
{
conn.Close();
}
}
}
LogMessage("File execution terminated.");
}
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));
}
}
