ASP.NET - 2 into 1

Asked By Rahul
03-Feb-12 11:31 AM
how to bring this codings into one using datareader or if else or anything ?

1.
cmd=new SqlCommand("select Count(*) from agentreg where userid='" +TextBox1.Text+ "' and username='"+TextBox2.Text+"'",con);

 

int i = (int)cmd.ExecuteScalar();

if (i != 0)

{

cmd =

new SqlCommand("select count (username) from creditadd where services ='" + DropDownList1.SelectedItem.Value + "' and username='" + TextBox2.Text + "' and userid='" + TextBox1.Text + "'", con);

int j = (int)cmd.ExecuteScalar();

 

if (j == 0)

{

cmd = new SqlCommand("insert into creditadd values (@userid,@username,@services,@credit)", con);

cmd.Parameters.AddWithValue(

"userid", TextBox1.Text);

cmd.Parameters.AddWithValue("username", TextBox2.Text);

cmd.Parameters.AddWithValue(

"services", DropDownList1.SelectedItem.Value);

cmd.Parameters.AddWithValue("credit", TextBox3.Text);

cmd.ExecuteNonQuery();

TextBox1.Text =

"";

TextBox2.Text = "";

TextBox3.Text =

"";

Response.Write("<script>alert('Successfull');</script>");

bindgrid();

dropbind();

}

 

else

{

Response.Write("<script>alert('Already Exist');</script>");

}

}

 

else

{

Response.Write("<script>alert('Not Registered');</script>");

}



2.

 

cmd =

new SqlCommand("select Count(*) from memreg where userid='" + TextBox1.Text + "' and username='" + TextBox2.Text + "'", con);

int k = (int)cmd.ExecuteScalar();

 

if (k != 0)

{

cmd = new SqlCommand("select count (username) from creditadd where services ='" + DropDownList1.SelectedItem.Value + "' and username='" + TextBox2.Text + "' and userid='" + TextBox1.Text + "'", con);

 

int l = (int)cmd.ExecuteScalar();

if (l == 0)

{

cmd =

new SqlCommand("insert into creditadd values (@userid,@username,@services,@credit)", con);

cmd.Parameters.AddWithValue("userid", TextBox1.Text);

cmd.Parameters.AddWithValue(

"username", TextBox2.Text);

 

cmd.Parameters.AddWithValue("services", DropDownList1.SelectedItem.Value);

cmd.Parameters.AddWithValue(

"credit", TextBox3.Text);

cmd.ExecuteNonQuery();

TextBox1.Text = "";

TextBox2.Text =

"";

TextBox3.Text = "";

Response.Write(

"<script>alert('Successfull');</script>");

bindgrid();

dropbind();

}

else

{

Response.Write("<script>alert('Already Exist');</script>");

}

}

 

else

{

Response.Write("<script>alert('Not Registered');</script>");

}

  [)ia6l0 iii replied to Rahul
03-Feb-12 12:04 PM
Good. So you thought about re factoring. 

Let's start. 

a) You can have a single SQLCommand object. 
SqlCommand command = default(SqlCommand);

b)Use a single SQLConnection. 
using (SqlConnection connection = new SqlConnection(connectionString))
{
}

c) Initialize the query based on your condition. 
string query = string.Empty;
Based on your conditions set query using String,format like:

query = string.format("select count (username) from creditadd where services = '{0}' and username='{1}' and userid='{2}" , DropDownList1.SelectedItem.Value, TextBox2.Text, TextBox1.Text );
OR
query = string.format("select Count(*) from memreg where userid = '{0}' and username='{1}'" , TextBox1.Text, TextBox2.Text );

d) Then you set the command with the query object.
cmd = new SqlCommand(query, connection);

So the skeleton code would look like:

SqlCommand command = default(SqlCommand);
string query = string.Empty;
int result = 0;

using (SqlConnection connection = new SqlConnection(connectionString))
{
if(your condition)
{
query = string.format("select count (username) from creditadd where services = '{0}' and username='{1}' and userid='{2}" , DropDownList1.SelectedItem.Value, TextBox2.Text, TextBox1.Text );
//OR
query = string.format("select Count(*) from memreg where userid = '{0}' and username='{1}'" , TextBox1.Text, TextBox2.Text );
}
cmd = new SqlCommand(query, connection);
result = (int)cmd.ExecuteScalar();
}

Thank you.
  D Company replied to Rahul
03-Feb-12 12:23 PM
hello friend.

here i have seen that in both the code block you are using select and insert query, first thing if you want to add in one code block, you have to define all the common variable in global section.like command, reader dataset...etc.

Second there will be defnetly a business logic in your app for which you are using 2 different query,so it can be handeled from your UI

for example

if(somecontrolsareselected)
{
//execute first bolck
}
else
{
//execute second block
}

so, here you have to identify the, logic where actually your query differentiate. and than put the condition and apply the above mentioned things.

Regards
D
  Danasegarane Arunachalam replied to Rahul
03-Feb-12 09:01 PM
Use the Grouping Function in sql. And here is the modified coding.

