Click here to Skip to main content
15,559,931 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
See more:
how can i do that without using stored procedure. i want to create update string dynamically using array. can i do that....see the below code

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...
 Collapse
     //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);
Posted
Comments
Sandeep Mewara 16-May-11 7:27am    
Not clear.

It seems very Easy dear....
C#
public int Insert(string Query, SortedList<string, object> parameter = null)
{
    _cmd = new SqlCommand(Query, _con);
    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...
 Collapse
     //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("INSERT INTO tablename VALUES('','','','')",_sl);

Thanks & Regards,
Punit


ok dear then you should pass object of SqlCommand, and in the above fuction you have catch that object like this...
C#
public int Insert(SqlCommand cmd)
{
    cmd.Connection = con;
    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...
     //first, create an object of that class. here,we assume obj is object of that class
     System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
     cmd.CommandText = "something"; //your procedure name or your whole Insert query like "INSERT INTO tablename VALUES(@firstName,@lastName,@age)"
     cmd.AddWithValue(&quot;@firstName&quot;, firstName);
     cmd.AddWithValue(&quot;@lastName&quot;, lastName);
     cmd.AddWithValue(&quot;@age&quot;, age);
     int _affectedRow = obj.Insert(cmd);
 
Share this answer
 
v2
Comments
rahul dev123 16-May-11 9:29am    
i need to create insert command dynamically for various table...Not just declare like this one
parmar_punit 18-May-11 5:07am    
dear rahul,
plz check it now, I've updated it...
it might useful to you...
Thanks
using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for MyClass
/// </summary>
public class MyClass
{
    public SqlConnection con;
    public SqlCommand cmd;
    public SqlDataAdapter adp;
    public DataSet ds;
	public MyClass()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public void Connection()
    {
        con = new SqlConnection("Connection String");
        con.Open();
    }
    public DataSet FillDate(string MyString)
    {
        Connection();
        adp = new SqlDataAdapter(MyString,con);
        ds = new DataSet();
        adp.Fill(ds);
        return ds;
    }

}


Write insert function like filldata, and call it from pages.
 
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