Click here to Skip to main content
15,036,363 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi
i need insert listbox items (nos 50) into the DB using store procedure

Note: don't hit the db many times..

code:

C#
DataSet dsUpdate = new DataSet();
           for (int i = 0; i < lstMappedDepots.Items.Count; i++)
           {

              objEntity .DepotID = Convert.ToInt32(lstMappedDepots.Items[i].Value);
             objEntity .ModifiedBy  = Convert.ToInt32(Session["UserID"]);
              objEntity .ModifiedDate  = Convert.ToDateTime(DateTime.Now.ToString());
               dsUpdate = ObjModal.ValidateDepotUpdate(objEntity);
           }


public DataSet ValidateDepotUpdate(Entity .DCR_DFE_ValidateMap_Entity  userent)
      {
          DataSet oDS = null;
          try
          {
              DBDataSet oDbDs;
              DBHelper oDbHlp = new DBHelper();
              Connect objConnect = new Connect();

              oDbHlp.Command = "DcrDfe_Validat_Depot_prd";
              DBsetConnection = objConnect.TelentDBConnection;
              oDbHlp.CommandType = CommandType.StoredProcedure;

              oDbHlp.Parameters.Add(new DBHelper.Parameter("@Depot_ID", userent.DepotID));
              oDbHlp.Parameters.Add(new DBHelper.Parameter("@Modified_BY", userent.ModifiedBy));
              oDbHlp.Parameters.Add(new DBHelper.Parameter("@Modified_Date", userent.ModifiedDate));
              DBsetConnection.Open();
              oDbDs = DBsetConnection.ExecuteDataSet(oDbHlp);
              oDS = oDbDs.CDataSet;
              oDbHlp = null;
          }
          catch (Exception ex)
          {
              throw ex;
          }
          finally
          {
              DBsetConnection.Close();
          }
          return oDS;
      }

   }



i tried some other way

yes i have used the datatable concept.

var dataTable = new DataTable();

 dataTable.Columns.Add("itmes");
 for (int i = 0; i < listBox1.Items.Count; i++)
 { 
dataTable.Rows.Add(listBox1.Items[i].ToString()); 
}

 using (SqlConnection ocon = new SqlConnection("Data Source=172.19.44.21;Initial Catalog=sample;Persist Security Info=True;User ID=sa;Password=saa"))
 { SqlCommand cmd = new SqlCommand("InsertItems", ocon); 
cmd.CommandType = CommandType.StoredProcedure;
 SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt", dataTable);
 tvparam.SqlDbType = SqlDbType.Structured; ocon.Open();
 cmd.ExecuteNonQuery(); ocon.Close();

 }

 i created userdefined table type: 

CREATE TYPE [dbo].[MYDATATABLE12] AS TABLE
( 
[Items] [varchar](10) NULL
 ) GO 

and used sp like:

 Create PROCEDURE [dbo].[InsertItems]

 @dt AS dbo.MYDATATABLE READONLY 

AS 

BEGIN SET NOCOUNT ON;

 INSERT dbo.Items(Item) SELECT Items FROM @dt;

 END 


NOTE: but i go for sql server 2005 this is not possible(Datatable)
Posted
Updated 8-Aug-12 20:31pm
v6
Comments
Prasad_Kulkarni 8-Aug-12 8:10am
   
..and what you've tried so far?
PriyaRajavelu 8-Aug-12 8:17am
   
i have done this,but i hit everytime the DB for individual items of listbox.
so any idea to avoid this(many times hit the db).....
Prasad_Kulkarni 8-Aug-12 8:19am
   
Not clear. For me at least need more information, more over with some code snippets if possible for you.
Kenneth Haugland 8-Aug-12 8:11am
   
A simple search on google should give you a much quicker reply than posting the question here. You havent even told us what you have tried, so perhaps you should have started there? ;)

1 solution

What about fill a DataTable, and set the listbox datasource to that DataTable?

Fuzzy question.

Regards
   
Comments
Christian Amado 8-Aug-12 11:47am
   
Why you downvoted? Your question isn't clear & I tried to helped you.
Christian Amado 8-Aug-12 11:49am
   
My solution was posted based on v1 of the question.
PriyaRajavelu 9-Aug-12 1:05am
   
yes i have used the datatable concept.

var dataTable = new DataTable();
dataTable.Columns.Add("itmes");

for (int i = 0; i < listBox1.Items.Count; i++)
{
dataTable.Rows.Add(listBox1.Items[i].ToString());
}


using (SqlConnection ocon = new SqlConnection("Data Source=172.19.44.21;Initial Catalog=sample;Persist Security Info=True;User ID=sa;Password=saa"))
{
SqlCommand cmd = new SqlCommand("InsertItems", ocon);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt", dataTable);
tvparam.SqlDbType = SqlDbType.Structured;
ocon.Open();
cmd.ExecuteNonQuery();
ocon.Close();
}

i created userdefined table type:

CREATE TYPE [dbo].[MYDATATABLE12] AS TABLE(
[Items] [varchar](10) NULL
)
GO

and used sp like:

Create PROCEDURE [dbo].[InsertItems]
@dt AS dbo.MYDATATABLE READONLY
AS
BEGIN
SET NOCOUNT ON;

INSERT dbo.Items(Item) SELECT Items FROM @dt;
END

NOTE:
but i go for sql server 2005 this is not possible(Datatable)

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