Display Image from Database in ASP.NET

Scenario -

I have image names (eg:- abc.jpg) saved in a database & actual images lie in a folder in file system (~/ClientImages/).

Now I want to retrieve image from db & diplay it on webpage,in a div

Solution :-
 It is a very basic method,You can also fill the datagrid from CS file,But I opted dirty approach ie.SqlAdaperSource Take a Gridview choose - DataSource (ie :- SqlDataAddapter,go through the wizard ).

DataKeyNames="id" DataSourceID="SqlDataSource1">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:Image ID="img12" runat="server" Width="600px" Height="400" ImageUrl='<%# Page.ResolveUrl(string.Format("~/ClientImages/{0}", Eval("image"))) %>' />

</ItemTemplate>
</asp:TemplateField>

</Columns>

</asp:GridView>


## SqlDataSource ##
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:XXXXConnectionStringName %>"
SelectCommand="Stored_Procedure_Name" SelectCommandType="StoredProcedure">
</asp:SqlDataSource

Checkbox Validation in ASP.NET

Validation of check box is little bit tricky  but you can validate with Java Script

Add this script in the page.

<script type="text/javascript">
    function validateCheckBox(source,args)
    {
        var cb=document.getElementById("Privacy1");
        if(!cb.checked)
         args.IsValid=false;
    }
    </script>

add a Checkbox Control

<asp:CheckBox ID="Privacy1" runat="server"></asp:CheckBox>

Now you need to add a custom Validator:-

  <asp:CustomValidator ID="CustomValidator1" runat="server" 
            ErrorMessage="CustomValidator" 
            ValidateEmptyText="True" ClientValidationFunction="validateCheckBox"></asp:CustomValidator>

How to make free trials last FOREVER!!!

How to make free trials last FOREVER!!!

Difficulty : Piece of Cake 

You can make any software work for life time.          
        

 I am taking example of “ADOBE FLASH CS5 PROFESSIONAL “  Trial version as an example

Here we go  ->

• First download any trial software(Flash CS5)
• Type       runasdate        in Google          
• Open 1st displayed page  most probably      nirsoft.
• Download the software which is free.
• Extract it  & run .
• Then the first option will be application to run .Select desired(CS5)  application’s exe file's path.
• Now,set a date/time  Make it sure it must be in the trial period. (you can set same date when application is installed or next day’s  date)
• Check both  radio button, (move the time forward a/c to the real time  & immediate mode)
• Create a new desktop shortcut   with desired name (eg:- RippedCS5 ).
• Click   RUN  
• That’s it
• This procedure is  required only for 1st time , make sure to make a shortcut & only use that shortcut  on desktop. Configure every trial software with this utility & create short cut then u don’t need to run it again-2                       

                                                                                                   ENJOY

Test Stored Procedure Manually in MS SQL

Write a stored procedure  eg :  sp_Insert_Client_info

Now on New Query window  execut it with stored procedure's required value,

 like this  sp_Insert_Client_info'Age','India','phone',10101

Above sp will take 3 arguments/values :

1. Age  or text value
2. India or Text vale
3. 10101  or integer value

Another example:

 sp_Insert_Client_Info'Jhon','sharma',87

Read Excel data & Insert into SQL

