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;
}
public static DbConnection DbConnectionFromConnectionString(string connectionString)
{
DbConnection connection = null;
OleDbConnectionStringBuilder builder = new System.Data.OleDb.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 DbConnection DbConnectionFromConnectionString(string connectionString)
{
DbConnection connection = null;
OleDbConnectionStringBuilder builder = new System.Data.OleDb.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;
}
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();
}
/// <summary>
/// returns value to indicate if connection string has a password associated with it
/// </summary>
/// <param name="connectionString"></param>
/// <returns></returns>
protected virtual bool HasPassword(string connectionString)
{
if (!string.IsNullOrEmpty(connectionString))
{
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(this.connectionString);
return builder.ContainsKey("Jet OLEDB:Database Password");
}
else
{
return false;
}
}
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;
}
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 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
}
