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