locked
Dataset RRS feed

  • Question

  • User-797751191 posted

    Hi

    i have below code . In below code i am passing Query . What changes i need to make if user can pass query or even stored procedure alsol

    public static DataSet DataRecords(string SrcSql)
            {
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(SrcSql, ConfigurationManager.ConnectionStrings["Cnn"].ConnectionString);
                da.Fill(ds);
                return ds;
            }

    Thanks

    Tuesday, October 1, 2019 7:34 AM

All replies

  • User288213138 posted

    Hi jsshivalik,

    pass query or even stored procedure alsol

    According to your description, I couldn’t understand your requirement clearly.

    Your string SrcSql is a query, in SqlDataAdapter you get this query.

    What do you want to do? please post more details information about your requirement.

    Best regards,

    Sam

    Tuesday, October 1, 2019 8:23 AM
  • User1120430333 posted

    https://www.c-sharpcorner.com/article/using-stored-procedures-in-conjuction-with-dataadapter/

    Although the example is in VB, it clearly shows what you need in place to execute a sproc and using with a dataadapter, using the SQL Command objects.

    So your one method can't do both a T-SQL query and executer a sproc. You need two separate methods, IMO.  

    Tuesday, October 1, 2019 9:02 AM
  • User753101303 posted

    Hi,

    Your intent is unclear.

    Your method already pass a SQL statement to the db so you could potentially pass whatever you want. Note that having users to be able to enter their own SQL or SP (is this what you are trying to do ?) could be a security risk (SrcSql would just come from an iinput field).

    Try maybe to give us some more context. Are you trying to provide a search capability to your users ? Usually you are building a query for them using a user interface rather than letting them to directly enter whatever they want.

    Tuesday, October 1, 2019 9:15 AM
  • User-797751191 posted

    Hi

      IN case i want to pass Stored procedure name not query then what changes i need to make in my code . User can pass Query or Stored Procedure also

    Thanks

    Tuesday, October 1, 2019 11:16 AM
  • User288213138 posted

    Hi jsshivalik,

    pass Stored procedure name not query then what changes i need to make in my code

    You can try below code:

    public static DataSet DataRecords()
            {
                string constr=ConfigurationManager.ConnectionStrings["Cnn"].ConnectionString;
                DataSet ds = new DataSet();
                SqlConnection conn=new SqlConnection (constr);
                conn.open();
                SqlDataAdapter sda = new SqlDataAdapter("Stored procedure name", conn);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.Fill(ds);
                return ds;
            }

    Best regards,

    Sam

    Tuesday, October 1, 2019 1:25 PM
  • User753101303 posted

    You are just passing a string that you then run on the db side and could be anything you want including a SP name so what you shown should work. If you tried something and got a problem tell us what happened.

    Once again if you really let users to enter themselves any SQL statement what if someone tries DELETE FROM MyTable ? Seems weird to let users entering a SQL statement. You should likely still restrict what a user could enter as it seems quite risky.

    Tuesday, October 1, 2019 1:35 PM
  • User475983607 posted

    IN case i want to pass Stored procedure name not query then what changes i need to make in my code . User can pass Query or Stored Procedure also

    IMHO, the shared code is a very poor design.  A common pattern for executing a stored procedure is wrapping the stored procedure within a method of the same name where the method input parameters match the stored procedure.  This same pattern applies to dynamic SQL.   This makes you code easier to maintain.

    If your design is based on generating a SQL command string elsewhere in the code and passing the SQL to a code block then see any beginning level ADO.NET tutorial.  Simply move the the SQL, connection string, and parameters to method inputs. 

    This example...https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-code-examples#sqlclient

    becomes code similar to the following.

            public static DataTable GetData(string connectionNode, string sql, List<SqlParameter> parameters)
            {
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connectionNode].ConnectionString);
                SqlCommand cmd = new SqlCommand(sql, conn);
                if(parameters.Count > 0)
                {
                    cmd.Parameters.AddRange(parameters.ToArray());
                }
                DataTable dt = new DataTable();
    
                conn.Open();
                dt.Load(cmd.ExecuteReader());
                conn.Close();
                return dt;
            }

    I recommend going through a few ADO.NET tutorials and perhaps browse the C# programming guide.

    Tuesday, October 1, 2019 2:17 PM