Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 To: Kulwant Singh
  Where is Kulwant Singh?
 Shimla
 India
 Kulwant Singh
 Tags
Subject: RE: variable no. of parameters
Thread ID: 393821 Message ID: 393852 # Views: 47 # Ratings: 2
Version: Not Applicable Category: .NET
Date: Tuesday, December 24, 2013 2:31:56 PM         
   


> Dear Experts,
>
> as you can see, this is C# code for inserting Data...
>
>
        public void insertData(string name,string surName, string age)
>         {
>             SqlConnection connection = new SqlConnection(getConnectionString());
>             SqlCommand cmd = new SqlCommand("INSERT INTO MYTABLE (NAME,SURNAME,AGE) VALUES (@NAME,@SURNAME,@AGE)", connection);
>             try
>             {
>                 cmd.Parameters.AddWithValue("@NAME", name.ToUpper());
>                 cmd.Parameters.AddWithValue("@SURNAME", surName.ToUpper());
>                 cmd.Parameters.AddWithValue("@AGE", age.ToUpper());
>                 connection.Open();
>                 cmd.ExecuteNonQuery();
>                 connection.Close();
>                 connection.Dispose();
>             }

>
> This method accepts 3 parameters and the TRY block adds 3 parameters in three lines of code.
>
> How do I re-write this code to send in N number of parameters ie if I need to deal with 5 fields, I send in five parameters and the try block add 5 Parameters...
>
> PLUS
> instead of a Hard coded SQL Command, if I send in another parameter ie the SQL COMMAND
> how to write code for this line
> SqlCommand cmd = new SqlCommand(.....????
>
>
> TIA
>
>
>
> kulwant
> (Help Save the TIGER. Save Wildlife. Live & let Live)

One of the things I did in the past was to pass a query string and a dictionary to a method that does the connection part for me and execute the command. Something like this (no try ... catch here which should be):

public int NonQuery(string sql, Dictionary<string, object> parameters = null)
{
  int effectedRecords;
  using ( SqlConnection con = new SqlConnection(getConnectionString()) )
  {    
    SqlCommand cmd = new SqlCommand(sql, con);
    
    if (parameters != null)
    {
        foreach (var p in parameters)
        {
          cmd.Parameters.AddWithValue(p.Key, p.Value);
        }
    }
    
    con.Open();
    effectedRecords = cmd.ExecuteNonQuery();
    con.Close();
  }
  return effectedRecords;
}


Then per your sample you could call it like:

string sql = "INSERT INTO MYTABLE (NAME,SURNAME,AGE) VALUES (@NAME,@SURNAME,@AGE)";
var parameters = new Dictionary<string,object>{ 
      {"@NAME", name.ToUpper()},
      {"@SURNAME", surName.ToUpper()},
      {"@AGE", age},   // age.ToUpper() ????? or even age itself ???
    };  
NonQuery(sql, parameters);



BUT, since there is an OO way to deal with data for a world where 'everything' is an object I prefer that one. Enter the Linq. If you simply have used Linq and/or Entity Framework this question wouldn't exist. Same with Linq assuming this was for 'Person' type in 'People' collection (table):

var newPerson = new Person {Name = name.ToUpper(), SurName = surname.ToUpper(), Age = age};
db.People.Add( newPerson );  
db.SaveChanges();




Cetin Basoz

Give some sample data in code for your question - here is the tool you need to generate the code

My Blog


MongoDb Certified Developer
MongoDb Certified DBA


Support Wikipedia

ENTIRE THREAD

variable no. of parameters Posted by Kulwant Singh @ 12/24/2013 7:43:45 AM
RE: variable no. of parameters Posted by Stefan Wuebbe @ 12/24/2013 11:00:04 AM
RE: variable no. of parameters Posted by Cetin Basoz @ 12/24/2013 2:31:56 PM