One Store procedure for all operation on table

......Starting Store procedure.......


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[ASP_CountryMaster]

@CountryID int=NULL,
@CountryName varchar(50)=NULL,
@OtherFlag tinyint=NULL,
@Operation varchar(50)=null

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


if(@Operation='selectall')
begin
select * from CountryMaster where OtherFlag=1
end
if(@Operation='selectallother')
begin
select * from CountryMaster where OtherFlag=0
end

if(@Operation='insert')
begin
insert into CountryMaster
(
CountryName,
OtherFlag
)
values
(
@CountryName,
@OtherFlag
)
end

if(@Operation='selectbyid')
begin
select * from CountryMaster where CountryID=@CountryID
end

if(@Operation='update')
begin
update CountryMaster set
CountryName=@CountryName where CountryID=@CountryID
end
if(@Operation='updateotherflag')
begin
update CountryMaster set
OtherFlag=@OtherFlag where CountryID=@CountryID
end
if(@Operation='delete')
begin
Delete from CountryMaster where CountryID=@CountryID
end
select @@Identity
END

.......End Of Store procedure.......

...Strat how to call store procdure without specifying all the field....

1) to Get value by ID

AppDB ObjDB = new AppDB();
SqlDataAdapter DA = new SqlDataAdapter("ASP_OrderDealer", ObjDB.con);
DA.SelectCommand.CommandType = CommandType.StoredProcedure;
DA.SelectCommand.Parameters.AddWithValue("@UserID", objOrderDealer.UserID);
DA.SelectCommand.Parameters.AddWithValue("@Operation", "selectbyuserid");
DataSet ds = new DataSet();
ObjDB.OpenDB();
DA.Fill(ds);
ObjDB.CloseDB();
if (ds.Tables[0].Rows.Count > 0)
{
return ds;
}
else
{
return null;
}

2) For inserting record..........

AppDB ObjDB = new AppDB();
SqlCommand SC = new SqlCommand("ASP_OrderDealer", ObjDB.con);
SC.CommandType = CommandType.StoredProcedure;
SC.Parameters.AddWithValue("@UserID", objOrderDealer.UserID);
SC.Parameters.AddWithValue("@GrossTotal", objOrderDealer.GrossTotal);
SC.Parameters.AddWithValue("@Remarks", objOrderDealer.Remarks);
SC.Parameters.AddWithValue("@Specification", objOrderDealer.Specification);
SC.Parameters.AddWithValue("@Requirement", objOrderDealer.Requirement);
SC.Parameters.AddWithValue("@Transport", objOrderDealer.Transport);
SC.Parameters.AddWithValue("@Paymentmode", objOrderDealer.Paymentmode);
SC.Parameters.AddWithValue("@Status", objOrderDealer.Status);
SC.Parameters.AddWithValue("@Operation", "insert");
ObjDB.OpenDB();
Object objs = SC.ExecuteScalar();
if (objs != null)
{
newOrderDealer = Convert.ToInt32(objs);
}

ObjDB.CloseDB();

Get Extension of file

using System.IO;

code...................

public string GetExtension(string filename)
{
return Path.GetExtension(filename);
}

Get Unique Key for naming image file

using System.Security.Cryptography;
using System.Text;
using System.IO;

Code..............................................................................................

public string GetUniqueKey()
{
int maxSize = 8;
int minSize = 5;
char[] chars = new char[62];
string a;
a = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890";
chars = a.ToCharArray();
int size = maxSize;
byte[] data = new byte[1];
RNGCryptoServiceProvider crypto = new RNGCryptoServiceProvider();
crypto.GetNonZeroBytes(data);
size = maxSize;
data = new byte[size];
crypto.GetNonZeroBytes(data);
StringBuilder result = new StringBuilder(size);
foreach (byte b in data)
{
result.Append(chars[b % (chars.Length - 1)]);
}
return result.ToString();
}

Select All the tables in your databse + MS SQL SERVER

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';

Delete All Store Procedure

Exuecute following query to delete all store procedure in your database


Declare @sql Varchar(500)
Declare @get_table varchar(100)
DECLARE s_gettable CURSOR FOR SELECT name FROM sys.objects where type_desc ='SQL_STORED_PROCEDURE'
OPEN s_gettable
FETCH NEXT FROM s_gettable INTO @get_table
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
Set @sql='drop procedure dbo.'+@get_table
exec(@sql)
END
FETCH NEXT FROM s_gettable INTO @get_table
END
CLOSE s_gettable
DEALLOCATE s_gettable

Find nth Largest & Smallest value

Largest Value

SELECT a.StoreId FROM Stores a WHERE (n-1) = (select count(*) from Stores b where b.StoreId > a.StoreId)


Smallest Value

SELECT a.StoreId FROM Stores a WHERE (n-1) = (select count(*) from Stores b where b.StoreId < a.StoreId)

Select Reocord from 50 to 100

SELECT TOP 5 * FROM Stores where StoreId in ( SELECT top 10 StoreId from Stores order by StoreId asc) order by StoreId desc