Click here to Skip to main content
15,881,139 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
See the bellow code:
But i want the parameters pass our requirement basis, if i have required in another forms, firstname,lastname,age,date,sex etc so i can't access this function. so i want to pass parameters our requirement basis. So how can i do that. Please help me.........Thanks in advance.

C#
public int Insert(string firstName, string lastName, int age)
        {
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlCommand dCmd = new SqlCommand("InsertData", conn);
            dCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                dCmd.Parameters.AddWithValue("@firstName", firstName);
                dCmd.Parameters.AddWithValue("@lastName", lastName);
                dCmd.Parameters.AddWithValue("@age", age);
                return dCmd.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                dCmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
Posted
Updated 15-May-11 21:56pm
v2
Comments
Wayne Gaylard 16-May-11 4:00am    
I added pre tags to your code, but the question is really unclear. Do you want a function which can be used to insert into all tables, or just into one table using different parameters? you need to be more clear.

Create this fuction in one class file if you have taken one class file in App_code folder then copy paste this function and create one instance of this class in other page class at where you want to call this function....
C#
public int Insert(string procedureName, SortedList<string, object> parameter = null)
{
    _cmd = new SqlCommand(procedureName, _con);
    _cmd.CommandType = CommandType.StoredProcedure;
    if (parameter != null)
    {
        for (int l = 0; l < parameter.Count; l++)
        {
            _cmd.Parameters.AddWithValue(parameter.Keys[l], parameter.Values[l]);
        }
    }
    int _rowAffected;
    try
    {
        if (_con.State == ConnectionState.Closed)
            _con.Open();
        _rowAffected = _cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
    finally
    {
        if (_con.State == ConnectionState.Open)
            _con.Close();
    }
    return _rowAffected;
}


Now, Invoke this method as following...
C#
//first, create an object of that class... here, assume we have one object named obj
var _sl = new SortedList<string, object>();
_sl.Add("@firstName", firstName);
_sl.Add("@lastName", lastName);
_sl.Add("@age", age);
int _affectedRow = obj.Insert("InsertData",_sl);
 
Share this answer
 
v2
Comments
Hemant__Sharma 16-May-11 5:06am    
Very good answer, straight to the point. My 5

It would be more better if you would have marked the function as "static". but good solution anyway.

--Hemant
parmar_punit 16-May-11 5:15am    
Thanks hemant
Albin Abel 17-May-11 6:12am    
Right Answer. My 5. A valuable point added by Sandeep as well.
I vote 5 for Sandeep Mewara and parmar_punit@yahoo.co.in. The reason for vote 5 for parmar is because of its simplicity and works correctly. My vote of 5 for Sandeep Mewara is because his concept works great too. Following are the explanations.

Dictionary is a simple and nice idea, but what if some users add _sl.Add("@MyHeart", lovepulse);. If this should not happen then the caller should know the table structure prior . What a tight coupling?

But a object model gives a defined contract. Somebody misunderstood object model as single object and downvoted Sandeep's answer. Let us have a look at how paramar's and Sandeep's answer together helps in this situation.

Objects have a novel concept Encapsulation

Let us have an object model

C#
 public Interface IInsertableObject
{
    public  SortedList<string,object>  getInsertData()
}
public class CommonObject:IInsertableObject
{
    public string FirstName{get;set;}
    public string LastName{get;set;}
    public int Age{get;set;} 
    public virtual SortedList<string,object> getInsertData()
    {
        // Here you will be having parmar's sorted list created based on the                 
        //object properties. Now this logic is encapsulated and no rubish                 
        //other than object properties are allowed
    }
}
public class ExtendedObjectBasedOnTable:CommonObject
{
    public DateTime birthDate{get;set;}
    public SexEnum Sex{get;set;}
        public overrides SortedList<string,object> getInsertData()
    {
        // Here you will be having parmar's sorted list created based on the                 
        //object properties. Now this logic is encapsulated and no rubish                 
        //other than object properties are allowed
    }
}


So your function will be like

C#
public int Insert(IInsertableObject insertableObject)
{
    SortedList<string,object> insertData=insertableObject.getInsertData();
    //use the sorted list as shown by Parmar.
}
insert(new CommonObject{ set the properties here.....})
insert(new ExtendedObjectBasedOnTable{ set the properties here.....})


So Sandeep's modal (Encapsulation and delegation) + Parmar's logic gives a better solution. The caller doesn't worry about persistance logic, but only need to know which object it can choose to match the parameters it is having. An another layer of factory goes here. But let us not make things further complex for OP.

Good luck
 
Share this answer
 
Comments
Sandeep Mewara 17-May-11 9:46am    
Full 5! :thumbsup:
parmar_punit 18-May-11 0:53am    
smart work my 5
What it sounds like you want to pass 1 to few parameters to a method for inserting data in database.

You need an object model. Create a class (Lets say personal Info). Add the proeprties like firstname, lastname, age, sex, etc to it. Use this object everywhere. Fill the data in it and pass it to the methods. handle the data in the method based on if present or not.

Try!
 
Share this answer
 
Comments
Albin Abel 17-May-11 6:08am    
No idea who down voted this. But I feel this is a viable idea too. Look at my answer. My 5+
Sandeep Mewara 17-May-11 9:46am    
Thanks Albin. I see 2 1-votes and no reason whatsoever. :)
try this
C#
public int Insert(string firstName, string lastName, int? age, DateTime? datetime, string sex)
    {
        if (!string.IsNullOrEmpty(firstName))
            dCmd.Parameters.AddWithValue("@firstName", firstName);
        if (!string.IsNullOrEmpty(lastName))
            dCmd.Parameters.AddWithValue("@lastName", lastName);
        if (age!=null)
            dCmd.Parameters.AddWithValue("@age", age);
        if(datetime!=null)
            dCmd.Parameters.AddWithValue("@datetime", datetime);
        if (!string.IsNullOrEmpty(sex))
            dCmd.Parameters.AddWithValue("@sex", sex);
    }
Insert("ABC", "", null, null, "");
 
Share this answer
 
Comments
Albin Abel 17-May-11 6:11am    
Sorry not agreed with this, what if there is a new parameter added?. Going to edit the function again. Our old school teacher taught something extensible would be fine, but not keep on modifying. :). I voted 4 for this
Shahdat Hosain 17-May-11 6:16am    
yes, solution 3 was better
Initialize all the parameter in the constructor.
Pass all the parameters to the stored procedure.
Call the function from the page creating object of the class, Remove all the parameter in the funtion Insert().
Write the properties in the class. and intialize the properties with the value you like to send.
For Eg. // please ignore syntax error check it in intellisense
public class Sample
{
 //Declare all the fields to be inserted in database
  private string firstName;
  private string lastName;
  private int age;