string strcommand="select Count(*) from  agentreg a, memreg m where a.userid=m.userid and a.username=m.username and a.userid='" +TextBox1.Text+ "' and a.username='"+TextBox2.Text+"'"
cmd=new SqlCommand(strcommand,con);
 
  
 
int i = (int)cmd.ExecuteScalar();
if (i != 0)
 
{
 
cmd =
 
new SqlCommand("select count (username) from creditadd where services ='" + DropDownList1.SelectedItem.Value + "' and username='" + TextBox2.Text + "' and userid='" + TextBox1.Text + "'", con);
int j = (int)cmd.ExecuteScalar();
 
  
 
if (j == 0)
 
{
cmd = new SqlCommand("insert into creditadd values (@userid,@username,@services,@credit)", con);
 
cmd.Parameters.AddWithValue(
 
"userid", TextBox1.Text);
cmd.Parameters.AddWithValue("username", TextBox2.Text);
 
cmd.Parameters.AddWithValue(
 
"services", DropDownList1.SelectedItem.Value);
cmd.Parameters.AddWithValue("credit", TextBox3.Text);
 
cmd.ExecuteNonQuery();
 
TextBox1.Text =
 
"";
TextBox2.Text = "";
 
TextBox3.Text =
 
"";
Response.Write("<script>alert('Successfull');</script>");
 
bindgrid();
 
dropbind();
 
}
 
  
 
else
 
{
Response.Write("<script>alert('Already Exist');</script>");
 
}
 
}
 
  
 
else
 
{
Response.Write("<script>alert('Not Registered');</script>");
 
}
  Sandeep Mittal replied to Rahul
04-Feb-12 12:26 AM
Better create a stored procedure like this, execute the below procedure with ExecuteScalar from the front end, this would you give you the value from Successfull/Already Exist/Not Registered.

CREATE PROCEDURE PROCNAME
  @userid   VARCHAR(10)
  , @username VARCHAR(10)
  , @services VARCHAR(10)
  , @credit     INT
AS
BEGIN
  IF EXISTS(SELECT 1 FROM agentreg WHERE userid = @userid and username = @username
        UNION ALL SELECT 1 FROM memreg WHERE userid = @userid and username = @username)
  BEGIN
    IF EXISTS(SELECT 1 FROM creditadd WHERE services = @services AND userid = @userid AND username = @username)
     BEGIN
      INSERT INTO creditadd VALUES (@userid,@username,@services,@credit)
      SELECT 'Successfull'
     END
     ELSE
     BEGIN
      SELECT 'Already Exist'
     END
  END
  ELSE
  BEGIN
    SELECT 'Not Registered'
  END
END
Create New Account
help
Store Procedure can anyone tell me how to implement store procedure in Asp .net with c# First open Microsoft SQL Server -> Enterprise Manager, then navigate to the database in which you want to create the stored procedure and select New Stored Procedure. See the below Stored Procedure Properties for what to enter, then click OK. Now create an application named Store
inserting all form values in a table using stored procedure hi everyone. . . . . . i have problem like . . .i have created a web page which is having and type which is a dropdownlist and the rest are textboxes and i have created stored procedures to insert these all values into the table but now from my web page how can i cal that stored procedure to insert all my values into table i tried like this but in between i stucked. . . . sqlconnection con = new sqlconnection(); sqlcommand cmd = new sqlcommand("proc_name", con); cmd.commandtype = commandtype.storedprocedure ; so next am trying code this correct cmd.Parameters.add(id, txtid.Text); cmd.parameters.add(fromdate, Txtfrom.Text); cmd.ExecuteNonQuery(); HI try this aspx: <asp:Label ID = "Label1" runat = "server" Visible = "false"> < / asp:Label> <br
how to use stored procedure for insert update delete or select Hi all how i cna use stored procedure for insert update delete or select in my application. thanks Regards Shoaib USE [AdventureWorksLT] CREATE Procedure [dbo].[InsertCustomer] @NameStyle bit, @Title nvarchar(8), @FirstName nvarchar(50), @MiddleName nvarchar(50), @LastName nvarchar CompanyName , @SalesPerson , @EmailAddress, @Phone, @PasswordHash, @PasswordSalt, @ModifiedDate, @Inactive ) select SCOPE_IDENTITY() as NewCustomerID UPDATE USE [AdventureWorksLT] CREATE PROCEDURE [dbo].[UpdateCustomer] @CustomerID int, @NameStyle bit, @Title nvarchar(8), @FirstName nvarchar(50), @MiddleName nvarchar(50
stored procedure how to write stored procedure for insert the record in sqlserver without using of parameter. . . it is possible. . . . . . .tell me how to do. . . . . . . how to write stored procedure with out parameter hi, To insert the record you have to use an another table you need to use values in insert command. Like this you can create stored procedure for insert create procedure SPInsert( @ Name varchar (50), @Address varchar (100), @Email varchar (20
classfile for gridview hi, i create store procedure for select table, how call this code in class file and how call class file the nice article look at this. . . Sample image Introduction There are several advantages of using stored procedure s instead of standard SQL . First, stored procedure s allow a lot more flexibility offering capabilities such as conditional logic. Second, because stored procedure s are stored within the DBMS, bandwidth and execution time are reduced. This is because