Delete All Tables from Database

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

Download File in ASP.NET

protected void Button1_Click(object sender, EventArgs e)

{

// Get the physical Path of the file(test.doc)
string filepath = Server.MapPath("images/5.jpg");
// Create New instance of FileInfo class to get the properties of the file being downloaded
FileInfo file = new FileInfo(filepath);
// Checking if file exists
if (file.Exists)

{

// Clear the content of the response

Response.ClearContent();

Response.Clear();

// LINE1: Add the file name and attachment, which will force the open/cance/save dialog to show, to the header

Response.AddHeader(

"Content-Disposition", "attachment; filename=" + file.Name); // Add the file size into the response header

Response.AddHeader(

"Content-Length", file.Length.ToString()); // Set the ContentType

Response.ContentType = ReturnExtension(file.Extension.ToLower());

// Write the file into the response (TransmitFile is for ASP.NET 2.0. In ASP.NET 1.1 you have to use WriteFile instead)

Response.TransmitFile(file.FullName);

Response.Flush();

// End the response

Response.End();

}

}

private string ReturnExtension(string fileExtension)

{

switch (fileExtension)

{

case ".htm":

case ".html":

case ".log":

return "text/HTML";

case ".txt":

return "text/plain";

case ".doc":

return "application/ms-word";

case ".tiff":

case ".tif":

return "image/tiff";

case ".asf":

return "video/x-ms-asf";

case ".avi":

return "video/avi";

case ".zip":

return "application/zip";

case ".xls":

case ".csv":

return "application/vnd.ms-excel";

case ".gif":

return "image/gif";

case ".jpg":

case "jpeg":

return "image/jpeg";

case ".bmp":

return "image/bmp";

case ".wav":

return "audio/wav";

case ".mp3":

return "audio/mpeg3";

case ".mpg":

case "mpeg":

return "video/mpeg";

case ".rtf":

return "application/rtf";

case ".asp":

return "text/asp";

case ".pdf":

return "application/pdf";

case ".fdf":

return "application/vnd.fdf";

case ".ppt":

return "application/mspowerpoint";

case ".dwg":

return "image/vnd.dwg";

case ".msg":

return "application/msoutlook";

case ".xml":

case ".sdxl":

return "application/xml";

case ".xdp":

return "application/vnd.adobe.xdp+xml";

default:

return "application/octet-stream";

}

}

Type Casting of User Control in ASP.NET + C#

ASP.usercontrol_login_ascx {Name of New Object} = (ASP.usercontrol_login_ascx)Master.FindControl("{Id of UserControl}");

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