better code for this

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

, +

C#

protected void imgbtnSave_Click(object sender, ImageClickEventArgs e)
    {

        SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["dbConnection"].ToString());
        con.Open();

        for (int i = 0; i < ddlTesterName.Items.Count && i < ddlBuildNO.Items.Count && i < lbAddedItems.Items.Count; i++)
        {
          
            
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = "usp_InsertWorkAllocation";
            com = new SqlCommand(str, con);
           

            com.Parameters.AddWithValue("@LID", ddlTesterName.Items[i].ToString());
            com.Parameters.AddWithValue("@BID", ddlBuildNO.Items[i].ToString());
            com.Parameters.AddWithValue("@TID", lbAddedItems.Items[i].ToString());
            com.ExecuteNonQuery();
            com = new SqlCommand(str, con);
            com.ExecuteNonQuery();




        }
    }

Posted 10-Feb-12 23:41pm

ythisbug

Updated 11-Feb-12 1:42am

v4

Add a Solution

Comments

RDBurmon 11-Feb-12 5:49am

How many control you have in your ASP page and what are those ?

ythisbug 11-Feb-12 5:54am

name(label)[dropdownlist(ddlTesterName)]
BuildNo(label)[dropdownlist(ddlBuildNo)]
lbTestCase(as listbox)lbAddedItems(as Listbox)
from lbTestCase m adding list to lbAddedItems.
and one button..when i click button it should add in sql selected dropdowns values and selected multiple list items from lbAddedItems..

any question??

Tech Code Freak 11-Feb-12 6:20am

How do you want these entries inserted in the database?
If you want to select multiple items from the listbox, then those items will be saved in one cell in the database, or as different entries?
eg:
Id TesterName BuildNo AddedItems
1 Alex 1 abc,xyz,pqr
2 Roger 2 mno,def
3 Sarah 3 ghi,wxy

OR

Id TesterName BuildNo AddedItems
1 Alex 1 abc
2 Alex 1 xyz
3 Alex 1 pqr
4 Roger 2 mno
5 Roger 2 def
6 Sarah 3 ghi
7 Sarah 3 wxy

Select the way you want the entries to be inserted and I will guide you further.

ythisbug 11-Feb-12 7:15am

second one

Id TesterName BuildNo AddedItems
1 Alex 1 abc
2 Alex 1 xyz
3 Alex 1 pqr
4 Roger 2 mno
5 Roger 2 def
6 Sarah 3 ghi
7 Sarah 3 wxy

Tech Code Freak 11-Feb-12 8:48am

Now checkout my ready to use solution--Solution 3
Add click on Accept Solution for all working Solutions.
Don't forget to vote!

ythisbug 11-Feb-12 7:16am

in added items u have to add listitems and in testername and buildnumber from dropdowns..

5 solutions

Solution 6

SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["dbConnection"].ToString());
con.Open();

