Database

private static void OnCreated(object source, FileSystemEventArgs e)
{
    // Specify what is done when a file is changed, created, or deleted.
    // get the file's extension
    string strFileExt = (Path.GetExtension(e.FullPath) ?? string.Empty).ToLower();
    string directoryFullPath = Path.GetDirectoryName(e.FullPath);

    // filter file types
    if (Regex.IsMatch(strFileExt, @".txt|.csv", RegexOptions.IgnoreCase))
    {
        Package _package = null;
        Application app = new Application();
        string dtsPackPath = ConfigurationManager.AppSettings["SSISPackagePath"];
        _package = app.LoadPackage(dtsPackPath, null);

        _package.Connections["Flat File Connection Manager"].ConnectionString = e.FullPath;
        _package.Connections["Flat File Connection Manager 1"].ConnectionString = directoryFullPath + @"\Test" + DateTime.Now.Year.ToString()
            + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString()
                    + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString()
                    + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".txt";

        //_package.Variables["User::SourceFileName"].Value = e.FullPath;

        //_package.Variables["User::FileName"].Value = directoryFullPath + @"\Test" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString()
        //            + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString()
        //            + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".txt";

        var res = _package.Execute();

        if (res == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success)
        {
            string message = "Package Executed Successfully....";
        }
    }

    while (Console.Read() != 'q') ;
}

private static void OnCreated(object source, FileSystemEventArgs e)
{
    // Specify what is done when a file is changed, created, or deleted.
    // get the file's extension
    string strFileExt = (Path.GetExtension(e.FullPath) ?? string.Empty).ToLower();
    string directoryFullPath = Path.GetDirectoryName(e.FullPath);

    if (Regex.IsMatch(strFileExt, @".txt|.csv", RegexOptions.IgnoreCase))
    {
        Package _package = null;
        Application app = new Application();
        string dtsPackPath = ConfigurationManager.AppSettings["SSISPackagePath"];
        string sqlServerConnectionString = ConfigurationManager.AppSettings["SqlServerConnectionString"];
        _package = app.LoadPackage(dtsPackPath, null);

        _package.Connections["Flat File Connection Manager"].ConnectionString = e.FullPath;

        //_package.Variables["User::SourceFileName"].Value = e.FullPath;

        //_package.Variables["User::FileName"].Value = directoryFullPath + @"\Test" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString()
        //    + DateTime.Now.Day.ToString()
        //    + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString()
        //    + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".txt";

        _package.Connections["BSCWD725128.DTS_Testing"].ConnectionString = sqlServerConnectionString;

        var res = _package.Execute();

        if (res == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success)
        {
            string message = "Package Executed Successfully....";
        }

        //_package.Connections["Flat File Connection Manager 1"].ConnectionString = directoryFullPath + @"\Test" + DateTime.Now.Year.ToString()
        //    + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString()
        //            + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString()
        //            + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".txt";
    }

    while (Console.Read() != 'q') ;
}

ALTER PROCEDURE [EventPortion].[24Start]
 AS  

IF object_id('[IntegratedTest1].[EventPortion].[244Q1]') is not null 
DROP TABLE [IntegratedTest1].[EventPortion].[244Q1]

IF object_id('[IntegratedTest1].[EventPortion].[244Q2]') is not null 
DROP TABLE [IntegratedTest1].[EventPortion].[244Q2]

IF object_id('[IntegratedTest1].[EventPortion].[244Q3]') is not null 
DROP TABLE [IntegratedTest1].[EventPortion].[244Q3]


SELECT * Into [IntegratedTest1].[EventPortion].[244Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q1

While (object_id('[IntegratedTest1].[EventPortion].[244Q1]') is null)
Begin
WAITFOR DELAY '00:00:00.001';
End

Begin
WAITFOR DELAY '00:00:01.000';
End

SELECT * Into [IntegratedTest1].[EventPortion].[244Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q2


While (object_id('[IntegratedTest1].[EventPortion].[244Q2]') is null)
Begin
WAITFOR DELAY '00:00:00.001';
End
SELECT * Into [IntegratedTest1].[EventPortion].[244Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q3

ALTER PROCEDURE [EventPortion].[24Start]
 AS
DROP TABLE [IntegratedTest1].[EventPortion].[244Q1]
DROP TABLE [IntegratedTest1].[EventPortion].[244Q2]
DROP TABLE [IntegratedTest1].[EventPortion].[244Q3]
SELECT * Into [IntegratedTest1].[EventPortion].[244Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q1
SELECT * Into [IntegratedTest1].[EventPortion].[244Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q2
SELECT * Into [IntegratedTest1].[EventPortion].[244Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q3
DROP TABLE [IntegratedTest1].[EventPortion].[245Q1]
DROP TABLE [IntegratedTest1].[EventPortion].[245Q2]
DROP TABLE [IntegratedTest1].[EventPortion].[245Q3]
SELECT * Into [IntegratedTest1].[EventPortion].[245Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q1
SELECT * Into [IntegratedTest1].[EventPortion].[245Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q2
SELECT * Into [IntegratedTest1].[EventPortion].[245Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q3

private void ParseStartProcedure(string spText)
        {            
            spText = spText.Replace("\r", " ");            
            spText = spText.Replace("\t", " ");           
            spText = spText.Trim();

            // Take everything after the first "AS"
            string[] queryPieces = Regex.Split(spText.ToLower(), " as ");
            string[] queryLines = Regex.Split(queryPieces[1], "\n"); //split on the new line character

            for (int i = 0; i < queryLines.Length; i++)
            {
                if (queryLines[i] != "")
                {
                    string query = queryLines[i];
                    SqlCommand cmd = new SqlCommand(query, m_dbSettings.sqlMetadataDbConn);
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException ex)
                    {
                        //TODO: Add more robust error handling to handle errors other than "Failed to drop, etc.
                        string message = ex.Message;
                    }
                }
            }            
        }

Item(Id_Item,ItemNo,ItemName)
 Lot(Id_Lot,Item_Id,LotNo)
 Process(Id_Process, ProcessNo, ProcessName)
 Lot_Process(Id,Lot_Id,Process_Id)
 Material(Id,Process_Id,MaterialName, Quantity)

CREATE TABLE ps_data.test (
  Test_ID int(11) NOT NULL AUTO_INCREMENT,
  Idx int(11) NOT NULL,
  Grp int(11) DEFAULT NULL,
  PRIMARY KEY (Test_ID)
)
ENGINE = INNODB
AUTO_INCREMENT = 10
AVG_ROW_LENGTH = 1820
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(1, 1, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(2, 2, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(3, 3, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(4, 4, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(5, 5, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(6, 6, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(7, 7, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(8, 8, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(9, 9, 2);

UPDATE test
SET Idx=(SELECT COALESCE(MAX(tbl.Idx), 0)+1 FROM (SELECT * FROM test) tbl WHERE tbl.Grp=1)
WHERE Test_ID=1 

UPDATE test
SET Idx=(SELECT COALESCE(MAX(tbl.Idx), 0)+1 FROM (SELECT * FROM test WHERE Grp=1) tbl)
WHERE Test_ID=1 

UPDATE test t, (SELECT * FROM test WHERE Grp=1) tbl
SET t.Idx= (SELECT COALESCE(MAX(tbl.Idx), 0)+1)
WHERE t.Test_ID=1