C#

Norris Chappell wrote:

IF NOT EXISTS (SELECT * FROM StaffTrackingFTEData) INSERT INTO StaffTrackingFTEData (...

IF NOT EXISTS (SELECT * FROM StaffTrackingFTEData WHERE SomeColumn = @SomeParameter And ...)

protected void SubmitButton_Click(object sender, EventArgs e)
           {

               const string Query = "IF NOT EXISTS (SELECT StaffTrackingID, Period FROM StaffTrackingFTEData where StaffTrackingID = @StaffTrackingID and Period = @Period) INSERT INTO StaffTrackingFTEData ([StaffTrackingID], [EstimateHours], [EstimateFTE], [ActualHours], [ActualFTE],[Comment], [CommentBy], [Period]) VALUES ((Select StaffTracking.ID From StaffTracking where StaffTracking.CATWResourceName = @Name), @Estimatehours, @EstimateFTE, @ActualHours, @ActualFTE, @Comment, @CommentBy, @Period)";
               SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLStaffingConn"].ConnectionString);

               using (SqlCommand command = new SqlCommand(Query, conn))
               {
                   conn.Open();
                   command.Parameters.AddWithValue("@StaffTrackingID", SqlDbType.Int).Direction = ParameterDirection.Output;
                   foreach (GridViewRow row in gvCATW.Rows)
                   {


                       command.Parameters.AddWithValue("@Name", ((TextBox)row.FindControl("txtName")).Text);
                       command.Parameters.AddWithValue("@EstimateHours", ((TextBox)row.FindControl("txtEstimateHours")).Text);
                       command.Parameters.AddWithValue("@EstimateFTE", ((TextBox)row.FindControl("txtEstimateFTE")).Text);
                       command.Parameters.AddWithValue("@ActualHours", ((TextBox)row.FindControl("txtHours")).Text);
                       command.Parameters.AddWithValue("@ActualFTE", ((Label)row.FindControl("Label1")).Text);
                       command.Parameters.AddWithValue("@Comment", ((TextBox)row.FindControl("txtComment")).Text);
                       command.Parameters.AddWithValue("@CommentBy", ((TextBox)row.FindControl("txtCommentBy")).Text);
                       command.Parameters.AddWithValue("@Period", ((TextBox)row.FindControl("txtPeriod")).Text);


                       command.ExecuteNonQuery();
                       command.Parameters.Clear();
                   }
               }

           }

const string Query = @"DECLARE @StaffTrackingID int;

SELECT @StaffTrackingID = ID FROM StaffTracking WHERE CATWResourceName = @Name;
If @@ROWCOUNT = 0 RAISERROR('No staff tracking record found for resource ''%s''.', 16, 1, @Name);

IF NOT EXISTS (SELECT 1 FROM StaffTrackingFTEData WHERE StaffTrackingID = @StaffTrackingID And Period = @Period) 
    INSERT INTO StaffTrackingFTEData ([StaffTrackingID], [EstimateHours], [EstimateFTE], [ActualHours], [ActualFTE],[Comment], [CommentBy], [Period]) 
    VALUES (@StaffTrackingID, @Estimatehours, @EstimateFTE, @ActualHours, @ActualFTE, @Comment, @CommentBy, @Period)
";

const string Query = @"DECLARE @StaffTrackingID int;

SELECT @StaffTrackingID = ID FROM StaffTracking WHERE CATWResourceName = @Name;

If @@ROWCOUNT != 0 And NOT EXISTS (SELECT 1 FROM StaffTrackingFTEData WHERE StaffTrackingID = @StaffTrackingID And Period = @Period)
    INSERT INTO StaffTrackingFTEData ([StaffTrackingID], [EstimateHours], [EstimateFTE], [ActualHours], [ActualFTE],[Comment], [CommentBy], [Period])
    VALUES (@StaffTrackingID, @Estimatehours, @EstimateFTE, @ActualHours, @ActualFTE, @Comment, @CommentBy, @Period)
";

<pre lang="c#"> protected void SubmitButton_Click(object sender, EventArgs e)
            {

                const string Query = "INSERT INTO StaffTrackingFTEData ([StaffTrackingID], [EstimateHours], [EstimateFTE], [ActualHours], [ActualFTE],[Comment], [CommentBy], [Period]) VALUES ((Select StaffTracking.ID From StaffTracking where StaffTracking.CATWResourceName = @Name), @Estimatehours, @EstimateFTE, @ActualHours, @ActualFTE, @Comment, @CommentBy, @Period)";
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLStaffingConn"].ConnectionString);

                using (SqlCommand command = new SqlCommand(Query, conn))
                {
                    conn.Open();
                    command.Parameters.AddWithValue("@StaffTrackingID", SqlDbType.Int).Direction = ParameterDirection.Output;
                    foreach (GridViewRow row in gvCATW.Rows)
                    {


                        command.Parameters.AddWithValue("@Name", ((TextBox)row.FindControl("txtName")).Text); 
.....
.....
.....

[DllImport("wininet.dll")]
private extern static IntPtr InternetConnect(IntPtr internet, string host, short port, string username, string password, int service, int flags, IntPtr context);

[DllImport("wininet.dll")]
private extern static bool FtpCommand(IntPtr connect, bool expectResponse, int flags, string command, IntPtr context, out IntPtr ftpCommand);

[DllImport("wininet.dll")]
private extern static bool IntPtr InternetCloseHandle(IntPtr connect);

public static bool ChangePermission(string uri, string folder, string userName, string password)
  {
   IntPtr context = IntPtr.Zero;
   IntPtr ftpCommand = IntPtr.Zero;
   IntPtr open = InternetOpen("Portal", 1, null, null, 0x10000000);
   IntPtr connected = InternetConnect(open, uri, 21, userName, password, 1, 0x08000000, context);
   if (!FtpCommand(connected, false, 0x00000001, "CWD " + folder, context, out ftpCommand)) return false;
   if (!FtpCommand(connected, false, 0x00000001, "SITE CHMOD 775 theFile.txt", context, out ftpCommand)) return false;
   bool connectedclose = InternetCloseHandle(connected);
   bool openclose = InternetCloseHandle(open);
   return (connectedclose && openclose)
}