System.Data.SqlClient.SqlParameterCollection.AddWithValue(string, object)

private void bindParams(SqlCommand cmd, Command command)
    {
      if (command.Parameters == null) return;
      var pName = "?";
      try
      {
        foreach(var par in command.Parameters)
        {
          pName = "?";
          var name = par.Name.NonBlank("parameter name");
          pName = name;
          var val = par.Value;

          if (val != null)
          {
            var tp = Command.Param.TryMap(par.Type);
            if (tp != null)
            {
              val = val.replacedtring().AsType(tp);
            }
          }
          cmd.Parameters.AddWithValue(name, val);
        }
      }
      catch(Exception error)
      {
        throw new ValidationException("Command parameter `{0}` cast error: {1}".Args(pName, error.ToMessageWithType()), error);
      }
    }

public void PopulateParameters(SqlCommand cmd, Query query)
          {
              foreach(var par in query.Where(p => p.HasValue))
                cmd.Parameters.AddWithValue(par.Name, par.Value);

              if (query.StoreKey!=null)
              {
              var where = GeneratorUtils.KeyToWhere(query.StoreKey, cmd.Parameters);
              cmd.CommandText += "\n WHERE \n {0}".Args( where );
              }

              CRUDGenerator.ConvertParameters(Store, cmd.Parameters);
          }

public async Task<bool> StoreTranscriptionAsync(
            Guid transcriptionId,
            string locale,
            string fileName,
            float approximateCost,
            SpeechTranscript speechTranscript)
        {
            if (speechTranscript == null)
            {
                throw new ArgumentNullException(nameof(speechTranscript));
            }

            try
            {
                Connection = new SqlConnection(DBConnectionString);
                Connection.Open();

                var query = "INSERT INTO dbo.Transcriptions (ID, Locale, Name, Source, Timestamp, Duration, DurationInSeconds, NumberOfChannels, ApproximateCost)" +
                    " VALUES (@id, @locale, @name, @source, @timestamp, @duration, @durationInSeconds, @numberOfChannels, @approximateCost)";

                using (var command = new SqlCommand(query, Connection))
                {
                    command.Parameters.AddWithValue("@id", transcriptionId);
                    command.Parameters.AddWithValue("@locale", locale);
                    command.Parameters.AddWithValue("@name", fileName);
                    command.Parameters.AddWithValue("@source", speechTranscript.Source);
                    command.Parameters.AddWithValue("@timestamp", speechTranscript.Timestamp);
                    command.Parameters.AddWithValue("@duration", speechTranscript.Duration ?? string.Empty);
                    command.Parameters.AddWithValue("@durationInSeconds", TimeSpan.FromTicks(speechTranscript.DurationInTicks).TotalSeconds);
                    command.Parameters.AddWithValue("@numberOfChannels", speechTranscript.CombinedRecognizedPhrases.Count());
                    command.Parameters.AddWithValue("@approximateCost", approximateCost);

                    var result = await command.ExecuteNonQueryAsync().ConfigureAwait(false);

                    if (result < 0)
                    {
                        Logger.LogInformation("Did not store json in Db, command did not update table");
                    }
                    else
                    {
                        var phrasesByChannel = speechTranscript.RecognizedPhrases.GroupBy(t => t.Channel);

                        foreach (var phrases in phrasesByChannel)
                        {
                            var channel = phrases.Key;
                            await StoreCombinedRecognizedPhrasesAsync(transcriptionId, channel, speechTranscript, phrases).ConfigureAwait(false);
                        }
                    }
                }

                Connection.Close();
            }
            catch (SqlException e)
            {
                Logger.LogInformation(e.ToString());
                return false;
            }

            return true;
        }

public bool update(Catagoriesbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to update Data in DAtabase
                string query = "UPDATE tbl_catagories set [email protected],[email protected],[email protected]_by,[email protected]_date WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                cmd.Parameters.AddWithValue("@catagory", u.catagory);
                cmd.Parameters.AddWithValue("@supplier", u.supplier);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }

            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public bool delete(Catagoriesbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to delete Data in DAtabase
                string query = "DELETE FROM tbl_catagories WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Sucessfull
                    issucess = false;
                }
            }

            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public bool exist(Catagoriesbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issuccess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to selecte Data from DAtabase
                string query = "select * from tbl_catagories where [email protected] AND [email protected] AND [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@catagory", u.catagory);
                cmd.Parameters.AddWithValue("@supplier", u.supplier);
                //Getting DAta from dAtabase
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //TO hold the data from database
                DataTable dt = new DataTable();
                //Database Connection Open
                conn.Open();
                //Fill Data in our DataTable
                adapter.Fill(dt);
                //Checking The rows in DataTable
                if (dt.Rows.Count > 0)
                {
                    //Data Exist
                    issuccess = true;
                }
                else
                {
                    //Data not exist
                    issuccess = false;
                }
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }
            return issuccess;
        }

