LearnFast.Ninja

Get DataTable With Parameter - asp.net C Sharp parameterized query

Access to the database with query with parameter using param type and value. This is one way to protect from sql injection in ASP.NET. Remember not to open the connection. The Fill method is open the connection and close it, but if the connection is open the Fill method would not close the connection and it will stay open.

Instructions: Access to the database with query with parameter using param type and value.

/// <summary></summary>

/// <param name="Query">"select * from MyTable where param1=@param1"</param>

/// <param name="ParamName">@param1</param>

/// <param name="ParamValue">"1244"</param>

/// <returns>DataTable</returns>

/// <example>

/// GetDataTableWithParameter("select * from MyTable where Item_ID=@param1", "@param1", "1244")

/// </example>

public static DataTable GetDataTableWithParameter(string Query, string ParamName, string ParamValue)

{

    string myConnectionString = WebConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;

    SqlConnection oSqlConnection = new SqlConnection(myConnectionString);

    try

    {

        SqlCommand cmd = new SqlCommand(Query, oSqlConnection);

        SqlParameter param1 = new SqlParameter();

        param1.ParameterName = ParamName;

        if (!string.IsNullOrEmpty(ParamValue))

        {

            param1.Value = ParamValue; // "@Param1"

            cmd.Parameters.Add(param1);

        }

        SqlDataAdapter oSqlDataAdapter = new SqlDataAdapter(cmd);

        DataTable DataTable_To_Fill = new DataTable();

        oSqlDataAdapter.Fill(DataTable_To_Fill);

        return DataTable_To_Fill;

    }

    finally

    {

        oSqlConnection.Close();

    }

}// end function


Tags

asp.net

dal dataaccess datatable param parameter fill