/// <summary>
        /// UC16 RetrieveDataFromDB
        /// </summary>
        /// <returns></returns>
        public static ContactsDB RetrieveData()
        {
            try
            {
                connection = new SqlConnection(connectionString);

                ContactsDB contactsDB = new ContactsDB();

                string query = "select c.first_name, c.last_name, c.address, c.city, c.state, c.zipcode, c.phone_no, bk.Book_Name, bk.Book_Type from Contacts c inner join BookNameType bk on c.Book_ID=bk.Bookk_ID where c.first_name='Rachit'";

                SqlCommand command = new SqlCommand(query, connection);

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        contactsDB.first_name   = reader.GetString(0);
                        contactsDB.last_name    = reader.GetString(1);
                        contactsDB.address      = reader.GetString(2);
                        contactsDB.city         = reader.GetString(3);
                        contactsDB.state        = reader.GetString(4);
                        contactsDB.zipcode      = Convert.ToInt32(reader.GetInt32(5));
                        contactsDB.phone_no     = reader.GetString(6);
                        contactsDB.email        = reader.GetString(7);
                        contactsDB.Book_Name    = reader.GetString(8);
                        contactsDB.Contact_Type = reader.GetString(9);
                    }
                }
                else
                {
                    Console.WriteLine("No rows found");
                }
                reader.Close();

                return(contactsDB);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return(null);
            }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// UC16 RetrieveDataFromDB
        /// </summary>
        /// <returns></returns>
        public static ContactsDB RetrieveData()
        {
            try
            {
                connection = new SqlConnection(connectionString);

                ContactsDB contactsDB = new ContactsDB();

                string query = "select c.FirstName, c.LastName, c.City, c.PhoneNumber, bk.B_Name, bk.B_Type from Contacts c inner join BookNameType bk on c.B_ID=bk.B_ID where c.FirstName='Akash'";

                SqlCommand command = new SqlCommand(query, connection);

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        contactsDB.first_name = reader.GetString(0);
                        contactsDB.last_name  = reader.GetString(1);
                        contactsDB.city       = reader.GetString(2);
                        contactsDB.phone      = reader.GetString(3);
                        contactsDB.B_Name     = reader.GetString(4);
                        contactsDB.B_Type     = reader.GetString(5);
                    }
                }
                else
                {
                    Console.WriteLine("No rows found");
                }
                reader.Close();

                return(contactsDB);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return(null);
            }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// UC19 RetrieveDataByCityorState
        /// </summary>
        public static void RetrieveDataByCityorState()
        {
            ContactsDB contactsDB = new ContactsDB();

            try
            {
                connection = new SqlConnection(connectionString);
                string query = "select c.FirstName, c.LastName, c.City, c.State, c.PhoneNumber, bk.B_Name, bk.B_Type from Contacts c inner join BookNameType bk on c.B_ID=bk.B_ID where City='Mulund' or State='Bengal'";

                SqlCommand command = new SqlCommand(query, connection);

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        contactsDB.first_name = reader.GetString(0);
                        contactsDB.last_name  = reader.GetString(1);
                        contactsDB.city       = reader.GetString(2);
                        contactsDB.state      = reader.GetString(3);
                        contactsDB.phone      = reader.GetString(4);
                        contactsDB.B_Name     = reader.GetString(5);
                        contactsDB.B_Type     = reader.GetString(6);
                        Console.WriteLine("FirstName: " + contactsDB.first_name + ", LastName: " + contactsDB.last_name + ", City: " + contactsDB.city + ", State: " + contactsDB.state + ", Phone: " + contactsDB.phone + ", BookName: " + contactsDB.B_Name + ", BookType: " + contactsDB.B_Type);
                        Console.WriteLine();
                    }
                }
                else
                {
                    Console.WriteLine("No rows found");
                }
                reader.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
            }
            finally
            {
                connection.Close();
            }
        }
示例#4
0
        public bool AddContactDetailsInDB(ContactsDB model)
        {
            try
            {
                using (connection)
                {
                    connection = new SqlConnection(connectionString);
                    SqlCommand command = new SqlCommand("InsertIntoMultipleTables", connection);
                    connection.Open();

                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@BookID", model.B_ID);
                    command.Parameters.AddWithValue("@BookName", model.B_Name);
                    command.Parameters.AddWithValue("@BookType", model.B_Type);
                    command.Parameters.AddWithValue("@FirstName", model.first_name);
                    command.Parameters.AddWithValue("@LastName", model.last_name);

                    var result = command.ExecuteNonQuery();
                    //this.connection.Close();
                    if (result != 0)
                    {
                        return(true);
                    }
                    return(false);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                connection.Close();
                return(false);
            }
            finally
            {
                connection.Close();
            }
        }