public bool insert(invoicebll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to insert Data in DAtabase
                string query = "Insert into tbl_invoice(inv_no,customer_name,total_payable,paid_amount,discount,due_amount,change_amount,added_by,sales_date)Values(@inv_no,@customer_name,@total_payable,@paid_amount,@discount,@due_amount,@change_amount,@added_by,@sales_date)";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@inv_no", u.inv_no);
                cmd.Parameters.AddWithValue("@customer_name", u.customer_name);
                cmd.Parameters.AddWithValue("@total_payable", u.total_payable);
                cmd.Parameters.AddWithValue("@paid_amount", u.paid_amount);
                cmd.Parameters.AddWithValue("@discount", u.discount);
                cmd.Parameters.AddWithValue("@due_amount", u.due_amount);
                cmd.Parameters.AddWithValue("@change_amount", u.change_amount);
                cmd.Parameters.AddWithValue("@added_by", u.added_by);
                cmd.Parameters.AddWithValue("@sales_date", u.sales_date);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public bool login(loginbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issuccess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to check login
                string query = "select * from tbl_users where [email protected]_name AND [email protected] AND [email protected]_type";
                //Creating SQL Command to preplaced value
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@user_name", u.user_name);
                cmd.Parameters.AddWithValue("@preplacedword", u.preplacedword);
                cmd.Parameters.AddWithValue("@user_type", u.user_type);
                //Getting DAta from dAtabase
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //TO hold the data from database 
                DataTable dt = new DataTable();
                //Database Connection Open
                conn.Open();
                //Fill Data in our DataTable
                adapter.Fill(dt);
                //Checking The rows in DataTable 
                if (dt.Rows.Count > 0)
                {
                    //Login Sucessful
                    issuccess = true;
                }
                else
                {
                    //Login Failed
                    issuccess = false;
                }
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issuccess;
        }

public bool insert(Manage_Productsbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to insert Data in DAtabase
                string query = "Insert into tbl_stock(product_name,colour_code,supplier,catagory,purchase_price,retail_price,type,quanreplacedy,added_by,added_date)Values(@product_name,@colour_code,@supplier,@catagory,@purchase_price,@retail_price,@type,@quanreplacedy,@added_by,@added_date)";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@product_name", u.product_name);
                cmd.Parameters.AddWithValue("@colour_code", u.colour_code);
                cmd.Parameters.AddWithValue("@supplier" , u.supplier);
                cmd.Parameters.AddWithValue("@catagory", u.catagory);     
                cmd.Parameters.AddWithValue("@purchase_price", u.purchase_price);
                cmd.Parameters.AddWithValue("@retail_price", u.retail_price);
                cmd.Parameters.AddWithValue("@type", u.type);
                cmd.Parameters.AddWithValue("@quanreplacedy", u.quanreplacedy);
                cmd.Parameters.AddWithValue("@added_by", u.added_by);
                cmd.Parameters.AddWithValue("@added_date", u.added_date);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public bool exist(Manage_Productsbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issuccess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to selecte Data from DAtabase
                string query = "select * from tbl_stock where [email protected]_name AND [email protected] AND [email protected] AND [email protected]_code AND [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@type", u.type);
                cmd.Parameters.AddWithValue("@product_name", u.product_name);
                cmd.Parameters.AddWithValue("@supplier", u.supplier);
                cmd.Parameters.AddWithValue("@catagory", u.catagory);
                cmd.Parameters.AddWithValue("@colour_code", u.colour_code);
                //Getting DAta from dAtabase
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //TO hold the data from database
                DataTable dt = new DataTable();
                //Database Connection Open
                conn.Open();
                //Fill Data in our DataTable
                adapter.Fill(dt);
                //Checking The rows in DataTable
                if (dt.Rows.Count > 0)
                {
                    //Data Exist
                    issuccess = true;
                }
                else
                {
                    //Data not exist
                    issuccess = false;
                }


            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issuccess;
        }

public bool insert(product_detailsbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to insert Data in DAtabase
                string query = "Insert into tbl_productdetails(product_id,product_name,price,quanreplacedy,discount,total,purchase_price,inv_no,type,added_by,added_date,code)Values(@product_id,@product_name,@price,@quanreplacedy,@discount,@total,@purchase_price,@inv_no,@type,@added_by,@added_date,@code)";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@product_id", u.product_id);
                cmd.Parameters.AddWithValue("@product_name", u.product_name);
                cmd.Parameters.AddWithValue("@price", u.price);
                cmd.Parameters.AddWithValue("@quanreplacedy", u.quanreplacedy);
                cmd.Parameters.AddWithValue("@discount", u.discount);
                cmd.Parameters.AddWithValue("@total", u.total);
                cmd.Parameters.AddWithValue("@purchase_price", u.purchase_price);
                cmd.Parameters.AddWithValue("@inv_no", u.inv_no);
                cmd.Parameters.AddWithValue("@type", u.type);
                cmd.Parameters.AddWithValue("@added_by", u.added_by);
                cmd.Parameters.AddWithValue("@added_date", u.added_date);
                cmd.Parameters.AddWithValue("@code", u.code);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public bool update(product_detailsbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to insert Data in DAtabase
                string query = "UPDATE tbl_productdetails set [email protected]_id,[email protected]_name,[email protected],[email protected],[email protected],[email protected],[email protected]_price,[email protected]_no,[email protected],[email protected]_by,[email protected]_date,[email protected] WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                cmd.Parameters.AddWithValue("@product_id", u.product_id);
                cmd.Parameters.AddWithValue("@product_name", u.product_name);
                cmd.Parameters.AddWithValue("@price", u.price);
                cmd.Parameters.AddWithValue("@quanreplacedy", u.quanreplacedy);
                cmd.Parameters.AddWithValue("@discount", u.discount);
                cmd.Parameters.AddWithValue("@total", u.total);
                cmd.Parameters.AddWithValue("@purchase_price", u.purchase_price);
                cmd.Parameters.AddWithValue("@inv_no", u.inv_no);
                cmd.Parameters.AddWithValue("@type", u.type);
                cmd.Parameters.AddWithValue("@added_by", u.added_by);
                cmd.Parameters.AddWithValue("@added_date", u.added_date);
                cmd.Parameters.AddWithValue("@code", u.code);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int rows = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (rows > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public bool insert(Catagoriesbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to insert Data in DAtabase
                string query = "Insert into tbl_catagories(catagory,supplier,added_by,added_date)Values(@catagory,@supplier,@added_by,@added_date)";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@catagory", u.catagory);
                cmd.Parameters.AddWithValue("@supplier", u.supplier);
                cmd.Parameters.AddWithValue("@added_by", u.added_by);
                cmd.Parameters.AddWithValue("@added_date", u.added_date);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }
            return issucess;
        }

public bool delete(invoicebll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to delete Data in DAtabase
                string query = "DELETE FROM tbl_invoice WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }

            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public bool update(invoicebll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to insert Data in DAtabase
                string query = "UPDATE tbl_invoice set [email protected]_no, [email protected]_name,[email protected]_payable,[email protected]_amount,[email protected],[email protected]_amount,[email protected]_amount,[email protected]_by,[email protected]_date WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                cmd.Parameters.AddWithValue("@inv_no", u.inv_no);
                cmd.Parameters.AddWithValue("@customer_name", u.customer_name);
                cmd.Parameters.AddWithValue("@total_payable", u.total_payable);
                cmd.Parameters.AddWithValue("@paid_amount", u.paid_amount);
                cmd.Parameters.AddWithValue("@discount", u.discount);
                cmd.Parameters.AddWithValue("@due_amount", u.due_amount);
                cmd.Parameters.AddWithValue("@change_amount", u.change_amount);
                cmd.Parameters.AddWithValue("@added_by", u.added_by);
                cmd.Parameters.AddWithValue("@sales_date", u.sales_date);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public bool update(suppliersbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to update Data in DAtabase
                string query = "UPDATE tbl_suppliers set [email protected]_name,[email protected],[email protected],[email protected],[email protected]_no WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                cmd.Parameters.AddWithValue("@user_name", u.user_name);
                cmd.Parameters.AddWithValue("@email", u.email);
                cmd.Parameters.AddWithValue("@company", u.company);
                cmd.Parameters.AddWithValue("@adress", u.adress);
                cmd.Parameters.AddWithValue("@phone_no", u.phone_no);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }

            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public bool insert(usersbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to insert Data in DAtabase
                string query = "Insert into tbl_users(user_name,user_type,preplacedword,email,cnic,adress,phone_no,added_by,added_date)Values(@user_name,@user_type,@preplacedword,@email,@cnic,@adress,@phone_no,@added_by,@added_date)";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@user_name", u.user_name);
                cmd.Parameters.AddWithValue("@user_type", u.user_type);
                cmd.Parameters.AddWithValue("@preplacedword", u.preplacedword);
                cmd.Parameters.AddWithValue("@email", u.email);
                cmd.Parameters.AddWithValue("@cnic", u.cnic);
                cmd.Parameters.AddWithValue("@adress", u.adress);
                cmd.Parameters.AddWithValue("@phone_no", u.phone_no);
                cmd.Parameters.AddWithValue("@added_by", u.added_by);
                cmd.Parameters.AddWithValue("@added_date", u.added_date);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public bool update(usersbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to update Data in DAtabase
                string query = "UPDATE tbl_users set [email protected]_name,[email protected]_type,[email protected],[email protected],[email protected],[email protected],[email protected]_no WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                cmd.Parameters.AddWithValue("@user_name", u.user_name);
                cmd.Parameters.AddWithValue("@user_type", u.user_type);
                cmd.Parameters.AddWithValue("@preplacedword", u.preplacedword);
                cmd.Parameters.AddWithValue("@email", u.email);
                cmd.Parameters.AddWithValue("@cnic", u.cnic);
                cmd.Parameters.AddWithValue("@adress", u.adress);
                cmd.Parameters.AddWithValue("@phone_no", u.phone_no);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }

            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public bool exist(usersbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issuccess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to selecte Data from DAtabase
                string query = "select * from tbl_users where [email protected]_name AND [email protected] AND [email protected] AND [email protected]_no ";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@user_name", u.user_name);
                cmd.Parameters.AddWithValue("@cnic", u.cnic);
                cmd.Parameters.AddWithValue("@email", u.email);
                cmd.Parameters.AddWithValue("@phone_no", u.phone_no);
                //Getting DAta from dAtabase
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //TO hold the data from database
                DataTable dt = new DataTable();
                //Database Connection Open
                conn.Open();
                //Fill Data in our DataTable
                adapter.Fill(dt);
                //Checking The rows in DataTable 
                if (dt.Rows.Count > 0)
                {
                    //Data Exist
                    issuccess = true;
                }
                else
                {
                    //Data not exist
                    issuccess = false;
                }
                
            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issuccess;
        }

public bool delete(usersbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to delete Data in DAtabase
                string query = "DELETE FROM tbl_users WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }

            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public bool update_(Manage_Productsbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to update Data in DAtabase
                string query = "UPDATE tbl_stock set [email protected]_name,[email protected]_code,[email protected],[email protected],[email protected]_price,[email protected]_price,[email protected],[email protected] WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                cmd.Parameters.AddWithValue("@product_name", u.product_name);
                cmd.Parameters.AddWithValue("@colour_code", u.colour_code);
                cmd.Parameters.AddWithValue("@supplier", u.supplier);
                cmd.Parameters.AddWithValue("@catagory", u.catagory);
                cmd.Parameters.AddWithValue("@purchase_price", u.purchase_price);
                cmd.Parameters.AddWithValue("@retail_price", u.retail_price);
                cmd.Parameters.AddWithValue("@type", u.type); 
                cmd.Parameters.AddWithValue("@quanreplacedy", u.quanreplacedy);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }

            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public bool delete(Manage_Productsbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to delete Data in DAtabase
                string query = "DELETE FROM tbl_stock WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }

            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public bool UpdateQuanreplacedy(int id, decimal quanreplacedy)
        {
            
            //Create a Boolean Variable and Set its value to false
            bool success = false;

            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);

            try
            {
                //Write the SQL Query to Update Qty
                string sql = "UPDATE tbl_stock SET [email protected] WHERE [email protected]";

                //Create SQL Command to Preplaced the calue into Queyr
                SqlCommand cmd = new SqlCommand(sql, conn);
                //Preplaceding the VAlue trhough parameters
                cmd.Parameters.AddWithValue("@quanreplacedy", quanreplacedy);
                cmd.Parameters.AddWithValue("@id", id);

                //Open Database Connection
                conn.Open();

                //Create Int Variable and Check whether the query is executed Successfully or not
                int rows = cmd.ExecuteNonQuery();
                //Lets check if the query is executed Successfully or not
                if (rows > 0)
                {
                    //Query Executed Successfully
                    success = true;
                }
                else
                {
                    //Failed to Execute Query
                    success = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return success;
        }

public bool UpdateQuanreplacedy(int id, decimal quanreplacedy)
        {

            //Create a Boolean Variable and Set its value to false
            bool success = false;

            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);

            try
            {
                //Write the SQL Query to Update Qty
                string sql = "UPDATE tbl_productdetails SET [email protected] WHERE [email protected]";

                //Create SQL Command to Preplaced the calue into Queyr
                SqlCommand cmd = new SqlCommand(sql, conn);
                //Preplaceding the VAlue trhough parameters
                cmd.Parameters.AddWithValue("@quanreplacedy", quanreplacedy);
                cmd.Parameters.AddWithValue("@id", id);

                //Open Database Connection
                conn.Open();

                //Create Int Variable and Check whether the query is executed Successfully or not
                int rows = cmd.ExecuteNonQuery();
                //Lets check if the query is executed Successfully or not
                if (rows > 0)
                {
                    //Query Executed Successfully
                    success = true;
                }
                else
                {
                    //Failed to Execute Query
                    success = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return success;
        }

public bool delete(product_detailsbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to delete Data in DAtabase
                string query = "DELETE FROM tbl_productdetails WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }

            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public bool insert(suppliersbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to insert Data in DAtabase
                string query = "Insert into tbl_suppliers(user_name,email,company,adress,phone_no,added_by,added_date)Values(@user_name,@email,@company,@adress,@phone_no,@added_by,@added_date)";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@user_name", u.user_name);
                cmd.Parameters.AddWithValue("@email", u.email);
                cmd.Parameters.AddWithValue("@company", u.company);
                cmd.Parameters.AddWithValue("@adress", u.adress);
                cmd.Parameters.AddWithValue("@phone_no", u.phone_no);
                cmd.Parameters.AddWithValue("@added_by", u.added_by);
                cmd.Parameters.AddWithValue("@added_date", u.added_date);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }


            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public bool delete(suppliersbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issucess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to delete Data in DAtabase
                string query = "DELETE FROM tbl_suppliers WHERE [email protected]";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@id", u.id);
                //Database Connection Open
                conn.Open();
                //To execute non query
                int row = cmd.ExecuteNonQuery();
                //If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
                if (row > 0)
                {
                    //Query Sucessfull
                    issucess = true;
                }
                else
                {
                    //Query Failed
                    issucess = false;
                }
            }

            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issucess;
        }

public bool exist(suppliersbll u)
        {
            //Create a boolean variable and set its value to false and return it
            bool issuccess = false;
            //MEthod to connect Database
            connclreplaced c = new connclreplaced();
            SqlConnection conn = new SqlConnection(c.connection);
            try
            {
                //SQL Query to selecte Data from DAtabase
                string query = "select * from tbl_suppliers where [email protected] AND [email protected]_no";
                //For Executing Command
                SqlCommand cmd = new SqlCommand(query, conn);
                //Preplaceding Values to the Variables
                cmd.Parameters.AddWithValue("@email", u.email);
                cmd.Parameters.AddWithValue("@phone_no", u.phone_no);
                //Getting DAta from dAtabase
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //TO hold the data from database
                DataTable dt = new DataTable();
                //Database Connection Open
                conn.Open();
                //Fill Data in our DataTable
                adapter.Fill(dt);
                //Checking The rows in DataTable 
                if (dt.Rows.Count > 0)
                {
                    //Data Exist
                    issuccess = true;
                }
                else
                {
                    //Data not exist
                    issuccess = false;
                }


            }
            catch (Exception ex)
            {
                //Throw Message if any error occurs
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Closing Connection
                conn.Close();
            }

            return issuccess;
        }

private static int LogRequest(string origin, string operation, string request, string location)
      {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
          connection.Open();

          using (var command = connection.CreateCommand())
          {
            command.CommandText = "insert into [Requests] (Origin, Operation, Location, Request) values (@origin, @operation, @location, @request); select @@IDENreplacedY;";
            command.Parameters.AddWithValue("origin", origin);
            command.Parameters.AddWithValue("operation", operation);
            command.Parameters.AddWithValue("location", location);
            command.Parameters.AddWithValue("request", request);
            var result = command.ExecuteScalar();
            return Convert.ToInt32(result);
          }
        }
      }

private static void LogResponse(string response, int id)
      {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
          connection.Open();

          using (var command = connection.CreateCommand())
          {
            command.CommandText = "update [Requests] set Response = @response where ID = @id";
            command.Parameters.AddWithValue("response", response);
            command.Parameters.AddWithValue("id", id);
            command.ExecuteNonQuery();
          }
        }
      }

public static DataTable RunSPReturnDataTable(string spName, Paras paras, SqlConnection conn)
		{
			try
			{
				SqlCommand salesCMD = new SqlCommand(spName, conn);
				salesCMD.CommandType = CommandType.StoredProcedure;			 

				/// 加上他们的参数			
				foreach(Para para in paras)
				{
                    SqlParameter myParm = salesCMD.Parameters.AddWithValue(para.ParaName, para.DAType);
					myParm.Value = para.val;
				}

				//selectCMD.CommandTimeout =60;
				SqlDataAdapter sda = new SqlDataAdapter(salesCMD);
				if (conn.State==System.Data.ConnectionState.Closed)				 
					conn.Open();
				DataTable dt = new DataTable();
				sda.Fill(dt);
				sda.Dispose();					
				return dt;
			}
			catch(System.Exception ex)
			{
				throw ex;
			}
		}

private static void ModifyTableContent1()
        {
            CheckValues.Add(ChangeType.Insert.ToString(), new Tuple<NVarcharMaxAndVarcharMaxTypeModel, NVarcharMaxAndVarcharMaxTypeModel>(new NVarcharMaxAndVarcharMaxTypeModel { VarcharMaxColumn = new string('*', 6000), NvarcharMaxColumn = new string('*', 8000) }, new NVarcharMaxAndVarcharMaxTypeModel()));
            CheckValues.Add(ChangeType.Update.ToString(), new Tuple<NVarcharMaxAndVarcharMaxTypeModel, NVarcharMaxAndVarcharMaxTypeModel>(new NVarcharMaxAndVarcharMaxTypeModel { VarcharMaxColumn = "111", NvarcharMaxColumn = "new byte[] { 1, 2, 3, 4, 5, 6 }" }, new NVarcharMaxAndVarcharMaxTypeModel()));
            CheckValues.Add(ChangeType.Delete.ToString(), new Tuple<NVarcharMaxAndVarcharMaxTypeModel, NVarcharMaxAndVarcharMaxTypeModel>(new NVarcharMaxAndVarcharMaxTypeModel { VarcharMaxColumn = "111", NvarcharMaxColumn = "new byte[] { 1, 2, 3, 4, 5, 6 }" }, new NVarcharMaxAndVarcharMaxTypeModel()));

            using (var sqlConnection = new SqlConnection(ConnectionStringForTestUser))
            {
                sqlConnection.Open();

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"INSERT INTO [{TableName}] ([varcharMAXColumn], [nvarcharMAXColumn]) VALUES(@varcharMAXColumn, @nvarcharMAXColumn)";
                    sqlCommand.Parameters.AddWithValue("@varcharMAXColumn", CheckValues[ChangeType.Insert.ToString()].Item1.VarcharMaxColumn);
                    sqlCommand.Parameters.AddWithValue("@nvarcharMAXColumn", CheckValues[ChangeType.Insert.ToString()].Item1.NvarcharMaxColumn);
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"UPDATE [{TableName}] SET [varcharMAXColumn] = @varcharMAXColumn, [nvarcharMAXColumn] = @nvarcharMAXColumn";
                    sqlCommand.Parameters.AddWithValue("@varcharMAXColumn", CheckValues[ChangeType.Update.ToString()].Item1.VarcharMaxColumn);
                    sqlCommand.Parameters.AddWithValue("@nvarcharMAXColumn", CheckValues[ChangeType.Update.ToString()].Item1.NvarcharMaxColumn);
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"DELETE FROM [{TableName}]";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

private static void ModifyTableContent1()
        {
            CheckValues.Add(ChangeType.Insert.ToString(), 
                new Tuple<NVarcharMaxAndVarcharMaxType2Model, NVarcharMaxAndVarcharMaxType2Model>(new NVarcharMaxAndVarcharMaxType2Model
                { VarcharMaxColumn = new string('¢', 6000), NvarcharMaxColumn = "мы фантастические" }, new NVarcharMaxAndVarcharMaxType2Model()));
            
            using (var sqlConnection = new SqlConnection(ConnectionStringForTestUser))
            {
                sqlConnection.Open();

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"INSERT INTO [{TableName}] ([varcharMAXColumn], [nvarcharMAXColumn]) VALUES(@varcharMAXColumn, @nvarcharMAXColumn)";
                    sqlCommand.Parameters.AddWithValue("@varcharMAXColumn", CheckValues[ChangeType.Insert.ToString()].Item1.VarcharMaxColumn);
                    sqlCommand.Parameters.AddWithValue("@nvarcharMAXColumn", CheckValues[ChangeType.Insert.ToString()].Item1.NvarcharMaxColumn);
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

private void ModifyTableContent()
        {
            var yesterday = DateTime.Now.AddDays(-3);

            using (var sqlConnection = new SqlConnection(ConnectionStringForTestUser))
            {
                sqlConnection.Open();

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"INSERT INTO [{TableName}] ([Id], [Start]) VALUES (1, @today)";
                    sqlCommand.Parameters.AddWithValue("@today", _now);
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.Parameters.Clear();
                    sqlCommand.CommandText = $"INSERT INTO [{TableName}] ([Id], [Start]) VALUES (2, @yesterday)";
                    sqlCommand.Parameters.AddWithValue("@yesterday", yesterday);
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"DELETE from [{TableName}]";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

private static void ModifyTableContent1()
        {
            using (var sqlConnection = new SqlConnection(ConnectionStringForTestUser))
            {
                sqlConnection.Open();

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"INSERT INTO [{TableName}] ([VarcharMAXColumn], [NvarcharMAXColumn]) VALUES(@varcharMAXColumn, @nvarcharMAXColumn)";
                    sqlCommand.Parameters.AddWithValue("@varcharMAXColumn", _checkValues[ChangeType.Insert.ToString()].Item1.VarcharMaxColumn);
                    sqlCommand.Parameters.AddWithValue("@nvarcharMAXColumn", _checkValues[ChangeType.Insert.ToString()].Item1.NvarcharMaxColumn);
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.Parameters.Clear();
                    sqlCommand.CommandText = $"UPDATE [{TableName}] SET [VarcharMAXColumn] = @varcharMAXColumn, [NvarcharMAXColumn] = @nvarcharMAXColumn";
                    sqlCommand.Parameters.AddWithValue("@varcharMAXColumn", _checkValues[ChangeType.Update.ToString()].Item1.VarcharMaxColumn);
                    sqlCommand.Parameters.AddWithValue("@nvarcharMAXColumn", _checkValues[ChangeType.Update.ToString()].Item1.NvarcharMaxColumn);
                    sqlCommand.ExecuteNonQuery();
                }

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.Parameters.Clear();
                    sqlCommand.CommandText = $"DELETE FROM [{TableName}]";
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

private static void ModifyTableContent1()
        {
            CheckValues.Add(ChangeType.Insert.ToString(),
                new Tuple<NVarcharMaxAndVarcharMaxModel2, NVarcharMaxAndVarcharMaxModel2>(new NVarcharMaxAndVarcharMaxModel2
                { varcharMAXColumn = new string('¢', 6000), nvarcharMAXColumn = "мы фантастические" }, new NVarcharMaxAndVarcharMaxModel2()));

            using (var sqlConnection = new SqlConnection(ConnectionString))
            {
                sqlConnection.Open();

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"INSERT INTO [{TableName}] ([varcharMAXColumn], [nvarcharMAXColumn]) VALUES(@varcharMAXColumn, @nvarcharMAXColumn)";
                    sqlCommand.Parameters.AddWithValue("@varcharMAXColumn", CheckValues[ChangeType.Insert.ToString()].Item1.varcharMAXColumn);
                    sqlCommand.Parameters.AddWithValue("@nvarcharMAXColumn", CheckValues[ChangeType.Insert.ToString()].Item1.nvarcharMAXColumn);
                    sqlCommand.ExecuteNonQuery();
                }

                Thread.Sleep(1000);
            }
        }

private static void ModifyTableContent1()
        {
            CheckValues.Add(ChangeType.Insert.ToString(), new Tuple<XmlNVarcharMaxAndVarcharMaxModel, XmlNVarcharMaxAndVarcharMaxModel>(new XmlNVarcharMaxAndVarcharMaxModel { varcharMAXColumn = new string('*', 6000), nvarcharMAXColumn = new string('*', 8000) }, new XmlNVarcharMaxAndVarcharMaxModel()));
            CheckValues.Add(ChangeType.Update.ToString(), new Tuple<XmlNVarcharMaxAndVarcharMaxModel, XmlNVarcharMaxAndVarcharMaxModel>(new XmlNVarcharMaxAndVarcharMaxModel { varcharMAXColumn = "111", nvarcharMAXColumn = "new byte[] { 1, 2, 3, 4, 5, 6 }" }, new XmlNVarcharMaxAndVarcharMaxModel()));
            CheckValues.Add(ChangeType.Delete.ToString(), new Tuple<XmlNVarcharMaxAndVarcharMaxModel, XmlNVarcharMaxAndVarcharMaxModel>(new XmlNVarcharMaxAndVarcharMaxModel { varcharMAXColumn = "111", nvarcharMAXColumn = "new byte[] { 1, 2, 3, 4, 5, 6 }" }, new XmlNVarcharMaxAndVarcharMaxModel()));

            using (var sqlConnection = new SqlConnection(ConnectionString))
            {
                sqlConnection.Open();

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"INSERT INTO [{TableName}] ([varcharMAXColumn], [nvarcharMAXColumn]) VALUES(@varcharMAXColumn, @nvarcharMAXColumn)";
                    sqlCommand.Parameters.AddWithValue("@varcharMAXColumn", CheckValues[ChangeType.Insert.ToString()].Item1.varcharMAXColumn);
                    sqlCommand.Parameters.AddWithValue("@nvarcharMAXColumn", CheckValues[ChangeType.Insert.ToString()].Item1.nvarcharMAXColumn);
                    sqlCommand.ExecuteNonQuery();
                }

                Thread.Sleep(1000);

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"UPDATE [{TableName}] SET [varcharMAXColumn] = @varcharMAXColumn, [nvarcharMAXColumn] = @nvarcharMAXColumn";
                    sqlCommand.Parameters.AddWithValue("@varcharMAXColumn", CheckValues[ChangeType.Update.ToString()].Item1.varcharMAXColumn);
                    sqlCommand.Parameters.AddWithValue("@nvarcharMAXColumn", CheckValues[ChangeType.Update.ToString()].Item1.nvarcharMAXColumn);
                    sqlCommand.ExecuteNonQuery();
                }

                Thread.Sleep(1000);

                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = $"DELETE FROM [{TableName}]";
                    sqlCommand.ExecuteNonQuery();
                }

                Thread.Sleep(1000);
            }
        }

[Transactional(TransactionalTypes.TransactionScope)]
        private void Child_Update()
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("DeepLoad"))
            {
                using (var cmd = new SqlCommand("UpdateB02Level1", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Level_1_ID", ReadProperty(Level_1_IDProperty)).DbType = DbType.Int32;
                    cmd.Parameters.AddWithValue("@Level_1_Name", ReadProperty(Level_1_NameProperty)).DbType = DbType.String;
                    var args = new DataPortalHookArgs(cmd);
                    OnUpdatePre(args);
                    cmd.ExecuteNonQuery();
                    OnUpdatePost(args);
                }
                FieldManager.UpdateChildren(this);
            }
        }

[Transactional(TransactionalTypes.TransactionScope)]
        private void Child_DeleteSelf()
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("DeepLoad"))
            {
                // flushes all pending data operations
                FieldManager.UpdateChildren(this);
                using (var cmd = new SqlCommand("DeleteB02Level1", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Level_1_ID", ReadProperty(Level_1_IDProperty)).DbType = DbType.Int32;
                    var args = new DataPortalHookArgs(cmd);
                    OnDeletePre(args);
                    cmd.ExecuteNonQuery();
                    OnDeletePost(args);
                }
            }
            // removes all previous references to children
            LoadProperty(B03Level11SingleObjectProperty, DataPortal.CreateChild<B03Level11Child>());
            LoadProperty(B03Level11ASingleObjectProperty, DataPortal.CreateChild<B03Level11ReChild>());
            LoadProperty(B03Level11ObjectsProperty, DataPortal.CreateChild<B03Level11Coll>());
        }

[Transactional(TransactionalTypes.TransactionScope)]
        private void Child_Update(B02Level1 parent)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("DeepLoad"))
            {
                using (var cmd = new SqlCommand("UpdateB03Level11Child", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Level_1_ID", parent.Level_1_ID).DbType = DbType.Int32;
                    cmd.Parameters.AddWithValue("@Level_1_1_Child_Name", ReadProperty(Level_1_1_Child_NameProperty)).DbType = DbType.String;
                    var args = new DataPortalHookArgs(cmd);
                    OnUpdatePre(args);
                    cmd.ExecuteNonQuery();
                    OnUpdatePost(args);
                }
            }
        }

[Transactional(TransactionalTypes.TransactionScope)]
        private void Child_DeleteSelf(B02Level1 parent)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("DeepLoad"))
            {
                using (var cmd = new SqlCommand("DeleteB03Level11Child", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Level_1_ID", parent.Level_1_ID).DbType = DbType.Int32;
                    var args = new DataPortalHookArgs(cmd);
                    OnDeletePre(args);
                    cmd.ExecuteNonQuery();
                    OnDeletePost(args);
                }
            }
        }

[Transactional(TransactionalTypes.TransactionScope)]
        private void Child_DeleteSelf(B04Level11 parent)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("DeepLoad"))
            {
                using (var cmd = new SqlCommand("DeleteB05Level111Child", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Level_1_1_ID", parent.Level_1_1_ID).DbType = DbType.Int32;
                    var args = new DataPortalHookArgs(cmd);
                    OnDeletePre(args);
                    cmd.ExecuteNonQuery();
                    OnDeletePost(args);
                }
            }
        }

[Transactional(TransactionalTypes.TransactionScope)]
        private void Child_Insert(B04Level11 parent)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("DeepLoad"))
            {
                using (var cmd = new SqlCommand("AddB05Level111ReChild", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Level_1_1_ID", parent.Level_1_1_ID).DbType = DbType.Int32;
                    cmd.Parameters.AddWithValue("@Level_1_1_1_Child_Name", ReadProperty(Level_1_1_1_Child_NameProperty)).DbType = DbType.String;
                    var args = new DataPortalHookArgs(cmd);
                    OnInsertPre(args);
                    cmd.ExecuteNonQuery();
                    OnInsertPost(args);
                }
            }
        }

[Transactional(TransactionalTypes.TransactionScope)]
        private void Child_Update(B04Level11 parent)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("DeepLoad"))
            {
                using (var cmd = new SqlCommand("UpdateB05Level111ReChild", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Level_1_1_ID", parent.Level_1_1_ID).DbType = DbType.Int32;
                    cmd.Parameters.AddWithValue("@Level_1_1_1_Child_Name", ReadProperty(Level_1_1_1_Child_NameProperty)).DbType = DbType.String;
                    var args = new DataPortalHookArgs(cmd);
                    OnUpdatePre(args);
                    cmd.ExecuteNonQuery();
                    OnUpdatePost(args);
                }
            }
        }

[Transactional(TransactionalTypes.TransactionScope)]
        private void Child_Insert(B06Level111 parent)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("DeepLoad"))
            {
                using (var cmd = new SqlCommand("AddB07Level1111Child", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Level_1_1_1_ID", parent.Level_1_1_1_ID).DbType = DbType.Int32;
                    cmd.Parameters.AddWithValue("@Level_1_1_1_1_Child_Name", ReadProperty(Level_1_1_1_1_Child_NameProperty)).DbType = DbType.String;
                    var args = new DataPortalHookArgs(cmd);
                    OnInsertPre(args);
                    cmd.ExecuteNonQuery();
                    OnInsertPost(args);
                }
            }
        }

[Transactional(TransactionalTypes.TransactionScope)]
        private void Child_Update(B06Level111 parent)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("DeepLoad"))
            {
                using (var cmd = new SqlCommand("UpdateB07Level1111Child", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Level_1_1_1_ID", parent.Level_1_1_1_ID).DbType = DbType.Int32;
                    cmd.Parameters.AddWithValue("@Level_1_1_1_1_Child_Name", ReadProperty(Level_1_1_1_1_Child_NameProperty)).DbType = DbType.String;
                    var args = new DataPortalHookArgs(cmd);
                    OnUpdatePre(args);
                    cmd.ExecuteNonQuery();
                    OnUpdatePost(args);
                }
            }
        }

[Transactional(TransactionalTypes.TransactionScope)]
        private void Child_DeleteSelf(B06Level111 parent)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("DeepLoad"))
            {
                using (var cmd = new SqlCommand("DeleteB07Level1111Child", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Level_1_1_1_ID", parent.Level_1_1_1_ID).DbType = DbType.Int32;
                    var args = new DataPortalHookArgs(cmd);
                    OnDeletePre(args);
                    cmd.ExecuteNonQuery();
                    OnDeletePost(args);
                }
            }
        }

protected override ReadOnlyList<IParameter> GetParameters(IDbConnection cn)
        {
            var dt = new DBStructure.INFORMATION_SCHEMA_PARAMETERSDataTable();
            using (var cmd = (SqlCommand)cn.CreateCommand())
            {
                var sb = new StringBuilder();
                sb.AppendLine(Properties.Resources.SqlServerGetParameters);
                sb.AppendLine("WHERE");
                if (!string.IsNullOrEmpty(ObjectCatalog))
                {
                    sb.Append("SPECIFIC_CATALOG = @Catalog");
                    cmd.Parameters.AddWithValue("@Catalog", ObjectCatalog);
                }
                if (!string.IsNullOrEmpty(ObjectSchema))
                {
                    sb.Append("SPECIFIC_SCHEMA = @Schema");
                    cmd.Parameters.AddWithValue("@Schema", ObjectSchema);
                }
                sb.Append("SPECIFIC_NAME = @Name");
                cmd.Parameters.AddWithValue("@Name", ObjectName);

                cmd.CommandText = sb.ToString();
                using (var da = new SqlDataAdapter(cmd))
                {
                    da.Fill(dt);
                }
            }
            var rows = new DBStructure.INFORMATION_SCHEMA_PARAMETERSRow[dt.Count];
            return GetParameters(rows);
        }

[Transactional(TransactionalTypes.TransactionScope)]
        private void Child_Insert()
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("DeepLoad"))
            {
                using (var cmd = new SqlCommand("AddB02Level1", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Level_1_ID", ReadProperty(Level_1_IDProperty)).Direction = ParameterDirection.Output;
                    cmd.Parameters.AddWithValue("@Level_1_Name", ReadProperty(Level_1_NameProperty)).DbType = DbType.String;
                    var args = new DataPortalHookArgs(cmd);
                    OnInsertPre(args);
                    cmd.ExecuteNonQuery();
                    OnInsertPost(args);
                    LoadProperty(Level_1_IDProperty, (int) cmd.Parameters["@Level_1_ID"].Value);
                }
                FieldManager.UpdateChildren(this);
            }
        }

[Transactional(TransactionalTypes.TransactionScope)]
        private void Child_Insert(B02Level1 parent)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("DeepLoad"))
            {
                using (var cmd = new SqlCommand("AddB03Level11Child", ctx.Connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Level_1_ID", parent.Level_1_ID).DbType = DbType.Int32;
                    cmd.Parameters.AddWithValue("@Level_1_1_Child_Name", ReadProperty(Level_1_1_Child_NameProperty)).DbType = DbType.String;
                    var args = new DataPortalHookArgs(cmd);
                    OnInsertPre(args);
                    cmd.ExecuteNonQuery();
                    OnInsertPost(args);
                }
            }
        }