for (int i = 0; i < ddlTesterName.Items.Count && i < ddlBuildNO.Items.Count && i < lbAddedItems.Items.Count; i++)
{
com = new SqlCommand(str, con);

com.CommandType = CommandType.StoredProcedure;
com.CommandText = "usp_InsertWorkAllocation";


SqlParameter pLID = new SqlParameter("@LID", SqlDbType.VarChar, 50);
SqlParameter pBID = new SqlParameter("@BID", SqlDbType.VarChar, 50);
SqlParameter pTID = new SqlParameter("@TID", SqlDbType.VarChar, 50);

//com.Parameters.AddWithValue("@LID", ddlTesterName.Items[i].ToString());
//com.Parameters.AddWithValue("@BID", ddlBuildNO.Items[i].ToString());
//com.Parameters.AddWithValue("@TID", lbAddedItems.Items[i].ToString());
 
com.Parameters.AddWithValue("@LID",ddlTesterName.SelectedItem.ToString());
com.Parameters.AddWithValue("@BID",ddlBuildNO.SelectedItem.ToString());
com.Parameters.AddWithValue("@TID",lbAddedItems.Items[i].ToString());
com.ExecuteNonQuery();

Posted 28-Feb-12 20:10pm

ythisbug

Add a Solution

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

Sander Rossel · Accepted Answer · 2012-02-11T00:05:00

Not like that. Rule number one when working with databases, USE PARAMETERIZED QUERIES!!! I can't say this too many times, and yet I already have.
First of all, parameterized queries make for better performance. Sql server caches your queries and by parameterizing them the chance of a query being re-used, and thus using cache instead of doing the entire query again, raises significantly.
But, I year you say "my computer is fast enough I don't care if I get my data in 10 or 15 milliseconds". Well, you might be right, so there is another VERY IMPORTANT REASON to parameterize your queries: SQL INJECTION[^]! I don't know how your drop down box is filled, but let's say a user can enter dropdown values somewhere. The user enters "D'Artagnan", do you see what happens? The ' will break your query and your user will get an exception instead of expected results! In worst case scenario's hackers will get user information, login names, unencrypted passwords etc. or delete entire tables and databases! "Wow", I hear you think, "that's pretty serious, what do I have to do!?".
Well, luckily, parameterizing your queries isn't to difficult. Here's what to do:

C#

// Replace this code:
str = "insert into tblWorkAllocation values('" + ddlTesterName.Items[i].ToString() + "','" + ddlBuildNO.Items[i].ToString() + "','" + lbAddedItems.Items[i].ToString() + "')";
com = new SqlCommand(str, con);
com.ExecuteNonQuery();

// With this code:
str = "insert into tblWorkAllocation values('@TesterName','@BuildNO','@AddedItem')";
com = new SqlCommand(str, con);
com.Parameters.AddWithValue("@TesterName", ddlTesterName.Items[i].ToString());
com.Parameters.AddWithValue("@BuildNO", ddlBuildNO.Items[i].ToString());
com.Parameters.AddWithValue("@AddedItem", ddlAddedItems.Items[i].ToString());
com.ExecuteNonQuery();

As you can see your code is cleaner and more readable. No more nasty string concatenation. But the biggest importance here is that your parameters (@TesterName, @BuildNO, @AddedItem) are now replaced with the values you added to the parameter collection of your Command Object. "D'Artagnan" will now be accepted as a proper value, sql injection has become an impossibility or at least really very unlikely and you and your users can breathe easy knowing that your code is correct and safe!

As for your question:
I think this line of code is stopping the loop from ever looping more than once:

C#

if (ddlTesterName.Items[i].Selected == true && ddlBuildNO.Items[i].Selected == true && lbAddedItems.Items[i].Selected ==true)

Since only one item can be selected at one time :)
Hope this helps!

OriginalGriff · Accepted Answer · 2012-02-11T00:15:00

You can build up a single command containing multiple row information:

SQL

INSERT INTO "table_name" ("column1", "column2", ...) VALUES ("value1", "value2", ...) ("next value1", "next value2", ...)

I would use a StringBuilder rather than a string for this.

Unfortunately, AFAIK (and I would love to be told different) you can't do multiple inserts using parametrized queries, which you should really be using to avoid SQL Injection attacks.

[edit]
There is a way to do this, without building a command string, and using parametrized queries, and issuing just the single update: BulkUpdate from a DataTable:

C#

string strConnect = @"Data Source=GRIFFPC\SQLEXPRESS;Initial Catalog=Testing;Integrated Security=True";
using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlBulkCopy bulk = new SqlBulkCopy(con))
        {
        bulk.DestinationTableName = "Test";
        DataTable dt = new DataTable();
        dt.Columns.Add("Id");
        dt.Columns.Add("Data");
        dt.Rows.Add("ID 1", "DATA 1");
        dt.Rows.Add("ID 2", "DATA 2");
        dt.Rows.Add("ID 3", "DATA 3");
        dt.Rows.Add("ID 4", "DATA 4");
        bulk.WriteToServer(dt);
        }
    }

All you have to do is create three columns, each with the name of your DB column, then add each row as you go round your loop.
[/edit]

Tech Code Freak · Accepted Answer · 2012-02-11T02:36:00

Solution 3

Use the following code:
Create your connection and open it, then add the following:
[EDIT]
First, run a query:

SQL

Select top 1 WID from tblWorkAllocation order by WID desc

This will give you the last entered WID(primary key). Store it in a variable

C#

int wid=0;
object obj=SqlCommand1.ExecuteScalar();
if(obj!=null)
   wid=Convert.ToInt32(obj.ToString());

