Using SqlDataSource from Uncontrolled Situations

Question

As part of my common utilities that I have used in all of my business applications, I have this code ...

using System.Web.UI.WebControls;

public class Database
    {
    /// <summary>
    /// Creates a DataView object using the provided query and an SqlDataSource object.
    /// </summary>
    /// <param name="query">The select command to perform.</param>
    /// <returns>A DataView with data results from executing the query.</returns>
    public static DataView GetDataView(string query)
        {
        SqlDataSource ds = GetDBConnection();
        ds.SelectCommand = query;
        DataView dv = (DataView)ds.Select(DataSourceSelectArguments.Empty);
        return dv;
        }

     /// <summary>
     /// Creates a SqlDataSource object with initialized connection string and provider
     /// </summary>
    /// <returns>An SqlDataSource that has been initialized.</returns>
    public static SqlDataSource GetDBConnection()
        {
        SqlDataSource db = new SqlDataSource();
        db.ConnectionString = GetDefaultConnectionString(); //retrieves connection string from .config file
        db.ProviderName = GetDefaultProviderName(); //retrieves provider name from .config file
        return db;
        }
   }

Then, in my projects, to retrieve data from the databases, I will have some code, for example ..

DataView dv=Database.GetDataView("select mycolumn from my table");
//loop through data and make use of it

I spent some heat on people using SqlDataSource in this way. People don't seem to like that I use web control exclusively from code instead of putting it on an ASPX page. This is not like them, but they could not tell me the flaw. So is there a flaw? This is my main question. Because, if there are many shortcomings, I may have to change how I make many internal applications that I developed.

nonASP.NET, System.Web. , , , , . SqlDataSource WPF/Windows Forms/Console?

Answer 1

, , - .

, , , .

• ? ( , -.
• (UI.Control )?
• , / ?

Answer 2

, , SQL- , , : - -?

:

public static class Database
{
    private static readonly Func<DbCommandBuilder, int, string> getParameterName = CreateDelegate("GetParameterName");
    private static readonly Func<DbCommandBuilder, int, string> getParameterPlaceholder = CreateDelegate("GetParameterPlaceholder");

    private static Func<DbCommandBuilder, int, string> CreateDelegate(string methodName)
    {
        MethodInfo method = typeof(DbCommandBuilder).GetMethod(methodName, BindingFlags.Instance | BindingFlags.NonPublic, Type.DefaultBinder, new Type[] { typeof(Int32) }, null);
        return (Func<DbCommandBuilder, int, string>)Delegate.CreateDelegate(typeof(Func<DbCommandBuilder, int, string>), method);
    }

    private static string GetDefaultProviderName()
    {
        ...
    }

    private static string GetDefaultConnectionString()
    {
        ...
    }

    public static DbProviderFactory GetProviderFactory()
    {
        string providerName = GetDefaultProviderName();
        return DbProviderFactories.GetFactory(providerName);
    }

    private static DbConnection GetDBConnection(DbProviderFactory factory)
    {
        DbConnection connection = factory.CreateConnection();
        connection.ConnectionString = GetDefaultConnectionString();
        return connection;
    }

    public static DbConnection GetDBConnection()
    {
        DbProviderFactory factory = GetProviderFactory();
        return GetDBConnection(factory);
    }

    private static void ProcessParameters(
        DbProviderFactory factory, 
        DbCommand command, 
        string query, 
        object[] queryParameters)
    {
        if (queryParameters == null && queryParameters.Length == 0)
        {
            command.CommandText = query;
        }
        else
        {
            IFormatProvider formatProvider = CultureInfo.InvariantCulture;
            DbCommandBuilder commandBuilder = factory.CreateCommandBuilder();
            StringBuilder queryText = new StringBuilder(query);

            for (int index = 0; index < queryParameters.Length; index++)
            {
                string name = getParameterName(commandBuilder, index);
                string placeholder = getParameterPlaceholder(commandBuilder, index);
                string i = index.ToString("D", formatProvider);

                command.Parameters.AddWithValue(name, queryParameters[index]);
                queryText = queryText.Replace("{" + i + "}", placeholder);
            }

            command.CommandText = queryText.ToString();
        }
    }

    public static DataView GetDataView(string query, params object[] queryParameters)
    {
        DbProviderFactory factory = GetProviderFactory();

        using (DbConnection connection = GetDBConnection(factory))
        using (DbCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.Text;
            ProcessParameters(factory, command, query, queryParameters);

            DbDataAdapter adapter = factory.CreateDataAdapter();
            adapter.SelectCommand = command;

            DataTable table = new DataTable();
            adapter.Fill(table);
            return table.DefaultView;
        }
    }
}

, , SQL-:

DataView dv = Database.GetDataView(
   "select mycolumn from my table where id = {0} and name = {1}",
   1234, "Robert');DROP TABLE Students;--");

, .

Answer 3

, , -

(1) . v5 FW3.5 v6 FW4.5, . .

EL 2,3,4 , Dataset. , .

, Microsoft. , , , - . . , EL Data Caching . , . , .

(2) , ​​. Anything System.Web.... - . , Data Access - . [save for "common" ] , .

:

Microsoft. "". . - , , .

, . . , , . , System.Windows.Forms Asp.net. , , .

. , WPF- , . System.Web ?