Used spread gear DLL
DB  must have  following column   ..I will add later



  public void readExcel(String FilePath, String SheetName)
    {
        string row = "";
        string column = "";
        string transectionocurs = "";
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        SqlTransaction myTrans = con.BeginTransaction();
        SqlCommand cmd1 = new SqlCommand("sp_InserXSLData", con);
        cmd1.Transaction = myTrans;
        try
        {

            SpreadsheetGear.IWorkbook oWB;
            SpreadsheetGear.IWorksheet OSHEET;
            SpreadsheetGear.IRange oRng;
            //  DataTable ds = new DataTable();

            oWB = SpreadsheetGear.Factory.GetWorkbookSet().Workbooks.OpenFromStream(FileUpload1.PostedFile.InputStream);
            SpreadsheetGear.IWorksheet templateWorksheet = oWB.Worksheets[0];
            DataSet ds = oWB.GetDataSet(SpreadsheetGear.Data.GetDataFlags.FormattedText);
     

            bool saveit = true;


            DateTimeFormatInfo info = new System.Globalization.CultureInfo("en-gb").DateTimeFormat;
                                 
            if (ds.Tables[0].Rows.Count > 0)
            {
                if ((ds.Tables[0].Columns["Mobilenumber"].ToString().ToLower() == "mobile") &&
                    (ds.Tables[0].Columns["Name"].ToString().ToLower() == "name") &&
                    (ds.Tables[0].Columns["flightdate"].ToString().ToLower() == "flightdate") &&
                    (ds.Tables[0].Columns["scheduleddeparturetime"].ToString().ToLower() == "departuretime") &&
                    (ds.Tables[0].Columns["Airport"].ToString().ToLower() == "airport") &&
                    (ds.Tables[0].Columns["terminal"].ToString().ToLower() == "terminal"))
                {

                    for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                    {
                     
                        row = j.ToString();
                        DateTime FlightDate = Convert.ToDateTime(ds.Tables[0].Rows[j]["flightdate"],info);

                        objClass = new WisemiserSMS();
                        column = ds.Tables[0].Columns["Mobilenumber"].ToString().ToLower();
                        objClass.PhoneNoxsl = ds.Tables[0].Rows[j]["Mobilenumber"].ToString();
                        column = ds.Tables[0].Columns["Name"].ToString().ToLower();
                        objClass.name = ds.Tables[0].Rows[j]["Name"].ToString();
                        objClass.servertime = System.DateTime.Now;
                        column = ds.Tables[0].Columns["Airport"].ToString().ToLower();
                        objClass.airport = ds.Tables[0].Rows[j]["Airport"].ToString();
                        column = ds.Tables[0].Columns["terminal"].ToString().ToLower();
                        objClass.terminal = ds.Tables[0].Rows[j]["terminal"].ToString();
                        cmd1.CommandType = CommandType.StoredProcedure;
                        cmd1.Parameters.AddWithValue("@phoneno", ds.Tables[0].Rows[j]["Mobilenumber"].ToString());
                        cmd1.Parameters.AddWithValue("@name", ds.Tables[0].Rows[j]["Name"].ToString());
                        column = ds.Tables[0].Columns["flightdate"].ToString().ToLower();
                        cmd1.Parameters.AddWithValue("@flightdate", FlightDate.Month + "/" + FlightDate.Day + "/" + FlightDate.Year);
                        cmd1.Parameters.AddWithValue("@airport", ds.Tables[0].Rows[j]["Airport"].ToString());
                        cmd1.Parameters.AddWithValue("@terminal", ds.Tables[0].Rows[j]["terminal"].ToString());
                        column = ds.Tables[0].Columns["scheduleddeparturetime"].ToString().ToLower();
                        cmd1.Parameters.AddWithValue("@schedularDepartureTime", Convert.ToDateTime(ds.Tables[0].Rows[j]["scheduleddeparturetime"]));
                        cmd1.ExecuteNonQuery();
                        transectionocurs = "y";
                        cmd1.Parameters.Clear();

                    }


                }
                else
                {
                    saveit = false;
                    lblError.Text = "Error:Column name is not correct";

                    if (transectionocurs == "y")
                        myTrans.Rollback();
                    System.IO.File.Delete(filePath);
                }

            }
            else
            {
                saveit = false;
                lblError.Text = "Error:No data found in the file";

                if (transectionocurs == "y")
                    myTrans.Rollback();
                System.IO.File.Delete(filePath);
            }


            if (saveit == true)
            {
                myTrans.Commit();
                lblError.Text = "Data uploaded successfully";
            }
            //con.Close();
            //cmd1.Dispose();

        }
        catch (Exception ex)
        {
            if (row != "" && column != "")
            {
                lblError.Text = "Row  " + (Convert.ToInt32(row) + 1) + " and column " + column + " has wrong data";
                System.IO.File.Delete(filePath);
                if (transectionocurs == "y")
                    myTrans.Rollback();
            }
            else
            {
                lblError.Text = "You have wrong data in excel sheet.Please rectify";
                System.IO.File.Delete(filePath);
                if (transectionocurs == "y")
                    myTrans.Rollback();
            }
        }

    }

