ADO.NET Sample

SQL:-
CREATE PROCEDURE ShowSuppliers(
@txt varchar(50),
@Name varchar(50) output,
@Company varchar (50) output,
@Country varchar (50) output
)
AS
select @Name = ContactName, @Company = CompanyName,
 @Country = Country
from Suppliers
Where Country like "%"+ @txt +"%"
GO
--------------------------------------------------------------------------------------------------------
C#:-
// Define the command object with stored procedure name.
SqlCoommand objCommand = new Command("ShowSuppliers", objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
 
// Define input Sql parameter with input value.
SqlParameter Param = objCommand.Parameters.Add("@txt", SqlDbType.Varchar, 50);
Param.Direction = ParameterDirection.Input;
Param.Value = "US";
 
// Define sql stored procedure output parameters
SqlParameter Param = objCommand.Parameters.Add("@Name", SqlDbType.Varchar, 50);
Param.Direction = ParameterDirection.Output;
SqlParameter Param = objCommand.Parameters.Add("@Company", SqlDbType.Varchar, 50);
Param.Direction = ParameterDirection.Output;
SqlParameter Param = objCommand.Parameters.Add("@Country", SqlDbType.Varchar, 50);
Param.Direction = ParameterDirection.Output;
 
// Execute the query
objCommand.ExecuteNonQuery();
 
// Retrieve Sql StoredProcedure output data.
Response.Write (objCommand.Parameters["@Name"].Value.ToString() + "<BR>");
Response.Write (objCommand.Parameters["@Company"].Value.ToString() + "<BR>");
Response.Write (objCommand.Parameters["@Country"].Value.ToString() + "<BR>");
 
=================================================================================
SQL:-
CREATE PROCEDURE ShowSuppliers (
@txt varchar(50)
)
AS
Select CompanyName, City, Country
from Suppliers
Where  Country like "%" + @txt + "%"
--------------------------------------------------------------------------------------------------------
C#:-
try{
 SqlCommand objCommand = new SqlCommand("ShowSuppliers",objConnect);
 objCommand.CommandType = CommandType.StoredProcedure;

 SqlParameter Param = objCommand.Parameters.Add("@txt",SqlDbType.VarChar, 50);
 Param.Value = "US";
 
 SqlDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);

   while (objDataReader .Read())
     {
        // Get data from the DataReader
        Console.WriteLine("CompanyName:-", (objDataReader ["CompanyName"]);
        Console.WriteLine("City:-", (objDataReader ["City"]);
        Console.WriteLine("Country :-", (objDataReader ["Country "]);
     }
}
finally
{
     // close the reader in the face of exceptions
     if (objDataReader != null)
     {
        if (!objDataReader.IsClosed)
          objDataReader.Close();
      }
}
 
 

1 comment:

Subscribe to: Post Comments (Atom)