AsSplitQuery with SqlParameter through FromSql

[smitpatel]

#11370 with AsSplitQuery

[smitpatel]

I looked at DbParameter. DbParameter does not have clone method. (SqlParameter/NpgSqlParameter provides it through ICloneable).
So this cannot be solved for general purpose case easily. Create new parameter from existing DbParameter is not possible since we wouldn't know which facets to copy in relational layer. (or even type). Clone method would help exactly which is not available everywhere.

Not using DbParameter and passing actual values work fine. WIthout using AsSplitQuery also works fine. When context level default is split query but query is not executing additional command that scenario also works fine.

We could have solution that if sqlParameter implements ICloneable, we clone it if not then let it fail because we don't have a way to deal with it.

[ajcvickers]

@roji @bricelam for their thoughts on cloning DbParameter in different providers.

[smitpatel]

So sqlite is the only one which does not implement ICloneable (Checked MySql/NpgSql/SqlClient).
Apparently Sqlite does not throw error that same SqliteParameter is contained in another collection. So test just pass. Perhaps @bricelam can determine if that is behavior we need to change to align with other providers.

[smitpatel]

Submitted a PR to clone the parameter when ICloneable is implemented.

[smitpatel]

Thanks to @Tasteful - The code snippet they shared gave the idea that we can clone it whenever the parameter implements ICloneable

[smitpatel]

Hit the headwall. When it is out parameter then by cloning we don't get value back. Perhaps, we should just block this scenario as we cannot make it work for all.
Un-supported scenario would be to use split query with FromSql where DbParameters are passed. Users can always go with non-DbParameter format or avoid split query.

[bricelam]

Sqlite does not throw error that same SqliteParameter is contained in another collection. Perhaps @bricelam can determine if that is behavior we need to change to align with other providers.

Heck no. That behavior makes no sense on SQLite since there are no output parameters--they're all read-only, and I am 💯 against artificial constraints.

[bricelam]

But I'd be happy to implement a Clone method... (just not by implementing ICloneable)

[smitpatel]

If the lack of throwing is not a bug then Sqlite can remain as is. It does not affect query.

[Tasteful]

In my original code I'm actual checking if the parameter is an SqlParameter and in that case using reflection to see if the parameter already is attached to a command and in that case clone, otherwise we using the original parameter. I understand that using reflection like this is not good and it may introduce errors if the SqlParameter is changed internal in next version.

My use case when passing in a an SqlDbType.Structured parameter for streaming the information into an table-value-parameter is fine if the framework can support cloning of parameter with ParameterDirection.Input. Not sure if it is worth to make an try/catch in other case when the parameter is throwing ArgumentException from SqlParameterCollection.Add to wrap the error message with something meaningful for this case.

[kurda-kurda]

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace superkk.Data
{
class ClsAccess
{
SqlConnection con;

    public ClsAccess()
    {
        con = new SqlConnection(@"Server = .\SQLEXPRESS; database = SuperHH; Integrated Security = true");
    }
    public void Open()
    {
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            MessageBox.Show("پەیوندیەکە سەرکەتوبوو ", "Hamza IT", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }

    }
    public void Close()
    {
        if (con.State == ConnectionState.Closed)
        {
            con.Close();
            MessageBox.Show("بەداخەوەهیچ پەیوەندیەک نی یە ", "Hamza IT", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }

    }
    public void ExecuteCommand(string Stored_Procedure, SqlParameter[] param)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = Stored_Procedure;
            cmd.Connection = con;
            if (param != null)
            {
                cmd.Parameters.AddRange(param);
            }
            cmd.ExecuteNonQuery();

        }
        catch
        {
            MessageBox.Show(" پەیوەندیەکە هەڵەیە < ", "Hamza IT", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return;
        }
    }
    public DataTable SelectData(string Stored_Procedure, SqlParameter[] param)
    {

        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = Stored_Procedure;
        cmd.Connection =con;

        if (param != null)
        {
            for (int i = 0; i < param.Length; i++)
            {
              cmd.Parameters.Add(param[1]);
            }

        }
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;

        }
  

        }

    }

    


  hello there  , 

I have issue with this parameter
that prevent me to run my csharp c#
that say sqlparameter is already contained by another sqlparameterCollection
please help me for this