Click here to Skip to main content
14,454,912 members
Rate this:
Please Sign up or sign in to vote.
See more:
This is my single page code.
use of this code is auto fill the data in txtboxpartyid.text when i have entered a data in txtboxprod_id.text box.


sqlconnection con = new sqlconnection();
sqlcommand cmd = new sqlcommand();

click on button();
{
con.open();
cmd= new sqlcommand("select * from product where prod_id='"+txtboxprod_id.text+"'",con);
sqldatareader sdr= cmd.executereader();
if(sdr.read())
{
txtboxpartyid.text=(sdr["party_id"].tostring());
}
con.close();
}

What I have tried:

i am trying to convert this single page code into 3 layer architecture but my code is not working and it's also give me an error on sqldatareader part. the error is your connection is closed. please help me to convert this single page code in 3 layer and thanks in advance...
below code is only main part fo the code without showing a sqlconnection or sqldataadapter code.
SQl command to create a procedure:
go
create procedure spsearchpartyid(@prod_id int)
as 
begin
select * from product where Prod_id=@prod_id;
end

>BELayer page code:
public string quantity { get; set; }
public string prod_id { get; set; }
public string party_id { get; set; }




DALayer page code
//command for auto fill data into quantity table;
        public SqlDataReader autofillparty_id(BEL beobj)
        {
            try
            {
                
                cmd = new SqlCommand("select * from product where prod_id='" + beobj.prod_id + "'", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "spsearchpartyid";
                cmd.Parameters.AddWithValue("@prod_id", beobj.prod_id);
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return sdr;
     
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally 
            {
               con.Close();
            }
        }



BALayer page code.
//command for autofill data into table quantity..
        public SqlDataReader autofillquantity(BEL beobj)
        {
            try
            {
                return daobj.autofillparty_id(beobj);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

<pre lang="c#"><pre lang="c#">
MAin Page code.
private void button1_Click(object sender, EventArgs e)
{
if (baobj.autofillquantity(beobj).Read())
{
txtboxpartyidquant.Text = (beobj.party_id.ToString());

}

}
Posted
Updated 15-Feb-20 14:37pm
Rate this:
Please Sign up or sign in to vote.

Solution 1

When you asked this question yesterday: How can I divide this single page command in 3 layer architecture in ASP.NET c#..[^]
I told you not to worry about it as you had much more serious problems:

Quote:
Forget trying to split it up for the moment - that's not a subject for a little text box like this anyway - and fix your whole app first: it has a major problem.

Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

That hasn't changed: and if you aren't going to listen to us when we give you advice, is there any point in you asking at all?
   
v2
Comments
Member 14743579 14-Feb-20 2:33am
   
do you have a solution about it than please give immediately it's urgent..
OriginalGriff 14-Feb-20 3:14am
   
It's nowhere near as urgent as you preserving your DB.
Member 14743579 14-Feb-20 3:31am
   
i think you don't have an answer about it.
if you have than please solve my problem..
OriginalGriff 14-Feb-20 3:42am
   
If you aren't going to listen to what we say, then there is no point in us saying anything.

Bye.
Member 14743579 14-Feb-20 3:50am
   
Forget trying to split it up for the moment - that's not a subject for a little text box like this anyway - and fix your whole app first: it has a major problem.

tell me what is the major problem in my app. i have created a lots of stores procedure to access the data from my database and i have also used a 3 layer architecture in app than what is the problem ???
phil.o 14-Feb-20 5:04am
   
The problem is building SQL queries by concatenating strings. You should never do that. Paul explained why already.
Dave Kreskowiak 16-Feb-20 0:08am
   
You don't have a clue what you're talking about.

This is NOT a stored procedure, nor are you even calling one:
SqlCommand("select * from product where prod_id='" + beobj.prod_id + "'", con)


You're code won't work at all because on the very next line, you told the command object that you're calling a stored procedure:
cmd.CommandType = CommandType.StoredProcedure;

You're NOT calling a stored procedure in your command text.

This code is going to fail immediately.
Rate this:
Please Sign up or sign in to vote.

Solution 2

You can not read SqlDataReader for closed connection. So you must move con.Close() method from DALayer page code into button1_Click event.

private void button1_Click(object sender, EventArgs e)
{
try
{
if (baobj.autofillquantity(beobj).Read())
{
txtboxpartyidquant.Text = (beobj.party_id.ToString());
}
}
finally
{
con.Close();
}
}
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100