Upload files in C#

 protected void btnUpload_Click(object sender, EventArgs e)
    {

        if (FileUpload1.HasFile == true)
        {

            filePath = Server.MapPath("~/FileUpload/" + FileUpload1.FileName);

            string strExension = Path.GetExtension(filePath);
            if (strExension == ".xls")
            {
                FileUpload1.SaveAs(filePath);



                readExcel(filePath, "Source Data");


            }
            else
            {
                lblError.ForeColor = System.Drawing.Color.Red;
                lblError.Text = "Please select a file with .XLS file extension (Excel File).";
            }

Data Access Layer (DAL Example) in ASP.NET (C#)

DAL Example


DAL resides in appcode folder.It is a simple C# class.

Create a new folder name it appcode & now add a new C# class in it & write code for database connectivity.

//Import Required Namespace Here
public class       CALLConnection
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
    public WisemiserSMS()
    {

    }
    #region properties

    string _phoneNumber;
    public string PhoneNumber { get { return _phoneNumber; } set { _phoneNumber = value; } }
    int _msgId;
    public int MessageId { get { return _msgId; } set { _msgId = value; } }
    int _userId;
    public int UserId { get { return _userId; } set { _userId = value; } }
    string _messageText;
    public string MessageText { get { return _messageText; } set { _messageText = value; } }
    DateTime _sentTime;
    public DateTime SentTime { get { return _sentTime; } set { _sentTime = value; } }
    string _userName;
    public string UserName { get { return _userName; } set { _userName = value; } }
    string _password;

 
    #endregion

    public void sentSMSFlag()
    {
    openConnection();
        DataSet ds = new DataSet();
        SqlCommand cmd = new SqlCommand("sp_smsSentFlag", con);
 
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@id", this.id);
        //cmd.Parameters.AddWithValue("@raiseflag", this.addedtime);

        cmd.ExecuteNonQuery();
        con.Close();
        cmd.Dispose();
    }

}


Now we can call it in our code like this  CALLConnection.sentSMSFlag()  

Casting date & time to datetime in SQL

Casting & concatenating date & time field of a table to datetime & perform a datetime search query

Okey its a quick post.We will get date & time separably from C# & we will concatenating in DB as a datetime & perform a search query.


In C# to SQL 

Suppose  variable 1=11/9/2011 10:53:00 AM

V2=11/9/2011 12:42:50

In table we have two fields  like one is date & second is time  .Now we will cast them in to Datetime & search with datetime parameter of C#

--[sp_checkFlights]'11/9/2011 10:53:00 AM','11/9/2011 12:42:50'

ALTER PROCEDURE [dbo].[sp_checkFlights]

(

@gmtnow datetime,

@addedtime datetime

)

AS

BEGIN

SET NOCOUNT ON;

SELECT *

FROM tbl_sms_record

WHERE CONVERT(datetime, Convert(varchar, FlightDate) + ' ' + Convert(varchar(8), SchedularDepartureTime))

BETWEEN @gmtnow AND @addedtime

END

Log in Stored Procedure Best Practice in SQL

Log in Stored Procedure Best Practice



(
@uname varchar(100),
@password varchar(100)
)
As
declare @pass varchar(100)
select @pass=password from tbl_user where user_name=@uname
if(@pass=@password)
begin
select user_id,role,type from tbl_user where user_name=@uname and password=@pass
end
else
begin
select 0 as user_id
end

Upload & save a file to server in C#

Add a asp file upload control to your page

Now add new button  btnsave (save button)
 add this code on btnsave click

    protected void btnsave_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile == false)
        {
            label.Text = "Please select a file ";
        }
        else
        {

// Note  UploadedFiles is a folder Name in the Project.

            string filePath = Server.MapPath("~/UploadedFiles/" + FileUpload1.FileName);
            FileUpload1.SaveAs(filePath);
            Label2.Text = "File Uploaded succesfully";
        }




This is a very basic File uploading in C# .Also Please take care of security aspect,max file size,delete file after use,same names etc parameters into account .


Thanks

If - Else in T-SQL

Its an example of a simple stored procedure using IF-Else in SQL .

First of all,we declared a variable ( @cnt ) to count no of rows that matches the condition ( @cnt=COUNT(*) ).

Select @cnt=COUNT(*) from tbl_X where AirlineCode = @AirlinesCode AND DestinationCode = @DestinationCode


Structure of IF Else like this :-

IF @cnt=0
Then
Insert New Record
Else
Update the Existing Record


Stored Procedure would be :-


USE [XXX]
GO



Create PROCEDURE [dbo].[sp_Admin_InsertIncreasedFare]


@AirlinesCode varchar(100),
@DestinationCode varchar(100),
@Amount decimal(18, 2)


AS
BEGIN

SET NOCOUNT ON;

declare @cnt int=0;

Select @cnt=COUNT(*) from tbl_X where AirlineCode = @AirlinesCode AND DestinationCode = @DestinationCode

-- If loop--
IF @cnt=0

Begin

Insert into tbl_X (AirlineCode,DestinationCode,Amount) VALUES (@AirlinesCode,@DestinationCode,@Amount)

END

Else

Begin

Update tbl_X Set Amount=@Amount where AirlineCode = @AirlinesCode AND DestinationCode = @DestinationCode

END

END

Set Default Page in ASP.NET through web.config

Sometime you need to set a different landing,default page (other than home.aspx,index.htm) or suppose you are working on Godaddy's server.Then you can set you default page through web.config


Add this section in web.config :-

system.webServer
defaultdocument
files
clear/>
add value="CreateThing.aspx"/>
/files
/defaultDocument
/system.webServer

*Note : Keep each line in < > or < />

Custom Regular Expression for Integer & decimal validation

It will allow integer & decimal values (Eg- 19,.50,166666.34,)

\d{1,16}(,\d{16})*(\.\d\d)?|\.\d\d