Geek asks and answers

Writing Your Own Provider Class in ASP.NET

Note. I do NOT want to write a custom membership provider.

I want to write my own provider class so that I can define it in web.config and access it as a Membership class.

Here is an example of my class (it has many other static methods):

public static class MySqlHelper
{
    private static string constring = ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;

    public static int ExecuteNonQuery(string mysqlquery)
    {
        SqlConnection conn = new SqlConnection(connString);
    SqlCommand cmd = new SqlCommand(mysqlquery, conn);
    int result;

    try
    {
        conn.Open();
        result= cmd.ExecuteNonQuery();
    }
    finally
    {
        conn.Close();
    }
    return result;

    }
}

Using: MySqlHelper.ExecuteNonQuery("select * from customers");

Now, as you can see, I hardcoded the connectionstring name, that is, "MyConnString". I plan to make it dynamic.

So, I was wondering if I can do this as a static built-in Membership class, where I can define connectionStringName in web.config. Thus, the class can be made reusable without pointing my connection string in web.config to "MyConnString".

1: connectionstring .

2: , Memberhip.CreateUser i.e. static.

- , / .

: , . , , . .

0
3

, , , , - , - IConnectionStringProvider. , , .:)

, , , . , appSettings MySqlProviderConnection , , .

appsetting, ConfigurationManager.ConnectionStrings. , , , .

+2

SqlConnection . MARS, . , , , . , , - , .

SqlConnections , / , . , , ; SQL Server, SQL Server .

, , / SqlConnection, , , . , , , .

public IEnumerable<SqlResults> ExecuteStoredProcedure(string procedure, params SqlParameter[] parameters) {
    using(SqlConnection connection = new SqlConnection(MyConnectionStringProperty)) {
        try {
            connection.Open();

            using(SqlCommand command = new SqlCommand(procedure, connection)) {
                command.CommandType = CommandType.StoredProcedure;

                if(parameters != null) {
                    command.Parameters.AddRange(parameters);
                }

                // yield return to handle whatever results from proc execution
                // can also consider expanding to support reader.NextResult()
                using(SqlDataReader reader = command.ExecuteReader()) {
                    yield return new SqlResults {
                        Reader = reader;
                    };
                }
            }
        }
        finally {
            if(connection.State != ConnectionState.Closed) {
                connection.Close();
            }
        }
    }
}

, , - , . , , . SqlResults SqlDataReader , .

static, , singleton - , ( /). - IoC Dependency Injection .

, , . , - :

// Since it an IEnumerable we can handle multiple result sets
foreach(SqlResults results in MySqlHelper.ExecuteStoredProcedure(myProcedureName, new SqlParameter("myParamName", myParamValue)) {
    // handle results
}

, . , using SqlClient . MySqlHelper, SQL-, , , ( ).

IoC/DI, Castle Windsor. . Control ( ) , . , , MySqlHelper, , , MySqlHelper. , . , "enter", , , IoC/DI ( ). . .

IoC/DI , , singleton. MySqlHelper , , , . IoC/DI MySqlHelper, , , , .

+1

Here is the complete code SqlHelperI used for small projects. But be careful with staticfor this class. If you will use it for a web project, remember that the connection will be shared in one instance for all users, which may cause problems ...

using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

public class SqlHelper
{
    private SqlConnection connection;

    public SqlHelper()
    {
        connection = new SqlConnection();
    }

    public void OpenConnection()
    {
        // Updated code getting the ConnectionString without hard naming it.
        // Yes, if you have more than 1 you'll have problems... But, how many times it happens?

        if (WebConfigurationManager.ConnectionStrings.Length == 0)
            throw new ArgumentNullException("You need to configure the ConnectionString on your Web.config.");
        else
        {
            connection.ConnectionString = WebConfigurationManager.ConnectionStrings[0].ConnectionString;
            connection.Open();
        }
    }

    public void CloseConnection()
    {
        if (connection != null && connection.State != ConnectionState.Closed)
            connection.Close();
    }

    public DataTable ExecuteToDataTable(string sql)
    {
        DataTable data;

        SqlCommand command = null;
        SqlDataAdapter adapter = null;

        try
        {
            if (connection.State != ConnectionState.Open)
                OpenConnection();

            command = new SqlCommand(sql, connection);
            adapter = new SqlDataAdapter(command);

            retorno = new DataTable();
            adapter.Fill(data);
        }
        finally
        {
            if (command != null)
                command.Dispose();

            if (adapter != null)
                adapter.Dispose();

            CloseConnection();
        }

        return data;
    }

    public int ExecuteNonQuery(string sql)
    {
        SqlCommand command = null;

        try
        {
            if (connection.State != ConnectionState.Open)
                OpenConnection();

            command = new SqlCommand(sql, connection);
            return command.ExecuteNonQuery();
        }
        finally
        {
            if (command != null)
                command.Dispose();

            CloseConnection();
        }
    }

    public object ExecuteScalar(string sql)
    {
        SqlCommand command = null;

        try
        {
            if (connection.State != ConnectionState.Open)
                OpenConnection();

            command = new SqlCommand(sql, connection);
            return command.ExecuteScalar();
        }
        finally
        {
            if (command != null)
                command.Dispose();

            CloseConnection();
        }
    }
}

Sample Usage:

SqlHelper sql = new SqlHelper();
DataTable data = sql.ExecuteToDataTable("SELECT * FROM Customers");
int affected = sql.ExecuteNonQuery("INSERT Customers VALUES ('Test')");

But if you really want to static(if you are in the same user environment), just put staticfor all the methods.

0
source

More articles:


All Articles