static void Main(string[] args)
{
//string connetionString = null;
SqlConnection connection;
SqlCommand command;
string sql = null;
//connetionString = @"Data Source=ls-its-db;Initial Catalog=DAL1_IDB;User ID=ADS\pmoldenhauer;Password="******"SELECT Roadway_Name, Is_Only_Cross_Street FROM [DAL1_IDB].[dbo].[CT_ROADWAY]";
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
csb.DataSource = "ls-its-db";
csb.IntegratedSecurity = true;
csb.UserID = "ADS\\pmoldenhauer";
csb.Password = Settings.Default.SettingsKey;
ISet <string> roadways = new HashSet <string>();
ISet <string> crossStreets = new HashSet <string>();
using (connection = new SqlConnection(csb.ConnectionString))
{
try
{
connection.Open();
command = new SqlCommand(sql, connection);
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
//Console.WriteLine(dataReader.GetValue(0) + " - " + dataReader.GetValue(1));
if (dataReader.GetBoolean(1))
{
crossStreets.Add(dataReader.GetValue(0).ToString());
}
else
{
roadways.Add(dataReader.GetValue(0).ToString());
}
}
}
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\pmoldenhauer\Desktop\Copy of RITMS roadways and cross streets.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
int roadway_name = 4;
int cross_street_name = 10;
// start looping at row 2 where data is - row 1 is the headings in excel
for (int i = 2; i <= 5; i++)
{
// Check the roadway names in Excel
if (xlRange.Cells[i, roadway_name] != null && xlRange.Cells[i, roadway_name].Value2 != null)
{
string roadwayName = xlRange.Cells[i, roadway_name].Value2.ToString();
if (!roadways.Contains(roadwayName))
{
// INSERT roadwayName into database if not in already
try
{
sql =
"INSERT INTO [DAL1_IDB].[dbo].[CT_ROADWAY] ([Roadway_Name],[Is_Only_Cross_Street]) VALUES ('" +
roadwayName + "',0)";
command = new SqlCommand(sql, connection);
int result = command.ExecuteNonQuery();
Console.WriteLine("Roadway inserted into database: " + roadwayName);
command.Dispose();
}
catch (SqlException ex)
{
Console.WriteLine("ERROR! Insert failed!");
}
}
roadways.Add(roadwayName);
}
// Check the cross street names in Excel
if (xlRange.Cells[i, cross_street_name] != null && xlRange.Cells[i, cross_street_name].Value2 != null)
{
string roadwayName = xlRange.Cells[i, cross_street_name].Value2.ToString();
if (!crossStreets.Contains(roadwayName))
{
// INSERT cross street into database if not in already
try
{
sql =
"INSERT INTO [DAL1_IDB].[dbo].[CT_ROADWAY] ([Roadway_Name],[Is_Only_Cross_Street]) VALUES ('" +
roadwayName + "',1)";
command = new SqlCommand(sql, connection);
int result = command.ExecuteNonQuery();
Console.WriteLine("Cross Street inserted into database: " + roadwayName);
command.Dispose();
}
catch (SqlException ex)
{
Console.WriteLine("ERROR! Insert failed!");
}
}
crossStreets.Add(roadwayName);
}
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
command.Dispose();
connection.Close();
}
catch (Exception ex)
{
Console.WriteLine("Can not open connection! ");
}
}
Console.ReadKey();
}
public static void DifferentMethod1()
{
Console.WriteLine("\tHello from DifferentMethod1() {0}:::{1} ", DateTime.Now, Thread.CurrentThread.ManagedThreadId);
}
static void Main(string[] args)
{
var dt = DateTime.Now.AddHours(1).AddMonths(-2).AddDays(10);
Console.WriteLine(dt.ToString("t"));
Console.WriteLine(dt.ToString("T"));
Console.WriteLine(dt.ToString("d"));
Console.WriteLine(dt.ToString("D"));
Console.WriteLine(dt.ToString("f"));
Console.WriteLine(dt.ToString("F"));
Console.WriteLine(dt.ToString("g"));
Console.WriteLine(dt.ToString("G"));
Console.WriteLine(dt.ToString("m"));
Console.WriteLine(dt.ToString("M"));
Console.WriteLine(dt.ToString("y"));
Console.WriteLine(dt.ToString("Y"));
Console.WriteLine(dt.ToString("r"));
Console.WriteLine(dt.ToString("R"));
Console.WriteLine(dt.ToString("s"));
Console.WriteLine(dt.ToString("u"));
Console.WriteLine("\n\n");
Console.WriteLine("{0:d }", dt);
Console.WriteLine("{0:dd }", dt);
Console.WriteLine("{0:ddd }", dt);
Console.WriteLine("{0:dddd }", dt);
Console.WriteLine("{0:M }", dt);
Console.WriteLine("{0:MM }", dt);
Console.WriteLine("{0:MMM }", dt);
Console.WriteLine("{0:MMMM }", dt);
Console.WriteLine("{0:y }", dt);
Console.WriteLine("{0:yy }", dt);
Console.WriteLine("{0:yyy }", dt);
Console.WriteLine("{0:yyyy }", dt);
Console.WriteLine("{0:h }", dt);
Console.WriteLine("{0:hh }", dt);
Console.WriteLine("{0:H }", dt);
Console.WriteLine("{0:HH }", dt);
Console.WriteLine("{0:m }", dt);
Console.WriteLine("{0:mm }", dt);
Console.WriteLine("{0:s }", dt);
Console.WriteLine("{0:ss }", dt);
Console.WriteLine("\n\n");
Console.WriteLine("{0:f }", dt);
Console.WriteLine("{0:ff }", dt);
Console.WriteLine("{0:fff }", dt);
Console.WriteLine("{0:ffff }", dt);
Console.WriteLine("{0:F }", dt);
Console.WriteLine("\n\n");
Console.WriteLine("{0:z }", dt);
Console.WriteLine("{0:zz }", dt);
Console.WriteLine("{0:zzz }", dt);
Console.WriteLine("{0:t }", dt);
Console.WriteLine("{0:tt }", dt);
var ci = new CultureInfo("en-GB");
Console.WriteLine(dt.ToString("t ", ci));
Console.WriteLine(dt.ToString("tt", ci));
///
Thread.CurrentThread.CurrentCulture = ci;
Console.WriteLine(dt.ToString("t"));
Console.WriteLine(dt.ToString("T"));
Console.WriteLine(dt.ToString("d"));
Console.WriteLine(dt.ToString("D"));
Console.WriteLine(dt.ToString("f"));
Console.WriteLine(dt.ToString("F"));
Console.WriteLine(dt.ToString("g"));
Console.WriteLine(dt.ToString("G"));
Console.WriteLine(dt.ToString("m"));
Console.WriteLine(dt.ToString("M"));
Console.WriteLine(dt.ToString("y"));
Console.WriteLine(dt.ToString("Y"));
Console.WriteLine(dt.ToString("r"));
Console.WriteLine(dt.ToString("R"));
Console.WriteLine(dt.ToString("s"));
Console.WriteLine(dt.ToString("u"));
Console.WriteLine("\n\n");
Console.WriteLine("{0:d }", dt);
Console.WriteLine("{0:dd }", dt);
Console.WriteLine("{0:ddd }", dt);
Console.WriteLine("{0:dddd }", dt);
Console.WriteLine("{0:M }", dt);
Console.WriteLine("{0:MM }", dt);
Console.WriteLine("{0:MMM }", dt);
Console.WriteLine("{0:MMMM }", dt);
Console.WriteLine("{0:y }", dt);
Console.WriteLine("{0:yy }", dt);
Console.WriteLine("{0:yyy }", dt);
Console.WriteLine("{0:yyyy }", dt);
Console.WriteLine("{0:h }", dt);
Console.WriteLine("{0:hh }", dt);
Console.WriteLine("{0:H }", dt);
Console.WriteLine("{0:HH }", dt);
Console.WriteLine("{0:m }", dt);
Console.WriteLine("{0:mm }", dt);
Console.WriteLine("{0:s }", dt);
Console.WriteLine("{0:ss }", dt);
Console.WriteLine("\n\n");
Console.WriteLine("{0:f }", dt);
Console.WriteLine("{0:ff }", dt);
Console.WriteLine("{0:fff }", dt);
Console.WriteLine("{0:ffff }", dt);
Console.WriteLine("{0:F }", dt);
Console.WriteLine("\n\n");
Console.WriteLine("{0:z }", dt);
Console.WriteLine("{0:zz }", dt);
Console.WriteLine("{0:zzz }", dt);
Console.WriteLine("{0:t }", dt);
Console.WriteLine("{0:tt }", dt);
}