Wrox Programmer Forums
Go Back   Wrox Programmer Forums > C# and C > C# 2012/5.0 > C# 2012/5.0 General Discussion
|
C# 2012/5.0 General Discussion Discussions about the C# 2012 language and tool not related to any specific Wrox book
Welcome to the p2p.wrox.com Forums.

You are currently viewing the C# 2012/5.0 General Discussion section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 14th, 2013, 11:48 PM
Registered User
 
Join Date: Aug 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Post Web service to do insert/update based on SqlDataReader hasrows value

I have posted this on several forums including the asp.net one to no avail. I have a webservice, it either adds or updates room inventory based on whether SqlDataReader hasrows being true or false. (Code below).
The problem I am having with this code is that it will either do a add for the entire range of dates entered or an entire an update for that entire range otherwise it throws an error like for example:
If I am changing inventory for the period 18/8/2013 - 25/8/2013 and 18/8-23/8 has no inventory but 24/8-25/8 has; an error is thrown because of the availability in 24/8 saying inventory is available and insert is not possible due to primary key constraints, halting the entire process.
I want my code to either add or update across the date range depending on whether its there or not rather then doing just an add or just update of everything.
Is that possible? How do I change my code for that?

Would be grateful for any insight on how to do this.

Code:
[WebMethod(Description = "Add or Amend Availability & Rates")]
        public bool Avail(string Username, string Password, DateTime Dte, DateTime Dtm, int ID, string RoomType, int Qty, double CurPrice)
        {
            GetCredentials(Username, Password);
            int ID= Convert.ToInt16(GetCredentials(Username, Password).Tables[0].Rows[0]["strTypeID"]);
            bool retVal = false;
            GetCredentials(Username, Password);
            using (SqlConnection mySQLconnection = new SqlConnection(connStr))
            {
                using (SqlCommand dbCommand = new SqlCommand("select * from Available where intID=@ID and dtm=@Dtm and strRoomType=@RoomType", mySQLconnection))
                {
                    SqlParameter dt = new SqlParameter("@Dtm", Dtm);
                    SqlParameter RoomT = new SqlParameter("@RoomType", RoomType);
                    SqlParameter typeI = new SqlParameter("@ID", ID);
                    dbCommand.Parameters.Add(dt);
                    dbCommand.Parameters.Add(RoomT);
                    dbCommand.Parameters.Add(typeI);
                    mySQLconnection.Open();
                    using (SqlDataReader reader = dbCommand.ExecuteReader())
                    {
                        if (!reader.HasRows)
                        {
                            AddAvail(Username, Password, Dte, Dtm, RoomType, Qty, CurPrice);
                            retVal = false;
                        }
                        else
                        {
                            AmdAvail(Username, Password, Dte, Dtm, RoomType, Qty, CurPrice);
                            retVal = true;
                        }
                        mySQLconnection.Close();
                        dbCommand.Dispose();
                        mySQLconnection.Dispose();
                        return retVal;
                    }            
                }
            }
        }

 /*----------------------------------------------------
     * Webmethod AddAvail Adds multiple availability for speicifed date range
     * ---------------------------------------------------*/
    [WebMethod(Description = "Multiple Add of Availability", BufferResponse = true)]
    public void AddAvail(string Username, string Password, DateTime Dte,DateTime Dtm, string RoomType, int Qty, double CurPrice)
    {
        GetAuthCredentials(Username, Password);
        DateTime dat = Dtm;
        int strTypeID = Convert.ToInt16(GetAuthCredentials(Username, Password).Tables[0].Rows[0]["strTypeID"]);
        using (SqlConnection mySQLconnection = new SqlConnection(connStr))
        {
            mySQLconnection.Open();
            for (DateTime date = Dte; date <= dat; date = date.AddDays(1.0))
            {
                string sqlInsertString = "INSERT INTO Available (dtm,intResortID,strRoomType,intQty,curPrice) VALUES (@dat,@strTypeID,@strRoomType,@intQty,@CurPrice)";
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = mySQLconnection;
                    command.CommandText = sqlInsertString;
                    SqlParameter dt = new SqlParameter("@dat", date);
                    SqlParameter intRID = new SqlParameter("@strTypeID", strTypeID);
                    SqlParameter strRType = new SqlParameter("@strRoomType", RoomType);
                    SqlParameter intQuty = new SqlParameter("@intQty", Qty);
                    SqlParameter curpPrice = new SqlParameter("@curPrice", CurPrice);
                    command.Parameters.AddRange(new SqlParameter[] { dt, intRID, strRType, intQuty, curpPrice });
                    command.ExecuteNonQuery();
                }         
            }
        }           
    }
    /*-----------------------------------------------------
    * Webmethod AmdAvail Amends multiple Availability for specified date range
    * -----------------------------------------------------*/
    [WebMethod(Description = "Multilple Updates", BufferResponse = true)]
    public DataSet AmdAvail(string Username, string Password, DateTime Dte, DateTime Dtm, string RoomType, int Qty, double CurPrice)
    {
        GetAuthCredentials(Username, Password);
        int strTypeID = Convert.ToInt16(GetAuthCredentials(Username, Password).Tables[0].Rows[0]["strTypeID"]);
        using (SqlConnection mySQLconnection = new SqlConnection(connStr))
        {
            mySQLconnection.Open();
            using (SqlCommand dbCommand = new SqlCommand())
            {
                dbCommand.CommandText = "Update Available set intQty=@Qty,curprice=@CurPrice where dtm between @Dte and @Dtm and strRoomType=@Roomtype and intResortID=@strTypeID ";
                dbCommand.Connection = mySQLconnection;
                //Create new DataAdapter
                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    da.SelectCommand = dbCommand;
                    SqlParameter typeI = new SqlParameter("@strTypeID", strTypeID);
                    dbCommand.Parameters.Add(typeI);
                    dbCommand.Parameters.AddWithValue("@Dtm", Dtm);
                    dbCommand.Parameters.AddWithValue("@Dte", Dte);
                    dbCommand.Parameters.AddWithValue("@Qty", Qty);
                    dbCommand.Parameters.AddWithValue("@RoomType", RoomType);
                    dbCommand.Parameters.AddWithValue("@curprice", CurPrice);
                    dbCommand.Parameters.AddWithValue("@username", Username);
                    dbCommand.Parameters.AddWithValue("@password", Password);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return ds;
                }
            }      
        }                     
    }





Similar Threads
Thread Thread Starter Forum Replies Last Post
UpdateListItem Web Service to Update Sharepoint Document Library ismailc C# 2005 0 January 7th, 2010 03:10 AM
Web Service Consuming another web service CraigWhitfield EJB 0 January 10th, 2008 08:38 AM
Service Oriented Web Service aldwinenriquez .NET Web Services 2 September 15th, 2005 03:25 AM
HasRows problem using Visual Studio 2002 Uncle Gizmo BOOK: Beginning VB.NET Databases 2 April 12th, 2005 08:14 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.