Click here to Skip to main content
14,735,415 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
Hi. I Wrote a function which get parameters for any Stored Procedure and initialize command parameters in a dynamic manner.
public void ExecProcedur<T>(String cmdText,string procName, SqlParameter paramtr, T paramvalu)
{
    SqlCommand cmd=new SqlCommand();
    cmd.Connection=Sqlc;
    cmd.CommandText=cmdText;
    cmd.CommandType=System.Data.CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue(paramtr,paramvalu); //error
    try{
        OpenConnection();
       // and ....


"paramvalu" can be int string (or even a list if "paramtr" be a list). i don't know how solve this. thanks in advance.
Posted
Updated 28-Jan-14 18:57pm
v2

1 solution

Try this generic method.

using System.Collections.Generic;
using System;
using System.Data;
using System.Data.SqlClient;


class Program
{


    static void Main(string[] args)
    {

        List<parametermapping> mappings = new List<parametermapping>();
        mappings.Add(new ParameterMapping() { ParameterName = "@ID", Value = 1 });
        mappings.Add(new ParameterMapping() { ParameterName = "@Name", Value = "karthik" });
        mappings.Add(new ParameterMapping() { ParameterName = "@Age", Value = 26 });
        mappings.Add(new ParameterMapping() { ParameterName = "@Address", Value = "Bangalore" });

        ExecuteSp(mappings, "yourstoredprodedureName");


    }

    protected static void AddSqlParameterToCollection(string sqlparametername, object value, SqlParameterCollection parameterCollection)
    {
        SqlParameter objSqlParameter = new SqlParameter();
        objSqlParameter.ParameterName = sqlparametername;
        objSqlParameter.Value = value;
        parameterCollection.Add(objSqlParameter);
    }
    protected static void ExecuteSp(List<parametermapping> mappings, string spName)
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = "your conection string";
        SqlCommand objCommand = new SqlCommand();
        SqlParameterCollection objParamColl;
        objParamColl = objCommand.Parameters;
        objCommand.CommandType = CommandType.StoredProcedure;

        try
        {
            if (mappings != null)
                foreach (ParameterMapping map in mappings)
                    AddSqlParameterToCollection(map.ParameterName, map.Value, objParamColl);

            objCommand.ExecuteNonQuery(); // for insert/delete/update

        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {

        }
    }
}

public class ParameterMapping
{
    public string ParameterName { get; set; }
    public object Value { get; set; }
}
   
v2
Comments
mit62 29-Jan-14 1:30am
   
thanks a lot. but I'm beginner in c#. I got the code and I understand why we should use SqlParameterCollection. but I just didn't get this line "objParamColl = objCommand.Parameters;" you know, I translate this line in my brain something like this" objCommand.Parameters=objParamColl;"
Karthik_Mahalingam 29-Jan-14 1:35am
   
if it is a single item then it is a Parameters , group of parameter combined to form ParameterCollections. this is how the .net framework has designed for us :)
mit62 29-Jan-14 2:14am
   
Ok. thanks
Karthik_Mahalingam 29-Jan-14 3:20am
   
Welcome mit62 :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900