using (SqlBulkCopy bulk = new SqlBulkCopy(con))
{
    bulk.DestinationTableName = "Test";
    DataTable dt = new DataTable();
    dt.Columns.Add("WID");
    dt.Columns.Add("LID");
    dt.Columns.Add("BID");
    dt.Columns.Add("TID"); 
    for(int i=0; i<lbAddedItems.Items.Count; i++)
    {
        if(lbAddedItems.Items[i].Selected)
        {   dt.Rows.Add(++wid, ddlTesterName.SelectedItem.ToString(), ddlBuildNO.SelectedItem.ToString(), lbAddedItems.Items[i].ToString());
        }
    }
    bulk.WriteToServer(dt);
}

[/EDIT]

Posted 11-Feb-12 2:36am

Tech Code Freak

Updated 12-Feb-12 20:32pm

v7

Comments

Espen Harlinn 11-Feb-12 10:18am

5'ed!

Tech Code Freak 12-Feb-12 0:36am

Thanks!

ythisbug 12-Feb-12 23:29pm

WID(pk) LID BID TID

643 10.2.3 test3 NULL
644 10.2.3 test8 NULL
645 10.2.3 test10 NULL
hi brother its adding like this..

ythisbug 12-Feb-12 23:33pm

in lid column shuld add Tesrername ,BID column shuld add build no and TID column should add Testcases..can any one tel what s the error..??

Tech Code Freak 13-Feb-12 0:29am

For the primary key, you must first retrieve the last entered WID in the table. then increment it in the loop. And then enter in the database. I am updating my Solution further as per your requirement.

ythisbug 13-Feb-12 0:58am

object obj = com.ExecuteScalar();
error like Object reference not set to an instance of an object.

Tech Code Freak 13-Feb-12 1:17am

Have you done this:
SqlCommand cmd=new SqlCommand("Select top 1 WID from tblWorkAllocation order by WID desc", SqlConnection1);
before object obj= cmd.ExecuteScalar(); ?
Here, tblWorkAllocation should be your table name, SqlConnection1 is your SqlConnection object to which you should have already allocated a connection string.

ythisbug 13-Feb-12 1:23am

ya..but y its not adding selected tester. i mean in LID..when i select from dropdowns starting tester only adding..

Tech Code Freak 13-Feb-12 2:08am

For that, try assigning value of the ddlTesterName.SelectedItem.Text or ddlTesterName.SelectedValue to some variable before entering in the database. Then add that variable in the database. Give it a try.

Entire Solution is edited. Replace existing with this new one.

Tech Code Freak 13-Feb-12 0:47am

Solution Updated! This will surely work.
Now click on Accept Solution and vote for it!

ythisbug 13-Feb-12 1:00am

that i will bro bt m getting one error..can u solve that.??

zyck 11-Feb-12 20:29pm

nice logic

Tech Code Freak 12-Feb-12 0:36am

Thanks.

ythisbug 13-Feb-12 3:43am

hi how to clear items from list box after saving..??

ythisbug · Accepted Answer · 2012-02-12T19:33:00

3rd solution..and thanks guys ur logic also working..

C#

SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["dbConnection"].ToString());
        con.Open();
         for (int i = 0; i < ddlTesterName.Items.Count && i < ddlBuildNO.Items.Count && i < lbAddedItems.Items.Count; i++)
        {
            com = new SqlCommand(str, con);
          
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = "usp_InsertWorkAllocation";
                     
            SqlParameter pLID = new SqlParameter("@LID", SqlDbType.VarChar, 50);
            SqlParameter pBID = new SqlParameter("@BID", SqlDbType.VarChar, 50);
            SqlParameter pTID = new SqlParameter("@TID", SqlDbType.VarChar, 50);
 
            //com.Parameters.AddWithValue("@LID", ddlTesterName.Items[i].ToString());
            //com.Parameters.AddWithValue("@BID", ddlBuildNO.Items[i].ToString());
            //com.Parameters.AddWithValue("@TID", lbAddedItems.Items[i].ToString());

            com.Parameters.AddWithValue("@LID",ddlTesterName.SelectedItem.ToString());
            com.Parameters.AddWithValue("@BID",ddlBuildNO.SelectedItem.ToString());
            com.Parameters.AddWithValue("@TID",lbAddedItems.Items[i].ToString());
            com.ExecuteNonQuery();
        }

better code for this

5 solutions

Solution 1

Solution 2

Solution 3

Solution 5

Solution 6

Add your solution here

Preview 0

better code for this

5 solutions

Solution 1

Solution 2

Solution 3

Solution 5

Solution 6

Add your solution here

Preview 0

Existing Members

...or Join us