 public Sample()
 {
   InitFields();
 }

 public void InitFields()
 {
     firstName = string.Empty;
     lastName = string.Empty;
     age = 0;
 }

 public bool Insert()
 {
    SqlConnection conn = new SqlConnection(connStr);
    conn.Open();
    SqlCommand dCmd = new SqlCommand("InsertData", conn);
    dCmd.CommandType = CommandType.StoredProcedure;
    try
    {
         dCmd.Parameters.AddWithValue("@firstName", FirstName);
         dCmd.Parameters.AddWithValue("@lastName", LastName);
         dCmd.Parameters.AddWithValue("@age", Age);
         return dCmd.ExecuteNonQuery();
    }
    catch
    {
         throw;
    }
    finally
    {
         dCmd.Dispose();
         conn.Close();
         conn.Dispose();
    }
 }

  //Properties here
  public string FirstName
  {
        get{return firstName;}
        set{firstName = value;}
  }
  public string LastName
  {
        get{return lastName;}
        set{lastName = value;}
  }
  public int Age
  {
        get{return age;}
        set{age = value;}
  }
}


And from page suppose you do not want to send age you can do it like this on click event

//Create an object of class

Sample obj = new Sample();
obj.FirstName = "ABC";
obj.LastName = "XYZ";
obj.Insert();
 
Share this answer
 

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